背景:有规则表t_rule,标签表t_label,中间表t_rule_label,根据t_rule的name和t_label表的name组合查询规则
t_rule表数据
id | name |
---|---|
1 | rule1 |
2 | rule2 |
t_label表数据
id | name |
---|---|
1 | label1 |
2 | label2 |
t_rule_label表数据
rule_id | label_id |
---|---|
1 | 1 |
1 | 2 |
2 | 1 |
2 | 2 |
使用mybatis-plus 查询
Page<CheckRule> page = new Page<>(query.getPageNum(), query.getPageSize());
select rule.id,rule.name,label.id label_id,label.name label_name
from t_rule rule
left join t_rule_label con on con.rule_id = rule.id
left join t_label label on label.id = con.label_id
where rule.name like '%ru%' and label.id in ('1','2')
left join 导致查询出4条数据
page.getTotal() 和 page.getRecords() 对应不上
因为一对多
导致getTotal()数量比getRecords()多,getRecords()为resultMap 组合之后的数据
<resultMap id="ruleResultMap" type="com.yss.rule.entity.Rule">
<id column="id" property="id" />
<result column="name" property="name" />
<collection property="labelList" ofType="com.yss.rule.entity.Label" >
<id column="lable_id" property="id" />
<result column="lable_name" property="name" />
</collection>
</resultMap>
<select id="getRules" resultMap="rulListMap">
select rule.id,rule.name,label.id label_id,label.name label_name
from t_rule rule
left join t_rule_label con on con.rule_id = rule.id
left join t_label label on label.id = con.label_id
<where>
<if test="ruleAndLabelVo.ruleName !=null and ruleAndLabelVo.ruleName !=''">
and rule.name like '%${ruleAndLabelVo.ruleName }%'
</if>
<if test="ruleAndLabelVo.labelIds !=null and ruleAndLabelVo.labelIds.size() !=0">
and lable.id in
<foreach collection="ruleAndLabelVo.labelIds" index="index" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</if>
</where>
</select>
行转列解决
对于oracle 行转列
wmsys.wm_concat,oracle 10g推出的函数,12c以后被去掉 select
id,wmsys.wm.concat(name) lable_name from t_label group by id
LISTAGG,是oracle11g推出的函数 select id, listagg(lable_name,’,’) within
group (order by lable_name) lable_name from t_label group by id;
对于mysql 行转列
SELECT GROUP_CONCAT(cast(
user_id
as char(10)) SEPARATOR ‘,’) as id
from user;
先分页再组装
public class RuleAndLabelVo{
//前端传递条件
private String ruleName;
private List<String> labelIds;
//第一次查询出的t_rule的主键集合
private List<String> ruleIds;
}
第一次查询,分页查询,查询出t_rule的主键集合
<select id="getRulesPage" resultMap="string">
select distinct rule.id
from t_rule rule
left join t_rule_label con on con.rule_id = rule.id
left join t_label label on label.id = con.label_id
<where>
<if test="ruleAndLabelVo.ruleName !=null and ruleAndLabelVo.ruleName !=''">
and rule.name like '%${ruleAndLabelVo.ruleName }%'
</if>
<if test="ruleAndLabelVo.labelIds !=null and ruleAndLabelVo.labelIds.size() !=0">
and lable.id in
<foreach collection="ruleAndLabelVo.labelIds" index="index" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</if>
</where>
order by rule.id
</select>
第二次查询,将t_rule的主键集合带入查询
List ruleIds = checkRuleMapper.getRulesPage(page,
ruleByRuleOrLabelVo); ruleAndLabelVo.setRuleIds(ruleIds);
<resultMap id="ruleResultMap" type="com.yss.rule.entity.Rule">
<id column="id" property="id" />
<result column="name" property="name" />
<collection property="labelList" ofType="com.yss.rule.entity.Label" >
<id column="lable_id" property="id" />
<result column="lable_name" property="name" />
</collection>
</resultMap>
<select id="getRules" resultMap="rulListMap">
select rule.id,rule.name,label.id label_id,label.name label_name
from t_rule rule
left join t_rule_label con on con.rule_id = rule.id
left join t_label label on label.id = con.label_id
<where>
<if test="ruleAndLabelVo.ruleIds !=null and ruleAndLabelVo.ruleIds.size() !=0">
and rule.id in
<foreach collection="ruleAndLabelVo.ruleIds" index="index" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</if>
</where>
</select>