MyBatis多表查询

在全局配置文件中中设置MyBatis执行日志

mybatis:configuration:log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

1. 多表一对一查询

假设有一个用户表和文章表，实体类中一个关联关系。

用户实体类

@Getter
@Setter
@ToString
public class UserInfo {private int id;private String name;private String password;
}

文章实体类

@Data
public class BlogInfo {private int blogId;private String title;private String content;private Integer userId;private Timestamp postTime;private String time;private UserInfo userInfo;
}

如果想查询的结果包含UserInfo的信息就需要使用，⼀对⼀映射要使⽤ <association> 标签，因为一篇文章只能对应一个作者。

Controller控制器代码

@Controller
@ResponseBody
public class BlogController {@Resourceprivate BlogService blogService;@RequestMapping("/getAllBlog")public List<BlogInfo> getAllBlog() {return blogService.getAllBlog();}
}

Service服务层代码

@Service
public class BlogService {@Resourceprivate BlogMapper blogMapper;public List<BlogInfo> getAllBlog() {return blogMapper.getAllBlog();}
}

BlogMap接口定义

@Mapper
public interface BlogMapper {List<BlogInfo> getAllBlog();
}

MyBatis接口实现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.example.demo.mapper.BlogMapper"><resultMap id="BlogBean" type="com.example.demo.model.BlogInfo"><!-- 映射主键 --><id column="blogId" property="blogId"></id><!-- 普通列映射（表字段->实体类） --><result column="title" property="title"></result><result column="content" property="content"></result><result column="userId" property="userId"></result><result column="postTime" property="postTime"></result><!-- 关联关系 --><association property="userInfo"resultMap="com.example.demo.mapper.UserMapper.UserBean"columnPrefix="u_"></association></resultMap><select id="getAllBlog" resultMap="BlogBean">select u.userId u_userId,u.username u_username,b.blogId,b.title,b.postTime,b.userId from blog b left join user u on u.userId=b.userId</select>
</mapper>

使用<association>标签，表示一对一的结果映射：

• property属性：指定文章类BlogInfo中对应的关联属性，也就是用户实例userInfo
• resultMap属性：指定的是关联关系的结果集映射，也就是UserInfo对象的属性和表之间的映射关系
• columnPrefix属性：绑定一对一对象的时候，是通过columnPrefix+association.resultMap.column来映射结果集字段。association.resultMap.column是指 <association>标签中 resultMap属性，对应的结果集映射中，column字段 。

Postman测试打印的部分结果

[{"blogId": 2,"title": "Java基础","content": null,"userId": 1,"postTime": "2022-02-25T11:50:52.000+00:00","time": null,"userInfo": {"id": 1,"name": "admin","password": null}},{"blogId": 5,"title": "我的第一篇博客","content": null,"userId": 1,"postTime": "2022-02-25T14:05:22.000+00:00","time": null,"userInfo": {"id": 1,"name": "admin","password": null}},

columnPrefix 如果省略，并且恰好两个表中如果有相同的字段，那么就会导致查询出错

2. 多表一对多

一对多需要使用<collection>标签，用法和<association>相同，比如说一个用户可以发布多篇文章。

用户实体类

@Getter
@Setter
@ToString
public class UserInfo {private int id;private String name;private String password;private List<BlogInfo> blogList;
}

映射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.example.demo.mapper.UserMapper"><resultMap id="UserMap" type="com.example.demo.model.UserInfo"><!-- 映射主键的(表中主键和程序实体类中的主键) --><id column="userId" property="id"></id><!-- 普通字段映射 --><result column="username" property="name"></result><result column="password" property="password"></result><!-- 外部关联管理 --><collection property="blogList"resultMap="com.example.demo.mapper.BlogMapper.BlogBean"columnPrefix="b_"></collection></resultMap><select id="getAll" resultMap="UserMap">select u.userId,u.username,b.blogId b_blogId,b.title b_title,b.postTime b_postTime from user u left join blog b on u.userId=b.userId</select></mapper>

• property属性对应的是UserInfo类中的属性blogList

Postman部分测试结果

动态SQL

动态SQL是MyBatis强大特征之一，在JDBC拼接SQL时候的痛处，不能忘记必要的空格添加，最后一个列名的逗号也要注意，利用动态SQL就能完成不同场景的SQL拼接。

MyBatis动态SQL

1.<if>标签

在注册某些账号的填写信息的时候，有必填项和非必填项，如果非必填项和少多写个接口就好了，但如果非必填项非常多的话就不行了，这个时候就需要动态标签<if>来判断了。

这里假设性别和年龄是非必填项。

接口定义

int logonUser(String name, String password, String sex, Integer age);

数据表

+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int(11)     | NO   | PRI | NULL    | auto_increment |
| name     | varchar(50) | NO   |     | NULL    |                |
| password | varchar(25) | NO   |     | NULL    |                |
| sex      | varchar(10) | YES  |     | NULL    |                |
| age      | int(11)     | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+

注意if<test=>是固定语法，里面的判断的字段要和接口中定义的名字对应。

• 如果满足条件才会添加<if>里的文字

<?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.example.demo.mapper.UserMapper"><insert id="logonUser">insert into userInfo(id,name,password<if test="sex!=null">,sex</if><if test="age!=null">,age</if>) value(null,#{name},#{password}<if test="sex!=null">,#{sex}</if><if test="age!=null">,#{age}</if>)</insert></mapper>

如果age和sex都为null那么执行的sql 就是

insert into userInfo(id,name,password ) value(null,?,? );

2.<trim>标签

前面的新增用户操作，age和sex可能是选填项，如果有多个字段，一般考虑用<trim>标签结合<if>标签，对多个字段都采用动态生成的方式

<trim>标签中有如下属性

• prefix：它表示整个语句块，以prefix的值作为前缀（在语句块最前面添加prefix字符）
• suffix：它表示整个语句块，以suffix的值作为后缀（在语句块最后添加suffix字符）
• prefixOverrides：表示整个语句块要去除掉的前缀（删除语句块最前面满足条件的字符）
• suffixOverrides：表示整个语句块要去除的后缀 （删除语句块最后面满足条件的字符）

那么就可以将插入语句改成如下：

<?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.example.demo.mapper.UserMapper"><insert id="logonUser">insert into userInfo<trim prefix="(" suffix=")" suffixOverrides=",">id,name,password,<if test="sex!=null">sex,</if><if test="age!=null">age</if></trim>value<trim prefix="(" suffix=")" suffixOverrides=",">null,#{name},#{password},<if test="sex!=null">#{sex},</if><if test="age!=null">#{age}</if></trim></insert></mapper>

假设调用接口的代码是

userMapper.logonUser("张辉","123456","男",null);

最后执行的SQL就是

insert into userInfo ( id,name,password, sex ) value ( null,?,?, ? )

• 基于prefix 配置，开始部分加上 (
• 基于suffix 配置，结束部分加上 )
• 多个if都已,结尾，使用suffixOverrides就可以把末尾的,给去掉

3. <where>标签

在使用查询语句的时候，如果有多个条件就会用到逻辑运算，如果有些条件不满足就会出现and前面没有条件判，导致sql报错

select * from userInfo where and sex!=null and age!=null;

使用<where>就可以很好的解决这个问题

• 生成where，如果有查询条件就会生成where，如果没有查询条件就会忽略where
• where会判断第一个查询条件前面有没有and，如果有则会删除

查找接口定义

List<User> findAllUser(String sex, int age);

<?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.example.demo.mapper.UserMapper"><select id="findAllUser" resultType="com.example.demo.model.User">select * from userInfo<where><if test="sex!=null">sex=#{sex}</if><if test="age!=null">and age=#{age}</if></where></select></mapper>

以上<where>标签也可以使 <trim prefix=“where” prefixOverrides="and> 替换

4.<set>标签

根据传入的用户对象属性来更新用户数据，可以使用<set>标签来指定动态内容.

示例：

根据用户Id来修改其它不为null的属性

接口定义

int updateUserId(User user);

xml文件的接口实现

• <set>也是会自动去除末尾的,的
• <set>标签也可以使⽤ <trim prefix="set" suffixOverrides=","> 替换

<?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.example.demo.mapper.UserMapper"><update id="updateUserId">update userInfo<set><if test="name!=null">name=#{name},</if><if test="password!=null">password=#{password},</if><if test="sex!=null">sex=#{sex},</if><if test="age!=null">age=#{age},</if></set>where id=#{id}</update></mapper>

最后拼接的SQL

update userInfo SET name=?, password=?, sex=?, age=? where id=?

5. <foreach>标签

对集合遍历的时候可以使用该标签，<foreach标签有以下属性：

• conection：绑定方法中的集合，如 List、Set、Map或者数组对象
• item：遍历时的每一个对象
• open：语句块开头的字符串
• close：语句块结束的字符串
• separtor：每次遍历之间间隔的字符串

假设要通过多个用户id删除多个用户

接口定义和测试代码

@Mapper
public interface UserMapper {// 方法定义int deleteListId(List<Integer> listId);
}@SpringBootTest
class UserMapperTest {@Resourceprivate UserMapper userMapper;@Testvoid deleteListId() {List<Integer> list = new ArrayList<>();list.add(1);list.add(2);list.add(3);userMapper.deleteListId(list);}
}

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.example.demo.mapper.UserMapper"><delete id="deleteListId">delete from userInfo where id in<foreach collection="listId" item="id" open="(" close=")" separator=",">#{id}</foreach></delete></mapper>

我的list集合里有3个用户Id,最后拼装的SQL

delete from userInfo where id in ( ? , ? , ? )