DBUtils
DBUtils
目录
1.DBUtils
DBUtils可以帮助开发者完成数据的封装（结果集到java对象的映射）
1、导入jar包
导入commons-dbutils-1.4.jar
2、ResultHandler 接⼝是⽤来处理结果集，可以将查询到的结果集转换成 Java 对象，提供了 4 种实现类。
BeanHandler 将结果集映射成 Java 对象 Student
BeanListHandler 将结果集映射成 List 集合 List
MapHandler 将结果集映射成 Map 对象
MapListHandler 将结果集映射成 MapList 结合
3、注意：
使用时反射的那个类里面必须有无参构造函数（后面会解释）
类里面的字段，必须和数据库中的一模一样
2.DBUtils使用示例
00.准备工作
000.创建数据库test，创建数据表student
/*
Navicat MySQL Data Transfer
Source Server : localhost_3306
Source Server Version : 80018
Source Host : localhost:3306
Source Database : test
Target Server Type : MYSQL
Target Server Version : 80018
File Encoding : 65001
Date: 2020-11-28 16:14:52
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`id` int(11) NOT NULL,
`name` varchar(255) DEFAULT NULL,
`score` varchar(255) DEFAULT NULL,
`birthday` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('20180001', '空木莲华', '100.0', '2000-03-18');
INSERT INTO `student` VALUES ('20180002', '噬神者', '100.0', '2000-03-18');
INSERT INTO `student` VALUES ('20180003', '德玛西亚', '100.0', '2000-03-18');
INSERT INTO `student` VALUES ('20180005', '猫主子', '100.0', '2000-03-18');
INSERT INTO `student` VALUES ('20180006', '经济舱', '100.0', '2000-03-18');
INSERT INTO `student` VALUES ('20180007', '现实', '100.0', '2000-03-18');
INSERT INTO `student` VALUES ('20180008', '八神', '100.0', '2000-03-18');
INSERT INTO `student` VALUES ('20180010', '时间零', '100.0', '2000-03-18');
INSERT INTO `student` VALUES ('20180011', '十步杀俩人', '100.0', '2000-03-18');
INSERT INTO `student` VALUES ('20183638', '张志流', '100.0', '2000-03-18');
INSERT INTO `student` VALUES ('20183694', '边信哲', '100.0', '2000-03-18');
001.数据库连接池，c3p0-config.xml
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<named-config name="testc3p0">
<!-- 指定连接数据源的基本属性 -->
<property name="user">root</property>
<property name="password">xjmwan1314</property>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai</property>
<!-- 若数据库中连接数不⾜时, ⼀次向数据库服务器申请多少个连接 -->
<property name="acquireIncrement">5</property>
<!-- 初始化数据库连接池时连接的数量 -->
<property name="initialPoolSize">20</property>
<!-- 数据库连接池中的最⼩的数据库连接数 -->
<property name="minPoolSize">2</property>
<!-- 数据库连接池中的最⼤的数据库连接数 -->
<property name="maxPoolSize">40</property>
</named-config>
</c3p0-config>
002.编写实体类student，重写toString()方法，无参构造函数。
package com.wildfire.DBUtils.entity;
public class Student {
private int id;
private String name;
private double score;
private String birthday;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public double getScore() {
return score;
}
public void setScore(double score) {
this.score = score;
}
public String getBirthday() {
return birthday;
}
public void setBirthday(String birthday) {
this.birthday = birthday;
}
public Student(int id, String name, double score, String birthday) {
this.id = id;
this.name = name;
this.score = score;
this.birthday = birthday;
}
public Student(){
}
@Override
public String toString() {
return "Student{" +
"id=" + id +
", name='" + name + '\'' +
", score=" + score +
", birthday='" + birthday + '\'' +
'}';
}
}
01.传统的JDBC完成查询。
完成数据库中id为20180001的学生信息的查询。
package com.wildfire.DBUtils;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.wildfire.DBUtils.entity.Student;
import javax.xml.transform.Result;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class DBUtils {
//测试用主函数
public static void main(String []args){
Student student=findById(20180001);
System.out.println(student);
}
//JDBC通过id查询数据
public static Student findById(int id){
Connection connection=null;
PreparedStatement preparedStatement=null;
ResultSet resultSet=null;
Student student=null;
try {
//加载驱动
ComboPooledDataSource comboPooledDataSource=new ComboPooledDataSource("testc3p0");
//获取连接
connection=comboPooledDataSource.getConnection();
String sql="Select * from student where id=?";
preparedStatement=connection.prepareStatement(sql);
preparedStatement.setInt(1,id);
resultSet=preparedStatement.executeQuery();
while(resultSet.next()){
String name=resultSet.getString(2);
double score=resultSet.getDouble(3);
String birthday=resultSet.getString(4);
student=new Student(id,name,score,birthday);
}
} catch (SQLException e) {
e.printStackTrace();
} finally{
try {
connection.close();
resultSet.close();
preparedStatement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return student;
}
}
查询结果如下：
Student{id=20180001, name='空木莲华', score=100.0, birthday='2000-03-18'}
02.使用DBUtils完成查询。
001.查询一条数据。
完成数据库中id为20180001的学生信息的查询。
package com.wildfire.DBUtils;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.wildfire.DBUtils.entity.Student;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import javax.xml.transform.Result;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class DBUtils {
//测试用主函数
public static void main(String []args){
Student student=findById(20180001);
System.out.println(student);
Student studentClass=findByIdDBUtils(20180001);
System.out.println(studentClass);
}
//JDBC通过id查询数据
public static Student findById(int id){
Connection connection=null;
PreparedStatement preparedStatement=null;
ResultSet resultSet=null;
Student student=null;
try {
//加载驱动
ComboPooledDataSource comboPooledDataSource=new ComboPooledDataSource("testc3p0");
//获取连接
connection=comboPooledDataSource.getConnection();
String sql="Select * from student where id=?";
preparedStatement=connection.prepareStatement(sql);
preparedStatement.setInt(1,id);
resultSet=preparedStatement.executeQuery();
while(resultSet.next()){
String name=resultSet.getString(2);
double score=resultSet.getDouble(3);
String birthday=resultSet.getString(4);
student=new Student(id,name,score,birthday);
}
} catch (SQLException e) {
e.printStackTrace();
} finally{
try {
connection.close();
resultSet.close();
preparedStatement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return student;
}
//使用DBUtils通过id查询数据
public static Student findByIdDBUtils(int id){
Connection connection=null;
Student student=null;
try {
//加载驱动
ComboPooledDataSource comboPooledDataSource=new ComboPooledDataSource("testc3p0");
//获取连接
connection=comboPooledDataSource.getConnection();
String sql="Select * from student where id=?";
QueryRunner queryRunner=new QueryRunner();
//根据Student对象（结构）去封装结果集
//这里也防止了SQL注入，如果没有参数，可以去掉id，因为原方法定义中是可变参数
student=queryRunner.query(connection,sql,new BeanHandler<>(Student.class),id);
} catch (SQLException e) {
e.printStackTrace();
} finally{
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return student;
}
}
查询结果如下：
Student{id=20180001, name='空木莲华', score=100.0, birthday='2000-03-18'}
002.查询全部数据。
package com.wildfire.DBUtils;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.wildfire.DBUtils.entity.Student;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import javax.xml.transform.Result;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class DBUtils {
//测试用主函数
public static void main(String []args){
Student student=findById(20180001);
System.out.println(student);
Student studentClass=findByIdDBUtils(20180001);
System.out.println(studentClass);
List<Student> studentList=findAllDubtils();
System.out.println(studentList);
}
//JDBC通过id查询数据
public static Student findById(int id){
Connection connection=null;
PreparedStatement preparedStatement=null;
ResultSet resultSet=null;
Student student=null;
try {
//加载驱动
ComboPooledDataSource comboPooledDataSource=new ComboPooledDataSource("testc3p0");
//获取连接
connection=comboPooledDataSource.getConnection();
String sql="Select * from student where id=?";
preparedStatement=connection.prepareStatement(sql);
preparedStatement.setInt(1,id);
resultSet=preparedStatement.executeQuery();
while(resultSet.next()){
String name=resultSet.getString(2);
double score=resultSet.getDouble(3);
String birthday=resultSet.getString(4);
student=new Student(id,name,score,birthday);
}
} catch (SQLException e) {
e.printStackTrace();
} finally{
try {
connection.close();
resultSet.close();
preparedStatement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return student;
}
//使用DBUtils通过id查询数据
public static Student findByIdDBUtils(int id){
Connection connection=null;
Student student=null;
try {
//加载驱动
ComboPooledDataSource comboPooledDataSource=new ComboPooledDataSource("testc3p0");
//获取连接
connection=comboPooledDataSource.getConnection();
String sql="Select * from student where id=?";
QueryRunner queryRunner=new QueryRunner();
//根据Student对象（结构）去封装结果集
//这里也防止了SQL注入，如果没有参数，可以去掉id，因为原方法定义中是可变参数
student=queryRunner.query(connection,sql,new BeanHandler<>(Student.class),id);
} catch (SQLException e) {
e.printStackTrace();
} finally{
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return student;
}
public static List<Student> findAllDubtils(){
Connection connection=null;
List<Student>list=new ArrayList<>();
try {
//加载驱动
ComboPooledDataSource comboPooledDataSource=new ComboPooledDataSource("testc3p0");
//获取连接
connection=comboPooledDataSource.getConnection();
String sql="Select * from student";
QueryRunner queryRunner=new QueryRunner();
//根据Student对象（结构）去封装结果集
//这里也防止了SQL注入，如果没有参数，可以去掉id，因为原方法定义中是可变参数
//这里修改了
list=queryRunner.query(connection,sql,new BeanListHandler<>(Student.class));
} catch (SQLException e) {
e.printStackTrace();
} finally{
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return list;
}
}
输出结果如下：
[Student{id=20180001, name='空木莲华', score=100.0, birthday='2000-03-18'}, Student{id=20180002, name='噬神者', score=100.0, birthday='2000-03-18'}, Student{id=20180003, name='德玛西亚', score=100.0, birthday='2000-03-18'}, Student{id=20180005, name='猫主子', score=100.0, birthday='2000-03-18'}, Student{id=20180006, name='经济舱', score=100.0, birthday='2000-03-18'}, Student{id=20180007, name='现实', score=100.0, birthday='2000-03-18'}, Student{id=20180008, name='八神', score=100.0, birthday='2000-03-18'}, Student{id=20180010, name='时间零', score=100.0, birthday='2000-03-18'}, Student{id=20180011, name='十步杀俩人', score=100.0, birthday='2000-03-18'}, Student{id=20183638, name='张志流', score=100.0, birthday='2000-03-18'}, Student{id=20183694, name='边信哲', score=100.0, birthday='2000-03-18'}]
主要改变的地方是：
list=queryRunner.query(connection,sql,new BeanListHandler<>(Student.class));
003.为什么实体类中必须有无参构造函数？
student=queryRunner.query(connection,sql,new BeanHandler<>(Student.class),id);
返回的是一个Student类型的对象，BeanHandler<>(Student.class)会根据你传入的类型，通过反射机制找到类的无参构造，调用无参构造创建对象，然后把结果集中的数据赋给对象，然后把对象返回。
004.相应的其他两种方式
主要改变的地方是
Map<String,Object> map=queryRunner.query(connection,sql,new MapHandler());
List<Map<String,Object>>list=queryRunner.query(connection,sql,new MapListHandler());
03.总结
使用这种方法获得的结果集是一定的，得到的值不同取决于你用什么类去反射，用什么类型来接收结果集。
