001: /*
002: $Header: /cvsroot/xorm/xorm/src/org/xorm/datastore/sql/BaseSQLDriver.java,v 1.42 2003/08/06 10:31:32 wbiggs Exp $
003:
004: This file is part of XORM.
005:
006: XORM is free software; you can redistribute it and/or modify
007: it under the terms of the GNU General Public License as published by
008: the Free Software Foundation; either version 2 of the License, or
009: (at your option) any later version.
010:
011: XORM is distributed in the hope that it will be useful,
012: but WITHOUT ANY WARRANTY; without even the implied warranty of
013: MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
014: GNU General Public License for more details.
015:
016: You should have received a copy of the GNU General Public License
017: along with XORM; if not, write to the Free Software
018: Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
019: */
020: package org.xorm.datastore.sql;
021:
022: import java.io.CharArrayReader;
023: import java.sql.Connection;
024: import java.sql.PreparedStatement;
025: import java.sql.ResultSet;
026: import java.sql.SQLException;
027: import java.sql.Timestamp;
028: import java.sql.Types;
029: import java.util.ArrayList;
030: import java.util.Collection;
031: import java.util.Date;
032: import java.util.HashMap;
033: import java.util.Iterator;
034: import java.util.List;
035: import java.util.Set;
036: import java.util.logging.Logger;
037: import javax.sql.DataSource;
038:
039: import org.xorm.I15d;
040: import org.xorm.datastore.Column;
041: import org.xorm.datastore.DataFetchGroup;
042: import org.xorm.datastore.DatastoreDriver;
043: import org.xorm.datastore.DriverException;
044: import org.xorm.datastore.Row;
045: import org.xorm.datastore.Table;
046: import org.xorm.query.Selector;
047:
048: /**
049: * Implements the datastore driver interface for a "plain vanilla"
050: * generic JDBC driver. The implementation is configured by the
051: * drivers.properties file, which maps particular idioms for handling
052: * sequences and autoincrement columns to particular JDBC driver classes.
053: * This is intended to handle all JDBC drivers that work according to
054: * the specification; subclasses should be necessary only for databases
055: * that behave in odd or non-standard ways.
056: */
057: public class BaseSQLDriver implements DatastoreDriver, I15d {
058: protected SQLConnectionInfo connectionInfo;
059: protected Logger logger = Logger
060: .getLogger("org.xorm.datastore.sql");
061: protected DataSource dataSource;
062: protected Connection currentConnection;
063: protected boolean readOnly;
064:
065: // For debugging; stack trace of the last caller.
066: private Exception lastBegin;
067:
068: public void setConnectionInfo(SQLConnectionInfo connectionInfo) {
069: this .connectionInfo = connectionInfo;
070: }
071:
072: public void setDataSource(DataSource dataSource) {
073: this .dataSource = dataSource;
074: logger.config("dataSource set to " + dataSource);
075: }
076:
077: protected boolean inTransaction() {
078: return currentConnection != null;
079: }
080:
081: // Transactional methods
082:
083: public void begin(boolean readOnly) throws DriverException {
084: this .readOnly = readOnly;
085: // Acquire a connection from the pool
086: try {
087: if (currentConnection != null) {
088: throw new DriverException(I18N.msg("E_txn_in_use"),
089: lastBegin);
090: }
091: currentConnection = dataSource.getConnection();
092: currentConnection.setAutoCommit(readOnly);
093: currentConnection.setReadOnly(readOnly);
094:
095: // Populate lastBegin
096: try {
097: throw new Exception();
098: } catch (Exception e) {
099: lastBegin = e;
100: }
101: } catch (SQLException e) {
102: throw new DriverException(e);
103: }
104: }
105:
106: public void commit() throws DriverException {
107: try {
108: if (currentConnection == null) {
109: logger
110: .info("Trying to close a null connection, weird.");
111: return;
112: }
113: if (!readOnly) {
114: // Commit on the connection
115: currentConnection.commit();
116: }
117: currentConnection.close();
118: currentConnection = null;
119: } catch (SQLException e) {
120: throw new DriverException(e);
121: }
122: }
123:
124: public void rollback() throws DriverException {
125: try {
126: if (currentConnection == null) {
127: logger
128: .info("Trying to roll back a null connection, weird.");
129: return;
130: }
131: if (!readOnly) {
132: // Rollback on the connection
133: currentConnection.rollback();
134: }
135: currentConnection.close();
136: currentConnection = null;
137: } catch (SQLException e) {
138: throw new DriverException(e);
139: }
140: }
141:
142: /**
143: * Contains a set of prepared statement Strings for each
144: * Table that has been dealth with. Static because the same
145: * Strings apply as long as we're working with the same Table.
146: *
147: * Key: org.xorm.datastore.Table
148: * Value: BaseSQLDriver$PreparedStatements
149: */
150: private static HashMap tableToStatements = new HashMap();
151:
152: /**
153: * Subclasses should override this if they need to provide custom
154: * SQL generation for the prepared statements.
155: */
156: protected PreparedStatements createPreparedStatements(Table table) {
157: return new PreparedStatements(table);
158: }
159:
160: protected PreparedStatements getStatements(Table table) {
161: PreparedStatements statements = (PreparedStatements) tableToStatements
162: .get(table);
163: if (statements == null) {
164: statements = createPreparedStatements(table);
165: tableToStatements.put(table, statements);
166: }
167: return statements;
168: }
169:
170: public void create(Row row) throws DriverException {
171: if (readOnly) {
172: throw new DriverException(I18N.msg("E_read_only_txn",
173: "create"));
174: }
175: Column primaryKey = row.getTable().getPrimaryKey();
176: String statement = getStatements(row.getTable()).insertStatement;
177: String nextIDStatement = getStatements(row.getTable()).nextIDStatement;
178:
179: Object useID = null;
180: try {
181: // If primary key uses a non-autoincremented sequence, get the ID
182: if (primaryKey != null && primaryKey.getSequence() != null
183: && !primaryKey.isAutoIncremented()) {
184: logger.info(nextIDStatement);
185: PreparedStatement ps1 = currentConnection
186: .prepareStatement(nextIDStatement);
187: ResultSet rs1 = ps1.executeQuery();
188: useID = null;
189: if (rs1.next()) {
190: useID = rs1.getObject(1);
191: }
192: rs1.close();
193:
194: row.setValue(row.getTable().getPrimaryKey(), useID);
195: }
196:
197: logger.info(statement);
198: logger.info(row.toString());
199:
200: // Now do insert
201: PreparedStatement ps = currentConnection
202: .prepareStatement(statement);
203: Set columns = row.getTable().getColumns();
204: int pos = 1;
205: for (Iterator i = columns.iterator(); i.hasNext();) {
206: Column c = (Column) i.next();
207: if ((c == primaryKey && primaryKey.isAutoIncremented())
208: || c.isReadOnly()) {
209: continue;
210: }
211: Object value = row.getValue(c);
212: setObject(ps, pos++, value, c.getType());
213: }
214: ps.executeUpdate();
215: ps.close();
216:
217: // If autoincremented, read ID now
218: if (primaryKey != null && primaryKey.isAutoIncremented()) {
219: logger.info(nextIDStatement);
220: PreparedStatement ps1 = currentConnection
221: .prepareStatement(nextIDStatement);
222: ResultSet rs1 = ps1.executeQuery();
223: useID = null;
224: if (rs1.next()) {
225: useID = rs1.getObject(1);
226: }
227: rs1.close();
228: logger.info("using ID " + useID);
229: // TODO inform other objects about ID?
230: row.setValue(row.getTable().getPrimaryKey(), useID);
231: }
232:
233: } catch (SQLException e) {
234: e.printStackTrace();
235: throw new DriverException(e);
236: }
237: }
238:
239: /**
240: * Updates a row against the database. Uses the dirty bit settings
241: * on Row to determine which fields to update.
242: */
243: public void update(Row row) throws DriverException {
244: if (readOnly) {
245: throw new DriverException(I18N.msg("E_read_only_txn",
246: "update"));
247: }
248: Table table = row.getTable();
249: Column primaryKey = table.getPrimaryKey();
250:
251: // Cannot update rows without a primary key
252: if (primaryKey == null) {
253: return;
254: }
255:
256: StringBuffer sql = new StringBuffer();
257: sql.append("UPDATE ").append(table.getName()).append(" SET ");
258: Iterator it = table.getColumns().iterator();
259: boolean seenOne = false;
260: while (it.hasNext()) {
261: Column c = (Column) it.next();
262: if ((c == primaryKey && primaryKey.isAutoIncremented())
263: || c.isReadOnly()) {
264: continue;
265: }
266: if (row.isDirty(c)) {
267: if (seenOne) {
268: sql.append(", ");
269: } else {
270: seenOne = true;
271: }
272: sql.append(c.getName()).append(" = ?");
273: }
274: }
275: // No need to do anything if nothing has changed.
276: if (!seenOne) {
277: return;
278: }
279: sql.append(" WHERE ").append(table.getPrimaryKey().getName())
280: .append(" = ?");
281: String statement = sql.toString();
282: logger.info(statement);
283: logger.info(row.toString());
284: try {
285: PreparedStatement ps = currentConnection
286: .prepareStatement(statement);
287: int pos = 1;
288: Set columns = row.getTable().getColumns();
289: for (Iterator i = columns.iterator(); i.hasNext();) {
290: Column c = (Column) i.next();
291: if (c == primaryKey && primaryKey.isAutoIncremented())
292: continue;
293: if (row.isDirty(c)) {
294: Object value = row.getValue(c);
295: setObject(ps, pos++, value, c.getType());
296: }
297: }
298: ps.setObject(pos++, row.getPrimaryKeyValue());
299: ps.executeUpdate();
300: ps.close();
301: } catch (SQLException e) {
302: throw new DriverException(e);
303: }
304: }
305:
306: public void delete(Row row) throws DriverException {
307: if (readOnly) {
308: throw new DriverException(I18N.msg("E_read_only_txn",
309: "delete"));
310: }
311: // If there is no primary key, delete using all columns explicitly
312: if (row.getTable().getPrimaryKey() == null) {
313: StringBuffer sql = new StringBuffer()
314: .append("DELETE FROM ").append(
315: row.getTable().getName()).append(" WHERE ");
316: Iterator it = row.getTable().getColumns().iterator();
317: while (it.hasNext()) {
318: Column c = (Column) it.next();
319: sql.append(c.getName());
320: if (row.getValue(c) == null) {
321: sql.append(" IS NULL");
322: } else {
323: sql.append(" = ?");
324: }
325: if (it.hasNext())
326: sql.append(" AND ");
327: }
328:
329: String statement = sql.toString();
330: logger.info(statement);
331: logger.info(row.toString());
332:
333: try {
334: PreparedStatement ps = currentConnection
335: .prepareStatement(statement);
336: Set columns = row.getTable().getColumns();
337: int i = 1; // preparedstatement position
338: for (it = columns.iterator(); it.hasNext();) {
339: Column c = (Column) it.next();
340: Object value = row.getValue(c);
341: if (value != null) {
342: setObject(ps, i++, value, c.getType());
343: }
344: }
345: ps.executeUpdate();
346: ps.close();
347: } catch (SQLException e) {
348: throw new DriverException(e);
349: }
350: } else {
351: // Usually we have a primary key to delete with
352: String statement = getStatements(row.getTable()).deleteStatement;
353: logger.info(statement);
354: logger.info("{ " + row.getPrimaryKeyValue() + " }");
355: try {
356: PreparedStatement ps = currentConnection
357: .prepareStatement(statement);
358: ps.setObject(1, row.getPrimaryKeyValue());
359: ps.executeUpdate();
360: ps.close();
361: } catch (SQLException e) {
362: throw new DriverException(e);
363: }
364: }
365: }
366:
367: public int count(Selector selector) throws DriverException {
368: SQLQuery query = new SQLQuery(selector);
369: Table table = query.getTargetTable();
370: // Similar to a select for the class
371: String statement = query.toCountSQL();
372: logger.info(statement);
373: int size = 0;
374: try {
375: PreparedStatement ps = currentConnection
376: .prepareStatement(statement);
377: int pos = 0;
378: Iterator it = query.getParameters().iterator();
379: while (it.hasNext()) {
380: Object obj = it.next();
381: logger.info("Parameter: " + obj);
382: setObject(ps, ++pos, obj, null /*type???*/);
383: }
384:
385: ResultSet rs = ps.executeQuery();
386: if (rs.next()) {
387: size = rs.getInt(1);
388: }
389: rs.close();
390: ps.close();
391: } catch (SQLException e) {
392: throw new DriverException(e);
393: }
394: return size;
395: }
396:
397: public Collection select(Selector selector, Set extraRows)
398: throws DriverException {
399: SQLQuery query = new SQLQuery(selector);
400: String statement = query.toSQL();
401: logger.info(statement);
402: ArrayList list = new ArrayList();
403:
404: try {
405: PreparedStatement ps = currentConnection
406: .prepareStatement(statement);
407:
408: int pos = 0;
409: Iterator it = query.getParameters().iterator();
410: while (it.hasNext()) {
411: Object obj = it.next();
412: logger.info("Parameter: " + obj);
413: setObject(ps, ++pos, obj, null /*type???*/);
414: }
415:
416: ResultSet rs = ps.executeQuery();
417: Set aliases = query.getAliases();
418:
419: while (rs.next()) {
420: Row row;
421: Set columns;
422: SQLQuery.Alias alias;
423: Table table;
424:
425: pos = 0;
426: Iterator i = aliases.iterator();
427: while (i.hasNext()) {
428: alias = (SQLQuery.Alias) i.next();
429: table = alias.getTable();
430: Set fcs = alias.getFetchColumns();
431: if (!fcs.isEmpty()) {
432: row = new Row(table);
433: Iterator fci = fcs.iterator();
434: while (fci.hasNext()) {
435: Column c = (Column) fci.next();
436:
437: // TODO will this be flexible enough?
438: row.setValue(c, rs.getObject(++pos));
439: }
440: if (table.equals(query.getTargetTable())) {
441: list.add(row);
442: } else {
443: extraRows.add(row);
444: }
445: } // non-empty fetchgroup
446: } // for each alias
447: } // for each result
448: rs.close();
449: ps.close();
450: } catch (SQLException e) {
451: throw new DriverException(e);
452: }
453: return list;
454: }
455:
456: /**
457: * Handles default conversions that should work with most JDBC
458: * drivers. Instances of java.util.Date are converted to
459: * java.sql.Timestamp; values for SQL "CHAR" columns are converted
460: * into a character stream.
461: */
462: protected void setObject(PreparedStatement ps, int pos,
463: Object value, String type) throws SQLException {
464: if (value != null && value.getClass() == Date.class) {
465: ps.setTimestamp(pos,
466: new Timestamp(((Date) value).getTime()));
467: } else {
468: if (type != null) {
469: int typeCode = SQLType.forName(type);
470: if (typeCode == Types.CHAR && value != null) {
471: char[] c_array = value.toString().toCharArray();
472: CharArrayReader r = new CharArrayReader(c_array);
473: ps.setCharacterStream(pos, r, c_array.length);
474: return;
475: }
476: ps.setObject(pos, value, typeCode);
477: return;
478: }
479: ps.setObject(pos, value);
480: }
481: }
482:
483: // The remainder of this class is concerned with SQL
484:
485: public class PreparedStatements {
486: public String nextIDStatement;
487: public String insertStatement;
488: public String deleteStatement;
489:
490: public PreparedStatements(Table table) {
491: // Generate statements that will be reused as PreparedStatements
492: nextIDStatement = generateNextIDStatement(table);
493: insertStatement = generateInsertStatement(table);
494: deleteStatement = generateDeleteStatement(table);
495: }
496:
497: /** This method should be implemented by subclasses. */
498: public String generateNextIDStatement(Table table) {
499: if (table.getPrimaryKey() == null)
500: return null;
501:
502: String pattern = (table.getPrimaryKey().isAutoIncremented()) ? connectionInfo
503: .getLastIDStatement()
504: : connectionInfo.getNextIDStatement();
505: if ((pattern == null) || "".equals(pattern)) {
506: return null;
507: }
508: String seqName = table.getPrimaryKey().getSequence();
509: if (seqName != null) {
510: return pattern.replaceAll("\\{0\\}", table
511: .getPrimaryKey().getSequence());
512: } else
513: return pattern;
514: }
515:
516: public String generateInsertStatement(Table table) {
517: StringBuffer sql = new StringBuffer();
518: sql.append("INSERT INTO ").append(table.getName()).append(
519: " (");
520: Iterator it = table.getColumns().iterator();
521: Column primaryKey = table.getPrimaryKey();
522: StringBuffer args = new StringBuffer();
523: boolean seenAny = false;
524: while (it.hasNext()) {
525: Column c = (Column) it.next();
526: if ((c == primaryKey && primaryKey.isAutoIncremented())
527: || c.isReadOnly()) {
528: continue;
529: }
530: if (!seenAny) {
531: seenAny = true;
532: } else {
533: args.append(",");
534: sql.append(",");
535: }
536: sql.append(c.getName());
537: args.append("?");
538: }
539: sql.append(") VALUES (").append(args.toString())
540: .append(")");
541: return sql.toString();
542: }
543:
544: public String generateDeleteStatement(Table table) {
545: if (table.getPrimaryKey() == null)
546: return null;
547: StringBuffer sql = new StringBuffer();
548: sql.append("DELETE FROM ").append(table.getName()).append(
549: " WHERE ").append(table.getPrimaryKey().getName())
550: .append(" = ?");
551: return sql.toString();
552: }
553: } // inner class PreparedStatements
554: } // class BaseSQLDriver
|