mybatisplus实现连表查询+分页+模糊查询

首先创建一个RoleInfoVo类

@Data
@AllArgsConstructor
@NoArgsConstructor
public class RoleInfoVo {
    private Long id;
    private String name;
    private String description;
    List<User> userList;
}

该类中多了一个List对象用来存储该角色下的所有用户

Role类

@Data
public class Role {
    private long id;
    private String name;
    private String description;
}

User类

@Data
public class User {
  private long id;
  private String nickName;
  @ApiModelProperty(value = "用户名")
  private String userName;
  @ApiModelProperty(value = "密码")
  private String password;
  @ApiModelProperty(value = "性别 0 --- 男 1 --- 女")
  private int sex;

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.qcby.demo.Mapper.RoleMapper">
<resultMap id="BaseResultMap" type="com.qcby.demo.entity.Role">
  <id column="id" jdbcType="BIGINT" property="id" />
  <result column="name" jdbcType="VARCHAR" property="name" />
  <result column="description" jdbcType="VARCHAR" property="description" />
</resultMap>

<resultMap id="BaseResultManyToManyMap" type="com.qcby.demo.vo.RoleInfoVo">
  <id column="id" jdbcType="BIGINT" property="id" />
  <id column="name" jdbcType="VARCHAR" property="name" />
  <id column="description" jdbcType="VARCHAR" property="description" />
  <collection property="userList" column="id" select="com.qcby.demo.Mapper.UserMapper.getByRoleId">
  </collection>
</resultMap>

其中

<collection property="userList" column="id" select="com.qcby.demo.Mapper.UserMapper.getByRoleId">
  </collection>

根据id字段UserMapper中的getByRoleId方法

  <select id="getByRoleId" resultMap="BaseResultMap">
    select * from user u left join ref_user_role r on u.id = r.user_id where r.role_id = #{roleId}
  </select>

查出来后放到

中

mapper层的实现

    List<RoleInfoVo> listPage(IPage<RoleInfoVo> page,@Param("role") Role role);

service接口

IPage<RoleInfoVo> listPage(IPage<RoleInfoVo> page,@Param("role") Role role);

实现类

  public IPage<RoleInfoVo> listPage(IPage<RoleInfoVo> page, Role role) {
    return page.setRecords(this.baseMapper.listPage(page,role));
  }

controller层

  public ResultJson listPage(int pageNo,int pageSize,Role role){
    Page<RoleInfoVo> page = new Page<>(pageNo, pageSize);
    IPage<RoleInfoVo> pageInfo = roleService.listPage(page, role);
    return ResultJson.ok(pageInfo);
  }
}

模糊查找的sql语句

 <select id="listPage" resultMap="BaseResultManyToManyMap">
    select * from role  where 1 = 1
    <if test="role.id != null">
      and id like concat('%',#{role.id},'%')
    </if>
    <if test="role.name != null">
      and name like concat('%',#{role.name},'%')
    </if>
    <if test="role.description != null">
      and description like concat('%',#{role.description},'%')
    </if>
  </select>

注意resultMap=“BaseResultManyToManyMap”

最后实现效果为：

{
    "code": 200,
    "message": "操作成功",
    "data": {
        "records": [
            {
                "id": 2,
                "name": "老师",
                "description": "老师",
                "userList": [
                    {
                        "id": 4,
                        "nickName": "老师",
                        "userName": "华强",
                        "password": "123456",
                        "sex": 0,
                        "authList": null
                    }
                ]
            }
        ],
        "total": 1,
        "size": 3,
        "current": 1,
        "orders": [],
        "optimizeCountSql": true,
        "hitCount": false,
        "countId": null,
        "maxLimit": null,
        "searchCount": true,
        "pages": 1
    }
}