QueryRunner类实战
先上一个登录代码---判断登录是否成功
1、c3p0-config.xml
<?xml version="1.0" encoding="UTF-8"?> <c3p0-config> <default-config> <property name="user">root</property> <property name="password">123456</property> <property name="driverClass">com.mysql.jdbc.Driver</property> <property name="jdbcUrl">jdbc:mysql:///filter</property> </default-config> </c3p0-config>
2.C3P0Utils
package com.ithiema.utils; import com.mchange.v2.c3p0.ComboPooledDataSource; import javax.sql.DataSource; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class C3P0Utils { //import com.mchange.v2.c3p0.ComboPooledDataSource; private static DataSource dataSource = new ComboPooledDataSource(); private static ThreadLocal<Connection> tl = new ThreadLocal<Connection>(); // 直接可以获取一个连接池 public static DataSource getDataSource() { return dataSource; } public static Connection getConnection() throws SQLException{ return dataSource.getConnection(); } // 获取连接对象 public static Connection getCurrentConnection() throws SQLException { Connection con = tl.get(); if (con == null) { con = dataSource.getConnection(); tl.set(con); } return con; } // 开启事务 public static void startTransaction() throws SQLException { Connection con = getCurrentConnection(); if (con != null) { con.setAutoCommit(false); } } // 事务回滚 public static void rollback() throws SQLException { Connection con = getCurrentConnection(); if (con != null) { con.rollback(); } } // 提交并且 关闭资源及从ThreadLocall中释放 public static void commitAndRelease() throws SQLException { Connection con = getCurrentConnection(); if (con != null) { con.commit(); // 事务提交 con.close();// 关闭资源 tl.remove();// 从线程绑定中移除 } } // 关闭资源方法 public static void closeConnection() throws SQLException { Connection con = getCurrentConnection(); if (con != null) { con.close(); } } public static void closeStatement(Statement st) throws SQLException { if (st != null) { st.close(); } } public static void closeResultSet(ResultSet rs) throws SQLException { if (rs != null) { rs.close(); } } }
3.UserDao.java
package com.ithiema.dao; import com.ithiema.domain.User; import com.ithiema.utils.C3P0Utils; import org.apache.commons.dbutils.QueryRunner; import org.apache.commons.dbutils.handlers.BeanHandler; import java.sql.SQLException; public class UserDao { public User login(String username, String password) throws SQLException { QueryRunner runner = new QueryRunner(C3P0Utils.getDataSource()); String sql = "select * from user where username=? and password=?"; return runner.query(sql, new BeanHandler<User>(User.class), username,password); } }
QueryRunner类
QueryRunner中提供对sql语句操作的API
它主要有三个方法
query() 用于执行select
update() 用于执行insert/update/delete
batch() 批处理
1,Query语句
先来看下query的两种形式, 我们这里主要讲第一个方法, 因为我们用C3P0来统一管理connection.(QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource()))
query(sql,ResultSetHandler,Object...params);
query(conn,sql,ResultSetHandler,Object...params);
第一种: 不需要params
//查询所有图书
public List<Book> selectAllBooks() throws SQLException {
QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
return qr.query("select * from books", new BeanListHandler<Book>(Book.class));
}
第二种: 需要一个参数查询
//根据id查询指定的书
public Book selectBookById(String id) throws SQLException {
QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
return qr.query("select * from books where id=?", new BeanHandler(Book.class),id);
}
第三种:需要多个参数查询
//多条件查询图书信息
public List<Book> findBookByManyCondition(String id, String category,
String name, String minprice, String maxprice) throws SQLException {
StringBuilder sql = new StringBuilder("select * from books where 1=1");
List list = new ArrayList();
if(!"".equals(id)){
sql.append(" and id like ?");
list.add("%"+id+"%");
}
if(!"".equals(category)){
sql.append(" and category=?");
list.add(category);
}
if(!"".equals(name)){
sql.append(" and name like ?");
list.add("%"+name+"%");
}
if(!"".equals(minprice)){
sql.append(" and price > ?");
list.add(minprice);
}
if(!"".equals(maxprice)){
sql.append(" and price < ?");
list.add(maxprice);
}
QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
return qr.query(sql.toString(),new BeanListHandler<Book>(Book.class),list.toArray());
}
那么我们来看下源码的实现:
(1)QueryRunner.java
//第一种情况,无参数
public <T> T query(String sql, ResultSetHandler<T> rsh) throws SQLException {
Connection conn = this.prepareConnection();
return this.query(conn, true, sql, rsh, (Object[]) null);
}
//第二种和第三种使用同一方法: 需要参数
public <T> T query(String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException {
Connection conn = this.prepareConnection();
return this.query(conn, true, sql, rsh, params);
}
解读: 这里先是获取connection, 利用this.preparaConnection() 获取. 然后调用query()方法去执行查询语句. 接下来看源码是如何获取到当前传输过来的connection以及query()方法的内部实现.
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();
}
这里很简单, 因为我们用的C3P0数据库连接池获取的DataSource, 所以这里直就可以过去到当前的Connection.接下来就看下query()方法的内部实现.
private <T> T query(Connection conn, boolean closeConn, String sql, ResultSetHandler<T> rsh, Object... params)
throws SQLException {
if (conn == null) {
throw new SQLException("Null connection");
}
if (sql == null) {
if (closeConn) {
close(conn);
}
throw new SQLException("Null SQL statement");
}
if (rsh == null) {
if (closeConn) {
close(conn);
}
throw new SQLException("Null ResultSetHandler");
}
PreparedStatement stmt = null;
ResultSet rs = null;
T 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 {
try {
close(rs);
} finally {
close(stmt);
if (closeConn) {
close(conn);
}
}
}
return result;
}
解读: 在这里可以看出, 无论是否有传递参数params, 都调用的是同一个query方法, 接着来看this.fillStatement(stmt, params);是如何将参数赋予preparedStatement中的.
public void fillStatement(PreparedStatement stmt, Object... params) throws SQLException {
// check the parameter count, if we can
ParameterMetaData pmd = null;
if (!pmdKnownBroken) {
pmd = stmt.getParameterMetaData();
int stmtCount = pmd.getParameterCount();
int paramsCount = params == null ? 0 : params.length;
if (stmtCount != paramsCount) {
throw new SQLException("Wrong number of parameters: expected "
+ stmtCount + ", was given " + paramsCount);
}
}
// nothing to do here
if (params == null) {
return;
}
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);
}
}
}
这个方法就是核心所在.
第一种情况: 当params为null的时候, 直接return然后执行sql语句.
第二种第三种情况: 当params不为null时, 循环遍历传入的params, 然后将params赋值到preparedStatement中, 然后填充占位符进行sql查询. 这里我们也来回顾下直接使用preparedStatement来进行查询的方式:
@Test
public void update(){
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
try{
conn = JdbcUtils.getConnection();
String sql = "update users set name=?,email=? where id=?";
st = conn.prepareStatement(sql);
st.setString(1, "gacl");
st.setString(2, "gacl@sina.com");
st.setInt(3, 2);
int num = st.executeUpdate();
if(num>0){
System.out.println("更新成功！！");
}
}catch (Exception e) {
e.printStackTrace();
}finally{
JdbcUtils.release(conn, st, rs);
}
}
@Test
public void find(){
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
try{
conn = JdbcUtils.getConnection();
String sql = "select * from users where id=?";
st = conn.prepareStatement(sql);
st.setInt(1, 1);
rs = st.executeQuery();
if(rs.next()){
System.out.println(rs.getString("name"));
}
}catch (Exception e) {
}finally{
JdbcUtils.release(conn, st, rs);
}
}
2, Update语句
查看update语句:
//修改图书
public void updateBook(Book book) throws SQLException {
QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
qr.update(
"UPDATE books SET NAME=? ,price=?,bnum=?,category=?,description=? WHERE id=?",
book.getName(), book.getPrice(), book.getBnum(),
book.getCategory(), book.getDescription(), book.getId())
}
接着是QueryRunner.java中的update 方法:
public int update(String sql, Object... params) throws SQLException {
Connection conn = this.prepareConnection();
return this.update(conn, true, sql, params);
}
private int update(Connection conn, boolean closeConn, String sql, Object... params) throws SQLException {
if (conn == null) {
throw new SQLException("Null connection");
}
if (sql == null) {
if (closeConn) {
close(conn);
}
throw new SQLException("Null SQL statement");
}
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 {
close(stmt);
if (closeConn) {
close(conn);
}
}
return rows;
}
到了参数赋值的时候又调用了上面的fillStatement方法, 这里就不再阐述了.
3, Batch语句
这里直接看batch方法的实例, 然后结合源码的实现.
//批量删除
public void delBooks(String[] ids) throws SQLException {
QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
Object[][] params = new Object[ids.length][];//高维确定执行sql语句的次数，低维是给？赋值
for (int i = 0; i < params.length; i++) {
params[i] = new Object[]{ids[i]};//给“？”赋值
}
qr.batch("delete from books where id=?", params);
}
然后看QueryRunner中的batch()方法:
public int[] batch(String sql, Object[][] params) throws SQLException {
Connection conn = this.prepareConnection();
return this.batch(conn, true, sql, params);
}
private int[] batch(Connection conn, boolean closeConn, String sql, Object[][] params) throws SQLException {
if (conn == null) {
throw new SQLException("Null connection");
}
if (sql == null) {
if (closeConn) {
close(conn);
}
throw new SQLException("Null SQL statement");
}
if (params == null) {
if (closeConn) {
close(conn);
}
throw new SQLException("Null parameters. If parameters aren't need, pass an empty array.");
}
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, (Object[])params);
} finally {
close(stmt);
if (closeConn) {
close(conn);
}
}
return rows;
}
解读: 因为params是一个二维数组, 所以往preparedStatement中赋值的时候使用了for循环, 然后通过preparedstatement.addBatch() 进行批量添加, 然后执行executeBatch()进行操作.
/**
* Adds a set of parameters to this <code>PreparedStatement</code>
* object's batch of commands.
*
* @exception SQLException if a database access error occurs or
* this method is called on a closed <code>PreparedStatement</code>
* @see Statement#addBatch
* @since 1.2
*/
void addBatch() throws SQLException;
你们都是有经验的开发人员
