View Javadoc
1   package fr.ifremer.quadrige3.core.dao.technical;
2   
3   /*-
4    * #%L
5    * Quadrige3 Core :: Quadrige3 Core Shared
6    * $Id:$
7    * $HeadURL:$
8    * %%
9    * Copyright (C) 2017 Ifremer
10   * %%
11   * This program is free software: you can redistribute it and/or modify
12   * it under the terms of the GNU Affero General Public License as published by
13   * the Free Software Foundation, either version 3 of the License, or
14   * (at your option) any later version.
15   * 
16   * This program is distributed in the hope that it will be useful,
17   * but WITHOUT ANY WARRANTY; without even the implied warranty of
18   * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
19   * GNU General Public License for more details.
20   * 
21   * You should have received a copy of the GNU Affero General Public License
22   * along with this program.  If not, see <http://www.gnu.org/licenses/>.
23   * #L%
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   * Useful method around DAO and entities.
71   *
72   * @author Benoit Lavenier <benoit.lavenier@e-is.pro>
73   * @since 3.5
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       * Constant <code>DB_DIRECTORY="db"</code>
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       * Logger.
95       */
96      private static final Log log = LogFactory.getLog(Daos.class);
97  
98      private static final boolean debug = log.isDebugEnabled();
99  
100     /**
101      * <p>Constructor for Daos.</p>
102      */
103     protected Daos() {
104         // helper class does not instantiate
105     }
106 
107     /**
108      * Create a new hibernate configuration, with all hbm.xml files for the schema need for app
109      *
110      * @param jdbcUrl  a {@link java.lang.String} object.
111      * @param username a {@link java.lang.String} object.
112      * @param password a {@link java.lang.String} object.
113      * @param schema   a {@link java.lang.String} object.
114      * @param dialect  a {@link java.lang.String} object.
115      * @param driver   a {@link java.lang.String} object.
116      * @return the hibernate Configuration
117      */
118     public static Properties getConnectionProperties(String jdbcUrl, String username, String password, String schema, String dialect, String driver) {
119 
120         // Building a new configuration
121         Properties p = new Properties();
122 
123         // Set driver
124         p.setProperty(Environment.DRIVER, driver);
125 
126         // Set hibernate dialect
127         p.setProperty(Environment.DIALECT, dialect);
128 
129         // To be able to retrieve connection
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         // Try with synonyms enable
139         p.setProperty(AvailableSettings.ENABLE_SYNONYMS, "true");
140 
141         // Pour tester avec le metadata generic (normalement plus long pour Oracle)
142         // cfg.setProperty("hibernatetool.metadatadialect", "org.hibernate.cfg.rveng.dialect.JDBCMetaDataDialect");
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      * <p>closeSilently.</p>
152      *
153      * @param statement a {@link java.sql.Statement} object.
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      * <p>closeSilently.</p>
181      *
182      * @param connection a {@link java.sql.Connection} object.
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      * <p>closeSilently.</p>
198      *
199      * @param statement a {@link java.sql.ResultSet} object.
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      * <p>closeSilently.</p>
227      *
228      * @param session a {@link org.hibernate.Session} object.
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      * <p>createConnection.</p>
245      *
246      * @param connectionProperties a {@link java.util.Properties} object.
247      * @return a {@link java.sql.Connection} object.
248      * @throws java.sql.SQLException if any.
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      * <p>getUrl.</p>
260      *
261      * @param connectionProperties a {@link java.util.Properties} object.
262      * @return a {@link java.lang.String} object.
263      */
264     public static String getUrl(Properties connectionProperties) {
265         return connectionProperties.getProperty(Environment.URL);
266     }
267 
268     /**
269      * <p>getUser.</p>
270      *
271      * @param connectionProperties a {@link java.util.Properties} object.
272      * @return a {@link java.lang.String} object.
273      */
274     public static String getUser(Properties connectionProperties) {
275         return connectionProperties.getProperty(Environment.USER);
276     }
277 
278     /**
279      * <p>getDriver.</p>
280      *
281      * @param connectionProperties a {@link java.util.Properties} object.
282      * @return a {@link java.lang.String} object.
283      */
284     public static String getDriver(Properties connectionProperties) {
285         return connectionProperties.getProperty(Environment.DRIVER);
286     }
287 
288     /**
289      * <p>createConnection.</p>
290      *
291      * @param jdbcUrl  a {@link java.lang.String} object.
292      * @param user     a {@link java.lang.String} object.
293      * @param password a {@link java.lang.String} object.
294      * @return a {@link java.sql.Connection} object.
295      * @throws java.sql.SQLException if any.
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         // If same URL as datasource, use the dataSource
308         Connection connection = DataSourceUtils.getConnection(dataSource);
309         connection.setAutoCommit(false);
310         return connection;
311     }
312 
313     /**
314      * <p>fillConnectionProperties.</p>
315      *
316      * @param p        a {@link java.util.Properties} object.
317      * @param url      a {@link java.lang.String} object.
318      * @param username a {@link java.lang.String} object.
319      * @param password a {@link java.lang.String} object.
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      * <p>getJdbcUrl.</p>
332      *
333      * @param directory a {@link java.io.File} object.
334      * @param dbName    a {@link java.lang.String} object.
335      * @return a {@link java.lang.String} object.
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      * <p>isHsqlDatabase.</p>
345      *
346      * @param jdbcUrl a {@link java.lang.String} object.
347      * @return a boolean.
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      * <p>isOracleDatabase.</p>
367      *
368      * @param jdbcUrl a {@link java.lang.String} object.
369      * @return a boolean.
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      * <p>isHsqlFileDatabase.</p>
383      *
384      * @param jdbcUrl a {@link java.lang.String} object.
385      * @return a boolean.
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      * <p>isHsqlFileDatabase.</p>
394      *
395      * @param conn a {@link java.sql.Connection} object.
396      * @return a boolean.
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      * <p>getDbDirectoryFromJdbcUrl.</p>
411      *
412      * @param jdbcUrl a {@link java.lang.String} object.
413      * @return a {@link java.lang.String} object.
414      */
415     public static String getDbDirectoryFromJdbcUrl(String jdbcUrl) {
416         Assert.notNull(jdbcUrl);
417 
418         // HsqlDB file database
419         if (jdbcUrl.startsWith(JDBC_URL_PREFIX_HSQLDB_FILE)) {
420             String dbDirectory = jdbcUrl.substring(JDBC_URL_PREFIX_HSQLDB_FILE.length());
421 
422             // Remove the DB name
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      * <p>setIntegrityConstraints.</p>
435      *
436      * @param connectionProperties       a {@link java.util.Properties} object.
437      * @param enableIntegrityConstraints a boolean.
438      * @throws java.sql.SQLException if any.
439      */
440     public static void setIntegrityConstraints(Properties connectionProperties, boolean enableIntegrityConstraints) throws SQLException {
441         // Execute the SQL order
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      * <p>setIntegrityConstraints.</p>
454      *
455      * @param connection                 a {@link java.sql.Connection} object.
456      * @param enableIntegrityConstraints a boolean.
457      * @throws java.sql.SQLException if any.
458      */
459     public static void setIntegrityConstraints(Connection connection, boolean enableIntegrityConstraints) throws SQLException {
460         String jdbcUrl = connection.getMetaData().getURL();
461 
462         String sql;
463         // if HSQLDB
464         if (isHsqlDatabase(jdbcUrl)) {
465             Version hsqldbVersion = getDatabaseVersion(connection);
466 
467             // 1.8 :
468             if ("1.8".equals(hsqldbVersion.toString())) {
469                 sql = "SET REFERENTIAL_INTEGRITY %s";
470             }
471 
472             // 2.x :
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         // else: not supported operation
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      * Check if connection properties are valid. Try to open a SQL connection, then close it. If no error occur, the connection is valid.
498      *
499      * @param jdbcDriver a {@link java.lang.String} object.
500      * @param jdbcUrl    a {@link java.lang.String} object.
501      * @param user       a {@link java.lang.String} object.
502      * @param password   a {@link java.lang.String} object.
503      * @return a boolean.
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      * Check if connection properties are valid. Try to open a SQL connection, then close it. If no error occur, the connection is valid.
535      *
536      * @param connectionProperties a {@link java.util.Properties} object.
537      * @return a boolean.
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      * <p>computeDistanceInMeters.</p>
557      *
558      * @param startLatitude  a {@link java.lang.Float} object.
559      * @param startLongitude a {@link java.lang.Float} object.
560      * @param endLatitude    a {@link java.lang.Float} object.
561      * @param endLongitude   a {@link java.lang.Float} object.
562      * @return a int.
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      * <p>getDistanceInMiles.</p>
582      *
583      * @param distance a {@link java.lang.Float} object.
584      * @return a {@link java.lang.String} object.
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      * <p>getRoundedLengthStep.</p>
600      *
601      * @param lengthStep a float.
602      * @param aroundUp   a boolean.
603      * @return a float.
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      * <p>getDecimalFormatSymbols.</p>
612      *
613      * @return a {@link java.text.DecimalFormatSymbols} object.
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      * <p>Getter for the field <code>decimalFormat</code>.</p>
626      *
627      * @param minDecimal a int.
628      * @param maxDecimal a int.
629      * @return a {@link java.text.DecimalFormat} object.
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      * <p>getWeightStringValue.</p>
644      *
645      * @param weight a {@link java.lang.Float} object.
646      * @return a {@link java.lang.String} object.
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      * <p>getValueOrComputedValue.</p>
662      *
663      * @param value         a N object.
664      * @param computedValue a N object.
665      * @return a N object.
666      */
667     public static <N extends Number> N getValueOrComputedValue(N value, N computedValue) {
668         return value == null ? computedValue : value;
669     }
670 
671     /**
672      * <p>getValueOrComputedValueComputed.</p>
673      *
674      * @param value         a N object.
675      * @param computedValue a N object.
676      * @return a {@link java.lang.Boolean} object.
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      * Round the given value to max 4 digits.
691      *
692      * @param value the float to round.
693      * @return the rounded value
694      * @since 1.0.1
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      * Compare two weights with rounding them to kilograms.
703      *
704      * @param v0 first weight to compare
705      * @param v1 second weight to compare
706      * @return 1 if v0 > v1, -1 if v0 < v1, 0 if v0 == v1
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             // v0 > v1
715             result = 1;
716         } else if (delta < -0.0001f) {
717             // v0 < v1
718             result = -1;
719         } else {
720             // v0 == v1
721             result = 0;
722         }
723         return result;
724     }
725 
726     /**
727      * <p>isSmallerWeight.</p>
728      *
729      * @param v0 a float.
730      * @param v1 a float.
731      * @return a boolean.
732      */
733     public static boolean isSmallerWeight(float v0, float v1) {
734         return compareWeights(v0, v1) < 0;
735     }
736 
737     /**
738      * <p>isGreaterWeight.</p>
739      *
740      * @param v0 a float.
741      * @param v1 a float.
742      * @return a boolean.
743      */
744     public static boolean isGreaterWeight(float v0, float v1) {
745         return compareWeights(v0, v1) > 0;
746     }
747 
748     /**
749      * <p>isEqualWeight.</p>
750      *
751      * @param v0 a float.
752      * @param v1 a float.
753      * @return a boolean.
754      */
755     public static boolean isEqualWeight(float v0, float v1) {
756         return compareWeights(v0, v1) == 0;
757     }
758 
759     /**
760      * <p>isNotEqualWeight.</p>
761      *
762      * @param v0 a float.
763      * @param v1 a float.
764      * @return a boolean.
765      */
766     public static boolean isNotEqualWeight(float v0, float v1) {
767         return compareWeights(v0, v1) != 0;
768     }
769 
770     /**
771      * set parameter values for a query: 'statusValidCode' and 'statusTemporaryCode'
772      *
773      * @param query a query with this parameters inside
774      * @return the given query object
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      * <p>convertToDouble.</p>
784      *
785      * @param floatValue a {@link java.lang.Float} object.
786      * @return a {@link java.lang.Double} object.
787      */
788     public static Double convertToDouble(Float floatValue) {
789         // TODO : trouver une meilleur solution (attention à ne pas perdre de précision)
790         return floatValue != null ? Double.parseDouble(Float.toString(floatValue)) : null;
791     }
792 
793     /**
794      * <p>convertToFloat.</p>
795      *
796      * @param doubleValue a {@link java.lang.Double} object.
797      * @return a {@link java.lang.Float} object.
798      */
799     public static Float convertToFloat(Double doubleValue) {
800         // TODO : trouver une meilleur solution (attention à ne pas perdre de précision)
801         return doubleValue != null ? Float.parseFloat(Double.toString(doubleValue)) : null;
802     }
803 
804     /**
805      * <p>convertToInteger.</p>
806      *
807      * @param value a {@link java.lang.String} object.
808      * @return a {@link java.lang.Integer} object.
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      * <p>convertToInteger.</p>
824      *
825      * @param numericalValue a {@link java.lang.Float} object.
826      * @return a {@link java.lang.Integer} object.
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      * <p>convertToDate.</p>
847      *
848      * @param date a {@link java.lang.Object} object.
849      * @return a {@link java.util.Date} object.
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      * <p>safeConvertToBoolean.</p>
863      *
864      * @param object       a {@link java.lang.Object} object.
865      * @param defaultValue a boolean.
866      * @return a boolean.
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      * Convert to boolean (and return false if null)
883      *
884      * @param object a {@link java.lang.Object} object.
885      * @return a boolean.
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      * Convert to Boolean (may return null)
902      *
903      * @param object a {@link java.lang.Object} object.
904      * @return a {@link java.lang.Boolean} object.
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      * <p>convertToString.</p>
928      *
929      * @param bool a {@link java.lang.Boolean} object.
930      * @return a {@link java.lang.String} object.
931      */
932     public static String convertToString(Boolean bool) {
933         return bool == null ? null : (bool ? "1" : "0");
934     }
935 
936     /**
937      * <p>sqlUpdate.</p>
938      *
939      * @param dataSource a {@link javax.sql.DataSource} object.
940      * @param sql        a {@link java.lang.String} object.
941      * @return a int.
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      * <p>sqlUpdate.</p>
954      *
955      * @param connection a {@link java.sql.Connection} object.
956      * @param sql        a {@link java.lang.String} object.
957      * @return a int.
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         // Log using a special logger
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      * <p>sqlUnique.</p>
983      *
984      * @param dataSource a {@link javax.sql.DataSource} object.
985      * @param sql        a {@link java.lang.String} object.
986      * @return a {@link java.lang.Object} object.
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      * <p>sqlUniqueTyped.</p>
999      *
1000      * @param dataSource a {@link javax.sql.DataSource} object.
1001      * @param sql        a {@link java.lang.String} object.
1002      * @param <T>        a T object.
1003      * @return a T object.
1004      */
1005     @SuppressWarnings("unchecked")
1006     public static <T> T sqlUniqueTyped(DataSource dataSource, String sql) {
1007         return (T) sqlUnique(dataSource, sql);
1008     }
1009 
1010     /**
1011      * <p>sqlUnique.</p>
1012      *
1013      * @param connection a {@link java.sql.Connection} object.
1014      * @param sql        a {@link java.lang.String} object.
1015      * @return a {@link java.lang.Object} object.
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         // Log using a special logger
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      * <p>sqlUniqueOrNull.</p>
1050      *
1051      * @param connection a {@link java.sql.Connection} object.
1052      * @param sql        a {@link java.lang.String} object.
1053      * @return a {@link java.lang.Object} object.
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         // Log using a special logger
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      * <p>sqlUniqueTyped.</p>
1088      *
1089      * @param connection a {@link java.sql.Connection} object.
1090      * @param sql        a {@link java.lang.String} object.
1091      * @param <T>        a T object.
1092      * @return a T object.
1093      */
1094     @SuppressWarnings("unchecked")
1095     public static <T> T sqlUniqueTyped(Connection connection, String sql) {
1096         return (T) sqlUnique(connection, sql);
1097     }
1098 
1099     /**
1100      * <p>shutdownDatabase.</p>
1101      *
1102      * @param connection a {@link java.sql.Connection} object.
1103      */
1104     public static void shutdownDatabase(Connection connection) {
1105         shutdownDatabase(connection, false);
1106     }
1107 
1108     /**
1109      * <p>shutdownDatabase.</p>
1110      *
1111      * @param dataSource a {@link javax.sql.DataSource} object.
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      * <p>shutdownDatabase.</p>
1128      *
1129      * @param connection a {@link java.sql.Connection} object.
1130      * @param compact    a boolean.
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      * <p>shutdownDatabase.</p>
1149      *
1150      * @param connectionProperties a {@link java.util.Properties} object.
1151      * @throws java.sql.SQLException if any.
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      * <p>prepareQuery.</p>
1164      *
1165      * @param connection a {@link java.sql.Connection} object.
1166      * @param sql        a {@link java.lang.String} object.
1167      * @return a {@link java.sql.PreparedStatement} object.
1168      * @throws java.sql.SQLException if any.
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      * <p>bindQuery.</p>
1181      *
1182      * @param connection a {@link java.sql.Connection} object.
1183      * @param sql        a {@link java.lang.String} object.
1184      * @param bindingMap a {@link java.util.Map} object.
1185      * @return a {@link java.sql.PreparedStatement} object.
1186      * @throws java.sql.SQLException if any.
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      * Concat single quoted strings with ',' character, without parenthesis
1244      *
1245      * @param strings a {@link Collection} object.
1246      * @return concatenated strings
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      * Concat integers with ',' character, without parenthesis
1254      *
1255      * @param integers a {@link Collection} object.
1256      * @return concatenated integers
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      * <p>compactDatabase.</p>
1264      *
1265      * @param dataSource a {@link javax.sql.DataSource} object.
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      * <p>compactDatabase.</p>
1278      *
1279      * @param connectionProperties a {@link java.util.Properties} object.
1280      * @throws java.sql.SQLException if any.
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      * Will compact database (only for HsqlDB connection)<br/>
1293      * This method typically call a 'CHECKPOINT DEFRAG'
1294      *
1295      * @param connection a valid JDBC connection
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      * Transform into a string (e.g. for log...) the given connection properties
1312      *
1313      * @param connectionProperties a {@link java.util.Properties} object.
1314      * @return a {@link java.lang.String} object.
1315      */
1316     public static String getLogString(Properties connectionProperties) {
1317         Assert.notNull(connectionProperties);
1318         StringBuilder result = new StringBuilder();
1319 
1320         // Driver
1321         String jdbcDriver = getDriver(connectionProperties);
1322         if (StringUtils.isNotBlank(jdbcDriver)) {
1323             result.append(t("quadrige3.persistence.connection.driver", jdbcDriver)).append('\n');
1324         }
1325 
1326         // DB Directory (if any)
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         // URL
1336         result.append(t("quadrige3.persistence.connection.url", getUrl(connectionProperties))).append('\n');
1337 
1338         // User
1339         result.append(t("quadrige3.persistence.connection.username", getUser(connectionProperties))).append('\n');
1340 
1341         // Catalog
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         // Schema
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      * Count number of rows in a table
1358      *
1359      * @param connection a {@link java.sql.Connection} object.
1360      * @param tableName  a {@link java.lang.String} object.
1361      * @return a long.
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      * Check DB directory tree, and return directory with database files inside
1387      * Throw exception if error in tree, or if script and properties files are not found
1388      *
1389      * @param dbDirectory a {@link java.io.File} object.
1390      * @return a {@link java.io.File} object.
1391      */
1392     public static File checkAndNormalizeDbDirectory(File dbDirectory) {
1393         Assert.notNull(dbDirectory);
1394         Assert.isTrue(dbDirectory.isDirectory(), "must be a directory");
1395 
1396         // Collect files and directories
1397         Collection<File> subFilesAndDirs = FileUtils.listFilesAndDirs(dbDirectory, TrueFileFilter.INSTANCE, TrueFileFilter.INSTANCE);
1398         subFilesAndDirs.remove(dbDirectory); // remove itself (keep children only)
1399 
1400         // If only one file or dir: should be directory (root directory)
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             // If not the 'db' directory, try to use it as main directory
1412             if (!subFileOrDir.getName().equalsIgnoreCase(DB_DIRECTORY)) {
1413                 dbDirectory = subFileOrDir;
1414                 subFilesAndDirs = FileUtils.listFilesAndDirs(dbDirectory, TrueFileFilter.INSTANCE, TrueFileFilter.INSTANCE);
1415                 subFilesAndDirs.remove(dbDirectory); // remove itself (keep children only)
1416             }
1417         }
1418 
1419         // Try to find a db directory
1420         for (File subDirOrFile : subFilesAndDirs) {
1421             if (subDirOrFile.isDirectory()
1422                     && subDirOrFile.getName().equalsIgnoreCase(DB_DIRECTORY)) {
1423                 dbDirectory = subDirOrFile;
1424                 break;
1425             }
1426         }
1427 
1428         // On db directory, try to find script and properties files
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      * Set collection items. Wille reuse the instance of the collection is possible
1452      *
1453      * @param existingEntities a {@link java.util.Collection} object.
1454      * @param function         a {@link Function} object.
1455      * @param vos              an array of V objects.
1456      * @param <E>              a E object.
1457      * @param <V>              a V object.
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      * Set collection items. Wille reuse the instance of the collection is possible
1468      *
1469      * @param existingEntities a {@link java.util.Collection} object.
1470      * @param function         a {@link Function} object.
1471      * @param vos              a {@link java.util.Collection} object.
1472      * @param <E>              a E object.
1473      * @param <V>              a V object.
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      * <p>getDatabaseCurrentTimestamp.</p>
1485      *
1486      * @param connection a {@link java.sql.Connection} object.
1487      * @param dialect    a {@link org.hibernate.dialect.Dialect} object.
1488      * @return a {@link java.sql.Timestamp} object.
1489      * @throws java.sql.SQLException if any.
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      * <p>getDatabaseVersion.</p>
1499      *
1500      * @param connection a {@link java.sql.Connection} object.
1501      * @return a {@link org.nuiton.version.Version} object.
1502      * @throws java.sql.SQLException if any.
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      * <p>convertToBigDecimal.</p>
1512      *
1513      * @param value       a {@link Double} object.
1514      * @param digitNumber a {@link Integer} object.
1515      * @return a {@link BigDecimal} object.
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     /* -- private methods  -- */
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      * Build a file path for a Photo like Q² <br/>
1593      * ex: PASS/OBJ60092549/PASS-OBJ60092549-60000320.jpg for the survey id=60092549 and photo id=60000320 <br/>
1594      * or: PREL/OBJ60165512/PREL-OBJ60165512-60003120.jpg for the sampling operation id=60165512 and photo id=60003120
1595      *
1596      * @param objectTypeCode a {@link String} object.
1597      * @param objectId a {@link Number} object.
1598      * @param photoId a {@link Number} object.
1599      * @param fileExtension a {@link String} object.
1600      * @return the local file path
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 }