Java tutorial
/* * Licensed to the Apache Software Foundation (ASF) under one or more * contributor license agreements. See the NOTICE file distributed with * this work for additional information regarding copyright ownership. * The ASF licenses this file to You under the Apache License, Version 2.0 * (the "License"); you may not use this file except in compliance with * the License. You may obtain a copy of the License at * * http://www.apache.org/licenses/LICENSE-2.0 * * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. * See the License for the specific language governing permissions and * limitations under the License. */ import java.beans.IntrospectionException; import java.beans.Introspector; import java.beans.PropertyDescriptor; import java.lang.reflect.InvocationTargetException; import java.lang.reflect.Method; import java.sql.Connection; import java.sql.ParameterMetaData; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Types; import java.util.Arrays; import javax.sql.DataSource; /** * Executes SQL queries with pluggable strategies for handling * <code>ResultSet</code>s. This class is thread safe. * * @see ResultSetHandler */ public class QueryRunner { /** * Is {@link ParameterMetaData#getParameterType(int)} broken (have we tried it yet)? */ private volatile boolean pmdKnownBroken = false; /** * The DataSource to retrieve connections from. */ protected final DataSource ds; /** * Constructor for QueryRunner. */ public QueryRunner() { super(); ds = null; } /** * Constructor for QueryRunner, allows workaround for Oracle drivers * @param pmdKnownBroken Oracle drivers don't support {@link ParameterMetaData#getParameterType(int) }; * if <code>pmdKnownBroken</code> is set to true, we won't even try it; if false, we'll try it, * and if it breaks, we'll remember not to use it again. */ public QueryRunner(boolean pmdKnownBroken) { super(); this.pmdKnownBroken = pmdKnownBroken; ds = null; } /** * Constructor for QueryRunner, allows workaround for Oracle drivers. Methods that do not take a * <code>Connection</code> parameter will retrieve connections from this * <code>DataSource</code>. * * @param ds The <code>DataSource</code> to retrieve connections from. */ public QueryRunner(DataSource ds) { super(); this.ds = ds; } /** * Constructor for QueryRunner, allows workaround for Oracle drivers. Methods that do not take a * <code>Connection</code> parameter will retrieve connections from this * <code>DataSource</code>. * * @param ds The <code>DataSource</code> to retrieve connections from. * @param pmdKnownBroken Oracle drivers don't support {@link ParameterMetaData#getParameterType(int) }; * if <code>pmdKnownBroken</code> is set to true, we won't even try it; if false, we'll try it, * and if it breaks, we'll remember not to use it again. */ public QueryRunner(DataSource ds, boolean pmdKnownBroken) { super(); this.pmdKnownBroken = pmdKnownBroken; this.ds = ds; } /** * Execute a batch of SQL INSERT, UPDATE, or DELETE queries. * * @param conn The Connection to use to run the query. The caller is * responsible for closing this Connection. * @param sql The SQL to execute. * @param params An array of query replacement parameters. Each row in * this array is one set of batch replacement values. * @return The number of rows updated per statement. * @throws SQLException if a database access error occurs * @since DbUtils 1.1 */ public int[] batch(Connection conn, String sql, Object[][] params) throws SQLException { PreparedStatement stmt = null; int[] rows = null; try { stmt = this.prepareStatement(conn, sql); for (int i = 0; i < params.length; i++) { this.fillStatement(stmt, params[i]); stmt.addBatch(); } rows = stmt.executeBatch(); } catch (SQLException e) { this.rethrow(e, sql, params); } finally { stmt.close(); } return rows; } /** * Execute a batch of SQL INSERT, UPDATE, or DELETE queries. The * <code>Connection</code> is retrieved from the <code>DataSource</code> * set in the constructor. This <code>Connection</code> must be in * auto-commit mode or the update will not be saved. * * @param sql The SQL to execute. * @param params An array of query replacement parameters. Each row in * this array is one set of batch replacement values. * @return The number of rows updated per statement. * @throws SQLException if a database access error occurs * @since DbUtils 1.1 */ public int[] batch(String sql, Object[][] params) throws SQLException { Connection conn = this.prepareConnection(); try { return this.batch(conn, sql, params); } finally { conn.close(); } } /** * Fill the <code>PreparedStatement</code> replacement parameters with * the given objects. * @param stmt PreparedStatement to fill * @param params Query replacement parameters; <code>null</code> is a valid * value to pass in. * @throws SQLException if a database access error occurs */ public void fillStatement(PreparedStatement stmt, Object[] params) throws SQLException { if (params == null) { return; } ParameterMetaData pmd = stmt.getParameterMetaData(); if (pmd.getParameterCount() < params.length) { throw new SQLException( "Too many parameters: expected " + pmd.getParameterCount() + ", was given " + params.length); } for (int i = 0; i < params.length; i++) { if (params[i] != null) { stmt.setObject(i + 1, params[i]); } else { // VARCHAR works with many drivers regardless // of the actual column type. Oddly, NULL and // OTHER don't work with Oracle's drivers. int sqlType = Types.VARCHAR; if (!pmdKnownBroken) { try { sqlType = pmd.getParameterType(i + 1); } catch (SQLException e) { pmdKnownBroken = true; } } stmt.setNull(i + 1, sqlType); } } } /** * Fill the <code>PreparedStatement</code> replacement parameters with the * given object's bean property values. * * @param stmt * PreparedStatement to fill * @param bean * a JavaBean object * @param properties * an ordered array of properties; this gives the order to insert * values in the statement * @throws SQLException * if a database access error occurs */ public void fillStatementWithBean(PreparedStatement stmt, Object bean, PropertyDescriptor[] properties) throws SQLException { Object[] params = new Object[properties.length]; for (int i = 0; i < properties.length; i++) { PropertyDescriptor property = properties[i]; Object value = null; Method method = property.getReadMethod(); if (method == null) { throw new RuntimeException( "No read method for bean property " + bean.getClass() + " " + property.getName()); } try { value = method.invoke(bean, new Object[0]); } catch (InvocationTargetException e) { throw new RuntimeException("Couldn't invoke method: " + method, e); } catch (IllegalArgumentException e) { throw new RuntimeException("Couldn't invoke method with 0 arguments: " + method, e); } catch (IllegalAccessException e) { throw new RuntimeException("Couldn't invoke method: " + method, e); } params[i] = value; } fillStatement(stmt, params); } /** * Fill the <code>PreparedStatement</code> replacement parameters with the * given object's bean property values. * * @param stmt * PreparedStatement to fill * @param bean * a JavaBean object * @param propertyNames * an ordered array of property names (these should match the * getters/setters); this gives the order to insert values in the * statement * @throws SQLException * if a database access error occurs */ public void fillStatementWithBean(PreparedStatement stmt, Object bean, String[] propertyNames) throws SQLException { PropertyDescriptor[] descriptors; try { descriptors = Introspector.getBeanInfo(bean.getClass()).getPropertyDescriptors(); } catch (IntrospectionException e) { throw new RuntimeException("Couldn't introspect bean " + bean.getClass().toString(), e); } PropertyDescriptor[] sorted = new PropertyDescriptor[propertyNames.length]; for (int i = 0; i < propertyNames.length; i++) { String propertyName = propertyNames[i]; if (propertyName == null) { throw new NullPointerException("propertyName can't be null: " + i); } boolean found = false; for (int j = 0; j < descriptors.length; j++) { PropertyDescriptor descriptor = descriptors[j]; if (propertyName.equals(descriptor.getName())) { sorted[i] = descriptor; found = true; break; } } if (!found) { throw new RuntimeException("Couldn't find bean property: " + bean.getClass() + " " + propertyName); } } fillStatementWithBean(stmt, bean, sorted); } /** * Returns the <code>DataSource</code> this runner is using. * <code>QueryRunner</code> methods always call this method to get the * <code>DataSource</code> so subclasses can provide specialized * behavior. * * @return DataSource the runner is using */ public DataSource getDataSource() { return this.ds; } /** * Factory method that creates and initializes a * <code>PreparedStatement</code> object for the given SQL. * <code>QueryRunner</code> methods always call this method to prepare * statements for them. Subclasses can override this method to provide * special PreparedStatement configuration if needed. This implementation * simply calls <code>conn.prepareStatement(sql)</code>. * * @param conn The <code>Connection</code> used to create the * <code>PreparedStatement</code> * @param sql The SQL statement to prepare. * @return An initialized <code>PreparedStatement</code>. * @throws SQLException if a database access error occurs */ protected PreparedStatement prepareStatement(Connection conn, String sql) throws SQLException { return conn.prepareStatement(sql); } /** * Factory method that creates and initializes a * <code>Connection</code> object. <code>QueryRunner</code> methods * always call this method to retrieve connections from its DataSource. * Subclasses can override this method to provide * special <code>Connection</code> configuration if needed. This * implementation simply calls <code>ds.getConnection()</code>. * * @return An initialized <code>Connection</code>. * @throws SQLException if a database access error occurs * @since DbUtils 1.1 */ protected Connection prepareConnection() throws SQLException { if (this.getDataSource() == null) { throw new SQLException("QueryRunner requires a DataSource to be " + "invoked in this way, or a Connection should be passed in"); } return this.getDataSource().getConnection(); } /** * Execute an SQL SELECT query with a single replacement parameter. The * caller is responsible for closing the connection. * * @param conn The connection to execute the query in. * @param sql The query to execute. * @param param The replacement parameter. * @param rsh The handler that converts the results into an object. * @return The object returned by the handler. * @throws SQLException if a database access error occurs * @deprecated Use {@link #query(Connection,String,ResultSetHandler,Object[])} instead */ public Object query(Connection conn, String sql, Object param, ResultSetHandler rsh) throws SQLException { return this.query(conn, sql, rsh, new Object[] { param }); } /** * Execute an SQL SELECT query with replacement parameters. The * caller is responsible for closing the connection. * * @param conn The connection to execute the query in. * @param sql The query to execute. * @param params The replacement parameters. * @param rsh The handler that converts the results into an object. * @return The object returned by the handler. * @throws SQLException if a database access error occurs * @deprecated Use {@link #query(Connection,String,ResultSetHandler,Object[])} instead */ public Object query(Connection conn, String sql, Object[] params, ResultSetHandler rsh) throws SQLException { return query(conn, sql, rsh, params); } /** * Execute an SQL SELECT query with replacement parameters. The * caller is responsible for closing the connection. * * @param conn The connection to execute the query in. * @param sql The query to execute. * @param rsh The handler that converts the results into an object. * @param params The replacement parameters. * @return The object returned by the handler. * @throws SQLException if a database access error occurs */ public Object query(Connection conn, String sql, ResultSetHandler rsh, Object[] params) throws SQLException { PreparedStatement stmt = null; ResultSet rs = null; Object result = null; try { stmt = this.prepareStatement(conn, sql); this.fillStatement(stmt, params); rs = this.wrap(stmt.executeQuery()); result = rsh.handle(rs); } catch (SQLException e) { this.rethrow(e, sql, params); } finally { rs.close(); stmt.close(); } return result; } /** * Execute an SQL SELECT query without any replacement parameters. The * caller is responsible for closing the connection. * * @param conn The connection to execute the query in. * @param sql The query to execute. * @param rsh The handler that converts the results into an object. * @return The object returned by the handler. * @throws SQLException if a database access error occurs */ public Object query(Connection conn, String sql, ResultSetHandler rsh) throws SQLException { return this.query(conn, sql, rsh, (Object[]) null); } /** * Executes the given SELECT SQL with a single replacement parameter. * The <code>Connection</code> is retrieved from the * <code>DataSource</code> set in the constructor. * * @param sql The SQL statement to execute. * @param param The replacement parameter. * @param rsh The handler used to create the result object from * the <code>ResultSet</code>. * * @return An object generated by the handler. * @throws SQLException if a database access error occurs * @deprecated Use {@link #query(String,ResultSetHandler,Object[])} instead */ public Object query(String sql, Object param, ResultSetHandler rsh) throws SQLException { return this.query(sql, rsh, new Object[] { param }); } /** * Executes the given SELECT SQL query and returns a result object. * The <code>Connection</code> is retrieved from the * <code>DataSource</code> set in the constructor. * * @param sql The SQL statement to execute. * @param params Initialize the PreparedStatement's IN parameters with * this array. * * @param rsh The handler used to create the result object from * the <code>ResultSet</code>. * * @return An object generated by the handler. * @throws SQLException if a database access error occurs * @deprecated Use {@link #query(String,ResultSetHandler,Object[])} instead */ public Object query(String sql, Object[] params, ResultSetHandler rsh) throws SQLException { return query(sql, rsh, params); } /** * Executes the given SELECT SQL query and returns a result object. * The <code>Connection</code> is retrieved from the * <code>DataSource</code> set in the constructor. * * @param sql The SQL statement to execute. * @param rsh The handler used to create the result object from * the <code>ResultSet</code>. * @param params Initialize the PreparedStatement's IN parameters with * this array. * @return An object generated by the handler. * @throws SQLException if a database access error occurs */ public Object query(String sql, ResultSetHandler rsh, Object[] params) throws SQLException { Connection conn = this.prepareConnection(); try { return this.query(conn, sql, rsh, params); } finally { conn.close(); } } /** * Executes the given SELECT SQL without any replacement parameters. * The <code>Connection</code> is retrieved from the * <code>DataSource</code> set in the constructor. * * @param sql The SQL statement to execute. * @param rsh The handler used to create the result object from * the <code>ResultSet</code>. * * @return An object generated by the handler. * @throws SQLException if a database access error occurs */ public Object query(String sql, ResultSetHandler rsh) throws SQLException { return this.query(sql, rsh, (Object[]) null); } /** * Throws a new exception with a more informative error message. * * @param cause The original exception that will be chained to the new * exception when it's rethrown. * * @param sql The query that was executing when the exception happened. * * @param params The query replacement parameters; <code>null</code> is a * valid value to pass in. * * @throws SQLException if a database access error occurs */ protected void rethrow(SQLException cause, String sql, Object[] params) throws SQLException { String causeMessage = cause.getMessage(); if (causeMessage == null) { causeMessage = ""; } StringBuffer msg = new StringBuffer(causeMessage); msg.append(" Query: "); msg.append(sql); msg.append(" Parameters: "); if (params == null) { msg.append("[]"); } else { msg.append(Arrays.asList(params)); } SQLException e = new SQLException(msg.toString(), cause.getSQLState(), cause.getErrorCode()); e.setNextException(cause); throw e; } /** * Execute an SQL INSERT, UPDATE, or DELETE query without replacement * parameters. * * @param conn The connection to use to run the query. * @param sql The SQL to execute. * @return The number of rows updated. * @throws SQLException if a database access error occurs */ public int update(Connection conn, String sql) throws SQLException { return this.update(conn, sql, (Object[]) null); } /** * Execute an SQL INSERT, UPDATE, or DELETE query with a single replacement * parameter. * * @param conn The connection to use to run the query. * @param sql The SQL to execute. * @param param The replacement parameter. * @return The number of rows updated. * @throws SQLException if a database access error occurs */ public int update(Connection conn, String sql, Object param) throws SQLException { return this.update(conn, sql, new Object[] { param }); } /** * Execute an SQL INSERT, UPDATE, or DELETE query. * * @param conn The connection to use to run the query. * @param sql The SQL to execute. * @param params The query replacement parameters. * @return The number of rows updated. * @throws SQLException if a database access error occurs */ public int update(Connection conn, String sql, Object[] params) throws SQLException { PreparedStatement stmt = null; int rows = 0; try { stmt = this.prepareStatement(conn, sql); this.fillStatement(stmt, params); rows = stmt.executeUpdate(); } catch (SQLException e) { this.rethrow(e, sql, params); } finally { stmt.close(); } return rows; } /** * Executes the given INSERT, UPDATE, or DELETE SQL statement without * any replacement parameters. The <code>Connection</code> is retrieved * from the <code>DataSource</code> set in the constructor. This * <code>Connection</code> must be in auto-commit mode or the update will * not be saved. * * @param sql The SQL statement to execute. * @throws SQLException if a database access error occurs * @return The number of rows updated. */ public int update(String sql) throws SQLException { return this.update(sql, (Object[]) null); } /** * Executes the given INSERT, UPDATE, or DELETE SQL statement with * a single replacement parameter. The <code>Connection</code> is * retrieved from the <code>DataSource</code> set in the constructor. * This <code>Connection</code> must be in auto-commit mode or the * update will not be saved. * * @param sql The SQL statement to execute. * @param param The replacement parameter. * @throws SQLException if a database access error occurs * @return The number of rows updated. */ public int update(String sql, Object param) throws SQLException { return this.update(sql, new Object[] { param }); } /** * Executes the given INSERT, UPDATE, or DELETE SQL statement. The * <code>Connection</code> is retrieved from the <code>DataSource</code> * set in the constructor. This <code>Connection</code> must be in * auto-commit mode or the update will not be saved. * * @param sql The SQL statement to execute. * @param params Initializes the PreparedStatement's IN (i.e. '?') * parameters. * @throws SQLException if a database access error occurs * @return The number of rows updated. */ public int update(String sql, Object[] params) throws SQLException { Connection conn = this.prepareConnection(); try { return this.update(conn, sql, params); } finally { conn.close(); } } /** * Wrap the <code>ResultSet</code> in a decorator before processing it. * This implementation returns the <code>ResultSet</code> it is given * without any decoration. * * <p> * Often, the implementation of this method can be done in an anonymous * inner class like this: * </p> * <pre> * QueryRunner run = new QueryRunner() { * protected ResultSet wrap(ResultSet rs) { * return StringTrimmedResultSet.wrap(rs); * } * }; * </pre> * * @param rs The <code>ResultSet</code> to decorate; never * <code>null</code>. * @return The <code>ResultSet</code> wrapped in some decorator. */ protected ResultSet wrap(ResultSet rs) { return rs; } } interface ResultSetHandler { /** * Turn the <code>ResultSet</code> into an Object. * * @param rs The <code>ResultSet</code> to handle. It has not been touched * before being passed to this method. * * @return An Object initialized with <code>ResultSet</code> data. It is * legal for implementations to return <code>null</code> if the * <code>ResultSet</code> contained 0 rows. * * @throws SQLException if a database access error occurs */ public Object handle(ResultSet rs) throws SQLException; }