commons-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From thecarlh...@apache.org
Subject [39/50] commons-dbutils git commit: DBUTILS-50 Apply user patch to support CallableStatement "out" parameters
Date Fri, 07 Jul 2017 02:06:14 GMT
DBUTILS-50 Apply user patch to support CallableStatement "out" parameters

git-svn-id: https://svn.apache.org/repos/asf/commons/proper/dbutils/trunk@1674183 13f79535-47bb-0310-9956-ffa450edef68


Project: http://git-wip-us.apache.org/repos/asf/commons-dbutils/repo
Commit: http://git-wip-us.apache.org/repos/asf/commons-dbutils/commit/aa276d1a
Tree: http://git-wip-us.apache.org/repos/asf/commons-dbutils/tree/aa276d1a
Diff: http://git-wip-us.apache.org/repos/asf/commons-dbutils/diff/aa276d1a

Branch: refs/heads/master
Commit: aa276d1adf6c3bf79340d26008a3ac760acefbc5
Parents: e5d6801
Author: Carl Franklin Hall <thecarlhall@apache.org>
Authored: Fri Apr 17 01:38:47 2015 +0000
Committer: Carl Franklin Hall <thecarlhall@apache.org>
Committed: Fri Apr 17 01:38:47 2015 +0000

----------------------------------------------------------------------
 src/changes/changes.xml                         |   3 +
 .../commons/dbutils/AbstractQueryRunner.java    |  35 +-
 .../apache/commons/dbutils/OutParameter.java    | 143 ++++++
 .../org/apache/commons/dbutils/QueryRunner.java | 239 +++++++++-
 .../commons/dbutils/OutParameterTest.java       |  77 +++
 .../apache/commons/dbutils/QueryRunnerTest.java | 463 ++++++++++++++++++-
 6 files changed, 957 insertions(+), 3 deletions(-)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/commons-dbutils/blob/aa276d1a/src/changes/changes.xml
----------------------------------------------------------------------
diff --git a/src/changes/changes.xml b/src/changes/changes.xml
index 5c36d00..906d000 100644
--- a/src/changes/changes.xml
+++ b/src/changes/changes.xml
@@ -65,6 +65,9 @@ The <action> type attribute can be add,update,fix,remove.
       <action dev="thecarlhall" type="fix" issue="DBUTILS-70" due-to="Michael Akerman">
         Add ability to configure statements used in QueryRunner
       </action>
+      <action dev="thecarlhall" type="add" issue="DBUTILS-50" due-to="Dan Fabulich">
+        Support CallableStatement "out" parameters
+      </action>
     </release>
 
     <release version="1.6" date="2014-07-20" description="Bugfixes and addition of insert
methods">

http://git-wip-us.apache.org/repos/asf/commons-dbutils/blob/aa276d1a/src/main/java/org/apache/commons/dbutils/AbstractQueryRunner.java
----------------------------------------------------------------------
diff --git a/src/main/java/org/apache/commons/dbutils/AbstractQueryRunner.java b/src/main/java/org/apache/commons/dbutils/AbstractQueryRunner.java
index bc08bd1..de735d9 100644
--- a/src/main/java/org/apache/commons/dbutils/AbstractQueryRunner.java
+++ b/src/main/java/org/apache/commons/dbutils/AbstractQueryRunner.java
@@ -21,6 +21,7 @@ import java.beans.Introspector;
 import java.beans.PropertyDescriptor;
 import java.lang.reflect.InvocationTargetException;
 import java.lang.reflect.Method;
+import java.sql.CallableStatement;
 import java.sql.Connection;
 import java.sql.ParameterMetaData;
 import java.sql.PreparedStatement;
@@ -265,6 +266,29 @@ public abstract class AbstractQueryRunner {
     }
 
     /**
+     * Factory method that creates and initializes a
+     * <code>CallableStatement</code> object for the given SQL.
+     * <code>QueryRunner</code> methods always call this method to prepare
+     * callable statements for them. Subclasses can override this method to
+     * provide special CallableStatement configuration if needed. This
+     * implementation simply calls <code>conn.prepareCall(sql)</code>.
+     *
+     * @param conn
+     *            The <code>Connection</code> used to create the
+     *            <code>CallableStatement</code>
+     * @param sql
+     *            The SQL statement to prepare.
+     * @return An initialized <code>CallableStatement</code>.
+     * @throws SQLException
+     *             if a database access error occurs
+     */
+    protected CallableStatement prepareCall(Connection conn, String sql)
+            throws SQLException {
+
+        return conn.prepareCall(sql);
+    }
+
+    /**
      * Factory method that creates and initializes a <code>Connection</code>
      * object. <code>QueryRunner</code> methods always call this method to
      * retrieve connections from its DataSource. Subclasses can override this
@@ -327,9 +351,18 @@ public abstract class AbstractQueryRunner {
             return;
         }
 
+        CallableStatement call = null;
+        if (stmt instanceof CallableStatement) {
+            call = (CallableStatement) stmt;
+        }
+
         for (int i = 0; i < params.length; i++) {
             if (params[i] != null) {
-                stmt.setObject(i + 1, params[i]);
+                if (call != null && params[i] instanceof OutParameter) {
+                    ((OutParameter)params[i]).register(call, i + 1);
+                } else {
+                    stmt.setObject(i + 1, params[i]);
+                }
             } else {
                 // VARCHAR works with many drivers regardless
                 // of the actual column type. Oddly, NULL and

http://git-wip-us.apache.org/repos/asf/commons-dbutils/blob/aa276d1a/src/main/java/org/apache/commons/dbutils/OutParameter.java
----------------------------------------------------------------------
diff --git a/src/main/java/org/apache/commons/dbutils/OutParameter.java b/src/main/java/org/apache/commons/dbutils/OutParameter.java
new file mode 100644
index 0000000..0abf84d
--- /dev/null
+++ b/src/main/java/org/apache/commons/dbutils/OutParameter.java
@@ -0,0 +1,143 @@
+/*
+ * Copyright 2014 The Apache Software Foundation.
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ *
+ *      http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.commons.dbutils;
+
+import java.sql.CallableStatement;
+import java.sql.SQLException;
+
+/**
+ * Represents an OUT parameter for a stored procedure.  When running a stored
+ * procedure with {@link ProcedureRunner}, pass an instance of
+ * <code>OutParameter</code> to indicate that the parameter at that index is
an
+ * OUT parameter.  The value of the parameter may be obtained from the
+ * <code>OutParameter</code> instance via {@link #getValue() }.
+ * <p>
+ * INOUT parameters are also supported by setting the <code>value</code> of
+ * the <code>OutParameter</code> instance before invoking the stored procedure.
+ *
+ * @param <T> the class of the parameter; should be compatible via cast with the
+ * class returned by the <code>CallableStatement.getObject(int)</code> method.
+ */
+public class OutParameter<T> {
+    private final int sqlType;
+    private final Class<T> javaType;
+    private T value = null;
+
+    /**
+     * Construct an <code>OutParameter</code> for the given JDBC SQL type and
+     * Java type.
+     * @param sqlType the JDBC SQL type of the parameter as in
+     * <code>java.sql.Types</code>.
+     * @param javaType the Java class of the parameter value, cast compatible
+     * with the type returned by <code>CallableStatement.getObject(int)</code>
+     * for the JDBC type given by <code>sqlType</code>.
+     */
+    public OutParameter(int sqlType, Class<T> javaType) {
+        this.sqlType = sqlType;
+        this.javaType = javaType;
+    }
+
+    /**
+     * Construct an <code>OutParameter</code> for the given JDBC SQL type and
+     * Java type and with the given value.  The parameter will be treated as an
+     * INOUT parameter if the value is null.
+     * @param sqlType the JDBC SQL type of the parameter as in
+     * <code>java.sql.Types</code>.
+     * @param javaType the Java class of the parameter value, cast compatible
+     * with the type returned by <code>CallableStatement.getObject(int)</code>
+     * for the JDBC type given by <code>sqlType</code>.
+     * @param value the IN value of the parameter
+     */
+    public OutParameter(int sqlType, Class<T> javaType, T value) {
+        this.sqlType = sqlType;
+        this.javaType = javaType;
+        this.value = value;
+    }
+
+    /**
+     * Get the JDBC SQL type for this OUT parameter.
+     * @return the JDBC SQL type for this OUT parameter.
+     */
+    public int getSqlType() {
+        return sqlType;
+    }
+
+    /**
+     * Get the Java class for this OUT parameter.
+     * @return the Java class for this OUT parameter.
+     */
+    public Class<T> getJavaType()
+    {
+        return javaType;
+    }
+
+    /**
+     * Get the value of the OUT parameter.  After the stored procedure has
+     * been executed, the value is the value returned via this parameter.
+     * @return the value of the OUT parameter.
+     */
+    public T getValue() {
+        return value;
+    }
+
+    /**
+     * Set the value of the OUT parameter.  If the value is not null when the
+     * stored procedure is executed, then the parameter will be treated like an
+     * INOUT parameter.
+     * @param value the new value for the parameter.
+     */
+    public void setValue(T value) {
+        this.value = value;
+    }
+
+    /**
+     * Set the value using the return value of the parameter an the given index
+     * from the given <code>CallableStatement</code>.
+     * @param stmt the already executed statement
+     * @param index the (1-based) index of the parameter
+     * @throws SQLException when the value could not be retrieved from the
+     * statement.
+     */
+    void setValue(CallableStatement stmt, int index) throws SQLException {
+        Object object = stmt.getObject(index);
+        value = javaType.cast(object);
+    }
+
+    /**
+     * Set up the given statement by registering an OUT parameter at the given
+     * index using the <code>sqlType</code> and <code>value</code>
of this
+     * <code>OutParameter</code>.  If the value is not null, the parameter is
+     * treated like an INOUT parameter and the value is set on the statement.
+     * @param stmt the statement the parameter should register on.
+     * @param index the (1-based) index of the parameter.
+     * @throws SQLException if the parameter could not be registered, or if the
+     * value of the parameter could not be set.
+     */
+    void register(CallableStatement stmt, int index) throws SQLException {
+        stmt.registerOutParameter(index, sqlType);
+        if (value != null) {
+            stmt.setObject(index, value);
+        }
+    }
+
+    @Override
+    public String toString()
+    {
+        return "OutParameter{" + "sqlType=" + sqlType + ", javaType="
+            + javaType + ", value=" + value + '}';
+    }
+}

http://git-wip-us.apache.org/repos/asf/commons-dbutils/blob/aa276d1a/src/main/java/org/apache/commons/dbutils/QueryRunner.java
----------------------------------------------------------------------
diff --git a/src/main/java/org/apache/commons/dbutils/QueryRunner.java b/src/main/java/org/apache/commons/dbutils/QueryRunner.java
index aaaf157..710b629 100644
--- a/src/main/java/org/apache/commons/dbutils/QueryRunner.java
+++ b/src/main/java/org/apache/commons/dbutils/QueryRunner.java
@@ -16,12 +16,14 @@
  */
 package org.apache.commons.dbutils;
 
+import java.sql.CallableStatement;
 import java.sql.Connection;
 import java.sql.PreparedStatement;
 import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.sql.Statement;
-
+import java.util.LinkedList;
+import java.util.List;
 import javax.sql.DataSource;
 
 /**
@@ -746,4 +748,239 @@ public class QueryRunner extends AbstractQueryRunner {
 
         return generatedKeys;
     }
+
+    /**
+     * Execute an SQL statement, including a stored procedure call, which does
+     * not return any result sets.
+     * Any parameters which are instances of {@link OutParameter} will be
+     * registered as OUT parameters.
+     * <p>
+     * Use this method when invoking a stored procedure with OUT parameters
+     * that does not return any result sets.  If you are not invoking a stored
+     * procedure, or the stored procedure has no OUT parameters, consider using
+     * {@link #update(java.sql.Connection, java.lang.String, java.lang.Object...) }.
+     * If the stored procedure returns result sets, use
+     * {@link #execute(java.sql.Connection, java.lang.String, org.apache.commons.dbutils.ResultSetHandler,
java.lang.Object...) }.
+     *
+     * @param conn The connection to use to run the query.
+     * @param sql The SQL to execute.
+     * @param params The query replacement parameters.
+     * @return The number of rows updated.
+     * @throws SQLException if a database access error occurs
+     */
+    public int execute(Connection conn, String sql, Object... params) throws SQLException
{
+        return this.execute(conn, false, sql, params);
+    }
+
+    /**
+     * Execute an SQL statement, including a stored procedure call, which does
+     * not return any result sets.
+     * Any parameters which are instances of {@link OutParameter} will be
+     * registered as OUT parameters.
+     * <p>
+     * Use this method when invoking a stored procedure with OUT parameters
+     * that does not return any result sets.  If you are not invoking a stored
+     * procedure, or the stored procedure has no OUT parameters, consider using
+     * {@link #update(java.lang.String, java.lang.Object...) }.
+     * If the stored procedure returns result sets, use
+     * {@link #execute(java.lang.String, org.apache.commons.dbutils.ResultSetHandler, java.lang.Object...)
}.
+     * <p>
+     * The <code>Connection</code> is retrieved from the <code>DataSource</code>
+     * set in the constructor.  This <code>Connection</code> must be in
+     * auto-commit mode or the update will not be saved.
+     *
+     * @param sql The SQL statement to execute.
+     * @param params Initializes the CallableStatement's parameters (i.e. '?').
+     * @throws SQLException if a database access error occurs
+     * @return The number of rows updated.
+     */
+    public int execute(String sql, Object... params) throws SQLException {
+        Connection conn = this.prepareConnection();
+
+        return this.execute(conn, true, sql, params);
+    }
+
+    /**
+     * Execute an SQL statement, including a stored procedure call, which
+     * returns one or more result sets.
+     * Any parameters which are instances of {@link OutParameter} will be
+     * registered as OUT parameters.
+     * <p>
+     * Use this method when: a) running SQL statements that return multiple
+     * result sets; b) invoking a stored procedure that return result
+     * sets and OUT parameters.  Otherwise you may wish to use
+     * {@link #query(java.sql.Connection, java.lang.String, org.apache.commons.dbutils.ResultSetHandler,
java.lang.Object...) }
+     * (if there are no OUT parameters) or
+     * {@link #execute(java.sql.Connection, java.lang.String, java.lang.Object...) }
+     * (if there are no result sets).
+     *
+     * @param <T> The type of object that the handler returns
+     * @param conn The connection to use to run the query.
+     * @param sql The SQL to execute.
+     * @param rsh The result set handler
+     * @param params The query replacement parameters.
+     * @return A list of objects generated by the handler
+     * @throws SQLException if a database access error occurs
+     */
+    public <T> List<T> execute(Connection conn, String sql, ResultSetHandler<T>
rsh, Object... params) throws SQLException {
+        return this.execute(conn, false, sql, rsh, params);
+    }
+
+    /**
+     * Execute an SQL statement, including a stored procedure call, which
+     * returns one or more result sets.
+     * Any parameters which are instances of {@link OutParameter} will be
+     * registered as OUT parameters.
+     * <p>
+     * Use this method when: a) running SQL statements that return multiple
+     * result sets; b) invoking a stored procedure that return result
+     * sets and OUT parameters.  Otherwise you may wish to use
+     * {@link #query(java.lang.String, org.apache.commons.dbutils.ResultSetHandler, java.lang.Object...)
}
+     * (if there are no OUT parameters) or
+     * {@link #execute(java.lang.String, java.lang.Object...) }
+     * (if there are no result sets).
+     *
+     * @param <T> The type of object that the handler returns
+     * @param sql The SQL to execute.
+     * @param rsh The result set handler
+     * @param params The query replacement parameters.
+     * @return A list of objects generated by the handler
+     * @throws SQLException if a database access error occurs
+     */
+    public <T> List<T> execute(String sql, ResultSetHandler<T> rsh, Object...
params) throws SQLException {
+        Connection conn = this.prepareConnection();
+
+        return this.execute(conn, true, sql, rsh, params);
+    }
+
+    /**
+     * Invokes the stored procedure via update after checking the parameters to
+     * ensure nothing is null.
+     * @param conn The connection to use for the update call.
+     * @param closeConn True if the connection should be closed, false otherwise.
+     * @param sql The SQL statement to execute.
+     * @param params An array of update replacement parameters.  Each row in
+     * this array is one set of update replacement values.
+     * @return The number of rows updated.
+     * @throws SQLException If there are database or parameter errors.
+     */
+    private int execute(Connection conn, boolean closeConn, String sql, Object... params)
throws SQLException {
+        if (conn == null) {
+            throw new SQLException("Null connection");
+        }
+
+        if (sql == null) {
+            if (closeConn) {
+                close(conn);
+            }
+            throw new SQLException("Null SQL statement");
+        }
+
+        CallableStatement stmt = null;
+        int rows = 0;
+
+        try {
+            stmt = this.prepareCall(conn, sql);
+            this.fillStatement(stmt, params);
+            stmt.execute();
+            rows = stmt.getUpdateCount();
+            this.retrieveOutParameters(stmt, params);
+
+        } catch (SQLException e) {
+            this.rethrow(e, sql, params);
+
+        } finally {
+            close(stmt);
+            if (closeConn) {
+                close(conn);
+            }
+        }
+
+        return rows;
+    }
+
+    /**
+     * Invokes the stored procedure via update after checking the parameters to
+     * ensure nothing is null.
+     * @param conn The connection to use for the update call.
+     * @param closeConn True if the connection should be closed, false otherwise.
+     * @param sql The SQL statement to execute.
+     * @param rsh The result set handler
+     * @param params An array of update replacement parameters.  Each row in
+     * this array is one set of update replacement values.
+     * @return List of all objects generated by the ResultSetHandler for all result sets
handled.
+     * @throws SQLException If there are database or parameter errors.
+     */
+    private <T> List<T> execute(Connection conn, boolean closeConn, String sql,
ResultSetHandler<T> rsh, Object... params) throws SQLException {
+        if (conn == null) {
+            throw new SQLException("Null connection");
+        }
+
+        if (sql == null) {
+            if (closeConn) {
+                close(conn);
+            }
+            throw new SQLException("Null SQL statement");
+        }
+
+        if (rsh == null) {
+            if (closeConn) {
+                close(conn);
+            }
+            throw new SQLException("Null ResultSetHandler");
+        }
+
+        CallableStatement stmt = null;
+        List<T> results = new LinkedList<T>();
+
+        try {
+            stmt = this.prepareCall(conn, sql);
+            this.fillStatement(stmt, params);
+            boolean moreResultSets = stmt.execute();
+            // Handle multiple result sets by passing them through the handler
+            // retaining the final result
+            ResultSet rs = null;
+            while (moreResultSets) {
+                try {
+                    rs = this.wrap(stmt.getResultSet());
+                    results.add(rsh.handle(rs));
+                    moreResultSets = stmt.getMoreResults();
+
+                } finally {
+                    close(rs);
+                }
+            }
+            this.retrieveOutParameters(stmt, params);
+
+        } catch (SQLException e) {
+            this.rethrow(e, sql, params);
+
+        } finally {
+            close(stmt);
+            if (closeConn) {
+                close(conn);
+            }
+        }
+
+        return results;
+    }
+
+    /**
+     * Set the value on all the {@link OutParameter} instances in the
+     * <code>params</code> array using the OUT parameter values from the
+     * <code>stmt</code>.
+     * @param stmt the statement from which to retrieve OUT parameter values
+     * @param params the parameter array for the statement invocation
+     * @throws SQLException when the value could not be retrieved from the
+     * statement.
+     */
+    private void retrieveOutParameters(CallableStatement stmt, Object[] params) throws SQLException
{
+        if (params != null) {
+            for (int i = 0; i < params.length; i++) {
+                if (params[i] instanceof OutParameter) {
+                    ((OutParameter)params[i]).setValue(stmt, i + 1);
+                }
+            }
+        }
+    }
 }

http://git-wip-us.apache.org/repos/asf/commons-dbutils/blob/aa276d1a/src/test/java/org/apache/commons/dbutils/OutParameterTest.java
----------------------------------------------------------------------
diff --git a/src/test/java/org/apache/commons/dbutils/OutParameterTest.java b/src/test/java/org/apache/commons/dbutils/OutParameterTest.java
new file mode 100644
index 0000000..967c6e6
--- /dev/null
+++ b/src/test/java/org/apache/commons/dbutils/OutParameterTest.java
@@ -0,0 +1,77 @@
+/*
+ * Copyright 2014 The Apache Software Foundation.
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ *
+ *      http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.commons.dbutils;
+
+import static org.junit.Assert.assertEquals;
+import static org.mockito.Mockito.any;
+import static org.mockito.Mockito.eq;
+import static org.mockito.Mockito.times;
+import static org.mockito.Mockito.verify;
+import static org.mockito.Mockito.when;
+
+import java.sql.CallableStatement;
+import java.sql.Types;
+import org.junit.Before;
+import org.junit.Test;
+import org.mockito.Mock;
+import org.mockito.MockitoAnnotations;
+
+public class OutParameterTest {
+    private static final int INDEX = 2;
+    private static final int VALUE = 42;
+
+    @Mock CallableStatement stmt;
+
+    OutParameter<Number> parameter;
+
+    @Before
+    public void setUp() throws Exception {
+        MockitoAnnotations.initMocks(this);    // init the mocks
+
+        parameter = new OutParameter<Number>(Types.INTEGER, Number.class);
+    }
+
+    @Test
+    public void testSetValue() throws Exception {
+        when(stmt.getObject(INDEX)).thenReturn(VALUE);
+
+        parameter.setValue(stmt, INDEX);
+
+        assertEquals(VALUE, parameter.getValue());
+    }
+
+    @Test
+    public void testRegister() throws Exception {
+        parameter.register(stmt, INDEX);
+        verify(stmt, times(1)).registerOutParameter(INDEX, Types.INTEGER);
+        verify(stmt, times(0)).setObject(eq(INDEX), any(Number.class));
+
+        parameter.setValue(VALUE);
+        parameter.register(stmt, INDEX);
+        verify(stmt, times(2)).registerOutParameter(INDEX, Types.INTEGER);
+        verify(stmt, times(1)).setObject(INDEX, VALUE);
+    }
+
+    @Test
+    public void testRegisterAlternateConstructor() throws Exception {
+        parameter = new OutParameter<Number>(Types.INTEGER, Number.class, VALUE);
+        parameter.register(stmt, INDEX);
+        verify(stmt, times(1)).registerOutParameter(INDEX, Types.INTEGER);
+        verify(stmt, times(1)).setObject(INDEX, VALUE);
+    }
+
+}

http://git-wip-us.apache.org/repos/asf/commons-dbutils/blob/aa276d1a/src/test/java/org/apache/commons/dbutils/QueryRunnerTest.java
----------------------------------------------------------------------
diff --git a/src/test/java/org/apache/commons/dbutils/QueryRunnerTest.java b/src/test/java/org/apache/commons/dbutils/QueryRunnerTest.java
index 73082b4..55a9988 100644
--- a/src/test/java/org/apache/commons/dbutils/QueryRunnerTest.java
+++ b/src/test/java/org/apache/commons/dbutils/QueryRunnerTest.java
@@ -26,6 +26,7 @@ import static org.mockito.Mockito.times;
 import static org.mockito.Mockito.verify;
 import static org.mockito.Mockito.when;
 
+import java.sql.CallableStatement;
 import java.sql.Connection;
 import java.sql.ParameterMetaData;
 import java.sql.PreparedStatement;
@@ -33,6 +34,7 @@ import java.sql.ResultSet;
 import java.sql.ResultSetMetaData;
 import java.sql.SQLException;
 import java.sql.Statement;
+import java.sql.Types;
 import java.util.ArrayList;
 import java.util.List;
 
@@ -45,6 +47,8 @@ import org.junit.Before;
 import org.junit.Test;
 import org.mockito.Mock;
 import org.mockito.MockitoAnnotations;
+import org.mockito.invocation.InvocationOnMock;
+import org.mockito.stubbing.Answer;
 
 @SuppressWarnings("boxing") // test code
 public class QueryRunnerTest {
@@ -54,6 +58,7 @@ public class QueryRunnerTest {
     @Mock DataSource dataSource;
     @Mock Connection conn;
     @Mock PreparedStatement stmt;
+    @Mock CallableStatement call;
     @Mock ParameterMetaData meta;
     @Mock ResultSet results;
     @Mock ResultSetMetaData resultsMeta;
@@ -67,6 +72,10 @@ public class QueryRunnerTest {
         when(stmt.getParameterMetaData()).thenReturn(meta);
         when(stmt.getResultSet()).thenReturn(results);
         when(stmt.executeQuery()).thenReturn(results);
+        when(conn.prepareCall(any(String.class))).thenReturn(call);
+        when(call.getParameterMetaData()).thenReturn(meta);
+        when(call.getResultSet()).thenReturn(results);
+        when(call.getMoreResults()).thenReturn(false);
         when(results.next()).thenReturn(false);
 
          handler = new ArrayHandler();
@@ -401,7 +410,7 @@ public class QueryRunnerTest {
         runner = new QueryRunner();
         callGoodUpdate(conn);
     }
-    
+
     @Test
     public void testGoodInsert() throws Exception {
         results = mock(ResultSet.class);
@@ -533,6 +542,458 @@ public class QueryRunnerTest {
     }
 
     //
+    // Execute tests
+    //
+    private void callGoodExecute(Connection conn) throws Exception {
+        when(call.execute()).thenReturn(false);
+        when(call.getUpdateCount()).thenReturn(3);
+
+        when(meta.getParameterCount()).thenReturn(2);
+        int result = runner.execute(conn, "{call my_proc(?, ?)}", "unit", "test");
+
+        Assert.assertEquals(3, result);
+
+        verify(call, times(1)).execute();
+        verify(call, times(1)).close();    // make sure we closed the statement
+        verify(conn, times(0)).close();    // make sure we do not close the connection
+
+        // call the other variation of query
+        when(meta.getParameterCount()).thenReturn(0);
+        result = runner.execute(conn, "{call my_proc()}");
+
+        Assert.assertEquals(3, result);
+
+        verify(call, times(2)).execute();
+        verify(call, times(2)).close();    // make sure we closed the statement
+        verify(conn, times(0)).close();    // make sure we do not close the connection
+
+        // Test single OUT parameter
+        when(meta.getParameterCount()).thenReturn(1);
+        when(call.getObject(1)).thenReturn(42);
+        OutParameter<Integer> intParam =
+            new OutParameter<Integer>(Types.INTEGER, Integer.class);
+        result = runner.execute(conn, "{?= call my_proc()}", intParam);
+
+        Assert.assertEquals(42, intParam.getValue().intValue());
+        Assert.assertEquals(3, result);
+
+        verify(call, times(3)).execute();
+        verify(call, times(3)).close();    // make sure we closed the statement
+        verify(conn, times(0)).close();    // make sure we do not close the connection
+
+        // Test OUT parameters with IN parameters
+        when(meta.getParameterCount()).thenReturn(3);
+        when(call.getObject(1)).thenReturn(4242);
+        intParam.setValue(null);
+        result = runner.execute(conn, "{?= call my_proc(?, ?)}", intParam, "unit", "test");
+
+        Assert.assertEquals(4242, intParam.getValue().intValue());
+        Assert.assertEquals(3, result);
+
+        verify(call, times(4)).execute();
+        verify(call, times(4)).close();    // make sure we closed the statement
+        verify(conn, times(0)).close();    // make sure we do not close the connection
+
+        // Test INOUT parameters
+        when(meta.getParameterCount()).thenReturn(3);
+        when(call.getObject(1)).thenReturn(24);
+        when(call.getObject(3)).thenReturn("out");
+        intParam.setValue(null);
+        OutParameter<String> stringParam =
+            new OutParameter<String>(Types.VARCHAR, String.class, "in");
+        result = runner.execute(conn, "{?= call my_proc(?, ?)}", intParam, "test", stringParam);
+
+        Assert.assertEquals(24, intParam.getValue().intValue());
+        Assert.assertEquals("out", stringParam.getValue());
+        Assert.assertEquals(3, result);
+
+        verify(call, times(5)).execute();
+        verify(call, times(5)).close();    // make sure we closed the statement
+        verify(conn, times(0)).close();    // make sure we do not close the connection
+    }
+
+    private void callGoodExecute() throws Exception {
+        when(call.execute()).thenReturn(false);
+        when(call.getUpdateCount()).thenReturn(3);
+
+        when(meta.getParameterCount()).thenReturn(2);
+        int result = runner.execute("{call my_proc(?, ?)}", "unit", "test");
+
+        Assert.assertEquals(3, result);
+
+        verify(call, times(1)).execute();
+        verify(call, times(1)).close();    // make sure we closed the statement
+        verify(conn, times(1)).close();    // make sure we do not close the connection
+
+        // call the other variation of query
+        when(meta.getParameterCount()).thenReturn(0);
+        result = runner.execute("{call my_proc()}");
+
+        Assert.assertEquals(3, result);
+
+        verify(call, times(2)).execute();
+        verify(call, times(2)).close();    // make sure we closed the statement
+        verify(conn, times(2)).close();    // make sure we do not close the connection
+
+        // Test single OUT parameter
+        when(meta.getParameterCount()).thenReturn(1);
+        when(call.getObject(1)).thenReturn(42);
+        OutParameter<Integer> intParam =
+            new OutParameter<Integer>(Types.INTEGER, Integer.class);
+        result = runner.execute("{?= call my_proc()}", intParam);
+
+        Assert.assertEquals(42, intParam.getValue().intValue());
+        Assert.assertEquals(3, result);
+
+        verify(call, times(3)).execute();
+        verify(call, times(3)).close();    // make sure we closed the statement
+        verify(conn, times(3)).close();    // make sure we do not close the connection
+
+        // Test OUT parameters with IN parameters
+        when(meta.getParameterCount()).thenReturn(3);
+        when(call.getObject(1)).thenReturn(4242);
+        intParam.setValue(null);
+        result = runner.execute("{?= call my_proc(?, ?)}", intParam, "unit", "test");
+
+        Assert.assertEquals(4242, intParam.getValue().intValue());
+        Assert.assertEquals(3, result);
+
+        verify(call, times(4)).execute();
+        verify(call, times(4)).close();    // make sure we closed the statement
+        verify(conn, times(4)).close();    // make sure we do not close the connection
+
+        // Test INOUT parameters
+        when(meta.getParameterCount()).thenReturn(3);
+        when(call.getObject(1)).thenReturn(24);
+        when(call.getObject(3)).thenReturn("out");
+        intParam.setValue(null);
+        OutParameter<String> stringParam =
+            new OutParameter<String>(Types.VARCHAR, String.class, "in");
+        result = runner.execute("{?= call my_proc(?, ?)}", intParam, "test", stringParam);
+
+        Assert.assertEquals(24, intParam.getValue().intValue());
+        Assert.assertEquals("out", stringParam.getValue());
+        Assert.assertEquals(3, result);
+
+        verify(call, times(5)).execute();
+        verify(call, times(5)).close();    // make sure we closed the statement
+        verify(conn, times(5)).close();    // make sure we do not close the connection
+    }
+
+    @Test
+    public void testGoodExecute() throws Exception {
+        callGoodExecute();
+    }
+
+    @Test
+    public void testGoodExecutePmdTrue() throws Exception {
+        runner = new QueryRunner(true);
+        callGoodExecute(conn);
+    }
+
+    @Test
+    public void testGoodExecuteDefaultConstructor() throws Exception {
+        runner = new QueryRunner();
+        callGoodExecute(conn);
+    }
+
+    // helper method for calling execute when an exception is expected
+    private void callExecuteWithException(Object... params) throws Exception {
+        boolean caught = false;
+
+        try {
+            when(call.execute()).thenReturn(false);
+            when(meta.getParameterCount()).thenReturn(2);
+            runner.query("{call my_proc(?, ?)}", handler, params);
+
+        } catch(SQLException e) {
+            caught = true;
+        }
+
+        if(!caught)
+            fail("Exception never thrown, but expected");
+    }
+
+    @Test
+    public void testNoParamsExecute() throws Exception {
+        callExecuteWithException();
+    }
+
+    @Test
+    public void testTooFewParamsExecute() throws Exception {
+        callExecuteWithException("unit");
+    }
+
+    @Test
+    public void testTooManyParamsExecute() throws Exception {
+        callExecuteWithException("unit", "test", "fail");
+    }
+
+    @Test(expected=SQLException.class)
+    public void testNullConnectionExecute() throws Exception {
+        when(meta.getParameterCount()).thenReturn(2);
+        when(dataSource.getConnection()).thenReturn(null);
+
+        runner.execute("{call my_proc(?, ?)}", "unit", "test");
+    }
+
+    @Test(expected=SQLException.class)
+    public void testNullSqlExecute() throws Exception {
+        when(meta.getParameterCount()).thenReturn(2);
+
+        runner.execute(null);
+    }
+
+    @Test(expected=SQLException.class)
+    public void testNullHandlerExecute() throws Exception {
+        when(meta.getParameterCount()).thenReturn(2);
+
+        runner.execute("{call my_proc(?, ?)}");
+    }
+
+    @Test
+    public void testExecuteException() throws Exception {
+        doThrow(new SQLException()).when(stmt).execute();
+
+        callExecuteWithException(handler, "unit", "test");
+    }
+
+    //
+    // Execute with ResultSetHandler
+    //
+
+    @Test
+    public void testExecuteWithMultipleResultSets() throws Exception {
+        when(call.execute()).thenReturn(true);
+        when(call.getMoreResults()).thenAnswer(new Answer<Boolean>()
+        {
+            int count = 1;
+            @Override
+            public Boolean answer(InvocationOnMock invocation)
+            {
+                return ++count <= 3;
+            }
+        });
+        when(meta.getParameterCount()).thenReturn(0);
+        List<Object[]> objects = runner.execute("{call my_proc()}", handler);
+
+        Assert.assertEquals(3, objects.size());
+        verify(call, times(1)).execute();
+        verify(results, times(3)).close();
+        verify(call, times(1)).close();    // make sure we closed the statement
+        verify(conn, times(1)).close();    // make sure we close the connection
+
+    }
+
+    private void callGoodExecuteWithResultSet(Connection conn) throws Exception {
+        when(call.execute()).thenReturn(true);
+
+        when(meta.getParameterCount()).thenReturn(2);
+        runner.execute(conn, "{call my_proc(?, ?)}", handler, "unit", "test");
+
+        verify(call, times(1)).execute();
+        verify(results, times(1)).close();
+        verify(call, times(1)).close();    // make sure we closed the statement
+        verify(conn, times(0)).close();    // make sure we do not close the connection
+
+        // call the other variation of query
+        when(meta.getParameterCount()).thenReturn(0);
+        runner.execute(conn, "{call my_proc()}", handler);
+
+        verify(call, times(2)).execute();
+        verify(results, times(2)).close();
+        verify(call, times(2)).close();    // make sure we closed the statement
+        verify(conn, times(0)).close();    // make sure we do not close the connection
+
+        // Test single OUT parameter
+        when(meta.getParameterCount()).thenReturn(1);
+        when(call.getObject(1)).thenReturn(42);
+        OutParameter<Integer> intParam =
+            new OutParameter<Integer>(Types.INTEGER, Integer.class);
+        runner.execute(conn, "{?= call my_proc()}", handler, intParam);
+
+        Assert.assertEquals(42, intParam.getValue().intValue());
+
+        verify(call, times(3)).execute();
+        verify(results, times(3)).close();
+        verify(call, times(3)).close();    // make sure we closed the statement
+        verify(conn, times(0)).close();    // make sure we do not close the connection
+
+        // Test OUT parameters with IN parameters
+        when(meta.getParameterCount()).thenReturn(3);
+        when(call.getObject(1)).thenReturn(4242);
+        intParam.setValue(null);
+        runner.execute(conn, "{?= call my_proc(?, ?)}", handler, intParam, "unit", "test");
+
+        Assert.assertEquals(4242, intParam.getValue().intValue());
+
+        verify(call, times(4)).execute();
+        verify(results, times(4)).close();
+        verify(call, times(4)).close();    // make sure we closed the statement
+        verify(conn, times(0)).close();    // make sure we do not close the connection
+
+        // Test INOUT parameters
+        when(meta.getParameterCount()).thenReturn(3);
+        when(call.getObject(1)).thenReturn(24);
+        when(call.getObject(3)).thenReturn("out");
+        intParam.setValue(null);
+        OutParameter<String> stringParam =
+            new OutParameter<String>(Types.VARCHAR, String.class, "in");
+        runner.execute(conn, "{?= call my_proc(?, ?)}", handler, intParam, "test", stringParam);
+
+        Assert.assertEquals(24, intParam.getValue().intValue());
+        Assert.assertEquals("out", stringParam.getValue());
+
+        verify(call, times(5)).execute();
+        verify(results, times(5)).close();
+        verify(call, times(5)).close();    // make sure we closed the statement
+        verify(conn, times(0)).close();    // make sure we do not close the connection
+    }
+
+    private void callGoodExecuteWithResultSet() throws Exception {
+        when(call.execute()).thenReturn(true);
+
+        when(meta.getParameterCount()).thenReturn(2);
+        runner.execute("{call my_proc(?, ?)}", handler, "unit", "test");
+
+        verify(call, times(1)).execute();
+        verify(results, times(1)).close();
+        verify(call, times(1)).close();    // make sure we closed the statement
+        verify(conn, times(1)).close();    // make sure we do not close the connection
+
+        // call the other variation of query
+        when(meta.getParameterCount()).thenReturn(0);
+        runner.execute("{call my_proc()}", handler);
+
+        verify(call, times(2)).execute();
+        verify(results, times(2)).close();
+        verify(call, times(2)).close();    // make sure we closed the statement
+        verify(conn, times(2)).close();    // make sure we do not close the connection
+
+        // Test single OUT parameter
+        when(meta.getParameterCount()).thenReturn(1);
+        when(call.getObject(1)).thenReturn(42);
+        OutParameter<Integer> intParam =
+            new OutParameter<Integer>(Types.INTEGER, Integer.class);
+        runner.execute("{?= call my_proc()}", handler, intParam);
+
+        Assert.assertEquals(42, intParam.getValue().intValue());
+
+        verify(call, times(3)).execute();
+        verify(results, times(3)).close();
+        verify(call, times(3)).close();    // make sure we closed the statement
+        verify(conn, times(3)).close();    // make sure we do not close the connection
+
+        // Test OUT parameters with IN parameters
+        when(meta.getParameterCount()).thenReturn(3);
+        when(call.getObject(1)).thenReturn(4242);
+        intParam.setValue(null);
+        runner.execute("{?= call my_proc(?, ?)}", handler, intParam, "unit", "test");
+
+        Assert.assertEquals(4242, intParam.getValue().intValue());
+
+        verify(call, times(4)).execute();
+        verify(results, times(4)).close();
+        verify(call, times(4)).close();    // make sure we closed the statement
+        verify(conn, times(4)).close();    // make sure we do not close the connection
+
+        // Test INOUT parameters
+        when(meta.getParameterCount()).thenReturn(3);
+        when(call.getObject(1)).thenReturn(24);
+        when(call.getObject(3)).thenReturn("out");
+        intParam.setValue(null);
+        OutParameter<String> stringParam =
+            new OutParameter<String>(Types.VARCHAR, String.class, "in");
+        runner.execute("{?= call my_proc(?, ?)}", handler, intParam, "test", stringParam);
+
+        Assert.assertEquals(24, intParam.getValue().intValue());
+        Assert.assertEquals("out", stringParam.getValue());
+
+        verify(call, times(5)).execute();
+        verify(results, times(5)).close();
+        verify(call, times(5)).close();    // make sure we closed the statement
+        verify(conn, times(5)).close();    // make sure we do not close the connection
+    }
+
+    @Test
+    public void testGoodExecuteWithResultSet() throws Exception {
+        callGoodExecuteWithResultSet();
+    }
+
+    @Test
+    public void testGoodExecuteWithResultSetPmdTrue() throws Exception {
+        runner = new QueryRunner(true);
+        callGoodExecuteWithResultSet(conn);
+    }
+
+    @Test
+    public void testGoodExecuteWithResultSetDefaultConstructor() throws Exception {
+        runner = new QueryRunner();
+        callGoodExecuteWithResultSet(conn);
+    }
+
+    // helper method for calling execute when an exception is expected
+    private void callExecuteWithResultSetWithException(Object... params) throws Exception
{
+        boolean caught = false;
+
+        try {
+            when(call.execute()).thenReturn(true);
+            when(meta.getParameterCount()).thenReturn(2);
+            runner.query("{call my_proc(?, ?)}", handler, params);
+
+        } catch(SQLException e) {
+            caught = true;
+        }
+
+        if(!caught)
+            fail("Exception never thrown, but expected");
+    }
+
+    @Test
+    public void testNoParamsExecuteWithResultSet() throws Exception {
+        callExecuteWithResultSetWithException();
+    }
+
+    @Test
+    public void testTooFewParamsExecuteWithResultSet() throws Exception {
+        callExecuteWithResultSetWithException("unit");
+    }
+
+    @Test
+    public void testTooManyParamsExecuteWithResultSet() throws Exception {
+        callExecuteWithResultSetWithException("unit", "test", "fail");
+    }
+
+    @Test(expected=SQLException.class)
+    public void testNullConnectionExecuteWithResultSet() throws Exception {
+        when(meta.getParameterCount()).thenReturn(2);
+        when(dataSource.getConnection()).thenReturn(null);
+
+        runner.execute("{call my_proc(?, ?)}", handler, "unit", "test");
+    }
+
+    @Test(expected=SQLException.class)
+    public void testNullSqlExecuteWithResultSet() throws Exception {
+        when(meta.getParameterCount()).thenReturn(2);
+
+        runner.execute(null, handler);
+    }
+
+    @Test(expected=SQLException.class)
+    public void testNullHandlerExecuteWithResultSet() throws Exception {
+        when(meta.getParameterCount()).thenReturn(2);
+
+        runner.execute("{call my_proc(?, ?)}", (ResultSetHandler)null);
+    }
+
+    @Test
+    public void testExecuteWithResultSetException() throws Exception {
+        doThrow(new SQLException()).when(stmt).execute();
+
+        callExecuteWithResultSetWithException(handler, "unit", "test");
+    }
+
+    //
     // Random tests
     //
     class MyBean {


Mime
View raw message