Mybatis @Insert @Delete @Update @Select
Mybatis用法
基础操作 - 删除
delete 传参
@Mapper
public interface EmpMapper {
// 根据ID动态删除数据
@Delete("delete from emp where id = #{id}") // Mybatis提供的参数占位符 #{param}
public void delete(Integer id);
}
SpringbootMybatisCrudApplicationTests 测试类删除
@SpringBootTest
class SpringbootMybatisCrudApplicationTests {
@Autowired
private EmpMapper empMapper;
@Test
void testDelete() {
empMapper.delete(16);
}
}
预编译SQL
application.properties
#配置mybatis日志输出位置,输出到控制台
mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl
再启动测试类,控制台就会输出对应日志,这就叫做预编译SQL
==> Preparing: delete from emp where id = ?
==> Parameters: 15(Integer)
<== Updates: 0
基础操作 - 插入
Insert 插入
// 新增员工
@Insert("insert into emp(username, name, gender, image, job, entrydate, dept_id, create_time, update_time)\n" +
" values (#{username}, #{name}, #{gender}, #{image}, #{job}, #{entrydate}, #{deptId}, #{createTime}, #{updateTime})")
void insert(Emp emp);
SpringbootMybatisCrudApplicationTests 测试类插入对象
@Test
void testInsert() {
// 构造员工对象
Emp emp = new Emp();
emp.setUsername("Tom");
emp.setName("汤姆");
emp.setImage("1.jpg");
emp.setGender((short) 1);
emp.setJob((short) 1);
emp.setEntrydate(LocalDate.of(2005, 1, 1));
emp.setCreateTime(LocalDateTime.now());
emp.setUpdateTime(LocalDateTime.now());
emp.setDeptId(1);
// 调用员工Mapper接口的insert方法
empMapper.insert(emp);
}
主键返回
// 新增员工
@Options(useGeneratedKeys = true, keyProperty = "id")
@Insert("insert into emp(username, name, gender, image, job, entrydate, dept_id, create_time, update_time)\n" +
" values (#{username}, #{name}, #{gender}, #{image}, #{job}, #{entrydate}, #{deptId}, #{createTime}, #{updateTime})")
void insert(Emp emp);
// 测试类
@Test
void testInsert() {
// 构造员工对象
Emp emp = new Emp();
emp.setUsername("Tom2");
emp.setName("汤姆2");
emp.setImage("1.jpg");
emp.setGender((short) 1);
emp.setJob((short) 1);
emp.setEntrydate(LocalDate.of(2005, 1, 1));
emp.setCreateTime(LocalDateTime.now());
emp.setUpdateTime(LocalDateTime.now());
emp.setDeptId(1);
// 调用员工Mapper接口的insert方法
empMapper.insert(emp);
System.out.println(emp.getId());
}
@Options(useGeneratedKeys = true, keyProperty = "id")
注解是 MyBatis 框架中的一个注解,它用于 MyBatis 映射器方法上,其目的是在执行 insert 操作后,能够将数据库生成的主键值回写到之前插入数据的实体对象中。解释这个注解的各部分:
useGeneratedKeys
: 这个属性设为 true,表示我们希望使用数据库自动生成的键值(例如:自动递增的 ID)。keyProperty
: 该属性指定了哪一个属性或字段应该被填充。通常,这个属性会被设置为实体类中代表主键的属性名。
基础操作 - 更新
UPDATE 更新
// 更新员工
@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}")
void update(Emp emp);
SpringbootMybatisCrudApplicationTests 测试类更新对象
// 更新员工
@Test
void testUpdate() {
// 构造员工对象
Emp emp = new Emp();
emp.setId(1);
emp.setUsername("TomTOPONE");
emp.setName("汤姆1111111");
emp.setImage("1.jpg");
emp.setGender((short) 1);
emp.setJob((short) 1);
emp.setEntrydate(LocalDate.of(2000, 1, 1));
emp.setUpdateTime(LocalDateTime.now());
emp.setDeptId(1);
// 调用员工Mapper接口的update方法
empMapper.update(emp);
}
基础操作 - 查询
SELECT 查询
// 根据Id查询员工
@Select("select * from emp where id = #{id}")
Emp getById(Integer id);
SpringbootMybatisCrudApplicationTests 测试类查询对象
// 根据 ID 查询员工
@Test
void testGetbyId() {
Emp emp = empMapper.getById(19);
System.out.println(emp);
}
但是发现有的数据没被封装进来,可是数据都是有值的
Mybatis的数据封装
实体类属性名 和 数据库表查询返回的字段名一致,Mybatis会自动封装
如果实体类属性名 和 数据库表查询返回的字段名不一致,不能自动封装
比如我们的实例类和SQL表中的字段不一样
1. 给字段起别名
// 给字段起别名
不一样的字段为 dept_id create_time update_time,类中字段为驼峰,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}")
Emp getById(Integer id);
2. 通过@Results,@Results注释手动映射封装
@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}")
Emp getById(Integer id);
3. 开启Mybatis的驼峰命名自动映射开关(经典最终方案)
application.properties
中定义 mybatis.configuration.map-underscore-to-camel-case=true
再使用
// 根据Id查询员工
@Select("select * from emp where id = #{id}")
Emp getById(Integer id);
基础操作 - 条件查询
根据需求查询员工信息
- 根据输入的
员工姓名
、员工性别
、入职时间
搜索满足条件的员工信息- 其中 员工姓名
支持模糊匹配
; 性别进行精确査询
; 入职时间进行范围查询
- 支持分页查询
- 并对查询的结果,根据最后修改时间进行倒序排序
mysql> SELECT * FROM emp WHERE name LIKE '%张%'AND gender = 1 AND entrydate BETWEEN '2010-01-01' AND '2020-01-01' ORDER BY update_time DESC;
+----+-----------+----------+--------+--------+-------+------+------------+---------+---------------------+---------------------+
| id | username | password | name | gender | image | job | entrydate | dept_id | create_time | update_time |
+----+-----------+----------+--------+--------+-------+------+------------+---------+---------------------+---------------------+
| 2 | zhangwuji | 123456 | 张无忌 | 1 | 2.jpg | 2 | 2015-01-01 | 2 | 2024-03-25 10:06:22 | 2024-03-25 10:06:22 |
+----+-----------+----------+--------+--------+-------+------+------------+---------+---------------------+---------------------+
1 row in set (0.01 sec)
EmpMapper.java
// 条件查询员工信息
// 由于like模糊匹配需要在关键字加`%`,所以使用到了 `$`,因为它是字符串拼接符号
@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(String name, Short gender, LocalDate begin, LocalDate end);
Test
@Test
void testList() {
List<Emp> list = empMapper.list("张", (short)1, LocalDate.of(2010, 1, 1), LocalDate.of(2020, 1, 1));
System.out.println(list);
}
cmd
==> Preparing: SELECT * FROM emp WHERE name LIKE '%张%' AND gender = ? AND entrydate BETWEEN ? AND ? ORDER BY update_time DESC
==> Parameters: 1(Short), 2010-01-01(LocalDate), 2020-01-01(LocalDate)
<== Columns: id, username, password, name, gender, image, job, entrydate, dept_id, create_time, update_time
<== Row: 2, zhangwuji, 123456, 张无忌, 1, 2.jpg, 2, 2015-01-01, 2, 2024-03-25 10:06:22, 2024-03-25 10:06:22
<== Total: 1
函数 concat
如果使用的是
$
符号拼接,则不是预编译SQL,会存在性能低,不安全,存在SQL注入问题
mysql> SELECT CONCAT("hello", "java", "new bee");
+------------------------------------+
| CONCAT("hello", "java", "new bee") |
+------------------------------------+
| hellojavanew bee |
+------------------------------------+
1 row in set (0.01 sec)
利用 concat
拼接 name like
@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);
XML映射文件
XML
映射文件的名称与Mapper
接口名称一致,并且将XML
映射文件和Mapper
接口放置在相同包下(同包同名)
XML
映射文件的namespace
属性为Mapper
接口全限定名一致。
XML
映射文件中sql
语句的id
与Mapper
接口中的方法名一致,并保持返回类型一致
EmpMapper.java
// 通过xml配置
public List<Emp> list(String name, Short gender, LocalDate begin, LocalDate end);
EmpMapper.xml
<?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">
<mapper namespace="com.itheima.mapper.EmpMapper">
<!-- resultType = 单条记录所封装的类型 -->
<select id="list" resultType="com.itheima.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>
运行测试类方法
@Test
void testList() {
List<Emp> list = empMapper.list("张", (short)1, LocalDate.of(2010, 1, 1), LocalDate.of(2020, 1, 1));
System.out.println(list);
}
==> Preparing: SELECT * FROM emp WHERE name LIKE CONCAT('%', ?, '%') AND gender = ? AND entrydate BETWEEN ? AND ? ORDER BY update_time DESC
==> Parameters: 张(String), 1(Short), 2010-01-01(LocalDate), 2020-01-01(LocalDate)
<== Columns: id, username, password, name, gender, image, job, entrydate, dept_id, create_time, update_time
<== Row: 2, zhangwuji, 123456, 张无忌, 1, 2.jpg, 2, 2015-01-01, 2, 2024-03-25 10:06:22, 2024-03-25 10:06:22
<== Total: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5d512ddb]
[Emp(id=2, username=zhangwuji, password=123456, name=张无忌, gender=1, image=2.jpg, job=2, entrydate=2015-01-01, deptId=2, createTime=2024-03-25T10:06:22, updateTime=2024-03-25T10:06:22)]
使用注解来映射简单语句会使代码显得更加简洁,但对于稍微复杂一点的语句,Java 注解不仅力不从心,还会让你本就复杂的 SQL 语句更加混乱不堪。 因此,如果你需要做一些很复杂的操作,最好用 XML 来映射语句。
选择何种方式来配置映射,以及认为是否应该要统一映射语句定义的形式,完全取决于你和你的团队。 换句话说,永远不要拘泥于一种方式,你可以很轻松的在基于注解和 XML 的语句映射方式间自由移植和切换。
动态SQL
if where 标签
<?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">
<mapper namespace="com.itheima.mapper.EmpMapper">
<!-- resultType = 单条记录所封装的类型 -->
<!-- <select id="list" resultType="com.itheima.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>-->
<select id="list" resultType="com.itheima.pojo.Emp">
SELECT *
FROM emp
<!-- <where> 标签的作用是用于处理 WHERE 子句中的条件连接,它可以自动添加适当的连接词(如 AND 或 OR),并且在必要时会省略不必要的连接词,使得生成的 SQL 语句更加清晰和简洁 -->
<where>
<!-- <if> 标签的作用是根据表达式(通常是判断条件)来确定是否在生成的 SQL 语句中包含该标签内的内容 -->
<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>
</mapper>
if 案例
// 更新员工
@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}")
void update(Emp emp);
// 动态更新员工 更新ID为 18的员工, username更新为 Tom111,name更新为 汤姆111,gender更新为 2
@Test
void testUpdate2() {
// 构造员工对象
Emp emp = new Emp();
emp.setId(18);
emp.setUsername("Tom111");
emp.setName("汤姆111");
emp.setGender((short) 2);
emp.setUpdateTime(LocalDateTime.now());
// 调用员工Mapper接口的update方法
empMapper.update(emp);
}
若执行后,
sql
表为
因为每次更新都重置了字段,所以为null
==> Preparing: update emp set username = ?, name = ?, gender = ?, image = ?, job = ?, entrydate = ?, dept_id = ?, update_time = ? where id = ?
==> Parameters: Tom111(String), 汤姆111(String), 2(Short), null, null, null, null, 2024-05-28T10:43:45.183911200(LocalDateTime), 18(Integer)
<== Updates: 1
更改 更新员工 方法
EmpMapper.java
// 更新员工
// @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}")
// void update(Emp emp);
void update2(Emp emp);
使用 动态sql
<?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">
<mapper namespace="com.itheima.mapper.EmpMapper">
<!-- 动态更新员工信息-->
<update id="update2">
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>
</mapper>
SpringbootMybatisCrudApplicationTests.java
// 动态更新员工
@Test
void testUpdate2() {
// 构造员工对象
Emp emp = new Emp();
emp.setId(19);
emp.setUsername("Tom222");
emp.setName("汤姆222");
emp.setGender((short) 1);
emp.setUpdateTime(LocalDateTime.now());
// 调用员工Mapper接口的update方法
empMapper.update2(emp);
}
运行测试类后
==> Preparing: update emp set username = ?, name = ?, gender = ?, update_time = ? where id = ?
==> Parameters: Tom222(String), 汤姆222(String), 1(Short), 2024-05-28T10:58:59.103973(LocalDateTime), 19(Integer)
<== Updates: 1
只更新username
// 动态更新员工
@Test
void testUpdate2() {
// 构造员工对象
Emp emp = new Emp();
emp.setId(19);
emp.setUsername("Tom22233");
// emp.setName("汤姆222");
// emp.setGender((short) 1);
// emp.setUpdateTime(LocalDateTime.now());
// 调用员工Mapper接口的update方法
empMapper.update2(emp);
}
sql语法错误:near dept_id = ?..
sql执行发现 where id 前面多了一个 ,
解决:
<?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">
<mapper namespace="com.itheima.mapper.EmpMapper">
<!-- 动态更新员工信息-->
<update id="update2">
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>
</mapper>
set 标签会去除掉字段之后多余的
,
foreach 标签
删除
id
为18
、19
、20
的三条数据
DELETE FROM emp WHERE id IN(18, 19, 20);
通过 mybatis 完成批量删除
EmpMapper.java
// 批量删除员工
public void deleteByIds(List<Integer> ids);
EmpMapper.xml
<!-- 批量删除员工的操作 (18, 19, 20)-->
<delete id="deleteByIds">
DELETE FROM emp WHERE id IN
<!-- collection: 要遍历的集合
item: 遍历出来的每一项
separator: 分隔符
open: 遍历开始前拼接的sql片段
close: 遍历结束后拼接的sql片段
-->
<foreach collection="ids" item="id" separator="," open="(" close=")">
#{id}
</foreach>
</delete>
测试类
// 批量删除员工 11 12 13
@Test
void testDeleteByIds() {
List<Integer> ids = Arrays.asList(11, 12, 13);
empMapper.deleteByIds(ids);
}
==> Preparing: DELETE FROM emp WHERE id IN ( ? , ? , ? )
==> Parameters: 11(Integer), 12(Integer), 13(Integer)
<== Updates: 3
sql、include 标签
sql 片段的抽取和引用
<!-- 定义可重用的 sql片段 -->
<?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">
<mapper namespace="com.itheima.mapper.EmpMapper">
<!-- 定义可重用的sq片段 -->
<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">
<!-- 通过refid,指定包含的sql片段 -->
<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>
<!-- 动态更新员工信息-->
<update id="update2">
update emp
... 省略代码
where id = #{id}
</update>
<!-- 批量删除员工的操作 (18, 19, 20)-->
<delete id="deleteByIds">
DELETE FROM emp WHERE id IN
<!-- collection: 要遍历的集合
item: 遍历出来的每一项
separator: 分隔符
open: 遍历开始前拼接的sql片段
close: 遍历结束后拼接的sql片段
-->
<foreach collection="ids" item="id" separator="," open="(" close=")">
#{id}
</foreach>
</delete>
</mapper>