1. Mybatis基础操作
1.1 需求
参考资料中提供的页面原型/tlias智能学习辅助系统,完成员工管理的需求开发。
里面涉及到的功能包括:
- 查询(select)
- 根据ID查询
- 分页查询
-
新增(insert)
-
修改(update)
-
删除(delete)
- 根据主键ID删除
- 根据主键ID批量删除
1.2 准备
1. 创建一个新的springboot工程,选择引入对应的起步依赖(mybatis、mysql驱动、lombok)
2. application.properties中引入数据库连接信息(复制过来即可)
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/mybatis
spring.datasource.username=root
spring.datasource.password=1234
3. 准备数据库表 emp,及对应的实体类 Emp (实体类属性采用驼峰命名模式)
- SQL:
-- 员工管理(带约束)
create table emp (
id int unsigned primary key auto_increment comment 'ID',
username varchar(20) not null unique comment '用户名',
password varchar(32) default '123456' comment '密码',
name varchar(10) not null comment '姓名',
gender tinyint unsigned not null comment '性别, 说明: 1 男, 2 女',
image varchar(300) comment '图像',
job tinyint unsigned comment '职位, 说明: 1 班主任,2 讲师, 3 学工主管, 4 教研主管, 5 咨询师',
entrydate date comment '入职时间',
dept_id int unsigned comment '部门ID',
create_time datetime not null comment '创建时间',
update_time datetime not null comment '修改时间'
) comment='员工表';
INSERT INTO emp
(id, username, password, name, gender, image, job, entrydate,dept_id, create_time, update_time) VALUES
(1,'jinyong','123456','金庸',1,'1.jpg',4,'2000-01-01',2,now(),now()),
(2,'zhangwuji','123456','张无忌',1,'2.jpg',2,'2015-01-01',2,now(),now()),
(3,'yangxiao','123456','杨逍',1,'3.jpg',2,'2008-05-01',2,now(),now()),
(4,'weiyixiao','123456','韦一笑',1,'4.jpg',2,'2007-01-01',2,now(),now()),
(5,'changyuchun','123456','常遇春',1,'5.jpg',2,'2012-12-05',2,now(),now()),
(6,'xiaozhao','123456','小昭',2,'6.jpg',3,'2013-09-05',1,now(),now()),
(7,'jixiaofu','123456','纪晓芙',2,'7.jpg',1,'2005-08-01',1,now(),now()),
(8,'zhouzhiruo','123456','周芷若',2,'8.jpg',1,'2014-11-09',1,now(),now()),
(9,'dingminjun','123456','丁敏君',2,'9.jpg',1,'2011-03-11',1,now(),now()),
(10,'zhaomin','123456','赵敏',2,'10.jpg',1,'2013-09-05',1,now(),now()),
(11,'luzhangke','123456','鹿杖客',1,'11.jpg',5,'2007-02-01',3,now(),now()),
(12,'hebiweng','123456','鹤笔翁',1,'12.jpg',5,'2008-08-18',3,now(),now()),
(13,'fangdongbai','123456','方东白',1,'13.jpg',5,'2012-11-01',3,now(),now()),
(14,'zhangsanfeng','123456','张三丰',1,'14.jpg',2,'2002-08-01',2,now(),now()),
(15,'yulianzhou','123456','俞莲舟',1,'15.jpg',2,'2011-05-01',2,now(),now()),
(16,'songyuanqiao','123456','宋远桥',1,'16.jpg',2,'2010-01-01',2,now(),now()),
(17,'chenyouliang','123456','陈友谅',1,'17.jpg',NULL,'2015-03-21',NULL,now(),now());
- 实体类Emp:
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.time.LocalDate;
import java.time.LocalDateTime;
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Emp {
private Integer id;
private String username;
private String password;
private String name;
private Short gender;
private String image;
private Short job;
private LocalDate entrydate;
private Integer deptId;
private LocalDateTime createTime;
private LocalDateTime updateTime;
}
4. 准备Mapper接口 EmpMapper
@Mapper
public interface EmpMapper {
}
整体项目结构如下:
1.3 删除
1.3.1 功能实现
当我们点击 后面的 “删除” 按钮时,前端页面会给服务端传递一个参数,也就是该行数据的ID。 我们接收到ID后,根据ID删除数据即可。
- 对应的SQL:
delete from emp where id = 17;
- 接口方法:
@Mapper
public interface EmpMapper {
@Delete("delete from emp where id = #{id}")
public void delete(Integer id);
}
注意:
如果mapper接口方法形参只有一个普通类型的参数,#{…} 里面的属性名可以随便写,如:#{id}、#{value}。
- 测试
接下来,我们就可以直接在单元测试类中通过 @Autowired 注解 EmpMapper接口。 然后就可以直接调用其delete方法传递参数进行测试了。
@SpringBootTest
class SpringbootMybatisQuickstart2ApplicationTests {
@Autowired
private EmpMapper empMapper;
@Test
public void testDel(){
empMapper.delete(15);
}
}
1.3.2 日志输入
可以在application.properties中,打开mybatis的日志,并指定输出到控制台。
#指定mybatis输出日志的位置, 输出控制台
mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl
开启日志之后,我们再次运行单元测试。可以看到在控制台中,输出了执行的SQL语句。
但是我们发现,输出的SQL语句为:delete from emp where id = ?,我们输入的参数 16 并没有在后面拼接,id的值是使用?进行占位。那这种SQL语句我们称为 预编译SQL。
1.3.3 预编译SQL
1.3.3.1 介绍
预编译的SQL,有两个优势:
- 性能更高
- 防止SQL注入
A. 性能更高: 预编译SQL,编译一次之后会将编译后的SQL语句缓存起来,后面再次执行这条insert语句时,SQL语句一样,不会再次编译。 只是输入的参数不同。
B. 防止SQL注入:将敏感字进行转义,安全。
1.3.3.2 SQL注入
SQL注入是通过操作输入来修改事先定义好的SQL语句,用以达到执行代码对服务器进行攻击的方法。
1.3.3.4 参数占位符
在Mybatis中提供的参数占位符有两种:${…}, #{…}。
1). #{…}
执行SQL时,会将#{…}替换为?,生成预编译SQL,会自动设置参数值。
使用时机:参数传递,都使用#{…}
2). ${…}
拼接SQL。直接将参数拼接在SQL语句中,存在SQL注入问题。
使用时机:如果对表名、列表进行动态设置时使用。
注意事项:
在项目开发中,建议使用#{…},生成预编译SQL,防止SQL注入安全。
1.4 新增
1.4.1 基本新增
1). SQL
insert into emp(username, name, gender, image, job, entrydate, dept_id, create_time, update_time) values ('linghuchong','令狐冲',1,'1.jpg',2,'2012-10-09',2,'2022-10-01 10:00:00','2022-10-01 10:00:00');
2). 接口方法
@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);
#{…} 里面写的名称是对象的属性。
3). 测试
@Test
public void testInsert() {
Emp emp = new Emp(null, "songyuanqiao2", null, "宋远桥2",
1, "1.jpg", 2, LocalDate.of(2012, 10, 10),
2,
LocalDateTime.now(), LocalDateTime.now());
empMapper.insert(emp);
System.out.println(emp.getId());
}
1.4.2 主键返回
- 含义:在数据添加成功后,需要获取插入数据库数据的主键。如:添加菜品口味。
@Options(keyProperty = "id", useGeneratedKeys = true)
@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);
1.5 更新
1). SQL
update emp set username = 'linghushaoxia', name = '令狐少侠', gender = 1 , image = '1.jpg' , job = 2, entrydate = '2012-01-01', dept_id = 2, update_time = '2022-10-01 12:12:12' where id = 18;
2). 接口方法
@Update("update emp set username=#{username}, name=#{name}, gender=#{gender}, image=#{image}, job=#{job}, entrydate=#{entrydate}, dept_id=#{deptId}, update_time=#{updateTime} where id=#{id}")
public void update(Emp emp);
3). 单元测试
@Test
public void testUpdate(){
Emp emp = new Emp(19,"songdaxia",null,"宋大侠2",(short)1,"2.jpg",(short)2, LocalDate.of(2012,1,1),2,null,LocalDateTime.now());
empMapper.update(emp);
}
1.6 查询
1.6.1 根据ID查询
在员工管理的页面中,当我们进行更新数据时,我们会点击 “编辑” 按钮,然后此时会发送一个请求到服务端,会根据Id查询该员工信息,用于页面回显。
那我们就需要根据ID查询员工信息。
1). SQL
select * from emp where id = 19;
2). 接口方法
@Select("select * from emp where id = #{id}")
public Emp getById(Integer id);
3). 单元测试
@Test
public void testGetById(){
Emp emp = empMapper.getById(1);
System.out.println(emp);
}
而在测试的过程中,我们会发现有几个字段(deptId、createTime、updateTime)是没有封装上的。
1.6.2 数据封装
我们看到查询返回的结果中大部分字段是有值的,但是deptId,createTime,updateTime这几个字段是没有值的,而数据库中是有对应的字段值的,这是为什么呢?
原因如下:
-
实体类属性名 和 数据库表查询返回的字段名一致,mybatis会自动封装为实体类。
-
如果实体类属性名 和 数据库表查询返回的字段名不一致,不能自动封装。
可以通过以下方案解决:
1). 起别名:在SQL语句中,对不一样的列名起别名,别名和实体类属性名一样。
@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 进行手动结果映射。
@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). 开启驼峰命名(推荐):如果字段名与属性名符合驼峰命名规则,mybatis会自动通过驼峰命名规则映射。
#开启驼峰命名自动映射,即从经典数据库列名 a_column 映射到经典 Java 属性名 aColumn。
mybatis.configuration.map-underscore-to-camel-case=true
要使用驼峰命名前提是 实体类的属性 与 数据库表中的字段名严格遵守驼峰命名。
1.6.3 条件查询
在员工管理的列表页面中,我们需要根据条件查询员工信息,查询条件包括:姓名、性别、入职时间。
通过页面原型以及需求描述,我们可以看出:
- 姓名:要求支持模糊匹配
- 性别:要求精确匹配
- 入职时间:要求进行范围查询
并且要求根据最后修改时间进行倒序排序。
- SQL:
select * from emp where name like '%张%' and gender = 1 and entrydate between '2010-01-01' and '2020-01-01' order by update_time desc;
- 接口方法:
方式一:
@Select("select * from emp where name like '%${name}%' and gender = #{gender} and entrydate between #{begin} and #{end} order by update_time desc")
public List<Emp> list(@Param("name")String name, @Param("gender")Short gender , @Param("begin")LocalDate begin ,@Param("end")LocalDate end);
这种方式使用 $ 进行字符串拼接,这种方式呢,由于是字符串拼接,并不是预编译的形式,所以效率不高、且存在sql注入风险。
方式二:
@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(@Param("name")String name, @Param("gender")Short gender , @Param("begin")LocalDate begin ,@Param("end")LocalDate end);
这种方式,生成的SQL都是预编译的SQL语句。 性能高、安全。 推荐方式二
@Test
public void list(){
List<Emp> list = empMapper.list("张", 1,
LocalDate.of(2010, 1, 1), LocalDate.of(2020, 1, 1));
System.out.println(list);
}
1.6.4 参数名说明
在springBoot的2.x版本之后,springBoot的父工程对compiler编译插件进行了默认的参数 parameters 配置,使得在编译时,会在字节码文件中保留原方法形参的名称,所以#{…}里面可以直接通过形参名获取对应的值。
在springBoot的1.x版本中,所有接口方法形参编译后的为var1、var2 …,此时如果有多个参数,就需要通过@Param注解来指定SQL语句中的参数名。
1.6.5 问题分析
在页面原型中,列表上方的条件是动态的,是可以不传递的,可以只是传递其中的一个 或者 两个 或者 三个。
而在我们刚才编写的SQL语句中,我们会看到,我们将三个条件直接写死了。 如果页面只传递了参数姓名 name 字段,其他两个字段 性别 和 入职时间没有传递,那么这两个参数的值就是null。
此时,执行的SQL语句为:
这个查询结果是不正确的。正确的做法应该是,传递了参数,再组装这个查询条件。如果没有传递参数,就不应该组装这个查询条件。
就比如,如果姓名输入了:“张”, 对应的SQL为:
select * from emp where name like '%张%' order by update_time desc;
如果如果姓名输入了:“张”, 性别选择了: 男, 则对应的SQL为:
select * from emp where name like '%张%' and gender = 1 order by update_time desc;
那这种:SQL语句会随着用户的输入或外部条件的变化而变化,我们称为 动态SQL。
2. Mybatis动态SQL
2.1 XML映射文件
使用Mybatis的注解,主要是来完成一些简单的增删改查功能。如果需要实现复杂的SQL功能,建议使用XML来配置映射语句,也就是将SQL语句写在XML配置文件中。
官方说明:
SQL映射配置文件的是需要符合一定的规范的:
-
XML映射文件的名称与Mapper接口名称一致,并且将XML映射文件和Mapper接口放置在相同包下。(同包同名)
-
XML映射文件的namespace属性为Mapper接口全限定名一致。
-
XML映射文件中sql语句的id与Mapper 接口中的方法名一致,并保持参数类型和返回值类型一致。
<select>标签的resultType属性,指的是查询返回的单条记录所封装的类型。
2.2 if
<if>:用于判断条件是否成立,使用test属性进行条件判断,如果条件为true,则拼接SQL。
1.4.1 条件查询
接下来,我们就通过标签来改造上述的条件查询。
- EmpMapper接口方法
public List<Emp> list(String name, Short gender , LocalDate begin ,LocalDate end);
- EmpMapper.xml
<select id="list" resultType="com.itheima.pojo.Emp">
select id, username, password, name, gender, image, job, entrydate, dept_id, create_time, update_time 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>
order by update_time desc
</select>
然后我们就可以进行测试了,我们可以传递不同的条件,然后通过日志查看最终组装执行的SQL语句。
进行测试之后我们发现,上述的SQL语句的配置是有问题的。
A. 如果,我们没有传递第一个参数,此时输出的SQL语句中,我们会看到多了一个where 之后多了一个and。 如下所示:
-
测试代码
@Test public void testList(){ List<Emp> empList = empMapper.list(null, (short)1, null,null); for (Emp emp : empList) { System.out.println(emp); } }
-
日志
B. 如果,我们没有传递任何参数,此时就代表要查询全部,但是输出的SQL语句中,会多一个where。如下所示:
在Mybatis中给我提供了另外一个标签, 该标签具有如下作用:
<where>:where 元素只会在子元素有内容的情况下才插入where子句。而且会自动去除子句的开头的AND 或OR。
通过标签改造上述功能, 改造后的配置为:
<select id="list" resultType="com.itheima.pojo.Emp">
select id, username, password, name, gender, image, job, entrydate, dept_id, create_time, update_time 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>
1.4.2 更新员工
完善更新员工功能,修改为动态更新员工数据信息。
需求:动态更新员工信息,如果字段有值,则更新;如果字段没有值,则不更新。
解决方案:动态SQL。
-
接口方法
public void update(Emp emp);
-
EmpMapper.xml
<update id="update"> update emp set <if test="username != null"> username=#{username}, </if> <if test="name != null"> name=#{name}, </if> <if test="gender != null"> gender=#{gender}, </if> <if test="image != null"> image=#{image}, </if> <if test="job != null"> job=#{job}, </if> <if test="entrydate != null"> entrydate=#{entrydate}, </if> <if test="deptId != null"> dept_id=#{deptId}, </if> <if test="updateTime != null"> update_time=#{updateTime} </if> where id=#{id} </update>
-
测试
@Test public void testUpdate2(){ Emp emp = new Emp(); emp.setId(19); emp.setUsername("syq"); emp.setUpdateTime(LocalDateTime.now()); empMapper.update(emp); }
查看到最终输出的SQL为:
但是,假如我们在更新时,没有指定updateTime字段。 也就意味着,在更新时,不需要更新该字段,我们再一起来看看输出的SQL语句。
测试代码:
@Test
public void testUpdate2(){
Emp emp = new Emp();
emp.setId(19);
emp.setUsername("syq");
empMapper.update(emp);
}
输出的SQL语句为:
在输出的SQL语句末尾出现了一个多余的逗号 “,”,造成SQL语句语法错误报错。 而要解决这个问题呢,我们可以通过 标签来解决。
<set>:动态地在行首插入 SET 关键字,并会删掉额外的逗号。(用在update语句中)
可以标签对上述的XML配置进行优化:
<update id="update">
update emp
<set>
<if test="username != null">
username=#{username},
</if>
<if test="name != null">
name=#{name},
</if>
<if test="gender != null">
gender=#{gender},
</if>
<if test="image != null">
image=#{image},
</if>
<if test="job != null">
job=#{job},
</if>
<if test="entrydate != null">
entrydate=#{entrydate},
</if>
<if test="deptId != null">
dept_id=#{deptId},
</if>
<if test="updateTime != null">
update_time=#{updateTime}
</if>
</set>
where id=#{id}
</update>
2.3 foreach
对于员工的删除功能,既支持删除单条记录,又支持批量删除。
1). SQL语句
delete from emp where id in (1,2,3);
2). 接口方法
//批量删除
public void deleteByIds(List<Integer> ids);
3). XML映射文件
<delete id="deleteByIds">
delete from emp where id in
<foreach collection="ids" item="id" separator="," open="(" close=")">
#{id}
</foreach>
</delete>
foreach属性介绍:
collection:集合名称
item:集合遍历出来的元素/项
separator:每一次遍历使用的分隔符
open:遍历开始前拼接的片段
close:遍历结束后拼接的片段
2.4 sql/include
- 问题分析:
在xml映射文件中配置的SQL,有时可能会存在很多重复的片段,此时就会存在很多冗余的代码。如下图这样:
我们可以考虑对重复的代码片段进行抽取,将其通过 标签封装到一个SQL片段 。 如下:
<sql id="commonSelect">
select id, username, password, name, gender, image, job, entrydate, dept_id, create_time, update_time from emp
</sql>
然后在原来抽取的地方进行引用,通过 标签。如下:
<select id="list" resultType="com.itheima.pojo.Emp">
<include refid="commonSelect"/>
<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>
标签含义:
<sql>:定义可重用的 SQL 片段。
<include>:通过属性refid,指定包含的sql片段。