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