Java/Database SQL JDBC/Select Query
Executes SQL queries with pluggable strategies for handling ResultSets. This class is thread safe.
<source lang="java">
/*
* 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
* ResultSet
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*pmdKnownBroken
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 *Connection
parameter will retrieve connections from this *DataSource
. * * @param ds TheDataSource
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 *Connection
parameter will retrieve connections from this *DataSource
. * * @param ds TheDataSource
to retrieve connections from. * @param pmdKnownBroken Oracle drivers don"t support {@link ParameterMetaData#getParameterType(int) }; * ifpmdKnownBroken
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 *Connection
is retrieved from theDataSource
* set in the constructor. ThisConnection
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 thePreparedStatement
replacement parameters with * the given objects. * @param stmt PreparedStatement to fill * @param params Query replacement parameters;null
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 thePreparedStatement
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 thePreparedStatement
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 theDataSource
this runner is using. *QueryRunner
methods always call this method to get the *DataSource
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 *PreparedStatement
object for the given SQL. *QueryRunner
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 callsconn.prepareStatement(sql)
. * * @param conn TheConnection
used to create the *PreparedStatement
* @param sql The SQL statement to prepare. * @return An initializedPreparedStatement
. * @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 *Connection
object.QueryRunner
methods * always call this method to retrieve connections from its DataSource. * Subclasses can override this method to provide * specialConnection
configuration if needed. This * implementation simply callsds.getConnection()
. * * @return An initializedConnection
. * @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. * TheConnection
is retrieved from the *DataSource
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 * theResultSet
. * * @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. * TheConnection
is retrieved from the *DataSource
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 * theResultSet
. * * @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. * TheConnection
is retrieved from the *DataSource
set in the constructor. * * @param sql The SQL statement to execute. * @param rsh The handler used to create the result object from * theResultSet
. * @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. * TheConnection
is retrieved from the *DataSource
set in the constructor. * * @param sql The SQL statement to execute. * @param rsh The handler used to create the result object from * theResultSet
. * * @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;null
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. TheConnection
is retrieved * from theDataSource
set in the constructor. This *Connection
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. TheConnection
is * retrieved from theDataSource
set in the constructor. * ThisConnection
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 *Connection
is retrieved from theDataSource
* set in the constructor. ThisConnection
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 theResultSet
in a decorator before processing it. * This implementation returns theResultSet
it is given * without any decoration. *
* Often, the implementation of this method can be done in an anonymous * inner class like this: *
** QueryRunner run = new QueryRunner() { * protected ResultSet wrap(ResultSet rs) { * return StringTrimmedResultSet.wrap(rs); * } * }; *
* * @param rs TheResultSet
to decorate; never *null
. * @return TheResultSet
wrapped in some decorator. */ protected ResultSet wrap(ResultSet rs) { return rs; } }
interface ResultSetHandler {
/** * Turn theResultSet
into an Object. * * @param rs TheResultSet
to handle. It has not been touched * before being passed to this method. * * @return An Object initialized withResultSet
data. It is * legal for implementations to returnnull
if the *ResultSet
contained 0 rows. * * @throws SQLException if a database access error occurs */ public Object handle(ResultSet rs) throws SQLException;
}
</source>
Retrieving All Rows from a Database Table
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class Main {
public static void main(String[] argv) throws Exception { String driver = "com.mysql.jdbc.Driver"; String user = "root"; String pass = "root"; Class.forName(driver).newInstance(); Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctutorial", user, pass); Statement st = con.createStatement(); ResultSet res = st.executeQuery("SELECT * FROM emp"); while (res.next()) { int i = res.getInt("ID"); String s = res.getString("name"); System.out.println(i + "\t\t" + s); } con.close(); }
}
</source>