You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@commons.apache.org by ba...@apache.org on 2003/03/06 19:47:35 UTC
cvs commit: jakarta-commons-sandbox/dbutils/src/java/org/apache/commons/dbutils ProcedureUtils.java DbUtils.java
baliuka 2003/03/06 10:47:35
Modified: dbutils/src/java/org/apache/commons/dbutils DbUtils.java
Added: dbutils/src/java/org/apache/commons/dbutils
ProcedureUtils.java
Log:
added ProdedureUtils, possible a new religion
Revision Changes Path
1.10 +222 -191 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.9
retrieving revision 1.10
diff -u -r1.9 -r1.10
--- DbUtils.java 22 Feb 2003 09:40:57 -0000 1.9
+++ DbUtils.java 6 Mar 2003 18:47:35 -0000 1.10
@@ -63,222 +63,253 @@
import java.util.List;
public final class DbUtils {
-
- public static interface ResultSetHandler{
- public Object handle(ResultSet rs)throws SQLException;
-
+ public static interface ResultSetHandler{
+
+ public Object handle(ResultSet rs)throws SQLException;
+
}
- public static abstract class ListAdapter extends ArrayList implements ResultSetHandler{
-
- final public Object handle(ResultSet rs)throws SQLException{
-
- while(rs.next()){
+ public static abstract class ListAdapter extends ArrayList implements ResultSetHandler{
+
+ final public Object handle(ResultSet rs)throws SQLException{
+
+ while(rs.next()){
add(fetch(rs));
- }
- return this;
- }
-
- public abstract Object fetch( ResultSet rs )throws SQLException;
-
- }
-
-
- public static Object executeQuery(Connection connection, String query,
- Object[] vals, ResultSetHandler rsh
- ) throws SQLException
- {
+ }
+ return this;
+ }
+
+ public abstract Object fetch( ResultSet rs )throws SQLException;
+
+ }
+
+
+ public static boolean execute(Connection connection, String query,Object[] vals, ResultSetHandler rsh)
+ throws SQLException {
+
PreparedStatement stmt = null;
ResultSet rs = null;
-
+
try {
+
+ stmt = connection.prepareStatement(query);
+ fillStatement(stmt, vals);
+
+ try{
+ if(stmt.execute()){
+ do{
+
+ rs = stmt.getResultSet();
+ rsh.handle(rs);
+
+ }while(stmt.getMoreResults());
+
+ return true;
+
+ }else{
+
+ return false;
+
+ }
+ }catch(SQLException sqle){
+ closeQuietly(rs);
+ rethrow( sqle, query, vals);
+
+ }
+ return false;
+
+ } finally {
+ closeQuietly(stmt);
+ }
+
+ }
+
+
+
+ public static Object executeQuery(Connection connection, String query, Object[] vals, ResultSetHandler rsh)
+ throws SQLException {
+
+ PreparedStatement stmt = null;
+ ResultSet rs = null;
+
+ try {
+
stmt = connection.prepareStatement(query);
fillStatement(stmt, vals);
-
- try{
- rs = stmt.executeQuery();
+ try{
+ rs = stmt.executeQuery();
}catch(SQLException sqle){
- rethrow( sqle, query, vals);
+ rethrow( sqle, query, vals);
}
- return rsh.handle(rs);
+ return rsh.handle(rs);
} finally {
closeQuietly(rs);
closeQuietly(stmt);
}
+
+ }
+ static void rethrow(SQLException cause, String sql,Object[] vals )throws SQLException{
+
+ String msg = cause.getMessage() + " in query " + sql +
+ java.util.Arrays.asList(vals).toString();
+ SQLException newsqle = new SQLException(msg);
+ newsqle.setNextException(cause);
+ throw newsqle;
+
}
-
- static void rethrow(SQLException cause, String sql,Object[] vals )throws SQLException{
- String msg = cause.getMessage() + " in query " + sql +
- java.util.Arrays.asList(vals).toString();
- SQLException newsqle = new SQLException(msg);
- newsqle.setNextException(cause);
- throw newsqle;
-
+ static void throwNoResults( String sql,Object[] vals )throws SQLException{
+
+ rethrow( new SQLException("No results returned"), sql, vals );
+
}
-
- static void throwNoResults( String sql,Object[] vals )throws SQLException{
- rethrow( new SQLException("No results returned"), sql, vals );
-
+ static void throwMultipleResults( String sql,Object[] vals )throws SQLException{
+
+ rethrow( new SQLException("No results returned"), sql, vals );
+
}
-
- static void throwMultipleResults( String sql,Object[] vals )throws SQLException{
- rethrow( new SQLException("No results returned"), sql, vals );
-
- }
-
-
-
- static void fillStatement(PreparedStatement stmt, Object[] vals) throws SQLException {
+
+
+ static void fillStatement(PreparedStatement stmt, Object[] vals)throws SQLException {
if (vals != null) {
int size = vals.length;
for (int i = 0; i < size; i++) {
- if( vals[i] != null ){
- stmt.setObject((i + 1), vals[i]);
- }else{
- stmt.setNull((i + 1), Types.OTHER);
- }
+ if( vals[i] != null ){
+ stmt.setObject((i + 1), vals[i]);
+ }else{
+ stmt.setNull((i + 1), Types.OTHER);
+ }
}
}
}
-
- public static int executeUpdate(Connection connection, String query,
- Object[] vals
- ) throws SQLException
- {
+
+ public static int executeUpdate(Connection connection, String query,Object[] vals)
+ throws SQLException {
PreparedStatement stmt = null;
-
+
stmt = connection.prepareStatement(query);
fillStatement(stmt, vals);
try {
return stmt.executeUpdate();
} catch(SQLException sqle) {
- rethrow( sqle, query, vals);
- //assert true
- return 0;
+ rethrow( sqle, query, vals);
+ //assert true
+ return 0;
} finally {
closeQuietly(stmt);
}
}
-
- /**
- * Creates a PreparedStatement using the String and Object array,
- * executes this using the Connection, and returns the results
- * inside an List.
+
+ /**
+ * 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 connection, String query,
- Object[] vals
- ) throws SQLException
- {
- return (List)executeQuery(connection,query,vals,
-
- new ListAdapter(){
-
- public Object fetch( ResultSet rs )throws SQLException{
- return resultSetToArray(rs);
- }
-
- }
-
- );
-
- }
-
-
-
- /**
- * Creates a PreparedStatement using the String and Object array,
- * executes this using the Connection, and returns the results
- * inside an Iterator.
+ public static List executeListQuery(Connection connection, String query, Object[] vals )
+ throws SQLException {
+ return (List)executeQuery(connection,query,vals,
+
+ new ListAdapter(){
+
+ public Object fetch( ResultSet rs )throws SQLException{
+ return resultSetToArray(rs);
+ }
+
+ }
+
+ );
+
+ }
+
+
+
+ /**
+ * 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 ).iterator();
-
+ public static Iterator executeQuery(Connection connection, String query, Object[] vals )
+ throws SQLException {
+ return executeListQuery(connection, query, vals ).iterator();
+
}
/**
- * Creates a PreparedStatement using the String and Object array,
- * executes this using the Connection, and returns the result
- * as int.
+ * Creates a PreparedStatement using the String and Object array,
+ * executes this using the Connection, and returns the result
+ * as int.
* Null values in the Object array will be passed to the driver.
*/
- public static int executeIntQuery(Connection connection, final String query,
- final Object[] vals
- ) throws SQLException
- {
- return ((Number) executeQuery(connection, query, vals,
-
- new ResultSetHandler(){
- public Object handle(ResultSet rs)throws SQLException{
- ResultSetMetaData rsmd = rs.getMetaData();
- if(rsmd.getColumnCount() > 1 ){
- throwMultipleResults(query, vals);
- }
- Number result = null;
- if (rs.next()) {
- result = new Integer( rs.getInt(1) );
- }else{
- throwNoResults(query, vals);
- }
- if(rs.next()){
- throwMultipleResults(query, vals);
+ public static int executeIntQuery(Connection connection, final String query, final Object[] vals )
+ throws SQLException {
+ return ((Number) executeQuery(connection, query, vals,
+
+ new ResultSetHandler(){
+ public Object handle(ResultSet rs)throws SQLException{
+ ResultSetMetaData rsmd = rs.getMetaData();
+ if(rsmd.getColumnCount() > 1 ){
+ throwMultipleResults(query, vals);
+ }
+ Number result = null;
+ if (rs.next()) {
+ result = new Integer( rs.getInt(1) );
+ }else{
+ throwNoResults(query, vals);
+ }
+ if(rs.next()){
+ throwMultipleResults(query, vals);
+ }
+ return result;
}
- return result;
- }
- }
- // ResultSetHandler
- )).intValue();
-
- }
-
-/**
- * Creates a PreparedStatement using the String and Object array,
- * executes this using the Connection, and returns the result
- * as row.
+ }
+ // ResultSetHandler
+ )).intValue();
+
+ }
+
+ /**
+ * Creates a PreparedStatement using the String and Object array,
+ * executes this using the Connection, and returns the result
+ * as row.
* Null values in the Object array will be passed to the driver.
*/
- public static Object[] executeRowQuery(Connection connection, final String query,
- final Object[] vals
- ) throws SQLException
- {
- return ((Object[]) executeQuery(connection, query, vals,
-
- new ResultSetHandler(){
- public Object handle(ResultSet rs)throws SQLException{
-
- if (rs.next()) {
- return resultSetToArray(rs);
- }else{
- throwNoResults(query, vals);
- }
- if(rs.next()){
- throwMultipleResults(query, vals);
+ public static Object[] executeRowQuery(Connection connection, final String query, final Object[] vals)
+ throws SQLException {
+ return ((Object[]) executeQuery(connection, query, vals,
+
+ new ResultSetHandler(){
+ public Object handle(ResultSet rs)throws SQLException{
+
+ if (rs.next()) {
+ return resultSetToArray(rs);
+ }else{
+ throwNoResults(query, vals);
+ }
+ if(rs.next()){
+ throwMultipleResults(query, vals);
+ }
+ //assert true
+ return null;
}
- //assert true
- return null;
- }
- }
- // ResultSetHandler
- ));
-
+ }
+ // ResultSetHandler
+ ));
+
}
-
-
+
+
/**
* Ensures that a database driver class is loaded.
* If this succeeds, then it returns true, else it returns false.
@@ -298,7 +329,7 @@
return false;
}
}
-
+
/**
* Create an Object array from a ResultSet.
* It is assumed that next() has already been called on the ResultSet.
@@ -316,7 +347,7 @@
}
return objs;
}
-
+
/**
* Iterate over a result set, getting an Object[] back
* for each row of the result set.
@@ -326,7 +357,7 @@
public static Iterator iterateResultSet(ResultSet rs) {
return new ResultSetIterator(rs);
}
-
+
/**
* Iterate over a result set, getting an Object[] back
* for each row of the result set.
@@ -335,7 +366,7 @@
public static Iterator iterateResultSetVersion1(ResultSet rs) {
return new ResultSetIteratorV1(rs);
}
-
+
/**
* Close a connection, avoid closing if null.
*/
@@ -345,7 +376,7 @@
}
conn.close();
}
-
+
/**
* Close a statement, avoid closing if null.
*/
@@ -355,7 +386,7 @@
}
stat.close();
}
-
+
/**
* Close a result set, avoid closing if null.
*/
@@ -365,7 +396,7 @@
}
rs.close();
}
-
+
/**
* Close a connection, avoid closing if null and hide
* any exceptions that occur.
@@ -377,7 +408,7 @@
// quiet
}
}
-
+
/**
* Close a statement, avoid closing if null and hide
* any exceptions that occur.
@@ -389,7 +420,7 @@
// quiet
}
}
-
+
/**
* Close a result set, avoid closing if null and hide
* any exceptions that occur.
@@ -401,7 +432,7 @@
// quiet
}
}
-
+
/**
* Commits a connection then closes it, avoid closing if null.
*/
@@ -412,9 +443,9 @@
conn.commit();
conn.close();
}
-
+
/**
- * Commits a connection then closes it, avoid closing if null and
+ * Commits a connection then closes it, avoid closing if null and
* hide any exceptions that occur.
*/
public static void commitAndCloseQuietly(Connection conn) {
@@ -424,38 +455,38 @@
// quiet
}
}
-
+
public static void printStackTrace(SQLException sqle, java.io.PrintStream ps){
SQLException next = sqle;
while( next != null ){
- next.printStackTrace(ps);
- next = next.getNextException();
+ next.printStackTrace(ps);
+ next = next.getNextException();
}
-
+
}
public static void printStackTrace(SQLException sqle){
+
+ printStackTrace( sqle, System.err );
+
+ }
+
+ public static void printWarnings(Connection connection, java.io.PrintStream ps){
+ if( connection != null ){
+ try{
+ printStackTrace(connection.getWarnings(), ps);
+ }catch(SQLException sqle){
+ printStackTrace(sqle, ps);
+ }
+ }
+
+ }
- printStackTrace( sqle, System.err );
-
+ public static void printWarnings(Connection connection ){
+ printWarnings(connection,System.err);
}
- public static void printWarnings(Connection connection, java.io.PrintStream ps){
- if( connection != null ){
- try{
- printStackTrace(connection.getWarnings(), ps);
- }catch(SQLException sqle){
- printStackTrace(sqle, ps);
- }
- }
-
- }
-
- public static void printWarnings(Connection connection ){
- printWarnings(connection,System.err);
- }
-
}
1.1 jakarta-commons-sandbox/dbutils/src/java/org/apache/commons/dbutils/ProcedureUtils.java
Index: ProcedureUtils.java
===================================================================
/*
* ====================================================================
*
* The Apache Software License, Version 1.1
*
* Copyright (c) 1999-2002 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 acknowlegement:
* "This product includes software developed by the
* Apache Software Foundation (http://www.apache.org/)."
* Alternately, this acknowlegement may appear in the software itself,
* if and wherever such third-party acknowlegements 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 Group.
*
* 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.util.*;
import java.lang.reflect.*;
import java.sql.*;
/**
* sample:
* class DAO {
*
* static interface Procedure{
* int execute(int p1);
* }
*
* static interface Procedure2{
* int execute2(int p1, int p2);
* }
*
* static private scalarProc(Class cls, String sql){
* ProcedureUtils.register(cls, sql, new ScalarHandlerImpl() );
*
* }
* static{
*
* scalarProc(Procedure.class,
* "SELECT COUNT(*) FROM MY_TABLE WHERE VALUE < $1"
* );
*
* salarProc(Procedure.class,
* "SELECT SUM(VALUE) - $2 FROM MY_TABLE WHERE VALUE > $1"
*
* );
* }
*
* static Procedure getProcedure(){
* return (Procedure)ProcedureUtils.getInstance(Procedure.class,
* currentConnection());
* }
*
* static Procedure getProcedure2(){
* return (Procedure2)ProcedureUtils.getInstance(Procedure2.class,
* currentConnection());
* }
*
*
* public static int main( String args ){
*
* System.out.println(getProcedure().execute(0) + getProcedure2().execute(1,3));
*
* }
* }
*
* TODO:
*
* interface MyDAO{
*
* @sql SELECT MAX(VALUE) FROM MY_TABLE WHERE VALUE < $1
* int getMaxLesThan(int p1);
*
* @sql SELECT COUNT(*) FROM MY_TABLE WHERE VALUE < $1 and VALUE >= $2
* int getCount(int p1, int p2);
* }
*
*
* @author baliuka
*/
public final class ProcedureUtils {
private static final Map PROCEDURES = new HashMap();
static class ProcedureDescriptor{
String jdbcSQL;
int indexMap[];
DbUtils.ResultSetHandler handler;
boolean update;
//TODO:
boolean cached;
boolean flushOnExecute;
}
private ProcedureUtils() {
}
private static ProcedureDescriptor compile(Class procInterface, String sqlStr,
DbUtils.ResultSetHandler handler,boolean update){
java.util.ArrayList indexes = new java.util.ArrayList();
char sql[] = sqlStr.toCharArray();
StringBuffer sb = new StringBuffer();
StringBuffer digit = new StringBuffer();
boolean arg = false;
boolean escape = false;
for (int i = 0; i < sql.length; i++ ){
if(sql[i] == '\''){
escape = false;
arg = false;
while( true ){
try{
sb.append(sql[i++]);
if(sql[i] == '\'' && sql[i - 1] != '\\'){
sb.append('\'');
break;
}
}catch(ArrayIndexOutOfBoundsException aie){
throw new IllegalArgumentException("unterminated string in query");
}
}
continue;
}
if(sql[i] == '$'){
escape = true;
arg = false;
continue;
}
if (Character.isDigit(sql[i]) && escape){
digit.append(sql[i]);
arg = true;
if( i != sql.length - 1 )
continue;
}
if(arg){
indexes.add( new Integer(Integer.parseInt(digit.toString())) );
sb.append('?');
digit.delete(0, digit.length() );
}
sb.append(sql[i]);
escape = false;
arg = false;
}
Method methods[] = procInterface.getDeclaredMethods();
if(methods.length != 1 ){
throw new IllegalArgumentException( procInterface.getName() +
" must declare singe method" );
}
if(update && ( methods[0].getReturnType() != Void.TYPE || methods[0].getReturnType() != Integer.TYPE )){
throw new IllegalArgumentException( "update method " + methods[0] +
" must return int or void " );
}
for( int i = 0; i < methods[0].getParameterTypes().length; i++ ){
if(!indexes.contains(new Integer( i + 1))){
throw new IllegalArgumentException( "missing prameter " +
methods[0].getParameterTypes() + " $" + (i + 1 ) );
}
}
ProcedureDescriptor descriptor = new ProcedureDescriptor();
if(indexes.size() > 0){
int indexMap[] = new int[indexes.size()];
for( int i = 0 ; i < indexMap.length; i++ ){
indexMap[i] = ((Integer)indexes.get(i)).intValue() - 1;
}
descriptor.indexMap = indexMap;
}else {
descriptor.indexMap = new int[0];
}
descriptor.handler = handler;
descriptor.jdbcSQL = sb.toString();
descriptor.update = update;
return descriptor;
}
private static void register( Class procInterface, String sql,
DbUtils.ResultSetHandler handler){
if(handler == null){
throw new NullPointerException("handler is null");
}
register( procInterface, sql, handler, false);
}
private static void register( Class procInterface, String sql){
register( procInterface, sql, null, false);
}
private static void register( Class procInterface, String sql,
DbUtils.ResultSetHandler handler, boolean update ){
if(!procInterface.isInterface()){
throw new IllegalArgumentException(procInterface.getName() + " is not an interface");
}
ProcedureDescriptor des = compile(procInterface,sql, handler, update );
PROCEDURES.put(procInterface,des);
}
public static Object getInstance(Class cls,Connection connection){
ProcedureDescriptor descriptor = (ProcedureDescriptor)PROCEDURES.get(cls);
if(descriptor == null){
throw new IllegalStateException("no procedure registred for " + cls.getName() );
}
return Proxy.newProxyInstance(cls.getClassLoader(),
new Class[]{cls},
new Invocation(connection, descriptor)
);
}
static class Invocation implements InvocationHandler{
Connection connection;
ProcedureDescriptor descriptor;
Invocation(Connection connection,ProcedureDescriptor descriptor){
this.connection = connection;
this.descriptor = descriptor;
}
private Object[] prepareArgs(Object[] args){
int len = descriptor.indexMap.length;
Object pargs[] = new Object[ len ];
for(int i = 0; i < len; i++ ){
pargs[i] = args[ descriptor.indexMap[i] ];
}
return pargs;
}
//TODO: some way to handle SQLException
public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
if(!descriptor.update){
return DbUtils.executeQuery( connection,
descriptor.jdbcSQL,
prepareArgs(args),
descriptor.handler
);
}else{
int updateCount = DbUtils.executeUpdate( connection,
descriptor.jdbcSQL,
prepareArgs(args)
);
if(method.getReturnType() == Void.TYPE ){
return null;
}else {
return new Integer(updateCount);
}
}
}
}
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commons-dev-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-dev-help@jakarta.apache.org