001 /*
002 * Licensed to the Apache Software Foundation (ASF) under one or more
003 * contributor license agreements. See the NOTICE file distributed with
004 * this work for additional information regarding copyright ownership.
005 * The ASF licenses this file to You under the Apache License, Version 2.0
006 * (the "License"); you may not use this file except in compliance with
007 * the License. You may obtain a copy of the License at
008 *
009 * http://www.apache.org/licenses/LICENSE-2.0
010 *
011 * Unless required by applicable law or agreed to in writing, software
012 * distributed under the License is distributed on an "AS IS" BASIS,
013 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
014 * See the License for the specific language governing permissions and
015 * limitations under the License.
016 */
017 package org.apache.commons.dbutils;
018
019 import java.sql.Connection;
020 import java.sql.PreparedStatement;
021 import java.sql.ResultSet;
022 import java.sql.SQLException;
023
024 import javax.sql.DataSource;
025
026 /**
027 * Executes SQL queries with pluggable strategies for handling
028 * <code>ResultSet</code>s. This class is thread safe.
029 *
030 * @see ResultSetHandler
031 */
032 public class QueryRunner extends AbstractQueryRunner {
033
034 /**
035 * Constructor for QueryRunner.
036 */
037 public QueryRunner() {
038 super();
039 }
040
041 /**
042 * Constructor for QueryRunner, allows workaround for Oracle drivers
043 * @param pmdKnownBroken Oracle drivers don't support
044 * {@link java.sql.ParameterMetaData#getParameterType(int) };
045 * if <code>pmdKnownBroken</code> is set to true, we won't even try it; if false, we'll try it,
046 * and if it breaks, we'll remember not to use it again.
047 */
048 public QueryRunner(boolean pmdKnownBroken) {
049 super(pmdKnownBroken);
050 }
051
052 /**
053 * Constructor for QueryRunner which takes a <code>DataSource</code>. Methods that do not take a
054 * <code>Connection</code> parameter will retrieve connections from this
055 * <code>DataSource</code>.
056 *
057 * @param ds The <code>DataSource</code> to retrieve connections from.
058 */
059 public QueryRunner(DataSource ds) {
060 super(ds);
061 }
062
063 /**
064 * Constructor for QueryRunner, allows workaround for Oracle drivers. Methods that do not take a
065 * <code>Connection</code> parameter will retrieve connections from this
066 * <code>DataSource</code>.
067 *
068 * @param ds The <code>DataSource</code> to retrieve connections from.
069 * @param pmdKnownBroken Oracle drivers don't support {@link java.sql.ParameterMetaData#getParameterType(int) };
070 * if <code>pmdKnownBroken</code> is set to true, we won't even try it; if false, we'll try it,
071 * and if it breaks, we'll remember not to use it again.
072 */
073 public QueryRunner(DataSource ds, boolean pmdKnownBroken) {
074 super(ds, pmdKnownBroken);
075 }
076
077 /**
078 * Execute a batch of SQL INSERT, UPDATE, or DELETE queries.
079 *
080 * @param conn The Connection to use to run the query. The caller is
081 * responsible for closing this Connection.
082 * @param sql The SQL to execute.
083 * @param params An array of query replacement parameters. Each row in
084 * this array is one set of batch replacement values.
085 * @return The number of rows updated per statement.
086 * @throws SQLException if a database access error occurs
087 * @since DbUtils 1.1
088 */
089 public int[] batch(Connection conn, String sql, Object[][] params) throws SQLException {
090 return this.batch(conn, false, sql, params);
091 }
092
093 /**
094 * Execute a batch of SQL INSERT, UPDATE, or DELETE queries. The
095 * <code>Connection</code> is retrieved from the <code>DataSource</code>
096 * set in the constructor. This <code>Connection</code> must be in
097 * auto-commit mode or the update will not be saved.
098 *
099 * @param sql The SQL to execute.
100 * @param params An array of query replacement parameters. Each row in
101 * this array is one set of batch replacement values.
102 * @return The number of rows updated per statement.
103 * @throws SQLException if a database access error occurs
104 * @since DbUtils 1.1
105 */
106 public int[] batch(String sql, Object[][] params) throws SQLException {
107 Connection conn = this.prepareConnection();
108
109 return this.batch(conn, true, sql, params);
110 }
111
112 /**
113 * Calls update after checking the parameters to ensure nothing is null.
114 * @param conn The connection to use for the batch call.
115 * @param closeConn True if the connection should be closed, false otherwise.
116 * @param sql The SQL statement to execute.
117 * @param params An array of query replacement parameters. Each row in
118 * this array is one set of batch replacement values.
119 * @return The number of rows updated in the batch.
120 * @throws SQLException If there are database or parameter errors.
121 */
122 private int[] batch(Connection conn, boolean closeConn, String sql, Object[][] params) throws SQLException {
123 if (conn == null) {
124 throw new SQLException("Null connection");
125 }
126
127 if (sql == null) {
128 if (closeConn) {
129 close(conn);
130 }
131 throw new SQLException("Null SQL statement");
132 }
133
134 if (params == null) {
135 if (closeConn) {
136 close(conn);
137 }
138 throw new SQLException("Null parameters. If parameters aren't need, pass an empty array.");
139 }
140
141 PreparedStatement stmt = null;
142 int[] rows = null;
143 try {
144 stmt = this.prepareStatement(conn, sql);
145
146 for (int i = 0; i < params.length; i++) {
147 this.fillStatement(stmt, params[i]);
148 stmt.addBatch();
149 }
150 rows = stmt.executeBatch();
151
152 } catch (SQLException e) {
153 this.rethrow(e, sql, (Object[])params);
154 } finally {
155 close(stmt);
156 if (closeConn) {
157 close(conn);
158 }
159 }
160
161 return rows;
162 }
163
164 /**
165 * Execute an SQL SELECT query with a single replacement parameter. The
166 * caller is responsible for closing the connection.
167 * @param <T> The type of object that the handler returns
168 * @param conn The connection to execute the query in.
169 * @param sql The query to execute.
170 * @param param The replacement parameter.
171 * @param rsh The handler that converts the results into an object.
172 * @return The object returned by the handler.
173 * @throws SQLException if a database access error occurs
174 * @deprecated Use {@link #query(Connection, String, ResultSetHandler, Object...)}
175 */
176 @Deprecated
177 public <T> T query(Connection conn, String sql, Object param, ResultSetHandler<T> rsh) throws SQLException {
178 return this.query(conn, false, sql, rsh, new Object[]{param});
179 }
180
181 /**
182 * Execute an SQL SELECT query with replacement parameters. The
183 * caller is responsible for closing the connection.
184 * @param <T> The type of object that the handler returns
185 * @param conn The connection to execute the query in.
186 * @param sql The query to execute.
187 * @param params The replacement parameters.
188 * @param rsh The handler that converts the results into an object.
189 * @return The object returned by the handler.
190 * @throws SQLException if a database access error occurs
191 * @deprecated Use {@link #query(Connection,String,ResultSetHandler,Object...)} instead
192 */
193 @Deprecated
194 public <T> T query(Connection conn, String sql, Object[] params, ResultSetHandler<T> rsh) throws SQLException {
195 return this.query(conn, false, sql, rsh, params);
196 }
197
198 /**
199 * Execute an SQL SELECT query with replacement parameters. The
200 * caller is responsible for closing the connection.
201 * @param <T> The type of object that the handler returns
202 * @param conn The connection to execute the query in.
203 * @param sql The query to execute.
204 * @param rsh The handler that converts the results into an object.
205 * @param params The replacement parameters.
206 * @return The object returned by the handler.
207 * @throws SQLException if a database access error occurs
208 */
209 public <T> T query(Connection conn, String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException {
210 return this.query(conn, false, sql, rsh, params);
211 }
212
213 /**
214 * Execute an SQL SELECT query without any replacement parameters. The
215 * caller is responsible for closing the connection.
216 * @param <T> The type of object that the handler returns
217 * @param conn The connection to execute the query in.
218 * @param sql The query to execute.
219 * @param rsh The handler that converts the results into an object.
220 * @return The object returned by the handler.
221 * @throws SQLException if a database access error occurs
222 */
223 public <T> T query(Connection conn, String sql, ResultSetHandler<T> rsh) throws SQLException {
224 return this.query(conn, false, sql, rsh, (Object[]) null);
225 }
226
227 /**
228 * Executes the given SELECT SQL with a single replacement parameter.
229 * The <code>Connection</code> is retrieved from the
230 * <code>DataSource</code> set in the constructor.
231 * @param <T> The type of object that the handler returns
232 * @param sql The SQL statement to execute.
233 * @param param The replacement parameter.
234 * @param rsh The handler used to create the result object from
235 * the <code>ResultSet</code>.
236 *
237 * @return An object generated by the handler.
238 * @throws SQLException if a database access error occurs
239 * @deprecated Use {@link #query(String, ResultSetHandler, Object...)}
240 */
241 @Deprecated
242 public <T> T query(String sql, Object param, ResultSetHandler<T> rsh) throws SQLException {
243 Connection conn = this.prepareConnection();
244
245 return this.query(conn, true, sql, rsh, new Object[]{param});
246 }
247
248 /**
249 * Executes the given SELECT SQL query and returns a result object.
250 * The <code>Connection</code> is retrieved from the
251 * <code>DataSource</code> set in the constructor.
252 * @param <T> The type of object that the handler returns
253 * @param sql The SQL statement to execute.
254 * @param params Initialize the PreparedStatement's IN parameters with
255 * this array.
256 *
257 * @param rsh The handler used to create the result object from
258 * the <code>ResultSet</code>.
259 *
260 * @return An object generated by the handler.
261 * @throws SQLException if a database access error occurs
262 * @deprecated Use {@link #query(String, ResultSetHandler, Object...)}
263 */
264 @Deprecated
265 public <T> T query(String sql, Object[] params, ResultSetHandler<T> rsh) throws SQLException {
266 Connection conn = this.prepareConnection();
267
268 return query(conn, true, sql, rsh, params);
269 }
270
271 /**
272 * Executes the given SELECT SQL query and returns a result object.
273 * The <code>Connection</code> is retrieved from the
274 * <code>DataSource</code> set in the constructor.
275 * @param <T> The type of object that the handler returns
276 * @param sql The SQL statement to execute.
277 * @param rsh The handler used to create the result object from
278 * the <code>ResultSet</code>.
279 * @param params Initialize the PreparedStatement's IN parameters with
280 * this array.
281 * @return An object generated by the handler.
282 * @throws SQLException if a database access error occurs
283 */
284 public <T> T query(String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException {
285 Connection conn = this.prepareConnection();
286
287 return this.query(conn, true, sql, rsh, params);
288 }
289
290 /**
291 * Executes the given SELECT SQL without any replacement parameters.
292 * The <code>Connection</code> is retrieved from the
293 * <code>DataSource</code> set in the constructor.
294 * @param <T> The type of object that the handler returns
295 * @param sql The SQL statement to execute.
296 * @param rsh The handler used to create the result object from
297 * the <code>ResultSet</code>.
298 *
299 * @return An object generated by the handler.
300 * @throws SQLException if a database access error occurs
301 */
302 public <T> T query(String sql, ResultSetHandler<T> rsh) throws SQLException {
303 Connection conn = this.prepareConnection();
304
305 return this.query(conn, true, sql, rsh, (Object[]) null);
306 }
307
308 /**
309 * Calls query after checking the parameters to ensure nothing is null.
310 * @param conn The connection to use for the query call.
311 * @param closeConn True if the connection should be closed, false otherwise.
312 * @param sql The SQL statement to execute.
313 * @param params An array of query replacement parameters. Each row in
314 * this array is one set of batch replacement values.
315 * @return The results of the query.
316 * @throws SQLException If there are database or parameter errors.
317 */
318 private <T> T query(Connection conn, boolean closeConn, String sql, ResultSetHandler<T> rsh, Object... params)
319 throws SQLException {
320 if (conn == null) {
321 throw new SQLException("Null connection");
322 }
323
324 if (sql == null) {
325 if (closeConn) {
326 close(conn);
327 }
328 throw new SQLException("Null SQL statement");
329 }
330
331 if (rsh == null) {
332 if (closeConn) {
333 close(conn);
334 }
335 throw new SQLException("Null ResultSetHandler");
336 }
337
338 PreparedStatement stmt = null;
339 ResultSet rs = null;
340 T result = null;
341
342 try {
343 stmt = this.prepareStatement(conn, sql);
344 this.fillStatement(stmt, params);
345 rs = this.wrap(stmt.executeQuery());
346 result = rsh.handle(rs);
347
348 } catch (SQLException e) {
349 this.rethrow(e, sql, params);
350
351 } finally {
352 try {
353 close(rs);
354 } finally {
355 close(stmt);
356 if (closeConn) {
357 close(conn);
358 }
359 }
360 }
361
362 return result;
363 }
364
365 /**
366 * Execute an SQL INSERT, UPDATE, or DELETE query without replacement
367 * parameters.
368 *
369 * @param conn The connection to use to run the query.
370 * @param sql The SQL to execute.
371 * @return The number of rows updated.
372 * @throws SQLException if a database access error occurs
373 */
374 public int update(Connection conn, String sql) throws SQLException {
375 return this.update(conn, false, sql, (Object[]) null);
376 }
377
378 /**
379 * Execute an SQL INSERT, UPDATE, or DELETE query with a single replacement
380 * parameter.
381 *
382 * @param conn The connection to use to run the query.
383 * @param sql The SQL to execute.
384 * @param param The replacement parameter.
385 * @return The number of rows updated.
386 * @throws SQLException if a database access error occurs
387 */
388 public int update(Connection conn, String sql, Object param) throws SQLException {
389 return this.update(conn, false, sql, new Object[]{param});
390 }
391
392 /**
393 * Execute an SQL INSERT, UPDATE, or DELETE query.
394 *
395 * @param conn The connection to use to run the query.
396 * @param sql The SQL to execute.
397 * @param params The query replacement parameters.
398 * @return The number of rows updated.
399 * @throws SQLException if a database access error occurs
400 */
401 public int update(Connection conn, String sql, Object... params) throws SQLException {
402 return update(conn, false, sql, params);
403 }
404
405 /**
406 * Executes the given INSERT, UPDATE, or DELETE SQL statement without
407 * any replacement parameters. The <code>Connection</code> is retrieved
408 * from the <code>DataSource</code> set in the constructor. This
409 * <code>Connection</code> must be in auto-commit mode or the update will
410 * not be saved.
411 *
412 * @param sql The SQL statement to execute.
413 * @throws SQLException if a database access error occurs
414 * @return The number of rows updated.
415 */
416 public int update(String sql) throws SQLException {
417 Connection conn = this.prepareConnection();
418
419 return this.update(conn, true, sql, (Object[]) null);
420 }
421
422 /**
423 * Executes the given INSERT, UPDATE, or DELETE SQL statement with
424 * a single replacement parameter. The <code>Connection</code> is
425 * retrieved from the <code>DataSource</code> set in the constructor.
426 * This <code>Connection</code> must be in auto-commit mode or the
427 * update will not be saved.
428 *
429 * @param sql The SQL statement to execute.
430 * @param param The replacement parameter.
431 * @throws SQLException if a database access error occurs
432 * @return The number of rows updated.
433 */
434 public int update(String sql, Object param) throws SQLException {
435 Connection conn = this.prepareConnection();
436
437 return this.update(conn, true, sql, new Object[]{param});
438 }
439
440 /**
441 * Executes the given INSERT, UPDATE, or DELETE SQL statement. The
442 * <code>Connection</code> is retrieved from the <code>DataSource</code>
443 * set in the constructor. This <code>Connection</code> must be in
444 * auto-commit mode or the update will not be saved.
445 *
446 * @param sql The SQL statement to execute.
447 * @param params Initializes the PreparedStatement's IN (i.e. '?')
448 * parameters.
449 * @throws SQLException if a database access error occurs
450 * @return The number of rows updated.
451 */
452 public int update(String sql, Object... params) throws SQLException {
453 Connection conn = this.prepareConnection();
454
455 return this.update(conn, true, sql, params);
456 }
457
458 /**
459 * Calls update after checking the parameters to ensure nothing is null.
460 * @param conn The connection to use for the update call.
461 * @param closeConn True if the connection should be closed, false otherwise.
462 * @param sql The SQL statement to execute.
463 * @param params An array of update replacement parameters. Each row in
464 * this array is one set of update replacement values.
465 * @return The number of rows updated.
466 * @throws SQLException If there are database or parameter errors.
467 */
468 private int update(Connection conn, boolean closeConn, String sql, Object... params) throws SQLException {
469 if (conn == null) {
470 throw new SQLException("Null connection");
471 }
472
473 if (sql == null) {
474 if (closeConn) {
475 close(conn);
476 }
477 throw new SQLException("Null SQL statement");
478 }
479
480 PreparedStatement stmt = null;
481 int rows = 0;
482
483 try {
484 stmt = this.prepareStatement(conn, sql);
485 this.fillStatement(stmt, params);
486 rows = stmt.executeUpdate();
487
488 } catch (SQLException e) {
489 this.rethrow(e, sql, params);
490
491 } finally {
492 close(stmt);
493 if (closeConn) {
494 close(conn);
495 }
496 }
497
498 return rows;
499 }
500
501 }