MyBatis多表查询+动态sql

最新推荐文章于 2024-05-08 15:24:52 发布

爱敲代码的三毛

最新推荐文章于 2024-05-08 15:24:52 发布

阅读量1.8k

点赞数

分类专栏： web后端文章标签： mybatis java spring boot mysql

本文链接：https://blog.csdn.net/weixin_53946852/article/details/130168226

版权

web后端专栏收录该内容

18 篇文章 0 订阅

订阅专栏

文章目录

MyBatis多表查询
- 1. 多表一对一查询
- 2. 多表一对多
动态SQL

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 {
    @Resource
    private BlogService blogService;

    @RequestMapping("/getAllBlog")
    public List<BlogInfo> getAllBlog() {
        return blogService.getAllBlog();
    }
}

Service服务层代码

@Service
public class BlogService {
    @Resource
    private 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 {
    @Resource
    private UserMapper userMapper;

    @Test
    void 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 ( ? , ? , ? )

爱敲代码的三毛

关注

0
点赞
踩
5

收藏

觉得还不错? 一键收藏
打赏
0
评论
MyBatis多表查询+动态sql

动态SQL是MyBatis强大特征之一，在JDBC拼接SQL时候的痛处，不能忘记必要的空格添加，最后一个列名的逗号也要注意，利用动态SQL就能完成不同场景的SQL拼接。在注册某些账号的填写信息的时候，有必填项和非必填项，如果非必填项和少多写个接口就好了，但如果非必填项非常多的话就不行了，这个时候就需要动态标签。在使用查询语句的时候，如果有多个条件就会用到逻辑运算，如果有些条件不满足就会出现and前面没有条件判，导致sql报错。是固定语法，里面的判断的字段要和接口中定义的名字对应。
复制链接

扫一扫