mybatis一对多关联查询分页解决方案

1、sql：分页查询

先分页查询获取id,再根据这些id 查询数据

 select a.id, a.xm, a.xb, a.ksno, a.zkzh, a.bmxh, a.zjlx, a.zjno, a.xp, a.status, a.dizhi, a.xuexiao, a.banji, a.cscode, a.
    fenpei_cscode, a.fenzu_code, a.fenzu_num, a.fenzu_datetime, a.tijiandian_id, a.kaoshi_code, a.
    updatetime, a.bak1, a.bak2, a.csrq, a.mzdm, a.wyyzdm, a.kslbdm, a.zzmmdm, a.bylbdm, a.xxdm, a.hkszddm,a.print_status, a.print_datetime, a.zhiwen_status, a.zhiwen_datetime, 
		a.querenbiao_status, a.querenbiao_datetime, a.zhujian_status, a.zhujian_datetime, a.zhiwenqueren_way,b.zong_status,d.id zdyj_id,d.keyNum,d.valueStr
 from t_bmk a,tj_huizong b,t_bmk_zdyj c,t_zdyj d where a.id=b.ks_id and a.id=c.ks_id and c.zdyj_id=d.id and a.id in(
select
 id
 from
(
    select a.id,row_number() over
    (
	 order by ksno
    ) num
 from t_bmk a,tj_huizong b where a.id=b.ks_id and exists(select 1 from t_bmk_zdyj where ks_id=a.id)) as ta where ta.num >1 and ta.num<30);

sqlserver 存储过程

ALTER PROCEDURE [dbo].[tiaoxingma_getAllByPage]
(
  @txm_ksno AS varchar(50) ,
	@txm_zjno AS varchar(50) ,
	@txm_bmxh AS varchar(50) ,
	@txm_xm AS varchar(50) ,
	@txm_xuexiao AS varchar(200) ,
	@txm_xb AS varchar(1) ,
	@txm_printStatus AS varchar(10) ,
	@txm_qsbjbh AS varchar(50) ,
	@txm_zzbjbh AS varchar(50) ,
	@txm_qsbmxh AS varchar(50) ,
	@txm_zzbmxh AS varchar(50) ,
  @startnum AS int ,
  @endnum AS int 
	)
AS
BEGIN
declare @sql nvarchar(2000)	
set @sql='
   select a.id, a.xm, a.xb, a.ksno, a.zkzh, a.bmxh, a.zjlx, a.zjno, a.xp, a.status, a.dizhi, a.xuexiao, a.banji, a.cscode, a.
    fenpei_cscode, a.fenzu_code, a.fenzu_num, a.fenzu_datetime, a.tijiandian_id, a.kaoshi_code, a.
    updatetime, a.bak1, a.bak2, a.csrq, a.mzdm, a.wyyzdm, a.kslbdm, a.zzmmdm, a.bylbdm, a.xxdm, a.hkszddm,a.print_status, a.print_datetime, a.zhiwen_status, a.zhiwen_datetime, 
		a.querenbiao_status, a.querenbiao_datetime, a.zhujian_status, a.zhujian_datetime, a.zhiwenqueren_way,b.zong_status,d.id zdyj_id,d.keyNum,d.valueStr
 from t_bmk a,tj_huizong b,t_bmk_zdyj c,t_zdyj d where a.id=b.ks_id and a.id=c.ks_id and c.zdyj_id=d.id and a.id in(
select
 id
 from
(
    select a.id,row_number() over
    (
	 order by ksno
    ) num
 from t_bmk a,tj_huizong b where a.id=b.ks_id and exists(select 1 from t_bmk_zdyj where ks_id=a.id) ';
IF(@txm_ksno is not null and @txm_ksno!='')
	BEGIN
	set @sql=@sql+' and a.ksno like ''%'+@txm_ksno+'%'' '
	END
IF(@txm_zjno is not null and @txm_zjno!='')
	BEGIN
	set @sql=@sql+' and a.zjno like ''%'+@txm_zjno+'%'' '
	END
IF(@txm_bmxh is not null and @txm_bmxh!='')
	BEGIN
	set @sql=@sql+' and a.bmxh like ''%'+@txm_bmxh+'%'' '
	END
IF(@txm_xm is not null and @txm_xm!='')
	BEGIN
	set @sql=@sql+' and a.xm like ''%'+@txm_xm+'%'' '
	END
IF(@txm_xuexiao is not null and @txm_xuexiao!='')
	BEGIN
	set @sql=@sql+' and a.xuexiao like ''%'+@txm_xuexiao+'%'' '
	END
IF(@txm_xb is not null and @txm_xb!='')
	BEGIN
	set @sql=@sql+' and a.xb ='+@txm_xb
	END
IF(@txm_printStatus is not null and @txm_printStatus!='')
	BEGIN
	set @sql=@sql+' and a.print_status ='''+@txm_printStatus+''''
	END
IF(@txm_qsbjbh is not null and @txm_qsbjbh!='')
	BEGIN
	set @sql=@sql+' and a.banji >= '+@txm_qsbjbh
	END
IF(@txm_zzbjbh is not null and @txm_zzbjbh!='')
	BEGIN
	set @sql=@sql+' and a.banji <='+@txm_zzbjbh
	END
IF(@txm_qsbmxh is not null and @txm_qsbmxh!='')
	BEGIN
	set @sql=@sql+' and a.bmxh >= '''+@txm_qsbmxh+''''
	END
IF(@txm_zzbmxh is not null and @txm_zzbmxh!='')
	BEGIN
	set @sql=@sql+' and a.bmxh <= '''+@txm_zzbmxh+''''
	END
set @sql=@sql+') as ta where ta.num > '+CONVERT(varchar(50),@startnum) + ' and ta.num <='+CONVERT(varchar(50), @endnum) +')'
exec sp_executesql @sql
END

2、查询数据总数：
sql

  select count(1) 
 from t_bmk a,tj_huizong b where a.id=b.ks_id and exists(select 1 from t_bmk_zdyj where ks_id=a.id)

sqlserver存储过程：

ALTER PROCEDURE [dbo].[tiaoxingma_getAllByPage_count]
(
  @txm_ksno AS varchar(50) ,
	@txm_zjno AS varchar(50) ,
	@txm_bmxh AS varchar(50) ,
	@txm_xm AS varchar(50) ,
	@txm_xuexiao AS varchar(200) ,
	@txm_xb AS varchar(10) ,
	@txm_printStatus AS varchar(10) ,
	@txm_qsbjbh AS varchar(50) ,
	@txm_zzbjbh AS varchar(50) ,
	@txm_qsbmxh AS varchar(50) ,
	@txm_zzbmxh AS varchar(50) 
	)
AS
BEGIN
declare @sql nvarchar(2000)	
set @sql='
   select count(1) 
 from t_bmk a,tj_huizong b where a.id=b.ks_id and exists(select 1 from t_bmk_zdyj where ks_id=a.id) ';
IF(@txm_ksno is not null and @txm_ksno!='')
	BEGIN
	set @sql=@sql+' and a.ksno like ''%'+@txm_ksno+'%'' '
	END
IF(@txm_zjno is not null and @txm_zjno!='')
	BEGIN
	set @sql=@sql+' and a.zjno like ''%'+@txm_zjno+'%'' '
	END
IF(@txm_bmxh is not null and @txm_bmxh!='')
	BEGIN
	set @sql=@sql+' and a.bmxh like ''%'+@txm_bmxh+'%'' '
	END
IF(@txm_xm is not null and @txm_xm!='')
	BEGIN
	set @sql=@sql+' and a.xm like ''%'+@txm_xm+'%'' '
	END
IF(@txm_xuexiao is not null and @txm_xuexiao!='')
	BEGIN
	set @sql=@sql+' and a.xuexiao like ''%'+@txm_xuexiao+'%'' '
	END
IF(@txm_xb is not null and @txm_xb!='')
	BEGIN
	set @sql=@sql+' and a.xb ='+@txm_xb
	END
IF(@txm_printStatus is not null and @txm_printStatus!='')
	BEGIN
	set @sql=@sql+' and a.print_status ='+@txm_printStatus
	END
IF(@txm_qsbjbh is not null and @txm_qsbjbh!='')
	BEGIN
	set @sql=@sql+' and a.banji >= '+@txm_qsbjbh
	END
IF(@txm_zzbjbh is not null and @txm_zzbjbh!='')
	BEGIN
	set @sql=@sql+' and a.banji <='+@txm_zzbjbh
	END
IF(@txm_qsbmxh is not null and @txm_qsbmxh!='')
	BEGIN
	set @sql=@sql+' and a.bmxh >='''+@txm_qsbmxh+''''
	END
IF(@txm_zzbmxh is not null and @txm_zzbmxh!='')
	BEGIN
	set @sql=@sql+' and a.bmxh <= '''+@txm_zzbmxh+''''
	END
exec sp_executesql @sql
END

3、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.zr.dao.TxmMapper">
    <resultMap id="BaseResultMap" type="com.zr.model.KaoSheng">
        <id column="id" jdbcType="BIGINT" property="id"/>
        <result column="xm" jdbcType="NVARCHAR" property="xm"/>
        <result column="xb" jdbcType="INTEGER" property="xb"/>
        <result column="ksno" jdbcType="VARCHAR" property="ksno"/>
        <result column="zkzh" jdbcType="VARCHAR" property="zkzh"/>
        <result column="bmxh" jdbcType="VARCHAR" property="bmxh"/>
        <result column="zjlx" jdbcType="VARCHAR" property="zjlx"/>
        <result column="zjno" jdbcType="VARCHAR" property="zjno"/>
        <result column="xp" jdbcType="VARCHAR" property="xp"/>
        <result column="status" jdbcType="INTEGER" property="status"/>
        <result column="dizhi" jdbcType="VARCHAR" property="dizhi"/>
        <result column="xuexiao" jdbcType="VARCHAR" property="xuexiao"/>
        <result column="banji" jdbcType="VARCHAR" property="banji"/>
        <result column="cscode" jdbcType="VARCHAR" property="cscode"/>
        <result column="fenpei_cscode" jdbcType="VARCHAR" property="fenpeiCscode"/>
        <result column="fenzu_code" jdbcType="VARCHAR" property="fenzuCode"/>
        <result column="fenzu_num" jdbcType="INTEGER" property="fenzuNum"/>
        <result column="fenzu_datetime" jdbcType="TIMESTAMP" property="fenzuDatetime"/>
        <result column="tijiandian_id" jdbcType="BIGINT" property="tijiandianId"/>
        <result column="kaoshi_code" jdbcType="VARCHAR" property="kaoshiCode"/>
        <result column="updatetime" jdbcType="TIMESTAMP" property="updatetime"/>
        <result column="bak1" jdbcType="BIGINT" property="bak1"/>
        <result column="bak2" jdbcType="VARCHAR" property="bak2"/>
        <result column="csrq" jdbcType="VARCHAR" property="csrq"/>
        <result column="mzdm" jdbcType="VARCHAR" property="mzdm"/>
        <result column="wyyzdm" jdbcType="VARCHAR" property="wyyzdm"/>
        <result column="kslbdm" jdbcType="VARCHAR" property="kslbdm"/>
        <result column="zzmmdm" jdbcType="VARCHAR" property="zzmmdm"/>
        <result column="bylbdm" jdbcType="VARCHAR" property="bylbdm"/>
        <result column="xxdm" jdbcType="VARCHAR" property="xxdm"/>
        <result column="hkszddm" jdbcType="VARCHAR" property="hkszddm"/>
        <result column="print_status" jdbcType="VARCHAR" property="printStatus"/>
        <result column="print_datetime" jdbcType="TIMESTAMP" property="printDatetime"/>
        <result column="zhiwen_status" jdbcType="VARCHAR" property="zhiwenStatus"/>
        <result column="zhiwen_datetime" jdbcType="TIMESTAMP" property="zhiwenDatetime"/>
        <result column="querenbiao_status" jdbcType="VARCHAR" property="querenbiaoStatus"/>
        <result column="querenbiao_datetime" jdbcType="TIMESTAMP" property="querenbiaoDatetime"/>
        <result column="zhujian_status" jdbcType="VARCHAR" property="zhujianStatus"/>
        <result column="zhujian_datetime" jdbcType="TIMESTAMP" property="zhujianDatetime"/>
        <result column="zhiwenqueren_way" jdbcType="VARCHAR" property="zhiwenquerenWay"/>
        <result column="zong_status" jdbcType="VARCHAR" property="zongStatus"/>
        <result column="querenbiao_frequency" jdbcType="INTEGER" property="querenbiaoFrequency" />
        <collection property="zdyjs" ofType="com.zr.model.Zdyj">
            <id column="zdyj_id" jdbcType="INTEGER" property="id"/>
            <result column="keyNum" jdbcType="VARCHAR" property="keynum"/>
            <result column="valueStr" jdbcType="VARCHAR" property="valuestr"/>
            <result column="type" jdbcType="INTEGER" property="type"/>
        </collection>
    </resultMap>
    <select id="tiaoxingma_getAllByPage" statementType="CALLABLE" resultMap="BaseResultMap">
    {CALL tiaoxingma_getAllByPage(#{ksno},#{zjno},#{bmxh},#{xm},#{xuexiao},#{xb},#{printStatus},#{qsbjbh},#{zzbjbh},#{qsbmxh},#{zzbmxh},#{startnum},#{endnum})}
  </select>
    <select id="tiaoxingma_getAllByPage_count" resultType="INTEGER" statementType="CALLABLE">
    {CALL tiaoxingma_getAllByPage_count(#{ksno},#{zjno},#{bmxh},#{xm},#{xuexiao},#{xb},#{printStatus},#{qsbjbh},#{zzbjbh},#{qsbmxh},#{zzbmxh})}
  </select>
 
</mapper>