MyBatis之联查

测本篇文章将简单说明联查标签的使用以及分页

联查涉及到两个标签分别为association标签（一对一）和collection标签（一对多）

1. association标签

下面代码是查询地址并根据地址里的用户外键联查出数据，地址对用户是一对一

@Data
@NoArgsConstructor
@AllArgsConstructor
public class AddressVo {
    private Integer id;
    private String addr;
    private String phone;
    private String postcode;
    private Integer userId;
    private User user;
}

    <resultMap id="selectAAU" type="addressVo" autoMapping="true">
// id标签是主键标签
        <id column="id" property="id"></id>
// result标签是非主键标签
        <result column="user_id" property="userId"></result>
        <association property="user" column="user_id" javaType="user" autoMapping="true">
            <id column="uid" property="id"></id>
        </association>
    </resultMap>

association中的属性及其意思

property 为实体类addressVo类中的用户对象名

column 相当于联合条件on后面的主表address的字段

javaType java的类型，这里写user，不写全类名是因为起了别名

autoMapping 自动装配注入

2. collection标签

一个用户对应多个地址，所以是一对多的关系，用collection标签

    <resultMap id="selectUAA" type="userVo" autoMapping="true">
        <id column="id" property="id"></id>
        <result column="create_time" property="createTime"></result>
        <result column="is_delete" property="isDelete"></result>
        <collection property="addresses" column="id" javaType="list" ofType="address" autoMapping="true">
            <id column="aid" property="id"></id>
            <result column="user_id" property="userId"></result>
        </collection>
    </resultMap>

collection标签属性与association标签差不多，ofType是集合中每条数据的类型

3. 多条件查询分页

有两种方式，一种是手动分页，一种是导入依赖自动分页

3.1 手动分页

    <!--    分页查询用户和地址-->
    <select id="userAndAddrPaging" parameterType="page" resultMap="pagingUser">
        select u.*, a.*, a.id as aid
        from (select id as uid, account, name, password, create_time, is_delete from user limit #{startNum},#{pageSize}) as u
                 left join address a on uid = a.user_id
    </select>

    <resultMap id="pagingUser" type="userVo" autoMapping="true">
        <id column="uid" property="id"></id>
        <result column="create_time" property="createTime"></result>
        <result column="is_delete" property="isDelete"></result>
        <collection property="addresses" ofType="address" autoMapping="true">
            <id column="aid" property="id"></id>
            <id column="user_id" property="userId"></id>
        </collection>
    </resultMap>

    //测试类代码
    @Test
    public void userPaging() {
        IUserMapper mapper = session.getMapper(IUserMapper.class);
        Integer pageNum = 2;
        Integer pageSize = 4;
        Page page = new Page(pageNum, pageSize);
        page.getStart();
        List<UserVo> userVos = mapper.userAndAddrPaging(page);
        System.out.println("总人数" + mapper.countUser());
        userVos.stream().forEach(a -> System.out.println(a));
    }

3.2 自动分页

添加依赖

<!--分页插件-->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper</artifactId>
            <version>5.2.0</version>
        </dependency>
    </dependencies>

    //    自动分页 地址 测试
    @Test
    public void addrPagingAuto() {
        Address address = new Address();
// 下面代码后面必须紧跟着sql语句
        PageHelper.startPage(2, 3);
        List<Address> addresses = session.getMapper(IAddressMapper.class).selectCondition(address);
//  获取对象，里面有很多好用属性，比如count总数，查询的结果list
        PageInfo<Address> pageInfo = PageInfo.of(addresses);
        System.out.println(pageInfo);
    }

补充：模糊查询用concat函数拼接，比如concat('%',#{name},'%')

看不懂的可以来下面链接中再仔细看看

mybatis_prac: mybatis的简单使用