You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@commons.apache.org by th...@apache.org on 2015/04/17 03:38:47 UTC
svn commit: r1674183 - in /commons/proper/dbutils/trunk/src: changes/
main/java/org/apache/commons/dbutils/ test/java/org/apache/commons/dbutils/
Author: thecarlhall
Date: Fri Apr 17 01:38:47 2015
New Revision: 1674183
URL: http://svn.apache.org/r1674183
Log:
DBUTILS-50 Apply user patch to support CallableStatement "out" parameters
Added:
commons/proper/dbutils/trunk/src/main/java/org/apache/commons/dbutils/OutParameter.java
commons/proper/dbutils/trunk/src/test/java/org/apache/commons/dbutils/OutParameterTest.java
Modified:
commons/proper/dbutils/trunk/src/changes/changes.xml
commons/proper/dbutils/trunk/src/main/java/org/apache/commons/dbutils/AbstractQueryRunner.java
commons/proper/dbutils/trunk/src/main/java/org/apache/commons/dbutils/QueryRunner.java
commons/proper/dbutils/trunk/src/test/java/org/apache/commons/dbutils/QueryRunnerTest.java
Modified: commons/proper/dbutils/trunk/src/changes/changes.xml
URL: http://svn.apache.org/viewvc/commons/proper/dbutils/trunk/src/changes/changes.xml?rev=1674183&r1=1674182&r2=1674183&view=diff
==============================================================================
--- commons/proper/dbutils/trunk/src/changes/changes.xml (original)
+++ commons/proper/dbutils/trunk/src/changes/changes.xml Fri Apr 17 01:38:47 2015
@@ -65,6 +65,9 @@ The <action> type attribute can be add,u
<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">
Modified: commons/proper/dbutils/trunk/src/main/java/org/apache/commons/dbutils/AbstractQueryRunner.java
URL: http://svn.apache.org/viewvc/commons/proper/dbutils/trunk/src/main/java/org/apache/commons/dbutils/AbstractQueryRunner.java?rev=1674183&r1=1674182&r2=1674183&view=diff
==============================================================================
--- commons/proper/dbutils/trunk/src/main/java/org/apache/commons/dbutils/AbstractQueryRunner.java (original)
+++ commons/proper/dbutils/trunk/src/main/java/org/apache/commons/dbutils/AbstractQueryRunner.java Fri Apr 17 01:38:47 2015
@@ -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 AbstractQueryRunne
}
/**
+ * 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 AbstractQueryRunne
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
Added: commons/proper/dbutils/trunk/src/main/java/org/apache/commons/dbutils/OutParameter.java
URL: http://svn.apache.org/viewvc/commons/proper/dbutils/trunk/src/main/java/org/apache/commons/dbutils/OutParameter.java?rev=1674183&view=auto
==============================================================================
--- commons/proper/dbutils/trunk/src/main/java/org/apache/commons/dbutils/OutParameter.java (added)
+++ commons/proper/dbutils/trunk/src/main/java/org/apache/commons/dbutils/OutParameter.java Fri Apr 17 01:38:47 2015
@@ -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 + '}';
+ }
+}
Modified: commons/proper/dbutils/trunk/src/main/java/org/apache/commons/dbutils/QueryRunner.java
URL: http://svn.apache.org/viewvc/commons/proper/dbutils/trunk/src/main/java/org/apache/commons/dbutils/QueryRunner.java?rev=1674183&r1=1674182&r2=1674183&view=diff
==============================================================================
--- commons/proper/dbutils/trunk/src/main/java/org/apache/commons/dbutils/QueryRunner.java (original)
+++ commons/proper/dbutils/trunk/src/main/java/org/apache/commons/dbutils/QueryRunner.java Fri Apr 17 01:38:47 2015
@@ -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 Abstrac
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);
+ }
+ }
+ }
+ }
}
Added: commons/proper/dbutils/trunk/src/test/java/org/apache/commons/dbutils/OutParameterTest.java
URL: http://svn.apache.org/viewvc/commons/proper/dbutils/trunk/src/test/java/org/apache/commons/dbutils/OutParameterTest.java?rev=1674183&view=auto
==============================================================================
--- commons/proper/dbutils/trunk/src/test/java/org/apache/commons/dbutils/OutParameterTest.java (added)
+++ commons/proper/dbutils/trunk/src/test/java/org/apache/commons/dbutils/OutParameterTest.java Fri Apr 17 01:38:47 2015
@@ -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);
+ }
+
+}
Modified: commons/proper/dbutils/trunk/src/test/java/org/apache/commons/dbutils/QueryRunnerTest.java
URL: http://svn.apache.org/viewvc/commons/proper/dbutils/trunk/src/test/java/org/apache/commons/dbutils/QueryRunnerTest.java?rev=1674183&r1=1674182&r2=1674183&view=diff
==============================================================================
--- commons/proper/dbutils/trunk/src/test/java/org/apache/commons/dbutils/QueryRunnerTest.java (original)
+++ commons/proper/dbutils/trunk/src/test/java/org/apache/commons/dbutils/QueryRunnerTest.java Fri Apr 17 01:38:47 2015
@@ -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 {