关联查询
现在有两个表,用户表与订单表,其关系如下:
下面用Mybatis实现一对一与一对多查询
一对一:
准备两个表与数据
DROP TABLE IF EXISTS `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 AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
INSERT INTO `orders` VALUES ('3', '1', '1000010', '2015-02-04 13:22:35', null);
INSERT INTO `orders` VALUES ('4', '1', '1000011', '2015-02-03 13:22:41', null);
INSERT INTO `orders` VALUES ('5', '10', '1000012', '2015-02-12 16:13:23', null);
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(32) NOT NULL COMMENT '用户名称',
`birthday` date DEFAULT NULL COMMENT '生日',
`sex` char(1) DEFAULT NULL COMMENT '性别',
`address` varchar(256) DEFAULT NULL COMMENT '地址',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8;
INSERT INTO `user` VALUES ('1', '王五', null, '2', null);
INSERT INTO `user` VALUES ('10', '张三', '2014-07-10', '1', '北京市');
INSERT INTO `user` VALUES ('16', '张小明', null, '1', '河南郑州');
INSERT INTO `user` VALUES ('22', '陈小明', null, '1', '河南郑州');
INSERT INTO `user` VALUES ('24', '张三丰', null, '1', '河南郑州');
INSERT INTO `user` VALUES ('25', '陈小明', null, '1', '河南郑州');
INSERT INTO `user` VALUES ('26', '王五', null, null, null);
实现方法一:使用resultType实现
Orders类
package com.hyx3.one_to_one.pojo;
import javax.jws.soap.SOAPBinding;
import java.util.Date;
public class Orders {
private Integer id;
private Integer user_id;
private String number;
private Date createtime;
private String note;
private User user;
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public Integer getUser_id() {
return user_id;
}
public void setUser_id(Integer user_id) {
this.user_id = user_id;
}
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 +
", user_id=" + user_id +
", number='" + number + '\'' +
", createTime=" + createtime +
", note='" + note + '\'' +
'}'+user;
}
}
User类
package com.hyx3.one_to_one.pojo;
import java.util.Date;
import java.util.List;
public class User {
private Integer id;
private String username;// 用户姓名
private String sex;// 性别
private Date birthday;// 生日
private String address;// 地址
private List<Orders> ordersList;
public User() {
}
public List<Orders> getOrdersList() {
return ordersList;
}
public void setOrdersList(List<Orders> ordersList) {
this.ordersList = ordersList;
}
public User(String username, String sex, Date birthday, String address) {
this.username = username;
this.sex = sex;
this.birthday = birthday;
this.address = address;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", sex='" + sex + '\'' +
", birthday=" + birthday +
", address='" + address + '\'' +
", ordersList=" + ordersList +
'}';
}
}
(a)改造POJO:目前已经拥有的实体,没有一个能够映射所有的字段,需要重新创建一个POJO类OrderCustomer,创建一个类,继承字段多的POJO类
package com.hyx3.one_to_one.pojo;
public class OrderCustomer extends Orders {
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() {
String string = super.toString();
return "OrderCustomer{" +
"username='" + username + '\'' +
", address='" + address + '\'' +
'}'+string;
}
}
(b)OrdersMapper接口
package com.hyx3.one_to_one.mapper;
import com.hyx3.one_to_one.pojo.OrderCustomer;
import com.hyx3.one_to_one.pojo.Orders;
import com.hyx3.one_to_one.pojo.User;
import java.util.List;
public interface OrderMapper {
// 1---1
public List<OrderCustomer> findUserOrders();
}
(c)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="com.hyx3.one_to_one.mapper.OrderMapper">
<select id="findUserOrders" resultType="ordercustomer">
select orders.* ,u.username , u.address from orders,user u where u.id = orders.user_id;
</select>
</mapper>
(d)测试类
package com.hyx3.one_to_one.test;
import com.hyx3.one_to_one.mapper.OrderMapper;
import com.hyx3.one_to_one.pojo.OrderCustomer;
import com.hyx3.one_to_one.pojo.Orders;
import com.hyx3.one_to_one.pojo.User;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Before;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
public class Demo01 {
private SqlSessionFactory sqlSessionFactory;
@Before
public void fun0() throws IOException {
InputStream inputStream = Resources.getResourceAsStream("one_to_one_sqlMapconfig.xml");
//通过配置创建会话工厂
sqlSessionFactory = new SqlSessionFactoryBuilder().build( inputStream );
}
@Test
public void fun(){
SqlSession sqlSession = sqlSessionFactory.openSession();
OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);
List<OrderCustomer> userOrders = mapper.findUserOrders();
for(OrderCustomer oc : userOrders){
System.out.println(oc);
}
}
}
实现方法一:使用resultMap实现
OrderMapper.class
package com.hyx3.one_to_one.mapper;
import com.hyx3.one_to_one.pojo.OrderCustomer;
import com.hyx3.one_to_one.pojo.Orders;
import com.hyx3.one_to_one.pojo.User;
import java.util.List;
public interface OrderMapper {
// 1---1
public List<Orders> findUserOrdersResultMap();
}
OrderMapper.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="com.hyx3.one_to_one.mapper.OrderMapper">
<resultMap id="orderUserMap" type="orders">
<id column="id" property="id"></id>
<result column="user_id" property="user_id"></result>
<result column="number" property="number"></result>
<result column="note" property="note"></result>
<association property="user" javaType="User">
<id column="user_id" property="id"></id>
<result column="username" property="username"></result>
<result column="address" property="address"></result>
</association>
</resultMap>
<select id="findUserOrdersResultMap" resultMap="orderUserMap">
select orders.* ,u.username , u.address from orders,user u where u.id = orders.user_id;
</select>
</mapper>
测试代码
package com.hyx3.one_to_one.test;
import com.hyx3.one_to_one.mapper.OrderMapper;
import com.hyx3.one_to_one.pojo.OrderCustomer;
import com.hyx3.one_to_one.pojo.Orders;
import com.hyx3.one_to_one.pojo.User;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Before;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
public class Demo01 {
private SqlSessionFactory sqlSessionFactory;
@Before
public void fun0() throws IOException {
InputStream inputStream = Resources.getResourceAsStream("one_to_one_sqlMapconfig.xml");
//通过配置创建会话工厂
sqlSessionFactory = new SqlSessionFactoryBuilder().build( inputStream );
}
@Test
public void fun1(){
SqlSession sqlSession = sqlSessionFactory.openSession();
OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);
List<Orders> userOrders = mapper.findUserOrdersResultMap();
for(Orders oc : userOrders){
System.out.println(oc);
}
}
}
总结:
实现一对一查询
ResultType:
使用resultType比较简单,如果没有对应POJO,就创建一个对应POJO即可。
如果,查询结果没有特殊性的要求,就建议使用ResultType
ResultMap:
需要单独定义ResultMap,实现有些麻烦,
如果 对查询的结果有特殊要求(指定结果)使用ResultMap,将关联查询的结果映射到POJO的属性中。
ResultMap可以实现延迟加载,ResultType无法实现延迟加载。
Order对象(顶层)包含User对象(第二层),如果暂未没有用到User属性,就不查询User的信息。
一对多
一对多的关系,只能用ResultMap实现,无法使用resultType实现。
修改POJO,User中添加一个新的属性
Mapper接口:
package com.hyx3.one_to_one.mapper;
import com.hyx3.one_to_one.pojo.OrderCustomer;
import com.hyx3.one_to_one.pojo.Orders;
import com.hyx3.one_to_one.pojo.User;
import java.util.List;
public interface OrderMapper {
//1-----n
public List<User> findUserOrder();
}
Mapper.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="com.hyx3.one_to_one.mapper.OrderMapper">
<resultMap id="userOrderMap" type="User">
<id column="id" property="id"></id>
<result column="username" property="username"></result>
<result column="birthday" property="birthday"></result>
<result column="sex" property="sex"></result>
<result column="address" property="address"></result>
<collection property="ordersList" javaType="list" ofType="Orders">
<id column="oid" property="id"></id>
<result column="createtime" property="createtime"></result>
</collection>
</resultMap>
<select id="findUserOrder" resultMap="userOrderMap">
select u.* ,o.id oid, o.createtime from user u left join orders o on u.id = o.user_id;
</select>
</mapper>
测试类
package com.hyx3.one_to_one.test;
import com.hyx3.one_to_one.mapper.OrderMapper;
import com.hyx3.one_to_one.pojo.OrderCustomer;
import com.hyx3.one_to_one.pojo.Orders;
import com.hyx3.one_to_one.pojo.User;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Before;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
public class Demo01 {
private SqlSessionFactory sqlSessionFactory;
@Before
public void fun0() throws IOException {
InputStream inputStream = Resources.getResourceAsStream("one_to_one_sqlMapconfig.xml");
//通过配置创建会话工厂
sqlSessionFactory = new SqlSessionFactoryBuilder().build( inputStream );
}
@Test
public void fun2(){
SqlSession sqlSession = sqlSessionFactory.openSession();
OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);
List<User> userOrder = mapper.findUserOrder222();
for(User oc : userOrder){
System.out.println(oc);
}
}
}
总结
Mybatis使用resultMap的collection对关联插入的多条记录映射到一个List集合属性中。
如果使用ResultType实现:只能自己手动处理数据,需要使用双层循环遍历,合并记录
ResultMap和ResultType总结
ResultType:
作用:
将查询结果按照SQL列名和POJO属性名一致,进行映射
场景:
常见的一些明细记录,比如:用户购买商品明细,将关联查询全部信息都要展示在页面上,此时可以直接使用ResultType,将一条记录映射到POJO中。前端页面变量List<POJO>
数据只有一层的情况。
ResultMap:
使用collection和association完成一对多和一对一高级映射(对结果又特殊的映射要求)
Association:
作用::将关联查询的信息映射到一个POJO对象中
场景:一对一,查询订单以及关联用户
Collection:
作用:将关系查询的信息映射到一个集合List中。
场景: 一对多,查询用户以及关联订单
多对多
多对多的关系,需要借助第三表来存储这个关系,无论是添加、还是删除、查询,本质都是对第三张表进行的操作。
商品和订单的关系
用户和角色的关系
用户和权限的关系