Java必学的MySQL知识
MySQL
1.数据库
database就是一个存储数据的仓库，为了方便数据的存储和管理，它将数据按照特定的规律存储在电脑的硬盘上，通过数据库管理系统，可以有效地组织和管理存储在数据库中的数据。
数据库分为两大类：
- 关系型数据库：MySQL、Oracle、SQLServer 重量级 业务型数据
- 非关系型数据库（NoSQL）：Redis、MongoDB 轻量级 常用的热点的数据，加快加载速度，如热销榜
MySQL + Redis
使用数据库，需要完成两件事情：
- 保存数据（最基础的要求）
- 管理数据（增删改查）
数据库管理系统DBMS（DataBase Management System）
数据库存储引擎：
存储引擎其实就说如何存储数据、如何为存储的数据建立索引、如何更新、查询数据等技术的实现方法。关系型数据库中数据是以表的形式存储的，所有存储引擎也可以称为表类型（存储和操作此表的类型）
DataGrip、IDEA自带、Navicat
查看数据库存储引擎：
show engines;
MySQL默认的存储引擎是
如何使用数据库？
1、通过SQL语言来管理数据库，SQL语言也是一种编程语言，专门来处理数据库
SQL的分类：
- DML（数据操作语言）操作数据库中包含的数据（insert、、delete）
- DDL（数据定义语言）创建、删除、修改数据库、数据表（create、drop、alter）
- DQL（数据查询语言）对数据库中的数据进行查询（select）
- DCL（数据控制语言）用来控制数据库组件的存取（事务）（commit、rollback）
学习数据库，需要掌握两部分内容
1、掌握数据库的使用（DataGrip可视化管理工具，SQL语句）
2、设计数据库：根据项目的需求，设计数据表和数据表之间的关联关系
一个数据库由多张数据表组成，数据是保存在数据表中的
数据表由两部分组成
1、表结构：表的设计
2、表数据：表中存储的数据
创建数据库
create database metest1 default character set utf8 collate utf8_general_ci;
create database metest2 default character set utf8 collate utf8_bin;
metest1：校验字符集是utf8_general_ci：查询数据时排列顺序不区分大小写
metest2：校验字符集是utf8_bin：查询数据时排列顺序区分大小写
建表
use metest1;
create table user(
name varchar(100)
);
插入
use metest1;
insert into user(name) values('a');
insert into user(name) values('b');
查询
use metest2;
select * from user order by name;
MySQL中的数据类型
整数类型：tinyint（1 个字节 byte）、smallint（2 个字节）、mediumint（3 个字节）、int（4 个字节）、bigint（8 个字节）
浮点类型：float（4 个字节）、double（8 个字节）
定点类型：decimal(M,D) M 数字的最大值（精度）D 小数点后数字的长度（标度）
日期和时间类型：year（1个字节）取值范围：1901-2155
time（3个字节）取值范围：-838:59:59 - 838:59:59
date（3个字节）取值范围：1000-01-01 - 9999-12-13
datetime（8个字节）取值范围：1000-01-01 00:00:00 - 9999-12-13 23:59:59
timestamp（4个字节）表示1970年1月1号到现在的毫秒数
字符串类型：char（4个字节）、varchar（8个字节）
二进制类型（音频、视频）：bit（1/8个字节）、binary（2个字节）、varbinary（8个字节）、tinyblob（255个字节）、blob（2的16次方-1个字节）、mediumblob（2的24次方-1个字节）、longblob（2的32次方-1个字节）
2 数据库基本操作
create database 数据库名称 default character set utf8 collate utf8_general_ci
删除数据库
drop database 数据库
查看数据库
show databases
选中数据库
use 数据库名称
创建数据表
create table 表名(
字段列表
)
use test;
create table user(
id int primary key auto_increment,
name varchar(100),
age int
);
主键(PRI)自增，主键是不能重复的，确保主键的值必须唯一。
主键自增，每添加一行数据，主键的值自动加一。
id int primary key auto_increment,
删除数据表
use 数据库;
drop table 表名;
查看所有表名
use 数据库;
show tables;
查看表结构
use 数据库;
desc 表名;
修改表的结构
use 数据库;
alter table 表名 add 字段名 字段数据类型;
修改一个字段
use 数据库;
alter table 表名 change 老字段名 新字段名 新字段数据类型;
删除一个字段
use 数据库;
alter table 表名 drop 字段名;
3 SQL函数
数据库在运行的时候会消耗很多资源，所以逻辑处理都放在后端。
1、数学函数
abs() 求绝对值
select abs(score) from student;
floor() 返回小于参数的最大整数
select floor(score) from user where id = 1;
ceil() 返回大于参数的最大整数
2、字符串函数
s1 中 index 位置开始，长度为 len 的字符替换成 s2，index 从 1 开始
select insert(name,1,2,'小红') from student;
upper()、ucase() 将字母变为大写
select ucase(name) from student;
select upper(name) from student;
lower()、lcase() 将字母变为小写
select lower(name) from student;
select lcase(name) from student;
left(s,len) 返回 s 字符串的前 len 个字符
select left(name,1) from student;
right(s,len) 返回 s 字符串的后 len 个字符
select right(name,2) from student;
substring(s,index,len) 截取 s 字符串，从 index 位置开始，长度为 len
select substring(name,2,1) from student;
reverse() 反序输出
select reverse(name) from student;
3、日期函数
curdate() current_date() 获取当前日期
select current_date();
curtime() current_time() 获取当前时间
select current_time() from student;
now() 获取当前日期+时间
select now();
datediff(d1,d2) d1 和 d2 之间相隔的天数
select datediff('2024-05-01','2021-06-23');
adddate(d,n) 返回 d 日期之后 n 天的日期
select adddate('2024-05-01',1000);
subdate(d,n) 返回 d 日期之前 n 天的日期
select subdate('2024-05-01',1000);
4、聚合函数
count() 根据某个字段统计数量
select count(id) from student;
sum() 计算某个字段值的总和
select sum(score) from student;
avg() 求某个字段值的平均值
select avg(score) from student;
max() 求某个字段的最大值
select max(score) from student;
min() 求某个字段的最小值
select min(score) from student;
5、MySQL运算符
执行运算符：加减乘除
select score + 10 from student where id = 1;
select score - 10 from student where id = 1;
select score * 10 from student where id = 1;
select score / 10 from student where id = 1;
比较运算符：大于、等于、小于、不等于
select score > 10 from student where id = 1;
select score < 10 from student where id = 1;
select score = 10 from student where id = 1;
select score != 10 from student where id = 1;
逻辑运算符：与 或 非
select score != 10 && score < 10 from student where id = 1;
select score != 10 || score < 10 from student where id = 1;
select !(score != 10 || score < 10) from student where id = 1;
2、特殊运算符
is null 判断值是否为空
select name is null from student where id = 2;
between and 判断值是否在某个区间之内
select score between 15 and 20 from student where id = 2;
in 判断值是否在某个特定的集合内
select score in(1,2,3) from student ;
like 模糊查询
select name from user where name like '%李%';
4 表设计
主键：表中的一个字段，该字段的值是每一行记录的唯一标识。
默认情况下，每张表都要有一个主键，一张表只能有一个主键，所谓的一张表多个主键，是指联合主键：用多个字段一起作为一张表的主键
主键生成策略：代理主键，主键的选择与业务无关，仅仅是用来标识一行数据，一般将主键的数据类型定义为int，因为int类型的存储空间较小。
数据索引：索引是一种特殊的数据库结构，可以用来快速查询数据库表中的特定记录，索引是提高数据库性能的重要方式，所有的字段都可以添加索引。
索引包括：普通索引、唯一性索引、全文索引、单列索引、多列索引、空间索引
使用索引可以提升检索数据的速度，但是创建索引和维护索引需要耗费时间，索引需要占用物理空间
普通索引：不需要任何限制条件的索引，可以在任意数据类型上创建
唯一索引：索引的值必须唯一，比如主键索引
全文索引：只能创建在char、varchar、text文本类型的字段上，查询数据量较大的字符串类型的字段时，使用全文索引可以提高查询速度，InnoDB存储索引不支持全文索引。
单列索引：只对应一个字段的索引
多列索引：在一张表的多个字段上创建一个索引，查询时使用第一个字段，即可触发该索引
空间索引：只能建立在空间数据类型上（GIS,经纬度），InnoDB存储引擎不支持该索引
索引是用来做快速排序的
1、主键自带索引
2、可以给其他字段手段添加索引，手动删除索引
索引的设计原则：
1、出现在where语句中的列，不是select后面要查询的列
2、索引的值，尽量唯一、效率更高
3、不要添加过多的索引，维护成本很高
添加索引
alter table 表名 add index 索引名（字段）
alter table user add index in_name(name);
create index 索引名 on 表名（字段名）
create index in_name on user(name);
删除索引
alter table 表名 drop index 索引名;
alter table user drop index in_name;
drop index 索引名 on 表名
drop index in_name on user;
5 表之间的关系
数据表之间的关系，数据表之间的关系有3种：
1、一对一关系
2、一对多关系
3、多对多关系
一对一：A表中的一条数据只能对应B表中的一条数据，B表中的一条数据只能对应A表中的一条数据
一对多：A表中的一条数据可以对应B表中的多条数据，B表中的一条数据只能对应A表中的一条数据
多对多：A表中的一条数据可以对应B表中的多条数据，B表中的一条数据可以对应A表中的多条数据
一对多的数据查询：
use testdb;
select * from student , class where id = 1;
#为什么报错？ 两张表都有id，id混淆
[23000][1052] Column 'id' in where clause is ambiguous
#一起查
select * from student , class where class.id= 3 and student.cid=class.id;
#起别名1
select * from student as s ,class as c where s.id = 3 and s.cid = c.id;
#起别名2
select * from student s ,class c where s.id = 1 and s.cid = c.id;
#q取名字
select s.name,c.name from student s ,class c where s.id = 1 and s.cid = c.id;
select s.name as studentname,c.name as classname from student s
,class c where s.id = 1 and s.cid = c.id;
select c.name classname,s.name studentname from class c
,student s where c.id=1 and c.id=s.cid;
多对多的数据查询：要把中间表带进去
select * from account a, course c,account_course ac where a.id=1 and a.id =
ac.aid and c.id=ac.cid;
select a.name accountname,c.name coursename from account a, course c,account_course
ac where a.id=1 and a.id = ac.aid and c.id=ac.cid;
#谁选了Java这门课程
select course.name,account.name from course,account,account_course where course.id
=1 and account.id=account_course.aid and course.id = account_course.cid;
笛卡尔积：查了两张表，乘积组合
6 事务
将多条SQL作为一个整体，要么全部执行，要么一条都不执行
事务有4个特性：
- 原子性：多条SQL语句是一个整体，不可再分割
- 一致性：SQL语句执行前后，数据库数据的值保持一致
- 隔离性：一个事务的执行不能被其他事务所干扰
- 持久性：一个事务一旦提交，数据库中数据的改变是永久性的
#张三借给李三500块钱
user set money = 500 where id=1;
user set money = 1500 where id=2;
#1、开启事务
start transaction;
#2、回滚
rollback;
#3、提交
commit;
7 视图
数据库中一张虚拟的表，允许不同用户或者应用程序以不同的方式查看同一张表中的数据。
需求：有的用户可以看到薪资，有的用户不能看到薪资
1、创建两张表，一张表有薪资字段，另一张表没有薪资字段，效率较低
2、基于同一张表创建两个不同的视图，一个视图中有薪资，另一个视图中没有薪资
创建视图
create view view_common as select id,name,age,score from user;
create view view_all as select * from user;
使用视图
select * from view_common;
select * from view_all;
删除视图
drop view view_common;
8 触发器
触发器定义了一系列的操作，可以在对指定表进行插入，更新，删除操作的同时自动执行这些操作，一般在完成数据同步时使用。
触发器的优点：
- 开发更快，因为触发器存储在数据库中的，所以不必编写每个触发器在应用程序中执行的操作
- 更容易维护，定义触发器之后，访问目标表，会自动调用触发器
- 业务全局实现，如果修改业务，只需要修改触发器即可，不需要修改业务代码
触发器的分类：
- 前触发器：在更新或插入操作前运行
- 后触发器：在更新、插入、删除后运行
- before delete 触发器：在删除之前运行
- instea of 触发器：对复杂试图执行插入、更新、删除时运行
触发器的使用，先创建两张表
#新增的触发器
#在tab1添加数据同步到tab2
use testdb;
create table tab1(
tab2_id varchar(11)
)
create table tab2(
tab2_id varchar(11)
)
create trigger t_afterinsert_on_tab1
after insert on tab1
for each row
begin
insert into tab2(tab2_id) values(new.tab1_id);
end;
insert into tab1(tab1_id) values('num1');
删除触发器
drop trigger t_afterinsert_on_tab1;
删除的触发器
#删除的触发器
create trigger t_after_delete_on_tab1
after delete on tab1
for each row
begin
delete from tab2 where tab2_id = old.tab1_id;
end;
delete from tab1 where tab1_id= 'num1';
9 存储过程
存储过程是一组为了完成特定功能的SQL语句的集合，经过编译之后存储在数据库中，用户通过指定存储过程的名称并给出参数来执行。
一次编写，多次调用，避免开发人员重复编写相同的SQL，存储过程是在数据库存储和执行的，可以减少客户端和服务器之间的数据传输，提高效率。
优点：
1、模块化程序设计，只需要创建一次存储过程，以后就可以在程序中调用该存储过程任意次。
2、执行速度更快，如果某操作需要执行大量SQL语句或重复执行，存储过程比SQL语句执行的更快。
3、更好的安全机制，对于没有权限执行存储过程的用户，也可以授权执行存储过程。
创建存储过程的基本形式：
create procedure 存储过程名称（参数列表）
存储过程的参数由三部分组成：输入输出类型、参数名称、参数类型
输入输出类型：in表示入参，out表示出参，参数类型就是MySQL数据库的任意数据类型
入参的存储过程：
#target=1,添加一个MySQL，否则添加一个Java
create procedure add_name(in target int)
begin
declare name varchar(20);
if target = 1 then
set name = 'MySQL';
else
set name = 'Java';
end if;
insert into user(name) values (name);
end;
调用
call add_name(1);
删除
drop procedure add_name;
出参的存储过程：
#计算从1加到100
create procedure example_while(out sum int)
begin
declare i int default 1;
declare s int default 0;
while i <= 100
do
set s = s + i;
set i = i + 1;
end while;
set sum = s;
end;
call example_while(@sum);
select @sum;
删除
drop procedure example_while;
流程控制语句 ：
if：
use testdb;
#根据不同的参数查询不同的字段
create procedure example_if(in x int)
begin
if x = 1 then
select name from user;
else
if
x = 2 then
select id from user;
end if;
end if;
end;
case：
create procedure example_case(in x int)
begin
case x
when 1 then select name from user;
when 2 then select id from user;
else select age from user;
end case;
end;
drop procedure example_case;
drop procedure example_if;
10 函数
函数和存储过程语法、功能类似，区别：函数必须有返回值，参数只能是in，存储过程不一定有返回值，参数可以是in/out，了解即可。
delimiter $$ -- 临时修改分隔符
create function name_of_user(user_uid int)
returns varchar(11)
reads sql data -- 关键修复：声明函数只读取数据
begin
return (select name from `user` where id = user_uid limit 1);
end$$
delimiter ; -- 恢复分隔符
调用
select name_of_user(1);
删除
drop function name_of_user;
11 JDBC
使用JDBC来访问数据库
Java Database Connectivity是一个独立于特定数据库管理系统，通用的SQL数据库存储和操作的公共接口，定义了一组标准，为访问不同的数据库提供了统一的途径。
JDBC接口包含两个层面：
1、面向应用的API，供开发人员调用
2、面向数据库的API，供开发商开发数据库驱动程序
-
JDBC API
-
供程序员调用的接口和类
-
DriverManager 类
-
Connection 接口
-
Statement 接口
-
ResultSet 接口
-
-
DriverManager
- 管理各种不同的JDBC驱动
-
JDBC驱动
- 负责连接各种不同的数据库
使用JDBC的原理：
1、加载数据驱动，Java程序和MySQL的桥梁
2、获取Connection，一次连接
3、Statement，由Connection产生，执行SQL语句
4、ResultSet，保存Statement执行后所产生的结果
常见的错误
Access denied for user 'root1'@'localhost' (using password: YES)
Access denied for user 'root'@'localhost' (using password: YES)
java.sql.SQLSyntaxErrorException: Unknown database 'tes2tdb'
数据库连接驱动依赖：mysql-connector-java
<dependencies>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.33</version> <!-- 可更新至最新版本 -->
<scope>runtime</scope> <!-- 通常只需运行时生效 -->
</dependency>
</dependencies>
12 数据库连接池
Java 通过 JDBC 访问数据库
数据库连接池
传统的 JDBC 开发模式
1、建立数据库连接
2、执行 SQL 语句
3、断开数据库连接
普通的 JDBC 数据库连接使用 DriverManager 来获取，每次都需要向数据库申请获取连接，验证用户名和密码。
执行完 SQL 之后断开连接，这样会消耗大量的资源和时间，数据库连接资源没有得到很好的重复利用。
为了解决这个问题，可以使用数据库连接池来解决。
数据库连接池的基本思想就是为数据库建立一个缓冲池，预先向缓冲池中放入一定数量的连接，当需要获取数据库连接对象时，只需要从缓冲池中取出一个，用完之后再放回去，供下一个请求使用，做到资源的重复利用。
允许应用程序重复使用一个现有的数据库连接对象，而不是每次都重新创建一个新的连接对象。
数据库连接池在初始化的时候会创建一定数量的数据库连接对象放入到连接池中。
当数据库连接池中没有空闲的连接时，请求会进入等待队列，等待其他线程释放连接。
JDBC 的数据库连接池使用 javax.sql.DataSource 来完成，DataSource 是一个接口，Java 官方提供的，C3P0 是一个第三方的实现，实际开发中使用 C3P0 作为数据库连接池。
使用步骤：
1、导入 jar 包
2、创建 C3P0 对象
package com.ping;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import java.sql.Connection;
import java.sql.Statement;
public class Test {
public static void main(String[] args) {
try {
ComboPooledDataSource dataSource = new ComboPooledDataSource();
//1、设置数据库驱动
dataSource.setDriverClass("com.mysql.cj.jdbc.Driver");
//2、设置URL
dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/testdb");
//3、设置用户名和密码
dataSource.setUser("root");
dataSource.setPassword("123456");
//4、设置连接池的参数
dataSource.setInitialPoolSize(20); //连接池初始化大小
dataSource.setMaxPoolSize(40); //连接池最大连接数
dataSource.setMinPoolSize(2); //连接池最小连接数
dataSource.setAcquireIncrement(5); //连接池每次增加的连接数,扩容
Connection connection = dataSource.getConnection();
String sql = "insert into user(name,age,score,money) values" +
"('user2',23,100,1000)";
Statement statement = connection.createStatement();
int i = statement.execute(sql);
System.out.println(i);
} catch (Exception e) {
e.printStackTrace();
}
}
}
所以数据库连接池就是为了提高连接效率，传统的 JDBC 开发模式是创建一个，用完就销毁。
13 DBUtils 工具
手写一个DBUtils，把之前学的技术全部应用起来。
JDBC 流程：
1、获取 Connection 连接对象
2、创建 Statement，执行 SQL
3、用 ResultSet 接收结果集，进行解析
DBUtils 工具其实就是基于JDBC把某些工作简化了，并不是JDBC的工作没有了，而是DBUtils帮JDBC去做了。
package com.ping;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.ping.com.entity.User;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
public class Test_DBUtils {
public static void main(String[] args) {
try {
ComboPooledDataSource dataSource = new ComboPooledDataSource();
//1、设置数据库驱动
dataSource.setDriverClass("com.mysql.cj.jdbc.Driver");
//2、设置URL
dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/testdb");
//3、设置用户名和密码
dataSource.setUser("root");
dataSource.setPassword("123456");
//4、设置连接池的参数
dataSource.setInitialPoolSize(20); //连接池初始化大小
dataSource.setMaxPoolSize(40); //连接池最大连接数
dataSource.setMinPoolSize(2); //连接池最小连接数
dataSource.setAcquireIncrement(5); //连接池每次增加的连接数,扩容
Connection connection = dataSource.getConnection();
String sql = "select * from user";
//Statement，由Connection产生，执行SQL语句
Statement statement = connection.createStatement();
//ResultSet，保存Statement执行后所产生的结果
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()) { //要一个一个去解析
Integer id = resultSet.getInt("id");
String name = resultSet.getString("name");
Integer age = resultSet.getInt("age");
Integer score = resultSet.getInt("score");
Integer money = resultSet.getInt("money");
System.out.println("id:" + id + " name:" + name + " age:" + age
+ " score:" + score + " money:" + money);
}
} catch (Exception e) {
= e.printStackTrace();
}
}
}
创建一个User实体：
package com.ping.com.entity;
public class User {
private Integer id;
private String name;
private Integer age;
private Integer score;
private Integer money;
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;
}
public Integer getScore() {
return score;
}
public void setScore(Integer score) {
this.score = score;
}
public Integer getMoney() {
return money;
}
public void setMoney(Integer money) {
this.money = money;
}
public User(Integer id, String name, Integer age, Integer score, Integer money) {
//注意要按顺序选中
this.id = id;
this.name = name;
this.age = age;
this.score = score;
this.money = money;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", name='" + name + '\'' +
", age=" + age +
", score=" + score +
", money=" + money +
'}';
}
}
数据表的数据转换成集合了，但是我们转换要写很多代码，现在我们想要简化一下代码：
简化前:Test_DBUtils类代码：
List <User> list = new ArrayList<>();
while (resultSet.next()) { //要一个一个去解析
Integer id = resultSet.getInt("id");
String name = resultSet.getString("name");
Integer age = resultSet.getInt("age");
Integer score = resultSet.getInt("score");
Integer money = resultSet.getInt("money");
User user = new User(id, name, age, score, money);
list.add(user);
}
for(User user : list) {
System.out.println(user);
}
简化后:
//DBUtils
QueryRunner queryRunner = new QueryRunner();
List<User> list = queryRunner.query(connection, sql,
new BeanListHandler<User>(User.class));
for (User user : list) {
System.out.println(user);
}
使用 DBUtils 进行简化
ResultSetHandler：其实就是一个结果集的处理器，专门用来处理 ResultSet，将 SQL 查询的结果转换成 Java 对象。
public interface ResultSetHandler<T> {
T handle(ResultSet var1) throws SQLException;
}
返回的是一个T，T是什么？
泛型，因为SQL查询的结果有很多不同的类型，具体转成什么对象是不知道的，所有用泛型处理。
ResultSetHandler是一个接口，真正的实例化肯定用实现类。
主要使用两个实现类：
- BeanHandler：将 SQL 查询结果转换为一个 Java 对象
- BeanListHandler：将 SQL 查询结果转换为装载了多个 Java 对象的 List 集合
String sql = "select * from user";
//DBUtils
QueryRunner queryRunner = new QueryRunner();
List<User> list = queryRunner.query(connection, sql,
new BeanListHandler<User>(User.class));
for (User user : list) {
System.out.println(user);
}
String sql2 = "select * from user where id=?";
User user = queryRunner.query(connection, sql2,
new BeanHandler<User>(User.class),19);
System.out.println("********************************************");
System.out.println(user);
可以很快的把数据查出来，会自动创建一个Statement对象把sql执行了，然后封装好集合。
使用 DBUtils 的时候需要注意，Java 类必须和数据表对应，Java 类的属性和表中的字段一一对应，Java 类必须有无参构造函数，否则无法创建对象。
底层实现用到了什么机制把结果集输出为Java对象？
梳理一下逻辑，先调query，在query里面去使用传进来的Connection和SQL去执行，获取一个resultSet，最后再把resultSet传给我们的handler，最终在handler把查询结果转换成java对象
手写DBUtils
1、创建MyqueryRunner类
2、创建MyBeanHandler类实现ResultSetHandler接口
3、创建MyBeanListHandler类实现ResultSetHandler接口，练习作业，自己动手写。
MyqueryRunner类
package com.dbutils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
public class MyQueryRunner{
public Object query(Connection connection,String sql, MyBeanHandler handler) {
try {
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery(sql);
return handler.handle(resultSet);//resultSet传递给下一层BeanListHandler去处理
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
}
MyBeanHandler类
package com.dbutils;
import org.apache.commons.dbutils.ResultSetHandler;
import java.lang.reflect.Constructor;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.ResultSet;
import java.sql.SQLException;
public class MyBeanHandler<T> implements ResultSetHandler {
private Class clazz;
public MyBeanHandler(Class clazz) {
this.clazz = clazz;
}
@Override
public T handle(ResultSet resultSet) throws SQLException {
//创建Java对象
Object object = null;
try {
Constructor constructor = clazz.getConstructor(null);
object = constructor.newInstance(null);
//给对象的属性赋值，类型要一致
//从结果集里去取数据
if (resultSet.next()) {
//获取实体类的字段
Field[] fields = clazz.getDeclaredFields();//getFields拿的是实体类里面的公有属性
for (Field field : fields) {
String fieldName = field.getName();
String TypeName = field.getType().getName();
Object value = null;
switch (TypeName) {
case "java.lang.Integer":
value = resultSet.getInt(fieldName);
break;
case "java.lang.String":
value = resultSet.getString(fieldName);
break;
}
//赋值
String methodName = "set" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
//System.out.println(methodName);
Method method = clazz.getMethod(methodName, field.getType());
method.invoke(object, value);
}
}
} catch (Exception e) {
e.printStackTrace();
}
return (T) object; //强转成泛型
}
}
所以这个工具类就是结合JDBC和反射去实现的。
保持代码结构清晰：
- 查询操作(GET) → doGet()
- 修改操作(POST) → doPost()
14 手写DBUtils进阶
PreparedStatement
String sql = "select * from user where id=? and age =? and name=?";
PreparedStatement statement = connection.prepareStatement(sql);
statement.setInt(1, 6);//第一个？号；需要操作的值
statement.setInt(2, 23);
statement.setString(3,"小张");
ResultSet resultSet = statement.executeQuery();
if(resultSet.next()){
int id = resultSet.getInt(1);
String name = resultSet.getString(2);
int age = resultSet.getInt(3);
int score = resultSet.getInt(4);
int money = resultSet.getInt(5);
System.out.println(id + " "+ name +" "+ age +" "+ score +" "+ money);
}
MyQueryRunner类：
传 where id=？怎么实现？
传入参数 Object param ,Object就是多态，既可以是int也可以是String，一个变量是可以有多个类型的，变量有父类，父类的父类还有父类。
PreparedStatement，如何判定传入的是 setInt 还是 setString ?
可以通过instanceof判断
Object num = 1;
System.out.println(num instanceof Integer);
System.out.println(num instanceof String);
output:
true
false
//判断param类型
if (param instanceof Integer) {
statement.setInt(1, (Integer) param);
}
if (param instanceof String) {
statement.setString(1, (String) param);
}
传两个参数怎么办呢？
表结构设计好之后是不能再动的，参数设置好了之后也是不能再动的，用可变参数
//判断param类型
for(Object item : param) {
if(item instanceof Integer) {
statement.setInt(1, (Integer) item);
}
}
statement.setInt 前面的数字怎么处理？
//判断param类型
for (int i = 0; i < param.length; i++) {
if (param[i] instanceof String) {
statement.setString(i + 1, (String) param[i]);
}
if (param[i] instanceof Integer) {
statement.setInt(i + 1, (Integer) param[i]);
}
}
query: Beanlist使用
MyQueryRunner myQueryRunner = new MyQueryRunner();
List<User> list =(List<User>) myQueryRunner.query(connection, sql,
new MyBeanListHandler<User>(User.class));
for (User user : list) {
System.out.println(user);
}
最终的DBUtils代码
MyBeanHandler
package com.dbutils;
import org.apache.commons.dbutils.ResultSetHandler;
import java.lang.reflect.Constructor;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.ResultSet;
import java.sql.SQLException;
public class MyBeanHandler<T> implements ResultSetHandler {
private Class clazz; // 存储目标类的Class对象
public MyBeanHandler(Class clazz) { //通过构造函数传入 Class 对象
this.clazz = clazz;
}
@Override
public T handle(ResultSet resultSet) throws SQLException {
//创建Java对象
Object object = null;
try {
Constructor constructor = clazz.getConstructor(null); //获取目标类的无参构造器
object = constructor.newInstance(null); //通过构造器创建新实例
//给对象的属性赋值，类型要一致
//从结果集里去取数据
if (resultSet.next()) {
//获取实体类的字段
Field[] fields = clazz.getDeclaredFields();//getFields拿的是实体类里面的公有属性
for (Field field : fields) {
String fieldName = field.getName(); //获取字段名（如 "id", "name"）
String TypeName = field.getType().getName(); //获取类型全限定名（如 "java.lang.Integer"）
//结果集值提取（动态类型转换）
Object value = null;
switch (TypeName) {
case "java.lang.Integer":
value = resultSet.getInt(fieldName); //根据字段类型选择对应的 ResultSet 方法
break;
case "java.lang.String":
value = resultSet.getString(fieldName);
break;
}
/*
动态调用Setter方法（反射核心）
*/
//赋值
String methodName = "set" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
//System.out.println(methodName);
Method method = clazz.getMethod(methodName, field.getType());
method.invoke(object, value);
}
}
} catch (Exception e) {
e.printStackTrace();
}
return (T) object; //强转成泛型
}
}
MyBeanListHandler
package com.dbutils;
import org.apache.commons.dbutils.ResultSetHandler;
import java.lang.reflect.Constructor;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
public class MyBeanListHandler<T> implements ResultSetHandler {
private Class clazz; //接收集合的泛型
public MyBeanListHandler(Class clazz) {
this.clazz = clazz;
}
@Override
public List<T> handle(ResultSet resultSet) throws SQLException {
List<T> list = new ArrayList<T>();
while (resultSet.next()) {
try {
Constructor constructor = clazz.getConstructor(null);
Object object = constructor.newInstance(null);
list.add((T) object);
//给对象的属性赋值，类型要一致
//从结果集里去取数据
//获取实体类的字段
Field[] declaredFields = clazz.getDeclaredFields();//getFields
// 拿的是实体类里面的公有属性
for (Field declarefield : declaredFields) {
String fieldName = declarefield.getName(); //获取字段名（如 "id", "name"）
String TypeName = declarefield.getType().getName(); //获取类型全限定名（如// "java.lang.Integer"）
//结果集值提取（动态类型转换）
Object value = null;
switch (TypeName) {
case "java.lang.Integer":
value = resultSet.getInt(fieldName); //根据字段类型选择对应的 ResultSet 方法
break;
case "java.lang.String":
value = resultSet.getString(fieldName);
break;
}
/*
动态调用Setter方法（反射核心）
*/
//赋值
String methodName = "set" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
//System.out.println(methodName);
Method method = clazz.getMethod(methodName, declarefield.getType());
method.invoke(object, value);
}
} catch (Exception e) {
e.printStackTrace();
}
}
return list;
}
}
MyQueryRunner
package com.dbutils;
import com.entity.User;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
public class MyQueryRunner {
public Object query(Connection connection, String sql,
MyBeanHandler handler, Object... param) {
try {
PreparedStatement statement = connection.prepareStatement(sql);//传？参
//判断param类型
for (int i = 0; i < param.length; i++) {
if (param[i] instanceof String) {
statement.setString(i + 1, (String) param[i]);
}
if (param[i] instanceof Integer) {
statement.setInt(i + 1, (Integer) param[i]);
}
}
ResultSet resultSet = statement.executeQuery();//PreparedStatement无需传sql
return handler.handle(resultSet);//resultSet传递给下一层BeanListHandler去处理
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
public Object query(Connection connection, String sql, MyBeanListHandler handler) {
try {
PreparedStatement statement = connection.prepareStatement(sql);
ResultSet resultSet = statement.executeQuery();//PreparedStatement无需传sql
return handler.handle(resultSet);//resultSet传递给下一层BeanListHandler去处理
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
public int update(Connection connection,String sql,Object ... param) {//增、删、改
int result = 0;
try {
PreparedStatement statement = connection.prepareStatement(sql);
//判断param类型
for (int i = 0; i < param.length; i++) {
if (param[i] instanceof String) {
statement.setString(i + 1, (String) param[i]);
}
if (param[i] instanceof Integer) {
statement.setInt(i + 1, (Integer) param[i]);
}
}
result = statement.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}
return result;
}
}
浙公网安备 33010602011771号