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.sql.Connection;
  13 import java.sql.DatabaseMetaData;
  14 import java.sql.DriverManager;
  15 import java.sql.ResultSet;
  16 import java.sql.SQLException;
  17 import java.sql.Statement;
  18
  19 import javax.sql.DataSource;
  20
  21 import org.apache.log4j.Logger;
  22
  23 import eu.etaxonomy.cdm.database.types.IDatabaseType;
  24
  25 /**
  26  * @author a.mueller
  27  * @created 18.12.2008
  28  * @veresultSetion 1.0
  29  */
  30 abstract class CdmDataSourceBase implements ICdmDataSource {
  31         private static final Logger logger = Logger.getLogger(CdmDataSourceBase.class);
  32
  33         private static final int TIMEOUT = 10;
  34         private Connection connection;
  35
  36
  37         public Connection getConnection() throws SQLException {
  38                 return getConnection(getUsername(), getPassword());
  39         }
  40
  41
  42         public Connection getConnection(String username, String password) throws SQLException {
  43                 try {
  44                         if(connection != null){
  45                                 boolean isValid = true;
  46 //                              try{
  47 //                                      isValid = connection.isValid(TIMEOUT);
  48 //                              } catch (java.lang.AbstractMethodError e){
  49 //                                      logger.error("Problems with Connection.isValid method\n" + "Exception: " + e.toString());
  50 //                              }
  51                                 if (isValid){
  52                                         return connection;
  53                                 }
  54                         }else{
  55                                 IDatabaseType dbType = getDatabaseType().getDatabaseType();
  56                                 String classString = dbType.getClassString();
  57                                 Class.forName(classString);
  58                                 String mUrl = dbType.getConnectionString(this);
  59                                 Connection connection = DriverManager.getConnection(mUrl, username, password);
  60                                 return  connection;
  61                         }
  62                 } catch (ClassNotFoundException e) {
  63                         logger.error("Database driver class could not be loaded\n" + "Exception: " + e.toString());
  64                 } catch(SQLException e) {
  65                         logger.error("Problems with database connection\n" + "Exception: " + e.toString());
  66                 }
  67                 return null;
  68         }
  69
  70
  71         /* (non-Javadoc)
  72          * @see eu.etaxonomy.cdm.database.ICdmDataSource#testConnection()
  73          */
  74         public boolean testConnection() throws ClassNotFoundException, SQLException {
  75
  76                 IDatabaseType dbType = getDatabaseType().getDatabaseType();
  77                 String classString = dbType.getClassString();
  78                 Class.forName(classString);
  79                 String mUrl = dbType.getConnectionString(this);
  80                 Connection connection = DriverManager.getConnection(mUrl, getUsername(), getPassword());
  81                 if (connection != null){
  82                         return true;
  83                 }
  84
  85                 return false;
  86         }
  87
  88         /* (non-Javadoc)
  89          * @see eu.etaxonomy.cdm.database.ICdmDataSource#getSingleValue(java.lang.String)
  90          */
  91         @Override
  92         public Object getSingleValue(String query) throws SQLException{
  93                 String queryString = query == null? "(null)": query;
  94                 ResultSet resultSet = executeQuery(query);
  95                 if (resultSet == null || resultSet.next() == false){
  96                         logger.warn("No record returned for query " +  queryString);
  97                         return null;
  98                 }
  99                 if (resultSet.getMetaData().getColumnCount() != 1){
 100                         logger.warn("More than one column selected in query" +  queryString);
 101                         return null;
 102                 }
 103                 Object object = resultSet.getObject(1);
 104                 if (resultSet.next()){
 105                         logger.warn("Multiple results for query " +  queryString);
 106                         return null;
 107                 }
 108                 return object;
 109         }
 110
 111
 112     /**
 113      * Executes a query and returns the ResultSet.
 114      * @return ResultSet for the query.
 115      * @throws SQLException
 116      */
 117         @Override
 118         public ResultSet executeQuery (String query) throws SQLException {
 119
 120                 ResultSet resultSet;
 121
 122                 if (query == null){
 123                         return null;
 124                 }
 125                 Connection connection = getConnection();
 126                 if (connection != null){
 127                         Statement statement = connection.createStatement();
 128                         resultSet = statement.executeQuery(query);
 129                 }else{
 130                         throw new RuntimeException("Could not establish connection to database");
 131                 }
 132                 return resultSet;
 133
 134         }
 135
 136
 137         /* (non-Javadoc)
 138          * @see eu.etaxonomy.cdm.database.ICdmDataSource#executeUpdate(java.lang.String)
 139          */
 140         @Override
 141         public int executeUpdate (String sqlUpdate) throws SQLException{
 142
 143                 int result;
 144                 Connection connection = null;
 145                 try {
 146                         if (sqlUpdate == null){
 147                                 return 0;
 148                         }
 149                         connection = getConnection();
 150                         Statement statement = connection.createStatement();
 151                         result = statement.executeUpdate(sqlUpdate);
 152                         return result;
 153                 } catch(SQLException e) {
 154                         try{
 155                                 if (! connection.getAutoCommit()){
 156                                         connection.rollback();
 157                                 }
 158                         }catch (SQLException ex){
 159                                 //do nothing -  maybe throw RuntimeException in future
 160                                 throw new RuntimeException(ex);
 161                         }
 162                         logger.error("Problems when executing update\n  " + sqlUpdate + " \n" + "Exception: " + e);
 163                         throw e;
 164                 }
 165         }
 166
 167
 168         /* (non-Javadoc)
 169          * @see eu.etaxonomy.cdm.database.ICdmDataSource#startTransaction()
 170          */
 171         @Override
 172         public void startTransaction() {
 173                 try {
 174                         Connection connection = getConnection();
 175                         connection.setAutoCommit(false);
 176                         this.connection = connection;
 177                 return;
 178                 } catch(SQLException e) {
 179                         logger.error("Problems when starting transaction \n" + "Exception: " + e);
 180                         return;
 181                 }
 182         }
 183
 184         /* (non-Javadoc)
 185          * @see eu.etaxonomy.cdm.database.ICdmDataSource#commitTransaction()
 186          */
 187         @Override
 188         public void commitTransaction() throws SQLException {
 189                 try {
 190                         Connection connection = getConnection();
 191                         connection.commit();
 192             } catch(SQLException e) {
 193                         logger.error("Problems when commiting transaction \n" + "Exception: " + e);
 194                         throw e;
 195                 }
 196         }
 197
 198         @Override
 199         public void rollback() throws SQLException {
 200                 try {
 201                         Connection connection = getConnection();
 202                         connection.rollback();
 203             } catch(SQLException e) {
 204                         logger.error("Problems when rolling back transaction \n" + "Exception: " + e);
 205                         throw e;
 206                 }
 207         }
 208
 209         /* (non-Javadoc)
 210          * @see eu.etaxonomy.cdm.database.ICdmDataSource#getMetaData()
 211          */
 212         @Override
 213         public DatabaseMetaData getMetaData() {
 214                 Connection connection = null;
 215                 try {
 216                         connection = getConnection();
 217                         return connection.getMetaData();
 218                 } catch (SQLException e) {
 219                         logger.error("Could not get metadata for datasource", e);
 220                         return null;
 221                 }
 222         }
 223
 224         /* (non-Javadoc)
 225          * @see eu.etaxonomy.cdm.database.ICdmDataSource#closeOpenConnections()
 226          */
 227         @Override
 228         public void closeOpenConnections() {
 229                 try {
 230                         if(connection != null && !connection.isClosed()){
 231                                 connection.close();
 232                                 connection = null;
 233                         }
 234                 } catch (SQLException e) {
 235                         logger.error("Error closing the connection");
 236                 }
 237         }
 238 }