在介绍dbUtils之前,先通过使用c3p0数据库连接池来操作数据库的增、删、改,查具体使用参加 JDBC学习(八)数据库连接池
建立数据库表 student
CREATE TABLE `student` (
`id` int AUTO_INCREMENT,
`username` varchar(30) ,
`password` int,
`age` int,
PRIMARY KEY (`id`)
);
先导jar包
c3p0-0.9.2-pre1.jar
mchange-commons-0.2.jar
mysql-connector-java-5.1.46.jar
c3p0配置文件
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<!-- 这是默认配置信息 -->
<default-config>
<!-- 连接四大参数配置 -->
<property name="jdbcUrl">jdbc:mysql://localhost:3306/sql_test</property>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="user">root</property>
<property name="password">123456</property>
<!-- 池参数配置 -->
<property name="acquireIncrement">3</property>
<property name="initialPoolSize">10</property>
<property name="minPoolSize">2</property>
<property name="maxPoolSize">10</property>
</default-config>
</c3p0-config>
JDBC连接工具类:JDBCUtils.java
/**
* 使用c3p0连接池
*/
public class JDBCUtils {
//创建一个连接池对象,这里在配置文件中设置参数 c3p0-config.xml
private static ComboPooledDataSource cpds=new ComboPooledDataSource();
/**
* 返回连接对象
* @return
* @throws SQLException
*/
public static Connection getConnction() throws SQLException {
return cpds.getConnection();
}
/**
* 返回连接池对象
* @return
*/
public static ComboPooledDataSource getDataSource(){
return cpds;
}
}
实体类 Stu.java
public class Stu {
private int id;
private String username;
private String password;
private int age;
public Stu(){
}
public Stu(int id,String username,String password,int age){
this.id=id;
this.username=username;
this.password=password;
this.age=age;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
@Override
public String toString() {
return "Stu{" +
"id=" + id +
", username='" + username + '\'' +
", password='" + password + '\'' +
", age=" + age +
'}';
}
}
测试类
public class Demo1 {
@Test
public void func1() throws InterruptedException, SQLException {
Stu stu=new Stu(1,"张三","123456",22);
//增
//insert(stu);
//改
//update(stu);
//删
//delete(stu.getId());
//查
query(stu.getId());
}
//添加
public void insert(Stu stu) {
Connection cn = null;
PreparedStatement ps = null;
try {
//获得连接对象
cn = JDBCUtils.getConnction();
//创建sql模板
String sql = "insert into student values(?,?,?,?)";
//实例化preparedStatement
ps = cn.prepareStatement(sql);
//设置参数
ps.setInt(1, stu.getId());
ps.setString(2,stu.getUsername());
ps.setString(3,stu.getPassword());
ps.setInt(4,stu.getAge());
//执行
ps.executeUpdate();
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
try {
if (ps != null) ps.close();
if (cn != null) cn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
//改
public void update(Stu stu) {
Connection cn = null;
PreparedStatement ps = null;
try {
//获得连接对象
cn = JDBCUtils.getConnction();
//创建sql模板
String sql = "update student set age=? where id=?";
//实例化preparedStatement
ps = cn.prepareStatement(sql);
//设置参数
ps.setInt(1,stu.getAge());
ps.setInt(2, stu.getId());
//执行
ps.executeUpdate();
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
try {
if (ps != null) ps.close();
if (cn != null) cn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
//删
public void delete(int stuid) {
Connection cn = null;
PreparedStatement ps = null;
try {
//获得连接对象
cn = JDBCUtils.getConnction();
//创建sql模板
String sql = "delete from student where id=?";
//实例化preparedStatement
ps = cn.prepareStatement(sql);
//设置参数
ps.setInt(1, stuid);
//执行
ps.executeUpdate();
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
try {
if (ps != null) ps.close();
if (cn != null) cn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
//查
public Stu query(int stuid) {
Connection cn = null;
PreparedStatement ps = null;
ResultSet rs=null;
try {
//获得连接对象
cn = JDBCUtils.getConnction();
//创建sql模板
String sql = "select * from student where id=?";
//实例化preparedStatement
ps = cn.prepareStatement(sql);
//设置参数
ps.setInt(1, stuid);
//执行
rs=ps.executeQuery();
if(!rs.next()) return null;
Stu stu=new Stu();
stu.setId(rs.getInt("id"));
stu.setUsername(rs.getString("username"));
stu.setPassword(rs.getString("password"));
stu.setAge(rs.getInt("age"));
return stu;
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
try {
if (ps != null) ps.close();
if (cn != null) cn.close();
if(rs!=null) rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
上面测试类中的很多代码重复的地方比较多,我们将其中重复性的代码写成一个小工具 QR.java
public class QR<T>{
private DataSource dataSource;
public QR(){
}
public QR(DataSource dataSource){
this.dataSource=dataSource;
}
/**
* 实现增,删,改
* @param sql sql模板
* @param params 参数
*/
public int update(String sql,Object... params) throws SQLException {
Connection cn = null;
PreparedStatement ps = null;
try {
//获得连接对象
cn = dataSource.getConnection();
//实例化preparedStatement
ps = cn.prepareStatement(sql);
//设置参数
initParams(ps,params);
//执行
return ps.executeUpdate();
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
try {
if (ps != null) ps.close();
if (cn != null) cn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public T query(String sql,ResultSetHandler<T> rh,Object... params) throws SQLException {
Connection cn = null;
PreparedStatement ps = null;
ResultSet rs=null;
try {
//获得连接对象
cn = dataSource.getConnection();
//实例化preparedStatement
ps = cn.prepareStatement(sql);
//设置参数
initParams(ps,params);
//执行
rs=ps.executeQuery();
//返回ResultSetHandler处理方法
return rh.handler(rs);
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
try {
if(rs!=null) rs.close();
if (ps != null) ps.close();
if (cn != null) cn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 初始化参数
* @param ps
* @param params
* @throws SQLException
*/
private void initParams(PreparedStatement ps,Object... params) throws SQLException {
for(int i=0;i<params.length;i++){
ps.setObject(i+1,params[i]);
}
}
}
/**
* 这个接口用来处理查询的结果集
* @param <T>
*/
interface ResultSetHandler<T> {
//抛出实现类会出现的异常
public T handler(ResultSet rs) throws SQLException;
}
对QR.java工具类的测试类 Demo2.java
public class Demo2 {
@Test
public void func1() throws SQLException {
//更新
update();
//查询
query();
}
/**
* 更新:包括增、删、改
* @return
* @throws SQLException
*/
public int update() throws SQLException {
QR qr=new QR(JDBCUtils.getDataSource());
String sql="insert into student values(?,?,?,?)";
Object[] params={1,"张三","123456",20};
return qr.update(sql);
}
/**
* 查询
* @return
* @throws SQLException
*/
public Stu query() throws SQLException {
QR qr=new QR(JDBCUtils.getDataSource());
String sql="select * from student where id=?";
//在这里将结果集封装成Stu类对象
ResultSetHandler<Stu> rh=new ResultSetHandler<Stu>() {
@Override
public Stu handler(ResultSet rs) throws SQLException {
Stu s=new Stu();
if(rs.next()){
s.setId(rs.getInt("id"));
s.setUsername(rs.getString("username"));
s.setPassword(rs.getString("password"));
s.setAge(rs.getInt("age"));
}
return s;
}
};
return (Stu) qr.query(sql,rh,1);
}
}
其实上面的QR工具类的例子就是dbUtils这个工具类的作用
DBUtils简介
- DBUtils是Apache Commons组件中的一员,开源免费!
- DBUtils是对JDBC的简单封装,但是它还是被很多公司使用!
- DBUtils的Jar包:common-dbutils.jar
dbUtils工具类中主要使用QueryRunner对象,QueryRunner包含的方法:
update方法:
- int update(String sql, Object... params) --> 可执行增、删、改语句
- int update(Connection con, String sql, Object... parmas) --> 需要调用者提供Connection,这说明本方法不再管理Connection了。支持事务!
query方法:
- T query(String sql, ResultSetHandler rsh, Object... params) --> 可执行查询:它会先得到ResultSet,然后调用rsh的handle()把rs转换成需要的类型!
- * T query(Connection con, String sql, ResultSetHadler rsh, Object... params),支持事务
ResultSetHandler接口:
- BeanHandler(单行) --> 构造器需要一个Class类型的参数,用来把一行结果转换成指定类型的javaBean对象
- BeanListHandler(多行) --> 构造器也是需要一个Class类型的参数,用来把一行结果集转换成一个javabean,那么多行就是转换成List对象,一堆javabean
- MapHandler(单行) --> 把一行结果集转换Map
一行记录: sid sname age gender
1001 zs 99 male
一个Map:{sid:1001, sname:zs, age:99, gender:male}
- MapListHandler(多行) --> 把一行记录转换成一个Map,多行就是多个Map,即List<Map>!
- ScalarHandler(单行单列) --> 通常用与select count(*) from t_stu语句!结果集是单行单列的!它返回一个Object
测试代码 Demo3.java
public class Demo3 {
//@Test
public void func1() throws SQLException {
QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
String sql="insert into student values(?,?,?,?)";
Object[] params={2,"李四","123456",24};
//插入
qr.update(sql,params);
}
@Test
/**
* BeanHandler 查询
* 结果:Stu{id=1, username='张三', password='123456', age=22}
* @throws SQLException
*/
public void func2() throws SQLException {
QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
String sql="select * from student where id=?";
Object[] params={1};
ResultSetHandler<Stu> rh=new ResultSetHandler<Stu>() {
@Override
public Stu handle(ResultSet rs) throws SQLException {
Stu s=new Stu();
if(rs.next()){
s.setId(rs.getInt("id"));
s.setUsername(rs.getString("username"));
s.setPassword(rs.getString("password"));
s.setAge(rs.getInt("age"));
}
return s;
}
};
//query第二个参数和我们自定义的ReusltSetHandler是一样的
//这里使用dbutils提供的BeanHandler可以将查询的结果集封装到给定的类中并返回
//但是需要保证一条就是查询的结果集(也就是数据库中的属性和给定的类中元素的属性名称一致)
Stu stu=qr.query(sql,new BeanHandler<>(Stu.class),params);
System.out.println(stu.toString());
}
@Test
/**
* BeanListHandler 查询
* 结果[Stu{id=1, username='张三', password='123456', age=22}, Stu{id=2, username='李四', password='123456', age=24}]
*/
public void func3() throws SQLException {
QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
String sql="select * from student";
List<Stu> list=qr.query(sql,new BeanListHandler<>(Stu.class));
System.out.println(list);
}
@Test
/**
* MapHandler查询
* 结果{password=123456, id=1, age=22, username=张三}
*/
public void func4() throws SQLException {
QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
String sql="select * from student where id=?";
Object[] params={1};
Map<String,Object> map =qr.query(sql,new MapHandler(),params);
System.out.println(map);
}
@Test
/**
*
* MapListHandler 查询
* 结果:[{password=123456, id=1, age=22, username=张三}, {password=123456, id=2, age=24, username=李四}]
*/
public void func5() throws SQLException {
QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
String sql="select * from student";
List<Map<String,Object>> map =qr.query(sql,new MapListHandler());
System.out.println(map);
}
@Test
/**
* ScalarHandler
* 结果:2
*/
public void func6() throws SQLException {
QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
String sql="select count(*) from student";
/**
* new ScalarHandler()返回的是一个是一个Object
* select count(*) from student 返回的可以是 Integer\Long\BigInteger(不同的数据库可能不同)
* 好在Integer\Long\BigInteger三者有共同的父类:Number
*/
Number num= (Number) qr.query(sql,new ScalarHandler());
//可以根据需要转换成指定的类型
System.out.println(num);
}
}