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