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 2020/01/05 02:36:49 UTC
[commons-dbutils] 01/05: DBUTILS-131 Optimization to use
PreparedStatement only when params are available
This is an automated email from the ASF dual-hosted git repository.
thecarlhall pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/commons-dbutils.git
commit 53037495f2f7cb1d46d42c26cbf1493a29f4a610
Author: Carl Hall <th...@apache.org>
AuthorDate: Sat Jan 4 15:27:43 2020 -0800
DBUTILS-131 Optimization to use PreparedStatement only when params are available
---
src/changes/changes.xml | 7 +-
.../org/apache/commons/dbutils/QueryRunner.java | 42 +++--
.../commons/dbutils/AsyncQueryRunnerTest.java | 121 ++++++++-------
.../apache/commons/dbutils/QueryRunnerTest.java | 171 ++++++++++++---------
4 files changed, 199 insertions(+), 142 deletions(-)
diff --git a/src/changes/changes.xml b/src/changes/changes.xml
index 670e321..19f77fe 100644
--- a/src/changes/changes.xml
+++ b/src/changes/changes.xml
@@ -45,8 +45,8 @@ The <action> type attribute can be add,update,fix,remove.
<body>
<release version="2.0" date="2020-01-DD" description="New features and bug fixes.">
- <action due-to="thecarlhall" type="update">
- Java 1.7 now required. clirr, checkstyle, and spotbugs configured as part of default build.
+ <action dev="thecarlhall" type="fix" issue="DBUTILS-131" due-to="yairlenga">
+ Speedup query calls without parameters; Use PreparedStatement only when parameters are present.
</action>
<action dev="thecarlhall" type="fix">
Always copy Date, Time, Timestamp on get and set in SqlNullCheckedResultSet.
@@ -69,6 +69,9 @@ The <action> type attribute can be add,update,fix,remove.
<action dev="ggregory" type="fix" issue="DBUTILS-139" due-to="Gary Gregory">
Update Java requirement from version 6 to 7.
</action>
+ <action due-to="thecarlhall" type="update">
+ clirr, checkstyle, and spotbugs configured as part of default build.
+ </action>
</release>
<release version="1.7" date="2017-07-20" description="Bug fixes and separate column & property handlers using SPI">
diff --git a/src/main/java/org/apache/commons/dbutils/QueryRunner.java b/src/main/java/org/apache/commons/dbutils/QueryRunner.java
index dfe59ab..f76ce19 100644
--- a/src/main/java/org/apache/commons/dbutils/QueryRunner.java
+++ b/src/main/java/org/apache/commons/dbutils/QueryRunner.java
@@ -377,14 +377,20 @@ public class QueryRunner extends AbstractQueryRunner {
throw new SQLException("Null ResultSetHandler");
}
- PreparedStatement stmt = null;
+ Statement stmt = null;
ResultSet rs = null;
T result = null;
try {
- stmt = this.prepareStatement(conn, sql);
- this.fillStatement(stmt, params);
- rs = this.wrap(stmt.executeQuery());
+ if (params != null && params.length > 0) {
+ PreparedStatement ps = this.prepareStatement(conn, sql);
+ stmt = ps;
+ this.fillStatement(ps, params);
+ rs = this.wrap(ps.executeQuery());
+ } else {
+ stmt = conn.createStatement();
+ rs = this.wrap(stmt.executeQuery(sql));
+ }
result = rsh.handle(rs);
} catch (final SQLException e) {
@@ -516,13 +522,19 @@ public class QueryRunner extends AbstractQueryRunner {
throw new SQLException("Null SQL statement");
}
- PreparedStatement stmt = null;
+ Statement stmt = null;
int rows = 0;
try {
- stmt = this.prepareStatement(conn, sql);
- this.fillStatement(stmt, params);
- rows = stmt.executeUpdate();
+ if (params != null && params.length > 0) {
+ PreparedStatement ps = this.prepareStatement(conn, sql);
+ stmt = ps;
+ this.fillStatement(ps, params);
+ rows = ps.executeUpdate();
+ } else {
+ stmt = conn.createStatement();
+ rows = stmt.executeUpdate(sql);
+ }
} catch (final SQLException e) {
this.rethrow(e, sql, params);
@@ -634,13 +646,19 @@ public class QueryRunner extends AbstractQueryRunner {
throw new SQLException("Null ResultSetHandler");
}
- PreparedStatement stmt = null;
+ Statement stmt = null;
T generatedKeys = null;
try {
- stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
- this.fillStatement(stmt, params);
- stmt.executeUpdate();
+ if (params != null && params.length > 0) {
+ PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
+ stmt = ps;
+ this.fillStatement(ps, params);
+ ps.executeUpdate();
+ } else {
+ stmt = conn.createStatement();
+ stmt.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
+ }
final ResultSet resultSet = stmt.getGeneratedKeys();
generatedKeys = rsh.handle(resultSet);
} catch (final SQLException e) {
diff --git a/src/test/java/org/apache/commons/dbutils/AsyncQueryRunnerTest.java b/src/test/java/org/apache/commons/dbutils/AsyncQueryRunnerTest.java
index f900feb..44db6d2 100644
--- a/src/test/java/org/apache/commons/dbutils/AsyncQueryRunnerTest.java
+++ b/src/test/java/org/apache/commons/dbutils/AsyncQueryRunnerTest.java
@@ -29,6 +29,7 @@ import java.sql.ParameterMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
+import java.sql.Statement;
import java.util.concurrent.ExecutionException;
import java.util.concurrent.Executors;
import java.util.concurrent.Future;
@@ -49,7 +50,8 @@ public class AsyncQueryRunnerTest {
@Mock DataSource dataSource;
@Mock Connection conn;
- @Mock PreparedStatement stmt;
+ @Mock PreparedStatement prepStmt;
+ @Mock Statement stmt;
@Mock ParameterMetaData meta;
@Mock ResultSet results;
@@ -58,10 +60,16 @@ public class AsyncQueryRunnerTest {
MockitoAnnotations.initMocks(this);
when(dataSource.getConnection()).thenReturn(conn);
- when(conn.prepareStatement(any(String.class))).thenReturn(stmt);
- when(stmt.getParameterMetaData()).thenReturn(meta);
+
+ when(conn.prepareStatement(any(String.class))).thenReturn(prepStmt);
+ when(prepStmt.getParameterMetaData()).thenReturn(meta);
+ when(prepStmt.getResultSet()).thenReturn(results);
+ when(prepStmt.executeQuery()).thenReturn(results);
+
+ when(conn.createStatement()).thenReturn(stmt);
when(stmt.getResultSet()).thenReturn(results);
- when(stmt.executeQuery()).thenReturn(results);
+ when(stmt.executeQuery(any(String.class))).thenReturn(results);
+
when(results.next()).thenReturn(false);
handler = new ArrayHandler();
@@ -77,9 +85,9 @@ public class AsyncQueryRunnerTest {
future.get();
- verify(stmt, times(2)).addBatch();
- verify(stmt, times(1)).executeBatch();
- verify(stmt, times(1)).close(); // make sure we closed the statement
+ verify(prepStmt, times(2)).addBatch();
+ verify(prepStmt, times(1)).executeBatch();
+ verify(prepStmt, times(1)).close(); // make sure we closed the statement
verify(conn, times(0)).close(); // make sure we closed the connection
}
@@ -89,9 +97,9 @@ public class AsyncQueryRunnerTest {
future.get();
- verify(stmt, times(2)).addBatch();
- verify(stmt, times(1)).executeBatch();
- verify(stmt, times(1)).close(); // make sure we closed the statement
+ verify(prepStmt, times(2)).addBatch();
+ verify(prepStmt, times(1)).executeBatch();
+ verify(prepStmt, times(1)).close(); // make sure we closed the statement
verify(conn, times(1)).close(); // make sure we closed the connection
}
@@ -138,9 +146,9 @@ public class AsyncQueryRunnerTest {
future.get();
- verify(stmt, times(2)).addBatch();
- verify(stmt, times(1)).executeBatch();
- verify(stmt, times(1)).close(); // make sure the statement is closed
+ verify(prepStmt, times(2)).addBatch();
+ verify(prepStmt, times(1)).executeBatch();
+ verify(prepStmt, times(1)).close(); // make sure the statement is closed
verify(conn, times(1)).close(); // make sure the connection is closed
} catch(final Exception e) {
caught = true;
@@ -195,7 +203,7 @@ public class AsyncQueryRunnerTest {
public void testAddBatchException() throws Exception {
final String[][] params = new String[][] { { "unit", "unit" }, { "test", "test" } };
- doThrow(new SQLException()).when(stmt).addBatch();
+ doThrow(new SQLException()).when(prepStmt).addBatch();
callBatchWithException("select * from blah where ? = ?", params);
}
@@ -204,7 +212,7 @@ public class AsyncQueryRunnerTest {
public void testExecuteBatchException() throws Exception {
final String[][] params = new String[][] { { "unit", "unit" }, { "test", "test" } };
- doThrow(new SQLException()).when(stmt).executeBatch();
+ doThrow(new SQLException()).when(prepStmt).executeBatch();
callBatchWithException("select * from blah where ? = ?", params);
}
@@ -215,39 +223,43 @@ public class AsyncQueryRunnerTest {
//
private void callGoodQuery(final Connection conn) throws Exception {
when(meta.getParameterCount()).thenReturn(2);
- runner.query(conn, "select * from blah where ? = ?", handler, "unit", "test").get();
+ String sql = "select * from blah where ? = ?";
+ runner.query(conn, sql, handler, "unit", "test").get();
- verify(stmt, times(1)).executeQuery();
+ verify(prepStmt, times(1)).executeQuery();
verify(results, times(1)).close();
- verify(stmt, times(1)).close(); // make sure we closed the statement
+ verify(prepStmt, times(1)).close(); // make sure we closed the statement
verify(conn, times(0)).close(); // make sure we closed the connection
// call the other variation of query
when(meta.getParameterCount()).thenReturn(0);
- runner.query(conn, "select * from blah", handler).get();
+ sql = "select * from blah";
+ runner.query(conn, sql, handler).get();
- verify(stmt, times(2)).executeQuery();
+ verify(stmt, times(1)).executeQuery(sql);
verify(results, times(2)).close();
- verify(stmt, times(2)).close(); // make sure we closed the statement
+ verify(stmt, times(1)).close(); // make sure we closed the statement
verify(conn, times(0)).close(); // make sure we closed the connection
}
private void callGoodQuery() throws Exception {
when(meta.getParameterCount()).thenReturn(2);
- runner.query("select * from blah where ? = ?", handler, "unit", "test").get();
+ String sql = "select * from blah where ? = ?";
+ runner.query(sql, handler, "unit", "test").get();
- verify(stmt, times(1)).executeQuery();
+ verify(prepStmt, times(1)).executeQuery();
verify(results, times(1)).close();
- verify(stmt, times(1)).close(); // make sure we closed the statement
+ verify(prepStmt, times(1)).close(); // make sure we closed the statement
verify(conn, times(1)).close(); // make sure we closed the connection
// call the other variation of query
when(meta.getParameterCount()).thenReturn(0);
- runner.query("select * from blah", handler).get();
+ sql = "select * from blah";
+ runner.query(sql, handler).get();
- verify(stmt, times(2)).executeQuery();
+ verify(stmt, times(1)).executeQuery(sql);
verify(results, times(2)).close();
- verify(stmt, times(2)).close(); // make sure we closed the statement
+ verify(stmt, times(1)).close(); // make sure we closed the statement
verify(conn, times(2)).close(); // make sure we closed the connection
}
@@ -278,9 +290,9 @@ public class AsyncQueryRunnerTest {
when(meta.getParameterCount()).thenReturn(2);
runner.query("select * from blah where ? = ?", handler, params).get();
- verify(stmt, times(1)).executeQuery();
+ verify(prepStmt, times(1)).executeQuery();
verify(results, times(1)).close();
- verify(stmt, times(1)).close(); // make sure we closed the statement
+ verify(prepStmt, times(1)).close(); // make sure we closed the statement
verify(conn, times(1)).close(); // make sure we closed the connection
} catch(final Exception e) {
caught = true;
@@ -293,7 +305,7 @@ public class AsyncQueryRunnerTest {
@Test
public void testNoParamsQuery() throws Exception {
- callQueryWithException();
+ callGoodQuery();
}
@Test
@@ -330,7 +342,7 @@ public class AsyncQueryRunnerTest {
@Test
public void testExecuteQueryException() throws Exception {
- doThrow(new SQLException()).when(stmt).executeQuery();
+ doThrow(new SQLException()).when(prepStmt).executeQuery();
callQueryWithException(handler, "unit", "test");
}
@@ -341,51 +353,56 @@ public class AsyncQueryRunnerTest {
//
private void callGoodUpdate(final Connection conn) throws Exception {
when(meta.getParameterCount()).thenReturn(2);
- runner.update(conn, "update blah set ? = ?", "unit", "test").get();
+ String sql = "update blah set ? = ?";
+ runner.update(conn, sql, "unit", "test").get();
- verify(stmt, times(1)).executeUpdate();
- verify(stmt, times(1)).close(); // make sure we closed the statement
+ verify(prepStmt, times(1)).executeUpdate();
+ verify(prepStmt, times(1)).close(); // make sure we closed the statement
verify(conn, times(0)).close(); // make sure we closed the connection
// call the other variation
when(meta.getParameterCount()).thenReturn(0);
- runner.update(conn, "update blah set unit = test").get();
+ sql = "update blah set unit = test";
+ runner.update(conn, sql).get();
- verify(stmt, times(2)).executeUpdate();
- verify(stmt, times(2)).close(); // make sure we closed the statement
+ verify(stmt, times(1)).executeUpdate(sql);
+ verify(stmt, times(1)).close(); // make sure we closed the statement
verify(conn, times(0)).close(); // make sure we closed the connection
// call the other variation
when(meta.getParameterCount()).thenReturn(1);
- runner.update(conn, "update blah set unit = ?", "test").get();
+ sql = "update blah set unit = ?";
+ runner.update(conn, sql, "test").get();
- verify(stmt, times(3)).executeUpdate();
- verify(stmt, times(3)).close(); // make sure we closed the statement
+ verify(prepStmt, times(2)).executeUpdate();
+ verify(prepStmt, times(2)).close(); // make sure we closed the statement
verify(conn, times(0)).close(); // make sure we closed the connection
}
private void callGoodUpdate() throws Exception {
when(meta.getParameterCount()).thenReturn(2);
- runner.update("update blah set ? = ?", "unit", "test").get();
+ String sql = "update blah set ? = ?";
+ runner.update(sql, "unit", "test").get();
- verify(stmt, times(1)).executeUpdate();
- verify(stmt, times(1)).close(); // make sure we closed the statement
+ verify(prepStmt, times(1)).executeUpdate();
+ verify(prepStmt, times(1)).close(); // make sure we closed the statement
verify(conn, times(1)).close(); // make sure we closed the connection
// call the other variation
when(meta.getParameterCount()).thenReturn(0);
- runner.update("update blah set unit = test").get();
+ sql = "update blah set unit = test";
+ runner.update(sql).get();
- verify(stmt, times(2)).executeUpdate();
- verify(stmt, times(2)).close(); // make sure we closed the statement
+ verify(stmt, times(1)).executeUpdate(sql);
+ verify(stmt, times(1)).close(); // make sure we closed the statement
verify(conn, times(2)).close(); // make sure we closed the connection
// call the other variation
when(meta.getParameterCount()).thenReturn(1);
runner.update("update blah set unit = ?", "test").get();
- verify(stmt, times(3)).executeUpdate();
- verify(stmt, times(3)).close(); // make sure we closed the statement
+ verify(prepStmt, times(2)).executeUpdate();
+ verify(prepStmt, times(2)).close(); // make sure we closed the statement
verify(conn, times(3)).close(); // make sure we closed the connection
}
@@ -415,8 +432,8 @@ public class AsyncQueryRunnerTest {
when(meta.getParameterCount()).thenReturn(2);
runner.update("select * from blah where ? = ?", params).get();
- verify(stmt, times(1)).executeUpdate();
- verify(stmt, times(1)).close(); // make sure we closed the statement
+ verify(prepStmt, times(1)).executeUpdate();
+ verify(prepStmt, times(1)).close(); // make sure we closed the statement
verify(conn, times(1)).close(); // make sure we closed the connection
} catch(final Exception e) {
caught = true;
@@ -429,7 +446,7 @@ public class AsyncQueryRunnerTest {
@Test
public void testNoParamsUpdate() throws Exception {
- callUpdateWithException();
+ callGoodUpdate();
}
@Test
@@ -480,7 +497,7 @@ public class AsyncQueryRunnerTest {
@Test
public void testExecuteUpdateException() throws Exception {
- doThrow(new SQLException()).when(stmt).executeUpdate();
+ doThrow(new SQLException()).when(prepStmt).executeUpdate();
callUpdateWithException("unit", "test");
}
diff --git a/src/test/java/org/apache/commons/dbutils/QueryRunnerTest.java b/src/test/java/org/apache/commons/dbutils/QueryRunnerTest.java
index 727c2c9..2802c16 100644
--- a/src/test/java/org/apache/commons/dbutils/QueryRunnerTest.java
+++ b/src/test/java/org/apache/commons/dbutils/QueryRunnerTest.java
@@ -17,9 +17,9 @@
package org.apache.commons.dbutils;
import static org.junit.Assert.fail;
-import static org.mockito.Matchers.any;
-import static org.mockito.Matchers.eq;
+import static org.mockito.Mockito.any;
import static org.mockito.Mockito.doThrow;
+import static org.mockito.Mockito.eq;
import static org.mockito.Mockito.mock;
import static org.mockito.Mockito.never;
import static org.mockito.Mockito.times;
@@ -46,6 +46,7 @@ import org.junit.Assert;
import org.junit.Before;
import org.junit.Test;
import org.mockito.Mock;
+import org.mockito.Mockito;
import org.mockito.MockitoAnnotations;
import org.mockito.invocation.InvocationOnMock;
import org.mockito.stubbing.Answer;
@@ -57,7 +58,8 @@ public class QueryRunnerTest {
@Mock DataSource dataSource;
@Mock Connection conn;
- @Mock PreparedStatement stmt;
+ @Mock PreparedStatement prepStmt;
+ @Mock Statement stmt;
@Mock CallableStatement call;
@Mock ParameterMetaData meta;
@Mock ResultSet results;
@@ -68,14 +70,21 @@ public class QueryRunnerTest {
MockitoAnnotations.initMocks(this); // init the mocks
when(dataSource.getConnection()).thenReturn(conn);
- when(conn.prepareStatement(any(String.class))).thenReturn(stmt);
- when(stmt.getParameterMetaData()).thenReturn(meta);
+
+ when(conn.prepareStatement(any(String.class))).thenReturn(prepStmt);
+ when(prepStmt.getParameterMetaData()).thenReturn(meta);
+ when(prepStmt.getResultSet()).thenReturn(results);
+ when(prepStmt.executeQuery()).thenReturn(results);
+
+ when(conn.createStatement()).thenReturn(stmt);
when(stmt.getResultSet()).thenReturn(results);
- when(stmt.executeQuery()).thenReturn(results);
+ when(stmt.executeQuery(any(String.class))).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();
@@ -90,9 +99,9 @@ public class QueryRunnerTest {
when(meta.getParameterCount()).thenReturn(2);
runner.batch(conn, "select * from blah where ? = ?", params);
- verify(stmt, times(2)).addBatch();
- verify(stmt, times(1)).executeBatch();
- verify(stmt, times(1)).close(); // make sure we closed the statement
+ verify(prepStmt, times(2)).addBatch();
+ verify(prepStmt, times(1)).executeBatch();
+ verify(prepStmt, times(1)).close(); // make sure we closed the statement
verify(conn, times(0)).close(); // make sure we do not close the connection, since QueryRunner.batch(Connection, String, Object[][]) does not close connections
}
@@ -100,9 +109,9 @@ public class QueryRunnerTest {
when(meta.getParameterCount()).thenReturn(2);
runner.batch("select * from blah where ? = ?", params);
- verify(stmt, times(2)).addBatch();
- verify(stmt, times(1)).executeBatch();
- verify(stmt, times(1)).close(); // make sure we closed the statement
+ verify(prepStmt, times(2)).addBatch();
+ verify(prepStmt, times(1)).executeBatch();
+ verify(prepStmt, times(1)).close(); // make sure we closed the statement
verify(conn, times(1)).close(); // make sure we closed the connection
}
@@ -145,9 +154,9 @@ public class QueryRunnerTest {
try {
runner.batch(sql, params);
- verify(stmt, times(2)).addBatch();
- verify(stmt, times(1)).executeBatch();
- verify(stmt, times(1)).close(); // make sure the statement is closed
+ verify(prepStmt, times(2)).addBatch();
+ verify(prepStmt, times(1)).executeBatch();
+ verify(prepStmt, times(1)).close(); // make sure the statement is closed
verify(conn, times(1)).close(); // make sure the connection is closed
} catch(final SQLException e) {
caught = true;
@@ -202,7 +211,7 @@ public class QueryRunnerTest {
public void testAddBatchException() throws Exception {
final String[][] params = new String[][] { { "unit", "unit" }, { "test", "test" } };
- doThrow(new SQLException()).when(stmt).addBatch();
+ doThrow(new SQLException()).when(prepStmt).addBatch();
callBatchWithException("select * from blah where ? = ?", params);
}
@@ -211,7 +220,7 @@ public class QueryRunnerTest {
public void testExecuteBatchException() throws Exception {
final String[][] params = new String[][] { { "unit", "unit" }, { "test", "test" } };
- doThrow(new SQLException()).when(stmt).executeBatch();
+ doThrow(new SQLException()).when(prepStmt).executeBatch();
callBatchWithException("select * from blah where ? = ?", params);
}
@@ -222,39 +231,43 @@ public class QueryRunnerTest {
//
private void callGoodQuery(final Connection conn) throws Exception {
when(meta.getParameterCount()).thenReturn(2);
- runner.query(conn, "select * from blah where ? = ?", handler, "unit", "test");
+ String sql = "select * from blah where ? = ?";
+ runner.query(conn, sql, handler, "unit", "test");
- verify(stmt, times(1)).executeQuery();
+ verify(prepStmt, times(1)).executeQuery();
verify(results, times(1)).close();
- verify(stmt, times(1)).close(); // make sure we closed the statement
+ verify(prepStmt, times(1)).close(); // make sure we closed the statement
verify(conn, times(0)).close(); // make sure we do not close the connection, since QueryRunner.query(Connection, String, ResultSetHandler<T>, Object...) does not close connections
// call the other variation of query
when(meta.getParameterCount()).thenReturn(0);
- runner.query(conn, "select * from blah", handler);
+ sql = "select * from blah";
+ runner.query(conn, sql, handler);
- verify(stmt, times(2)).executeQuery();
+ verify(stmt, times(1)).executeQuery(sql);
verify(results, times(2)).close();
- verify(stmt, times(2)).close(); // make sure we closed the statement
+ verify(stmt, times(1)).close(); // make sure we closed the statement
verify(conn, times(0)).close(); // make sure we do not close the connection, see above
}
private void callGoodQuery() throws Exception {
when(meta.getParameterCount()).thenReturn(2);
- runner.query("select * from blah where ? = ?", handler, "unit", "test");
+ String sql = "select * from blah where ? = ?";
+ runner.query(sql, handler, "unit", "test");
- verify(stmt, times(1)).executeQuery();
+ verify(prepStmt, times(1)).executeQuery();
verify(results, times(1)).close();
- verify(stmt, times(1)).close(); // make sure we closed the statement
+ verify(prepStmt, times(1)).close(); // make sure we closed the statement
verify(conn, times(1)).close(); // make sure we closed the connection
// call the other variation of query
when(meta.getParameterCount()).thenReturn(0);
- runner.query("select * from blah", handler);
+ sql = "select * from blah";
+ runner.query(sql, handler);
- verify(stmt, times(2)).executeQuery();
+ verify(stmt, times(1)).executeQuery(sql);
verify(results, times(2)).close();
- verify(stmt, times(2)).close(); // make sure we closed the statement
+ verify(stmt, times(1)).close(); // make sure we closed the statement
verify(conn, times(2)).close(); // make sure we closed the connection
}
@@ -282,12 +295,13 @@ public class QueryRunnerTest {
try {
when(meta.getParameterCount()).thenReturn(2);
- runner.query("select * from blah where ? = ?", handler, params);
+ String sql = "select * from blah where ? = ?";
+ runner.query(sql, handler, params);
- verify(stmt, never()).close(); // make sure the statement is still open
- verify(stmt, times(1)).executeQuery();
+ verify(prepStmt, never()).close(); // make sure the statement is still open
+ verify(prepStmt, times(1)).executeQuery();
+ verify(prepStmt, times(1)).close(); // make sure we closed the statement
verify(results, times(1)).close();
- verify(stmt, times(1)).close(); // make sure we closed the statement
verify(conn, times(1)).close(); // make sure we closed the connection
} catch(final SQLException e) {
caught = true;
@@ -300,7 +314,7 @@ public class QueryRunnerTest {
@Test
public void testNoParamsQuery() throws Exception {
- callQueryWithException();
+ callGoodQuery();
}
@Test
@@ -337,7 +351,7 @@ public class QueryRunnerTest {
@Test
public void testExecuteQueryException() throws Exception {
- doThrow(new SQLException()).when(stmt).executeQuery();
+ doThrow(new SQLException()).when(prepStmt).executeQuery();
callQueryWithException(handler, "unit", "test");
}
@@ -350,49 +364,53 @@ public class QueryRunnerTest {
when(meta.getParameterCount()).thenReturn(2);
runner.update(conn, "update blah set ? = ?", "unit", "test");
- verify(stmt, times(1)).executeUpdate();
- verify(stmt, times(1)).close(); // make sure we closed the statement
+ verify(prepStmt, times(1)).executeUpdate();
+ verify(prepStmt, times(1)).close(); // make sure we closed the statement
verify(conn, times(0)).close(); // make sure we do not close the connection, since QueryRunner.update(Connection, String, Object...) does not close connections
// call the other variation
when(meta.getParameterCount()).thenReturn(0);
- runner.update(conn, "update blah set unit = test");
+ String sql = "update blah set unit = test";
+ runner.update(conn, sql);
- verify(stmt, times(2)).executeUpdate();
- verify(stmt, times(2)).close(); // make sure we closed the statement
+ verify(stmt, times(1)).executeUpdate(sql);
+ verify(stmt, times(1)).close(); // make sure we closed the statement
verify(conn, times(0)).close(); // make sure we do not close the connection, see above
// call the other variation
when(meta.getParameterCount()).thenReturn(1);
runner.update(conn, "update blah set unit = ?", "test");
- verify(stmt, times(3)).executeUpdate();
- verify(stmt, times(3)).close(); // make sure we closed the statement
+ verify(prepStmt, times(2)).executeUpdate();
+ verify(prepStmt, times(2)).close(); // make sure we closed the statement
verify(conn, times(0)).close(); // make sure we do not close the connection, see above
}
private void callGoodUpdate() throws Exception {
when(meta.getParameterCount()).thenReturn(2);
- runner.update("update blah set ? = ?", "unit", "test");
+ String sql = "update blah set ? = ?";
+ runner.update(sql, "unit", "test");
- verify(stmt, times(1)).executeUpdate();
- verify(stmt, times(1)).close(); // make sure we closed the statement
+ verify(prepStmt, times(1)).executeUpdate();
+ verify(prepStmt, times(1)).close(); // make sure we closed the statement
verify(conn, times(1)).close(); // make sure we closed the connection
// call the other variation
when(meta.getParameterCount()).thenReturn(0);
- runner.update("update blah set unit = test");
+ sql = "update blah set unit = test";
+ runner.update(sql);
- verify(stmt, times(2)).executeUpdate();
- verify(stmt, times(2)).close(); // make sure we closed the statement
+ verify(stmt, times(1)).executeUpdate(sql);
+ verify(stmt, times(1)).close(); // make sure we closed the statement
verify(conn, times(2)).close(); // make sure we closed the connection
// call the other variation
when(meta.getParameterCount()).thenReturn(1);
- runner.update("update blah set unit = ?", "test");
+ sql = "update blah set unit = ?";
+ runner.update(sql, "test");
- verify(stmt, times(3)).executeUpdate();
- verify(stmt, times(3)).close(); // make sure we closed the statement
+ verify(prepStmt, times(2)).executeUpdate();
+ verify(prepStmt, times(2)).close(); // make sure we closed the statement
verify(conn, times(3)).close(); // make sure we closed the connection
}
@@ -418,15 +436,15 @@ public class QueryRunnerTest {
results = mock(ResultSet.class);
when(meta.getParameterCount()).thenReturn(2);
- when(conn.prepareStatement(any(String.class), eq(Statement.RETURN_GENERATED_KEYS))).thenReturn(stmt);
- when(stmt.getGeneratedKeys()).thenReturn(results);
+ when(conn.prepareStatement(any(String.class), eq(Statement.RETURN_GENERATED_KEYS))).thenReturn(prepStmt);
+ when(prepStmt.getGeneratedKeys()).thenReturn(results);
when(results.next()).thenReturn(true).thenReturn(false);
when(results.getObject(1)).thenReturn(1L);
final Long generatedKey = runner.insert("INSERT INTO blah(col1, col2) VALUES(?,?)", new ScalarHandler<Long>(), "unit", "test");
- verify(stmt, times(1)).executeUpdate();
- verify(stmt, times(1)).close(); // make sure we closed the statement
+ verify(prepStmt, times(1)).executeUpdate();
+ verify(prepStmt, times(1)).close(); // make sure we closed the statement
verify(conn, times(1)).close(); // make sure we closed the connection
Assert.assertEquals(1L, generatedKey.longValue());
@@ -438,8 +456,8 @@ public class QueryRunnerTest {
resultsMeta = mock(ResultSetMetaData.class);
when(meta.getParameterCount()).thenReturn(2);
- when(conn.prepareStatement(any(String.class), eq(Statement.RETURN_GENERATED_KEYS))).thenReturn(stmt);
- when(stmt.getGeneratedKeys()).thenReturn(results);
+ when(conn.prepareStatement(any(String.class), eq(Statement.RETURN_GENERATED_KEYS))).thenReturn(prepStmt);
+ when(prepStmt.getGeneratedKeys()).thenReturn(results);
when(results.next()).thenReturn(true).thenReturn(true).thenReturn(false);
when(results.getMetaData()).thenReturn(resultsMeta);
when(resultsMeta.getColumnCount()).thenReturn(1);
@@ -466,9 +484,9 @@ public class QueryRunnerTest {
final List<Object> generatedKeys = runner.insertBatch("INSERT INTO blah(col1, col2) VALUES(?,?)", handler, params);
- verify(stmt, times(2)).addBatch();
- verify(stmt, times(1)).executeBatch();
- verify(stmt, times(1)).close(); // make sure we closed the statement
+ verify(prepStmt, times(2)).addBatch();
+ verify(prepStmt, times(1)).executeBatch();
+ verify(prepStmt, times(1)).close(); // make sure we closed the statement
verify(conn, times(1)).close(); // make sure we closed the connection
Assert.assertEquals(2, generatedKeys.size());
@@ -480,10 +498,11 @@ public class QueryRunnerTest {
try {
when(meta.getParameterCount()).thenReturn(2);
- runner.update("select * from blah where ? = ?", params);
+ String sql = "select * from blah where ? = ?";
+ runner.update(sql, params);
- verify(stmt, times(1)).executeUpdate();
- verify(stmt, times(1)).close(); // make sure we closed the statement
+ verify(prepStmt, times(1)).executeUpdate();
+ verify(prepStmt, times(1)).close(); // make sure we closed the statement
verify(conn, times(1)).close(); // make sure we closed the connection
} catch(final SQLException e) {
caught = true;
@@ -496,7 +515,7 @@ public class QueryRunnerTest {
@Test
public void testNoParamsUpdate() throws Exception {
- callUpdateWithException();
+ callGoodUpdate();
}
@Test
@@ -526,7 +545,7 @@ public class QueryRunnerTest {
@Test
public void testExecuteUpdateException() throws Exception {
- doThrow(new SQLException()).when(stmt).executeUpdate();
+ doThrow(new SQLException()).when(prepStmt).executeUpdate();
callUpdateWithException("unit", "test");
}
@@ -537,11 +556,11 @@ public class QueryRunnerTest {
final QueryRunner queryRunner = new QueryRunner(stmtConfig);
queryRunner.prepareStatement(conn, "select 1");
- verify(stmt).setFetchDirection(eq(1));
- verify(stmt).setFetchSize(eq(2));
- verify(stmt).setMaxFieldSize(eq(3));
- verify(stmt).setMaxRows(eq(4));
- verify(stmt).setQueryTimeout(eq(5));
+ verify(prepStmt).setFetchDirection(eq(1));
+ verify(prepStmt).setFetchSize(eq(2));
+ verify(prepStmt).setMaxFieldSize(eq(3));
+ verify(prepStmt).setMaxRows(eq(4));
+ verify(prepStmt).setQueryTimeout(eq(5));
}
//
@@ -720,7 +739,7 @@ public class QueryRunnerTest {
@Test
public void testNoParamsExecute() throws Exception {
- callExecuteWithException();
+ callGoodExecute();
}
@Test
@@ -757,7 +776,7 @@ public class QueryRunnerTest {
@Test
public void testExecuteException() throws Exception {
- doThrow(new SQLException()).when(stmt).execute();
+ doThrow(new SQLException()).when(prepStmt).execute();
callExecuteWithException(handler, "unit", "test");
}
@@ -943,7 +962,7 @@ public class QueryRunnerTest {
try {
when(call.execute()).thenReturn(true);
when(meta.getParameterCount()).thenReturn(2);
- runner.query("{call my_proc(?, ?)}", handler, params);
+ runner.execute("{call my_proc(?, ?)}", handler, params);
} catch(final SQLException e) {
caught = true;
@@ -993,7 +1012,7 @@ public class QueryRunnerTest {
@Test
public void testExecuteWithResultSetException() throws Exception {
- doThrow(new SQLException()).when(stmt).execute();
+ doThrow(new SQLException()).when(prepStmt).execute();
callExecuteWithResultSetWithException(handler, "unit", "test");
}
@@ -1018,14 +1037,14 @@ public class QueryRunnerTest {
public void testFillStatementWithBean() throws Exception {
final MyBean bean = new MyBean();
when(meta.getParameterCount()).thenReturn(3);
- runner.fillStatementWithBean(stmt, bean, "a", "b", "c");
+ runner.fillStatementWithBean(prepStmt, bean, "a", "b", "c");
}
@Test(expected=NullPointerException.class)
public void testFillStatementWithBeanNullNames() throws Exception {
final MyBean bean = new MyBean();
when(meta.getParameterCount()).thenReturn(3);
- runner.fillStatementWithBean(stmt, bean, "a", "b", null);
+ runner.fillStatementWithBean(prepStmt, bean, "a", "b", null);
}
@Test(expected=SQLException.class)