1 /*
2 * Licensed to the Apache Software Foundation (ASF) under one or more
3 * contributor license agreements. See the NOTICE file distributed with
4 * this work for additional information regarding copyright ownership.
5 * The ASF licenses this file to You under the Apache License, Version 2.0
6 * (the "License"); you may not use this file except in compliance with
7 * the License. You may obtain a copy of the License at
8 *
9 * http://www.apache.org/licenses/LICENSE-2.0
10 *
11 * Unless required by applicable law or agreed to in writing, software
12 * distributed under the License is distributed on an "AS IS" BASIS,
13 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
14 * See the License for the specific language governing permissions and
15 * limitations under the License.
16 */
17 package org.apache.commons.dbutils;
18
19 import java.sql.Connection;
20 import java.sql.PreparedStatement;
21 import java.sql.ResultSet;
22 import java.sql.SQLException;
23
24 import javax.sql.DataSource;
25
26 /**
27 * Executes SQL queries with pluggable strategies for handling
28 * <code>ResultSet</code>s. This class is thread safe.
29 *
30 * @see ResultSetHandler
31 */
32 public class QueryRunner extends AbstractQueryRunner {
33
34 /**
35 * Constructor for QueryRunner.
36 */
37 public QueryRunner() {
38 super();
39 }
40
41 /**
42 * Constructor for QueryRunner that controls the use of <code>ParameterMetaData</code>.
43 *
44 * @param pmdKnownBroken Some drivers don't support {@link java.sql.ParameterMetaData#getParameterType(int) };
45 * if <code>pmdKnownBroken</code> is set to true, we won't even try it; if false, we'll try it,
46 * and if it breaks, we'll remember not to use it again.
47 */
48 public QueryRunner(boolean pmdKnownBroken) {
49 super(pmdKnownBroken);
50 }
51
52 /**
53 * Constructor for QueryRunner that takes a <code>DataSource</code> to use.
54 *
55 * Methods that do not take a <code>Connection</code> parameter will retrieve connections from this
56 * <code>DataSource</code>.
57 *
58 * @param ds The <code>DataSource</code> to retrieve connections from.
59 */
60 public QueryRunner(DataSource ds) {
61 super(ds);
62 }
63
64 /**
65 * Constructor for QueryRunner that takes a <code>DataSource</code> and controls the use of <code>ParameterMetaData</code>.
66 * Methods that do not take a <code>Connection</code> parameter will retrieve connections from this
67 * <code>DataSource</code>.
68 *
69 * @param ds The <code>DataSource</code> to retrieve connections from.
70 * @param pmdKnownBroken Some drivers don't support {@link java.sql.ParameterMetaData#getParameterType(int) };
71 * if <code>pmdKnownBroken</code> is set to true, we won't even try it; if false, we'll try it,
72 * and if it breaks, we'll remember not to use it again.
73 */
74 public QueryRunner(DataSource ds, boolean pmdKnownBroken) {
75 super(ds, pmdKnownBroken);
76 }
77
78 /**
79 * Execute a batch of SQL INSERT, UPDATE, or DELETE queries.
80 *
81 * @param conn The Connection to use to run the query. The caller is
82 * responsible for closing this Connection.
83 * @param sql The SQL to execute.
84 * @param params An array of query replacement parameters. Each row in
85 * this array is one set of batch replacement values.
86 * @return The number of rows updated per statement.
87 * @throws SQLException if a database access error occurs
88 * @since DbUtils 1.1
89 */
90 public int[] batch(Connection conn, String sql, Object[][] params) throws SQLException {
91 return this.batch(conn, false, sql, params);
92 }
93
94 /**
95 * Execute a batch of SQL INSERT, UPDATE, or DELETE queries. The
96 * <code>Connection</code> is retrieved from the <code>DataSource</code>
97 * set in the constructor. This <code>Connection</code> must be in
98 * auto-commit mode or the update will not be saved.
99 *
100 * @param sql The SQL to execute.
101 * @param params An array of query replacement parameters. Each row in
102 * this array is one set of batch replacement values.
103 * @return The number of rows updated per statement.
104 * @throws SQLException if a database access error occurs
105 * @since DbUtils 1.1
106 */
107 public int[] batch(String sql, Object[][] params) throws SQLException {
108 Connection conn = this.prepareConnection();
109
110 return this.batch(conn, true, sql, params);
111 }
112
113 /**
114 * Calls update after checking the parameters to ensure nothing is null.
115 * @param conn The connection to use for the batch call.
116 * @param closeConn True if the connection should be closed, false otherwise.
117 * @param sql The SQL statement to execute.
118 * @param params An array of query replacement parameters. Each row in
119 * this array is one set of batch replacement values.
120 * @return The number of rows updated in the batch.
121 * @throws SQLException If there are database or parameter errors.
122 */
123 private int[] batch(Connection conn, boolean closeConn, String sql, Object[][] params) throws SQLException {
124 if (conn == null) {
125 throw new SQLException("Null connection");
126 }
127
128 if (sql == null) {
129 if (closeConn) {
130 close(conn);
131 }
132 throw new SQLException("Null SQL statement");
133 }
134
135 if (params == null) {
136 if (closeConn) {
137 close(conn);
138 }
139 throw new SQLException("Null parameters. If parameters aren't need, pass an empty array.");
140 }
141
142 PreparedStatement stmt = null;
143 int[] rows = null;
144 try {
145 stmt = this.prepareStatement(conn, sql);
146
147 for (int i = 0; i < params.length; i++) {
148 this.fillStatement(stmt, params[i]);
149 stmt.addBatch();
150 }
151 rows = stmt.executeBatch();
152
153 } catch (SQLException e) {
154 this.rethrow(e, sql, (Object[])params);
155 } finally {
156 close(stmt);
157 if (closeConn) {
158 close(conn);
159 }
160 }
161
162 return rows;
163 }
164
165 /**
166 * Execute an SQL SELECT query with a single replacement parameter. The
167 * caller is responsible for closing the connection.
168 * @param <T> The type of object that the handler returns
169 * @param conn The connection to execute the query in.
170 * @param sql The query to execute.
171 * @param param The replacement parameter.
172 * @param rsh The handler that converts the results into an object.
173 * @return The object returned by the handler.
174 * @throws SQLException if a database access error occurs
175 * @deprecated Use {@link #query(Connection, String, ResultSetHandler, Object...)}
176 */
177 @Deprecated
178 public <T> T query(Connection conn, String sql, Object param, ResultSetHandler<T> rsh) throws SQLException {
179 return this.<T>query(conn, false, sql, rsh, new Object[]{param});
180 }
181
182 /**
183 * Execute an SQL SELECT query with replacement parameters. The
184 * caller is responsible for closing the connection.
185 * @param <T> The type of object that the handler returns
186 * @param conn The connection to execute the query in.
187 * @param sql The query to execute.
188 * @param params The replacement parameters.
189 * @param rsh The handler that converts the results into an object.
190 * @return The object returned by the handler.
191 * @throws SQLException if a database access error occurs
192 * @deprecated Use {@link #query(Connection,String,ResultSetHandler,Object...)} instead
193 */
194 @Deprecated
195 public <T> T query(Connection conn, String sql, Object[] params, ResultSetHandler<T> rsh) throws SQLException {
196 return this.<T>query(conn, false, sql, rsh, params);
197 }
198
199 /**
200 * Execute an SQL SELECT query with replacement parameters. The
201 * caller is responsible for closing the connection.
202 * @param <T> The type of object that the handler returns
203 * @param conn The connection to execute the query in.
204 * @param sql The query to execute.
205 * @param rsh The handler that converts the results into an object.
206 * @param params The replacement parameters.
207 * @return The object returned by the handler.
208 * @throws SQLException if a database access error occurs
209 */
210 public <T> T query(Connection conn, String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException {
211 return this.<T>query(conn, false, sql, rsh, params);
212 }
213
214 /**
215 * Execute an SQL SELECT query without any replacement parameters. The
216 * caller is responsible for closing the connection.
217 * @param <T> The type of object that the handler returns
218 * @param conn The connection to execute the query in.
219 * @param sql The query to execute.
220 * @param rsh The handler that converts the results into an object.
221 * @return The object returned by the handler.
222 * @throws SQLException if a database access error occurs
223 */
224 public <T> T query(Connection conn, String sql, ResultSetHandler<T> rsh) throws SQLException {
225 return this.<T>query(conn, false, sql, rsh, (Object[]) null);
226 }
227
228 /**
229 * Executes the given SELECT SQL with a single replacement parameter.
230 * The <code>Connection</code> is retrieved from the
231 * <code>DataSource</code> set in the constructor.
232 * @param <T> The type of object that the handler returns
233 * @param sql The SQL statement to execute.
234 * @param param The replacement parameter.
235 * @param rsh The handler used to create the result object from
236 * the <code>ResultSet</code>.
237 *
238 * @return An object generated by the handler.
239 * @throws SQLException if a database access error occurs
240 * @deprecated Use {@link #query(String, ResultSetHandler, Object...)}
241 */
242 @Deprecated
243 public <T> T query(String sql, Object param, ResultSetHandler<T> rsh) throws SQLException {
244 Connection conn = this.prepareConnection();
245
246 return this.<T>query(conn, true, sql, rsh, new Object[]{param});
247 }
248
249 /**
250 * Executes the given SELECT SQL query and returns a result object.
251 * The <code>Connection</code> is retrieved from the
252 * <code>DataSource</code> set in the constructor.
253 * @param <T> The type of object that the handler returns
254 * @param sql The SQL statement to execute.
255 * @param params Initialize the PreparedStatement's IN parameters with
256 * this array.
257 *
258 * @param rsh The handler used to create the result object from
259 * the <code>ResultSet</code>.
260 *
261 * @return An object generated by the handler.
262 * @throws SQLException if a database access error occurs
263 * @deprecated Use {@link #query(String, ResultSetHandler, Object...)}
264 */
265 @Deprecated
266 public <T> T query(String sql, Object[] params, ResultSetHandler<T> rsh) throws SQLException {
267 Connection conn = this.prepareConnection();
268
269 return this.<T>query(conn, true, sql, rsh, params);
270 }
271
272 /**
273 * Executes the given SELECT SQL query and returns a result object.
274 * The <code>Connection</code> is retrieved from the
275 * <code>DataSource</code> set in the constructor.
276 * @param <T> The type of object that the handler returns
277 * @param sql The SQL statement to execute.
278 * @param rsh The handler used to create the result object from
279 * the <code>ResultSet</code>.
280 * @param params Initialize the PreparedStatement's IN parameters with
281 * this array.
282 * @return An object generated by the handler.
283 * @throws SQLException if a database access error occurs
284 */
285 public <T> T query(String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException {
286 Connection conn = this.prepareConnection();
287
288 return this.<T>query(conn, true, sql, rsh, params);
289 }
290
291 /**
292 * Executes the given SELECT SQL without any replacement parameters.
293 * The <code>Connection</code> is retrieved from the
294 * <code>DataSource</code> set in the constructor.
295 * @param <T> The type of object that the handler returns
296 * @param sql The SQL statement to execute.
297 * @param rsh The handler used to create the result object from
298 * the <code>ResultSet</code>.
299 *
300 * @return An object generated by the handler.
301 * @throws SQLException if a database access error occurs
302 */
303 public <T> T query(String sql, ResultSetHandler<T> rsh) throws SQLException {
304 Connection conn = this.prepareConnection();
305
306 return this.<T>query(conn, true, sql, rsh, (Object[]) null);
307 }
308
309 /**
310 * Calls query after checking the parameters to ensure nothing is null.
311 * @param conn The connection to use for the query call.
312 * @param closeConn True if the connection should be closed, false otherwise.
313 * @param sql The SQL statement to execute.
314 * @param params An array of query replacement parameters. Each row in
315 * this array is one set of batch replacement values.
316 * @return The results of the query.
317 * @throws SQLException If there are database or parameter errors.
318 */
319 private <T> T query(Connection conn, boolean closeConn, String sql, ResultSetHandler<T> rsh, Object... params)
320 throws SQLException {
321 if (conn == null) {
322 throw new SQLException("Null connection");
323 }
324
325 if (sql == null) {
326 if (closeConn) {
327 close(conn);
328 }
329 throw new SQLException("Null SQL statement");
330 }
331
332 if (rsh == null) {
333 if (closeConn) {
334 close(conn);
335 }
336 throw new SQLException("Null ResultSetHandler");
337 }
338
339 PreparedStatement stmt = null;
340 ResultSet rs = null;
341 T result = null;
342
343 try {
344 stmt = this.prepareStatement(conn, sql);
345 this.fillStatement(stmt, params);
346 rs = this.wrap(stmt.executeQuery());
347 result = rsh.handle(rs);
348
349 } catch (SQLException e) {
350 this.rethrow(e, sql, params);
351
352 } finally {
353 try {
354 close(rs);
355 } finally {
356 close(stmt);
357 if (closeConn) {
358 close(conn);
359 }
360 }
361 }
362
363 return result;
364 }
365
366 /**
367 * Execute an SQL INSERT, UPDATE, or DELETE query without replacement
368 * parameters.
369 *
370 * @param conn The connection to use to run the query.
371 * @param sql The SQL to execute.
372 * @return The number of rows updated.
373 * @throws SQLException if a database access error occurs
374 */
375 public int update(Connection conn, String sql) throws SQLException {
376 return this.update(conn, false, sql, (Object[]) null);
377 }
378
379 /**
380 * Execute an SQL INSERT, UPDATE, or DELETE query with a single replacement
381 * parameter.
382 *
383 * @param conn The connection to use to run the query.
384 * @param sql The SQL to execute.
385 * @param param The replacement parameter.
386 * @return The number of rows updated.
387 * @throws SQLException if a database access error occurs
388 */
389 public int update(Connection conn, String sql, Object param) throws SQLException {
390 return this.update(conn, false, sql, new Object[]{param});
391 }
392
393 /**
394 * Execute an SQL INSERT, UPDATE, or DELETE query.
395 *
396 * @param conn The connection to use to run the query.
397 * @param sql The SQL to execute.
398 * @param params The query replacement parameters.
399 * @return The number of rows updated.
400 * @throws SQLException if a database access error occurs
401 */
402 public int update(Connection conn, String sql, Object... params) throws SQLException {
403 return update(conn, false, sql, params);
404 }
405
406 /**
407 * Executes the given INSERT, UPDATE, or DELETE SQL statement without
408 * any replacement parameters. The <code>Connection</code> is retrieved
409 * from the <code>DataSource</code> set in the constructor. This
410 * <code>Connection</code> must be in auto-commit mode or the update will
411 * not be saved.
412 *
413 * @param sql The SQL statement to execute.
414 * @throws SQLException if a database access error occurs
415 * @return The number of rows updated.
416 */
417 public int update(String sql) throws SQLException {
418 Connection conn = this.prepareConnection();
419
420 return this.update(conn, true, sql, (Object[]) null);
421 }
422
423 /**
424 * Executes the given INSERT, UPDATE, or DELETE SQL statement with
425 * a single replacement parameter. The <code>Connection</code> is
426 * retrieved from the <code>DataSource</code> set in the constructor.
427 * This <code>Connection</code> must be in auto-commit mode or the
428 * update will not be saved.
429 *
430 * @param sql The SQL statement to execute.
431 * @param param The replacement parameter.
432 * @throws SQLException if a database access error occurs
433 * @return The number of rows updated.
434 */
435 public int update(String sql, Object param) throws SQLException {
436 Connection conn = this.prepareConnection();
437
438 return this.update(conn, true, sql, new Object[]{param});
439 }
440
441 /**
442 * Executes the given INSERT, UPDATE, or DELETE SQL statement. The
443 * <code>Connection</code> is retrieved from the <code>DataSource</code>
444 * set in the constructor. This <code>Connection</code> must be in
445 * auto-commit mode or the update will not be saved.
446 *
447 * @param sql The SQL statement to execute.
448 * @param params Initializes the PreparedStatement's IN (i.e. '?')
449 * parameters.
450 * @throws SQLException if a database access error occurs
451 * @return The number of rows updated.
452 */
453 public int update(String sql, Object... params) throws SQLException {
454 Connection conn = this.prepareConnection();
455
456 return this.update(conn, true, sql, params);
457 }
458
459 /**
460 * Calls update after checking the parameters to ensure nothing is null.
461 * @param conn The connection to use for the update call.
462 * @param closeConn True if the connection should be closed, false otherwise.
463 * @param sql The SQL statement to execute.
464 * @param params An array of update replacement parameters. Each row in
465 * this array is one set of update replacement values.
466 * @return The number of rows updated.
467 * @throws SQLException If there are database or parameter errors.
468 */
469 private int update(Connection conn, boolean closeConn, String sql, Object... params) throws SQLException {
470 if (conn == null) {
471 throw new SQLException("Null connection");
472 }
473
474 if (sql == null) {
475 if (closeConn) {
476 close(conn);
477 }
478 throw new SQLException("Null SQL statement");
479 }
480
481 PreparedStatement stmt = null;
482 int rows = 0;
483
484 try {
485 stmt = this.prepareStatement(conn, sql);
486 this.fillStatement(stmt, params);
487 rows = stmt.executeUpdate();
488
489 } catch (SQLException e) {
490 this.rethrow(e, sql, params);
491
492 } finally {
493 close(stmt);
494 if (closeConn) {
495 close(conn);
496 }
497 }
498
499 return rows;
500 }
501
502 }