maybatis一对多/多对一查询

最新推荐文章于 2023-06-13 22:16:09 发布

没有梦想何必远方。

最新推荐文章于 2023-06-13 22:16:09 发布

阅读量309

点赞数

分类专栏： mybatis

本文链接：https://blog.csdn.net/qq_41799291/article/details/88932481

版权

mybatis 专栏收录该内容

4 篇文章 1 订阅

订阅专栏

1.mybatis 一对多的三种实现方式（查询1的一方把多的一方查出来）：

方式1：

1的一方：

<?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.finance.cmp.ruleEngine.dao.mapper.TRuleMapper">
    <resultMap id="BaseResultMap" type="com.finance.cmp.ruleEngine.dao.model.TRule">
        <!--
          WARNING - @mbg.generated
        -->
        <id column="id" jdbcType="INTEGER" property="id"/>
        <result column="rule_name" jdbcType="VARCHAR" property="ruleName"/>
        <result column="rule_expressions" jdbcType="VARCHAR" property="ruleExpressions"/>
        <result column="status" jdbcType="VARCHAR" property="status"/>
        <result column="create_time" jdbcType="TIMESTAMP" property="createTime"/>
        <result column="update_time" jdbcType="TIMESTAMP" property="updateTime"/>
        //一对多 conllection标签
        //property：一的类中持有多的集合的引用 
        // ofType：多的一方集合的类型 
        //select ：在多的一方的xml中写查询语句 
        // 把要按什么条件查的参数传入多的一方的sql
        <collection property="tRuleFactors" ofType="com.finance.cmp.ruleEngine.dao.model.TRuleFactor" select="com.finance.cmp.ruleEngine.dao.mapper.TRuleFactorMapper.selectFactorByRuleId" column="id">

        </collection>
    </resultMap>


    <select id="selectById" parameterType="map" resultMap="BaseResultMap">
    SELECT
	*
    FROM
      t_rule a
    WHERE
      a.id = #{id}
  </select>

 

</mapper>

多的一方的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.finance.cmp.ruleEngine.dao.mapper.TRuleFactorMapper">
    <resultMap id="BaseResultMap" type="com.finance.cmp.ruleEngine.dao.model.TRuleFactor">
        <!--
          WARNING - @mbg.generated
        -->
        <id column="id" jdbcType="INTEGER" property="id"/>
        <result column="rule_id" jdbcType="INTEGER" property="ruleId"/>
        <result column="factor_in_expression" jdbcType="VARCHAR" property="factorInExpression"/>
        <result column="factor_var_type" jdbcType="VARCHAR" property="factorVarType"/>
        <result column="factor_var_id" jdbcType="INTEGER" property="factorVarId"/>
        <result column="status" jdbcType="VARCHAR" property="status"/>
        <result column="create_time" jdbcType="TIMESTAMP" property="createTime"/>
        <result column="update_time" jdbcType="TIMESTAMP" property="updateTime"/>
    </resultMap>
    <select id="selectFactorByRuleId" parameterType="int" resultMap="BaseResultMap">
        SELECT * FROM t_rule_factor WHERE rule_id=#{0}
  </select>
</mapper>

方式2：联合查询（left join）

案例中t_strategy 为1的一方，t_strategy_flow为2的一方：
t_strategy 的xml如下按t_strategy 的id查t_strategy 表要把与之关联的t_strategy_flow的数据查出来：

<?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.finance.cmp.ruleEngine.dao.mapper.TStrategyMapper">
  <resultMap id="BaseResultMap" type="com.finance.cmp.ruleEngine.dao.model.TStrategy">
    <!--
      WARNING - @mbg.generated
    -->
    //两表id重复 故两表id都有取别名
    <id column="sid" jdbcType="INTEGER" property="id" />
    <result column="strategy_code" jdbcType="VARCHAR" property="strategyCode" />
    <result column="strategy_name" jdbcType="VARCHAR" property="strategyName" />
    <result column="child_strategy_id" jdbcType="INTEGER" property="childStrategyId" />
    <result column="sstatus" jdbcType="VARCHAR" property="status" />
    <result column="create_time" jdbcType="TIMESTAMP" property="createTime" />
    <result column="update_time" jdbcType="TIMESTAMP" property="updateTime" />
    //一对多标签collection 
    //ofType：集合的类型（实体的全路径）
    <collection property="tStrategyFlows" ofType="com.finance.cmp.ruleEngine.dao.model.TStrategyFlow">
    //把多的一方的属性写在下面 用于映射
      <id column="fid" property="id"></id>
      <result column="strategy_id" jdbcType="INTEGER" property="strategyId" />
      <result column="sub_strategy_id" jdbcType="INTEGER" property="subStrategyId" />
      <result column="card_id" jdbcType="INTEGER" property="cardId" />
      <result column="positive_next_sub_strategy" jdbcType="INTEGER" property="positiveNextSubStrategy" />
      <result column="negative_next_sub_strategy" jdbcType="INTEGER" property="negativeNextSubStrategy" />
      <result column="nohit_next_rule_sub_strategy" jdbcType="INTEGER" property="nohitNextRuleSubStrategy" />

      <result column="card_score_compare_para" jdbcType="DECIMAL" property="cardScoreComparePara" />
      <result column="score_gt_para_next_card" jdbcType="INTEGER" property="scoreGtParaNextCard" />
      <result column="score_eq_para_next_card" jdbcType="INTEGER" property="scoreEqParaNextCard" />
      <result column="score_lt_para_next_card" jdbcType="INTEGER" property="scoreLtParaNextCard" />
      <result column="priority" jdbcType="INTEGER" property="priority" />
      <result column="fstatus" jdbcType="VARCHAR" property="status" />
    </collection>
  </resultMap>

  <select id="selectById" parameterType="map" resultMap="BaseResultMap">
      SELECT
	s.id sid,
	s.strategy_code,
	s.strategy_name,
	s.create_time,
	s.update_time,
	s.`status` sstatus,
	f.id fid,
	f.strategy_id,
	f.sub_strategy_id,
	f.card_id,
	f.positive_next_sub_strategy,
	f.negative_next_sub_strategy,
	f.nohit_next_rule_sub_strategy,
	f.card_score_compare_para,
	f.score_gt_para_next_card,
	f.score_eq_para_next_card,
	f.score_lt_para_next_card,
	f.priority,
	f.`status` fstatus
FROM
	t_strategy s
LEFT JOIN t_strategy_flow f ON s.id = f.strategy_id
WHERE
	s.id = #{id}
  </select>



</mapper>

效果为：
在这里插入图片描述

2.多对一查询：

查多的一方查全部也好按id查也好查出与之对应的那一个关联对象

多的一方的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.finance.cmp.ruleEngine.dao.mapper.TRuleFactorMapper">
    <resultMap id="BaseResultMap" type="com.finance.cmp.ruleEngine.dao.model.TRuleFactor">
        <!--
          WARNING - @mbg.generated
        -->
        <id column="id" jdbcType="INTEGER" property="id"/>
        <result column="rule_id" jdbcType="INTEGER" property="ruleId"/>
        <result column="factor_in_expression" jdbcType="VARCHAR" property="factorInExpression"/>
        <result column="factor_var_type" jdbcType="VARCHAR" property="factorVarType"/>
        <result column="factor_var_id" jdbcType="INTEGER" property="factorVarId"/>
        <result column="status" jdbcType="VARCHAR" property="status"/>
        <result column="create_time" jdbcType="TIMESTAMP" property="createTime"/>
        <result column="update_time" jdbcType="TIMESTAMP" property="updateTime"/>
        //多对一标签association
        //select：在一的一方写sql 通过namespace的路径把select的名字写上 
        //column：一的一方sql的条件 按多表的什么字段查
        <association property="tStrategy" select="com.finance.cmp.ruleEngine.dao.mapper.TRuleMapper.getId" column="rule_id">

        </association>
    </resultMap>
    //查多表的全部也好 按id查也好
    <select id="selectById" parameterType="int" resultMap="BaseResultMap">
        SELECT * FROM t_rule_factor WHERE id=#{id}
  </select>
</mapper>

一的一方的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.finance.cmp.ruleEngine.dao.mapper.TRuleMapper">
    <resultMap id="BaseResultMap" type="com.finance.cmp.ruleEngine.dao.model.TRule">
        <!--
          WARNING - @mbg.generated
        -->
        <id column="id" jdbcType="INTEGER" property="id"/>
        <result column="rule_name" jdbcType="VARCHAR" property="ruleName"/>
        <result column="rule_expressions" jdbcType="VARCHAR" property="ruleExpressions"/>
        <result column="status" jdbcType="VARCHAR" property="status"/>
        <result column="create_time" jdbcType="TIMESTAMP" property="createTime"/>
        <result column="update_time" jdbcType="TIMESTAMP" property="updateTime"/>
        <collection property="tRuleFactors" ofType="com.finance.cmp.ruleEngine.dao.model.TRuleFactor" select="com.finance.cmp.ruleEngine.dao.mapper.TRuleFactorMapper.selectFactorByRuleId" column="id">

        </collection>
    </resultMap>
    <select id="getId" parameterType="int" resultMap="BaseResultMap">
       select  * from t_rule WHERE id =#{0}
    </select>
</mapper>

多对一方式2：也可联合查询（left join）

没有梦想何必远方。

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
maybatis一对多/多对一查询

1.mybatis 一对多的三种实现方式（查询1的一方把多的一方查出来）：方式1：1的一方：<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.d...
复制链接

扫一扫