001package fr.ifremer.adagio.synchro.dao;
002
003/*
004 * #%L
005 * Tutti :: Persistence API
006 * $Id: TuttiEntities.java 1578 2014-02-07 15:31:18Z tchemit $
007 * $HeadURL: http://svn.forge.codelutin.com/svn/tutti/trunk/tutti-persistence/src/main/java/fr/ifremer/tutti/persistence/entities/TuttiEntities.java $
008 * %%
009 * Copyright (C) 2012 Ifremer
010 * %%
011 * This program is free software: you can redistribute it and/or modify
012 * it under the terms of the GNU Affero General Public License as published by
013 * the Free Software Foundation, either version 3 of the License, or
014 * (at your option) any later version.
015 * 
016 * This program is distributed in the hope that it will be useful,
017 * but WITHOUT ANY WARRANTY; without even the implied warranty of
018 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
019 * GNU General Public License for more details.
020 * 
021 * You should have received a copy of the GNU Affero General Public License
022 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
023 * #L%
024 */
025
026import java.io.File;
027import java.math.BigDecimal;
028import java.math.MathContext;
029import java.sql.Connection;
030import java.sql.DriverManager;
031import java.sql.ResultSet;
032import java.sql.SQLException;
033import java.sql.Statement;
034import java.text.DecimalFormat;
035import java.text.DecimalFormatSymbols;
036import java.util.Properties;
037
038import org.apache.commons.lang3.StringUtils;
039import org.apache.commons.logging.Log;
040import org.apache.commons.logging.LogFactory;
041import org.hibernate.JDBCException;
042import org.hibernate.Session;
043import org.hibernate.cfg.AvailableSettings;
044import org.hibernate.cfg.Configuration;
045import org.hibernate.cfg.Environment;
046import org.hibernate.dialect.Dialect;
047import org.hibernate.exception.GenericJDBCException;
048import org.hibernate.exception.spi.SQLExceptionConversionDelegate;
049import org.hibernate.exception.spi.SQLExceptionConverter;
050
051import com.google.common.base.Preconditions;
052
053import fr.ifremer.adagio.synchro.SynchroTechnicalException;
054
055/**
056 * Usefull method around DAO and entities.
057 * 
058 * @author Benoit Lavenier <benoit.lavenier@e-is.pro>
059 * @since 3.5.4
060 */
061public class DaoUtils {
062    /** Logger. */
063    private static final Log log = LogFactory.getLog(DaoUtils.class);
064
065    private final static String JDBC_URL_PREFIX_HSQLDB = "jdbc:hsqldb:file:";
066
067    protected DaoUtils() {
068        // helper class does not instantiate
069    }
070
071    /**
072     * Create a new hibernate configuration, with all hbm.xml files
073     * for the schema need for app
074     * 
075     * @return the hibernate Configuration
076     */
077    public static Properties getConnectionProperties(String jdbcUrl, String username, String password, String schema, String dialect, String driver) {
078
079        // Building a new configuration
080        Properties p = new Properties();
081
082        // Set driver
083        p.setProperty(Environment.DRIVER, driver);
084
085        // Set hibernate dialect
086        p.setProperty(Environment.DIALECT, dialect);
087
088        // To be able to retrieve connection
089        p.setProperty(Environment.URL, jdbcUrl);
090        p.setProperty(Environment.USER, username);
091        p.setProperty(Environment.PASS, password);
092
093        if (StringUtils.isNotBlank(schema)) {
094            p.setProperty(Environment.DEFAULT_SCHEMA, schema);
095        }
096
097        // Try with synonyms enable
098        p.setProperty(AvailableSettings.ENABLE_SYNONYMS, "true");
099
100        // Pour tester avec le metadata generic (normalement plus long pour Oracle)
101        // cfg.setProperty("hibernatetool.metadatadialect", "org.hibernate.cfg.rveng.dialect.JDBCMetaDataDialect");
102        if (jdbcUrl.startsWith("jdbc:oracle")) {
103            p.setProperty("hibernatetool.metadatadialect", "org.hibernate.cfg.rveng.dialect.OracleMetaDataDialect");
104        }
105
106        return p;
107    }
108
109    /**
110     * Override default Hibernate 'org.hibernate.tool.hbm2ddl.DatabaseMetadata', because of th use of deprecated method
111     * buildSqlExceptionConverter()
112     * (see https://hibernate.atlassian.net/browse/HHH-9131)
113     * 
114     * @return
115     */
116    public static SQLExceptionConverter newSQLExceptionConverter(final Dialect dialect) {
117        // Build a valid sql converter
118        return new SQLExceptionConverter() {
119            private static final long serialVersionUID = 5458961195167573495L;
120
121            SQLExceptionConversionDelegate delegate = dialect.buildSQLExceptionConversionDelegate();;
122
123            @Override
124            public JDBCException convert(SQLException sqlException, String message, String sql) {
125                JDBCException exception = delegate.convert(sqlException, message, sql);
126                if (exception != null) {
127                    return exception;
128                }
129                return new GenericJDBCException(message, sqlException, sql);
130            }
131        };
132    }
133
134    public static void shutdownDatabase(Properties connectionProperties) throws SQLException {
135        Connection conn = DaoUtils.createConnection(connectionProperties);
136        try {
137            shutdownDatabase(conn);
138        } finally {
139            closeSilently(conn);
140        }
141    }
142
143    public static void closeSilently(Statement statement) {
144        try {
145            if (statement != null && !statement.isClosed()) {
146                statement.close();
147            }
148        } catch (AbstractMethodError e) {
149            try {
150                statement.close();
151            } catch (SQLException e1) {
152            }
153            if (log.isDebugEnabled()) {
154                log.debug("Fix this linkage error, damned hsqlsb 1.8.0.7:(");
155            }
156        } catch (IllegalAccessError e) {
157            if (log.isDebugEnabled()) {
158                log.debug("Fix this IllegalAccessError error, damned hsqlsb 1.8.0.7:(");
159            }
160        } catch (Exception e) {
161            if (log.isErrorEnabled()) {
162                log.error("Could not close statement, but do not care", e);
163            }
164        }
165    }
166
167    public static void closeSilently(Connection connection) {
168        try {
169            if (connection != null && !connection.isClosed()) {
170                connection.close();
171            }
172        } catch (Exception e) {
173            try {
174                connection.close();
175            } catch (SQLException e1) {
176            }
177            if (log.isErrorEnabled()) {
178                log.error("Could not close connection, but do not care", e);
179            }
180        }
181    }
182
183    public static void closeSilently(ResultSet statement) {
184        try {
185            if (statement != null && !statement.isClosed()) {
186
187                statement.close();
188            }
189        } catch (AbstractMethodError e) {
190            try {
191                statement.close();
192            } catch (SQLException e1) {
193            }
194            if (log.isDebugEnabled()) {
195                log.debug("Fix this linkage error, damned hsqlsb 1.8.0.7:(");
196            }
197        } catch (IllegalAccessError e) {
198            if (log.isDebugEnabled()) {
199                log.debug("Fix this IllegalAccessError error, damned hsqlsb 1.8.0.7:(");
200            }
201        } catch (Exception e) {
202            if (log.isErrorEnabled()) {
203                log.error("Could not close statement, but do not care", e);
204            }
205        }
206    }
207
208    public static void closeSilently(Session session) {
209        try {
210            if (session != null && session.isOpen()) {
211
212                session.close();
213            }
214        } catch (Exception e) {
215            if (log.isErrorEnabled()) {
216                log.error("Could not close session, but do not care", e);
217            }
218        }
219    }
220
221    public static Connection createConnection(Properties connectionProperties) throws SQLException {
222        return createConnection(
223                connectionProperties.getProperty(Environment.URL),
224                connectionProperties.getProperty(Environment.USER),
225                connectionProperties.getProperty(Environment.PASS));
226    }
227
228    public static Connection createConnection(String jdbcUrl,
229            String user,
230            String password) throws SQLException {
231        Connection connection = DriverManager.getConnection(jdbcUrl,
232                user,
233                password);
234        connection.setAutoCommit(false);
235        return connection;
236    }
237
238    public static void fillConnectionProperties(Properties p,
239            String url,
240            String username,
241            String password) {
242        p.put(Environment.URL, url);
243        p.put(Environment.USER, username);
244        p.put(Environment.PASS, password);
245    }
246
247    public static String getJdbcUrl(File directory, String dbName) {
248        String jdbcUrl = JDBC_URL_PREFIX_HSQLDB + directory.getAbsolutePath() + "/" + dbName;
249        jdbcUrl = jdbcUrl.replaceAll("\\\\", "/");
250        return jdbcUrl;
251    }
252
253    public static boolean isFileDatabase(String jdbcUrl) {
254        Preconditions.checkNotNull(jdbcUrl);
255        return jdbcUrl.startsWith(JDBC_URL_PREFIX_HSQLDB);
256    }
257
258    /**
259     * Check if connection properties are valid. Try to open a SQL connection, then close it.
260     * If no error occur, the connection is valid.
261     * 
262     * @param jdbcDriver
263     * @param jdbcUrl
264     * @param user
265     * @param password
266     * @return
267     */
268    public static boolean isValidConnectionProperties(Properties connectionProperties) {
269        return isValidConnectionProperties(
270                connectionProperties.getProperty(Environment.DRIVER),
271                connectionProperties.getProperty(Environment.URL),
272                connectionProperties.getProperty(Environment.USER),
273                connectionProperties.getProperty(Environment.PASS));
274    }
275
276    /**
277     * Check if connection properties are valid. Try to open a SQL connection, then close it.
278     * If no error occur, the connection is valid.
279     * 
280     * @param jdbcDriver
281     * @param jdbcUrl
282     * @param user
283     * @param password
284     * @return
285     */
286    public static boolean isValidConnectionProperties(
287            String jdbcDriver,
288            String jdbcUrl,
289            String user,
290            String password) {
291        String driverClassName = jdbcDriver;
292        try {
293            Class<?> driverClass = Class.forName(driverClassName);
294            DriverManager.registerDriver((java.sql.Driver) driverClass.newInstance());
295        } catch (Exception e) {
296            log.error("Could not load JDBC Driver: " + e.getMessage(), e);
297            return false;
298        }
299
300        Connection connection = null;
301        try {
302            connection = createConnection(
303                    jdbcUrl,
304                    user,
305                    password);
306            return true;
307        } catch (SQLException e) {
308            log.error("Could not connect to database: " + e.getMessage().trim());
309        } finally {
310            DaoUtils.closeSilently(connection);
311        }
312        return false;
313    }
314
315    public static String getUrl(Properties connectionProperties) {
316        return connectionProperties.getProperty(Environment.URL);
317    }
318
319    public static Dialect getDialect(Properties connectionProperties) {
320        return Dialect.getDialect(connectionProperties);
321    }
322
323    public static Configuration getConfiguration(Properties connectionProperties) {
324        return new Configuration().setProperties(connectionProperties);
325    }
326
327    public static void shutdownDatabase(Connection connection) {
328        try {
329            String jdbcUrl = connection.getMetaData().getURL();
330            if (jdbcUrl.startsWith(JDBC_URL_PREFIX_HSQLDB)) {
331                sqlUpdate(connection, "SHUTDOWN");
332            }
333        } catch (SQLException e) {
334            e.printStackTrace();
335        }
336
337    }
338
339    public static int sqlUpdate(Connection connection, String sql) {
340        Statement stmt = null;
341        try {
342            stmt = connection.createStatement();
343        } catch (SQLException ex) {
344            closeSilently(stmt);
345            throw new SynchroTechnicalException("Could not open database connection", ex);
346        }
347
348        // Log using a special logger
349        if (log.isDebugEnabled()) {
350            log.debug(sql);
351        }
352
353        try {
354            return stmt.executeUpdate(sql);
355        } catch (SQLException ex) {
356            throw new SynchroTechnicalException("Could not execute query: " + sql, ex);
357        } finally {
358            closeSilently(stmt);
359        }
360    }
361
362}