java queryrunner_QueryRunner类实战

先上一个登录代码---判断登录是否成功

1、c3p0-config.xml

root

123456

com.mysql.jdbc.Driver

jdbc:mysql:///filter

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 classC3P0Utils {//import com.mchange.v2.c3p0.ComboPooledDataSource;

private static DataSource dataSource = newComboPooledDataSource();private static ThreadLocal tl = new ThreadLocal();//直接可以获取一个连接池

public staticDataSource getDataSource() {returndataSource;

}public staticConnection getConnection() throws SQLException{returndataSource.getConnection();

}//获取连接对象

public staticConnection getCurrentConnection() throws SQLException {

Connection con= tl.get();if (con == null) {

con=dataSource.getConnection();

tl.set(con);

}returncon;

}//开启事务

public static voidstartTransaction() throws SQLException {

Connection con=getCurrentConnection();if (con != null) {

con.setAutoCommit(false);

}

}//事务回滚

public static voidrollback() throws SQLException {

Connection con=getCurrentConnection();if (con != null) {

con.rollback();

}

}//提交并且 关闭资源及从ThreadLocall中释放

public static voidcommitAndRelease() throws SQLException {

Connection con=getCurrentConnection();if (con != null) {

con.commit();//事务提交

con.close();//关闭资源

tl.remove();//从线程绑定中移除

}

}//关闭资源方法

public static voidcloseConnection() throws SQLException {

Connection con=getCurrentConnection();if (con != null) {

con.close();

}

}public static voidcloseStatement(Statement st) throws SQLException {if (st != null) {

st.close();

}

}public static voidcloseResultSet(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 classUserDao {publicUser login(String username, String password) throws SQLException {

QueryRunner runner= newQueryRunner(C3P0Utils.getDataSource());

String sql= "select * from user where username=? and password=?";return runner.query(sql, new BeanHandler(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 selectAllBooks() throws SQLException {

QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());

return qr.query("select * from books", new BeanListHandler(Book.class));

}

第二种: 需要一个参数查询

//根据id查询指定的书

pub