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 &amp; 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)