1. /*
  2. * $Header: /home/cvs/jakarta-commons/dbutils/src/java/org/apache/commons/dbutils/QueryRunner.java,v 1.6 2003/11/12 01:00:55 dgraham Exp $
  3. * $Revision: 1.6 $
  4. * $Date: 2003/11/12 01:00:55 $
  5. *
  6. * ====================================================================
  7. *
  8. * The Apache Software License, Version 1.1
  9. *
  10. * Copyright (c) 2002-2003 The Apache Software Foundation. All rights
  11. * reserved.
  12. *
  13. * Redistribution and use in source and binary forms, with or without
  14. * modification, are permitted provided that the following conditions
  15. * are met:
  16. *
  17. * 1. Redistributions of source code must retain the above copyright
  18. * notice, this list of conditions and the following disclaimer.
  19. *
  20. * 2. Redistributions in binary form must reproduce the above copyright
  21. * notice, this list of conditions and the following disclaimer in
  22. * the documentation and/or other materials provided with the
  23. * distribution.
  24. *
  25. * 3. The end-user documentation included with the redistribution, if
  26. * any, must include the following acknowledgement:
  27. * "This product includes software developed by the
  28. * Apache Software Foundation (http://www.apache.org/)."
  29. * Alternately, this acknowledgement may appear in the software itself,
  30. * if and wherever such third-party acknowledgements normally appear.
  31. *
  32. * 4. The names "The Jakarta Project", "Commons", and "Apache Software
  33. * Foundation" must not be used to endorse or promote products derived
  34. * from this software without prior written permission. For written
  35. * permission, please contact apache@apache.org.
  36. *
  37. * 5. Products derived from this software may not be called "Apache"
  38. * nor may "Apache" appear in their names without prior written
  39. * permission of the Apache Software Foundation.
  40. *
  41. * THIS SOFTWARE IS PROVIDED ``AS IS'' AND ANY EXPRESSED OR IMPLIED
  42. * WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES
  43. * OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
  44. * DISCLAIMED. IN NO EVENT SHALL THE APACHE SOFTWARE FOUNDATION OR
  45. * ITS CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
  46. * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
  47. * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF
  48. * USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
  49. * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
  50. * OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT
  51. * OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
  52. * SUCH DAMAGE.
  53. * ====================================================================
  54. *
  55. * This software consists of voluntary contributions made by many
  56. * individuals on behalf of the Apache Software Foundation. For more
  57. * information on the Apache Software Foundation, please see
  58. * <http://www.apache.org/>.
  59. *
  60. */
  61. package org.apache.commons.dbutils;
  62. import java.sql.Connection;
  63. import java.sql.PreparedStatement;
  64. import java.sql.ResultSet;
  65. import java.sql.SQLException;
  66. import java.sql.Types;
  67. import java.util.Arrays;
  68. import javax.sql.DataSource;
  69. /**
  70. * Executes SQL queries with pluggable strategies for handling
  71. * <code>ResultSet</code>s. This class is thread safe.
  72. *
  73. * @see ResultSetHandler
  74. *
  75. * @author Henri Yandell
  76. * @author Juozas Baliuka
  77. * @author Steven Caswell
  78. * @author David Graham
  79. */
  80. public class QueryRunner {
  81. /**
  82. * The DataSource to retrieve connections from.
  83. */
  84. protected DataSource ds = null;
  85. /**
  86. * Constructor for QueryRunner.
  87. */
  88. public QueryRunner() {
  89. super();
  90. }
  91. /**
  92. * Constructor for QueryRunner. Methods that do not take a
  93. * <code>Connection</code> parameter will retrieve connections from this
  94. * <code>DataSource</code>.
  95. *
  96. * @param ds The <code>DataSource</code> to retrieve connections from.
  97. */
  98. public QueryRunner(DataSource ds) {
  99. super();
  100. this.ds = ds;
  101. }
  102. /**
  103. * Fill the <code>PreparedStatement</code> replacement parameters with
  104. * the given objects.
  105. * @param stmt
  106. * @param params Query replacement parameters; <code>null</code> is a valid
  107. * value to pass in.
  108. * @throws SQLException
  109. */
  110. protected void fillStatement(PreparedStatement stmt, Object[] params)
  111. throws SQLException {
  112. if (params == null) {
  113. return;
  114. }
  115. for (int i = 0; i < params.length; i++) {
  116. if (params[i] != null) {
  117. stmt.setObject(i + 1, params[i]);
  118. } else {
  119. stmt.setNull(i + 1, Types.OTHER);
  120. }
  121. }
  122. }
  123. /**
  124. * Returns the <code>DataSource</code> this runner is using.
  125. */
  126. public DataSource getDataSource() {
  127. return this.ds;
  128. }
  129. /**
  130. * Factory method that creates and initializes a
  131. * <code>PreparedStatement</code> object for the given SQL.
  132. * <code>QueryRunner</code> methods always call this method to prepare
  133. * statements for them. Subclasses can override this method to provide
  134. * special PreparedStatement configuration if needed. This implementation
  135. * simply calls <code>conn.prepareStatement(sql)</code>.
  136. *
  137. * @param conn The <code>Connection</code> used to create the
  138. * <code>PreparedStatement</code>
  139. * @param sql The SQL statement to prepare.
  140. * @return An initialized <code>PreparedStatement</code>.
  141. * @throws SQLException
  142. */
  143. protected PreparedStatement prepareStatement(Connection conn, String sql)
  144. throws SQLException {
  145. return conn.prepareStatement(sql);
  146. }
  147. /**
  148. * Execute an SQL SELECT query with a single replacement parameter. The
  149. * caller is responsible for connection cleanup.
  150. *
  151. * @param conn The connection to execute the query in.
  152. * @param sql The query to execute.
  153. * @param param The replacement parameter.
  154. * @param rsh The handler that converts the results into an object.
  155. * @return The object returned by the handler.
  156. * @throws SQLException
  157. */
  158. public Object query(
  159. Connection conn,
  160. String sql,
  161. Object param,
  162. ResultSetHandler rsh)
  163. throws SQLException {
  164. return this.query(conn, sql, new Object[] { param }, rsh);
  165. }
  166. /**
  167. * Execute an SQL SELECT query with replacement parameters. The
  168. * caller is responsible for connection cleanup.
  169. *
  170. * @param conn The connection to execute the query in.
  171. * @param sql The query to execute.
  172. * @param params The replacement parameters.
  173. * @param rsh The handler that converts the results into an object.
  174. * @return The object returned by the handler.
  175. * @throws SQLException
  176. */
  177. public Object query(
  178. Connection conn,
  179. String sql,
  180. Object[] params,
  181. ResultSetHandler rsh)
  182. throws SQLException {
  183. PreparedStatement stmt = null;
  184. ResultSet rs = null;
  185. Object result = null;
  186. try {
  187. stmt = this.prepareStatement(conn, sql);
  188. this.fillStatement(stmt, params);
  189. rs = this.wrap(stmt.executeQuery());
  190. result = rsh.handle(rs);
  191. } catch (SQLException e) {
  192. this.rethrow(e, sql, params);
  193. } finally {
  194. DbUtils.close(rs);
  195. DbUtils.close(stmt);
  196. }
  197. return result;
  198. }
  199. /**
  200. * Execute an SQL SELECT query without any replacement parameters. The
  201. * caller is responsible for connection cleanup.
  202. *
  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. * @return The object returned by the handler.
  207. * @throws SQLException
  208. */
  209. public Object query(Connection conn, String sql, ResultSetHandler rsh)
  210. throws SQLException {
  211. return this.query(conn, sql, (Object[]) null, rsh);
  212. }
  213. /**
  214. * Executes the given SELECT SQL with a single replacement parameter.
  215. * The <code>Connection</code> is retrieved from the
  216. * <code>DataSource</code> set in the constructor.
  217. *
  218. * @param sql The SQL statement to execute.
  219. * @param param The replacement parameter.
  220. * @param rsh The handler used to create the result object from
  221. * the <code>ResultSet</code>.
  222. *
  223. * @return An object generated by the handler.
  224. * @throws SQLException
  225. */
  226. public Object query(String sql, Object param, ResultSetHandler rsh)
  227. throws SQLException {
  228. return this.query(sql, new Object[] { param }, rsh);
  229. }
  230. /**
  231. * Executes the given SELECT SQL query and returns a result object.
  232. * The <code>Connection</code> is retrieved from the
  233. * <code>DataSource</code> set in the constructor.
  234. *
  235. * @param sql The SQL statement to execute.
  236. * @param params Initialize the PreparedStatement's IN parameters with
  237. * this array.
  238. *
  239. * @param rsh The handler used to create the result object from
  240. * the <code>ResultSet</code>.
  241. *
  242. * @return An object generated by the handler.
  243. * @throws SQLException
  244. */
  245. public Object query(String sql, Object[] params, ResultSetHandler rsh)
  246. throws SQLException {
  247. Connection conn = this.ds.getConnection();
  248. try {
  249. return this.query(conn, sql, params, rsh);
  250. } finally {
  251. DbUtils.close(conn);
  252. }
  253. }
  254. /**
  255. * Executes the given SELECT SQL without any replacement parameters.
  256. * The <code>Connection</code> is retrieved from the
  257. * <code>DataSource</code> set in the constructor.
  258. *
  259. * @param sql The SQL statement to execute.
  260. * @param rsh The handler used to create the result object from
  261. * the <code>ResultSet</code>.
  262. *
  263. * @return An object generated by the handler.
  264. * @throws SQLException
  265. */
  266. public Object query(String sql, ResultSetHandler rsh) throws SQLException {
  267. return this.query(sql, (Object[]) null, rsh);
  268. }
  269. /**
  270. * Throws a new exception with a more informative error message.
  271. *
  272. * @param cause The original exception that will be chained to the new
  273. * exception when it's rethrown.
  274. *
  275. * @param sql The query that was executing when the exception happened.
  276. *
  277. * @param params The query replacement paramaters; <code>null</code> is a
  278. * valid value to pass in.
  279. *
  280. * @throws SQLException
  281. */
  282. protected void rethrow(SQLException cause, String sql, Object[] params)
  283. throws SQLException {
  284. StringBuffer msg = new StringBuffer(cause.getMessage());
  285. msg.append(" Query: ");
  286. msg.append(sql);
  287. msg.append(" Parameters: ");
  288. if (params == null) {
  289. msg.append("[]");
  290. } else {
  291. msg.append(Arrays.asList(params));
  292. }
  293. SQLException e = new SQLException(msg.toString());
  294. e.setNextException(cause);
  295. throw e;
  296. }
  297. /**
  298. * Sets the <code>DataSource</code> this runner will use to get
  299. * database connections from. This should be called after creating a
  300. * runner with the default constructor if you intend to use the
  301. * execute methods without passing in a <code>Connection</code>.
  302. *
  303. * @param dataSource The DataSource to use.
  304. */
  305. public void setDataSource(DataSource dataSource) {
  306. this.ds = dataSource;
  307. }
  308. /**
  309. * Execute an SQL INSERT, UPDATE, or DELETE query without replacement
  310. * parameters.
  311. *
  312. * @param conn The connection to use to run the query.
  313. * @param sql The SQL to execute.
  314. * @return The number of rows updated.
  315. * @throws SQLException
  316. */
  317. public int update(Connection conn, String sql) throws SQLException {
  318. return this.update(conn, sql, (Object[]) null);
  319. }
  320. /**
  321. * Execute an SQL INSERT, UPDATE, or DELETE query with a single replacement
  322. * parameter.
  323. *
  324. * @param conn The connection to use to run the query.
  325. * @param sql The SQL to execute.
  326. * @param param The replacement parameter.
  327. * @return The number of rows updated.
  328. * @throws SQLException
  329. */
  330. public int update(Connection conn, String sql, Object param)
  331. throws SQLException {
  332. return this.update(conn, sql, new Object[] { param });
  333. }
  334. /**
  335. * Execute an SQL INSERT, UPDATE, or DELETE query.
  336. *
  337. * @param conn The connection to use to run the query.
  338. * @param sql The SQL to execute.
  339. * @param params The query replacement parameters.
  340. * @return The number of rows updated.
  341. * @throws SQLException
  342. */
  343. public int update(Connection conn, String sql, Object[] params)
  344. throws SQLException {
  345. PreparedStatement stmt = null;
  346. int rows = 0;
  347. try {
  348. stmt = this.prepareStatement(conn, sql);
  349. this.fillStatement(stmt, params);
  350. rows = stmt.executeUpdate();
  351. } catch (SQLException e) {
  352. this.rethrow(e, sql, params);
  353. } finally {
  354. DbUtils.close(stmt);
  355. }
  356. return rows;
  357. }
  358. /**
  359. * Executes the given INSERT, UPDATE, or DELETE SQL statement without
  360. * any replacement parameters. The <code>Connection</code> is retrieved
  361. * from the <code>DataSource</code> set in the constructor.
  362. *
  363. * @param sql The SQL statement to execute.
  364. * @throws SQLException
  365. * @return The number of rows updated.
  366. */
  367. public int update(String sql) throws SQLException {
  368. return this.update(sql, (Object[]) null);
  369. }
  370. /**
  371. * Executes the given INSERT, UPDATE, or DELETE SQL statement with
  372. * a single replacement parameter. The <code>Connection</code> is
  373. * retrieved from the <code>DataSource</code> set in the constructor.
  374. *
  375. * @param sql The SQL statement to execute.
  376. * @param param The replacement parameter.
  377. * @throws SQLException
  378. * @return The number of rows updated.
  379. */
  380. public int update(String sql, Object param) throws SQLException {
  381. return this.update(sql, new Object[] { param });
  382. }
  383. /**
  384. * Executes the given INSERT, UPDATE, or DELETE SQL statement. The
  385. * <code>Connection</code> is retrieved from the <code>DataSource</code>
  386. * set in the constructor.
  387. *
  388. * @param sql The SQL statement to execute.
  389. * @param params Initializes the PreparedStatement's IN (i.e. '?')
  390. * parameters.
  391. * @throws SQLException
  392. * @return The number of rows updated.
  393. */
  394. public int update(String sql, Object[] params) throws SQLException {
  395. Connection conn = this.ds.getConnection();
  396. try {
  397. return this.update(conn, sql, params);
  398. } finally {
  399. DbUtils.close(conn);
  400. }
  401. }
  402. /**
  403. * Wrap the <code>ResultSet</code> in a decorator before processing it.
  404. * This implementation returns the <code>ResultSet</code> it is given
  405. * without any decoration.
  406. *
  407. * <p>
  408. * Often, the implementation of this method can be done in an anonymous
  409. * inner class like this:
  410. * </p>
  411. * <pre>
  412. * QueryRunner run = new QueryRunner() {
  413. * protected ResultSet wrap(ResultSet rs) {
  414. * return StringTrimmedResultSet.wrap(rs);
  415. * }
  416. * };
  417. * </pre>
  418. *
  419. * @param rs The <code>ResultSet</code> to decorate; never
  420. * <code>null</code>.
  421. * @return The <code>ResultSet</code> wrapped in some decorator.
  422. */
  423. protected ResultSet wrap(ResultSet rs) {
  424. return rs;
  425. }
  426. }