mybatis（二）---关联表查询

来源：互联网发布：javascript return; 编辑：程序博客网时间：2024/05/21 11:06

一、一对多查询---用户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>

<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 标签。

如果有疑问或者对该博文有何看法或建议或有问题的，欢迎评论，恳请指正！

0 0