mybatis collection 子查询，嵌套查询，解决分页问题

<?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.xx.springboot.business.system.dao.UserDao">
<resultMap id="UserDtoMap" type="com.xx.springboot.business.system.dto.UserDto">
<id column="id" property="id" jdbcType="BIGINT"/>
<result column="open_id" property="openId" jdbcType="VARCHAR"/>
<result column="user_code" property="userCode" jdbcType="VARCHAR"/>
<result column="account" property="account" jdbcType="VARCHAR"/>
<result column="salt" property="salt" jdbcType="VARCHAR"/>
<result column="department_id" property="departmentId" jdbcType="VARCHAR"/>
<result column="department_name" property="departmentName" jdbcType="VARCHAR"/>
<result column="real_name" property="realName" jdbcType="VARCHAR"/>
<result column="age" property="age" jdbcType="BIGINT"/>
<result column="sex" property="sex" jdbcType="BIGINT"/>
<result column="phone" property="phone" jdbcType="VARCHAR"/>
<result column="phone" property="phone" jdbcType="VARCHAR"/>
<result column="id_card_num" property="idCardNum" jdbcType="VARCHAR"/>
<result column="email" property="email" jdbcType="VARCHAR"/>
<result column="address" property="address" jdbcType="VARCHAR"/>
<result column="create_user_id" property="createUserId" jdbcType="BIGINT"/>
<result column="modified_user_id" property="modifiedUserId" jdbcType="BIGINT"/>
<result column="gmt_create" property="gmtCreate" jdbcType="TIMESTAMP"/>
<result column="gmt_modified" property="gmtModified" jdbcType="TIMESTAMP"/>
<result column="is_deleted" property="isDeleted" jdbcType="TINYINT"/>
<collection property="roleDOList" columnPrefix="sr_" resultMap="RoleMap"/>
<collection property="organizationDOList" columnPrefix="so_" resultMap="OrganizationMap"/>
</resultMap>
<resultMap id="RoleMap" type="com.xx.springboot.business.system.domain.SysRoleDO">
<id column="id" property="id" jdbcType="BIGINT"/>
<result column="role_code" property="roleCode" jdbcType="VARCHAR"/>
<result column="role_name" property="roleName" jdbcType="VARCHAR"/>
<result column="role_desc" property="roleDesc" jdbcType="VARCHAR"/>
<result column="create_user_id" property="createUserId" jdbcType="BIGINT"/>
<result column="modified_user_id" property="modifiedUserId" jdbcType="BIGINT"/>
<result column="gmt_create" property="gmtCreate" jdbcType="TIMESTAMP"/>
<result column="gmt_modified" property="gmtModified" jdbcType="TIMESTAMP"/>
<result column="is_deleted" property="isDeleted" jdbcType="TINYINT"/>
</resultMap>
<resultMap id="OrganizationMap" type="com.xx.springboot.business.system.domain.SysOrganizationDO">
<id column="id" property="id" jdbcType="BIGINT"/>
<result column="organization_code" property="organizationCode" jdbcType="VARCHAR"/>
<result column="organization_name" property="organizationName" jdbcType="VARCHAR"/>
<result column="create_user_id" property="createUserId" jdbcType="BIGINT"/>
<result column="modified_user_id" property="modifiedUserId" jdbcType="BIGINT"/>
<result column="gmt_create" property="gmtCreate" jdbcType="TIMESTAMP"/>
<result column="gmt_modified" property="gmtModified" jdbcType="TIMESTAMP"/>
<result column="is_deleted" property="isDeleted" jdbcType="TINYINT"/>
</resultMap>
<!--子查询map，解决分页问题-->
<resultMap id="UserDtoMapSelect" type="com.xx.springboot.business.system.dto.UserDto">
<id column="id" property="id" jdbcType="BIGINT"/>
<result column="open_id" property="openId" jdbcType="VARCHAR"/>
<result column="user_code" property="userCode" jdbcType="VARCHAR"/>
<result column="account" property="account" jdbcType="VARCHAR"/>
<result column="salt" property="salt" jdbcType="VARCHAR"/>
<result column="department_id" property="departmentId" jdbcType="VARCHAR"/>
<result column="department_name" property="departmentName" jdbcType="VARCHAR"/>
<result column="real_name" property="realName" jdbcType="VARCHAR"/>
<result column="age" property="age" jdbcType="BIGINT"/>
<result column="sex" property="sex" jdbcType="BIGINT"/>
<result column="phone" property="phone" jdbcType="VARCHAR"/>
<result column="phone" property="phone" jdbcType="VARCHAR"/>
<result column="id_card_num" property="idCardNum" jdbcType="VARCHAR"/>
<result column="email" property="email" jdbcType="VARCHAR"/>
<result column="address" property="address" jdbcType="VARCHAR"/>
<result column="create_user_id" property="createUserId" jdbcType="BIGINT"/>
<result column="modified_user_id" property="modifiedUserId" jdbcType="BIGINT"/>
<result column="gmt_create" property="gmtCreate" jdbcType="TIMESTAMP"/>
<result column="gmt_modified" property="gmtModified" jdbcType="TIMESTAMP"/>
<result column="is_deleted" property="isDeleted" jdbcType="TINYINT"/>
<collection property="roleDOList" column="id" select="getRoleListByUserId"/>
<collection property="organizationDOList" column="id" select="getOrganizationListByUserId"/>
</resultMap>
<select id="getAll" resultType="com.xx.springboot.business.system.domain.SysUserDO">
select * from sys_user;
</select>
<select id="getUserListPage" resultType="com.xx.springboot.business.system.domain.SysUserDO">
select
*
from sys_user
limit #{start},#{size}
</select>
<select id="getUserListPageNum" resultType="java.lang.Long">
select
count(*)
from sys_user
</select>
<select id="getUserByAccount" resultType="com.xx.springboot.business.system.domain.SysUserDO">
select * from sys_user where account = #{account}
</select>
<select id="selectEntityAndPage"
parameterType="com.xx.springboot.business.system.domain.SysUserDO"
resultType="com.xx.springboot.business.system.dto.UserDto">
select *
from sys_user
</select>
<select id="selectUserRole" resultType="com.xx.springboot.business.system.dto.UserRoleDto">
select *
from sys_user u,sys_role_user ru,sys_role r where u.id = ru.user_id and r.id = ru.role_id
</select>
<sql id="whereSql">
<where>
<if test="1==1 ">
su.is_deleted = 0
</if>
<if test="organizationId != null and organizationId != '' ">
and suo.organization_id = #{organizationId}
</if>
<if test="roleId != null and roleId != '' ">
and sru.role_id = #{roleId}
</if>
<if test="account != null and account != '' ">
and su.account = #{account}
</if>
<if test="realName != null and realName != '' ">
and su.real_name = #{realName}
</if>
<if test="phone != null and phone != '' ">
and su.phone = #{phone}
</if>
<if test="email != null and email != '' ">
and su.email = #{email}
</if>
<if test="startTimestamp != null and startTimestamp != 0 ">
and UNIX_TIMESTAMP(su.gmt_create) &gt;= #{startTimestamp}
</if>
<if test="endTimestamp != null and endTimestamp != 0 ">
and UNIX_TIMESTAMP(su.gmt_create ) &lt;= #{endTimestamp}
</if>
</where>
</sql>
<select id="getUserDtoList1" resultMap="UserDtoMap">
SELECT
su.id as id,
su.open_id,
su.user_code,
su.account,
su.salt,
su.department_id,
su.department_name,
su.real_name,
su.age,
su.sex,
su.phone,
su.id_card_num,
su.email,
su.address,
su.create_user_id,
su.modified_user_id,
su.gmt_create,
su.gmt_modified,
su.is_deleted,
sr.id as sr_id,
sr.role_code as sr_role_code,
sr.role_name as sr_role_name,
sr.role_desc as sr_role_desc,
so.id as so_id,
so.organization_name as so_organization_name,
so.organization_code as so_organization_code
FROM
sys_user su
left join sys_user_organization suo on suo.user_id = su.id
left join sys_organization so on so.id = suo.organization_id
left join sys_role_user sru on sru.user_id = su.id
left join sys_role sr on sr.id = sru.role_id
WHERE su.id IN (
select temp.id from
(SELECT su.* FROM sys_user su
LEFT JOIN sys_user_organization suo ON suo.user_id = su.id
LEFT JOIN sys_role_user sru ON sru.user_id = su.id
<include refid="whereSql"/>
GROUP BY su.id
limit #{start},#{size}) as temp
)
</select>
<!-- 子查询方式 ，效率略低 -->
<select id="getUserDtoList" resultMap="UserDtoMapSelect">
SELECT
su.id as id,
su.open_id,
su.user_code,
su.account,
su.salt,
su.department_id,
su.department_name,
su.real_name,
su.age,
su.sex,
su.phone,
su.id_card_num,
su.email,
su.address,
su.create_user_id,
su.modified_user_id,
su.gmt_create,
su.gmt_modified,
su.is_deleted
FROM
sys_user su
left join sys_user_organization suo on suo.user_id = su.id
left join sys_organization so on so.id = suo.organization_id
left join sys_role_user sru on sru.user_id = su.id
left join sys_role sr on sr.id = sru.role_id
<include refid="whereSql"/>
group by su.id
limit #{start},#{size}
</select>
<select id="getUserDtoListNum" resultType="java.lang.Integer">
select
count(DISTINCT(su.id))
FROM
sys_user su
left join sys_user_organization suo on suo.user_id = su.id
left join sys_role_user sru on sru.user_id = su.id
<include refid="whereSql"/>
</select>
<select id="getRoleListByUserId" resultType="com.xx.springboot.business.system.domain.SysRoleDO">
SELECT
sr.*
FROM
sys_role_user sru
left join sys_role sr on sr.id = sru.role_id
WHERE
sru.user_id = #{userId}
</select>
<select id="getOrganizationListByUserId" resultType="com.xx.springboot.business.system.domain.SysOrganizationDO">
SELECT
so.*
FROM
sys_user_organization suo
LEFT JOIN sys_organization so on so.id = suo.organization_id
WHERE
suo.user_id = #{userId}
</select>
</mapper>

最后

以上就是畅快黄蜂为你收集整理的mybatis collection 子查询，嵌套查询，解决分页问题的全部内容，希望文章能够帮你解决mybatis collection 子查询，嵌套查询，解决分页问题所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。