一、Mybatis
准备
// User对象信息
@Data
@NoArgsConstructor
@AllArgsConstructor
public class User {
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;// dept_id
private LocalDateTime createTime;// create_time
private LocalDateTime updateTime;// update_time
}
1.1删除操作
数据库语句
delect form table where id = ?
接口方法
@Mapper
public interface EmpMapper {
@Delete("delete from emp where id = #{id}") // #{}
public int delete(Integer id); // 成功返回1
}
参数占位符
- #{…}——会预编译SQL,参数替换为?,自动设置参数(参数传递时)
- ${…}——直接拼接SQL,存在SQL注入问题(表操作动态设置时)
1.2新增操作
数据库语句
insert into emp(username, name, gender, image, job, entrydate, dept_id, create_time, update_time)
values ('Tom', '汤姆', 1, '1.jpg',1,'2005-01-01',1,now(),now());
接口方法
@Mapper
public interface EmpMapper {
@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});")
public void insert(User user); // 对象包装
}
新增(主键返回):数据添加成功后返回主键的id
@Options(keyProperty = "id",useGeneratedKeys = true) // 将自动生成的主键值id赋值给emp对象
@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});")
public void insert(User user);
1.3更新操作
数据库语句
update emp set username = '', name = '', gender = '', image = '', job = '', entrydate = '', dept_id = '',update_time = '' where id = 1
接口方法
@Mapper
public interface EmpMapper {
@Update("update emp set username = #{username},name = #{name},gender = #{gender} ,image = #{image},job = #{job},update_time = #{updateTime}" +
"where id = #{id}")
public void update(User user);
}
1.4.1单个查询操作
数据库语句
select * from emp where id = 18
接口方法
@Mapper
public interface EmpMapper {
@Select("select * from emp where id = #{id}")
public User GetById(Integer id);
}
- 如果实体类中的属性名和数据库查询返回字段名不一致,不能自动封装
解决方案一:给字段起别名
@Select("select username, name, gender, image, job, entrydate, dept_id deptId, create_time createTime, update_time updateTime from emp where id = #{id}")
public User GetById(Integer id);
解决方案二:@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 User GetById(Integer id);
解决方案三:开启mybatis驼峰命名自动映射开关
# 必须按照驼峰命名规则映射 数据库字段a_column -> 属性名aCloumn
mybatis.configuration.map-underscore-to-camel-case=true
1.4.2条件查询操作
数据库语句
select * from emp where name like '%张%' and gender = 1 order by update_time desc;
接口方法
@Mapper
public interface EmpMapper {
@Select("select * from emp where name like '%${name}%' and gender = #{gender} order by update_time desc")
public List<User> list(String name,Short gender);
}
- 这里只能用*${}来拼接是因为字符串有字符串模糊查询是不能出现#{}的,在预编出变成?*,问号是不能出现在字符串里面的
可以使用数据库concat()
函数来实现字符串拼接
数据库语句
select * from emp where name like concat('%','张','%') and gender = 1 order by update_time desc;
接口方法
@Mapper
public interface EmpMapper {
@Select("select * from emp where name like concat('%',#{name},'%') and gender = #{gender} order by update_time desc")
public List<User> list(String name,Short gender);
}
1.5配置XML文件配置SQL句
配置文件规范
- XML映射文件的名称与Mapper接口名称一致,并且将XML映射文件和Mapper接口放置在相同包下(同包同名)。
- XML映射文件的namespace属性为Mapper接口权限定名一致。
- XML映射文件中sql语句的id与Mapper接口中的方法名一致,并保持返回类型一致。
Mapper接口
@Mapper
public interface EmpMapper {
public List<User> list(String name,Short gender);
}
XML映射文件
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="whopxx.start.mapper.EmpMapper">
<select id="list" resultType="whopxx.start.pojo.User">
select *
from emp
where name like concat('%', #{name}, '%')
and gender = #{gender}
order by update_time desc
</select>
</mapper>
使用Mvbatis的注解,主要是来完成一些简单的增删改查功能。如果需要实现复杂的SOL功能,建议使用XML来配置映射
1.6动态SQL
<if>:用于判断条件是否成立。使用test属性进行条件判断,如果条件为true,则拼接SQL
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="whopxx.start.mapper.EmpMapper">
<select id="list" resultType="whopxx.start.pojo.User">
select *
from emp
where
<if test="name != null">
name like concat('%', #{name}, '%')
</if>
<if test="gender != null">
and gender = #{gender}
</if>
order by update_time desc
</select>
</mapper>
这种方式无法解决and语句拼接出错
<where>:where元素只会在子元素有内容的情况下才插入where子句。且会自动去除子句开头的AND或OR。
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="whopxx.start.mapper.EmpMapper">
<select id="list" resultType="whopxx.start.pojo.User">
select *
from emp
<where>
<if test="name != null">
name like concat('%', #{name}, '%')
</if>
<if test="gender != null">
and gender = #{gender}
</if>
</where>
order by update_time desc
</select>
</mapper>
动态更新员工信息
<set>:动态的在行首插入set关键字,并且会删掉额外的逗号。(用于update语句之中)
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="whopxx.start.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="updateTime != null">update_time = #{updateTime}</if>
</set>
where id = #{id}
</update>
</mapper>
<foreach>
<delete id="deleteByIds">
delete from emp where id in
<foreach collection="ids" item="id" separator="," open="(" close=")">
#{id}
</foreach>
</delete>
collection: 集合名称
item: 集合遍历出来的元素/项
separator: 每一次遍历使用的分隔符
open: 遍历开始前拼接的片段
close: 遍历结束后拼接的片段
实现批量删除
SQL语句
delete from emp where id in (1,2,3);
接口方法
@Mapper
public interface EmpMapper {
public void deleteByIds(List<Integer>ids);
}
XML映射文件
<delete id="deleteByIds">
delete from emp where id in
<foreach collection="ids" item="id" separator="," open="(" close=")">
#{id}
</foreach>
</delete>
@Test
@Test
public void Test3(){
List<Integer> ids = Arrays.asList(13,14,15);
emp.deleteByIds(ids);
}
<sql><include>
提高代码复用性,类似于代码的封装
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="whopxx.start.mapper.EmpMapper">
<sql id="testSelect">
select id,username,password,name,gender,image,job,entrydate,dept_id,create_time,update_time from emp
</sql>
<select id="list" resultType="whopxx.start.pojo.User">
<include refid="testSelect"/>
<where>
<if test="name != null">
name like concat('%', #{name}, '%')
</if>
<if test="gender != null">
and gender = #{gender}
</if>
</where>
order by update_time desc
</select>
</mapper>
<sql>:定义可重用的sql片段。
<include>:通过属性refid,指定包含的sql片段。