MyBatis多对一，一对多，多对多，一对多关联查询

一、Person实体类

 1 public class Person {
 2     private Integer personId;
 3     private String name;
 4     private Integer gender;
 5     private String personAddr;
 6     private Date birthday;  
 7     //与订单的关系是一对多
 8     private List<Orders> orderList;
 9     //与角色是一对多关系
10     private List<Role> roleList;  
11     //与身份证是一对一关系
12     private IdCard ic;
13 }

二、mapper映射文件相关配置

 1 <!-- =====================一对多关联查询============================ -->
 2   <resultMap type="person" id="selectOrderByPersonIdRm" extends="BaseResultMap">
 3       <collection property="orderList" ofType="cn.tx.model1.Orders">
 4           <id column="ORDER_ID" property="orderId" jdbcType="INTEGER" />
 5         <result column="PERSON_ID" property="personId" jdbcType="INTEGER" />
 6         <result column="TOTAL_PRICE" property="totalPrice" jdbcType="REAL" />
 7         <result column="ADDR" property="addr" jdbcType="VARCHAR" />
 8       </collection>
 9   </resultMap>
10   <select id="selectOrderByPersonId" parameterType="int" resultMap="selectOrderByPersonIdRm">
11       SELECT * from person p, orders o where p.PERSON_ID = o.PERSON_ID and p.PERSON_ID = #{personId};
12   </select>
<resultMap type="person" id="selectOrderAndDetailByPersonIdRM" extends="BaseResultMap">
      <collection property="orderList" ofType="cn.tx.model1.Orders">
          <id column="ORDER_ID" property="orderId" jdbcType="INTEGER" />
        <result column="PERSON_ID" property="personId" jdbcType="INTEGER" />
        <result column="TOTAL_PRICE" property="totalPrice" jdbcType="REAL" />
        <result column="ADDR" property="addr" jdbcType="VARCHAR" />
        <collection property="detailList" ofType="cn.tx.model1.OrderDetail">
            <id column="DETAIL_ID" property="detailId" jdbcType="INTEGER" />
            <result column="ORDER_ID" property="orderId" jdbcType="INTEGER" />
            <result column="ITEM_NAME" property="itemName" jdbcType="VARCHAR" />
            <result column="PRICE" property="price" jdbcType="REAL" />
            <result column="QUANTITY" property="quantity" jdbcType="INTEGER" />
        </collection>
      </collection>
  </resultMap>
  <select id="selectOrderAndDetailByPersonId" parameterType="int" resultMap="selectOrderAndDetailByPersonIdRM">
      SELECT * from person p, orders o,order_detail od where p.PERSON_ID = o.PERSON_ID and o.ORDER_ID = od.ORDER_ID and p.PERSON_ID = #{personId}
  </select>
<resultMap type="person" id="selectPersonAndRoleByPersonIdRM" extends="BaseResultMap">
      <collection property="roleList" ofType="cn.tx.model1.Role">
          <id column="ROLE_ID" property="roleId" jdbcType="INTEGER" />
        <result column="ROLE_NAME" property="roleName" jdbcType="VARCHAR" />
        <result column="DESCPRIT" property="descprit" jdbcType="VARCHAR" />
      </collection>
  </resultMap>
  <select id="selectPersonAndRoleByPersonId" parameterType="int" resultMap="selectPersonAndRoleByPersonIdRM">
      SELECT * from person p,person_role pr,role r where p.PERSON_ID=pr.PERSON_ID AND pr.ROLE_ID=r.ROLE_ID AND p.PERSON_ID=#{id}
  </select>

 

posted @ 2018-07-12 12:00  cat_fish  阅读(1349)  评论(0)  编辑  收藏  举报