/*
select o.id as oid,
o.user_id ,
o.number,
o.createtime,
o.note,
u.id as uid,
u.username,
u.birthday,
u.sex,
u.address
from `order` o left join `user` u
on o.user_id=u.id;
*/
List<OrderUer> findAllOrderUser();
<select id="queryOrderAndUser" resultType="OrderUser">
SELECT
o.id ,user_id userId,number,createtime,note,u.id uid,username,birthday,sex,address
FROM orderx o LEFT JOIN USER u
ON o.user_id = u.id;
</select>
public class OrderUser {
// 订单id
private int id;
// 用户id
private Integer userId;
// 订单号
private String number;
// 订单创建时间
private Date createtime;
// 备注
private String note;
private int uid;
private String username;
private Date birthday;
private String sex;
private String address;
关联查询(一对多)
分析下属性 从用户表的角度看,一个用户可以多次购买,产生多个订单。使用左连接查询每个用户的订单。
查询结果如何转换成JavaBean? User { List orders; }
<!--3:查询每个用户下的多个订单: 一对多--><resultMapid="userOrderMap"type="User"autoMapping="true"><!--1:指定User类的字段和属性之间的映射关系--><idcolumn="uid"property="id"></id><!--2:指定User类中的Order字段和属性之间的映射关系--><collectionproperty="orders"javaType="list"ofType="Order"autoMapping="true"><idcolumn="id"property="id"></id><resultcolumn="user_id"property="userId"></result></collection></resultMap><selectid="queryOrdersOfUser"resultMap="userOrderMap">
SELECT
u.id uid,u.username,u.birthday,u.sex,u.address,o.id ,o.user_id ,o.number,o.createtime,o.note
FROM USER u INNER JOIN orderx o
ON o.user_id = u.id;
</select>
//3:查询每个用户下的多个订单: 一对多
public List<User> queryOrdersOfUser();