0001: /*
0002: * hgcommons 7
0003: * Hammurapi Group Common Library
0004: * Copyright (C) 2003 Hammurapi Group
0005: *
0006: * This program is free software; you can redistribute it and/or
0007: * modify it under the terms of the GNU Lesser General Public
0008: * License as published by the Free Software Foundation; either
0009: * version 2 of the License, or (at your option) any later version.
0010: *
0011: * This program is distributed in the hope that it will be useful,
0012: * but WITHOUT ANY WARRANTY; without even the implied warranty of
0013: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
0014: * Lesser General Public License for more details.
0015: *
0016: * You should have received a copy of the GNU Lesser General Public
0017: * License along with this library; if not, write to the Free Software
0018: * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
0019: *
0020: * URL: http://www.hammurapi.biz/hammurapi-biz/ef/xmenu/hammurapi-group/products/products/hgcommons/index.html
0021: * e-Mail: support@hammurapi.biz
0022: */
0023: package biz.hammurapi.sql;
0024:
0025: import java.io.IOException;
0026: import java.io.InputStream;
0027: import java.io.InputStreamReader;
0028: import java.io.Reader;
0029: import java.io.StringWriter;
0030: import java.sql.Connection;
0031: import java.sql.DriverManager;
0032: import java.sql.PreparedStatement;
0033: import java.sql.ResultSet;
0034: import java.sql.ResultSetMetaData;
0035: import java.sql.SQLException;
0036: import java.sql.Statement;
0037: import java.util.ArrayList;
0038: import java.util.Collection;
0039: import java.util.HashMap;
0040: import java.util.Map;
0041: import java.util.Properties;
0042:
0043: import javax.sql.DataSource;
0044:
0045: import biz.hammurapi.config.ConfigurationException;
0046: import biz.hammurapi.config.Context;
0047: import biz.hammurapi.config.DomConfigFactory;
0048: import biz.hammurapi.config.MapContext;
0049: import biz.hammurapi.config.PropertyParser;
0050: import biz.hammurapi.metrics.MeasurementCategoryFactory;
0051: import biz.hammurapi.metrics.TimeIntervalCategory;
0052: import biz.hammurapi.sql.hypersonic.HypersonicInMemoryDataSource;
0053: import biz.hammurapi.util.ExceptionSink;
0054:
0055: /**
0056: * This class contains methods to process SQL statements in more convenient
0057: * way comparing to standard JDBC.
0058: * @author Pavel Vlasov
0059: * @version $Revision: 1.14 $
0060: */
0061: public class SQLProcessor {
0062: private DataSource dataSource;
0063: private Connection connection;
0064: private PropertyParser propertyParser;
0065: private TimeIntervalCategory timeIntervalCategory = MeasurementCategoryFactory
0066: .getTimeIntervalCategory(SQLProcessor.class);
0067: private Context nameMap;
0068:
0069: /**
0070: * If SQLProcessor constructed with this constructor
0071: * then it obtains connection from the datasource, processes
0072: * request and closes connection.
0073: * @param dataSource DataSource
0074: * @param nameMap NameMap allows to write parameterized SQL statements
0075: * like "SELECT ${AMOUNT} FROM ${ACCOUNT} WHERE ${ANUM}=? AND CLOSED=1"
0076: * nameMap shall contain mapping from AMOUNT, ACCOUNT and ANUM to actual
0077: * database field names. If nameMap doesn't contain mapping for some
0078: * properties then property names will be used as property values.
0079: * See {@link biz.hammurapi.config.PropertyParser}.
0080: * One property value can contain a reference to another property.
0081: * If nameMap is null then no property parsing will happen.
0082: */
0083: public SQLProcessor(DataSource dataSource, Context nameMap) {
0084: this (nameMap);
0085: this .dataSource = dataSource;
0086: }
0087:
0088: /**
0089: * @param nameMap
0090: */
0091: private SQLProcessor(Context nameMap) {
0092: super ();
0093: this .nameMap = nameMap;
0094: if (nameMap != null) {
0095: propertyParser = new PropertyParser(nameMap, true);
0096: }
0097: }
0098:
0099: /**
0100: * If SQLProcessor created with this constructor then is doesn't
0101: * close the connection after processing.
0102: * @param connection
0103: * @param nameMap See {@link biz.hammurapi.sql.SQLProcessor#SQLProcessor(DataSource, Properties)}
0104: */
0105: public SQLProcessor(Connection connection, Context nameMap) {
0106: this (nameMap);
0107: this .connection = connection;
0108: }
0109:
0110: /**
0111: * Replaces ${<property name>} with property value. See {@link biz.hammurapi.config.PropertyParser}
0112: * @param str
0113: * @return parsed string
0114: */
0115: public String parse(String str) {
0116: if (propertyParser == null) {
0117: return str;
0118: }
0119:
0120: return propertyParser.parse(str);
0121: }
0122:
0123: /**
0124: * Returns connection if you need it for JDBC calls outside of the SQLProcessor
0125: * @return Connection
0126: * @throws SQLException
0127: */
0128: public Connection getConnection() throws SQLException {
0129: return connection == null ? dataSource == null ? null
0130: : dataSource.getConnection() : connection;
0131: }
0132:
0133: /**
0134: * Closes connection if it was provided by DataSource. Does nothing otherwise.
0135: * @param connection Connection to release.
0136: * @throws SQLException
0137: */
0138: public void releaseConnection(Connection connection)
0139: throws SQLException {
0140: if (this .connection == null && connection != null) {
0141: connection.close();
0142: }
0143: }
0144:
0145: /**
0146: * Processes SQL SELECT statement in the following way:
0147: * <UL>
0148: * <li>Obtains connection</li>
0149: * <LI>If parameterizer==null creates Statement, creates PreparedStatement otherwise</li>
0150: * <LI>Invokes parameterizer.parameterize() if parameterizer is not null</li>
0151: * <li>Iterates through result set and invokes rowProcessor.process() on each row</li>
0152: * <li>If there was no rows and rowProcess is instance of {@link RowProcessorEx} then rowProcessor.onEmptyResultSet() is invoked</li>
0153: * <li>ResultSet, Statement and connection are properly released</li>
0154: * </UL>
0155: *
0156: * @param sql SQL statment to execute
0157: * @param parameterizer Parameterizer
0158: * @param rowProcessor RowProcessor
0159: * @throws SQLException
0160: */
0161: public void processSelect(String sql, Parameterizer parameterizer,
0162: RowProcessor rowProcessor) throws SQLException {
0163: Connection con = getConnection();
0164: try {
0165: processSelect(con, sql, parameterizer, rowProcessor);
0166: } finally {
0167: releaseConnection(con);
0168: }
0169: }
0170:
0171: /**
0172: * @param con
0173: * @param sql
0174: * @param parameterizer
0175: * @param rowProcessor
0176: * @param releaseConnection
0177: * @throws SQLException
0178: */
0179: private void processSelect(Connection con, String sql,
0180: Parameterizer parameterizer, RowProcessor rowProcessor)
0181: throws SQLException {
0182: long start = timeIntervalCategory == null ? 0
0183: : timeIntervalCategory.getTime();
0184: if (parameterizer == null) {
0185: Statement statement = con.createStatement();
0186: try {
0187: ResultSet rs = statement.executeQuery(parse(sql));
0188: if (propertyParser != null) {
0189: rs = new ResultSetProxy(this , rs);
0190: }
0191:
0192: try {
0193: if (rowProcessor instanceof MetadataAwareRowProcessor) {
0194: ((MetadataAwareRowProcessor) rowProcessor)
0195: .processMetadata(rs.getMetaData());
0196: }
0197:
0198: boolean isEmpty = true;
0199:
0200: while (rs.next()) {
0201: isEmpty = false;
0202: if (!rowProcessor.process(rs)) {
0203: break;
0204: }
0205: }
0206:
0207: if (isEmpty
0208: && rowProcessor instanceof RowProcessorEx) {
0209: ((RowProcessorEx) rowProcessor)
0210: .onEmptyResultSet();
0211: }
0212: } finally {
0213: rs.close();
0214: }
0215: } catch (SQLException e) {
0216: throw new SQLExceptionEx(
0217: "Failed to execute statement: " + sql, e);
0218: } finally {
0219: statement.close();
0220: }
0221: } else {
0222: PreparedStatement statement = con
0223: .prepareStatement(parse(sql));
0224: parameterizer.parameterize(statement);
0225: try {
0226: ResultSet rs = statement.executeQuery();
0227: try {
0228: if (rowProcessor instanceof MetadataAwareRowProcessor) {
0229: ((MetadataAwareRowProcessor) rowProcessor)
0230: .processMetadata(rs.getMetaData());
0231: }
0232:
0233: boolean isEmpty = true;
0234:
0235: while (rs.next()) {
0236: isEmpty = false;
0237: if (!rowProcessor
0238: .process(propertyParser == null ? rs
0239: : new ResultSetProxy(this , rs))) {
0240: break;
0241: }
0242: }
0243:
0244: if (isEmpty
0245: && rowProcessor instanceof RowProcessorEx) {
0246: ((RowProcessorEx) rowProcessor)
0247: .onEmptyResultSet();
0248: }
0249: } finally {
0250: rs.close();
0251: }
0252: } catch (SQLException e) {
0253: throw new SQLExceptionEx(
0254: "Failed to execute statement: " + sql, e);
0255: } finally {
0256: statement.close();
0257: }
0258: }
0259: if (timeIntervalCategory != null) {
0260: timeIntervalCategory.addInterval(sql, start);
0261: }
0262: }
0263:
0264: /**
0265: * Processes SQL INSERT, UPDATE or DELETE statement in the following way:
0266: * <UL>
0267: * <li>Obtains connection</li>
0268: * <LI>If parameterizer==null creates Statement, creates PreparedStatement otherwise</li>
0269: * <LI>Invokes parameterizer.parameterize() if parameterizer is not null</li>
0270: * <li>Executes update</li>
0271: * <li>ResultSet, Statement and connection are properly released</li>
0272: * </UL>
0273: *
0274: * @param sql SQL statment to execute
0275: * @param parameterizer Parameterizer
0276: * @return Number of updates. See {@link Statement#executeUpdate(java.lang.String)}
0277: * @throws SQLException
0278: */
0279: public int processUpdate(String sql, Parameterizer parameterizer)
0280: throws SQLException {
0281: Connection con = getConnection();
0282: try {
0283: return processUpdate(con, sql, parameterizer);
0284: } finally {
0285: releaseConnection(con);
0286: }
0287: }
0288:
0289: /**
0290: * @param con
0291: * @param sql
0292: * @param parameterizer
0293: * @return
0294: * @throws SQLException
0295: */
0296: private int processUpdate(Connection con, String sql,
0297: Parameterizer parameterizer) throws SQLException {
0298: long start = timeIntervalCategory == null ? 0
0299: : timeIntervalCategory.getTime();
0300: String parsedSql = parse(sql);
0301: try {
0302: if (parameterizer == null) {
0303: Statement statement = con.createStatement();
0304: try {
0305: return statement.executeUpdate(parsedSql);
0306: } catch (SQLException e) {
0307: throw new SQLExceptionEx(
0308: "Failed to execute statement: " + parsedSql,
0309: e);
0310: } finally {
0311: statement.close();
0312: }
0313: }
0314:
0315: PreparedStatement statement = con
0316: .prepareStatement(parsedSql);
0317: try {
0318: parameterizer.parameterize(statement);
0319: return statement.executeUpdate();
0320: } catch (SQLException e) {
0321: throw new SQLExceptionEx(
0322: "Failed to execute statement: " + parsedSql, e);
0323: } finally {
0324: statement.close();
0325: }
0326: } finally {
0327: if (timeIntervalCategory != null) {
0328: timeIntervalCategory.addInterval(parsedSql, start);
0329: }
0330: }
0331: }
0332:
0333: /**
0334: * Processes SELECT statement read from resource file. Suitable for huge SQL statements or SQL statements
0335: * maintained by other developers.
0336: * @param resourceName
0337: * @param parameterizer
0338: * @param rowProcessor
0339: * @throws SQLException
0340: * @throws IOException
0341: */
0342: public void processResourceSelect(String resourceName,
0343: Parameterizer parameterizer, RowProcessor rowProcessor)
0344: throws SQLException, IOException {
0345: processSelect(resourceToString(resourceName), parameterizer,
0346: rowProcessor);
0347: }
0348:
0349: /**
0350: * Processes SELECT statement read from resource file. Suitable for huge SQL statements or SQL statements
0351: * maintained by other developers.
0352: * @param resourceName
0353: * @param parameterizer
0354: * @return Number of updates. See {@link Statement#executeUpdate(java.lang.String)}
0355: * @throws SQLException
0356: * @throws IOException
0357: */
0358: public int processResourceUpdate(String resourceName,
0359: Parameterizer parameterizer) throws SQLException,
0360: IOException {
0361: return processUpdate(resourceToString(resourceName),
0362: parameterizer);
0363: }
0364:
0365: private String resourceToString(String resourceName)
0366: throws IOException {
0367: InputStream in = getClass().getClassLoader()
0368: .getResourceAsStream(resourceName);
0369: if (in == null) {
0370: return null;
0371: }
0372:
0373: StringWriter sw = new StringWriter();
0374: try {
0375: char[] buf = new char[1024];
0376: Reader r = new InputStreamReader(in);
0377: try {
0378: int len;
0379: while ((len = r.read(buf)) != -1) {
0380: sw.write(buf, 0, len);
0381: }
0382: } finally {
0383: r.close();
0384: }
0385: } finally {
0386: sw.close();
0387: }
0388: return sw.toString();
0389: }
0390:
0391: /**
0392: * Excecutes script with ; as statement separator
0393: * @param reader Script source
0394: * @throws IOException
0395: * @throws SQLException
0396: * @see SQLProcessor#executeScript(Reader, char)
0397: */
0398: public void executeScript(Reader reader) throws IOException,
0399: SQLException {
0400: executeScript(reader, ';');
0401: }
0402:
0403: /**
0404: * Executes series of SQL statement read from reader and separated by statementSeparator
0405: * @param reader Script source
0406: * @param statementSeparator Statement separator
0407: * @throws IOException
0408: * @throws SQLException
0409: */
0410: public void executeScript(Reader reader, char statementSeparator)
0411: throws IOException, SQLException {
0412: executeScript(reader, statementSeparator, null);
0413: }
0414:
0415: /**
0416: *
0417: * @param reader Script source
0418: * @param statementSeparator Statement separator
0419: * @param exceptionSink Exception sink. Consumes exceptions thrown by individual statements. If sink is null then exception is
0420: * rethrown and script execution terminates.
0421: * @throws IOException
0422: * @throws SQLException
0423: */
0424: public void executeScript(Reader reader, char statementSeparator,
0425: ExceptionSink exceptionSink) throws IOException,
0426: SQLException {
0427: if (reader != null) {
0428: Connection con = getConnection();
0429: try {
0430: Statement stmt = con.createStatement();
0431: try {
0432: try {
0433: StringBuffer sb = new StringBuffer();
0434: int ch;
0435: while ((ch = reader.read()) != -1) {
0436: if (ch == statementSeparator) {
0437: // Double separator is replaced with one e.g. ;; -> ;
0438: int nextCh = reader.read();
0439: if (nextCh == -1) {
0440: break;
0441: } else if (nextCh == statementSeparator) {
0442: sb.append((char) nextCh);
0443: } else {
0444: executeBuffer(stmt, sb,
0445: exceptionSink);
0446: sb = new StringBuffer();
0447: sb.append((char) nextCh);
0448: }
0449: } else {
0450: sb.append((char) ch);
0451: }
0452: }
0453: executeBuffer(stmt, sb, exceptionSink);
0454: } finally {
0455: reader.close();
0456: }
0457: } finally {
0458: stmt.close();
0459: }
0460: } finally {
0461: releaseConnection(con);
0462: }
0463: }
0464: }
0465:
0466: /**
0467: * @param stmt
0468: * @param sb
0469: * @throws SQLException
0470: */
0471: private void executeBuffer(Statement stmt, StringBuffer sb,
0472: ExceptionSink exceptionSink) throws SQLException {
0473: String sql = sb.toString().trim();
0474: if (sql.length() != 0) {
0475: try {
0476: stmt.execute(sql);
0477: } catch (SQLException e) {
0478: if (exceptionSink == null) {
0479: throw new SQLExceptionEx("SQL: " + sql, e);
0480: } else {
0481: exceptionSink.consume(sql, e);
0482: }
0483: }
0484: }
0485: }
0486:
0487: /**
0488: * Executes SQL statement and returns collection backed by the database.
0489: * @param sql Select statement to execute
0490: * @param parameterizer Parameterizer
0491: * @param projector Projector which instantiates objects. It is null then projector
0492: * which projects row to collection will be used.
0493: * @return Collection backed by the database. The collection doesn't hold
0494: * any SQL resources open, neither it keeps any refernces to created objects.
0495: * It keeps only references to the SQLProcessor, parameterizer
0496: * and projector. Thus any call to one of collection methods retursn 'fresh'
0497: * results from the database.
0498: *
0499: * Iterators created by this collection open ResultSet and close it when
0500: * Iterator.hasNext() returns false.
0501: */
0502: public Collection project(String sql, Parameterizer parameterizer,
0503: Projector projector) {
0504: return new ResultSetCollection(this , parse(sql), parameterizer,
0505: projector == null ? defaultProjector : projector);
0506: }
0507:
0508: /**
0509: * Executes SQL statement and returns collection backed by the database.
0510: * Rows are projected to collection of field values.
0511: * @param sql Select statement to execute
0512: * @param parameterizer Parameterizer
0513: * @param projector Projector which instantiates objects. It is null then projector
0514: * which projects row to collection will be used.
0515: * @return Collection backed by the database. The collection doesn't hold
0516: * any SQL resources open, neither it keeps any refernces to created objects.
0517: * It keeps only references to the SQLProcessor and parameterizer. Thus any call to one of collection methods retursn 'fresh'
0518: * results from the database.
0519: *
0520: * Iterators created by this collection open ResultSet and close it when
0521: * Iterator.hasNext() returns false.
0522: */
0523: public Collection project(String sql, Parameterizer parameterizer) {
0524: return new ResultSetCollection(this , parse(sql), parameterizer,
0525: defaultProjector);
0526: }
0527:
0528: /**
0529: * Executes SQL statement and returns collection backed by the database.
0530: * @param sql Select statement to execute
0531: * @return Collection backed by the database. The collection doesn't hold
0532: * any SQL resources open, neither it keeps any refernces to created objects.
0533: * It keeps only references to the SQLProcessor.
0534: * Thus any call to one of collection methods retursn 'fresh'
0535: * results from the database.
0536: *
0537: * Iterators created by this collection open ResultSet and close it when
0538: * Iterator.hasNext() returns false.
0539: */
0540: public Collection project(String sql) {
0541: return new ResultSetCollection(this , parse(sql), null,
0542: defaultProjector);
0543: }
0544:
0545: /**
0546: * Executes SQL statement and returns collection backed by the database.
0547: * @param sql Select statement to execute
0548: * @param parameterizer Parameterizer
0549: * @param theInterface Iterface to implement
0550: * @return Collection backed by the database. The collection doesn't hold
0551: * any SQL resources open, neither it keeps any refernces to created objects.
0552: * It keeps only references to the SQLProcessor, parameterizer
0553: * and projector. Thus any call to one of collection methods retursn 'fresh'
0554: * results from the database.
0555: *
0556: * Iterators created by this collection open ResultSet and close it when
0557: * Iterator.hasNext() returns false.
0558: */
0559: public Collection project(String sql, Parameterizer parameterizer,
0560: Class theInterface) {
0561: return new ResultSetCollection(this , parse(sql), parameterizer,
0562: new InterfaceProjector(theInterface, null));
0563: }
0564:
0565: /**
0566: * Executes SQL statement and returns collection backed by the database.
0567: * @param sql Select statement to execute
0568: * @param parameterizer Parameterizer
0569: * @param theInterface Interface to implement
0570: * @return Collection backed by the database. The collection doesn't hold
0571: * any SQL resources open, neither it keeps any refernces to created objects.
0572: * It keeps only references to the SQLProcessor, parameterizer
0573: * and projector. Thus any call to one of collection methods retursn 'fresh'
0574: * results from the database.
0575: *
0576: * Iterators created by this collection open ResultSet and close it when
0577: * Iterator.hasNext() returns false.
0578: */
0579: public Collection project(String sql, Parameterizer parameterizer,
0580: Class theInterface, Object delegate) {
0581: return new ResultSetCollection(this , parse(sql), parameterizer,
0582: new InterfaceProjector(theInterface, delegate, null));
0583: }
0584:
0585: /**
0586: * Executes SQL statement and returns collection backed by the database.
0587: * @param sql Select statement to execute
0588: * @param parameterizer Parameterizer
0589: * @param projector Projector which instantiates objects. If it is null
0590: * then projector which projects row to collection will be used
0591: * @param pageSize Maximum number of records to return
0592: * @param pageNum Number of page. Starts with 1.
0593: * @return Collection backed by the database. The collection doesn't hold
0594: * any SQL resources open, neither it keeps any refernces to created objects.
0595: * It keeps only references to the SQLProcessor, parameterizer
0596: * and projector. Thus any call to one of collection methods retursn 'fresh'
0597: * results from the database.
0598: *
0599: * Iterators created by this collection open ResultSet and close it when
0600: * Iterator.hasNext() returns false.
0601: */
0602: public Collection project(String sql, Parameterizer parameterizer,
0603: Projector projector, int pageNum, int pageSize) {
0604: return new ResultSetCollection(this , parse(sql), parameterizer,
0605: projector == null ? defaultProjector : projector,
0606: pageNum, pageSize);
0607: }
0608:
0609: /**
0610: * Executes SQL statement and returns collection backed by the database.
0611: * @param sql Select statement to execute
0612: * @param parameterizer Parameterizer
0613: * @param theInterface Interface to implement
0614: * @param pageSize Maximum number of records to return
0615: * @param pageNum Number of page. Starts with 1.
0616: * @return Collection backed by the database. The collection doesn't hold
0617: * any SQL resources open, neither it keeps any refernces to created objects.
0618: * It keeps only references to the SQLProcessor, parameterizer
0619: * and projector. Thus any call to one of collection methods retursn 'fresh'
0620: * results from the database.
0621: *
0622: * Iterators created by this collection open ResultSet and close it when
0623: * Iterator.hasNext() returns false.
0624: */
0625: public Collection project(String sql, Parameterizer parameterizer,
0626: Class theInterface, int pageNum, int pageSize) {
0627: return new ResultSetCollection(this , parse(sql), parameterizer,
0628: new InterfaceProjector(theInterface, null), pageNum,
0629: pageSize);
0630: }
0631:
0632: /**
0633: * Executes SQL statement and returns collection backed by the database.
0634: * @param sql Select statement to execute
0635: * @param parameterizer Parameterizer
0636: * @param theInterface Interface to implement
0637: * @param delegate Object to delegate invocations which didn't match field getters/setters
0638: * @param pageSize Maximum number of records to return
0639: * @param pageNum Number of page. Starts with 1.
0640: * @return Collection backed by the database. The collection doesn't hold
0641: * any SQL resources open, neither it keeps any refernces to created objects.
0642: * It keeps only references to the SQLProcessor, parameterizer
0643: * and projector. Thus any call to one of collection methods retursn 'fresh'
0644: * results from the database.
0645: *
0646: * Iterators created by this collection open ResultSet and close it when
0647: * Iterator.hasNext() returns false.
0648: */
0649: public Collection project(String sql, Parameterizer parameterizer,
0650: Class theInterface, Object delegate, int pageNum,
0651: int pageSize) {
0652: return new ResultSetCollection(this , parse(sql), parameterizer,
0653: new InterfaceProjector(theInterface, delegate, null),
0654: pageNum, pageSize);
0655: }
0656:
0657: /**
0658: * Executes SQL statement and puts results to receiver
0659: * @param sql Select statement to execute
0660: * @param parameterizer Parameterizer
0661: * @param projector Projector which instantiates objects. If it is null then
0662: * projector which projects row to collection will be used
0663: * @param receiver Collection to put results to
0664: * @return receiver with added objects. Convenient for calls like
0665: * Iterator it=processor.project(..., new LinkedList());
0666: * @throws SQLException
0667: */
0668: public Collection project(final String sql,
0669: final Parameterizer parameterizer,
0670: final Projector projector, final Collection receiver)
0671: throws SQLException {
0672: processSelect(sql, parameterizer, new RowProcessor() {
0673: public boolean process(ResultSet rs) throws SQLException {
0674: Object o = (projector == null ? defaultProjector
0675: : projector).project(rs);
0676: if (o instanceof DataAccessObject) {
0677: ((DataAccessObject) o)
0678: .setSQLProcessor(SQLProcessor.this );
0679: }
0680: receiver.add(o);
0681: return true;
0682: }
0683: });
0684:
0685: return receiver;
0686: }
0687:
0688: /**
0689: * Executes SQL statement and puts results to receiver
0690: * @param sql Select statement to execute
0691: * @param parameterizer Parameterizer
0692: * @param theInterface Interface to implement
0693: * @param receiver Collection to put results to
0694: * @return receiver with added objects. Convenient for calls like
0695: * Iterator it=processor.project(..., new LinkedList());
0696: * @throws SQLException
0697: */
0698: public Collection project(final String sql,
0699: final Parameterizer parameterizer,
0700: final Class theInterface, final Collection receiver)
0701: throws SQLException {
0702: final Projector projector = new InterfaceProjector(
0703: theInterface, null);
0704: processSelect(sql, parameterizer, new RowProcessor() {
0705: public boolean process(ResultSet rs) throws SQLException {
0706: Object o = projector.project(rs);
0707: if (o instanceof DataAccessObject) {
0708: ((DataAccessObject) o)
0709: .setSQLProcessor(SQLProcessor.this );
0710: }
0711: receiver.add(o);
0712: return true;
0713: }
0714: });
0715:
0716: return receiver;
0717: }
0718:
0719: /**
0720: * Executes SQL statement and puts results to receiver
0721: * @param sql Select statement to execute
0722: * @param parameterizer Parameterizer
0723: * @param theInterface Interface to implement
0724: * @param delegate Object to delegate invocations which didn't match field getters/setters.
0725: * @param receiver Collection to put results to
0726: * @return receiver with added objects. Convenient for calls like
0727: * Iterator it=processor.project(..., new LinkedList());
0728: * @throws SQLException
0729: */
0730: public Collection project(final String sql,
0731: final Parameterizer parameterizer,
0732: final Class theInterface, Object delegate,
0733: final Collection receiver) throws SQLException {
0734: final Projector projector = new InterfaceProjector(
0735: theInterface, delegate, null);
0736: processSelect(sql, parameterizer, new RowProcessor() {
0737: public boolean process(ResultSet rs) throws SQLException {
0738: Object o = projector.project(rs);
0739: if (o instanceof DataAccessObject) {
0740: ((DataAccessObject) o)
0741: .setSQLProcessor(SQLProcessor.this );
0742: }
0743: receiver.add(o);
0744: return true;
0745: }
0746: });
0747:
0748: return receiver;
0749: }
0750:
0751: /**
0752: * Executes SQL statement and puts results to receiver
0753: * @param sql Select statement to execute
0754: * @param parameterizer Parameterizer
0755: * @param projector Projector which instantiates objects. If it is null then
0756: * projector which projects row to collection will be used.
0757: * @param receiver Collection to put results to
0758: * @param pageSize Maximum number of records to return
0759: * @param pageNum Number of page. Starts with 1.
0760: * @return receiver with added objects. Convenient for calls like
0761: * Iterator it=processor.project(..., new LinkedList());
0762: * @throws SQLException
0763: */
0764: public Collection project(final String sql,
0765: final Parameterizer parameterizer,
0766: final Projector projector, final Collection receiver,
0767: final int pageSize, final int pageNum) throws SQLException {
0768: final int[] counter = { 0 };
0769:
0770: processSelect(sql, parameterizer, new RowProcessor() {
0771: public boolean process(ResultSet rs) throws SQLException {
0772: if (++counter[0] > (pageNum - 1) * pageSize
0773: && counter[0] <= pageNum * pageSize) {
0774: Object o = (projector == null ? defaultProjector
0775: : projector).project(rs);
0776: if (o instanceof DataAccessObject) {
0777: ((DataAccessObject) o)
0778: .setSQLProcessor(SQLProcessor.this );
0779: }
0780: receiver.add(o);
0781: }
0782: return true;
0783: }
0784: });
0785:
0786: return receiver;
0787: }
0788:
0789: private Projector defaultProjector = new Projector() {
0790: public Object project(ResultSet rs) throws SQLException {
0791: Collection ret = new ArrayList();
0792: for (int i = 1, j = rs.getMetaData().getColumnCount(); i <= j; i++) {
0793: ret.add(rs.getObject(i));
0794: }
0795: return ret;
0796: }
0797: };
0798:
0799: /**
0800: * @param string
0801: * @param parameterizer
0802: * @param projector Projector. If it is null then projector which projects
0803: * row to collection will be used.
0804: * @return
0805: * @throws SQLException
0806: */
0807: public Object projectSingleObject(String sql,
0808: Parameterizer parameterizer, final Projector projector)
0809: throws SQLException {
0810: final Object[] ret = { null };
0811: processSelect(sql, parameterizer, new RowProcessor() {
0812: public boolean process(ResultSet rs) throws SQLException {
0813: ret[0] = (projector == null ? defaultProjector
0814: : projector).project(rs);
0815: if (ret[0] instanceof DataAccessObject) {
0816: ((DataAccessObject) ret[0])
0817: .setSQLProcessor(SQLProcessor.this );
0818: }
0819: return false;
0820: }
0821: });
0822:
0823: return ret[0];
0824: }
0825:
0826: /**
0827: * Executes query and injects values from the first row to target object.
0828: * @param string
0829: * @param parameterizer
0830: * @param target Object to inject values to
0831: * @throws SQLException
0832: */
0833: public void inject(String sql, Parameterizer parameterizer,
0834: final Map columnMap, final Object target)
0835: throws SQLException {
0836: processSelect(sql, parameterizer, new RowProcessor() {
0837: public boolean process(ResultSet rs) throws SQLException {
0838: ResultSetMetaData metaData = rs.getMetaData();
0839: Map contextMap = new HashMap();
0840: for (int i = 1, cc = metaData.getColumnCount(); i <= cc; i++) {
0841: String colName = metaData.getColumnName(i);
0842: String propertyName = BaseReflectionProjector
0843: .propertyName(colName);
0844:
0845: if (columnMap != null
0846: && columnMap.containsKey(propertyName)) {
0847: propertyName = (String) columnMap
0848: .get(propertyName);
0849: }
0850:
0851: if (propertyName != null) {
0852: contextMap.put(propertyName, rs
0853: .getObject(colName));
0854: }
0855: }
0856:
0857: try {
0858: DomConfigFactory.inject(target, new MapContext(
0859: contextMap));
0860: } catch (ConfigurationException e) {
0861: throw new SQLExceptionEx(e);
0862: }
0863: return false;
0864: }
0865: });
0866: }
0867:
0868: /**
0869: * @param sql
0870: * @param parameterizer
0871: * @return Object representing first row
0872: * @throws SQLException
0873: */
0874: public Object projectSingleObject(String sql,
0875: Parameterizer parameterizer, Class theInterface)
0876: throws SQLException {
0877: final Projector projector = new InterfaceProjector(
0878: theInterface, null);
0879: final Object[] ret = { null };
0880: processSelect(sql, parameterizer, new RowProcessor() {
0881: public boolean process(ResultSet rs) throws SQLException {
0882: ret[0] = projector.project(rs);
0883: if (ret[0] instanceof DataAccessObject) {
0884: ((DataAccessObject) ret[0])
0885: .setSQLProcessor(SQLProcessor.this );
0886: }
0887: return false;
0888: }
0889: });
0890:
0891: return ret[0];
0892: }
0893:
0894: /**
0895: * @param sql
0896: * @param parameterizer
0897: * @return boolean value of the first column of the first row or 'false' if there are no rows.
0898: * @throws SQLException
0899: */
0900: public boolean projectSingleBoolean(String sql,
0901: Parameterizer parameterizer) throws SQLException {
0902: final boolean[] ret = { false };
0903: processSelect(sql, parameterizer, new RowProcessor() {
0904: public boolean process(ResultSet rs) throws SQLException {
0905: ret[0] = rs.getBoolean(1);
0906: return false;
0907: }
0908: });
0909:
0910: return ret[0];
0911: }
0912:
0913: /**
0914: * @param sql
0915: * @param parameterizer
0916: * @return byte value of the first column of the first row or 0 if there are no rows.
0917: * @throws SQLException
0918: */
0919: public byte projectSingleByte(String sql,
0920: Parameterizer parameterizer) throws SQLException {
0921: final byte[] ret = { 0 };
0922: processSelect(sql, parameterizer, new RowProcessor() {
0923: public boolean process(ResultSet rs) throws SQLException {
0924: ret[0] = rs.getByte(1);
0925: return false;
0926: }
0927: });
0928:
0929: return ret[0];
0930: }
0931:
0932: /**
0933: * @param sql
0934: * @param parameterizer
0935: * @return byte[] value of the first column of the first row or null if there are no rows.
0936: * @throws SQLException
0937: */
0938: public byte[] projectSingleBytes(String sql,
0939: Parameterizer parameterizer) throws SQLException {
0940: final byte[][] ret = { null };
0941: processSelect(sql, parameterizer, new RowProcessor() {
0942: public boolean process(ResultSet rs) throws SQLException {
0943: ret[0] = rs.getBytes(1);
0944: return false;
0945: }
0946: });
0947:
0948: return ret[0];
0949: }
0950:
0951: /**
0952: * @param sql
0953: * @param parameterizer
0954: * @return int value of the first column of the first row or 0 if there are no rows.
0955: * @throws SQLException
0956: */
0957: public int projectSingleInt(String sql, Parameterizer parameterizer)
0958: throws SQLException {
0959: final int[] ret = { 0 };
0960: processSelect(sql, parameterizer, new RowProcessor() {
0961: public boolean process(ResultSet rs) throws SQLException {
0962: ret[0] = rs.getInt(1);
0963: return false;
0964: }
0965: });
0966:
0967: return ret[0];
0968: }
0969:
0970: /**
0971: * @param sql
0972: * @param parameterizer
0973: * @return short value of the first column of the first row or 0 if there are no rows.
0974: * @throws SQLException
0975: */
0976: public short projectSingleShort(String sql,
0977: Parameterizer parameterizer) throws SQLException {
0978: final short[] ret = { 0 };
0979: processSelect(sql, parameterizer, new RowProcessor() {
0980: public boolean process(ResultSet rs) throws SQLException {
0981: ret[0] = rs.getShort(1);
0982: return false;
0983: }
0984: });
0985:
0986: return ret[0];
0987: }
0988:
0989: /**
0990: * @param sql
0991: * @param parameterizer
0992: * @return double value of the first column of the first row or 0 if there are no rows.
0993: * @throws SQLException
0994: */
0995: public double projectSingleDouble(String sql,
0996: Parameterizer parameterizer) throws SQLException {
0997: final double[] ret = { 0 };
0998: processSelect(sql, parameterizer, new RowProcessor() {
0999: public boolean process(ResultSet rs) throws SQLException {
1000: ret[0] = rs.getDouble(1);
1001: return false;
1002: }
1003: });
1004:
1005: return ret[0];
1006: }
1007:
1008: /**
1009: * @param sql
1010: * @param parameterizer
1011: * @return float value of the first column of the first row or 0 if there are no rows.
1012: * @throws SQLException
1013: */
1014: public float projectSingleFloat(String sql,
1015: Parameterizer parameterizer) throws SQLException {
1016: final float[] ret = { 0 };
1017: processSelect(sql, parameterizer, new RowProcessor() {
1018: public boolean process(ResultSet rs) throws SQLException {
1019: ret[0] = rs.getFloat(1);
1020: return false;
1021: }
1022: });
1023:
1024: return ret[0];
1025: }
1026:
1027: /**
1028: * @param sql
1029: * @param parameterizer
1030: * @return long value of the first column of the first row or 0 if there are no rows.
1031: * @throws SQLException
1032: */
1033: public long projectSingleLong(String sql,
1034: Parameterizer parameterizer) throws SQLException {
1035: final long[] ret = { 0 };
1036: processSelect(sql, parameterizer, new RowProcessor() {
1037: public boolean process(ResultSet rs) throws SQLException {
1038: ret[0] = rs.getLong(1);
1039: return false;
1040: }
1041: });
1042:
1043: return ret[0];
1044: }
1045:
1046: /**
1047: * @param sql
1048: * @param parameterizer
1049: * @return String value of the first column of the first row or null if there are no rows.
1050: * @throws SQLException
1051: */
1052: public String projectSingleString(String sql,
1053: Parameterizer parameterizer) throws SQLException {
1054: final String[] ret = { null };
1055: processSelect(sql, parameterizer, new RowProcessor() {
1056: public boolean process(ResultSet rs) throws SQLException {
1057: ret[0] = rs.getString(1);
1058: return false;
1059: }
1060: });
1061:
1062: return ret[0];
1063: }
1064:
1065: /**
1066: * @param sql
1067: * @param parameterizer
1068: * @return object value of the first column of the first row or null if there are no rows.
1069: * @throws SQLException
1070: */
1071: public Object projectSingleObject(String sql,
1072: Parameterizer parameterizer) throws SQLException {
1073: final Object[] ret = { null };
1074: processSelect(sql, parameterizer, new RowProcessor() {
1075: public boolean process(ResultSet rs) throws SQLException {
1076: ret[0] = rs.getObject(1);
1077: return false;
1078: }
1079: });
1080:
1081: return ret[0];
1082: }
1083:
1084: private static Map methodMap = new HashMap();
1085:
1086: static {
1087: methodMap.put("boolean", "projectSingleBoolean");
1088: methodMap.put("byte", "projectSingleByte");
1089: methodMap.put("byte[]", "projectSingleBytes");
1090: methodMap.put("char", "projectSingleChar");
1091: methodMap.put("int", "projectSingleInt");
1092: methodMap.put("short", "projectSingleShort");
1093: methodMap.put("double", "projectSingleDouble");
1094: methodMap.put("float", "projectSingleFloat");
1095: methodMap.put("long", "projectSingleLong");
1096: methodMap.put("java.lang.String", "projectSingleString");
1097: methodMap.put("java.lang.Object", "projectSingleObject");
1098: }
1099:
1100: /**
1101: * Finds projectSingleXXX method for a particular type.
1102: * @param className
1103: * @return
1104: */
1105: public static String findProjectSingleMethodName(String className) {
1106: return (String) methodMap.get(className);
1107: }
1108:
1109: /**
1110: * @param sql
1111: * @param parameterizer
1112: * @return char value of the first column of the first row or 0 if there are no rows.
1113: * @throws SQLException
1114: */
1115: public char projectSingleChar(String sql,
1116: Parameterizer parameterizer) throws SQLException {
1117: final char[] ret = { 0 };
1118: processSelect(sql, parameterizer, new RowProcessor() {
1119: public boolean process(ResultSet rs) throws SQLException {
1120: String str = rs.getString(1);
1121: if (str != null && str.length() > 0) {
1122: ret[0] = str.charAt(0);
1123: }
1124: return false;
1125: }
1126: });
1127:
1128: return ret[0];
1129: }
1130:
1131: /**
1132: * @param string
1133: * @param parameterizer
1134: * @return
1135: * @throws SQLException
1136: */
1137: public Object projectSingleObject(String sql,
1138: Parameterizer parameterizer, Class theInterface,
1139: Object delegate) throws SQLException {
1140: final Projector projector = new InterfaceProjector(
1141: theInterface, delegate, null);
1142: final Object[] ret = { null };
1143: processSelect(sql, parameterizer, new RowProcessor() {
1144: public boolean process(ResultSet rs) throws SQLException {
1145: ret[0] = projector.project(rs);
1146: if (ret[0] instanceof DataAccessObject) {
1147: ((DataAccessObject) ret[0])
1148: .setSQLProcessor(SQLProcessor.this );
1149: }
1150: return false;
1151: }
1152: });
1153:
1154: return ret[0];
1155: }
1156:
1157: /**
1158: * Generates primary key.
1159: * @param primaryKeysTable Table holding primary keys counters. DDL:
1160: * <PRE>CREATE TABLE <I>table name</I> (
1161: KEY_NAME VARCHAR(50) NOT NULL
1162: , KEY_VALUE INTEGER DEFAULT '0' NOT NULL
1163: , PRIMARY KEY (KEY_NAME)
1164: );</PRE>
1165: * @param keyName Key name
1166: * @return
1167: * @throws SQLException
1168: */
1169: public int nextPK(final String primaryKeysTable,
1170: final String keyName) throws SQLException {
1171: final Connection con = getConnection();
1172: try {
1173: boolean ac = con.getAutoCommit();
1174: try {
1175: con.setAutoCommit(false);
1176: int value = nextPK(con, primaryKeysTable, keyName);
1177: con.commit();
1178: return value;
1179: } catch (SQLException e) {
1180: con.rollback();
1181: throw e;
1182: } finally {
1183: con.setAutoCommit(ac);
1184: }
1185: } finally {
1186: releaseConnection(con);
1187: }
1188: }
1189:
1190: /**
1191: * @param con
1192: * @param primaryKeysTable
1193: * @param keyName
1194: * @return
1195: * @throws SQLException
1196: */
1197: public int nextPK(final Connection con,
1198: final String primaryKeysTable, final String keyName)
1199: throws SQLException {
1200: final Parameterizer parameterizer = new Parameterizer() {
1201: public void parameterize(PreparedStatement preparedStatement)
1202: throws SQLException {
1203: preparedStatement.setString(1, keyName);
1204: }
1205: };
1206:
1207: final int[] value = { 0 };
1208:
1209: processSelect(con, "SELECT KEY_VALUE FROM " + primaryKeysTable
1210: + " WHERE KEY_NAME=?", parameterizer,
1211: new RowProcessorEx() {
1212: public boolean process(ResultSet resultSet)
1213: throws SQLException {
1214: value[0] = resultSet.getInt("KEY_VALUE") + 1;
1215: processUpdate(
1216: con,
1217: "UPDATE "
1218: + primaryKeysTable
1219: + " SET KEY_VALUE=KEY_VALUE+1 WHERE KEY_NAME=?",
1220: parameterizer);
1221: return false;
1222: }
1223:
1224: public void onEmptyResultSet() throws SQLException {
1225: processUpdate(
1226: con,
1227: "INSERT INTO "
1228: + primaryKeysTable
1229: + " (KEY_NAME, KEY_VALUE) VALUES (?, 0)",
1230: parameterizer);
1231: }
1232: });
1233: return value[0];
1234: }
1235:
1236: interface Person {
1237: String getFirstName();
1238:
1239: String getLastName();
1240: }
1241:
1242: public static void main(final String[] args) throws Exception {
1243: doCool();
1244: doDull();
1245: }
1246:
1247: /**
1248: * @throws ClassNotFoundException
1249: * @throws SQLException
1250: */
1251: private static void doDull() throws ClassNotFoundException,
1252: SQLException {
1253: Class.forName("org.hsqldb.jdbcDriver");
1254: Connection con = DriverManager.getConnection("jdbc:hsqldb:.",
1255: "sa", "");
1256: try {
1257: Statement st = con.createStatement();
1258: try {
1259: //st.executeUpdate("create table people (first_name varchar(200), last_name varchar(200))");
1260: } finally {
1261: st.close();
1262: }
1263:
1264: PreparedStatement ps = con
1265: .prepareStatement("insert into people (first_name, last_name) values (?, ?)");
1266: try {
1267: for (int i = 0; i < 20; i++) {
1268: ps.setString(1, "Pavel-" + i);
1269: ps.setString(2, "Vlasov");
1270: ps.execute();
1271: }
1272: } finally {
1273: ps.close();
1274: }
1275:
1276: Statement st1 = con.createStatement();
1277: try {
1278: st1
1279: .executeUpdate("insert into people (first_name, last_name) values ('Olga', 'Vlasov')");
1280: } finally {
1281: st1.close();
1282: }
1283:
1284: Statement st2 = con.createStatement();
1285: try {
1286: ResultSet rs = st2.executeQuery("select * from people");
1287: try {
1288: while (rs.next()) {
1289: System.out.println(rs.getString("FIRST_NAME")
1290: + " " + rs.getString("LAST_NAME"));
1291: }
1292: } finally {
1293: rs.close();
1294: }
1295: } finally {
1296: st2.close();
1297: }
1298: } finally {
1299: con.close();
1300: }
1301: }
1302:
1303: /**
1304: * @throws ClassNotFoundException
1305: * @throws IOException
1306: * @throws SQLException
1307: */
1308: private static void doCool() throws ClassNotFoundException,
1309: IOException, SQLException {
1310: HypersonicInMemoryDataSource ds = new HypersonicInMemoryDataSource(
1311: (Reader) null);
1312: try {
1313: SQLProcessor processor = new SQLProcessor(ds, null);
1314: processor
1315: .processUpdate(
1316: "create table people (first_name varchar(200), last_name varchar(200))",
1317: null);
1318:
1319: final int[] counter = { 0 };
1320: for (int i = 0; i < 20; i++) {
1321: processor
1322: .processUpdate(
1323: "insert into people (first_name, last_name) values (?, ?)",
1324: new Parameterizer() {
1325: public void parameterize(
1326: PreparedStatement ps)
1327: throws SQLException {
1328: ps.setString(1, "Dhawal "
1329: + ++counter[0]);
1330: ps.setString(2, "Manwatkar");
1331: }
1332: });
1333: }
1334:
1335: processor
1336: .processUpdate(
1337: "insert into people (first_name, last_name) values ('Pavel', 'Vlasov')",
1338: null);
1339:
1340: processor.processSelect("select * from people", null,
1341: new RowProcessor() {
1342: public boolean process(ResultSet rs)
1343: throws SQLException {
1344: System.out.println(rs
1345: .getString("FIRST_NAME")
1346: + " " + rs.getString("LAST_NAME"));
1347: return true;
1348: }
1349: });
1350:
1351: } finally {
1352: ds.shutdown();
1353: }
1354: }
1355:
1356: public TimeIntervalCategory getTimeIntervalCategory() {
1357: return timeIntervalCategory;
1358: }
1359:
1360: public void setTimeIntervalCategory(
1361: TimeIntervalCategory timeIntervalCategory) {
1362: this .timeIntervalCategory = timeIntervalCategory;
1363: }
1364:
1365: public Context getNameMap() {
1366: return nameMap;
1367: }
1368:
1369: public void executeTransaction(Transaction transaction)
1370: throws SQLException {
1371: Connection con = getConnection();
1372: try {
1373: boolean ac = con.getAutoCommit();
1374: try {
1375: con.setAutoCommit(false);
1376: SQLProcessor processor = new SQLProcessor(con, nameMap);
1377: try {
1378: if (transaction.execute(processor)) {
1379: con.commit();
1380: } else {
1381: con.rollback();
1382: }
1383: } catch (SQLException e) {
1384: con.rollback();
1385: throw e;
1386: }
1387: } finally {
1388: con.setAutoCommit(ac);
1389: }
1390: } finally {
1391: releaseConnection(con);
1392: }
1393: }
1394:
1395: protected DataSource getDataSource() {
1396: return dataSource;
1397: }
1398: }
|