archive/net.sourceforge.phpeclipse.quantum.sql/src/com/quantum/sql/MultiSQLServer.java

   1 package com.quantum.sql;
   2
   3 import java.sql.Connection;
   4 import java.sql.DatabaseMetaData;
   5 import java.sql.Driver;
   6 import java.sql.ResultSet;
   7 import java.sql.SQLException;
   8 import java.sql.Statement;
   9 import java.util.Properties;
  10
  11 import com.quantum.Messages;
  12 import com.quantum.adapters.AdapterFactory;
  13 import com.quantum.adapters.DatabaseAdapter;
  14 import com.quantum.model.Bookmark;
  15 import com.quantum.model.ConnectionException;
  16 import com.quantum.model.Entity;
  17 import com.quantum.model.JDBCDriver;
  18 import com.quantum.model.PasswordFinder;
  19 import com.quantum.util.sql.SQLInstructionBuilder;
  20 import com.quantum.view.LogProxy;
  21
  22
  23 /**
  24  * MultiSQLServer is a Singleton, used  as a interface with the sql drivers.
  25  * Use MultiSQLServer.getInstance() to get the object.
  26  */
  27 public class MultiSQLServer implements ConnectionEstablisher {
  28     public static final String USERNAME = "user"; //$NON-NLS-1$
  29     public static final String PASSWORD = "password"; //$NON-NLS-1$
  30     private static MultiSQLServer instance = null;
  31
  32     private MultiSQLServer() {
  33     }
  34     public synchronized static MultiSQLServer getInstance() {
  35         if (instance == null) {
  36             instance = new MultiSQLServer();
  37         }
  38         return instance;
  39     }
  40
  41     public void commit(Connection con) throws SQLException {
  42         LogProxy log = LogProxy.getInstance();
  43         try {
  44             con.commit();
  45         } catch (SQLException e) {
  46             log.addText(LogProxy.ERROR, "Error commiting: " + e, e); //$NON-NLS-1$
  47             throw e;
  48         }
  49     }
  50
  51     public void rollback(Connection con) throws SQLException {
  52         LogProxy log = LogProxy.getInstance();
  53         try {
  54             con.rollback();
  55         } catch (SQLException e) {
  56             log.addText(LogProxy.ERROR, "Error rolling back: " + e, e); //$NON-NLS-1$
  57             throw e;
  58         }
  59     }
  60
  61     public void setAutoCommit(Connection con, boolean enabled) {
  62         LogProxy log = LogProxy.getInstance();
  63         try {
  64             if (con != null) {
  65                 con.setAutoCommit(enabled);
  66             } else {
  67                 log.addText(LogProxy.ERROR, "Please connect before setting autocommit"); //$NON-NLS-1$
  68             }
  69         } catch (SQLException e) {
  70             log.addText(LogProxy.ERROR, "Error setting autocommit: " + e, e); //$NON-NLS-1$
  71         }
  72     }
  73
  74     public void disconnect(Connection connection) throws SQLException {
  75         if (connection != null) {
  76             connection.close();
  77         }
  78     }
  79
  80     /**
  81      * Makes a connection to a JDBC driver based on the data from a bookmark
  82      * @param bookmark -
  83      *     The Bookmark with the data needed to make the connection
  84      * @param passwordFinder -
  85      *     A utility class that can be invoked if the bookmark does not
  86      *     include a password
  87      * @return The Connection object if everything went OK
  88      */
  89     public Connection connect(Bookmark bookmark, PasswordFinder passwordFinder)
  90         throws ConnectionException {
  91
  92         String password = bookmark.getPassword();
  93         if (bookmark.getPromptForPassword()) {
  94             password = passwordFinder.getPassword();
  95             if (passwordFinder.isPasswordMeantToBeSaved()) {
  96                 bookmark.setPassword(password);
  97             }
  98         }
  99
 100         if (password != null) {
 101             Connection connection = connect(bookmark, password);
 102             if (connection != null) {
 103                         // Set the autoCommit state of the bookmark to the default on new connections
 104                                 bookmark.setAutoCommit(bookmark.getDefaultAutoCommit());
 105                                 // Set the autoCommit state of the JDBC connection to the bookmark autoCommit statec
 106                         setAutoCommit(connection, bookmark.isAutoCommit());
 107             }
 108                 return connection;
 109         } else {
 110             return null;
 111         }
 112
 113     }
 114     private Connection connect(Bookmark bookmark, String password)
 115         throws ConnectionException {
 116         LogProxy log = LogProxy.getInstance();
 117         log.addText(LogProxy.QUERY, "Connecting to: " + bookmark.getName()); //$NON-NLS-1$
 118         try {
 119                 JDBCDriver jdbcDriver = bookmark.getJDBCDriver();
 120                 Driver driver = jdbcDriver.getDriver();
 121             if (driver != null) {
 122                     Properties props = new Properties();
 123                     props.put(USERNAME, bookmark.getUsername());
 124                     props.put(PASSWORD, password);
 125                     // TODO: This kind of things should really be made general-purpose
 126                     if (jdbcDriver.getType().equals(AdapterFactory.ORACLE)){
 127                         // remarksReporting will make the JDBC driver return the remarks for the tables and
 128                         // the columns. It'll make getting the tables and columns much slower, so it should
 129                         // really be made into an option
 130                         // TODO: Make remark reporting into an option
 131                         props.put("remarksReporting", "true");
 132                         // includeSynonyms will make the JDBC driver return the proper columns when querying
 133                         // about a synonym. If not given, synonyms will appear with no columns, exports of data
 134                         // containing synonyms will break, etc.n So it's needed from the moment you add the synonyms
 135                         // with the getSynonymsList() in the Database.getEntities() function. That could also be
 136                         // made into an option, but more logically when more databases are addedd
 137                         // TODO: Make including synonyms into an option
 138                         props.put("includeSynonyms", "true");
 139                     }
 140                     Connection connection =
 141                         driver.connect(bookmark.getConnect(), props);
 142                     if (connection == null) {
 143                         throw new ConnectionException("Error: Driver returned a null connection: " + bookmark.toString()); //$NON-NLS-1$
 144                     }
 145
 146                     DatabaseMetaData metaData = connection.getMetaData();
 147                     jdbcDriver.setName(metaData.getDriverName());
 148                     jdbcDriver.setVersion(metaData.getDriverVersion());
 149                     log.addText(LogProxy.RESULTS, "Connected to: " + bookmark.getName()); //$NON-NLS-1$
 150                     System.out.println("Connected"); //$NON-NLS-1$
 151                     return connection;
 152             } else {
 153                 throw new ConnectionException(Messages.getString(
 154                                 ConnectionException.class, "couldNotInstantiateDriver",
 155                                                 new Object[] { jdbcDriver.getClassName(), bookmark.getName() }));
 156             }
 157         } catch (SQLException e) {
 158             throw new ConnectionException(e);
 159         }
 160     }
 161         public SQLResults execute(Bookmark bookmark, Connection con, Entity entity, String s)
 162                         throws SQLException {
 163                 return execute(bookmark, con, entity, s, 200);
 164         }
 165
 166         public SQLResults execute(Bookmark bookmark, Connection con, String s)
 167                         throws SQLException {
 168                 return execute(bookmark, con, null, s, 200);
 169         }
 170
 171         public SQLResultSetResults getMetaData(Entity entity, Connection connection) throws SQLException {
 172                 String query = SQLInstructionBuilder.buildSelectAllColumnsNoRows(entity);
 173                 SQLResultSetResults results = null;
 174                 if (connection != null) {
 175                         Statement statement = connection.createStatement();
 176                         try {
 177                                 ResultSet set = statement.executeQuery(query);
 178                                 try {
 179                                         results = SQLMetaDataResults.create(entity.getBookmark(), set, query, entity);
 180                                 } finally {
 181                                         set.close();
 182                                 }
 183                         } finally {
 184                                 statement.close();
 185                         }
 186                 }
 187                 return results;
 188         }
 189
 190         public SQLResults execute(Bookmark bookmark, Connection con, String sql,
 191                         int numberOfRowsPerPage) throws SQLException {
 192                 return execute(bookmark, con, null, sql, numberOfRowsPerPage);
 193         }
 194
 195
 196         public SQLResults execute(
 197                         Bookmark bookmark,
 198                         Connection con,
 199                         Entity entity,
 200                         String sql,
 201                         int numberOfRowsPerPage)
 202                         throws SQLException {
 203
 204                 long startTime = System.currentTimeMillis();
 205                 System.out.println("Executing"); //$NON-NLS-1$
 206                 LogProxy log = LogProxy.getInstance();
 207                 log.addText(LogProxy.QUERY, "SQL (" + bookmark.getName() + ") [" + sql + "]"); //$NON-NLS-1$ //$NON-NLS-2$
 208                 Statement statement = con.createStatement();
 209                 try {
 210                         SQLResults results;
 211                         if (statement.execute(sql)) {
 212                                 ResultSet set = statement.getResultSet();
 213                                 try {
 214                                         results = SQLStandardResultSetResults.create(set, bookmark, sql, entity, numberOfRowsPerPage);
 215                                 } finally {
 216                                         set.close();
 217                                 }
 218                         } else {
 219                                 int updates = statement.getUpdateCount();
 220                                 results = new SQLUpdateResults(updates);
 221                         }
 222                         log.addText(LogProxy.RESULTS, "Success: result set displayed"); //$NON-NLS-1$
 223                         if (results != null) {
 224                                 results.setTime(System.currentTimeMillis() - startTime);
 225                         }
 226                         return results;
 227                 } finally {
 228                         statement.close();
 229                 }
 230         }
 231
 232         public int getSize(Bookmark bookmark, Connection connection, String tableName, DatabaseAdapter adapter) throws SQLException {
 233             SQLResultSetResults results = (SQLResultSetResults) execute(
 234                     bookmark, connection, adapter.getCountQuery(tableName));
 235             if (results.getRowCount() > 0 && results.getColumnCount() > 0) {
 236                 return Integer.parseInt(results.getElement(1, 1).toString());
 237             } else {
 238                 return -1;
 239             }
 240         }
 241 }