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.DatabaseAdapter;
  13 import com.quantum.model.Bookmark;
  14 import com.quantum.model.ConnectionException;
  15 import com.quantum.model.Entity;
  16 import com.quantum.model.JDBCDriver;
  17 import com.quantum.model.PasswordFinder;
  18 import com.quantum.view.LogProxy;
  19
  20
  21 /**
  22  * MultiSQLServer is a Singleton, used  as a interface with the sql drivers.
  23  * Use MultiSQLServer.getInstance() to get the object.
  24  */
  25 public class MultiSQLServer implements ConnectionEstablisher {
  26     public static final String USERNAME = "user"; //$NON-NLS-1$
  27     public static final String PASSWORD = "password"; //$NON-NLS-1$
  28     private static MultiSQLServer instance = null;
  29
  30     private MultiSQLServer() {
  31     }
  32     public synchronized static MultiSQLServer getInstance() {
  33         if (instance == null) {
  34             instance = new MultiSQLServer();
  35         }
  36         return instance;
  37     }
  38
  39     public void commit(Connection con) throws SQLException {
  40         LogProxy log = LogProxy.getInstance();
  41         try {
  42             con.commit();
  43         } catch (SQLException e) {
  44             log.addText(LogProxy.ERROR, "Error commiting: " + e, e); //$NON-NLS-1$
  45             throw e;
  46         }
  47     }
  48
  49     public void rollback(Connection con) throws SQLException {
  50         LogProxy log = LogProxy.getInstance();
  51         try {
  52             con.rollback();
  53         } catch (SQLException e) {
  54             log.addText(LogProxy.ERROR, "Error rolling back: " + e, e); //$NON-NLS-1$
  55             throw e;
  56         }
  57     }
  58
  59     public void setAutoCommit(Connection con, boolean enabled) {
  60         LogProxy log = LogProxy.getInstance();
  61         try {
  62             if (con != null) {
  63                 con.setAutoCommit(enabled);
  64             } else {
  65                 log.addText(LogProxy.ERROR, "Please connect before setting autocommit"); //$NON-NLS-1$
  66             }
  67         } catch (SQLException e) {
  68             log.addText(LogProxy.ERROR, "Error setting autocommit: " + e, e); //$NON-NLS-1$
  69         }
  70     }
  71
  72     public void disconnect(Connection connection) throws ConnectionException {
  73         try {
  74             if (connection != null) {
  75                 connection.close();
  76             }
  77         } catch (SQLException e) {
  78             throw new ConnectionException(e);
  79         }
  80     }
  81
  82     /**
  83      * Makes a connection to a JDBC driver based on the data from a bookmark
  84      * @param bookmark -
  85      *     The Bookmark with the data needed to make the connection
  86      * @param passwordFinder -
  87      *     A utility class that can be invoked if the bookmark does not
  88      *     include a password
  89      * @return The Connection object if everything went OK
  90      */
  91     public Connection connect(Bookmark bookmark, PasswordFinder passwordFinder)
  92         throws ConnectionException {
  93
  94         String password = bookmark.getPassword();
  95         if (bookmark.getPromptForPassword()) {
  96             password = passwordFinder.getPassword();
  97             if (passwordFinder.isPasswordMeantToBeSaved()) {
  98                 bookmark.setPassword(password);
  99             }
 100         }
 101
 102         if (password != null) {
 103             Connection connection = connect(bookmark, password);
 104             if (connection != null) {
 105                         // Set the autoCommit state of the bookmark to the default on new connections
 106                                 bookmark.setAutoCommit(bookmark.getDefaultAutoCommit());
 107                                 // Set the autoCommit state of the JDBC connection to the bookmark autoCommit statec
 108                         setAutoCommit(connection, bookmark.isAutoCommit());
 109             }
 110                 return connection;
 111         } else {
 112             return null;
 113         }
 114
 115     }
 116     private Connection connect(Bookmark bookmark, String password)
 117         throws ConnectionException {
 118         LogProxy log = LogProxy.getInstance();
 119         log.addText(LogProxy.QUERY, "Connecting to: " + bookmark.getName()); //$NON-NLS-1$
 120         try {
 121                 JDBCDriver jdbcDriver = bookmark.getJDBCDriver();
 122             Driver driver = jdbcDriver.getDriver();
 123             if (driver != null) {
 124                     Properties props = new Properties();
 125                     props.put(USERNAME, bookmark.getUsername());
 126                     props.put(PASSWORD, password);
 127                     Connection connection =
 128                         driver.connect(bookmark.getConnect(), props);
 129                     if (connection == null) {
 130                         throw new ConnectionException("Error: Driver returned a null connection: " + bookmark.toString()); //$NON-NLS-1$
 131                     }
 132
 133                     DatabaseMetaData metaData = connection.getMetaData();
 134                     jdbcDriver.setName(metaData.getDriverName());
 135                     jdbcDriver.setVersion(metaData.getDriverVersion());
 136                     log.addText(LogProxy.RESULTS, "Connected to: " + bookmark.getName()); //$NON-NLS-1$
 137                     System.out.println("Connected"); //$NON-NLS-1$
 138                     return connection;
 139             } else {
 140                 throw new ConnectionException(Messages.getString(
 141                                 ConnectionException.class, "couldNotInstaniateDriver",
 142                                                 new Object[] { jdbcDriver.getClassName(), bookmark.getName() }));
 143             }
 144         } catch (SQLException e) {
 145             throw new ConnectionException(e);
 146         }
 147     }
 148         public SQLResults execute(Bookmark bookmark, Connection con, Entity entity, String s)
 149                         throws SQLException {
 150                 return execute(bookmark, con, entity, s, 200);
 151         }
 152
 153         public SQLResults execute(Bookmark bookmark, Connection con, String s)
 154                         throws SQLException {
 155                 return execute(bookmark, con, null, s, 200);
 156         }
 157
 158         public SQLResultSetResults getMetaData(Entity entity, Connection connection) throws SQLException {
 159                 String query = "SELECT * FROM " + entity.getQuotedTableName() + " WHERE (1 = 0)"; //$NON-NLS-1$ //$NON-NLS-2$
 160                 SQLResultSetResults results = null;
 161                 if (connection != null) {
 162                         Statement statement = connection.createStatement();
 163                         try {
 164                                 ResultSet set = statement.executeQuery(query);
 165                                 try {
 166                                         results = SQLMetaDataResults.create(entity.getBookmark(), set, query, entity);
 167                                 } finally {
 168                                         set.close();
 169                                 }
 170                         } finally {
 171                                 statement.close();
 172                         }
 173                 }
 174                 return results;
 175         }
 176
 177         public SQLResults execute(Bookmark bookmark, Connection con, String sql,
 178                         int numberOfRowsPerPage) throws SQLException {
 179                 return execute(bookmark, con, null, sql, numberOfRowsPerPage);
 180         }
 181
 182
 183         public SQLResults execute(
 184                         Bookmark bookmark,
 185                         Connection con,
 186                         Entity entity,
 187                         String sql,
 188                         int numberOfRowsPerPage)
 189                         throws SQLException {
 190
 191                 long startTime = System.currentTimeMillis();
 192                 System.out.println("Executing"); //$NON-NLS-1$
 193                 LogProxy log = LogProxy.getInstance();
 194                 log.addText(LogProxy.QUERY, "Executing Request [" + sql + "]"); //$NON-NLS-1$ //$NON-NLS-2$
 195                 Statement statement = con.createStatement();
 196                 try {
 197                         SQLResults results;
 198                         if (statement.execute(sql)) {
 199                                 ResultSet set = statement.getResultSet();
 200                                 try {
 201                                         results = SQLStandardResultSetResults.create(set, bookmark, sql, entity, numberOfRowsPerPage);
 202                                 } finally {
 203                                         set.close();
 204                                 }
 205                         } else {
 206                                 int updates = statement.getUpdateCount();
 207                                 results = new SQLUpdateResults(updates);
 208                         }
 209                         log.addText(LogProxy.RESULTS, "Success: result set displayed"); //$NON-NLS-1$
 210                         if (results != null) {
 211                                 results.setTime(System.currentTimeMillis() - startTime);
 212                         }
 213                         return results;
 214                 } finally {
 215                         statement.close();
 216                 }
 217         }
 218
 219         public int getSize(Bookmark bookmark, Connection connection, String tableName, DatabaseAdapter adapter) throws SQLException {
 220             SQLResultSetResults results = (SQLResultSetResults) execute(
 221                     bookmark, connection, adapter.getCountQuery(tableName));
 222             if (results.getRowCount() > 0 && results.getColumnCount() > 0) {
 223                 return Integer.parseInt(results.getElement(1, 1).toString());
 224             } else {
 225                 return -1;
 226             }
 227         }
 228 }