回顾
DBUtils来完成增加、删除、修改
1、依赖
mysql数据库驱动、阿里德鲁伊连接池、apache DBUtils API
单元测试Junit
2、实体类
私有属性 get/set属性 构造函数(无参、全参) toString()
注意:
要序列化
3、连接池工具类
提供数据源 javax.sql.DataSource对象
4、使用数据源完成增加、删除、修改操作
QueryRunner.update()
5、DBUtils查询
ResultSetHandler结果集处理类
BeanHandler : 将结果集第一条件封装到指定的javabean.
BeanListHandler : 将结果集每一条封装到指字的javabean,将javabean封装到List
ScalarHandler:单个数据。
package com.ww.jdbc_demo_02.update;
import com.ww.jdbc02.pojo.User;
import com.ww.jdbc02.utils.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.*;
import org.junit.Test;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
public class DBUtilsDemo02 {
//查询所有
@Test
public void selectAllUser() throws SQLException {
String sql="select * from user";
//结果集可能是:没有、1条、N条
QueryRunner queryRunner=new QueryRunner(DruidUtils.getDataSource());
List<User> list=queryRunner.query(sql,new BeanListHandler<>(User.class));
for(User user:list){
System.out.println(user.toString());
}
}
//通过ID查询
@Test
public void selectByIdUser() throws SQLException {
String sql="select * from user where id=?";
//结果集:没有、1条
QueryRunner queryRunner=new QueryRunner(DruidUtils.getDataSource());
User user= (User) queryRunner.query(sql, new BeanHandler(User.class),3);
System.out.println(user);
}
//查询所有--2
@Test
public void selectAllUser02() throws SQLException {
String sql="select * from user";
QueryRunner queryRunner=new QueryRunner(DruidUtils.getDataSource());
List<Map<String,Object>> list=queryRunner.query(sql,new MapListHandler());
for(Map<String,Object> map:list){
System.out.println(map);
}
}
//通过ID查询--2
@Test
public void selectByIdUser02() throws SQLException {
String sql="select * from user where id=?";
QueryRunner queryRunner=new QueryRunner(DruidUtils.getDataSource());
Map map=queryRunner.query(sql, new MapHandler(),3);
System.out.println(map);
}
//统计记录总数
@Test
public void selectCount() throws SQLException {
String sql="select count(*) from user";
QueryRunner queryRunner=new QueryRunner(DruidUtils.getDataSource());
long count=queryRunner.query(sql,new ScalarHandler<>());
System.out.println(count);
}
}