Mybatis基本操作
一、环境准备
![image-20230507112959151](https://picgoi.oss-cn-qingdao.aliyuncs.com/javaweb/20230507113000.png)
数据库表和springboot工程在课程资料中都有提供
注意,entrydate是用的jdk1.8中的LocalDate类型,createTime用的是jdk1.8中的LocalDateTime类型
二、删除
Mybatis中提供了一种参数占位符 #{id}
![image-20230507121304062](https://picgoi.oss-cn-qingdao.aliyuncs.com/javaweb/20230507121306.png)
empMapper.java
package com.itheima.mapper;
import org.apache.ibatis.annotations.*;
@Mapper
public interface EmpMapper {
@Delete("delete from emp where id=#{id}")
public void delete(Integer id);
//其实delete是有返回值的,返回删除的行数,不过一般不需要
// public int delete(Integer id);
}
测试方法
@SpringBootTest
class SpringbootMybatisCrudApplicationTests {
@Autowired
public EmpMapper empMapper;
@Test
public void testDelete(){
// int i = empMapper.delete(17);
// System.out.println(i);
empMapper.delete(17);
}
}
三、删除(预编译SQL)
![image-20230507122711651](https://picgoi.oss-cn-qingdao.aliyuncs.com/javaweb/20230507122712.png)
![image-20230507123317236](https://picgoi.oss-cn-qingdao.aliyuncs.com/javaweb/20230507123318.png)
![image-20230507123441956](https://picgoi.oss-cn-qingdao.aliyuncs.com/javaweb/20230507123443.png)
预编译SQL的优势—性能更高
![image-20230507123500769](https://picgoi.oss-cn-qingdao.aliyuncs.com/javaweb/20230507123503.png)
预编译SQL的优势—防止SQL注入
![image-20230507124712481](https://picgoi.oss-cn-qingdao.aliyuncs.com/javaweb/20230507124713.png)
![image-20230507124824431](https://picgoi.oss-cn-qingdao.aliyuncs.com/javaweb/20230507124825.png)
mybatis提供了2个参数占位符
参数占位符(面试题)
![image-20230507125035627](https://picgoi.oss-cn-qingdao.aliyuncs.com/javaweb/20230507125037.png)
用${id}就不会生成预编译SQL,性能低,不安全,一般不用
![image-20230507125226350](https://picgoi.oss-cn-qingdao.aliyuncs.com/javaweb/20230507125228.png)
小结
![image-20230507125340880](https://picgoi.oss-cn-qingdao.aliyuncs.com/javaweb/20230507125342.png)
四、新增
![image-20230507131920444](https://picgoi.oss-cn-qingdao.aliyuncs.com/javaweb/20230507131921.png)
![image-20230507131939953](https://picgoi.oss-cn-qingdao.aliyuncs.com/javaweb/20230507131940.png)
首先要会写SQL语句:
像这种参数多的,推荐多个参数封装到一个对象中,这样维护比较方便,也便于阅读
1、在empMapper接口中添加,#{Emp对象的属性名}
@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);
![image-20230507133653679](https://picgoi.oss-cn-qingdao.aliyuncs.com/javaweb/20230507133654.png)
2、测试
@Test
public void testInsert(){
// 创建Emp对象
Emp emp = new Emp();
emp.setUsername("Tom");
emp.setName("汤姆");
emp.setGender((short)1);
emp.setImage("1.jpg");
emp.setJob((short)1);
emp.setDeptId(1);
emp.setEntrydate(LocalDate.now());
emp.setCreateTime(LocalDateTime.now());
emp.setUpdateTime(LocalDateTime.now());
empMapper.insert(emp);
}
![image-20230507133910151](https://picgoi.oss-cn-qingdao.aliyuncs.com/javaweb/20230507133911.png)
![image-20230507135754194](https://picgoi.oss-cn-qingdao.aliyuncs.com/javaweb/20230507135755.png)
五、新增(主键返回)
![image-20230507140829974](https://picgoi.oss-cn-qingdao.aliyuncs.com/javaweb/20230507140830.png)
![image-20230507141115229](https://picgoi.oss-cn-qingdao.aliyuncs.com/javaweb/20230507141116.png)
@Options(keyProperty = "id",useGeneratedKeys = true)//获取返回的主键,keyProperty是将返回的主键往哪个emp的属性封装
@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);
![image-20230507141132297](https://picgoi.oss-cn-qingdao.aliyuncs.com/javaweb/20230507141133.png)
![image-20230507141145213](https://picgoi.oss-cn-qingdao.aliyuncs.com/javaweb/20230507141146.png)
六、更新(根据主键修改员工信息)
![image-20230507141815461](https://picgoi.oss-cn-qingdao.aliyuncs.com/javaweb/20230507141816.png)
1、先写SQL语句
-- 更新员工信息
update emp set username='',name='',gender='',image='',job='',entrydate='',dept_id='',update_time=''
where id=1;
2、添加mapper接口方法
@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);
![image-20230507162430099](https://picgoi.oss-cn-qingdao.aliyuncs.com/javaweb/20230507162431.png)
3、测试
@Test
public void testUpdate(){
// 创建Emp对象
Emp emp = new Emp();
emp.setId(21);
emp.setUsername("Tom3");
emp.setName("汤姆3");
emp.setGender((short)1);
emp.setImage("2.jpg");
emp.setJob((short)1);
emp.setDeptId(1);
emp.setEntrydate(LocalDate.of(2012-10-10,12,10));
emp.setUpdateTime(LocalDateTime.now());
empMapper.update(emp);
}
![image-20230507162014500](https://picgoi.oss-cn-qingdao.aliyuncs.com/javaweb/20230507162015.png)
七、查询(根据ID)
1、先写SQL语句
-- 根据ID查询员工信息
select * from emp where id=1;
2、Mapper接口方法
// 查询
@Select("select * from emp where id=#{id}")
public Emp getById(Integer id);
3、测试
//查询
@Test
public void select(){
Emp emp = empMapper.getById(2);
System.out.println(emp);
}
![image-20230507164124777](https://picgoi.oss-cn-qingdao.aliyuncs.com/javaweb/20230507164131.png)
![image-20230507164218691](https://picgoi.oss-cn-qingdao.aliyuncs.com/javaweb/20230507164219.png)
但是deptId后面的字段都是null,但是数据库中确实有这些数据
Mybatis的封装机制
![image-20230507164502427](https://picgoi.oss-cn-qingdao.aliyuncs.com/javaweb/20230507164503.png)
方法一
// 起别名:在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 进行手动结果映射。
@Select("select * from emp where id=#{id}")
@Results({
@Result(column = "dept_id",property = "deptId"),
@Result(column = "create_time", property = "createTime"),
@Result(column = "update_time", property = "updateTime")
})
public Emp getById(Integer id);
方法三
![image-20230507171000347](https://picgoi.oss-cn-qingdao.aliyuncs.com/javaweb/20230507171002.png)
查询语句保持不变
![image-20230507171113437](https://picgoi.oss-cn-qingdao.aliyuncs.com/javaweb/20230507171115.png)
类的属性名和数据库查询返回的字段名不一致,就不能自动封装,返回值结果为null,如何解决?
![image-20230507171247480](https://picgoi.oss-cn-qingdao.aliyuncs.com/javaweb/20230507171248.png)
八、查询(条件查询)
1、写SQL语句
-- 条件查询
select * from emp where name like '%张%' and gender=1 and entrydate between '2010-01-01' and '2020-01-01';
2、Mapper创建方法,因为’'之间是字符串,#{name}是不能出现在引号之间的也就是?,所以一种方法是用${name}进行拼接。
// 条件查询
@Select("select * from emp where name like '%${name}%' and gender=#{gender} and entrydate between #{begin} and #{end}")
public List<Emp> select(String name, short gender, LocalDate begin,LocalDate end);
还有一种方法是用MySQL提供的函数concat,用来拼接字符串
@Select("select * from emp where name like concat('%',#{name},'%') and gender=#{gender} and entrydate between #{begin} and #{end}")
public List<Emp> select(String name, short gender, LocalDate begin,LocalDate end);
![image-20230507183737647](https://picgoi.oss-cn-qingdao.aliyuncs.com/javaweb/20230507183738.png)
3、测试
@Test
public void select(){
List<Emp> empList = empMapper.select("张", (short) 1, LocalDate.of(2010, 1, 1), LocalDate.of(2020, 1, 1));
System.out.println(empList);
}
![image-20230507184106194](https://picgoi.oss-cn-qingdao.aliyuncs.com/javaweb/20230507184107.png)
![image-20230507184127461](https://picgoi.oss-cn-qingdao.aliyuncs.com/javaweb/20230507184128.png)
![image-20230507184310290](https://picgoi.oss-cn-qingdao.aliyuncs.com/javaweb/20230507184311.png)
九、XML映射文件
配置SQL语句,①通过注解配置(已经学完了)。②通过XML文件配置
1、同包同名
![image-20230508152217214](https://picgoi.oss-cn-qingdao.aliyuncs.com/javaweb/20230508152218.png)
![image-20230508152803587](https://picgoi.oss-cn-qingdao.aliyuncs.com/javaweb/20230508152804.png)
网页搜索mybatis中文网
![image-20230508153105388](https://picgoi.oss-cn-qingdao.aliyuncs.com/javaweb/20230508153107.png)
复制约束条件
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
2、namespace属性与mapper接口的全限定名一致
![image-20230508153514570](https://picgoi.oss-cn-qingdao.aliyuncs.com/javaweb/20230508153515.png)
![image-20230508153544113](https://picgoi.oss-cn-qingdao.aliyuncs.com/javaweb/20230508153545.png)
3、xml映射文件中的sql语句中的id与mapper接口中的方法名一致,返回值类型一致(全限定名)。
![image-20230508155356345](https://picgoi.oss-cn-qingdao.aliyuncs.com/javaweb/20230508155357.png)
![image-20230508152436122](https://picgoi.oss-cn-qingdao.aliyuncs.com/javaweb/20230508152437.png)
为什么有这样的规范?要不然mybatis凭什么找到对应的xml文件以及该接口方法对应的sql语句
![image-20230508161540454](https://picgoi.oss-cn-qingdao.aliyuncs.com/javaweb/20230508161542.png)
![image-20230508162047644](https://picgoi.oss-cn-qingdao.aliyuncs.com/javaweb/20230508162048.png)
官方说明:https://mybatis.net.cn/getting-started.html