1 一对一查询
以用户和订单为例,以订单为中心,一个订单只能属于一个用户
1.1 方法一,使用resultType
1.1.1 新建一个OrderUser,继承Order
package com.itykd.domain;
public class OrderUser extends Order{
private String username;
private String address;
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
@Override
public String toString() {
return "OrderUser [username=" + username + ", address=" + address + "]";
}
}
1.1.2 编写mapper.xml,并引入主配置文件
<!--一对一关联,方法一:使用resultType -->
<select id ="getOrderToUserByResultType" resultType="orderUser">
select o.id,o.user_id userId,o.createtime,o.note,u.username,u.address
FROM orders o LEFT JOIN user u ON u.id = o.user_id
</select>
1.2 方法二,使用resultMap(推荐)
使用resultMap更加符合面向对象的思想
1.2.1 在Order类中添加一个User属性
package com.itykd.domain;
import java.util.Date;
public class Order {
private int id;
private Integer userId;
private String number;
private Date createtime;
private String note;
private User user;
//省略get、set方法
}
1.2.2 编写Mapper.xml
这里分为两部分,一部分是通过resultMap建立一对一的联系,这里的id(order_map_user)要与sql语句中的resultMap的名称一样
<!-- 一对一关联(Order对User):resultMap -->
<!-- 这里type为order,即映射到order,因为我们已经在order这个pojo类中定义了一个User,所以以它为基准 -->
<resultMap type="order" id="order_map_user">
<id property="id" column="id" />
<result property="userId" column="user_id" />
<result property="number" column="number" />
<result property="createtime" column="createtime" />
<result property="note" column="note" />
<!-- association: 配置一对一属性-->
<!-- property:order类的User属性名(一个Order对应一个User) -->
<!-- javaType:属性类型 -->
<association property="user" javaType="user">
<!-- id:声明主键,表示user_id是关联查询的唯一标识 -->
<id property="id" column="user_id"/>
<result property="username" column="username"/>
<result property="address" column="address"/>
<result property="sex" column="sex"/>
</association>
</resultMap>
<!--一对一关联,方法二:使用resultMap -->
<select id ="getOrderToUserByResultMap" resultMap="order_map_user">
select o.id,o.user_id,o.createtime,o.note,u.username,u.address,u.sex
FROM orders o LEFT JOIN user u ON u.id = o.user_id
</select>
2 一对多查询
以用户和订单为例,以用户为中心,而一个用户可以下多个订单
2.1 在User类中添加一个List<Order>成员
package com.itykd.domain;
import java.util.Date;
import java.util.List;
public class User {
private int id;
private int[] ids;
private String username;
private String sex;
private Date birthday;
private String address;
private String uuid;
private List<Order> orderList;
}
2.2 编写Mapper.xml
<resultMap type="user" id="user_map_order">
<id property="id" column="id"/>
<result property="username" column="username"/>
<result property="birthday" column="birthday"/>
<result property="sex" column="sex"/>
<result property="address" column="address"/>
<!--配置一对多关系用collection标签 -->
<collection property="orderList" ofType="order">
<!-- 配置主键,是关联order的唯一标识 -->
<id property="id" column="oid"/>
<result property="number" column="number"/>
<result property="createtime" column="createtime"/>
<result property="note" column="note"/>
</collection>
</resultMap>
<select id="getUserToOrderByResultMap" resultMap="user_map_order">
SELECT u.id,u.username,u.birthday,u.sex,u.address,o.id oid,
o.number,o.createtime,o.note FROM`user` u
EFT JOIN `orders` o ON u.id = o.user_id
</select>