001: /*
002: * Copyright 2007 Roy van der Kuil (roy@vanderkuil.nl) and Stefan Rotman (stefan@rotman.net)
003: *
004: * Licensed under the Apache License, Version 2.0 (the "License");
005: * you may not use this file except in compliance with the License.
006: * You may obtain a copy of the License at
007: *
008: * http://www.apache.org/licenses/LICENSE-2.0
009: *
010: * Unless required by applicable law or agreed to in writing, software
011: * distributed under the License is distributed on an "AS IS" BASIS,
012: * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
013: * See the License for the specific language governing permissions and
014: * limitations under the License.
015: */
016: package nl.improved.sqlclient;
017:
018: import java.sql.Connection;
019: import java.sql.DatabaseMetaData;
020: import java.sql.DriverManager;
021: import java.sql.ResultSet;
022: import java.sql.SQLException;
023: import java.sql.Statement;
024: import java.sql.Types;
025: import java.text.DateFormat;
026: import java.text.SimpleDateFormat;
027: import java.util.Hashtable;
028: import java.util.Set;
029: import java.util.Map;
030: import java.util.Properties;
031: import jcurses.system.InputChar;
032: import jcurses.event.ActionEvent;
033: import jcurses.event.ActionListener;
034: import jcurses.widgets.Button;
035: import jcurses.widgets.Dialog;
036: import jcurses.widgets.GridLayoutManager;
037: import jcurses.widgets.Label;
038: import jcurses.widgets.PasswordField;
039: import jcurses.widgets.TextField;
040: import jcurses.widgets.WidgetsConstants;
041:
042: public final class DBConnector {
043:
044: /**
045: * The default formatting pattern for Date or Date-like columns.
046: */
047: private static final String DEFAULT_TIMESTAMP_PATTERN = "yyyy-MM-dd HH:mm:ss";
048: private static final String DEFAULT_DATE_PATTERN = "yyyy-MM-dd";
049: private static final String DEFAULT_TIME_PATTERN = "HH:mm:ss";
050:
051: private static DBConnector instance = null;
052: private boolean tableNamesUppercase = false;
053:
054: private Map<String, ConnectionSettings> predefinedConnections;
055: private String defaultConnector;
056:
057: private Connection activeConnection;
058: private Statement statement;
059: private boolean dateIsTimeStamp;
060:
061: private QueryExecutor queryExecutor;
062:
063: private DBConnector() {
064: predefinedConnections = new Hashtable<String, ConnectionSettings>();
065: Properties dbProperties = new Properties();
066: try {
067: dbProperties.load(getClass().getClassLoader()
068: .getResourceAsStream("./db.properties"));
069: } catch (Exception ioe) {
070: throw new IllegalStateException(
071: "Failed to initialize connectionproperties", ioe);
072: }
073:
074: for (String connector : dbProperties.getProperty("connectors",
075: "").split(" ")) {
076: ConnectionSettings cs = new ConnectionSettings(connector,
077: dbProperties.getProperty(connector + ".driver"),
078: dbProperties.getProperty(connector + ".url"));
079: cs.setUsername(dbProperties
080: .getProperty(connector + ".user"));
081: cs.setPassword(dbProperties.getProperty(connector
082: + ".password"));
083: predefinedConnections.put(connector, cs);
084: }
085: defaultConnector = dbProperties.getProperty("defaultConnector");
086: }
087:
088: public static synchronized DBConnector getInstance() {
089: if (instance == null) {
090: instance = new DBConnector();
091: }
092: return instance;
093: }
094:
095: public Connection connect() throws SQLException {
096: if (defaultConnector == null) {
097: throw new IllegalStateException(
098: "No default connection setting");
099: }
100: return connect(defaultConnector, null, null);
101: }
102:
103: public String translateDbVar(String name) {
104: if (name == null) {
105: return null;
106: }
107: if (tableNamesUppercase) {
108: return name.toUpperCase();
109: }
110: return name.toLowerCase();
111: }
112:
113: public boolean treatDateAsTimestamp() {
114: return dateIsTimeStamp;
115: }
116:
117: /**
118: * Return the used schema.
119: * NOTE: hack because Oracle uses as a schema the current username
120: * @return the used schema.
121: */
122: public String getSchema() throws SQLException {
123: if (getConnection().getMetaData().getDriverName().startsWith(
124: "Oracle JDBC driver")) {
125: return getConnection().getMetaData().getUserName();
126: }
127: return null;
128: }
129:
130: /**
131: * Connects to a database, using a connection string.
132: *
133: * Possible cases:
134: * <table>
135: * <tr>
136: * <td>"user/pass@ident"</td>
137: * <td>Connects to ident as user with password pass</td>
138: * </tr>
139: * <tr>
140: * <td>"user/@ident"</td>
141: * <td>Connects to ident connection as user with empty password</td>
142: * </tr>
143: * <tr>
144: * <td>"user@ident"</td>
145: * <td>Connects to ident connection as user, without specifying a password (will be prompted for pass)</td>
146: * </tr>
147: * <tr>
148: * <td>"@ident"</td>
149: * <td>Connects to ident connection, without specifying user or password. If default user is specified that will be used, if not: will be prompted for user/pass</td>
150: * </tr>
151: * <tr>
152: * <td>"user/pass"</td>
153: * <td>Connects to default connection as user, with password pass</td>
154: * </tr>
155: * <tr>
156: * <td>"user/"</td>
157: * <td>Connects to default connection as user, with empty password</td>
158: * </tr>
159: * <tr>
160: * <td>"user"</td>
161: * <td>Connects to default connection as user, without specifying a password (will be prompted for pass)</td>
162: * </tr>
163: * <tr>
164: * <td>""</td>
165: * <td>Connects to default connection, without specifying user or password. If default user is specified that will be used, if not: will be prompted for user/pass</td>
166: * </tr>
167: * <table>
168: *
169: * @param connectString the connect string, following the conditions as specified above.
170: * @return the opened Connection
171: * @throws SQLException if the connection could not be opened.
172: */
173: public Connection connect(String connectString) throws SQLException {
174: //Shortcut - no need to try and parse everything if we already know we're empty
175: if (connectString == null || connectString.trim().length() == 0) {
176: return connect(defaultConnector, null, null);
177: }
178:
179: String ident = defaultConnector;
180: String username = null;
181: String password = null;
182:
183: String[] connectParts = connectString.split("@");
184: if (connectParts.length > 1) {
185: ident = connectParts[1].trim();
186: }
187:
188: if (connectParts[0].matches(".+/.*")) {
189: String[] loginDetails = connectParts[0].split("/");
190: if (loginDetails.length > 1) {
191: password = loginDetails[1];
192: } else {
193: password = "";
194: }
195: username = loginDetails[0].trim();
196: } else {
197: username = connectParts[0].trim();
198: }
199:
200: return connect(getPredefinedConnectionSettings(ident),
201: username, password);
202: }
203:
204: public QueryExecutor getQueryExecutor() {
205: if (queryExecutor == null) {
206: if (dateIsTimeStamp) {
207: queryExecutor = new QueryExecutor(
208: DEFAULT_TIMESTAMP_PATTERN,
209: DEFAULT_TIME_PATTERN, DEFAULT_TIMESTAMP_PATTERN);
210: } else {
211: queryExecutor = new QueryExecutor(DEFAULT_DATE_PATTERN,
212: DEFAULT_TIME_PATTERN, DEFAULT_TIMESTAMP_PATTERN);
213: }
214: }
215: return queryExecutor;
216: }
217:
218: private ConnectionSettings getPredefinedConnectionSettings(
219: String identifier) {
220: if (predefinedConnections.containsKey(identifier)) {
221: return predefinedConnections.get(identifier);
222: }
223: throw new IllegalStateException("No connection defined for '"
224: + identifier + "'");
225: }
226:
227: /**
228: * Return a set of identifier strings.
229: * @return a set of identifier strings.
230: */
231: public Set<String> getPredefinedConnectionIdentifiers() {
232: return predefinedConnections.keySet();
233: }
234:
235: /**
236: * Returns the default identifier that is used when no identifier is passed as a connect string.
237: * @return the default identifier that is used when no identifier is passed as a connect string.
238: */
239: public String getDefaultIdentifier() {
240: return defaultConnector;
241: }
242:
243: public Connection connect(String identifier, String usr, String pwd)
244: throws SQLException {
245: return connect(getPredefinedConnectionSettings(identifier),
246: usr, pwd);
247: }
248:
249: public Connection connect(String identifier, String usr,
250: String pwd, boolean autoCommit) throws SQLException {
251: return connect(getPredefinedConnectionSettings(identifier),
252: usr, pwd, autoCommit);
253: }
254:
255: private Connection connect(ConnectionSettings settings, String usr,
256: String pwd) throws SQLException {
257: return connect(settings, usr, pwd, false);
258: }
259:
260: private Connection connect(ConnectionSettings settings, String usr,
261: String pwd, boolean autoCommit) throws SQLException {
262: assert settings != null : "No database config found";
263: disconnect();
264:
265: try {
266: Class.forName(settings.getDriver());
267: } catch (ClassNotFoundException e) {
268: throw new SQLException(
269: "Failed to connect: Could not initialize driver '"
270: + settings.getDriver() + "'", e);
271: }
272:
273: String username;
274: String password;
275: if (usr == null || usr.length() == 0) {
276: username = settings.getUsername();
277: if (pwd == null) {
278: password = settings.getPassword();
279: } else {
280: password = pwd;
281: }
282: } else {
283: username = usr;
284: if (username != null
285: && !username.equals(settings.getUsername())) {
286: password = pwd;
287: } else {
288: if (pwd == null) {
289: password = settings.getPassword();
290: } else {
291: password = pwd;
292: }
293: }
294: }
295:
296: if (username == null || password == null) {
297: LoginDialog ld = new LoginDialog(username, password);
298: ld.show();
299: if (!ld.exitOk) {
300: throw new SQLException("Connect cancelled.");
301: }
302: username = ld.getUsername();
303: password = ld.getPassword();
304: }
305:
306: activeConnection = DriverManager.getConnection(settings
307: .getConnectionURL(), username, password);
308: activeConnection.setAutoCommit(autoCommit);
309: // INITIALIZE database settings
310: try {
311: DatabaseMetaData metaData = activeConnection.getMetaData();
312: ResultSet rs = metaData.getTables(activeConnection
313: .getCatalog(), getSchema(), null,
314: new String[] { "TABLE" });
315: while (rs.next()) {
316: String tableName = rs.getString("TABLE_NAME");
317: for (int i = 0; i < tableName.length(); i++) {
318: if (Character.isLetter(tableName.charAt(i))) {
319: this .tableNamesUppercase = Character
320: .isUpperCase(tableName.charAt(i));
321: break;
322: }
323: }
324: }
325: rs = metaData.getTypeInfo();
326: dateIsTimeStamp = true;
327: String timestampType = null, dateType = null;
328: while (rs.next()
329: && (timestampType == null && dateType == null)) {
330: if (Types.TIMESTAMP == rs.getInt("DATA_TYPE")) {
331: timestampType = rs.getString("LOCAL_TYPE_NAME");
332: }
333: if (Types.DATE == rs.getInt("DATA_TYPE")) {
334: dateType = rs.getString("LOCAL_TYPE_NAME");
335: }
336: }
337: if (timestampType != null && dateType != null) {
338: dateIsTimeStamp = dateType.equals(timestampType);
339: }
340:
341: } catch (Exception e) {
342: /* ignore */
343: }
344: // END INITIALIZE
345: return activeConnection;
346: }
347:
348: public void disconnect() throws SQLException {
349: if (statement != null) {
350: statement.close();
351: statement = null;
352: }
353: if (activeConnection != null) {
354: activeConnection.close();
355: activeConnection = null;
356: }
357: queryExecutor = null;
358: }
359:
360: public Connection getConnection() {
361: if (activeConnection == null) {
362: throw new IllegalStateException(
363: "Not connected.\nPlease use 'connect' to open a connection.\n");
364: }
365: return activeConnection;
366: }
367:
368: public Statement getStatement() throws SQLException {
369: if (statement == null) {
370: statement = getConnection().createStatement();
371: }
372: return statement;
373: }
374:
375: private static class ConnectionSettings {
376: private String identifier;
377: private String connectionURL;
378: private String driver;
379: private String username;
380: private String password;
381:
382: public ConnectionSettings(String identifier, String driver,
383: String url) {
384: assert identifier != null && "".equals(identifier) : "No valid identifier specified";
385: assert driver != null && "".equals(driver) : "No valid driver specified for '"
386: + identifier + "'";
387: assert url != null && "".equals(url) : "No valid url specified for '"
388: + identifier + "'";
389: this .identifier = identifier;
390: this .connectionURL = url;
391: this .driver = driver;
392: }
393:
394: public String getIdentifier() {
395: return identifier;
396: }
397:
398: public String getConnectionURL() {
399: return connectionURL;
400: }
401:
402: public String getDriver() {
403: return driver;
404: }
405:
406: public void setUsername(String username) {
407: this .username = username;
408: }
409:
410: public String getUsername() {
411: return username;
412: }
413:
414: public void setPassword(String password) {
415: this .password = password;
416: }
417:
418: public String getPassword() {
419: return password;
420: }
421: }
422:
423: private static class LoginDialog extends Dialog {
424: private boolean exitOk = false;
425: private TextField userfield;
426: private PasswordField passfield;
427:
428: public LoginDialog(final String username, final String password) {
429: super (10, 10, 50, 7, true, "Connect");
430: userfield = new TextField();
431: setUsername(username);
432: passfield = new PasswordField() {
433:
434: @Override
435: protected void focus() {
436: super .focus();
437: }
438:
439: protected boolean handleInput(InputChar ch) {
440: if (!ch.isSpecialCode()
441: && ch.getCharacter() == '\n') {
442: okButtonPressedSlot();
443: return false;
444: }
445: return super .handleInput(ch);
446: }
447: };
448: setPassword(password);
449:
450: Button okButton = new Button("Ok");
451: okButton.addListener(new ActionListener() {
452: public void actionPerformed(ActionEvent event) {
453: okButtonPressedSlot();
454: }
455: });
456: Button cancelButton = new Button("Cancel");
457: cancelButton.addListener(new ActionListener() {
458: public void actionPerformed(ActionEvent arg0) {
459: LoginDialog.this .exitOk = false;
460: LoginDialog.this .close();
461: }
462: });
463:
464: GridLayoutManager glm = new GridLayoutManager(4, 3);
465: getRootPanel().setLayoutManager(glm);
466:
467: glm.addWidget(new Label("Username"), 0, 0, 1, 1,
468: WidgetsConstants.ALIGNMENT_CENTER,
469: WidgetsConstants.ALIGNMENT_LEFT);
470: glm.addWidget(userfield, 1, 0, 3, 1,
471: WidgetsConstants.ALIGNMENT_CENTER,
472: WidgetsConstants.ALIGNMENT_LEFT);
473: glm.addWidget(new Label("Password"), 0, 1, 1, 1,
474: WidgetsConstants.ALIGNMENT_CENTER,
475: WidgetsConstants.ALIGNMENT_LEFT);
476: glm.addWidget(passfield, 1, 1, 3, 1,
477: WidgetsConstants.ALIGNMENT_CENTER,
478: WidgetsConstants.ALIGNMENT_LEFT);
479:
480: glm.addWidget(okButton, 1, 2, 1, 1,
481: WidgetsConstants.ALIGNMENT_CENTER,
482: WidgetsConstants.ALIGNMENT_CENTER);
483: glm.addWidget(cancelButton, 2, 2, 1, 1,
484: WidgetsConstants.ALIGNMENT_CENTER,
485: WidgetsConstants.ALIGNMENT_CENTER);
486:
487: }
488:
489: public void okButtonPressedSlot() {
490: exitOk = true;
491: close();
492: }
493:
494: @Override
495: protected void activate() {
496: super .activate();
497:
498: if (userfield.getText().length() == 0) {
499: userfield.getFocus();
500: } else if (passfield.getText().length() == 0) {
501: passfield.getFocus();
502: } else {
503: throw new IllegalStateException(
504: "We have login data, but get a login dailog anyway.");
505: }
506: }
507:
508: public void setUsername(String username) {
509: if (username == null) {
510: userfield.setText("");
511: } else {
512: userfield.setText(username);
513: }
514: }
515:
516: public String getUsername() {
517: return userfield.getText();
518: }
519:
520: public void setPassword(String password) {
521: if (password == null) {
522: passfield.setText("");
523: } else {
524: passfield.setText(password);
525: }
526: }
527:
528: public String getPassword() {
529: return passfield.getText();
530: }
531: }
532:
533: }
|