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