new Preferences tree
[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 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 }