1 package net.sumaris.core.dao.technical;
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27 import com.google.common.base.Preconditions;
28 import com.google.common.collect.Lists;
29 import net.sumaris.core.config.SumarisConfiguration;
30 import net.sumaris.core.dao.technical.model.IUpdateDateEntityBean;
31 import net.sumaris.core.exception.BadUpdateDateException;
32 import net.sumaris.core.exception.SumarisTechnicalException;
33 import net.sumaris.core.util.Beans;
34 import net.sumaris.core.util.Dates;
35 import net.sumaris.core.util.Geometries;
36 import org.apache.commons.collections4.CollectionUtils;
37 import org.apache.commons.io.FileUtils;
38 import org.apache.commons.io.filefilter.TrueFileFilter;
39 import org.apache.commons.lang3.StringUtils;
40 import org.slf4j.Logger;
41 import org.slf4j.LoggerFactory;
42 import org.hibernate.Session;
43 import org.hibernate.cfg.AvailableSettings;
44 import org.hibernate.cfg.Environment;
45 import org.hibernate.dialect.Dialect;
46 import org.nuiton.i18n.I18n;
47 import org.nuiton.version.Version;
48 import org.nuiton.version.Versions;
49 import org.springframework.dao.DataAccessResourceFailureException;
50 import org.springframework.dao.DataIntegrityViolationException;
51 import org.springframework.dao.DataRetrievalFailureException;
52 import org.springframework.jdbc.datasource.DataSourceUtils;
53
54 import javax.sql.DataSource;
55 import java.io.File;
56 import java.math.BigDecimal;
57 import java.math.MathContext;
58 import java.sql.*;
59 import java.text.DecimalFormat;
60 import java.text.DecimalFormatSymbols;
61 import java.text.SimpleDateFormat;
62 import java.time.OffsetDateTime;
63 import java.time.ZoneOffset;
64 import java.util.Date;
65 import java.util.*;
66 import java.util.function.Function;
67 import java.util.regex.Matcher;
68 import java.util.regex.Pattern;
69
70 import static org.nuiton.i18n.I18n.t;
71
72
73
74
75
76
77
78 public class Daos {
79
80 private final static String JDBC_URL_PREFIX = "jdbc:";
81 private final static String JDBC_URL_PREFIX_HSQLDB = JDBC_URL_PREFIX + "hsqldb:";
82 private final static String JDBC_URL_PREFIX_ORACLE = JDBC_URL_PREFIX + "oracle:";
83 private final static String JDBC_URL_PREFIX_HSQLDB_FILE = JDBC_URL_PREFIX_HSQLDB + "file:";
84
85
86
87
88
89 public static final String DB_DIRECTORY = "db";
90
91
92
93
94 private static final Logger log = LoggerFactory.getLogger(Daos.class);
95
96 private static final boolean debug = log.isDebugEnabled();
97
98
99
100
101 protected Daos() {
102
103 }
104
105
106
107
108
109
110
111
112
113
114
115
116 public static Properties getConnectionProperties(String jdbcUrl, String username, String password, String schema, String dialect, String driver) {
117
118
119 Properties p = new Properties();
120
121
122 p.setProperty(Environment.DRIVER, driver);
123
124
125 p.setProperty(Environment.DIALECT, dialect);
126
127
128 p.setProperty(Environment.URL, jdbcUrl);
129 p.setProperty(Environment.USER, username);
130 p.setProperty(Environment.PASS, password);
131
132 if (StringUtils.isNotBlank(schema)) {
133 p.setProperty(Environment.DEFAULT_SCHEMA, schema);
134 }
135
136
137 p.setProperty(AvailableSettings.ENABLE_SYNONYMS, "true");
138
139
140
141 if (jdbcUrl.startsWith("jdbc:oracle")) {
142 p.setProperty("hibernatetool.metadatadialect", "org.hibernate.cfg.rveng.dialect.OracleMetaDataDialect");
143 }
144
145 return p;
146 }
147
148
149
150
151
152
153 public static void closeSilently(Statement statement) {
154 try {
155 if (statement != null && !statement.isClosed()) {
156 statement.close();
157 }
158 } catch (AbstractMethodError e) {
159 try {
160 statement.close();
161 } catch (SQLException ignored) {
162 }
163 if (debug) {
164 log.debug("Fix this linkage error, damned hsqlsb 1.8.0.7:(");
165 }
166 } catch (IllegalAccessError e) {
167 if (debug) {
168 log.debug("Fix this IllegalAccessError error, damned hsqlsb 1.8.0.7:(");
169 }
170 } catch (Exception e) {
171 if (log.isErrorEnabled()) {
172 log.error("Could not close statement, but do not care", e);
173 }
174 }
175 }
176
177
178
179
180
181
182 public static void closeSilently(Connection connection) {
183 try {
184 if (connection != null && !connection.isClosed()) {
185 connection.close();
186 }
187 } catch (Exception e) {
188 if (log.isErrorEnabled()) {
189 log.error("Could not close connection, but do not care", e);
190 }
191 }
192 }
193
194
195
196
197
198
199 public static void closeSilently(ResultSet statement) {
200 try {
201 if (statement != null && !statement.isClosed()) {
202
203 statement.close();
204 }
205 } catch (AbstractMethodError e) {
206 try {
207 statement.close();
208 } catch (SQLException ignored) {
209 }
210 if (debug) {
211 log.debug("Fix this linkage error, damned hsqlsb 1.8.0.7:(");
212 }
213 } catch (IllegalAccessError e) {
214 if (debug) {
215 log.debug("Fix this IllegalAccessError error, damned hsqlsb 1.8.0.7:(");
216 }
217 } catch (Exception e) {
218 if (log.isErrorEnabled()) {
219 log.error("Could not close statement, but do not care", e);
220 }
221 }
222 }
223
224
225
226
227
228
229 public static void closeSilently(Session session) {
230 try {
231 if (session != null && session.isOpen()) {
232
233 session.close();
234 }
235 } catch (Exception e) {
236 if (log.isErrorEnabled()) {
237 log.error("Could not close session, but do not care", e);
238 }
239 }
240 }
241
242
243
244
245
246
247
248
249 public static Connection createConnection(Properties connectionProperties) throws SQLException {
250 return createConnection(
251 connectionProperties.getProperty(Environment.URL),
252 connectionProperties.getProperty(Environment.USER),
253 connectionProperties.getProperty(Environment.PASS)
254 );
255 }
256
257
258
259
260
261
262
263 public static String getUrl(Properties connectionProperties) {
264 return connectionProperties.getProperty(Environment.URL);
265 }
266
267
268
269
270
271
272
273 public static String getUser(Properties connectionProperties) {
274 return connectionProperties.getProperty(Environment.USER);
275 }
276
277
278
279
280
281
282
283 public static String getDriver(Properties connectionProperties) {
284 return connectionProperties.getProperty(Environment.DRIVER);
285 }
286
287
288
289
290
291
292
293
294
295
296 public static Connection createConnection(String jdbcUrl,
297 String user,
298 String password) throws SQLException {
299 Connection connection = DriverManager.getConnection(jdbcUrl,
300 user,
301 password);
302 connection.setAutoCommit(false);
303 return connection;
304 }
305
306
307
308
309
310
311
312
313
314 public static void fillConnectionProperties(Properties p,
315 String url,
316 String username,
317 String password) {
318 p.put(Environment.URL, url);
319 p.put(Environment.USER, username);
320 p.put(Environment.PASS, password);
321 }
322
323
324
325
326
327
328
329
330 public static String getJdbcUrl(File directory, String dbName) {
331 String jdbcUrl = JDBC_URL_PREFIX_HSQLDB_FILE + directory.getAbsolutePath() + "/" + dbName;
332 jdbcUrl = jdbcUrl.replaceAll("\\\\", "/");
333 return jdbcUrl;
334 }
335
336 public static String getDbms(String jdbcUrl) {
337 Preconditions.checkNotNull(jdbcUrl);
338 Preconditions.checkArgument(jdbcUrl.startsWith(JDBC_URL_PREFIX));
339 return jdbcUrl.substring(JDBC_URL_PREFIX.length(), jdbcUrl.indexOf(":", JDBC_URL_PREFIX.length()));
340 }
341
342
343
344
345
346
347
348 public static boolean isHsqlDatabase(String jdbcUrl) {
349 Preconditions.checkNotNull(jdbcUrl);
350 return jdbcUrl.startsWith(JDBC_URL_PREFIX_HSQLDB);
351 }
352
353 public static boolean isHsqlDatabase(Connection conn) {
354 Preconditions.checkNotNull(conn);
355 try {
356 String jdbcUrl = conn.getMetaData().getURL();
357 return isHsqlDatabase(jdbcUrl);
358 }
359 catch(SQLException e) {
360 throw new SumarisTechnicalException(e);
361 }
362 }
363
364
365
366
367
368
369
370 public static boolean isOracleDatabase(String jdbcUrl) {
371 Preconditions.checkNotNull(jdbcUrl);
372 return jdbcUrl.startsWith(JDBC_URL_PREFIX_ORACLE);
373 }
374
375 public static boolean isOracleDatabase(Connection conn) {
376 Preconditions.checkNotNull(conn);
377 try {
378 String jdbcUrl = conn.getMetaData().getURL();
379 return isOracleDatabase(jdbcUrl);
380 }
381 catch(SQLException e) {
382 throw new SumarisTechnicalException(e);
383 }
384 }
385
386
387
388
389
390
391
392 public static boolean isFileDatabase(String jdbcUrl) {
393 Preconditions.checkNotNull(jdbcUrl);
394 return jdbcUrl.startsWith(JDBC_URL_PREFIX_HSQLDB_FILE);
395 }
396
397
398
399
400
401
402
403 public static boolean isFileDatabase(Connection conn) {
404 Preconditions.checkNotNull(conn);
405 try {
406 String jdbcUrl = conn.getMetaData().getURL();
407 return isFileDatabase(jdbcUrl);
408 }
409 catch(SQLException e) {
410 throw new SumarisTechnicalException(e);
411 }
412 }
413
414
415
416
417
418
419
420 public static String getDbDirectoryFromJdbcUrl(String jdbcUrl) {
421 Preconditions.checkNotNull(jdbcUrl);
422
423
424 if (jdbcUrl.startsWith(JDBC_URL_PREFIX_HSQLDB_FILE)) {
425 String dbDirectory = jdbcUrl.substring(JDBC_URL_PREFIX_HSQLDB_FILE.length());
426
427
428 int lastSlashIndex = dbDirectory.lastIndexOf('/');
429 if (lastSlashIndex != -1) {
430 dbDirectory = dbDirectory.substring(0, lastSlashIndex);
431 }
432 return dbDirectory;
433 }
434
435 return null;
436 }
437
438
439
440
441
442
443
444
445 public static void setIntegrityConstraints(Properties connectionProperties, boolean enableIntegrityConstraints) throws SQLException {
446
447 Connection connection = null;
448 try {
449 connection = createConnection(connectionProperties);
450 setIntegrityConstraints(connection, enableIntegrityConstraints);
451 } finally {
452 closeSilently(connection);
453 }
454
455 }
456
457
458
459
460
461
462
463
464 public static void setIntegrityConstraints(Connection connection, boolean enableIntegrityConstraints) throws SQLException {
465 String jdbcUrl = connection.getMetaData().getURL();
466
467 String sql;
468
469 if (isHsqlDatabase(jdbcUrl)) {
470 Version hsqldbVersion = getDatabaseVersion(connection);
471
472
473 if ("1.8".equals(hsqldbVersion.toString())) {
474 sql = "SET REFERENTIAL_INTEGRITY %s";
475 }
476
477
478 else {
479 sql = "SET DATABASE REFERENTIAL INTEGRITY %s";
480 }
481 sql = String.format(sql, enableIntegrityConstraints ? "TRUE" : "FALSE");
482 sqlUpdate(connection, sql);
483 }
484
485
486
487
488
489
490 else {
491 throw new SumarisTechnicalException(String.format(
492 "Could not enable/disable integrity constraints on database: %s. Not implemented for this DBMS.", jdbcUrl));
493 }
494
495 }
496
497
498
499
500
501
502
503
504
505
506 public static boolean isValidConnectionProperties(
507 String jdbcDriver,
508 String jdbcUrl,
509 String user,
510 String password) {
511 try {
512 Class<?> driverClass = Class.forName(jdbcDriver);
513 DriverManager.registerDriver((Driver) driverClass.newInstance());
514 } catch (Exception e) {
515 log.error("Could not load JDBC Driver: " + e.getMessage(), e);
516 return false;
517 }
518
519 Connection connection = null;
520 try {
521 connection = createConnection(
522 jdbcUrl,
523 user,
524 password);
525 return true;
526 } catch (SQLException e) {
527 log.error("Could not connect to database: " + e.getMessage().trim());
528 } finally {
529 Daos.closeSilently(connection);
530 }
531 return false;
532 }
533
534
535
536
537
538
539
540 public static boolean isValidConnectionProperties(Properties connectionProperties) {
541 return isValidConnectionProperties(
542 connectionProperties.getProperty(Environment.DRIVER),
543 connectionProperties.getProperty(Environment.URL),
544 connectionProperties.getProperty(Environment.USER),
545 connectionProperties.getProperty(Environment.PASS));
546 }
547
548 private static final MathContext MATH_CONTEXT_4_DIGIT = new MathContext(4);
549
550 private static DecimalFormatSymbols symbols;
551
552 private static DecimalFormat decimalFormat;
553
554
555
556
557
558
559
560
561
562
563
564 @Deprecated
565 public static int computeDistanceInMeters(Float startLatitude,
566 Float startLongitude,
567 Float endLatitude,
568 Float endLongitude) {
569 return Geometries.getDistanceInMeters(startLatitude, startLongitude, endLatitude, endLongitude);
570 }
571
572
573
574
575
576
577
578
579 @Deprecated
580 public static String getDistanceInMiles(Float distance) {
581 return Geometries.getDistanceInMilles(distance);
582 }
583
584
585
586
587
588
589
590
591 public static float getRoundedLengthStep(float lengthStep, boolean aroundUp) {
592 int intValue = (int) ((lengthStep + (aroundUp ? 0.001f : 0f)) * 10);
593 return intValue / 10f;
594 }
595
596
597
598
599
600
601 public static DecimalFormatSymbols getDecimalFormatSymbols() {
602 if (symbols == null) {
603 symbols = new DecimalFormatSymbols();
604 symbols.setDecimalSeparator('.');
605 symbols.setGroupingSeparator(' ');
606 }
607 return symbols;
608 }
609
610
611
612
613
614
615
616
617 public static DecimalFormat getDecimalFormat(int minDecimal, int maxDecimal) {
618 if (decimalFormat == null) {
619 decimalFormat = new DecimalFormat();
620 decimalFormat.setDecimalFormatSymbols(getDecimalFormatSymbols());
621 decimalFormat.setGroupingUsed(false);
622 }
623 decimalFormat.setMinimumFractionDigits(minDecimal);
624 decimalFormat.setMaximumFractionDigits(maxDecimal);
625 return decimalFormat;
626 }
627
628
629
630
631
632
633
634 public static String getWeightStringValue(Float weight) {
635 String textValue;
636 if (weight != null) {
637 DecimalFormat weightDecimalFormat = getDecimalFormat(1, 3);
638 textValue = weightDecimalFormat.format(weight);
639
640 } else {
641 textValue = "";
642 }
643 return textValue;
644 }
645
646
647
648
649
650
651
652
653 public static <N extends Number> N getValueOrComputedValue(N value, N computedValue) {
654 return value == null ? computedValue : value;
655 }
656
657
658
659
660
661
662
663
664 public static <N extends Number> Boolean getValueOrComputedValueComputed(N value, N computedValue) {
665 Boolean result;
666 if (value == null) {
667
668 result = computedValue == null ? null : true;
669 } else {
670 result = false;
671 }
672 return result;
673 }
674
675
676
677
678
679
680
681
682 public static float roundKiloGram(float value) {
683 BigDecimal sumB = new BigDecimal(value);
684 return sumB.abs(MATH_CONTEXT_4_DIGIT).floatValue();
685 }
686
687
688
689
690
691
692
693
694 public static int compareWeights(float v0, float v1) {
695 v0 = roundKiloGram(v0);
696 v1 = roundKiloGram(v1);
697 float delta = v0 - v1;
698 int result;
699 if (delta > 0.00001) {
700
701 result = 1;
702 } else if (delta < -0.0001f) {
703
704 result = -1;
705 } else {
706
707 result = 0;
708 }
709 return result;
710 }
711
712
713
714
715
716
717
718
719 public static boolean isSmallerWeight(float v0, float v1) {
720 return compareWeights(v0, v1) < 0;
721 }
722
723
724
725
726
727
728
729
730 public static boolean isGreaterWeight(float v0, float v1) {
731 return compareWeights(v0, v1) > 0;
732 }
733
734
735
736
737
738
739
740
741 public static boolean isEqualWeight(float v0, float v1) {
742 return compareWeights(v0, v1) == 0;
743 }
744
745
746
747
748
749
750
751
752 public static boolean isNotEqualWeight(float v0, float v1) {
753 return compareWeights(v0, v1) != 0;
754 }
755
756
757
758
759
760
761
762 public static Double convertToDouble(Float floatValue) {
763 if (floatValue == null) {
764 return null;
765 }
766
767 return Double.parseDouble(Float.toString(floatValue));
768 }
769
770
771
772
773
774
775
776 public static Float convertToFloat(Double doubleValue) {
777 if (doubleValue == null) {
778 return null;
779 }
780
781 return Float.parseFloat(Double.toString(doubleValue));
782 }
783
784
785
786
787
788
789
790 public static Integer convertToInteger(String value) {
791 if (value == null) {
792 return null;
793 }
794 try {
795 return Integer.parseInt(value);
796 } catch (NumberFormatException nfe) {
797 log.error("error when try to parse an integer", nfe);
798 return null;
799 }
800 }
801
802
803
804
805
806
807
808 public static Date convertToDate(Object date) {
809 if (date instanceof Timestamp) {
810 return new Date(((Timestamp) date).getTime());
811 } else if (date instanceof Date) {
812 return (Date) date;
813 } else {
814 return null;
815 }
816 }
817
818
819
820
821
822
823
824
825 public static boolean safeConvertToBoolean(Object object, boolean defaultValue) {
826 if (object instanceof Boolean) {
827 return ((Boolean) object);
828 } else if (object instanceof Number) {
829 return ((Number) object).intValue() > 0;
830 } else if (object instanceof String) {
831 if (StringUtils.isNumeric((String) object)) {
832 return Integer.parseInt((String) object) != 0;
833 }
834 }
835 return defaultValue;
836 }
837
838
839
840
841
842
843
844 public static boolean safeConvertToBoolean(Object object) {
845 if (object instanceof Boolean) {
846 return ((Boolean) object);
847 } else if (object instanceof Number) {
848 return ((Number) object).intValue() > 0;
849 } else if (object instanceof String) {
850 if (StringUtils.isNumeric((String) object)) {
851 return Integer.parseInt((String) object) != 0;
852 }
853 }
854 return false;
855 }
856
857
858
859
860
861
862
863 public static Boolean convertToBoolean(Object object) {
864 if (object == null) {
865 return null;
866 }
867 if (object instanceof Boolean) {
868 return ((Boolean) object);
869 } else if (object instanceof Number) {
870 return ((Number) object).intValue() > 0;
871 } else if (object instanceof String) {
872 if (StringUtils.isNumeric((String) object)) {
873 return Integer.parseInt((String) object) != 0;
874 }
875 } else if (object instanceof Character) {
876 if (StringUtils.isNumeric(object.toString())) {
877 return Integer.parseInt(object.toString()) != 0;
878 }
879 }
880 throw new SumarisTechnicalException(String.format("Unable to convert value to boolean, for class [%s]", object.getClass().getCanonicalName()));
881 }
882
883
884
885
886
887
888
889 public static String convertToString(Boolean bool) {
890 return bool == null ? null : (bool ? "1" : "0");
891 }
892
893
894
895
896
897
898
899
900 public static int sqlUpdate(DataSource dataSource, String sql) {
901 Connection connection = DataSourceUtils.getConnection(dataSource);
902 try {
903 return sqlUpdate(connection, sql);
904 } finally {
905 DataSourceUtils.releaseConnection(connection, dataSource);
906 }
907 }
908
909
910
911
912
913
914
915
916 public static int sqlUpdate(Connection connection, String sql) {
917 Statement stmt;
918 try {
919 stmt = connection.createStatement();
920 } catch (SQLException ex) {
921 throw new DataAccessResourceFailureException("Could not open database connection", ex);
922 }
923
924
925 if (debug) {
926 log.debug(sql);
927 }
928
929 try {
930 return stmt.executeUpdate(sql);
931 } catch (SQLException ex) {
932 throw new DataIntegrityViolationException("Could not execute query: " + sql, ex);
933 } finally {
934 closeSilently(stmt);
935 }
936 }
937
938
939
940
941
942
943
944
945 public static Object sqlUnique(DataSource dataSource, String sql) throws DataAccessResourceFailureException {
946 Connection connection = DataSourceUtils.getConnection(dataSource);
947 try {
948 return sqlUnique(connection, sql);
949 } finally {
950 DataSourceUtils.releaseConnection(connection, dataSource);
951 }
952 }
953
954
955
956
957
958
959
960
961 public static Object sqlUniqueTimestamp(DataSource dataSource, String sql) throws DataAccessResourceFailureException {
962 Connection connection = DataSourceUtils.getConnection(dataSource);
963 try {
964 return sqlUnique(connection, sql, true);
965 } finally {
966 DataSourceUtils.releaseConnection(connection, dataSource);
967 }
968 }
969
970
971
972
973
974
975
976
977
978 @SuppressWarnings("unchecked")
979 public static <T> T sqlUniqueTyped(DataSource dataSource, String sql) {
980 return (T) sqlUnique(dataSource, sql);
981 }
982
983
984
985
986
987
988
989
990 public static Object sqlUnique(Connection connection, String sql) {
991 return sqlUnique(connection, sql, false);
992 }
993
994
995
996
997
998
999
1000
1001 public static Object sqlUnique(Connection connection, String sql, boolean timestamp) {
1002 Statement stmt;
1003 try {
1004 stmt = connection.createStatement();
1005 } catch (SQLException ex) {
1006 throw new DataAccessResourceFailureException("Could not open database connection", ex);
1007 }
1008
1009
1010 if (debug) {
1011 log.debug(sql);
1012 }
1013
1014 try {
1015 ResultSet rs = stmt.executeQuery(sql);
1016 if (!rs.next()) {
1017 throw new DataRetrievalFailureException("Executed query return no row: " + sql);
1018 }
1019 Object result = timestamp
1020 ? rs.getTimestamp(1)
1021 : rs.getObject(1);
1022 if (rs.next()) {
1023 throw new DataRetrievalFailureException("Executed query has more than one row: " + sql);
1024 }
1025 return result;
1026
1027 } catch (SQLException ex) {
1028 throw new DataIntegrityViolationException("Could not execute query: " + sql, ex);
1029 } finally {
1030 closeSilently(stmt);
1031 }
1032 }
1033
1034
1035
1036
1037
1038
1039
1040
1041 public static Object sqlUniqueOrNull(Connection connection, String sql) {
1042 Statement stmt;
1043 try {
1044 stmt = connection.createStatement();
1045 } catch (SQLException ex) {
1046 throw new DataAccessResourceFailureException("Could not open database connection", ex);
1047 }
1048
1049
1050 if (debug) {
1051 log.debug(sql);
1052 }
1053
1054 try {
1055 ResultSet rs = stmt.executeQuery(sql);
1056 if (!rs.next()) {
1057 return null;
1058 }
1059 Object result = rs.getObject(1);
1060 if (rs.next()) {
1061 throw new DataRetrievalFailureException("Executed query has more than one row: " + sql);
1062 }
1063 return result;
1064
1065 } catch (SQLException ex) {
1066 throw new DataIntegrityViolationException("Could not execute query: " + sql, ex);
1067 } finally {
1068 closeSilently(stmt);
1069 }
1070 }
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080 @SuppressWarnings("unchecked")
1081 public static <T> T sqlUniqueTyped(Connection connection, String sql) {
1082 return (T) sqlUnique(connection, sql);
1083 }
1084
1085
1086
1087
1088
1089
1090 public static void shutdownDatabase(Connection connection) {
1091 shutdownDatabase(connection, false);
1092 }
1093
1094
1095
1096
1097
1098
1099 public static void shutdownDatabase(DataSource dataSource) {
1100 Connection connection = DataSourceUtils.getConnection(dataSource);
1101 try {
1102 shutdownDatabase(connection);
1103 } finally {
1104 DataSourceUtils.releaseConnection(connection, dataSource);
1105 }
1106 }
1107
1108
1109
1110
1111
1112
1113
1114 public static void shutdownDatabase(Connection connection, boolean compact) {
1115 try {
1116 String jdbcUrl = connection.getMetaData().getURL();
1117 if (isFileDatabase(jdbcUrl)) {
1118 String sql = "SHUTDOWN";
1119 if (compact) {
1120 sql += " COMPACT";
1121 }
1122 sqlUpdate(connection, sql);
1123 }
1124 } catch (SQLException e) {
1125 e.printStackTrace();
1126 }
1127 }
1128
1129
1130
1131
1132
1133
1134
1135 public static void shutdownDatabase(Properties connectionProperties) throws SQLException {
1136 Connection conn = Daos.createConnection(connectionProperties);
1137 try {
1138 shutdownDatabase(conn);
1139 } finally {
1140 closeSilently(conn);
1141 }
1142 }
1143
1144
1145
1146
1147
1148
1149
1150
1151
1152 public static PreparedStatement prepareQuery(Connection connection, String sql) throws SQLException {
1153
1154 if (debug) {
1155 log.debug(String.format("Execute query: %s", sql));
1156 }
1157
1158 return connection.prepareStatement(sql);
1159 }
1160
1161
1162
1163
1164
1165
1166
1167
1168
1169
1170 public static PreparedStatement bindQuery(Connection connection, String sql, Map<String, Object> bindingMap) throws SQLException {
1171 StringBuilder sb = new StringBuilder();
1172
1173 StringBuilder debugParams = null;
1174 if (debug) {
1175 debugParams = new StringBuilder();
1176 }
1177
1178 List<Object> orderedBindingValues = Lists.newArrayList();
1179 Matcher paramMatcher = Pattern.compile(":[a-zA-Z_0-9]+").matcher(sql);
1180 int offset = 0;
1181 while (paramMatcher.find()) {
1182 String bindingName = sql.substring(paramMatcher.start() + 1, paramMatcher.end());
1183 Object bindingValue = bindingMap.get(bindingName);
1184 if (bindingValue == null && !bindingMap.containsKey(bindingName)) {
1185 log.error(t("sumaris.persistence.bindingQuery.error.log",
1186 bindingName,
1187 sql));
1188 throw new DataAccessResourceFailureException(t("sumaris.persistence.bindingQuery.error",
1189 sql));
1190 }
1191 orderedBindingValues.add(bindingValue);
1192 sb.append(sql.substring(offset, paramMatcher.start()))
1193 .append("?");
1194 offset = paramMatcher.end();
1195
1196 if (debug) {
1197 debugParams.append(", ").append(bindingValue);
1198 }
1199 }
1200 if (offset > 0) {
1201 if (offset < sql.length()) {
1202 sb.append(sql.substring(offset));
1203 }
1204 sql = sb.toString();
1205 }
1206
1207 if (debug) {
1208 log.debug(String.format("Execute query: %s", sql));
1209 log.debug(String.format(" with params: [%s]", debugParams.length() > 2 ? debugParams.substring(2)
1210 : "no binding"));
1211 }
1212
1213 PreparedStatement statement = connection.prepareStatement(sql);
1214
1215 int index = 1;
1216 for (Object value : orderedBindingValues) {
1217 statement.setObject(index, value);
1218 index++;
1219 }
1220
1221 return statement;
1222 }
1223
1224
1225
1226
1227
1228
1229 public static void compactDatabase(DataSource dataSource) {
1230 Connection connection = DataSourceUtils.getConnection(dataSource);
1231 try {
1232 compactDatabase(connection);
1233 } finally {
1234 DataSourceUtils.releaseConnection(connection, dataSource);
1235 }
1236 }
1237
1238
1239
1240
1241
1242
1243
1244 public static void compactDatabase(Properties connectionProperties) throws SQLException {
1245 Connection conn = Daos.createConnection(connectionProperties);
1246 try {
1247 compactDatabase(conn);
1248 } finally {
1249 closeSilently(conn);
1250 }
1251 }
1252
1253
1254
1255
1256
1257
1258
1259 public static void compactDatabase(Connection connection) {
1260
1261 try {
1262 connection.setReadOnly(false);
1263 String jdbcUrl = connection.getMetaData().getURL();
1264 if (jdbcUrl.startsWith(JDBC_URL_PREFIX_HSQLDB)) {
1265 String sql = "CHECKPOINT DEFRAG";
1266 sqlUpdate(connection, sql);
1267 }
1268 } catch (SQLException e) {
1269 throw new DataAccessResourceFailureException(I18n.t("sumaris.persistence.compactDatabase.error"), e);
1270 }
1271 }
1272
1273
1274
1275
1276
1277
1278
1279 public static String getLogString(Properties connectionProperties) {
1280 Preconditions.checkNotNull(connectionProperties);
1281 StringBuilder result = new StringBuilder();
1282
1283
1284 String jdbcDriver = getDriver(connectionProperties);
1285 if (StringUtils.isNotBlank(jdbcDriver)) {
1286 result.append(t("sumaris.persistence.connection.driver", jdbcDriver)).append('\n');
1287 }
1288
1289
1290 String jdbcUrl = Daos.getUrl(connectionProperties);
1291 if (Daos.isFileDatabase(jdbcUrl)) {
1292 String dbDirectory = Daos.getDbDirectoryFromJdbcUrl(jdbcUrl);
1293 if (dbDirectory != null) {
1294 result.append(t("sumaris.persistence.connection.directory", dbDirectory)).append('\n');
1295 }
1296 }
1297
1298
1299 result.append(t("sumaris.persistence.connection.url", getUrl(connectionProperties))).append('\n');
1300
1301
1302 result.append(t("sumaris.persistence.connection.username", getUser(connectionProperties))).append('\n');
1303
1304
1305 String jdbcCatalog = connectionProperties.getProperty(Environment.DEFAULT_CATALOG);
1306 if (StringUtils.isNotBlank(jdbcCatalog)) {
1307 result.append(t("sumaris.persistence.connection.catalog", jdbcCatalog)).append('\n');
1308 }
1309
1310
1311 String jdbcSchema = connectionProperties.getProperty(Environment.DEFAULT_SCHEMA);
1312 if (StringUtils.isNotBlank(jdbcSchema)) {
1313 result.append(t("sumaris.persistence.connection.schema", jdbcSchema)).append('\n');
1314 }
1315
1316 return result.substring(0, result.length() - 1);
1317 }
1318
1319
1320
1321
1322
1323
1324
1325
1326 public static long countTableRows(Connection connection, String tableName) {
1327
1328 String sql = "SELECT COUNT(*) FROM " + tableName;
1329 PreparedStatement statement = null;
1330 ResultSet rs;
1331 try {
1332 statement = connection.prepareStatement(sql);
1333 rs = statement.executeQuery();
1334 if (rs.next()) {
1335 Object result = rs.getObject(1);
1336 if (result != null && result instanceof Number) {
1337 return ((Number) result).longValue();
1338 }
1339 }
1340 throw new DataAccessResourceFailureException(String.format("Could not count rows for table %s, because query return no rows ! [%s]", tableName, sql));
1341 } catch (SQLException e) {
1342 throw new DataAccessResourceFailureException(String.format("Error while counting rows of table %s: [%s]", tableName, sql), e);
1343 } finally {
1344 Daos.closeSilently(statement);
1345 }
1346 }
1347
1348
1349
1350
1351
1352
1353
1354
1355 public static File checkAndNormalizeDbDirectory(File dbDirectory) {
1356 Preconditions.checkNotNull(dbDirectory);
1357 Preconditions.checkArgument(dbDirectory.isDirectory());
1358
1359
1360 Collection<File> subFilesAndDirs = FileUtils.listFilesAndDirs(dbDirectory, TrueFileFilter.INSTANCE, TrueFileFilter.INSTANCE);
1361 subFilesAndDirs.remove(dbDirectory);
1362
1363
1364 if (CollectionUtils.size(subFilesAndDirs) == 1) {
1365 File subFileOrDir = subFilesAndDirs.iterator().next();
1366 if (!subFileOrDir.isDirectory()) {
1367 throw new DataAccessResourceFailureException(t("sumaris.persistence.db.zip.badContent", DB_DIRECTORY));
1368 }
1369
1370 if (!subFileOrDir.isDirectory()) {
1371 throw new DataAccessResourceFailureException(t("sumaris.persistence.db.zip.badContent", DB_DIRECTORY));
1372 }
1373
1374
1375 if (!subFileOrDir.getName().equalsIgnoreCase(DB_DIRECTORY)) {
1376 dbDirectory = subFileOrDir;
1377 subFilesAndDirs = FileUtils.listFilesAndDirs(dbDirectory, TrueFileFilter.INSTANCE, TrueFileFilter.INSTANCE);
1378 subFilesAndDirs.remove(dbDirectory);
1379 }
1380 }
1381
1382
1383 for (File subDirOrFile : subFilesAndDirs) {
1384 if (subDirOrFile.isDirectory()
1385 && subDirOrFile.getName().equalsIgnoreCase(DB_DIRECTORY)) {
1386 dbDirectory = subDirOrFile;
1387 break;
1388 }
1389 }
1390
1391
1392 Collection<File> dbFiles = FileUtils.listFiles(dbDirectory, TrueFileFilter.INSTANCE, null);
1393 boolean hasScriptFile = false;
1394 boolean hasPropertiesFile = false;
1395 String dbName = SumarisConfiguration.getInstance().getDbName();
1396 for (File dbFile : dbFiles) {
1397 if (dbFile.isFile()
1398 && dbFile.getName().equalsIgnoreCase(dbName + ".script")) {
1399 hasScriptFile = true;
1400 }
1401 if (dbFile.isFile()
1402 && dbFile.getName().equalsIgnoreCase(dbName + ".properties")) {
1403 hasPropertiesFile = true;
1404 }
1405 }
1406 if (!hasScriptFile || !hasPropertiesFile) {
1407 throw new DataAccessResourceFailureException(t("sumaris.persistence.db.zip.badContent", DB_DIRECTORY));
1408 }
1409
1410 return dbDirectory;
1411 }
1412
1413
1414
1415
1416
1417
1418
1419
1420
1421
1422 public static <E, V> void replaceEntities(Collection<E> existingEntities, V[] vos, Function<V, E> function) {
1423 Preconditions.checkNotNull(existingEntities);
1424 Collection<E> newEntities = Beans.transformCollection(Arrays.asList(vos), function);
1425 existingEntities.clear();
1426 existingEntities.addAll(newEntities);
1427 }
1428
1429
1430
1431
1432
1433
1434
1435
1436
1437
1438 public static <E, V> void replaceEntities(Collection<E> existingEntities, Collection<V> vos, Function<V, E> function) {
1439 Preconditions.checkNotNull(existingEntities);
1440 Collection<E> newEntities = Beans.transformCollection(vos, function);
1441 existingEntities.clear();
1442 existingEntities.addAll(newEntities);
1443 }
1444
1445
1446
1447
1448
1449
1450
1451
1452
1453
1454 public static Timestamp getDatabaseCurrentTimestamp(Connection connection, Dialect dialect) throws SQLException {
1455 final String sql = dialect.getCurrentTimestampSelectString();
1456 Object result = Daos.sqlUniqueTyped(connection, sql);
1457 return toTimestampFromJdbcResult(result);
1458 }
1459
1460
1461
1462
1463
1464
1465
1466
1467 public static Version getDatabaseVersion(Connection connection) throws SQLException {
1468 int majorVersion = connection.getMetaData().getDatabaseMajorVersion();
1469 int minorVersion = connection.getMetaData().getDatabaseMinorVersion();
1470 return Versions.valueOf(String.format("%d.%d", majorVersion, minorVersion));
1471 }
1472
1473
1474
1475
1476
1477
1478
1479
1480 public static BigDecimal convertToBigDecimal(Number value, Integer digitNumber) {
1481
1482 if (value == null) {
1483 return null;
1484 }
1485
1486 int digitNb = digitNumber == null ? 0 : digitNumber;
1487 return new BigDecimal(String.format(Locale.US, "%." + digitNb + "f", value));
1488 }
1489
1490 public static Timestamp toTimestampFromJdbcResult(Object source) throws SQLException {
1491 Object result = source;
1492 if (!(result instanceof Timestamp)) {
1493 if (result instanceof Date) {
1494 result = new Timestamp(((Date) result).getTime());
1495 } else if (result instanceof OffsetDateTime) {
1496 result = new Timestamp(((OffsetDateTime) result).atZoneSimilarLocal(ZoneOffset.UTC).toInstant().toEpochMilli());
1497 } else {
1498 throw new SQLException("Could not get database current timestamp. Invalid result (not a timestamp ?): " + result);
1499 }
1500 }
1501 return (Timestamp) result;
1502 }
1503
1504
1505
1506
1507 public static void setTimezone(Connection connection, String timezone) throws SQLException {
1508 Preconditions.checkNotNull(timezone);
1509
1510 setTimezone(connection, TimeZone.getTimeZone(timezone));
1511 }
1512
1513 public static void setTimezone(Connection connection, TimeZone timezone) throws SQLException {
1514 Preconditions.checkNotNull(connection);
1515 Preconditions.checkNotNull(timezone);
1516
1517 if (isHsqlDatabase(connection.getMetaData().getURL())) {
1518
1519 int offset = timezone.getOffset(System.currentTimeMillis());
1520
1521 StringBuffer sql = new StringBuffer()
1522 .append("SET TIME ZONE INTERVAL '")
1523 .append(offset < 0 ? "-" : "+")
1524 .append(new SimpleDateFormat("hh:mm").format(new Date(Math.abs(offset))))
1525 .append("' HOUR TO MINUTE;");
1526 PreparedStatement ps = connection.prepareStatement(sql.toString());
1527 ps.execute();
1528 ps.close();
1529 }
1530 }
1531
1532 public static void checkUpdateDateForUpdate(IUpdateDateEntityBean<?, ? extends Date> source,
1533 IUpdateDateEntityBean<?, ? extends Date> entity) {
1534
1535 if (entity.getUpdateDate() != null) {
1536 Timestamp serverUpdateDtNoMillisecond = Dates.resetMillisecond(entity.getUpdateDate());
1537 Timestamp sourceUpdateDtNoMillisecond = Dates.resetMillisecond(source.getUpdateDate());
1538 if (!Objects.equals(sourceUpdateDtNoMillisecond, serverUpdateDtNoMillisecond)) {
1539 throw new BadUpdateDateException(I18n.t("sumaris.persistence.error.badUpdateDate",
1540 getTableName(entity.getClass().getSimpleName()), source.getId(), serverUpdateDtNoMillisecond,
1541 sourceUpdateDtNoMillisecond));
1542 }
1543 }
1544 }
1545
1546 public static String getTableName(String entityName) {
1547
1548 return I18n.t("sumaris.persistence.table."+ entityName.substring(0,1).toLowerCase() + entityName.substring(1));
1549 }
1550
1551 }