1. 使用级联查询解决一对多分页问题
下面的 mapper 语句为第一个版本,其中还存在一个bug,级联查询没有办法解决带条件的查询问题。
<resultMap id="ProductCheckListPoolVoMap" type="com.lenovo.npi.portal.module.masterdata.vo.ProductCheckListPoolVo">
<id column="id" jdbcType="INTEGER" property="id"/>
<result column="phase_dict_code" jdbcType="VARCHAR" property="phaseDictCode"/>
<result column="phase" jdbcType="VARCHAR" property="phase"/>
<result column="sort" jdbcType="INTEGER" property="sort"/>
<result column="level1" jdbcType="VARCHAR" property="level1"/>
<result column="level2" jdbcType="VARCHAR" property="level2"/>
<result column="owner" jdbcType="VARCHAR" property="owner"/>
<result column="active_dict_code" jdbcType="VARCHAR" property="activeDictCode"/>
<result column="active" jdbcType="VARCHAR" property="active"/>
<result column="update_by" jdbcType="VARCHAR" property="updateBy"/>
<result column="update_time" jdbcType="TIMESTAMP" property="updateTime"/>
<collection property="designTypeMappingList" ofType="com.lenovo.npi.portal.module.masterdata.model.ProductCheckListPoolMapping"
select="selectDesignTypeMappings" column="id"/>
</resultMap>
<select id="getByKey" resultMap="ProductCheckListPoolVoMap">
SELECT dpclp.*
,sd2.dict_name as phase
,sd3.dict_name as active
,dpclp.*
from dim_product_check_list_pool as dpclp
left join sys_dict as sd2 on sd2.dict_code=dpclp.phase_dict_code
left join sys_dict as sd3 on sd3.dict_code=dpclp.active_dict_code
<include refid="pageWhere"></include>
<if test="param.sortName==null or param.sortName==''">
order by update_time desc
</if>
<if test="param.sortName!=null and param.sortBy!=null and param.sortName!='' and param.sortBy!=''">
order by
${@org.apache.commons.lang.StringEscapeUtils@escapeSql(param.sortName)}
${@org.apache.commons.lang.StringEscapeUtils@escapeSql(param.sortBy)}
</if>
<if test="param.pageSize > 0">
limit #{param.pageSize} offset #{param.offset}
</if>
</select>
<sql id="pageWhere">
<where>
<if test="param.key != null and param.key != ''">
LOWER(concat(dio.name,sd2.dict_name,dpclp.sort,dpclp.level1,dpclp.level2,dpclp.owner,sd3.dict_name,dpclp.update_by))
like LOWER(concat('%',#{param.key},'%'))
</if>
</where>
</sql>
2. 使用分组解决带条件的分页查询
思路
– 1. 查出所有 left join 数据;
– 2. 分组,id,name分别组装为字符串
– 3. 带入查询条件、分页
– 4. 查询返回后 java 组装(新增字符串字段)
<resultMap id="ProductCheckListPoolVoMap" type="com.lenovo.npi.portal.module.masterdata.vo.ProductCheckListPoolVo">
<id column="id" jdbcType="INTEGER" property="id"/>
<result column="phase_dict_code" jdbcType="VARCHAR" property="phaseDictCode"/>
<result column="phase" jdbcType="VARCHAR" property="phase"/>
<result column="sort" jdbcType="INTEGER" property="sort"/>
<result column="level1" jdbcType="VARCHAR" property="level1"/>
<result column="level2" jdbcType="VARCHAR" property="level2"/>
<result column="owner" jdbcType="VARCHAR" property="owner"/>
<result column="active_dict_code" jdbcType="VARCHAR" property="activeDictCode"/>
<result column="active" jdbcType="VARCHAR" property="active"/>
<result column="update_by" jdbcType="VARCHAR" property="updateBy"/>
<result column="update_time" jdbcType="TIMESTAMP" property="updateTime"/>
<result column="designTypeIds" jdbcType="VARCHAR" property="designTypeIds"/>
<result column="designTypes" jdbcType="VARCHAR" property="designTypes"/>
</resultMap>
<sql id="poolCommonSql">
with t_all as(
SELECT dpclp.*
,sd2.dict_name as phase
,sd3.dict_name as active
,dio.name as designType
,dio.id as designTypeId
from dim_product_check_list_pool as dpclp
left join dim_inhouse_odm as dio on dio.id = dpclp.design_type_id
left join sys_dict as sd2 on sd2.dict_code=dpclp.phase_dict_code
left join sys_dict as sd3 on sd3.dict_code=dpclp.active_dict_code
)
,t_concat as(
SELECT
ta.id,ta.phase,ta.sort,ta.level1,ta.level2,ta.owner,ta.active,ta.update_by,ta.update_time
,array_to_string(ARRAY_AGG(ta.designTypeId), ',') as designTypeIds
,array_to_string(ARRAY_AGG(ta.designType),',') as designTypes
from t_all as ta
group by ta.id,ta.phase,ta.sort,ta.level1,ta.level2,ta.owner,ta.active,ta.update_by,ta.update_time
)
</sql>
<select id="getByKey" resultMap="ProductCheckListPoolVoMap">
<include refid="poolCommonSql"></include>
SELECT * from t_concat as tc
<include refid="pageWhere"></include>
<if test="param.sortName==null or param.sortName==''">
order by update_time desc
</if>
<if test="param.sortName!=null and param.sortBy!=null and param.sortName!='' and param.sortBy!=''">
order by
${@org.apache.commons.lang.StringEscapeUtils@escapeSql(param.sortName)}
${@org.apache.commons.lang.StringEscapeUtils@escapeSql(param.sortBy)}
</if>
<if test="param.pageSize > 0">
limit #{param.pageSize} offset #{param.offset}
</if>
</select>
<sql id="pageWhere">
<where>
<if test="param.key != null and param.key != ''">
LOWER(concat(tc.designTypes,tc.phase,tc.sort,tc.level1,tc.level2,tc.owner,tc.active,tc.update_by))
like LOWER(concat('%',#{param.key},'%'))
</if>
</where>
</sql>