多表关联即是指数据库多个表进行关联,即有一对一,一对多,多对多三种情况,以用户与订单的关系来讲 一对一有一个订单对应一个用户,一对多有一个用户对应多个订单,一个订单对应多个订单详情,多对多有多个用户对应多个订单详情,此处订单详情是指商品信息。
案例:【一对一 放在 多对多 一起讲】
与上一篇mybatis增删查改不同的是,这次是需要对多个表进行操作。
UserMapper.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:命名空间,用于隔离sql -->
<!-- 还有一个很重要的作用,使用动态代理开发DAO,1. namespace必须和Mapper接口类路径一致 -->
<mapper namespace="com.mybatis.mapper.UserMapper">
<resultMap type="com.mybatis.po.User" id="userOrderResultMap">
<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 property="orders" javaType="List" ofType="com.mybatis.po.Order">
<!-- 配置主键,是关联Order的唯一标识 -->
<id property="id" column="id" />
<result property="order_id" column="order_id" />
<result property="createtime" column="createtime" />
<result property="note" column="note" />
</collection>
</resultMap>
<resultMap type="com.mybatis.po.User" id="userCategoryResultMap">
<!--用户信息-->
<id property="id" column="id" />
<result property="username" column="username" />
<!--订单信息(一对多)-->
<collection property="orders" javaType="List" ofType="com.mybatis.po.Order">
<id column="id" property="id"/>
<result column="user_id" property="user_id"/>
<result column="order_id" property="order_id"/>
<!--订单详情信息(一对多)-->
<collection property="orderitemList" javaType="List" ofType="com.mybatis.po.Orderitem">
<id column="id" property="id"/>
<result column="order_id" property="order_id"/>
<result column="item_num" property="item_num"/>
<result column="cate_id" property="cate_id"/>
<result column="item_id" property="item_id"/>
<!--商品信息(一对一)-->
<association property="category" javaType="com.mybatis.po.Category">
<id column="id" property="id"/>
<result column="cate_id" property="cate_id"/>
<result column="cname" property="cname"/>
</association>
</collection>
</collection>
</resultMap>
<!-- 根据用户ID查询用户信息-->
<!-- select:表示一个MappingStatement对象-->
<!-- id:statement的唯一标识-->
<!-- #{}:表示一个占位符?-->
<!-- #{id}:里面的id表示输入参数的参数名称,如果该参数是简单类型,那么#{}里面的参数名称可以任意-->
<!-- parameterType:输入参数的java类型-->
<!-- resultType:输出结果的所映射的java类型(单条结果所对应的的java类型)-->
<!-- 一对多关联,查询订单同时查询该用户下的订单 -->
<select id="queryUserOrder" resultMap="userOrderResultMap">
SELECT u.id,u.username,u.birthday,u.sex,u.address,o.id,o.order_id,o.createtime,o.note FROM `user` u LEFT JOIN `order` o ON u.id = o.user_id
</select>
<!-- 多对多关联,查询订单内的商品信息-->
<select id="queryUserCategoryMap" resultMap="userCategoryResultMap">
SELECT o.id,o.user_id,u.username,o.order_id,o.createtime,o.note,i.cate_id,c.cname,i.item_num FROM `order` o
LEFT JOIN `user` u ON o.user_id = u.id
LEFT JOIN `orderitem` i ON i.order_id = o.order_id
LEFT JOIN `category` c ON c.cate_id = i.cate_id
</select>
</mapper>
一对一使用的标签是 association
一对多使用的是 collection
表关联语句:left join
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:命名空间,用于隔离sql -->
<!-- 还有一个很重要的作用,使用动态代理开发DAO,1. namespace必须和Mapper接口类路径一致 -->
<mapper namespace="com.mybatis.mapper.OrderMapper">
<!-- id:设置ResultMap的id -->
<resultMap type="com.mybatis.po.Order" id="orderResultMap">
<!-- 定义主键 ,非常重要。如果是多个字段,则定义多个id -->
<!-- property:主键在pojo中的属性名 -->
<!-- column:主键在数据库中的列名 -->
<id property="id" column="id" />
<!-- 定义普通属性 -->
<result property="user_id" column="user_id" />
<result property="order_id" column="order_id" />
<result property="createtime" column="createtime" />
<result property="note" column="note"/>
</resultMap>
<resultMap type="com.mybatis.po.Order" id="orderUserResultMap">
<id property="id" column="id" />
<result property="user_id" column="user_id" />
<result property="order_id" column="order_id" />
<result property="createtime" column="createtime" />
<result property="note" column="note" />
<!-- association :配置一对一属性 -->
<!-- property:order里面的User属性名 -->
<!-- javaType:属性类型 -->
<association property="user" javaType="com.mybatis.po.User">
<!-- id:声明主键,表示user_id是关联查询对象的唯一标识-->
<id property="id" column="user_id" />
<result property="username" column="username" />
<result property="address" column="address" />
</association>
</resultMap>
<!-- 根据用户ID查询用户信息-->
<!-- select:表示一个MappingStatement对象-->
<!-- id:statement的唯一标识-->
<!-- #{}:表示一个占位符?-->
<!-- #{id}:里面的id表示输入参数的参数名称,如果该参数是简单类型,那么#{}里面的参数名称可以任意-->
<!-- parameterType:输入参数的java类型-->
<!-- resultType:输出结果的所映射的java类型(单条结果所对应的的java类型)-->
<!-- 查询所有的订单数据 -->
<select id="queryOrderAll" resultMap="orderResultMap" >
SELECT id, user_id, order_id, createtime, note FROM `order`
</select>
<select id="queryOrderUser" resultMap="orderUserResultMap">
SELECT o.id,o.user_id,o.order_id,o.createtime,o.note,u.username,u.address FROM `order` o
LEFT JOIN `user` u ON o.user_id = u.id
</select>
</mapper>
UserMapper接口:
package com.mybatis.mapper;
import com.mybatis.po.OrderUser;
import com.mybatis.po.User;
import java.util.List;
public interface UserMapper {
//一对多关联,查询用户信息同时包涵订单
List<User> queryUserOrder();
//多对多查询,查询用户信息包含商品详情
List<User> queryUserCategoryMap();
}
OrderMapper接口:
<?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:命名空间,用于隔离sql -->
<!-- 还有一个很重要的作用,使用动态代理开发DAO,1. namespace必须和Mapper接口类路径一致 -->
<mapper namespace="com.mybatis.mapper.OrderMapper">
<!-- id:设置ResultMap的id -->
<resultMap type="com.mybatis.po.Order" id="orderResultMap">
<!-- 定义主键 ,非常重要。如果是多个字段,则定义多个id -->
<!-- property:主键在pojo中的属性名 -->
<!-- column:主键在数据库中的列名 -->
<id property="id" column="id" />
<!-- 定义普通属性 -->
<result property="user_id" column="user_id" />
<result property="order_id" column="order_id" />
<result property="createtime" column="createtime" />
<result property="note" column="note"/>
</resultMap>
<resultMap type="com.mybatis.po.Order" id="orderUserResultMap">
<id property="id" column="id" />
<result property="user_id" column="user_id" />
<result property="order_id" column="order_id" />
<result property="createtime" column="createtime" />
<result property="note" column="note" />
<!-- association :配置一对一属性 -->
<!-- property:order里面的User属性名 -->
<!-- javaType:属性类型 -->
<association property="user" javaType="com.mybatis.po.User">
<!-- id:声明主键,表示user_id是关联查询对象的唯一标识-->
<id property="id" column="user_id" />
<result property="username" column="username" />
<result property="address" column="address" />
</association>
</resultMap>
<!-- 根据用户ID查询用户信息-->
<!-- select:表示一个MappingStatement对象-->
<!-- id:statement的唯一标识-->
<!-- #{}:表示一个占位符?-->
<!-- #{id}:里面的id表示输入参数的参数名称,如果该参数是简单类型,那么#{}里面的参数名称可以任意-->
<!-- parameterType:输入参数的java类型-->
<!-- resultType:输出结果的所映射的java类型(单条结果所对应的的java类型)-->
<!-- 查询所有的订单数据 -->
<select id="queryOrderAll" resultMap="orderResultMap" >
SELECT id, user_id, order_id, createtime, note FROM `order`
</select>
<select id="queryOrderUser" resultMap="orderUserResultMap">
SELECT o.id,o.user_id,o.order_id,o.createtime,o.note,u.username,u.address FROM `order` o
LEFT JOIN `user` u ON o.user_id = u.id
</select>
</mapper>
User:
package com.mybatis.po;
import java.io.Serializable;
import java.util.Date;
import java.util.List;
public class User implements Serializable {
private int id;
private String username;
private String sex;
private Date birthday;
private String address;
private List<Order> orders;
public User(){}
public User(String username,Date birthday, String sex, String address) {
this.username = username;
this.sex = sex;
this.birthday = birthday;
this.address = address;
}
public int getId() {
return id;
}
public void setId(int 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;
}
public List<Order> getOrders() {
return orders;
}
public void setOrders(List<Order> orders) {
this.orders = orders;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", sex='" + sex + '\'' +
", birthday=" + birthday +
", address='" + address + '\'' +
", orders=" + orders +
'}';
}
}
Order:
package com.mybatis.po;
import java.util.Date;
import java.util.List;
public class Order {
// 订单id
private int id;
// 用户id
private Integer user_id;
// 订单号
private String order_id;
// 订单创建时间
private Date createtime;
// 备注
private String note;
private User user;
private List<Orderitem> orderitemList;
public Order() {
}
public int getId() {
return id;
}
public void setId(int 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 getOrder_id() {
return order_id;
}
public void setOrder_id(String order_id) {
this.order_id = order_id;
}
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;
}
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
public List<Orderitem> getOrderitemList() {
return orderitemList;
}
public void setOrderitemList(List<Orderitem> orderitemList) {
this.orderitemList = orderitemList;
}
@Override
public String toString() {
return "Order{" +
"id=" + id +
", user_id=" + user_id +
", order_id='" + order_id + '\'' +
", createtime=" + createtime +
", note='" + note + '\'' +
", user=" + user +
", orderitemList=" + orderitemList +
'}';
}
}
OrderUser:(Order的子类)
package com.mybatis.po;
public class OrderUser extends Order {
private String username;
private String address;
public OrderUser() {
}
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{" +
"id=" + super.getId() +
", order_id='" + super.getOrder_id() + '\'' +
", createtime=" + super.getCreatetime() +
", note='" + super.getNote() + '\'' +
",username='" + username + '\'' +
", address='" + address + '\'' +
'}';
}
}
Orderitem:(订单详情)
package com.mybatis.po;
import java.util.List;
public class Orderitem {
private int id;
private String order_id;
private String item_id;
private String cate_id;
private int item_num;
private Category category;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getOrder_id() {
return order_id;
}
public void setOrder_id(String order_id) {
this.order_id = order_id;
}
public String getItem_id() {
return item_id;
}
public void setItem_id(String item_id) {
this.item_id = item_id;
}
public String getCate_id() {
return cate_id;
}
public void setCate_id(String cate_id) {
this.cate_id = cate_id;
}
public int getItem_num() {
return item_num;
}
public void setItem_num(int item_num) {
this.item_num = item_num;
}
public Category getCategory() {
return category;
}
public void setCategory(Category category) {
this.category = category;
}
@Override
public String toString() {
return "Orderitem{" +
"id=" + id +
", order_id='" + order_id + '\'' +
", item_id='" + item_id + '\'' +
", cate_id='" + cate_id + '\'' +
", item_num=" + item_num +
", category=" + category +
'}';
}
}
Category:
package com.mybatis.po;
public class Category {
private int id;
private String cname;
private String cate_id;
private float cprice;
private int cnum;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getCname() {
return cname;
}
public void setCname(String cname) {
this.cname = cname;
}
public String getCate_id() {
return cate_id;
}
public void setCate_id(String cate_id) {
this.cate_id = cate_id;
}
public float getCprice() {
return cprice;
}
public void setCprice(float cprice) {
this.cprice = cprice;
}
public int getCnum() {
return cnum;
}
public void setCnum(int cnum) {
this.cnum = cnum;
}
public void setNum(int num) {
this.cnum = num;
}
@Override
public String toString() {
return "Category{" +
"id=" + id +
", cname='" + cname + '\'' +
", cate_id='" + cate_id + '\'' +
", cprice=" + cprice +
", num=" + cnum +
'}';
}
}
测试类:
UserMapperTest:
package com.mybatis.test;
import com.mybatis.po.OrderUser;
import com.mybatis.po.User;
import com.mybatis.mapper.UserMapper;
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.InputStream;
import java.util.Date;
import java.util.List;
public class UserMapperTest {
private SqlSessionFactory sqlSessionFactory;
@Before
public void init() throws Exception {
// 创建SqlSessionFactoryBuilder
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
// 加载SqlMapConfig.xml配置文件
InputStream inputStream = Resources.getResourceAsStream("SqlMapConfig.xml");
// 创建SqlsessionFactory
this.sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream);
}
@Test
public void testQueryUserOrder(){
SqlSession sqlSession = this.sqlSessionFactory.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List<User> list = userMapper.queryUserOrder();
for (User user:list){
System.out.println(user);
}
// 和spring整合后由spring管理
sqlSession.close();
}
@Test
public void testQueryUserCategory(){
SqlSession sqlSession = this.sqlSessionFactory.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List<User> list = userMapper.queryUserCategoryMap();
for (User user:list){
System.out.println(user);
}
// 和spring整合后由spring管理
sqlSession.close();
}
OrderMapperTest:
package com.mybatis.test;
import com.mybatis.mapper.OrderMapper;
import com.mybatis.po.Order;
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.InputStream;
import java.util.List;
public class OrderMappingTest {
private SqlSessionFactory sqlSessionFactory;
@Before
public void init() throws Exception {
// 创建SqlSessionFactoryBuilder
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
// 加载SqlMapConfig.xml配置文件
InputStream inputStream = Resources.getResourceAsStream("SqlMapConfig.xml");
// 创建SqlsessionFactory
this.sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream);
}
@Test
public void testQueryOrderAll(){
//获取SqlSession
SqlSession sqlSession = this.sqlSessionFactory.openSession();
//从SqlSession获取Mapping接口的代理对象
OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class);
//执行查询
List<Order> list = orderMapper.queryOrderAll();
for (Order order:list){
System.out.println(order);
}
// 和spring整合后由spring管理
sqlSession.close();
}
@Test
public void QueryOrderUserResultMap(){
//获取SqlSession
SqlSession sqlSession = this.sqlSessionFactory.openSession();
//从SqlSession获取Mapping接口的代理对象
OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class);
//执行查询
List<Order> list = orderMapper.queryOrderUser();
for (Order order:list){
System.out.println(order);
}
// 和spring整合后由spring管理
sqlSession.close();
}
}
一对一结果:
一对多结果:
(以一个结果来看,orders是应该有多个的,由于数据没弄,但看括号类型可以看出)
User{id=1, username=‘关羽’, sex=‘男’, birthday=Wed Mar 04 00:00:00 CST 2020, address=‘广东省深圳市’, orders=[Order{id=1, user_id=null, order_id=‘10010’, createtime=Sat Mar 28 09:36:46 CST 2020, note=‘文具’, user=null, orderitemList=null}]}
多对多结果:
User{id=1, username=‘关羽’, sex=‘null’, birthday=null, address=‘null’, orders=[Order{id=1, user_id=1, order_id=‘10010’, createtime=null, note=‘null’, user=null, orderitemList=[Orderitem{id=1, order_id=‘10010’, item_id=‘null’, cate_id=‘10’, item_num=2, category=Category{id=1, cname=‘圆珠笔’, cate_id=‘10’, cprice=0.0, num=0}}]}]}
如有缺失配置文件,参考Mybatis配置与增删查改操作
感谢学习。(✪ω✪)