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