0001: /*
0002: * $Id: Sql.java 4066 2006-09-20 17:26:36Z glaforge $
0003: *
0004: * Copyright 2003 (C) James Strachan and Bob Mcwhirter. All Rights Reserved.
0005: *
0006: * Redistribution and use of this software and associated documentation
0007: * ("Software"), with or without modification, are permitted provided that the
0008: * following conditions are met: 1. Redistributions of source code must retain
0009: * copyright statements and notices. Redistributions must also contain a copy
0010: * of this document. 2. Redistributions in binary form must reproduce the above
0011: * copyright notice, this list of conditions and the following disclaimer in
0012: * the documentation and/or other materials provided with the distribution. 3.
0013: * The name "groovy" must not be used to endorse or promote products derived
0014: * from this Software without prior written permission of The Codehaus. For
0015: * written permission, please contact info@codehaus.org. 4. Products derived
0016: * from this Software may not be called "groovy" nor may "groovy" appear in
0017: * their names without prior written permission of The Codehaus. "groovy" is a
0018: * registered trademark of The Codehaus. 5. Due credit should be given to The
0019: * Codehaus - http://groovy.codehaus.org/
0020: *
0021: * THIS SOFTWARE IS PROVIDED BY THE CODEHAUS AND CONTRIBUTORS ``AS IS'' AND ANY
0022: * EXPRESSED OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
0023: * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
0024: * DISCLAIMED. IN NO EVENT SHALL THE CODEHAUS OR ITS CONTRIBUTORS BE LIABLE FOR
0025: * ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL
0026: * DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR
0027: * SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER
0028: * CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT
0029: * LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY
0030: * OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH
0031: * DAMAGE.
0032: *
0033: */
0034: package groovy.sql;
0035:
0036: import groovy.lang.Closure;
0037: import groovy.lang.GString;
0038:
0039: import java.security.AccessController;
0040: import java.security.PrivilegedActionException;
0041: import java.security.PrivilegedExceptionAction;
0042: import java.sql.CallableStatement;
0043: import java.sql.Connection;
0044: import java.sql.DriverManager;
0045: import java.sql.PreparedStatement;
0046: import java.sql.ResultSet;
0047: import java.sql.ResultSetMetaData;
0048: import java.sql.SQLException;
0049: import java.sql.Statement;
0050: import java.sql.Types;
0051: import java.util.ArrayList;
0052: import java.util.Collections;
0053: import java.util.Iterator;
0054: import java.util.List;
0055: import java.util.LinkedHashMap;
0056: import java.util.Properties;
0057: import java.util.logging.Level;
0058: import java.util.logging.Logger;
0059: import java.util.regex.Matcher;
0060: import java.util.regex.Pattern;
0061:
0062: import javax.sql.DataSource;
0063:
0064: /**
0065: * Represents an extent of objects
0066: *
0067: * @author Chris Stevenson
0068: * @author <a href="mailto:james@coredevelopers.net">James Strachan </a>
0069: * @version $Revision: 4066 $
0070: */
0071: public class Sql {
0072:
0073: protected Logger log = Logger.getLogger(getClass().getName());
0074:
0075: private DataSource dataSource;
0076:
0077: private Connection useConnection;
0078:
0079: /** lets only warn of using deprecated methods once */
0080: private boolean warned;
0081:
0082: // store the last row count for executeUpdate
0083: int updateCount = 0;
0084:
0085: /** allows a closure to be used to configure the statement before its use */
0086: private Closure configureStatement;
0087:
0088: /**
0089: * A helper method which creates a new Sql instance from a JDBC connection
0090: * URL
0091: *
0092: * @param url
0093: * @return a new Sql instance with a connection
0094: */
0095: public static Sql newInstance(String url) throws SQLException {
0096: Connection connection = DriverManager.getConnection(url);
0097: return new Sql(connection);
0098: }
0099:
0100: /**
0101: * A helper method which creates a new Sql instance from a JDBC connection
0102: * URL
0103: *
0104: * @param url
0105: * @return a new Sql instance with a connection
0106: */
0107: public static Sql newInstance(String url, Properties properties)
0108: throws SQLException {
0109: Connection connection = DriverManager.getConnection(url,
0110: properties);
0111: return new Sql(connection);
0112: }
0113:
0114: /**
0115: * A helper method which creates a new Sql instance from a JDBC connection
0116: * URL and driver class name
0117: *
0118: * @param url
0119: * @return a new Sql instance with a connection
0120: */
0121: public static Sql newInstance(String url, Properties properties,
0122: String driverClassName) throws SQLException,
0123: ClassNotFoundException {
0124: loadDriver(driverClassName);
0125: return newInstance(url, properties);
0126: }
0127:
0128: /**
0129: * A helper method which creates a new Sql instance from a JDBC connection
0130: * URL, username and password
0131: *
0132: * @param url
0133: * @return a new Sql instance with a connection
0134: */
0135: public static Sql newInstance(String url, String user,
0136: String password) throws SQLException {
0137: Connection connection = DriverManager.getConnection(url, user,
0138: password);
0139: return new Sql(connection);
0140: }
0141:
0142: /**
0143: * A helper method which creates a new Sql instance from a JDBC connection
0144: * URL, username, password and driver class name
0145: *
0146: * @param url
0147: * @return a new Sql instance with a connection
0148: */
0149: public static Sql newInstance(String url, String user,
0150: String password, String driverClassName)
0151: throws SQLException, ClassNotFoundException {
0152: loadDriver(driverClassName);
0153: return newInstance(url, user, password);
0154: }
0155:
0156: /**
0157: * A helper method which creates a new Sql instance from a JDBC connection
0158: * URL and driver class name
0159: *
0160: * @param url
0161: * @param driverClassName
0162: * the class name of the driver
0163: * @return a new Sql instance with a connection
0164: */
0165: public static Sql newInstance(String url, String driverClassName)
0166: throws SQLException, ClassNotFoundException {
0167: loadDriver(driverClassName);
0168: return newInstance(url);
0169: }
0170:
0171: /**
0172: * Attempts to load the JDBC driver on the thread, current or system class
0173: * loaders
0174: *
0175: * @param driverClassName
0176: * @throws ClassNotFoundException
0177: */
0178: public static void loadDriver(String driverClassName)
0179: throws ClassNotFoundException {
0180: // lets try the thread context class loader first
0181: // lets try to use the system class loader
0182: try {
0183: Class.forName(driverClassName);
0184: } catch (ClassNotFoundException e) {
0185: try {
0186: Thread.currentThread().getContextClassLoader()
0187: .loadClass(driverClassName);
0188: } catch (ClassNotFoundException e2) {
0189: // now lets try the classloader which loaded us
0190: try {
0191: Sql.class.getClassLoader().loadClass(
0192: driverClassName);
0193: } catch (ClassNotFoundException e3) {
0194: throw e;
0195: }
0196: }
0197: }
0198: }
0199:
0200: public static final OutParameter ARRAY = new OutParameter() {
0201: public int getType() {
0202: return Types.ARRAY;
0203: }
0204: };
0205: public static final OutParameter BIGINT = new OutParameter() {
0206: public int getType() {
0207: return Types.BIGINT;
0208: }
0209: };
0210: public static final OutParameter BINARY = new OutParameter() {
0211: public int getType() {
0212: return Types.BINARY;
0213: }
0214: };
0215: public static final OutParameter BIT = new OutParameter() {
0216: public int getType() {
0217: return Types.BIT;
0218: }
0219: };
0220: public static final OutParameter BLOB = new OutParameter() {
0221: public int getType() {
0222: return Types.BLOB;
0223: }
0224: };
0225: public static final OutParameter BOOLEAN = new OutParameter() {
0226: public int getType() {
0227: return Types.BOOLEAN;
0228: }
0229: };
0230: public static final OutParameter CHAR = new OutParameter() {
0231: public int getType() {
0232: return Types.CHAR;
0233: }
0234: };
0235: public static final OutParameter CLOB = new OutParameter() {
0236: public int getType() {
0237: return Types.CLOB;
0238: }
0239: };
0240: public static final OutParameter DATALINK = new OutParameter() {
0241: public int getType() {
0242: return Types.DATALINK;
0243: }
0244: };
0245: public static final OutParameter DATE = new OutParameter() {
0246: public int getType() {
0247: return Types.DATE;
0248: }
0249: };
0250: public static final OutParameter DECIMAL = new OutParameter() {
0251: public int getType() {
0252: return Types.DECIMAL;
0253: }
0254: };
0255: public static final OutParameter DISTINCT = new OutParameter() {
0256: public int getType() {
0257: return Types.DISTINCT;
0258: }
0259: };
0260: public static final OutParameter DOUBLE = new OutParameter() {
0261: public int getType() {
0262: return Types.DOUBLE;
0263: }
0264: };
0265: public static final OutParameter FLOAT = new OutParameter() {
0266: public int getType() {
0267: return Types.FLOAT;
0268: }
0269: };
0270: public static final OutParameter INTEGER = new OutParameter() {
0271: public int getType() {
0272: return Types.INTEGER;
0273: }
0274: };
0275: public static final OutParameter JAVA_OBJECT = new OutParameter() {
0276: public int getType() {
0277: return Types.JAVA_OBJECT;
0278: }
0279: };
0280: public static final OutParameter LONGVARBINARY = new OutParameter() {
0281: public int getType() {
0282: return Types.LONGVARBINARY;
0283: }
0284: };
0285: public static final OutParameter LONGVARCHAR = new OutParameter() {
0286: public int getType() {
0287: return Types.LONGVARCHAR;
0288: }
0289: };
0290: public static final OutParameter NULL = new OutParameter() {
0291: public int getType() {
0292: return Types.NULL;
0293: }
0294: };
0295: public static final OutParameter NUMERIC = new OutParameter() {
0296: public int getType() {
0297: return Types.NUMERIC;
0298: }
0299: };
0300: public static final OutParameter OTHER = new OutParameter() {
0301: public int getType() {
0302: return Types.OTHER;
0303: }
0304: };
0305: public static final OutParameter REAL = new OutParameter() {
0306: public int getType() {
0307: return Types.REAL;
0308: }
0309: };
0310: public static final OutParameter REF = new OutParameter() {
0311: public int getType() {
0312: return Types.REF;
0313: }
0314: };
0315: public static final OutParameter SMALLINT = new OutParameter() {
0316: public int getType() {
0317: return Types.SMALLINT;
0318: }
0319: };
0320: public static final OutParameter STRUCT = new OutParameter() {
0321: public int getType() {
0322: return Types.STRUCT;
0323: }
0324: };
0325: public static final OutParameter TIME = new OutParameter() {
0326: public int getType() {
0327: return Types.TIME;
0328: }
0329: };
0330: public static final OutParameter TIMESTAMP = new OutParameter() {
0331: public int getType() {
0332: return Types.TIMESTAMP;
0333: }
0334: };
0335: public static final OutParameter TINYINT = new OutParameter() {
0336: public int getType() {
0337: return Types.TINYINT;
0338: }
0339: };
0340: public static final OutParameter VARBINARY = new OutParameter() {
0341: public int getType() {
0342: return Types.VARBINARY;
0343: }
0344: };
0345: public static final OutParameter VARCHAR = new OutParameter() {
0346: public int getType() {
0347: return Types.VARCHAR;
0348: }
0349: };
0350:
0351: public static InParameter ARRAY(Object value) {
0352: return in(Types.ARRAY, value);
0353: }
0354:
0355: public static InParameter BIGINT(Object value) {
0356: return in(Types.BIGINT, value);
0357: }
0358:
0359: public static InParameter BINARY(Object value) {
0360: return in(Types.BINARY, value);
0361: }
0362:
0363: public static InParameter BIT(Object value) {
0364: return in(Types.BIT, value);
0365: }
0366:
0367: public static InParameter BLOB(Object value) {
0368: return in(Types.BLOB, value);
0369: }
0370:
0371: public static InParameter BOOLEAN(Object value) {
0372: return in(Types.BOOLEAN, value);
0373: }
0374:
0375: public static InParameter CHAR(Object value) {
0376: return in(Types.CHAR, value);
0377: }
0378:
0379: public static InParameter CLOB(Object value) {
0380: return in(Types.CLOB, value);
0381: }
0382:
0383: public static InParameter DATALINK(Object value) {
0384: return in(Types.DATALINK, value);
0385: }
0386:
0387: public static InParameter DATE(Object value) {
0388: return in(Types.DATE, value);
0389: }
0390:
0391: public static InParameter DECIMAL(Object value) {
0392: return in(Types.DECIMAL, value);
0393: }
0394:
0395: public static InParameter DISTINCT(Object value) {
0396: return in(Types.DISTINCT, value);
0397: }
0398:
0399: public static InParameter DOUBLE(Object value) {
0400: return in(Types.DOUBLE, value);
0401: }
0402:
0403: public static InParameter FLOAT(Object value) {
0404: return in(Types.FLOAT, value);
0405: }
0406:
0407: public static InParameter INTEGER(Object value) {
0408: return in(Types.INTEGER, value);
0409: }
0410:
0411: public static InParameter JAVA_OBJECT(Object value) {
0412: return in(Types.JAVA_OBJECT, value);
0413: }
0414:
0415: public static InParameter LONGVARBINARY(Object value) {
0416: return in(Types.LONGVARBINARY, value);
0417: }
0418:
0419: public static InParameter LONGVARCHAR(Object value) {
0420: return in(Types.LONGVARCHAR, value);
0421: }
0422:
0423: public static InParameter NULL(Object value) {
0424: return in(Types.NULL, value);
0425: }
0426:
0427: public static InParameter NUMERIC(Object value) {
0428: return in(Types.NUMERIC, value);
0429: }
0430:
0431: public static InParameter OTHER(Object value) {
0432: return in(Types.OTHER, value);
0433: }
0434:
0435: public static InParameter REAL(Object value) {
0436: return in(Types.REAL, value);
0437: }
0438:
0439: public static InParameter REF(Object value) {
0440: return in(Types.REF, value);
0441: }
0442:
0443: public static InParameter SMALLINT(Object value) {
0444: return in(Types.SMALLINT, value);
0445: }
0446:
0447: public static InParameter STRUCT(Object value) {
0448: return in(Types.STRUCT, value);
0449: }
0450:
0451: public static InParameter TIME(Object value) {
0452: return in(Types.TIME, value);
0453: }
0454:
0455: public static InParameter TIMESTAMP(Object value) {
0456: return in(Types.TIMESTAMP, value);
0457: }
0458:
0459: public static InParameter TINYINT(Object value) {
0460: return in(Types.TINYINT, value);
0461: }
0462:
0463: public static InParameter VARBINARY(Object value) {
0464: return in(Types.VARBINARY, value);
0465: }
0466:
0467: public static InParameter VARCHAR(Object value) {
0468: return in(Types.VARCHAR, value);
0469: }
0470:
0471: /**
0472: * Create a new InParameter
0473: * @param type the JDBC data type
0474: * @param value the object value
0475: * @return an InParameter
0476: */
0477: public static InParameter in(final int type, final Object value) {
0478: return new InParameter() {
0479: public int getType() {
0480: return type;
0481: }
0482:
0483: public Object getValue() {
0484: return value;
0485: }
0486: };
0487: }
0488:
0489: /**
0490: * Create a new OutParameter
0491: * @param type the JDBC data type.
0492: * @return an OutParameter
0493: */
0494: public static OutParameter out(final int type) {
0495: return new OutParameter() {
0496: public int getType() {
0497: return type;
0498: }
0499: };
0500: }
0501:
0502: /**
0503: * Create an inout parameter using this in parameter.
0504: * @param in
0505: */
0506: public static InOutParameter inout(final InParameter in) {
0507: return new InOutParameter() {
0508: public int getType() {
0509: return in.getType();
0510: }
0511:
0512: public Object getValue() {
0513: return in.getValue();
0514: }
0515: };
0516: }
0517:
0518: /**
0519: * Create a new ResultSetOutParameter
0520: * @param type the JDBC data type.
0521: * @return a ResultSetOutParameter
0522: */
0523: public static ResultSetOutParameter resultSet(final int type) {
0524: return new ResultSetOutParameter() {
0525: public int getType() {
0526: return type;
0527: }
0528: };
0529: }
0530:
0531: /**
0532: * Creates a variable to be expanded in the Sql string rather
0533: * than representing an sql parameter.
0534: * @param object
0535: */
0536: public static ExpandedVariable expand(final Object object) {
0537: return new ExpandedVariable() {
0538: public Object getObject() {
0539: return object;
0540: }
0541: };
0542: }
0543:
0544: /**
0545: * Constructs an SQL instance using the given DataSource. Each operation
0546: * will use a Connection from the DataSource pool and close it when the
0547: * operation is completed putting it back into the pool.
0548: *
0549: * @param dataSource
0550: */
0551: public Sql(DataSource dataSource) {
0552: this .dataSource = dataSource;
0553: }
0554:
0555: /**
0556: * Construts an SQL instance using the given Connection. It is the callers
0557: * responsibility to close the Connection after the Sql instance has been
0558: * used. You can do this on the connection object directly or by calling the
0559: * {@link java.sql.Connection#close()} method.
0560: *
0561: * @param connection
0562: */
0563: public Sql(Connection connection) {
0564: if (connection == null) {
0565: throw new NullPointerException(
0566: "Must specify a non-null Connection");
0567: }
0568: this .useConnection = connection;
0569: }
0570:
0571: public Sql(Sql parent) {
0572: this .dataSource = parent.dataSource;
0573: this .useConnection = parent.useConnection;
0574: }
0575:
0576: public DataSet dataSet(String table) {
0577: return new DataSet(this , table);
0578: }
0579:
0580: public DataSet dataSet(Class type) {
0581: return new DataSet(this , type);
0582: }
0583:
0584: /**
0585: * Performs the given SQL query calling the closure with the result set
0586: */
0587: public void query(String sql, Closure closure) throws SQLException {
0588: Connection connection = createConnection();
0589: Statement statement = connection.createStatement();
0590: configure(statement);
0591: ResultSet results = null;
0592: try {
0593: log.fine(sql);
0594: results = statement.executeQuery(sql);
0595: closure.call(results);
0596: } catch (SQLException e) {
0597: log.log(Level.FINE, "Failed to execute: " + sql, e);
0598: throw e;
0599: } finally {
0600: closeResources(connection, statement, results);
0601: }
0602: }
0603:
0604: /**
0605: * Performs the given SQL query with parameters calling the closure with the
0606: * result set
0607: */
0608: public void query(String sql, List params, Closure closure)
0609: throws SQLException {
0610: Connection connection = createConnection();
0611: PreparedStatement statement = null;
0612: ResultSet results = null;
0613: try {
0614: log.fine(sql);
0615: statement = connection.prepareStatement(sql);
0616: setParameters(params, statement);
0617: configure(statement);
0618: results = statement.executeQuery();
0619: closure.call(results);
0620: } catch (SQLException e) {
0621: log.log(Level.FINE, "Failed to execute: " + sql, e);
0622: throw e;
0623: } finally {
0624: closeResources(connection, statement, results);
0625: }
0626: }
0627:
0628: /**
0629: * Performs the given SQL query calling the closure with the result set
0630: */
0631: public void query(GString gstring, Closure closure)
0632: throws SQLException {
0633: List params = getParameters(gstring);
0634: String sql = asSql(gstring, params);
0635: query(sql, params, closure);
0636: }
0637:
0638: /**
0639: * @deprecated please use eachRow instead
0640: */
0641: public void queryEach(String sql, Closure closure)
0642: throws SQLException {
0643: warnDeprecated();
0644: eachRow(sql, closure);
0645: }
0646:
0647: /**
0648: * Performs the given SQL query calling the closure with each row of the
0649: * result set
0650: */
0651: public void eachRow(String sql, Closure closure)
0652: throws SQLException {
0653: Connection connection = createConnection();
0654: Statement statement = connection.createStatement();
0655: configure(statement);
0656: ResultSet results = null;
0657: try {
0658: log.fine(sql);
0659: results = statement.executeQuery(sql);
0660:
0661: GroovyResultSet groovyRS = new GroovyResultSet(results);
0662: while (groovyRS.next()) {
0663: closure.call(groovyRS);
0664: }
0665: } catch (SQLException e) {
0666: log.log(Level.FINE, "Failed to execute: " + sql, e);
0667: throw e;
0668: } finally {
0669: closeResources(connection, statement, results);
0670: }
0671: }
0672:
0673: /**
0674: * @deprecated please use eachRow instead
0675: */
0676: public void queryEach(String sql, List params, Closure closure)
0677: throws SQLException {
0678: warnDeprecated();
0679: eachRow(sql, params, closure);
0680: }
0681:
0682: /**
0683: * Performs the given SQL query calling the closure with the result set
0684: */
0685: public void eachRow(String sql, List params, Closure closure)
0686: throws SQLException {
0687: Connection connection = createConnection();
0688: PreparedStatement statement = null;
0689: ResultSet results = null;
0690: try {
0691: log.fine(sql);
0692: statement = connection.prepareStatement(sql);
0693: setParameters(params, statement);
0694: configure(statement);
0695: results = statement.executeQuery();
0696:
0697: GroovyResultSet groovyRS = new GroovyResultSet(results);
0698: while (groovyRS.next()) {
0699: closure.call(groovyRS);
0700: }
0701: } catch (SQLException e) {
0702: log.log(Level.FINE, "Failed to execute: " + sql, e);
0703: throw e;
0704: } finally {
0705: closeResources(connection, statement, results);
0706: }
0707: }
0708:
0709: /**
0710: * Performs the given SQL query calling the closure with the result set
0711: */
0712: public void eachRow(GString gstring, Closure closure)
0713: throws SQLException {
0714: List params = getParameters(gstring);
0715: String sql = asSql(gstring, params);
0716: eachRow(sql, params, closure);
0717: }
0718:
0719: /**
0720: * @deprecated please use eachRow instead
0721: */
0722: public void queryEach(GString gstring, Closure closure)
0723: throws SQLException {
0724: warnDeprecated();
0725: eachRow(gstring, closure);
0726: }
0727:
0728: /**
0729: * Performs the given SQL query and return the rows of the result set
0730: */
0731: public List rows(String sql) throws SQLException {
0732: List results = new ArrayList();
0733: Connection connection = createConnection();
0734: Statement statement = connection.createStatement();
0735: configure(statement);
0736: ResultSet rs = null;
0737: try {
0738: log.fine(sql);
0739: rs = statement.executeQuery(sql);
0740: while (rs.next()) {
0741: ResultSetMetaData metadata = rs.getMetaData();
0742: LinkedHashMap lhm = new LinkedHashMap(metadata
0743: .getColumnCount(), 1, true);
0744: for (int i = 1; i <= metadata.getColumnCount(); i++) {
0745: lhm.put(metadata.getColumnName(i), rs.getObject(i));
0746: }
0747: GroovyRowResult row = new GroovyRowResult(lhm);
0748: results.add(row);
0749: }
0750: return (results);
0751: } catch (SQLException e) {
0752: log.log(Level.FINE, "Failed to execute: " + sql, e);
0753: throw e;
0754: } finally {
0755: closeResources(connection, statement, rs);
0756: }
0757: }
0758:
0759: /**
0760: * Performs the given SQL query and return the first row of the result set
0761: */
0762: public Object firstRow(String sql) throws SQLException {
0763: List rows = rows(sql);
0764: if (rows.isEmpty())
0765: return null;
0766: return (rows.get(0));
0767: }
0768:
0769: /**
0770: * Performs the given SQL query with the list of params and return
0771: * the rows of the result set
0772: */
0773: public List rows(String sql, List params) throws SQLException {
0774: List results = new ArrayList();
0775: Connection connection = createConnection();
0776: PreparedStatement statement = null;
0777: ResultSet rs = null;
0778: try {
0779: log.fine(sql);
0780: statement = connection.prepareStatement(sql);
0781: setParameters(params, statement);
0782: configure(statement);
0783: rs = statement.executeQuery();
0784: while (rs.next()) {
0785: ResultSetMetaData metadata = rs.getMetaData();
0786: LinkedHashMap lhm = new LinkedHashMap(metadata
0787: .getColumnCount(), 1, true);
0788: for (int i = 1; i <= metadata.getColumnCount(); i++) {
0789: lhm.put(metadata.getColumnName(i), rs.getObject(i));
0790: }
0791: GroovyRowResult row = new GroovyRowResult(lhm);
0792: results.add(row);
0793: }
0794: return (results);
0795: } catch (SQLException e) {
0796: log.log(Level.FINE, "Failed to execute: " + sql, e);
0797: throw e;
0798: } finally {
0799: closeResources(connection, statement, rs);
0800: }
0801: }
0802:
0803: /**
0804: * Performs the given SQL query with the list of params and return
0805: * the first row of the result set
0806: */
0807: public Object firstRow(String sql, List params) throws SQLException {
0808: List rows = rows(sql, params);
0809: if (rows.isEmpty())
0810: return null;
0811: return rows.get(0);
0812: }
0813:
0814: /**
0815: * Executes the given piece of SQL
0816: */
0817: public boolean execute(String sql) throws SQLException {
0818: Connection connection = createConnection();
0819: Statement statement = null;
0820: try {
0821: log.fine(sql);
0822: statement = connection.createStatement();
0823: configure(statement);
0824: boolean isResultSet = statement.execute(sql);
0825: this .updateCount = statement.getUpdateCount();
0826: return isResultSet;
0827: } catch (SQLException e) {
0828: log.log(Level.FINE, "Failed to execute: " + sql, e);
0829: throw e;
0830: } finally {
0831: closeResources(connection, statement);
0832: }
0833: }
0834:
0835: /**
0836: * Executes the given SQL update
0837: *
0838: * @return the number of rows updated
0839: */
0840: public int executeUpdate(String sql) throws SQLException {
0841: Connection connection = createConnection();
0842: Statement statement = null;
0843: try {
0844: log.fine(sql);
0845: statement = connection.createStatement();
0846: configure(statement);
0847: this .updateCount = statement.executeUpdate(sql);
0848: return this .updateCount;
0849: } catch (SQLException e) {
0850: log.log(Level.FINE, "Failed to execute: " + sql, e);
0851: throw e;
0852: } finally {
0853: closeResources(connection, statement);
0854: }
0855: }
0856:
0857: /**
0858: * Executes the given SQL statement. See {@link #executeInsert(GString)}
0859: * for more details.
0860: * @param sql The SQL statement to execute.
0861: * @return A list of the auto-generated column values for each
0862: * inserted row.
0863: */
0864: public List executeInsert(String sql) throws SQLException {
0865: Connection connection = createConnection();
0866: Statement statement = null;
0867: try {
0868: log.fine(sql);
0869: statement = connection.createStatement();
0870: configure(statement);
0871: boolean hasResultSet = statement.execute(sql,
0872: Statement.RETURN_GENERATED_KEYS);
0873:
0874: // Prepare a list to contain the auto-generated column
0875: // values, and then fetch them from the statement.
0876: List autoKeys = new ArrayList();
0877: ResultSet keys = statement.getGeneratedKeys();
0878: int count = keys.getMetaData().getColumnCount();
0879:
0880: // Copy the column values into a list of a list.
0881: while (keys.next()) {
0882: List rowKeys = new ArrayList(count);
0883: for (int i = 1; i <= count; i++) {
0884: rowKeys.add(keys.getObject(i));
0885: }
0886:
0887: autoKeys.add(rowKeys);
0888: }
0889:
0890: // Store the update count so that it can be retrieved by
0891: // clients, and then return the list of auto-generated
0892: // values.
0893: this .updateCount = statement.getUpdateCount();
0894: return autoKeys;
0895: } catch (SQLException e) {
0896: log.log(Level.FINE, "Failed to execute: " + sql, e);
0897: throw e;
0898: } finally {
0899: closeResources(connection, statement);
0900: }
0901: }
0902:
0903: /**
0904: * Executes the given piece of SQL with parameters
0905: */
0906: public boolean execute(String sql, List params) throws SQLException {
0907: Connection connection = createConnection();
0908: PreparedStatement statement = null;
0909: try {
0910: log.fine(sql);
0911: statement = connection.prepareStatement(sql);
0912: setParameters(params, statement);
0913: configure(statement);
0914: boolean isResultSet = statement.execute();
0915: this .updateCount = statement.getUpdateCount();
0916: return isResultSet;
0917: } catch (SQLException e) {
0918: log.log(Level.FINE, "Failed to execute: " + sql, e);
0919: throw e;
0920: } finally {
0921: closeResources(connection, statement);
0922: }
0923: }
0924:
0925: /**
0926: * Executes the given SQL update with parameters
0927: *
0928: * @return the number of rows updated
0929: */
0930: public int executeUpdate(String sql, List params)
0931: throws SQLException {
0932: Connection connection = createConnection();
0933: PreparedStatement statement = null;
0934: try {
0935: log.fine(sql);
0936: statement = connection.prepareStatement(sql);
0937: setParameters(params, statement);
0938: configure(statement);
0939: this .updateCount = statement.executeUpdate();
0940: return this .updateCount;
0941: } catch (SQLException e) {
0942: log.log(Level.FINE, "Failed to execute: " + sql, e);
0943: throw e;
0944: } finally {
0945: closeResources(connection, statement);
0946: }
0947: }
0948:
0949: /**
0950: * Executes the given SQL statement with a particular list of
0951: * parameter values. See {@link #executeInsert(GString)} for
0952: * more details.
0953: * @param sql The SQL statement to execute.
0954: * @param params The parameter values that will be substituted
0955: * into the SQL statement's parameter slots.
0956: * @return A list of the auto-generated column values for each
0957: * inserted row.
0958: */
0959: public List executeInsert(String sql, List params)
0960: throws SQLException {
0961: // Now send the SQL to the database.
0962: Connection connection = createConnection();
0963: PreparedStatement statement = null;
0964: try {
0965: log.fine(sql);
0966:
0967: // Prepare a statement for the SQL and then execute it.
0968: statement = connection.prepareStatement(sql);
0969: setParameters(params, statement);
0970: configure(statement);
0971: boolean hasResultSet = statement.execute(sql,
0972: Statement.RETURN_GENERATED_KEYS);
0973:
0974: // Prepare a list to contain the auto-generated column
0975: // values, and then fetch them from the statement.
0976: List autoKeys = new ArrayList();
0977: ResultSet keys = statement.getGeneratedKeys();
0978: int count = keys.getMetaData().getColumnCount();
0979:
0980: // Copy the column values into a list of a list.
0981: while (keys.next()) {
0982: List rowKeys = new ArrayList(count);
0983: for (int i = 1; i <= count; i++) {
0984: rowKeys.add(keys.getObject(i));
0985: }
0986:
0987: autoKeys.add(rowKeys);
0988: }
0989:
0990: // Store the update count so that it can be retrieved by
0991: // clients, and then return the list of auto-generated
0992: // values.
0993: this .updateCount = statement.getUpdateCount();
0994: return autoKeys;
0995: } catch (SQLException e) {
0996: log.log(Level.FINE, "Failed to execute: " + sql, e);
0997: throw e;
0998: } finally {
0999: closeResources(connection, statement);
1000: }
1001: }
1002:
1003: /**
1004: * Executes the given SQL with embedded expressions inside
1005: */
1006: public boolean execute(GString gstring) throws SQLException {
1007: List params = getParameters(gstring);
1008: String sql = asSql(gstring, params);
1009: return execute(sql, params);
1010: }
1011:
1012: /**
1013: * Executes the given SQL update with embedded expressions inside
1014: *
1015: * @return the number of rows updated
1016: */
1017: public int executeUpdate(GString gstring) throws SQLException {
1018: List params = getParameters(gstring);
1019: String sql = asSql(gstring, params);
1020: return executeUpdate(sql, params);
1021: }
1022:
1023: /**
1024: * <p>Executes the given SQL with embedded expressions inside, and
1025: * returns the values of any auto-generated colums, such as an
1026: * autoincrement ID field. These values can be accessed using
1027: * array notation. For example, to return the second auto-generated
1028: * column value of the third row, use <code>keys[3][1]</code>. The
1029: * method is designed to be used with SQL INSERT statements, but is
1030: * not limited to them.</p>
1031: * <p>The standard use for this method is when a table has an
1032: * autoincrement ID column and you want to know what the ID is for
1033: * a newly inserted row. In this example, we insert a single row
1034: * into a table in which the first column contains the autoincrement
1035: * ID:</p>
1036: * <pre>
1037: * def sql = Sql.newInstance("jdbc:mysql://localhost:3306/groovy",
1038: * "user",
1039: * "password",
1040: * "com.mysql.jdbc.Driver")
1041: *
1042: * def keys = sql.insert("insert into test_table (INT_DATA, STRING_DATA) "
1043: * + "VALUES (1, 'Key Largo')")
1044: *
1045: * def id = keys[0][0]
1046: *
1047: * // 'id' now contains the value of the new row's ID column.
1048: * // It can be used to update an object representation's
1049: * // id attribute for example.
1050: * ...
1051: * </pre>
1052: * @return A list of column values representing each row's
1053: * auto-generated keys.
1054: */
1055: public List executeInsert(GString gstring) throws SQLException {
1056: List params = getParameters(gstring);
1057: String sql = asSql(gstring, params);
1058: return executeInsert(sql, params);
1059: }
1060:
1061: /**
1062: * Performs a stored procedure call
1063: */
1064: public int call(String sql) throws Exception {
1065: return call(sql, Collections.EMPTY_LIST);
1066: }
1067:
1068: /**
1069: * Performs a stored procedure call with the given parameters
1070: */
1071: public int call(String sql, List params) throws Exception {
1072: Connection connection = createConnection();
1073: CallableStatement statement = connection.prepareCall(sql);
1074: try {
1075: log.fine(sql);
1076: setParameters(params, statement);
1077: configure(statement);
1078: return statement.executeUpdate();
1079: } catch (SQLException e) {
1080: log.log(Level.FINE, "Failed to execute: " + sql, e);
1081: throw e;
1082: } finally {
1083: closeResources(connection, statement);
1084: }
1085: }
1086:
1087: /**
1088: * Performs a stored procedure call with the given parameters. The closure
1089: * is called once with all the out parameters.
1090: */
1091: public void call(String sql, List params, Closure closure)
1092: throws Exception {
1093: Connection connection = createConnection();
1094: CallableStatement statement = connection.prepareCall(sql);
1095: try {
1096: log.fine(sql);
1097: setParameters(params, statement);
1098: statement.execute();
1099: List results = new ArrayList();
1100: int indx = 0;
1101: int inouts = 0;
1102: for (Iterator iter = params.iterator(); iter.hasNext();) {
1103: Object value = iter.next();
1104: if (value instanceof OutParameter) {
1105: if (value instanceof ResultSetOutParameter) {
1106: results.add(new CallResultSet(statement, indx));
1107: } else {
1108: Object o = statement.getObject(indx + 1);
1109: if (o instanceof ResultSet) {
1110: results.add(new GroovyResultSet(
1111: (ResultSet) o));
1112: } else {
1113: results.add(o);
1114: }
1115: }
1116: inouts++;
1117: }
1118: indx++;
1119: }
1120: closure.call(results.toArray(new Object[inouts]));
1121: } catch (SQLException e) {
1122: log.log(Level.WARNING, "Failed to execute: " + sql, e);
1123: throw e;
1124: } finally {
1125: closeResources(connection, statement);
1126: }
1127: }
1128:
1129: /**
1130: * Performs a stored procedure call with the given parameters
1131: */
1132: public int call(GString gstring) throws Exception {
1133: List params = getParameters(gstring);
1134: String sql = asSql(gstring, params);
1135: return call(sql, params);
1136: }
1137:
1138: /**
1139: * Performs a stored procedure call with the given parameters,
1140: * calling the closure once with all result objects.
1141: */
1142: public void call(GString gstring, Closure closure) throws Exception {
1143: List params = getParameters(gstring);
1144: String sql = asSql(gstring, params);
1145: call(sql, params, closure);
1146: }
1147:
1148: /**
1149: * If this SQL object was created with a Connection then this method closes
1150: * the connection. If this SQL object was created from a DataSource then
1151: * this method does nothing.
1152: *
1153: * @throws SQLException
1154: */
1155: public void close() throws SQLException {
1156: if (useConnection != null) {
1157: useConnection.close();
1158: }
1159: }
1160:
1161: public DataSource getDataSource() {
1162: return dataSource;
1163: }
1164:
1165: public void commit() {
1166: try {
1167: this .useConnection.commit();
1168: } catch (SQLException e) {
1169: log.log(Level.SEVERE,
1170: "Caught exception commiting connection: " + e, e);
1171: }
1172: }
1173:
1174: public void rollback() {
1175: try {
1176: this .useConnection.rollback();
1177: } catch (SQLException e) {
1178: log.log(Level.SEVERE,
1179: "Caught exception rollbacking connection: " + e, e);
1180: }
1181: }
1182:
1183: /**
1184: * @return Returns the updateCount.
1185: */
1186: public int getUpdateCount() {
1187: return updateCount;
1188: }
1189:
1190: /**
1191: * If this instance was created with a single Connection then the connection
1192: * is returned. Otherwise if this instance was created with a DataSource
1193: * then this method returns null
1194: *
1195: * @return the connection wired into this object, or null if this object
1196: * uses a DataSource
1197: */
1198: public Connection getConnection() {
1199: return useConnection;
1200: }
1201:
1202: /**
1203: * Allows a closure to be passed in to configure the JDBC statements before they are executed
1204: * to do things like set the query size etc.
1205: *
1206: * @param configureStatement
1207: */
1208: public void withStatement(Closure configureStatement) {
1209: this .configureStatement = configureStatement;
1210: }
1211:
1212: // Implementation methods
1213: //-------------------------------------------------------------------------
1214:
1215: /**
1216: * @return the SQL version of the given query using ? instead of any
1217: * parameter
1218: */
1219: protected String asSql(GString gstring, List values) {
1220: String[] strings = gstring.getStrings();
1221: if (strings.length <= 0) {
1222: throw new IllegalArgumentException(
1223: "No SQL specified in GString: " + gstring);
1224: }
1225: boolean nulls = false;
1226: StringBuffer buffer = new StringBuffer();
1227: boolean warned = false;
1228: Iterator iter = values.iterator();
1229: for (int i = 0; i < strings.length; i++) {
1230: String text = strings[i];
1231: if (text != null) {
1232: buffer.append(text);
1233: }
1234: if (iter.hasNext()) {
1235: Object value = iter.next();
1236: if (value != null) {
1237: if (value instanceof ExpandedVariable) {
1238: buffer.append(((ExpandedVariable) value)
1239: .getObject());
1240: iter.remove();
1241: } else {
1242: boolean validBinding = true;
1243: if (i < strings.length - 1) {
1244: String nextText = strings[i + 1];
1245: if ((text.endsWith("\"") || text
1246: .endsWith("'"))
1247: && (nextText.startsWith("'") || nextText
1248: .startsWith("\""))) {
1249: if (!warned) {
1250: log
1251: .warning("In Groovy SQL please do not use quotes around dynamic expressions "
1252: + "(which start with $) as this means we cannot use a JDBC PreparedStatement "
1253: + "and so is a security hole. Groovy has worked around your mistake but the security hole is still there. "
1254: + "The expression so far is: "
1255: + buffer.toString()
1256: + "?" + nextText);
1257: warned = true;
1258: }
1259: buffer.append(value);
1260: iter.remove();
1261: validBinding = false;
1262: }
1263: }
1264: if (validBinding) {
1265: buffer.append("?");
1266: }
1267: }
1268: } else {
1269: nulls = true;
1270: buffer.append("?'\"?"); // will replace these with nullish
1271: // values
1272: }
1273: }
1274: }
1275: String sql = buffer.toString();
1276: if (nulls) {
1277: sql = nullify(sql);
1278: }
1279: return sql;
1280: }
1281:
1282: /**
1283: * replace ?'"? references with NULLish
1284: *
1285: * @param sql
1286: */
1287: protected String nullify(String sql) {
1288: /*
1289: * Some drivers (Oracle classes12.zip) have difficulty resolving data
1290: * type if setObject(null). We will modify the query to pass 'null', 'is
1291: * null', and 'is not null'
1292: */
1293: //could be more efficient by compiling expressions in advance.
1294: int firstWhere = findWhereKeyword(sql);
1295: if (firstWhere >= 0) {
1296: Pattern[] patterns = {
1297: Pattern.compile("(?is)^(.{" + firstWhere
1298: + "}.*?)!=\\s{0,1}(\\s*)\\?'\"\\?(.*)"),
1299: Pattern.compile("(?is)^(.{" + firstWhere
1300: + "}.*?)<>\\s{0,1}(\\s*)\\?'\"\\?(.*)"),
1301: Pattern.compile("(?is)^(.{" + firstWhere
1302: + "}.*?[^<>])=\\s{0,1}(\\s*)\\?'\"\\?(.*)"), };
1303: String[] replacements = { "$1 is not $2null$3",
1304: "$1 is not $2null$3", "$1 is $2null$3", };
1305: for (int i = 0; i < patterns.length; i++) {
1306: Matcher matcher = patterns[i].matcher(sql);
1307: while (matcher.matches()) {
1308: sql = matcher.replaceAll(replacements[i]);
1309: matcher = patterns[i].matcher(sql);
1310: }
1311: }
1312: }
1313: return sql.replaceAll("\\?'\"\\?", "null");
1314: }
1315:
1316: /**
1317: * Find the first 'where' keyword in the sql.
1318: *
1319: * @param sql
1320: */
1321: protected int findWhereKeyword(String sql) {
1322: char[] chars = sql.toLowerCase().toCharArray();
1323: char[] whereChars = "where".toCharArray();
1324: int i = 0;
1325: boolean inString = false; //TODO: Cater for comments?
1326: boolean noWhere = true;
1327: int inWhere = 0;
1328: while (i < chars.length && noWhere) {
1329: switch (chars[i]) {
1330: case '\'':
1331: if (inString) {
1332: inString = false;
1333: } else {
1334: inString = true;
1335: }
1336: break;
1337: default:
1338: if (!inString && chars[i] == whereChars[inWhere]) {
1339: inWhere++;
1340: if (inWhere == whereChars.length) {
1341: return i;
1342: }
1343: }
1344: }
1345: i++;
1346: }
1347: return -1;
1348: }
1349:
1350: /**
1351: * @return extracts the parameters from the expression as a List
1352: */
1353: protected List getParameters(GString gstring) {
1354: Object[] values = gstring.getValues();
1355: List answer = new ArrayList(values.length);
1356: for (int i = 0; i < values.length; i++) {
1357: if (values[i] != null) {
1358: answer.add(values[i]);
1359: }
1360: }
1361: return answer;
1362: }
1363:
1364: /**
1365: * Appends the parameters to the given statement
1366: */
1367: protected void setParameters(List params,
1368: PreparedStatement statement) throws SQLException {
1369: int i = 1;
1370: for (Iterator iter = params.iterator(); iter.hasNext();) {
1371: Object value = iter.next();
1372: setObject(statement, i++, value);
1373: }
1374: }
1375:
1376: /**
1377: * Strategy method allowing derived classes to handle types differently
1378: * such as for CLOBs etc.
1379: */
1380: protected void setObject(PreparedStatement statement, int i,
1381: Object value) throws SQLException {
1382: if (value instanceof InParameter
1383: || value instanceof OutParameter) {
1384: if (value instanceof InParameter) {
1385: InParameter in = (InParameter) value;
1386: Object val = in.getValue();
1387: if (null == val) {
1388: statement.setNull(i, in.getType());
1389: } else {
1390: statement.setObject(i, val, in.getType());
1391: }
1392: }
1393: if (value instanceof OutParameter) {
1394: try {
1395: OutParameter out = (OutParameter) value;
1396: ((CallableStatement) statement)
1397: .registerOutParameter(i, out.getType());
1398: } catch (ClassCastException e) {
1399: throw new SQLException(
1400: "Cannot register out parameter.");
1401: }
1402: }
1403: } else {
1404: statement.setObject(i, value);
1405: }
1406: }
1407:
1408: protected Connection createConnection() throws SQLException {
1409: if (dataSource != null) {
1410: //Use a doPrivileged here as many different properties need to be
1411: // read, and the policy
1412: //shouldn't have to list them all.
1413: Connection con = null;
1414: try {
1415: con = (Connection) AccessController
1416: .doPrivileged(new PrivilegedExceptionAction() {
1417: public Object run() throws SQLException {
1418: return dataSource.getConnection();
1419: }
1420: });
1421: } catch (PrivilegedActionException pae) {
1422: Exception e = pae.getException();
1423: if (e instanceof SQLException) {
1424: throw (SQLException) e;
1425: } else {
1426: throw (RuntimeException) e;
1427: }
1428: }
1429: return con;
1430: } else {
1431: //System.out.println("createConnection returning: " +
1432: // useConnection);
1433: return useConnection;
1434: }
1435: }
1436:
1437: protected void closeResources(Connection connection,
1438: Statement statement, ResultSet results) {
1439: if (results != null) {
1440: try {
1441: results.close();
1442: } catch (SQLException e) {
1443: log.log(Level.SEVERE,
1444: "Caught exception closing resultSet: " + e, e);
1445: }
1446: }
1447: closeResources(connection, statement);
1448: }
1449:
1450: protected void closeResources(Connection connection,
1451: Statement statement) {
1452: if (statement != null) {
1453: try {
1454: statement.close();
1455: } catch (SQLException e) {
1456: log.log(Level.SEVERE,
1457: "Caught exception closing statement: " + e, e);
1458: }
1459: }
1460: if (dataSource != null) {
1461: try {
1462: connection.close();
1463: } catch (SQLException e) {
1464: log.log(Level.SEVERE,
1465: "Caught exception closing connection: " + e, e);
1466: }
1467: }
1468: }
1469:
1470: private void warnDeprecated() {
1471: if (!warned) {
1472: warned = true;
1473: log
1474: .warning("queryEach() is deprecated, please use eachRow() instead");
1475: }
1476: }
1477:
1478: /**
1479: * Provides a hook to be able to configure JDBC statements, such as to configure
1480: *
1481: * @param statement
1482: */
1483: protected void configure(Statement statement) {
1484: if (configureStatement != null) {
1485: configureStatement.call(statement);
1486: }
1487: }
1488: }
|