Mybatis一对多关联查询

1、一对多关联查询

本例使用person和orders做例子。person和orders是一对多关系。

1.1实体类

person.java
在person.java中指定和orders的关系即设置一个list集合(和hibernate用法一样)

public class Person {
    private Integer personId;

    private String name;

    private Integer gender;

    private String personAddr;

    private Date birthday;

    private List<Orders> ordersList;

    public List<Orders> getOrdersList() {
        return ordersList;
    }

    public void setOrdersList(List<Orders> ordersList) {
        this.ordersList = ordersList;
    }

    public Integer getPersonId() {
        return personId;
    }

    public void setPersonId(Integer personId) {
        this.personId = personId;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getGender() {
        return gender;
    }

    public void setGender(Integer gender) {
        this.gender = gender;
    }

    public String getPersonAddr() {
        return personAddr;
    }

    public void setPersonAddr(String personAddr) {
        this.personAddr = personAddr;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    @Override
    public String toString() {
        return "Person{" +
                "personId=" + personId +
                ", name='" + name + '\'' +
                ", gender=" + gender +
                ", personAddr='" + personAddr + '\'' +
                ", birthday=" + birthday +
                ", ordersList=" + ordersList +
                '}';
    }
}

orders.java

public class Orders {
    private Integer orderId;

    private Integer personId;

    private Float totalPrice;

    private String addr;

    public Integer getOrderId() {
        return orderId;
    }

    public void setOrderId(Integer orderId) {
        this.orderId = orderId;
    }

    public Integer getPersonId() {
        return personId;
    }

    public void setPersonId(Integer personId) {
        this.personId = personId;
    }

    public Float getTotalPrice() {
        return totalPrice;
    }

    public void setTotalPrice(Float totalPrice) {
        this.totalPrice = totalPrice;
    }

    public String getAddr() {
        return addr;
    }

    public void setAddr(String addr) {
        this.addr = addr;
    }
}

1.2在PersonMapper.xml中配置和orders一对多的关系

设置结果映射集

 其中collection是orders的结果映射 里面两个属性
 property:集合的名字  ofType:集合元素的类型
 extends继承的是person自身的结果映射
 collection里面的内容可以直接拷贝orders的结果映射

<resultMap id="selectResultMap" type="com.model.Person" extends="BaseResultMap">
        <collection property="ordersList" ofType="com.model.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>
    </resultMap>

1.3selec语句编写

<select id="selectOrders" parameterType="int" resultMap="selectResultMap">
         SELECT *FROM person p ,orders o WHERE p.PERSON_ID=o.PERSON_ID and p.PERSON_ID=#{identity };
    </select>

1.4测试类

 @Test
    public void testSelect() {
        SqlSession session = sf.openSession();
        try {
            Person person = session.selectOne("mapper.PersonMapper.selectOrders", 2);
            System.out.println(person);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

2、一对多Collection的嵌套使用

需求 根据person 查出orders和orderDetails
sql：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=2;

2.1在orde中指定和orderDetail一对多的关系

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

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

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

2.2在person映射文件中配置select

首先配置结果映射集 在和orders一对多的结果映射中配置和orderDetail一对多的关系

    <resultMap id="selectOrderAndOrderDetailRM" type="com.model.Person" extends="BaseResultMap">
        <!--和ordeers一对多的结果映射-->
        <collection property="ordersList" ofType="com.model.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" />
            <!--配置orders和orderDetail一对多的结果映射-->
            <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>
        </collection>
    </resultMap>

select查询语句

    <select id="selectOrderAndOrderDetail" parameterType="int" resultMap="selectOrderAndOrderDetailRM">
      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=#{identity };
    </select>

2.3测试类

  @Test
    public void testSelectOrders() {
        SqlSession session = sf.openSession();
        try {
            Person person = session.selectOne("mapper.PersonMapper.selectOrderAndOrderDetail", 2);
            System.out.println(person);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            session.close();
        }
    }

mybatis中一对多或者多对一的时候出现表间字段名重复会出现解决如下：
http://www.mamicode.com/info-detail-1921215.html