You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@commons.apache.org by dg...@apache.org on 2003/10/19 21:21:30 UTC
cvs commit: jakarta-commons-sandbox/dbutils/src/java/org/apache/commons/dbutils QueryRunner.java DbUtils.java
dgraham 2003/10/19 12:21:30
Modified: dbutils/src/java/org/apache/commons/dbutils DbUtils.java
Added: dbutils/src/java/org/apache/commons/dbutils QueryRunner.java
Log:
Moved execute methods from DbUtils to new QueryRunner class as
non-static methods to allow subclass customization and to simplify
DbUtils API.
Added rollback() method to DbUtils.
Added initial support for DataSources in QueryRunner.
Revision Changes Path
1.39 +73 -283 jakarta-commons-sandbox/dbutils/src/java/org/apache/commons/dbutils/DbUtils.java
Index: DbUtils.java
===================================================================
RCS file: /home/cvs/jakarta-commons-sandbox/dbutils/src/java/org/apache/commons/dbutils/DbUtils.java,v
retrieving revision 1.38
retrieving revision 1.39
diff -u -r1.38 -r1.39
--- DbUtils.java 19 Oct 2003 18:30:24 -0000 1.38
+++ DbUtils.java 19 Oct 2003 19:21:30 -0000 1.39
@@ -63,14 +63,9 @@
import java.io.PrintWriter;
import java.sql.Connection;
-import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
-import java.sql.Types;
-import java.util.Arrays;
-import java.util.Iterator;
-import java.util.List;
/**
* A collection of JDBC helper methods.
@@ -81,252 +76,6 @@
* @author David Graham
*/
public final class DbUtils {
-
- public static boolean execute(
- Connection conn,
- String query,
- Object[] vals,
- ResultSetHandler rsh,
- ResultSetMetaDataHandler rsmdh,
- Object userObject)
- throws SQLException {
-
- PreparedStatement stmt = null;
- ResultSet rs = null;
-
- try {
- stmt = conn.prepareStatement(query);
- fillStatement(stmt, vals);
-
- if (!stmt.execute()) {
- return false;
- }
-
- do {
- rs = stmt.getResultSet();
- if (rs != null) {
- if (rsmdh != null) {
- rsmdh.handle(rs.getMetaData());
- }
-
- rsh.handle(rs, vals, userObject);
- }
-
- } while (stmt.getMoreResults());
-
- } catch (SQLException e) {
- rethrow(e, query, vals);
-
- } finally {
- closeQuietly(rs);
- closeQuietly(stmt);
- }
-
- return true;
- }
-
- public static Object executeQuery(
- Connection conn,
- String query,
- Object[] vals,
- ResultSetHandler rsh,
- ResultSetMetaDataHandler rsmdh)
- throws SQLException {
-
- return executeQuery(conn, query, vals, rsh, rsmdh, null);
- }
-
- public static Object executeQuery(
- Connection conn,
- String query,
- Object[] vals,
- ResultSetHandler rsh,
- ResultSetMetaDataHandler rsmdh,
- Object userObject)
- throws SQLException {
-
- PreparedStatement stmt = null;
- ResultSet rs = null;
- Object result = null;
-
- try {
- stmt = conn.prepareStatement(query);
- fillStatement(stmt, vals);
-
- rs = stmt.executeQuery();
-
- if (rsmdh != null) {
- rsmdh.handle(rs.getMetaData());
- }
-
- result = rsh.handle(rs, vals, userObject);
-
- } catch (SQLException sqle) {
- rethrow(sqle, query, vals);
- } finally {
- closeQuietly(rs);
- closeQuietly(stmt);
- }
-
- return result;
- }
-
- /**
- * Throws a new exception with a more informative error message.
- * @param cause The original exception that will be chained to the new
- * exception when it's rethrown.
- * @param sql The query that was executing when the exception happened.
- * @param vals The query replacement paramaters.
- * @throws SQLException
- */
- private static void rethrow(SQLException cause, String sql, Object[] vals)
- throws SQLException {
-
- StringBuffer msg = new StringBuffer(cause.getMessage() + " in query " + sql);
- if (vals != null) {
- msg.append(Arrays.asList(vals).toString());
- }
-
- SQLException newsqle = new SQLException(msg.toString());
- newsqle.setNextException(cause);
-
- throw newsqle;
- }
-
- /**
- * Fill the <code>PreparedStatement</code> replacement parameters with
- * the given objects.
- * @param stmt
- * @param params
- * @throws SQLException
- */
- private static void fillStatement(PreparedStatement stmt, Object[] params)
- throws SQLException {
-
- if (params == null) {
- return;
- }
-
- for (int i = 0; i < params.length; i++) {
- if (params[i] != null) {
- stmt.setObject(i + 1, params[i]);
- } else {
- stmt.setNull(i + 1, Types.OTHER);
- }
- }
- }
-
- /**
- * Execute an SQL INSERT, UPDATE, or DELETE query.
- * @param conn The connection to use to run the query.
- * @param query The SQL to execute.
- * @param params The query replacement parameters.
- * @return The number of rows updated.
- * @throws SQLException
- */
- public static int executeUpdate(Connection conn, String query, Object[] params)
- throws SQLException {
-
- PreparedStatement stmt = conn.prepareStatement(query);
- fillStatement(stmt, params);
-
- int rows = 0;
-
- try {
- rows = stmt.executeUpdate();
-
- } catch (SQLException e) {
- rethrow(e, query, params);
-
- } finally {
- closeQuietly(stmt);
- }
-
- return rows;
- }
-
- public static List executeListQuery(
- Connection conn,
- String query,
- Object[] vals,
- Object userObject)
- throws SQLException {
-
- return executeListQuery(conn, query, vals, null, userObject);
- }
-
- /**
- * Creates a PreparedStatement using the String and Object array,
- * executes this using the Connection, and returns the results
- * inside an List.
- * Null values in the Object array will be passed to the driver.
- */
- public static List executeListQuery(
- Connection conn,
- String query,
- Object[] vals,
- ResultSetMetaDataHandler rsmdh,
- Object userObject)
- throws SQLException {
-
- return (List) executeQuery(conn, query, vals, new ArrayListHandler(), rsmdh, userObject);
- }
-
- public static Iterator executeQuery(
- Connection conn,
- String query,
- Object[] vals,
- ResultSetMetaDataHandler rsmdh)
- throws SQLException {
-
- return executeListQuery(conn, query, vals, rsmdh, null).iterator();
- }
-
- /**
- * Creates a PreparedStatement using the String and Object array,
- * executes this using the Connection, and returns the results
- * inside an Iterator.
- * Null values in the Object array will be passed to the driver.
- */
- public static Iterator executeQuery(
- Connection connection,
- String query,
- Object[] vals)
- throws SQLException {
-
- return executeListQuery(connection, query, vals, null, null).iterator();
- }
-
- /**
- * Ensures that a database driver class is loaded.
- * If this succeeds, then it returns true, else it returns false.
- */
- public static boolean ensureLoaded(String name) {
- try {
- Class.forName(name).newInstance();
- return true;
-
- } catch (ClassNotFoundException e) {
- // TODO Logging?
- //e.printStackTrace();
- return false;
-
- } catch (IllegalAccessException e) {
- // TODO Logging?
- //e.printStackTrace();
-
- // Constructor is private, OK for DriverManager contract
- return true;
-
- } catch (InstantiationException e) {
- // TODO Logging?
- //e.printStackTrace();
- return false;
-
- } catch (Throwable t) {
- return false;
- }
- }
/**
* Close a <code>Connection</code>, avoid closing if null.
@@ -336,15 +85,6 @@
conn.close();
}
}
-
- /**
- * Close a <code>Statement</code>, avoid closing if null.
- */
- public static void close(Statement stmt) throws SQLException {
- if (stmt != null) {
- stmt.close();
- }
- }
/**
* Close a <code>ResultSet</code>, avoid closing if null.
@@ -354,6 +94,15 @@
rs.close();
}
}
+
+ /**
+ * Close a <code>Statement</code>, avoid closing if null.
+ */
+ public static void close(Statement stmt) throws SQLException {
+ if (stmt != null) {
+ stmt.close();
+ }
+ }
/**
* Close a <code>Connection</code>, avoid closing if null and hide
@@ -368,15 +117,14 @@
}
/**
- * Close a <code>Statement</code>, avoid closing if null and hide
- * any exceptions that occur.
+ * Close a <code>Connection</code>, <code>Statement</code> and
+ * <code>ResultSet</code>. Avoid closing if null and hide any
+ * exceptions that occur.
*/
- public static void closeQuietly(Statement stmt) {
- try {
- close(stmt);
- } catch (SQLException sqle) {
- // quiet
- }
+ public static void closeQuietly(Connection conn, Statement stmt, ResultSet rs) {
+ closeQuietly(rs);
+ closeQuietly(stmt);
+ closeQuietly(conn);
}
/**
@@ -392,14 +140,15 @@
}
/**
- * Close a <code>Connection</code>, <code>Statement</code> and
- * <code>ResultSet</code>. Avoid closing if null and hide any
- * exceptions that occur.
+ * Close a <code>Statement</code>, avoid closing if null and hide
+ * any exceptions that occur.
*/
- public static void closeQuietly(Connection conn, Statement stmt, ResultSet rs) {
- closeQuietly(rs);
- closeQuietly(stmt);
- closeQuietly(conn);
+ public static void closeQuietly(Statement stmt) {
+ try {
+ close(stmt);
+ } catch (SQLException sqle) {
+ // quiet
+ }
}
/**
@@ -423,6 +172,41 @@
// quiet
}
}
+
+ /**
+ * Ensures that a database driver class is loaded.
+ * If this succeeds, then it returns true, else it returns false.
+ */
+ public static boolean ensureLoaded(String name) {
+ try {
+ Class.forName(name).newInstance();
+ return true;
+
+ } catch (ClassNotFoundException e) {
+ // TODO Logging?
+ //e.printStackTrace();
+ return false;
+
+ } catch (IllegalAccessException e) {
+ // TODO Logging?
+ //e.printStackTrace();
+
+ // Constructor is private, OK for DriverManager contract
+ return true;
+
+ } catch (InstantiationException e) {
+ // TODO Logging?
+ //e.printStackTrace();
+ return false;
+
+ } catch (Throwable t) {
+ return false;
+ }
+ }
+
+ public static void printStackTrace(SQLException sqle) {
+ printStackTrace(sqle, new PrintWriter(System.err));
+ }
public static void printStackTrace(SQLException sqle, PrintWriter pw) {
@@ -436,8 +220,8 @@
}
}
- public static void printStackTrace(SQLException sqle) {
- printStackTrace(sqle, new PrintWriter(System.err));
+ public static void printWarnings(Connection connection) {
+ printWarnings(connection, new PrintWriter(System.err));
}
public static void printWarnings(Connection conn, PrintWriter pw) {
@@ -450,9 +234,15 @@
}
}
- public static void printWarnings(Connection connection) {
- printWarnings(connection, new PrintWriter(System.err));
+ /**
+ * Rollback any changes made on the given connection.
+ * @param conn The database Connection to rollback. A null value is legal.
+ * @throws SQLException
+ */
+ public static void rollback(Connection conn) throws SQLException {
+ if (conn != null) {
+ conn.rollback();
+ }
}
-
}
1.1 jakarta-commons-sandbox/dbutils/src/java/org/apache/commons/dbutils/QueryRunner.java
Index: QueryRunner.java
===================================================================
/*
* $Header: /home/cvs/jakarta-commons-sandbox/dbutils/src/java/org/apache/commons/dbutils/QueryRunner.java,v 1.1 2003/10/19 19:21:30 dgraham Exp $
* $Revision: 1.1 $
* $Date: 2003/10/19 19:21:30 $
*
* ====================================================================
*
* The Apache Software License, Version 1.1
*
* Copyright (c) 2002-2003 The Apache Software Foundation. All rights
* reserved.
*
* Redistribution and use in source and binary forms, with or without
* modification, are permitted provided that the following conditions
* are met:
*
* 1. Redistributions of source code must retain the above copyright
* notice, this list of conditions and the following disclaimer.
*
* 2. Redistributions in binary form must reproduce the above copyright
* notice, this list of conditions and the following disclaimer in
* the documentation and/or other materials provided with the
* distribution.
*
* 3. The end-user documentation included with the redistribution, if
* any, must include the following acknowledgement:
* "This product includes software developed by the
* Apache Software Foundation (http://www.apache.org/)."
* Alternately, this acknowledgement may appear in the software itself,
* if and wherever such third-party acknowledgements normally appear.
*
* 4. The names "The Jakarta Project", "Commons", and "Apache Software
* Foundation" must not be used to endorse or promote products derived
* from this software without prior written permission. For written
* permission, please contact apache@apache.org.
*
* 5. Products derived from this software may not be called "Apache"
* nor may "Apache" appear in their names without prior written
* permission of the Apache Software Foundation.
*
* THIS SOFTWARE IS PROVIDED ``AS IS'' AND ANY EXPRESSED OR IMPLIED
* WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES
* OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
* DISCLAIMED. IN NO EVENT SHALL THE APACHE SOFTWARE FOUNDATION OR
* ITS CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
* SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
* LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF
* USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
* ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
* OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT
* OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
* SUCH DAMAGE.
* ====================================================================
*
* This software consists of voluntary contributions made by many
* individuals on behalf of the Apache Software Foundation. For more
* information on the Apache Software Foundation, please see
* <http://www.apache.org/>.
*
*/
package org.apache.commons.dbutils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Arrays;
import javax.sql.DataSource;
/**
* Executes SQL queries with pluggable strategies for handling
* <code>ResultSet</code>s.
*
* @see ResultSetHandler
* @see ResultSetMetaDataHandler
*
* @author Henri Yandell
* @author Juozas Baliuka
* @author Steven Caswell
* @author David Graham
*/
public class QueryRunner {
/**
* The DataSource to retrieve connections from.
*/
protected DataSource ds = null;
/**
* Constructor for QueryRunner.
*/
public QueryRunner() {
super();
}
/**
* Constructor for QueryRunner.
* @param ds The <code>DataSource</code> to retrieve connections
* from.
*/
public QueryRunner(DataSource ds) {
super();
this.ds = ds;
}
public boolean execute(
Connection conn,
String query,
Object[] vals,
ResultSetHandler rsh,
ResultSetMetaDataHandler rsmdh,
Object userObject)
throws SQLException {
PreparedStatement stmt = null;
ResultSet rs = null;
try {
stmt = conn.prepareStatement(query);
fillStatement(stmt, vals);
if (!stmt.execute()) {
return false;
}
do {
rs = stmt.getResultSet();
if (rs != null) {
if (rsmdh != null) {
rsmdh.handle(rs.getMetaData());
}
rsh.handle(rs, vals, userObject);
}
} while (stmt.getMoreResults());
} catch (SQLException e) {
rethrow(e, query, vals);
} finally {
DbUtils.closeQuietly(rs);
DbUtils.closeQuietly(stmt);
}
return true;
}
/**
* Creates a PreparedStatement using the String and Object array,
* executes this using the Connection, and returns the results
* inside an Iterator.
* Null values in the Object array will be passed to the driver.
*/
public Object executeQuery(
Connection conn,
String query,
Object[] params,
ResultSetHandler rsh)
throws SQLException {
return this.executeQuery(conn, query, params, rsh, null, null);
}
public Object executeQuery(
Connection conn,
String query,
Object[] params,
ResultSetHandler rsh,
ResultSetMetaDataHandler rsmdh)
throws SQLException {
return this.executeQuery(conn, query, params, rsh, rsmdh, null);
}
public Object executeQuery(
Connection conn,
String query,
Object[] params,
ResultSetHandler rsh,
ResultSetMetaDataHandler rsmdh,
Object userObject)
throws SQLException {
PreparedStatement stmt = null;
ResultSet rs = null;
Object result = null;
try {
stmt = conn.prepareStatement(query);
this.fillStatement(stmt, params);
rs = stmt.executeQuery();
if (rsmdh != null) {
rsmdh.handle(rs.getMetaData());
}
result = rsh.handle(rs, params, userObject);
} catch (SQLException sqle) {
this.rethrow(sqle, query, params);
} finally {
DbUtils.closeQuietly(rs);
DbUtils.closeQuietly(stmt);
}
return result;
}
public Object executeQuery(
Connection conn,
String query,
Object[] params,
ResultSetMetaDataHandler rsmdh)
throws SQLException {
return this.executeQuery(conn, query, params, null, rsmdh, null);
}
/**
* Execute an SQL INSERT, UPDATE, or DELETE query.
* @param conn The connection to use to run the query.
* @param query The SQL to execute.
* @param params The query replacement parameters.
* @return The number of rows updated.
* @throws SQLException
*/
public int executeUpdate(Connection conn, String query, Object[] params)
throws SQLException {
PreparedStatement stmt = conn.prepareStatement(query);
this.fillStatement(stmt, params);
int rows = 0;
try {
rows = stmt.executeUpdate();
} catch (SQLException e) {
this.rethrow(e, query, params);
} finally {
DbUtils.closeQuietly(stmt);
}
return rows;
}
/**
* Fill the <code>PreparedStatement</code> replacement parameters with
* the given objects.
* @param stmt
* @param params Query replacement parameters; <code>null</code> is a valid
* value to pass in.
* @throws SQLException
*/
protected void fillStatement(PreparedStatement stmt, Object[] params)
throws SQLException {
if (params == null) {
return;
}
for (int i = 0; i < params.length; i++) {
if (params[i] != null) {
stmt.setObject(i + 1, params[i]);
} else {
stmt.setNull(i + 1, Types.OTHER);
}
}
}
/**
* Returns the <code>DataSource</code> this runner is using.
*/
public DataSource getDataSource() {
return this.ds;
}
/**
* Throws a new exception with a more informative error message.
* @param cause The original exception that will be chained to the new
* exception when it's rethrown.
* @param sql The query that was executing when the exception happened.
* @param params The query replacement paramaters; <code>null</code> is a
* valid value to pass in.
* @throws SQLException
*/
protected void rethrow(SQLException cause, String sql, Object[] params)
throws SQLException {
StringBuffer msg = new StringBuffer(cause.getMessage() + " in query " + sql);
if (params != null) {
msg.append(Arrays.asList(params).toString());
}
SQLException newsqle = new SQLException(msg.toString());
newsqle.setNextException(cause);
throw newsqle;
}
/**
* Sets the <code>DataSource</code> this runner will use to get
* database connections from. This should be called after creating a
* runner with the default constructor if you intend to use the
* execute methods without passing in a <code>Connection</code>.
* @param dataSource The DataSource to use.
*/
public void setDataSource(DataSource dataSource) {
this.ds = dataSource;
}
/**
* Executes the given SELECT SQL query and returns a result object.
* @param sql The SQL statement to execute.
* @param params Initialize the PreparedStatement's IN parameters with this
* array.
* @param rsh The handler used to create the result object from
* the <code>ResultSet</code>.
* @return An object generated by the handler.
* @throws SQLException
*/
public Object executeQuery(String sql, Object[] params, ResultSetHandler rsh)
throws SQLException {
Connection conn = this.ds.getConnection();
Object result = null;
try {
result = this.executeQuery(conn, sql, params, rsh, null, null);
} finally {
DbUtils.close(conn);
}
return result;
}
/**
* Executes the given INSERT, UPDATE, or DELETE SQL statement. The
* statement is executed in it's own transaction that will be committed or
* rolled back depending on any SQLExceptions thrown.
* @param sql The SQL statement to execute.
* @param preparer Initializes the PreparedStatement's IN (ie. '?')
* parameters.
* @param prepareObject An object to pass to the preparer to setup the
* PreparedStatement.
* @throws MapperException
* @return The number of rows updated.
*/
public int executeUpdate(String sql, Object[] params) throws SQLException {
Connection conn = this.ds.getConnection();
conn.setAutoCommit(false); // single transaction.
int rows = 0;
try {
rows = this.executeUpdate(conn, sql, params);
conn.commit();
} catch (SQLException e) {
DbUtils.rollback(conn);
throw e;
} finally {
DbUtils.close(conn);
}
return rows;
}
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commons-dev-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-dev-help@jakarta.apache.org