Quantum version 2.4.1
[phpeclipse.git] / 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 }