mybatis多对一关联查询

本例用orders和person做例子进行多对一关联查询

1、多对一的例子

第一步：在orders.java中指定和person多对一的关系

 //指定和person之间多对一的关系
    private Person person;

    public Person getPerson() {
        return person;
    }

    public void setPerson(Person person) {
        this.person = person;
    }

第二步：在OrdersMapper.xml中进行配置

<!--
    association用于多对一关联查询
    property:一的一端的属性的名字
    javaType:一的一端属性的类型
    -->
     <resultMap id="selectPersonByIdRm" type="com.model.Orders" extends="BaseResultMap">
         <association property="person" javaType="com.model.Person">
             <id column="PERSON_ID" property="personId" jdbcType="INTEGER" />
             <result column="NAME" property="name" jdbcType="VARCHAR" />
             <result column="GENDER" property="gender" jdbcType="INTEGER" />
             <result column="PERSON_ADDR" property="personAddr" jdbcType="VARCHAR" />
             <result column="BIRTHDAY" property="birthday" jdbcType="DATE" />
         </association>
     </resultMap>
    <select id="selectPersonById" parameterType="int" resultMap="selectPersonByIdRm">
         SELECT *FROM orders o ,person p
         WHERE o.PERSON_ID=p.PERSON_ID AND o.ORDER_ID=#{identity };
    </select>

第三步：测试

 @Test
    public void testSelect() {
        SqlSession session = sf.openSession();
        try {
            Orders o = session.selectOne("mapper.OrdersMapper.selectPersonById", 1);
            System.out.println(o);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            session.close();
        }
    }

2、将多对一和一对多进行联合

 需求是通过订单查询出订单的人和订单明细三者关系如下所示：

由上图可知订单和人员是多对一 订单和订单明细是一对多
所以联合查询的sql:SELECT *FROM orders o,person p,order_detail od WHERE o.PERSON_ID=p.PERSON_ID AND o.ORDER_ID=od.ORDER_ID AND o.ORDER_ID=1;

第一步：在orders.java中分别指定和person多对一的关系，和order_details一对多关系

//指定和orderDetail一对多的关系
    private List<OrderDetail> orderDetails;

    //指定和person之间多对一的关系
    private Person person;

    public Person getPerson() {
        return person;
    }

    public void setPerson(Person person) {
        this.person = person;
    }

    public List<OrderDetail> getOrderDetails() {
        return orderDetails;
    }

    public void setOrderDetails(List<OrderDetail> orderDetails) {
        this.orderDetails = orderDetails;
    }

第二步：在OrdersMapper.xml中进行配置

 <!--
    如果association和collection都有则一定要把association放在collection上面
    -->
    <resultMap id="selectPersonAndOrderDetailByIdRm" type="com.model.Orders" extends="BaseResultMap">
        <association property="person" javaType="com.model.Person">
            <id column="PERSON_ID" property="personId" jdbcType="INTEGER" />
            <result column="NAME" property="name" jdbcType="VARCHAR" />
            <result column="GENDER" property="gender" jdbcType="INTEGER" />
            <result column="PERSON_ADDR" property="personAddr" jdbcType="VARCHAR" />
            <result column="BIRTHDAY" property="birthday" jdbcType="DATE" />
        </association>
        <collection property="orderDetails" ofType="com.model.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>
    </resultMap>
    <select id="selectPersonAndOrderDetailById" parameterType="int" resultMap="selectPersonAndOrderDetailByIdRm">
        SELECT *FROM orders o,person p,order_detail od
        WHERE o.PERSON_ID=p.PERSON_ID AND o.ORDER_ID=od.ORDER_ID AND o.ORDER_ID=#{identity };
    </select>

第三步：测试类

  @Test
    public void testSelectPeronAndOrderDetail() {
        SqlSession session = sf.openSession();
        try {
            Orders o = session.selectOne("mapper.OrdersMapper.selectPersonAndOrderDetailById", 1);
            System.out.println(o);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            session.close();
        }
    }