一、一对多查询---用户vs订单
继上篇文档继续!!!
1、加入Orders.java
package top.einino.po;
import java.util.Date;
public class Orders {
private Integer id;
private Integer userId;
private String number;
private Date createTime;
private String note;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public Integer getUserId() {
return userId;
}
public void setUserId(Integer userId) {
this.userId = userId;
}
public String getNumber() {
return number;
}
public void setNumber(String number) {
this.number = number;
}
public Date getCreateTime() {
return createTime;
}
public void setCreateTime(Date createTime) {
this.createTime = createTime;
}
public String getNote() {
return note;
}
public void setNote(String note) {
this.note = note;
}
@Override
public String toString() {
return "Orders [id=" + id + ", userId=" + userId + ", number=" + number
+ ", createTime=" + createTime + ", note=" + note + "]";
}
}
2、新建orders表
CREATE TABLE `orders` (
`id` int(11) NOT NULL auto_increment,
`user_id` int(11) NOT NULL COMMENT '下单用户id',
`number` varchar(32) NOT NULL COMMENT '订单号',
`createtime` datetime NOT NULL COMMENT '创建订单时间',
`note` varchar(100) default NULL COMMENT '备注',
PRIMARY KEY (`id`),
KEY `FK_orders_1` (`user_id`),
CONSTRAINT `FK_orders_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
3、在User.java中加入
public List<Orders> orders;
public List<Orders> getOrders() {
return orders;
}
public void setOrders(List<Orders> orders) {
this.orders = orders;
}
4、在UserMapper.xml中加入
<resultMap type="user" id="userordersmap" extends="usermap">
<collection property="orders" ofType="top.einino.po.Orders">
<id property="id" column="order_id"/>
<result property="userId" column="id"/>
<result property="createTime" column="createTime"/>
<result property="number" column="number"/>
<result property="note" column="note"/>
</collection>
</resultMap>
<!-- 查询有订单的用户以及关联订单 -->
<select id="findUserOrders" resultMap="userordersmap">
select user.*,
orders.id order_id,
orders.number,
orders.note,
orders.createTime
from user, orders
where user.id = orders.user_id
</select>
5、在UserMapper.java中加入
//查询有订单的用户以及其他关联的所有订单
public List<User> findUserOrders();
6、在TestUserMapper.java中加入
//查询有订单的用户以及关联的所有的订单
@Test
public void findUserOrders(){
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> findUserOrders = mapper.findUserOrders();
if(findUserOrders != null && findUserOrders.size() > 0){
for(User user : findUserOrders ){
System.out.println(user);
List<Orders> orders = user.getOrders();
if(orders != null && orders.size() > 0){
for(Orders order : orders){
System.out.println(order);
}
}
}
}
}
二、一对一查询---订单vs用户
1、在Orders.java加入
private User user;
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
2、新建OrdersMapper.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="top.einino.mapper.OrdersMapper">
<resultMap type="orders" id="ordersusermap" >
<id property="id" column="id"/>
<result property="userId" column="user_id"/>
<result property="createTime" column="createTime"/>
<result property="number" column="number"/>
<result property="note" column="note"/>
<association property="user" javaType="top.einino.po.User">
<id property="id" column="user_id"/>
<result property="username" column="username"/>
<result property="gender" column="sex"/>
<result property="birthday" column="birthday"/>
<result property="address" column="address"/>
</association>
</resultMap>
<!-- 查询有订单的用户以及关联订单 -->
<select id="findOrdersUser" resultMap="ordersusermap">
select orders.*,
user.username,
user.birthday,
user.sex,
user.address
from orders,user
where orders.user_id = user.id
</select>
</mapper>
3、新建OrdersMapper.java
package top.einino.mapper;
import java.util.List;
import top.einino.po.Orders;
public interface OrdersMapper {
//查询所有的订单并且关联用户
public List<Orders> findOrdersUser();
}
4、测试
@Test
public void findOrdersUser(){
SqlSession sqlSession = sqlSessionFactory.openSession();
OrdersMapper mapper = sqlSession.getMapper(OrdersMapper.class);
List<Orders> findOrdersUser = mapper.findOrdersUser();
if(findOrdersUser != null && findOrdersUser.size() > 0 ){
for(Orders order: findOrdersUser){
System.out.println(order+" "+order.getUser() );
}
}
三、小结
该博文总结了mybatis对于一对多关联和一对一关联的处理方式。
在一对多关联中,需要在一的一方加入多的一方的集合List,并且在写resultMap时,使用collection 标签。
在一对一关联中,需要在一的一方加入一的一方的对象,并且在写resultMap进,使用association 标签。
如果有疑问或者对该博文有何看法或建议或有问题的,欢迎评论,恳请指正!