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 <[email protected]> Authored: Fri Apr 17 01:38:47 2015 +0000 Committer: Carl Franklin Hall <[email protected]> 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 {
