一对多查询(不适用默认的mybatisplus中的查询方法)

1.在entity/User中定义一个查询结果的字段;

    @TableField(exist = false) //告诉数据库这个字段是不存在
    private List<Book> bookList;  //bookList是用于额外查询的字段

2.在mapper/userMapper中创建一个方法;

//一对多查询
Page<User> findPage(Page<User> page);

3.在resources/mapper/Book.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.entity.User">
        <result property="id" column="id"/>
        <result property="username" column="username"/>
        <result property="password" column="password"/>
        <result property="nickName" column="nick_name"/>
        <result property="age" column="age"/>
        <result property="sex" column="sex"/>
        <result property="address" column="address"/>
<!--        <result property="avatar" column="avatar"/>-->
        <collection property="bookList" javaType="ArrayList" ofType="com.example.demo.entity.Book">
            <result column="b_id" property="id" />
            <result column="b_name" property="name" />
            <result column="b_price" property="price" />
        </collection>
    </resultMap>
<!--    as后面的缩写要和result中的完全一致-->
    <select id="findPage" resultMap="userMap">
        SELECT `user`.* ,book.id as b_id, book.name as b_name,book.price as b_price from `user`
        left join book on user.id = book.user_id where `user`.nick_name like concat('%', #{nickName}, '%')
    </select>
</mapper>

在UserController中路由分页查询中进行调用findPage

        Page<User> userPage = userMapper.findPage(new Page<>(pageNum,pageSize) );
posted @ 2022-01-11 15:04  成强  阅读(119)  评论(0编辑  收藏  举报