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 SQLException {
  73         if (connection != null) {
  74             connection.close();
  75         }
  76     }
  77
  78     /**
  79      * Makes a connection to a JDBC driver based on the data from a bookmark
  80      * @param bookmark -
  81      *     The Bookmark with the data needed to make the connection
  82      * @param passwordFinder -
  83      *     A utility class that can be invoked if the bookmark does not
  84      *     include a password
  85      * @return The Connection object if everything went OK
  86      */
  87     public Connection connect(Bookmark bookmark, PasswordFinder passwordFinder)
  88         throws ConnectionException {
  89
  90         String password = bookmark.getPassword();
  91         if (bookmark.getPromptForPassword()) {
  92             password = passwordFinder.getPassword();
  93             if (passwordFinder.isPasswordMeantToBeSaved()) {
  94                 bookmark.setPassword(password);
  95             }
  96         }
  97
  98         if (password != null) {
  99             Connection connection = connect(bookmark, password);
 100             if (connection != null) {
 101                         // Set the autoCommit state of the bookmark to the default on new connections
 102                                 bookmark.setAutoCommit(bookmark.getDefaultAutoCommit());
 103                                 // Set the autoCommit state of the JDBC connection to the bookmark autoCommit statec
 104                         setAutoCommit(connection, bookmark.isAutoCommit());
 105             }
 106                 return connection;
 107         } else {
 108             return null;
 109         }
 110
 111     }
 112     private Connection connect(Bookmark bookmark, String password)
 113         throws ConnectionException {
 114         LogProxy log = LogProxy.getInstance();
 115         log.addText(LogProxy.QUERY, "Connecting to: " + bookmark.getName()); //$NON-NLS-1$
 116         try {
 117                 JDBCDriver jdbcDriver = bookmark.getJDBCDriver();
 118             Driver driver = jdbcDriver.getDriver();
 119             if (driver != null) {
 120                     Properties props = new Properties();
 121                     props.put(USERNAME, bookmark.getUsername());
 122                     props.put(PASSWORD, password);
 123                     Connection connection =
 124                         driver.connect(bookmark.getConnect(), props);
 125                     if (connection == null) {
 126                         throw new ConnectionException("Error: Driver returned a null connection: " + bookmark.toString()); //$NON-NLS-1$
 127                     }
 128
 129                     DatabaseMetaData metaData = connection.getMetaData();
 130                     jdbcDriver.setName(metaData.getDriverName());
 131                     jdbcDriver.setVersion(metaData.getDriverVersion());
 132                     log.addText(LogProxy.RESULTS, "Connected to: " + bookmark.getName()); //$NON-NLS-1$
 133                     System.out.println("Connected"); //$NON-NLS-1$
 134                     return connection;
 135             } else {
 136                 throw new ConnectionException(Messages.getString(
 137                                 ConnectionException.class, "couldNotInstaniateDriver",
 138                                                 new Object[] { jdbcDriver.getClassName(), bookmark.getName() }));
 139             }
 140         } catch (SQLException e) {
 141             throw new ConnectionException(e);
 142         }
 143     }
 144         public SQLResults execute(Bookmark bookmark, Connection con, Entity entity, String s)
 145                         throws SQLException {
 146                 return execute(bookmark, con, entity, s, 200);
 147         }
 148
 149         public SQLResults execute(Bookmark bookmark, Connection con, String s)
 150                         throws SQLException {
 151                 return execute(bookmark, con, null, s, 200);
 152         }
 153
 154         public SQLResultSetResults getMetaData(Entity entity, Connection connection) throws SQLException {
 155                 String query = "SELECT * FROM " + entity.getQuotedTableName() + " WHERE (1 = 0)"; //$NON-NLS-1$ //$NON-NLS-2$
 156                 SQLResultSetResults results = null;
 157                 if (connection != null) {
 158                         Statement statement = connection.createStatement();
 159                         try {
 160                                 ResultSet set = statement.executeQuery(query);
 161                                 try {
 162                                         results = SQLMetaDataResults.create(entity.getBookmark(), set, query, entity);
 163                                 } finally {
 164                                         set.close();
 165                                 }
 166                         } finally {
 167                                 statement.close();
 168                         }
 169                 }
 170                 return results;
 171         }
 172
 173         public SQLResults execute(Bookmark bookmark, Connection con, String sql,
 174                         int numberOfRowsPerPage) throws SQLException {
 175                 return execute(bookmark, con, null, sql, numberOfRowsPerPage);
 176         }
 177
 178
 179         public SQLResults execute(
 180                         Bookmark bookmark,
 181                         Connection con,
 182                         Entity entity,
 183                         String sql,
 184                         int numberOfRowsPerPage)
 185                         throws SQLException {
 186
 187                 long startTime = System.currentTimeMillis();
 188                 System.out.println("Executing"); //$NON-NLS-1$
 189                 LogProxy log = LogProxy.getInstance();
 190                 log.addText(LogProxy.QUERY, "Executing Request [" + sql + "]"); //$NON-NLS-1$ //$NON-NLS-2$
 191                 Statement statement = con.createStatement();
 192                 try {
 193                         SQLResults results;
 194                         if (statement.execute(sql)) {
 195                                 ResultSet set = statement.getResultSet();
 196                                 try {
 197                                         results = SQLStandardResultSetResults.create(set, bookmark, sql, entity, numberOfRowsPerPage);
 198                                 } finally {
 199                                         set.close();
 200                                 }
 201                         } else {
 202                                 int updates = statement.getUpdateCount();
 203                                 results = new SQLUpdateResults(updates);
 204                         }
 205                         log.addText(LogProxy.RESULTS, "Success: result set displayed"); //$NON-NLS-1$
 206                         if (results != null) {
 207                                 results.setTime(System.currentTimeMillis() - startTime);
 208                         }
 209                         return results;
 210                 } finally {
 211                         statement.close();
 212                 }
 213         }
 214
 215         public int getSize(Bookmark bookmark, Connection connection, String tableName, DatabaseAdapter adapter) throws SQLException {
 216             SQLResultSetResults results = (SQLResultSetResults) execute(
 217                     bookmark, connection, adapter.getCountQuery(tableName));
 218             if (results.getRowCount() > 0 && results.getColumnCount() > 0) {
 219                 return Integer.parseInt(results.getElement(1, 1).toString());
 220             } else {
 221                 return -1;
 222             }
 223         }
 224 }