Java/Database SQL JDBC/Select Query
Executes SQL queries with pluggable strategies for handling ResultSets. This class is thread safe.
/*
* 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;
}
Retrieving All Rows from a Database Table
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();
}
}
