1、DBUtils是Apache Commons组件的一员,开源免费,是对JDBC的简单封装,但还是被很多公司使用!!
2、DBUtils的下载:首先要下载jar包,下载地址下载地址
3、DBUtils中的常用类:
(1)、DbUtils:里面有一系列的关闭资源的静态方法:
(2)、QueryRunner:常用的方法有update(用于增删改操作),query(用于查询),batch(用于批处理)等方法;
4、QueryRunner的用法:
QueryRunner qr = new QueryRunner();
qr.xxxx();
5、我们看到query方法的最后一个参数是一个ResultSetHandler接口,它的作用是把ResultSet转化成相应的目标类型,它常用的实现类有以下几个:
(1)MapHandler:单行处理器,把结果集转化成一个Map<String colName,Object obj>类型的对象(键是字段名,值是字段值);
(2)MapListHandler:多行处理器,把结果集转换成List<Map<String colName,Object obj>>类型的数据!
(3)BeanHandler:单行处理器,提供一个JavaBean的Class类型参数,把结果集转化成JavaBean对象;
(4)ColumnListHandler:多行单列处理器,把结果集转化成List类型的对象;
(5)BeanListHandler:多行多列处理器,把结果集封装到List对象中;
(6)ScalarHandler:单行单列处理器,把结果集转换成Object类型的对象!
代码:
实体类:
Student.java:
package cn.melo.entity;
public class Student {
private Integer id;
private String name;
private Integer age;
public Student() {
super();
}
public Student(Integer id, String name, Integer age) {
super();
this.id = id;
this.name = name;
this.age = age;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
@Override
public String toString() {
return "Student [id=" + id + ", name=" + name + ", age=" + age + "]";
}
}
测试类:
Demo.java:
package cn.melo.demo;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.jupiter.api.Test;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import cn.melo.entity.Student;
public class Demo {
private static Connection conn = null;
static{
ComboPooledDataSource cpds = new ComboPooledDataSource();
try {
conn = cpds.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
}
//测试添加
@Test
public void testInsert() throws SQLException {
//得到QueryRunner对象
QueryRunner qr = new QueryRunner();
//写出sql
String sql = "INSERT INTO student VALUES(?,?,?)";
//执行
int row = qr.update(conn, sql, 5,"Davis",15);
System.out.println(row);
DbUtils.close(conn);
}
//测试修改
@Test
public void testUpdate() throws SQLException {
//得到QueryRunner对象
QueryRunner qr = new QueryRunner();
//写出sql
String sql = "UPDATE student SET name=? WHERE id=?";
//执行
int row = qr.update(conn,sql,"戴维斯",5);
System.out.println(row);
DbUtils.close(conn);
}
// 测试修改
@Test
public void testDelete() throws SQLException {
// 得到QueryRunner对象
QueryRunner qr = new QueryRunner();
// 写出sql
String sql = "DELETE FROM student WHERE id=?";
// 执行
int row = qr.update(conn, sql, 5);
System.out.println(row);
DbUtils.close(conn);
}
//测试查询1:查询所有数据(多行多列)
@Test
public void testQuery1() throws SQLException {
// 得到QueryRunner对象
QueryRunner qr = new QueryRunner();
// 写出sql
String sql = "SELECT * FROM student";
// 执行
List<Student> list = qr.query(conn, sql, new BeanListHandler<Student>(Student.class));
//遍历集合
for (Student stu : list) {
System.out.println(stu);
}
DbUtils.close(conn);
}
//测试查询2:查询数据的总条数(单行单列)
@Test
public void testQuery2() throws SQLException {
// 得到QueryRunner对象
QueryRunner qr = new QueryRunner();
// 写出sql
String sql = "SELECT COUNT(*) FROM student";
// 执行
Object count = qr.query(conn, sql, new ScalarHandler<Object>());
System.out.println(count);
DbUtils.close(conn);
}
//测试查询3:查询id为1的学生的所有信息(单行多列)
@Test
public void testQuery3() throws SQLException {
// 得到QueryRunner对象
QueryRunner qr = new QueryRunner();
// 写出sql
String sql = "SELECT * FROM student WHERE id=?";
// 执行
Map<String, Object> map = qr.query(conn, sql, new MapHandler(),1);
Set<Entry<String, Object>> entrySet = map.entrySet();
for (Entry<String, Object> entry : entrySet) {
System.out.print(entry.getKey()+ ": ");
System.out.print(entry.getValue()+"\t");
}
DbUtils.close(conn);
}
}