cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/database/CdmDataSourceBase.java

   1 /**
   2 * Copyright (C) 2007 EDIT
   3 * European Distributed Institute of Taxonomy
   4 * http://www.e-taxonomy.eu
   5 *
   6 * The contents of this file are subject to the Mozilla Public License VeresultSetion 1.1
   7 * See LICENSE.TXT at the top of this package for the full license terms.
   8 */
   9
  10 package eu.etaxonomy.cdm.database;
  11
  12 import java.io.PrintWriter;
  13 import java.sql.Connection;
  14 import java.sql.DatabaseMetaData;
  15 import java.sql.DriverManager;
  16 import java.sql.ResultSet;
  17 import java.sql.SQLException;
  18 import java.sql.Statement;
  19 import java.util.HashMap;
  20 import java.util.Map;
  21 import java.util.Properties;
  22
  23 import org.apache.logging.log4j.LogManager;
  24 import org.apache.logging.log4j.Logger;
  25 import org.hibernate.cache.spi.RegionFactory;
  26 import org.hibernate.envers.boot.internal.EnversIntegrator;
  27 import org.springframework.beans.MutablePropertyValues;
  28 import org.springframework.beans.factory.config.PropertiesFactoryBean;
  29 import org.springframework.beans.factory.support.AbstractBeanDefinition;
  30 import org.springframework.beans.factory.support.RootBeanDefinition;
  31
  32 import eu.etaxonomy.cdm.config.CdmSource;
  33 import eu.etaxonomy.cdm.config.CdmSourceException;
  34 import eu.etaxonomy.cdm.database.types.IDatabaseType;
  35 import eu.etaxonomy.cdm.model.metadata.CdmMetaDataPropertyName;
  36 import eu.etaxonomy.cdm.persistence.hibernate.HibernateConfiguration;
  37
  38 /**
  39  * @author a.mueller
  40  * @since 18.12.2008
  41  */
  42 public abstract class CdmDataSourceBase extends CdmSource implements ICdmDataSource  {
  43
  44     private static final Logger logger = LogManager.getLogger();
  45
  46
  47     //  private static final int TIMEOUT = 10;
  48     private Connection connection;
  49
  50
  51     @Override
  52     public Connection getConnection() throws SQLException {
  53         return getConnection(getUsername(), getPassword());
  54     }
  55
  56
  57     @Override
  58     public Connection getConnection(String username, String password) throws SQLException {
  59         try {
  60             if(connection != null){
  61                 boolean isValid = true;
  62 //                              try{
  63 //                                      isValid = connection.isValid(TIMEOUT);
  64 //                              } catch (java.lang.AbstractMethodError e){
  65 //                                      logger.error("Problems with Connection.isValid method\n" + "Exception: " + e.toString());
  66 //                              }
  67                 if (isValid){
  68                     return connection;
  69                 }
  70             }else{
  71                 IDatabaseType dbType = getDatabaseType().getDatabaseType();
  72                 String classString = dbType.getClassString();
  73                 Class.forName(classString);
  74                 String mUrl = dbType.getConnectionString(this);
  75                 Connection connection = DriverManager.getConnection(mUrl, username, password);
  76                 return  connection;
  77             }
  78         } catch (ClassNotFoundException e) {
  79             throw new RuntimeException("Database driver class could not be loaded\n" + "Exception: " + e.toString(),e);
  80         } catch(SQLException e) {
  81             throw new RuntimeException("Problems with database connection\n" + "Exception: " + e.toString(), e);
  82         }
  83         return null;
  84     }
  85
  86     @Override
  87     public boolean testConnection() throws ClassNotFoundException, SQLException {
  88
  89         IDatabaseType dbType = getDatabaseType().getDatabaseType();
  90         String classString = dbType.getClassString();
  91         Class.forName(classString);
  92         String mUrl = dbType.getConnectionString(this);
  93         if(logger.isDebugEnabled()){
  94             logger.debug("testConnection() : " + mUrl);
  95         }
  96
  97         if(logger.isDebugEnabled()){
  98             logger.debug("testConnection() : " + mUrl + " : service is available");
  99         }
 100         // try to connect to the database server
 101         Connection connection = DriverManager.getConnection(mUrl, getUsername(), getPassword());
 102         if (connection != null){
 103             if(logger.isDebugEnabled()){
 104                 logger.debug("testConnection() : " + mUrl + " : jdbc connect successful");
 105             }
 106             return true;
 107         }
 108
 109         if(logger.isDebugEnabled()){
 110             logger.debug("testConnection() : " + mUrl + " : FAIL");
 111         }
 112         return false;
 113     }
 114
 115         @Override
 116         public boolean checkConnection() throws CdmSourceException {
 117                 try {
 118                         return testConnection();
 119                 } catch (ClassNotFoundException e) {
 120                         throw new CdmSourceException(e.getMessage());
 121                 } catch (SQLException e) {
 122                         throw new CdmSourceException(e.getMessage());
 123                 }
 124         }
 125
 126         @Override
 127         public String getConnectionMessage() {
 128                 String message = "";
 129                 if (getDatabaseType().equals(DatabaseTypeEnum.H2)) {
 130                         message = " local CDM Store ";
 131                 } else {
 132                         message = " CDM Community Store ";
 133                 }
 134                 message += "'" + getName() + "'";
 135
 136                 message = "Connecting to" + message + ".";
 137
 138                 return message;
 139         }
 140
 141     @Override
 142     public Object getSingleValue(String query) throws SQLException{
 143         String queryString = query == null? "(null)": query;
 144         ResultSet resultSet = executeQuery(query);
 145         if (resultSet == null || resultSet.next() == false){
 146             logger.info("No record returned for query " +  queryString);
 147             return null;
 148         }
 149         if (resultSet.getMetaData().getColumnCount() != 1){
 150             logger.info("More than one column selected in query" +  queryString);
 151             //first value will be taken
 152         }
 153         Object object = resultSet.getObject(1);
 154         if (resultSet.next()){
 155             logger.info("Multiple results for query " +  queryString);
 156             //first row will be taken
 157         }
 158         return object;
 159     }
 160
 161         @Override
 162         public String getDbSchemaVersion() throws CdmSourceException  {
 163                 try {
 164                         return (String)getSingleValue(CdmMetaDataPropertyName.DB_SCHEMA_VERSION.getSqlQuery());
 165                 } catch (SQLException e) {
 166                     try {
 167                     return (String)getSingleValue(CdmMetaDataPropertyName.DB_SCHEMA_VERSION.getSqlQueryOld());
 168                 } catch (SQLException e1) {
 169                     throw new CdmSourceException(e1.getMessage());
 170                 }
 171                 }
 172         }
 173
 174         @Override
 175         public boolean isDbEmpty() throws CdmSourceException {
 176                 // Any CDM DB should have a schema version
 177                 String dbSchemaVersion = getDbSchemaVersion();
 178
 179                 return (dbSchemaVersion == null || dbSchemaVersion.equals(""));
 180         }
 181     /**
 182      * Executes a query and returns the ResultSet.
 183      * @return ResultSet for the query.
 184      * @throws SQLException
 185      */
 186     @Override
 187     public ResultSet executeQuery (String query) throws SQLException {
 188
 189         ResultSet resultSet;
 190
 191         if (query == null){
 192             return null;
 193         }
 194         Connection connection = getConnection();
 195         if (connection != null){
 196             Statement statement = connection.createStatement();
 197             resultSet = statement.executeQuery(query);
 198         }else{
 199             throw new RuntimeException("Could not establish connection to database");
 200         }
 201         return resultSet;
 202
 203     }
 204
 205     @Override
 206     public int executeUpdate (String sqlUpdate) throws SQLException{
 207
 208         int result;
 209         Connection connection = null;
 210         try {
 211             if (sqlUpdate == null){
 212                 return 0;
 213             }
 214             connection = getConnection();
 215             Statement statement = connection.createStatement();
 216             result = statement.executeUpdate(sqlUpdate);
 217             return result;
 218         } catch(SQLException e) {
 219             try{
 220                 if (connection != null && ! connection.getAutoCommit()){
 221                     connection.rollback();
 222                 }
 223             }catch (SQLException ex){
 224                 //do nothing -  maybe throw RuntimeException in future
 225                 throw new RuntimeException(ex);
 226             }
 227             logger.error("Problems when executing update\n  " + sqlUpdate + " \n" + "Exception: " + e);
 228             throw e;
 229         }
 230     }
 231
 232     @Override
 233     public void startTransaction() {
 234         try {
 235             Connection connection = getConnection();
 236             this.connection = connection;
 237             connection.setAutoCommit(false);
 238             return;
 239         } catch(SQLException e) {
 240             logger.error("Problems when starting transaction \n" + "Exception: " + e);
 241             return;
 242         }
 243     }
 244
 245     @Override
 246     public void commitTransaction() throws SQLException {
 247         try {
 248             Connection connection = getConnection();
 249             connection.commit();
 250         } catch(SQLException e) {
 251             logger.error("Problems when commiting transaction \n" + "Exception: " + e);
 252             throw e;
 253         }
 254     }
 255
 256     @Override
 257     public void rollback() throws SQLException {
 258         try {
 259             Connection connection = getConnection();
 260             connection.rollback();
 261         } catch(SQLException e) {
 262             logger.error("Problems when rolling back transaction \n" + "Exception: " + e);
 263             throw e;
 264         }
 265     }
 266
 267
 268     @Override
 269     public DatabaseMetaData getMetaData() {
 270         Connection connection = null;
 271         try {
 272             connection = getConnection();
 273             return connection.getMetaData();
 274         } catch (SQLException e) {
 275             logger.error("Could not get metadata for datasource", e);
 276             return null;
 277         }
 278     }
 279
 280     @Override
 281     public void closeOpenConnections() {
 282         try {
 283             if(connection != null && !connection.isClosed()){
 284                 connection.close();
 285                 connection = null;
 286             }
 287         } catch (SQLException e) {
 288             logger.error("Error closing the connection");
 289         }
 290     }
 291
 292
 293     @Override
 294     public Map<CdmMetaDataPropertyName, String> getMetaDataMap() throws CdmSourceException {
 295                 Map<CdmMetaDataPropertyName, String> cdmMetaDataMap = new HashMap<>();
 296
 297                 for(CdmMetaDataPropertyName mdpn : CdmMetaDataPropertyName.values()) {
 298                         String value = null;
 299                         try {
 300                                 value = (String)getSingleValue(mdpn.getSqlQuery());
 301                         } catch (SQLException e1) {
 302                             try {
 303                         value = (String)getSingleValue(mdpn.getSqlQueryOld());
 304                     } catch (SQLException e) {
 305                         throw new CdmSourceException(this.toString(), e.getMessage());
 306                     }
 307                         }
 308                         if(value != null) {
 309                                 cdmMetaDataMap.put(mdpn, value);
 310                         }
 311                 }
 312                 return cdmMetaDataMap;
 313     }
 314
 315     // ************ javax.sql.DataSource base interfaces ********************/
 316
 317
 318     @Override
 319     public PrintWriter getLogWriter() throws SQLException {
 320         //implementations copied from org.springframework.jdbc.datasource.AbstractDataSource;
 321         throw new UnsupportedOperationException("getLogWriter");
 322     }
 323
 324
 325     @Override
 326     public void setLogWriter(PrintWriter out) throws SQLException {
 327         //implementations copied from org.springframework.jdbc.datasource.AbstractDataSource;
 328         throw new UnsupportedOperationException("setLogWriter");
 329     }
 330
 331
 332     @Override
 333     public void setLoginTimeout(int seconds) throws SQLException {
 334         //implementations copied from org.springframework.jdbc.datasource.AbstractDataSource;
 335         throw new UnsupportedOperationException("setLoginTimeout");
 336     }
 337
 338
 339     @Override
 340     public int getLoginTimeout() throws SQLException {
 341         //implementations copied from org.springframework.jdbc.datasource.AbstractDataSource;
 342         return 0;
 343     }
 344
 345
 346     /*
 347      * This is a preliminary implementation to be compliant with
 348      * java.sql.Datasource (1.6). It may not be fully working.
 349      * Please let the developers know if this doesn't work.
 350      */
 351
 352     //---------------------------------------------------------------------
 353     // Implementation of JDBC 4.0's Wrapper interface
 354     //---------------------------------------------------------------------
 355
 356     @Override
 357     @SuppressWarnings("unchecked")
 358     public <T> T unwrap(Class<T> iface) throws SQLException {
 359         if (iface.isInstance(this)) {
 360             return (T) this;
 361         }
 362         throw new SQLException("DataSource of type [" + getClass().getName() +
 363                 "] cannot be unwrapped as [" + iface.getName() + "]");
 364     }
 365
 366     @Override
 367     public boolean isWrapperFor(Class<?> iface) throws SQLException {
 368         return iface.isInstance(this);
 369     }
 370
 371
 372     //---------------------------------------------------------------------
 373     // Implementation of JDBC 4.1's getParentLogger method
 374     // Required in Java >=7.x
 375     // must not have the @Override annotation for compatibility with
 376     // java 1.6
 377     //---------------------------------------------------------------------
 378     @Override
 379     public java.util.logging.Logger getParentLogger() {
 380         //copied from org.springframework.jdbc.datasource.AbstractDataSource, not checked if this is correct
 381         return java.util.logging.Logger.getLogger(java.util.logging.Logger.GLOBAL_LOGGER_NAME);
 382     }
 383
 384     //********************* Base methods for CdmDataSource and CdmPersistentDatasource
 385
 386     protected AbstractBeanDefinition makeHibernatePropertiesBean(DatabaseTypeEnum dbType,
 387             DbSchemaValidation hbm2dll, boolean showSql,
 388             boolean formatSql, boolean registerAuditing, boolean registerSearchListener,
 389             Class<? extends RegionFactory> cacheProviderClass,
 390             String byteCodeProvider) {
 391
 392         AbstractBeanDefinition bd = new RootBeanDefinition(PropertiesFactoryBean.class);
 393         MutablePropertyValues hibernateProps = new MutablePropertyValues();
 394
 395         Properties props = new Properties();
 396         props.setProperty("hibernate.hbm2ddl.auto", hbm2dll.toString());
 397         props.setProperty("hibernate.dialect", dbType.getHibernateDialectCanonicalName());
 398 //      OLD:props.setProperty("hibernate.cache.provider_class", cacheProviderClass.getName());
 399         props.setProperty(HibernateConfiguration.CACHE_PROVIDER_CLASS, cacheProviderClass.getName());
 400         props.setProperty(HibernateConfiguration.SHOW_SQL, String.valueOf(showSql));
 401         props.setProperty(HibernateConfiguration.FORMAT_SQL, String.valueOf(formatSql));
 402         props.setProperty(HibernateConfiguration.REGISTER_SEARCH, String.valueOf(registerSearchListener));
 403         props.setProperty(EnversIntegrator.AUTO_REGISTER, String.valueOf(registerAuditing));
 404         props.setProperty(HibernateConfiguration.BYTECODE_PROVIDER, byteCodeProvider);
 405
 406         hibernateProps.add("properties",props);
 407         bd.setPropertyValues(hibernateProps);
 408         return bd;
 409
 410     }
 411
 412
 413 }