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