MyBatis官网:https://mybatis.org/mybatis-3/zh_CN/index.html
1 MySQL
1.1 概述
MySQL下载地址:https://dev.mysql.com/downloads/mysql/)
在终端连接MySQL服务器:mysql -u用户名 -p[密码] [-h数据库服务器IP地址 -P端口号]
或可在DataGrip或Idea(集成了DataGrip)的图形化界面中连接、操作MySQL数据库,如下图所示
数据库(DataBase,DB):存储和管理数据的仓库。
数据库管理系统(DataBase Management System,DBMS):操纵和管理数据库的大型软件。
关系型数据库(RDBMS):建立在关系模型基础上,由多张相互连接的二维表组成的数据库。使用表存储数据,格式统一,便于维护;使用SQL语言操作,标准统一,使用方便,可用于复杂查询。
SQL(Structured Query Language,操作关系型数据库的编程语言)定义了一套操作关系型数据库的统一标准。
- SQL语句可以单行或多行书写,以分号结尾
- SQL语句可以使用空格/缩进来增强语句的可读性
- MySQL数据库的SQL语句不区分大小写
- 注释:单行注释(
-- 注释内容
或#注释内容
)、多行注释(/* 注释内容 */
)
SQL语句分类 | 英文全称 | 中文名 | 说明 |
---|---|---|---|
DDL | Data Definition Language | 数组定义语言 | 用来定义数据库对象(数据库库,表,字段) |
DML | Data Manipulation Language | 数据操作语言 | 用来对数据库表中的数据进行增删改 |
DQL | Data Query Language | 数据查询语言 | 用来查询数据库中表的记录 |
DCL | Data Control Language | 数据控制语言 | 用来创建数据库用户、控制数据库的访问权限 |
1.2 数据库设计——DDL
1.2.1 数据库操作
- 查询数据库
/* 查询所有数据库 */
show databases;
/* 查询当前数据库 */
select database();
- 使用数据库
use 数据库名;
- 创建数据库
create database [if not exists] 数据库名;
- 删除数据库
drop database [if exists] 数据库名;
方括号[]中内容表示可选;所有的
database(s)
可替换成schema(s)
1.2.2 表操作
设置
create_time
/update_time
:用now()
函数获取当前时间
- 创建表结构
create table 表名 (
字段1 字段类型 [约束] [comment 字段1注释],
字段2 字段类型 [约束] [comment 字段2注释],
...
字段n 字段类型 [约束] [comment 字段n注释]
) [comment 表注释];
- 查询
/* 查询当前数据库所有表 */
show tables;
/* 查询表结构 */
desc 表名;
/* 查询建表语句 */
show create table 表名;
- 修改
/* 添加字段 */
alter table 表名 add 字段名 字段类型(长度) [comment 注释] [约束];
/* 修改字段类型 */
alter table 表名 modify 字段名 新字段类型(长度);
/* 修改字段名和字段类型 */
alter table 表名 change 旧字段名 新字段名 字段类型(长度) [comment 注释] [约束];
/* 删除字段 */
alter table 表名 drop column 字段名;
/* 修改表名 */
rename table 表名 to 新表名;
- 删除表结构
drop table [if exists] 表名;
约束
作用于表中字段上的规则,用于限制存储在表中的数据,保证数据库中数据的正确性、有效性和完整性。
约束 | 描述 | 关键字 |
---|---|---|
非空约束 | 限制该字段值不能为null | not null |
唯一约束 | 保证字段的所有数据都是唯一、不重复的 | unique |
主键约束 | 主键是一行数据的唯一标识,要求非空且唯一 | primary key (auto_increment 自增) |
默认约束 | 保存数据时,如果未指定该字段值,则采用默认值 | default + 默认值 |
外键约束 | 让两张表的数据建立连接,保证数据的一致性和完整性 | foreign key |
数据类型
MySQL中的数据类型主要分为数值类型、字符串类型、日期时间类型,字符串和日期型数据应包含在引号中。
数值类型 | 大小 | 有符号范围signed | 无符号范围unsigned | 描述 |
---|---|---|---|---|
tinyint | 1 | ( − 128 , 127 ) (-128,\ 127) (−128, 127) | ( 10 , 255 ) (10,\ 255) (10, 255) | 小整数值 |
smallint | 2 | ( − 32768 , 32767 ) (-32768,\ 32767) (−32768, 32767) | ( 0 , 65535 ) (0,\ 65535) (0, 65535) | 大整数值 |
mediumint | 3 | ( − 8388608 , 8388607 ) (-8388608,\ 8388607) (−8388608, 8388607) | ( 0 , 16777215 ) (0,\ 16777215) (0, 16777215) | 大整数值 |
int | 4 | ( − 2147483648 , 2147483647 ) (-2147483648,\ 2147483647) (−2147483648, 2147483647) | ( 0 , 4294967295 ) (0,\ 4294967295) (0, 4294967295) | 大整数值 |
bigint | 8 | ( − 2 63 , 2 63 − 1 ) (-2^{63},\ 2^{63}-1) (−263, 263−1) | ( 0 , 2 64 − 1 ) (0,\ 2^{64}-1) (0, 264−1) | 极大整数值 |
float | 4 | ( − 3.402823466 × 1 0 38 , 3.402823466351 × 1 0 38 ) (-3.402823466\times 10^{38},\ 3.402823466351\times 10^{38}) (−3.402823466×1038, 3.402823466351×1038) | 0 0 0 和 ( 1.175494351 × 1 0 − 38 , 3.402823466 × 1 0 38 ) (1.175494351\times 10^{-38},\ 3.402823466\times10^{38}) (1.175494351×10−38, 3.402823466×1038) | 单精度浮点数值,float(n, m) :整个数字长度为n,小数位为m |
double | 8 | ( − 1.7976931348623157 × 1 0 308 , 1.7976931348623157 × 1 0 308 ) (-1.7976931348623157\times 10^{308},\ 1.7976931348623157\times 10^{308}) (−1.7976931348623157×10308, 1.7976931348623157×10308) | 0 0 0 和 ( 2.2250738585072014 × 1 0 − 308 , 1.7976931348623157 × 1 0 308 ) (2.2250738585072014\times 10^{-308},\ 1.7976931348623157\times 10^{308}) (2.2250738585072014×10−308, 1.7976931348623157×10308) | 双精度浮点数值,double(n, m) :整个数字长度为n,小数位为m |
decimal | 小数值(精度更高),decimal(n, m) :整个数字长度为n,小数位为m |
字符串类型 | 大小 | 描述 |
---|---|---|
char | 0~255 bytes | 定长字符串,char(n) :最多只能存n个字符,始终占用n个字符的空间,空间换时间 |
varchar | 0~65535 bytes | 变长字符串,varchar(n) :最多只能存n个字符,不足n个时按实际长度存储,时间换空间 |
tinyblob | 0~255 bytes | 不超过255个字符的二进制数据 |
tinytext | 0~255 bytes | 短文本字符串 |
blob | 0~65535 bytes | 二进制形式的长文本数据 |
text | 0~65535 bytes | 长文本数据 |
mediumblob | 0~16 777 215 bytes | 二进制形式的中等长度文本数据 |
mediumtext | 0~16 777 215 bytes | 中等长度文本数据 |
longblob | 0~4 294 967 295 bytes | 二进制形式的极大文本数据 |
longtext | 0~294 967 295 bytes | 极大文本数据 |
日期类型 | 大小(byte) | 范围 | 格式 | 描述 |
---|---|---|---|---|
date | 3 | 1000-01-01 至 9999-12-31 | YYYY-MM-DD | 日期值 |
time | 3 | -838:59:59 至 838:59:59 | HH:MM:SS | 时间值或持续时间 |
year | 1 | 1901 至 2155 | YYYY | 年份值 |
datetime | 8 | 1000-01-01 00:00:00 至 9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
timestamp | 4 | 1970-01-01 00:00:01 至2038-01-19 03:14:07 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值,时间戳 |
1.3 数据库操作——DML
- 添加数据
/* 指定字段添加数据 */
insert into 表名 (字段名1, 字段名2) values (值1, 值2); -- 指定的字段与值一一对应
insert into 表名 (字段名1, 字段名2) values (值1, 值2), (值1, 值2); -- 批量添加数据
/* 全部字段添加数据 */
insert into 表名 values (值1, 值2, ...);
insert into 表名 values (值1, 值2, ...), (值1, 值2, ...); -- 批量添加数据
- 修改/更新数据
update 表名 set 字段名1 = 值1, 字段名2 = 值2, ... [where 条件];
- 删除操作(无法删除某一字段的值,可以使用
UPDATE
将字段的值置为NULL
)
delete from 表名 [where 条件];
1.4 数据库查询——DQL
1.4.1 基本查询
/* 查询字段 */
select 字段1, 字段2, 字段3 from 表名;
select * from 表名; -- 查询所有字段
/* 设置别名 */
select 字段1 [[as] 别名1], 字段2 [[as] 别名2] from 表名; -- 可以省略关键字as;别名有空格则必须带引号
/* 去重查询 */
select distinct 字段列表 from 表名;
通配符(*)不直观,影响效率,实际开发中尽量少用
1.4.2 条件查询
select 字段列表 from 表名 where 条件列表;
比较运算符 | 功能 |
---|---|
> | 大于 |
>= | 大于等于 |
< | 小于 |
<= | 小于等于 |
= | 等于 |
!= 或<> | 不等于 |
between ... and ... | 在某个范围之内(闭区间) |
in (...) | 属于in之后的列表中的值之一 |
like 占位符 | 模糊匹配:_ 匹配1个字符,% 匹配任意个字符(可以0个)。例: name like '王__' 表示匹配name为王某某(两个下划线) |
is null | 为null |
逻辑运算符 | 功能 |
---|---|
and 或&& | 并且(多个条件同时成立) |
or 或|| | 或者(多个条件任意一个成立) |
not 或! | 非,不是 |
流程控制:
- if函数:
if(条件表达式, true取值, false取值)
- case:
(case 表达式 when 值1 then 结果1 [when 值2 then 结果2 ... ] [else result] end)
1.4.3 分组查询
聚合函数:将一列数据作为一个整体,进行纵向计算,如用select 聚合函数(字段列表) from 表名;
可直接查询运算后的结果。null值不参与所有聚合函数运算。
常用的聚合函数 | 功能 | 语法说明 |
---|---|---|
count | 统计数量 | count(字段) :统计该字段非null值数量count(*) :统计所有行的数量,包括含null值的行(推荐)count(常量) :统计所有行的数量,包括含null值的行,效果同上 |
max | 最大值 | max(字段) |
min | 最小值 | min(字段) |
avg | 平均值 | avg(字段) ,返回浮点数 |
sum | 求和 | sum(字段) |
分组查询的字段列表一般为分组字段 聚合函数
,执行顺序:where > 聚合函数 > having 。
select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件];
-- 例1:根据性别分组,统计男女员工的数量
select gender, count(*) from tb_emp group by gender;
-- 例2:先查询入职时间在'2015-01-01'以前的员工,并对结果根据职位分组,获取员工数量大于等于2的职位
select job, count(*) from tb_emp where entrydate <= '2015-01-01' group by job having count(*) >= 2;
where与having区别
- 执行时机不同:where是分组前进行过滤,不满足where条件,不参与分组;而having是分组后对结果进行过滤。
- 判断条件不同:where不能对聚合函数进行判断,而having可以。
1.4.4 排序查询
ASC
:升序(默认)DESC
:降序
select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件] order by 字段1 排序方式1, 字段2 排序方式2 ... ;
如果是多字段排序,当第一个字段值相同时,才会根据第二个字段进行排序。
1.4.5 分页查询
起始索引从0开始,计算式:起始索引 = ( = ( =(页码 − 1 ) × -1) \times −1)×每页展示记录数
select 字段列表 from 表名 limit 起始索引, 查询记录数;
-- 例:从起始索引0开始查询员工数据,每页展示5条记录。
-- 1. 查询第1页员工数据(查询第1页时可省略起始索引0)
select * from tb_emp limit 5;
-- 2. 查询第2页员工数据
select * from tb_emp limit 5, 5;
-- 3. 查询第3页员工数据
select * from tb_emp limit 10, 5;
分页查询是数据库的方言,不同的数据库有不同的实现,MySQL中是LIMIT。
1.5 多表设计
项目开发中,在进行数据库表结构设计时,会根据业务需求及业务模块之间的关系,分析并设计表结构。由于业务之间相互关联,所以各个表结构之间也存在着各种联系,基本上分为三种:
- 一对多(多对一)
- 多对多
- 一对一
1.5.1 一对多
一对多关系中,"一"和"多"的双方常分别称为父表(主表)和子表(参考数据结构中的树)。
案例:部门与员工的关系。
-- 员工
create table tb_emp (
id int unsigned primary key auto_increment comment 'ID',
...
entrydate date comment '入职时间',
dept_id int unsigned comment '归属的部门ID', -- 关联的部门ID
create_time datetime not null comment '创建时间',
update_time datetime not null comment '修改时间'
) comment '员工表';
-- 部门
create table tb_dept (
id int unsigned primary key auto_increment comment 'ID',
name varchar(10) not null unique comment '部门名称',
create_time datetime not null comment '创建时间',
update_time datetime not null comment '修改时间'
) comment '部门表';
部门数据可以直接删除,然而还有部分员工归属于该部门下,此时就出现了数据的不完整、不一致问题。原因在于目前上述的两张表,在数据库层面并未建立关联,所以无法保证数据的一致性和完整性。
实现表与表之间关联的方法:外键
- 物理外键:使用外键约束
foreign key
定义外键关联另外一张表。在多的一方添加外键,关联另外一方的主键。- 缺点
- 影响增、删、改的效率(需要检查外键关系)
- 仅用于单节点数据库,不适用与分布式、集群场景
- 容易引发数据库的死锁问题,消耗性能
- 缺点
-- 创建表时指定外键
create table 表明 (
字段名 数据类型,
...
[constraint] [外键名称] foreign key (外键字段名) references 主表 (字段名)
);
-- 建完表后,添加外键
alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表(字段名);
- 逻辑外键:在业务层逻辑中,解决外键关联。可以避免物理外键的问题。
1.5.2 一对一
一对一关系多用于单表拆分,将一张表的基础字段放在一张表中,其他字段放在另一张表中,以提升操作效率。可以视为只有一个子表的特殊的一对多关系。
案例:用户与身份证信息的关系。
实现:在任意一方加入外键,关联另外一方的主键,并且设置外键为唯一的(UNIQUE
)。
1.5.3 多对多
案例:学生与课程的关系,一个学生可以选修多门课程,一门课程也可以供多个学生选择。
实现:建立第三张中间表,中间表至少包含两个外键,分别关联两方主键
1.6 多表查询
1.6.1 概述
多表查询:从多张表中查询数据,分为连接查询(内连接、外连接)、子查询。
select * from tb_emp, tb_dept; -- 该种方式使得两张表的数据两两组合,产生了大量无效数据
笛卡尔积:在数学中,两个集合(
A
A
A 集合和
B
B
B 集合)的所有组合情况。
多表查询的目的就是消除笛卡尔积产生的无效数据,可以根据所设外键指定条件进行查询。指定字段时用表名.字段名
格式。
select * from tb_emp, tb_dept where tb_emp.dept_id = tb.dept.id;
1.6.2 内连接
内连接:相当于查询上图中 A A A、 B B B 交集部分数据
/* 隐式内连接 */
select 字段列表 from 表1, 表2 where 条件 ... ;
/* 显式内连接 */
select 字段列表 from 表1 [inner] join 表2 on 连接条件 ... ;
-- 例:查询员工的姓名及所属部门的名称(注:某些员工不属于任何部门,也某些部门无员工)
-- 1. 隐式内连接
select tb_emp.name, tb_dept.name from tb_emp, tb_dept where tb_emp.dept_id = tb_dept.id;
select e.name, d.name from tb_emp e, tb_dept d where e.dept_id = d.id; -- 可先给表起别名,简化书写
-- 2. 显示内连接
select tb_emp.name, tb_dept.name from tb_emp inner join tb_dept on tb_emp.dept_id = tb.dept.id
1.6.3 外连接
外连接根据上图中集合位置可分为以下两类
- 左外连接:查询左表所有数据(包括两张表交集部分数据)
- 右外连接:查询右表所有数据(包括两张表交集部分数据)
/* 左外连接(更常用) */
select 字段列表 from 表1 left [outer] join 表2 on 连接条件 ... ; -- 表1为左表
/* 右外连接(可改写为左外连接) */
select 字段列表 from 表1 right [outer] join 表2 on 连接条件 ... ; -- 表2为右表
-- 例1:查询员工表所有员工的姓名及所属部门的名称
select e.name, d.name from tb_emp e left join tb_dept d on e.dept_id = d.id; -- 左外连接
-- 例2:查询部门表所有部门的名称及对应员工的名称
select e.name, d.name from tb_emp e right join tb_dept d on e.dept_id = d.id; -- 右外连接
1.6.4 子查询
子查询/嵌套查询:SQL语句中嵌套的select语句,外部语句可为insert、update、delete、select(最常见)。根据子查询返回的结果可分为如下几类
- 标量子查询:子查询返回的结果为单个值(数字、字符串、日期等),是最简单的子查询。常用的操作符有
=
、!=
、>
、>=
、<
、<=
。
-- 例1:查询部门表中教研部所有员工信息:1. 查询教研部的部门ID;2. 查询该ID的部门下的员工信息。
select * from tb_emp where dept_id = (select id from tb_dept where name = '教研部');
-- 例2:查询司马彰入职后的员工信息:1. 查询司马彰的入职时间;2. 查询在司马彰入职后的员工信息
select * from tb_emp where entrydate > (select entrydate from tb_emp where name = '司马彰');
- 列子查询:子查询返回的结果为一列(可以是多行)。常用的操作符有
in
、not in
等
-- 例:查询教研部和咨询部的所有员工信息:1. 查询教研部和咨询部的ID;2. 查询这些ID的部门下的员工信息
select * from tb_emp where dept_id in (select id from tb_dept where name = '教研部' or name = '咨询部');
- 行子查询:子查询返回的结果为一行(可以是多列)。常用的操作符有
=
、!=
、in
、not in
等
-- 例:查询与yohane的入职日期与职位都相同的员工信息:1. 查询yohane的入职日期与职位;2. 查询入职日期和职位与她都相同的员工信息
select * from tb_emp where (entrydate, job) = (select entrydate, job from tb_emp where name = 'yohane');
-- 优化写法
- 表子查询:子查询返回的结果为多行多列,常作为临时表使用。常用的操作符有
in
等
-- 例:查询入职日期是'2024-05-01'之后的员工信息,及其部门名称
select e.*, d.name from (select * from tb_emp where entrydate > '2024-05-01') e, tb_dept d where e.dept_id = d.id; -- e.*表示表e的所有信息
1.7 事务
事务是一组操作的集合,它是一个不可分割的工作单位。事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求,即这些操作要么同时成功,要么同时失败。
默认MySQL的事务是自动提交的,也就是说,当执行一条DML语句,MySQL会立即隐式的提交事务。
- 事务的控制
/* 开启事务 */ start transaction; begin; /* 提交事务 */ commit; /* 回滚事务 */ rollback;
- 事务的四大特性(ACID)
- 原子性(Atomicity):事务是不可分割的最小单元,要么全部成功,要么全部失败
- 一致性(Consistency):事务完成时,必须使所有的数据都保持一致状态
- 隔离性(Isolation):数据库系统提供的隔离机制,保证事务在不受外部并发操作影响的独立环境下运行
- 持久性(Durability):事务一旦提交或回滚,它对数据库中的数据的改变就是永久的
1.8 数据库优化——索引
索引(index)是帮助数据库高效获取数据的数据结构。
- 优点
- 提高数据查询的效率,降低效据库的I0成本。
- 通过索引列对数据进行排序,降低致据排序的成本,降低CPU消耗。
- 缺点
- 索引会占用存储空间。
- 索引大大提高了查询效率,同时却也降低了insert、update、delete的效率。
MySQL数据库支持的索引结构有很多,如:Hash索引、B+Tree案引、Full-Text索引等。我们平常所说的案引,若无特别指明都是指默认的B+Tree结构组织的索引:
- 每一个结点可以存储多个key(有n个key,就有n个指针)
- 所有的数据都存储在叶子结点,非叶子结点仅用于索引数据
- 叶子结点形成了一条双向链表,便于数据的排序及区间范围查询
使用普通的二叉搜索树或红黑树作为索引结构的缺点:大数据量情况下,层级深,检索速度慢。
索引的常用操作如下
/* 为表的字段创建索引 */
create [unique] index 索引名 on 表名 (字段名, ...); -- 加上unique表示唯一索引
/* 查看索引 */
show index from 表名;
/* 删除索引 */
drop index 索引名 on 表名;
- 主键字段在建表时,会自动创建主键索引。
- 添加唯一约束时,数据库实际上会添加唯一索引。
2 MyBatis
2.1 概述
JDBC(Java DataBase Connectivity )是sun公司官方定义的一套使用Java语言操作所有关系型数据库的API(规范)。各个数据库厂商去实现这套接口,提供数据库驱动jar包。我们可以使用这套接口(JDBC)编程,真正执行的代码是驱动jar包中的实现类。
MyBatis(官网:https://mybatis.org/mybatis-3/zh_CN/index.html)是一款优秀的持久层框架,用于简化JDBC的开发。
- 准备工作(创建SpringBoot工程、数据库表user、实体类User)
- 在application.properties中配置数据库连接:引入MyBatis的相关依赖,配置数据库的连接信息——四要素
# application.properties # 驱动类名称 spring.datasource.driver-class-name = com.mysql.cj.jdbc.Driver # 数据库连接的url spring.datasource.url = jdbc:mysql://localhost:3306/数据库 # 连接数据库的用户名 spring.datasource.username = 用户名 # 连接数据库的密码 spring.datasource.password = 密码
- 用注释
@Mapper
定义mapper接口,并在操作注释中编写SQL语句(或者用XML映射文件):运行时会自动生成该接口的实体类对象(代理对象),并将该对象成为Bean交给IOC容器管理/* mapper.UserMapper.java */ @Mapper public interface UserMapper { @Select("select * from user") public List<User> list(); // 查询全部用户信息 }
- 单元测试:使用依赖注入(DI)
/* SpringbootMybatisQuickstartApplicationTests.java */ @SpringBootTest class SpringbootMybatisQuickstartApplicationTests { @Autowired private UserMapper userMapper; @Test public void testListUser() { List<User> userList = userMapper.list(); userList.stream().forEach(System.out::println); } }
默认在MyBatis中编写SQL语句是不识别的,可如下图右键配置,并在Idea中配置MySQL数据库的连接(参见1.1)以识别表信息
要想查看MyBatis的日志,可以在application.properties中添加以下内容:
# 指定mybatis输出日志的位置,输出控制台
mybatis.configuration.log-impl = org.apache.ibatis.logging.stdout.StdOutImpl
2.2 数据库连接池
数据库连接池是负责分配、管理数据库连接(Connection)的容器。它允许应用程序重复使用一个现有的数据库连接,而不是再重新建立一个。释放空闲时间超过最大空闲时间的连接,来避免因为没有释放连接而引起的数据库连接遗漏。
优势:资源重用、提升系统响应速度、避免数据库连接遗漏
DataSource:sun公司官方提供的数据库连接池标准接口,由第三方组织实现此接口,用于获取连接
Connection getConnection() throws SQLException;
Hikari连接池是SpringBoot默认的连接池。
Druid(德鲁伊)连接池是阿里巴巴开源的数据库连接池项目,功能强大,性能优秀,是Java语言最好的数据库连接池之一。切换成Druid数据库连接池只需引入起步依赖druid-spring-boot-starter
,详见:官方文档。
2.3 lombok工具包
Lombok是一个实用的lava类库,能通过注解的形式自动生成构造器、getter/setter、equals、hashCode、toString等方法,并可以自动化生成日志变量,简化java开发、提高效率。Lombok会在编译时自动生成对应的java代码,使用时需要安装一个lombok的插件(Idea自带)并引入依赖lombok
。
常用的Lombok注解 | 作用 |
---|---|
@Getter /@Setter | 为所有的属性提供 get/set 方法 |
@ToString | 会给类自动生成易阅读的 toString 方法 |
@EqualsAndHashCode | 根据类所拥有的非静态字段自动重写 equals 方法和 hashCode 方法 |
@Data | 提供了更综合的生成代码功能(@Getter +@Setter +@ToString +@EqualsAndHashCode ) |
@NoArgsConstructor | 为实体类生成无参构造器 |
@AllArgsConstructor | 为实体类生成除了static修饰的字段外的全参构造器 |
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Emp {
private Integer id;
private String username;
private String password;
private String name;
private Short gender; // 性别:1男,2女
private String image; // 图像url
private Short job; // 职位:1班主任,2讲师,3保安
private LocalDate entrydate; // 对应表中entrydate为date类型
private Integer deptId;
private LocalDateTime createTime; // 对应表中create_time为datetime类型
private LocalDateTime updateTime; // 对应表中update_time为datetime类型
}
2.4 基础操作
案例:用MyBatis对员工表emp
和部门表dept
进行操作,相关的数据类Emp
如2.3定义。
2.4.1 删除
可以获取返回值,为删除的记录数量,不需要时设置返回值为void
即可(插入、更新等操作同理)。
/* com.hyplus.mapper.EmpMapper.java */
@Mapper
public interface EmpMapper {
// 例:删除指定ID的所有数据
@Delete("delete from emp where id = #{id}")
public int delete(Integer id);
}
/* SpringbootMybatisCrudApplicationTests.java */
@SpringBootTest
class SpringbootMybatisCrudApplicationTests {
@Autowired
private EmpMapper empMapper;
@Test
public void testDelete() {
int cnt = empMapper.delete(37); // 例:删除ID为37的数据
System.out.println(cnt);
}
}
参数占位符 | 说明 | 使用时机 |
---|---|---|
#{...} | 执行SQL时会被预编译为? ,会自动设置参数值 | 参数传递时使用 |
${...} | 直接将参数拼接在SQL语句中,存在SQL注入问题 | 对表名、列表进行动态设置时使用 |
预编译SQL性能更高,更安全(防止SQL注入)。#{...}
会被预编译为?
。
SQL注入是通过操作输入的数据来修改事先定义好的SQL语句,以达到执行代码对服务器进行攻击的方法。
2.4.2 新增
可直接用POJO替换冗长的形参列表,占位符中的参数即为同名的对象属性。
主键返回:在数据添加成功后,需要获取插入数据库数据的主键。想获取主键可以在上述mapper接口方法上追加注释@Options
并在其中设置参数,其中useGeneratedKeys = true
表示想获取生成的主键值,keyProperty
为获取的主键所封装到的POJO属性。
/* com.hyplus.mapper.EmpMapper.java */
@Mapper
public interface EmpMapper {
// 例:新增员工
@Options(keyProperty = "id", useGeneratedKeys = true) // 自动将主键赋给emp对象的id属性
@Insert("insert into emp (username, name, gender, image, job, entrydate, dept_id, create_time, update_time) " +
"values (#{username}, #{name}, #{gender}, #{image}, #{job}, #{entrydate}, #{deptID}, #{createTime}, #{updateTime})")
public void insert(Emp emp);
}
/* SpringbootMybatisCrudApplicationTests.java */
@SpringBootTest
class SpringbootMybatisCrudApplicationTests {
@Autowired
private EmpMapper empMapper;
@Test
public void testInsert() {
// 新建员工对象
Emp emp = new Emp();
emp.setUsername("hyplus");
emp.setName("Akira");
emp.setImage("my_pfp.jpg");
emp.setGender((short)2);
emp.setJob((short)1);
emp.setEntrydate(LocalDate.of(2000, 1, 1));
emp.setCreateTime(LocalDateTime.now());
emp.setUpdateTime(LocalDateTime.now());
emp.setDeptId(1);
// 添加员工
empMapper.insert(emp);
}
}
LocalDate.of(年, 月, 日)
:返回指定日期。LocalDateTime.now()
返回当前日期时间。
2.4.3 更新
/* com.hyplus.mapper.EmpMapper.java */
@Mapper
public interface EmpMapper {
// 例:更新员工
@Update("update emp set username = #{username}, name = #{name}, gender = #{gender}, image = #{image}, job = #{job}, entrydate = #{entrydate}, " +
"dept_id = #{deptId}, create_time = #{createTime}, update_time = #{updateTime} where id = #{id}")
public void update(Emp emp);
}
/* SpringbootMybatisCrudApplicationTests.java */
@SpringBootTest
class SpringbootMybatisCrudApplicationTests {
@Autowired
private EmpMapper empMapper;
@Test
public void testUpdate() {
// 设置更新后的员工对象
Emp emp = new Emp();
emp.setId(18); // 例:设置ID为18的员工
emp.setUsername("hyplus");
emp.setName("Akira");
emp.setImage("my_pfp.jpg");
emp.setGender((short)2);
emp.setJob((short)1);
emp.setEntrydate(LocalDate.of(2000, 1, 1));
emp.setUpdateTime(LocalDateTime.now()); // 每次更新必须将updateTime更新为当前时间
emp.setDeptId(1);
// 更新员工
empMapper.update(emp);
}
}
2.4.4 查询
(1) 根据ID查询
数据封装:若实体类属性名和数据库表查询返回的字段名一致,MyBatis会自动封装;若不一致,则不能自动封装。
解决不一致的方法:
- 直接给字段起别名
- 使用注解
@Results
、@Result
手动映射封装 - 开启MyBatis的驼峰命名自动映射开关:
a_column
→ \rightarrow →aColumn
# application.properties
# 开启驼峰命名自动映射
mybatis.configuracion.map-underscore-to-canel-case = true
/* com.hyplus.mapper.EmpMapper.java */
@Mapper
public interface EmpMapper {
// 例:根据ID查询员工。
@Select("select * from emp where id = #{id}")
public Emp getById(Integer id);
// 发生不匹配时的解决方法
// 法1:给字段起别名,让别名与实体类属性一致
// @Select("select id, username, password, name, gender, image, job, entrydate, " +
// "dept_id deptId, create_time createTime, update_time updateTime from emp where id = #{id}")
// public Emp getById(Integer id);
// 法2:通过@Results, @Result注解手动映射封装(column -> property)
// @Results({
// @Result(column = "dept_id", property = "deptId"),
// @Result(column = "create_time", property = "createTime"),
// @Result(column = "update_time", property = "updateTime"),
// })
// @Select("select * from emp where id = #{id}")
// public Emp getById(Integer id);
// 法3:开启驼峰命名自动映射,无需改动
}
/* SpringbootMybatisCrudApplicationTests.java */
@SpringBootTest
class SpringbootMybatisCrudApplicationTests {
@Autowired
private EmpMapper empMapper;
@Test
public void testGetByID() {
Emp emp = empMapper.getByID(20);
System.out.println(emp);
}
}
(2) 条件查询
#{...}
预编译为?
后无法出现在引号(' '
)之内,有两种替代方式:
- 占位符改用
${...}
。性能低、不安全、存在SQL注入问题。 - 使用MySQL提供的函数
concat(str1, str2, ...)
拼接字符串。推荐用这种方式。
/* com.hyplus.mapper.EmpMapper.java */
@Mapper
public interface EmpMapper {
// 例:查询指定的姓名含某字符串、性别、入职日期范围的所有员工
@Select("select * from emp where name like concat('%', #{name}, '%') and gender = #{gender} and " +
"entrydate between #{begin} and #{end} order by update_time desc")
public List<Emp> list(String name, Short gender, LocalDate begin, LocalDate end);
}
在SpringBoot的1.x版本或单独使用MyBatis时,每个形参前都需追加注释
@Param
并在其中指定字段名
2.5 XML映射文件
MyBatis的XML映射文件约束请查阅官网:https://mybatis.org/mybatis-3/zh_CN/getting-started.html
建议安装MyBatisX插件
- 规范
- XML映射文件的名称与Mapper接口名称一致,并且将XML映射文件和Mapper接口放置在相同包下(同包同名)。
- XML映射文件的
namespace
属性与Mapper接口全限定名一致。 - XML映射文件中SQL语句中的标签名与SQL操作名一致(
<select ...>
、<update ...>
等),id
属性值与Mapper接口中的方法名一致,resultType
属性也与接口方法的返回类型中的单条属性类型一致。
上述2.4.4(2)中mapper接口的注释可改写为如下的xml映射文件
<!-- src/main/resources/com/hyplus/mapper/EmpMapper.xml -->
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.hyplus.mapper.EmpMapper">
<select id="list" resultType="com.hyplus.pojo.Emp">
select * from emp where name like concat('%', #{name}, '%') and gender = #{gender}
and entrydate between #{begin} and #{end} order by update_time desc
</select>
</mapper>
/* com.hyplus.mapper.EmpMapper.java */
@Mapper
public interface EmpMapper {
// 调用该方法时会自动查找对应的xml配置文件中id属性名为该方法名的SQL语句
public List<Emp> list(String name, Short gender, LocalDate begin, LocalDate end);
}
使用注解来映射简单语句会使代码显得更加简洁,但对于稍微复杂一点的语句,Java 注解不仅力不从心,还会让本就复杂的 SQL 语句更加混乱不堪。 因此,如果你需要做一些很复杂的操作,最好用 XML 来映射语句。
选择何种方式来配置映射,以及是否应该要统一映射语句定义的形式,完全取决于你和你的团队。 换句话说,永远不要拘泥于一种方式,你可以很轻松地在基于注解和 XML 的语句映射方式间自由移植和切换。
2.6 动态SQL
动态SQL:随着用户的输入或外部条件的变化而变化的SQL语句
2.6.1 if标签
<if>
:用于判断条件是否成立。使用test
属性进行条件判断,其中字段名应与对象属性名一致(不是表字段名),如果条件为true,则拼接SQL
<where>
:替换where关键字,只会在子元素有内容的情况下才插入where子句,同时去除子句开头多余的and或or关键字
<set>
:替换set关键字,将所有更新字段全部包裹,同时去除子句末尾多余的逗号
<!-- 例1:改写2.5中的xml映射文件,动态改变查询条件 -->
<select id="list" resultType="com.hyplus.pojo.Emp">
select * from emp
<where>
<if test="name != null">
name like concat('%', #{name}, '%')
</if>
<if test="gender != null">
and gender = #{gender}
</if>
<if test="begin != null and end != null">
and entrydate between #{begin} and #{end}
</if>
</where>
order by update_time desc
</select>
<!-- 例2:动态更新员工数据信息 -->
<update id="update">
update emp
<set>
<if test="username != null">
username = #{username},
</if>
<if test="name != null">
name = #{name},
</if>
...
<if test="deptId != null">
dept_id = #{deptID},
</if>
<if test="updateTime != null">
update_time = #{updateTime}
</if>
</set>
where id = #{id}
</update>
2.6.2 foreach标签
<foreach>
:遍历列表中的所有元素,各属性含义如下:
collection
:遍历的集合(与接口方法参数名一致)item
:集合遍历出来的元素/项(“i”)separator
:每一次遍历使用的分隔符,拼接时分隔元素open
:遍历开始前拼接的SQL片段close
:遍历结束后拼接的SQL片段
<!-- 例:根据ID(主键)批量删除员工 -->
<delete id="deleteByIds">
delete from emp where id in
<foreach collection="ids" item="id" separator="," open="(" close=")">
#{id}
</foreach>
</delete>
2.6.3 sql、include标签
<sql>
:定义可重用的SQL片段并设置其id
属性。
<include>
:通过属性refid
指定包含的SQL片段。
<!-- 例:抽取出查询操作共用的SQL语句段 -->
<sql id="commonSelect">
select id, username, password, name, gender, image, job, entrytime, dept_id, create_time, update_time
from emp
</sql>
<select id="list" resultType="com.hyplus.pojo.Emp">
<include refid="commonSelect">
...
</select>