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
