【Mybatis框架】多表关联实现（一对一，一对多，多对多）

最新推荐文章于 2023-01-11 16:59:49 发布

谦风（Java）

最新推荐文章于 2023-01-11 16:59:49 发布

阅读量561

点赞数

分类专栏： # Mybatis框架文章标签： mybatis java

本文链接：https://blog.csdn.net/weixin_43316702/article/details/105894671

版权

Mybatis框架专栏收录该内容

5 篇文章 0 订阅

订阅专栏

多表关联即是指数据库多个表进行关联，即有一对一，一对多，多对多三种情况，以用户与订单的关系来讲 一对一有一个订单对应一个用户，一对多有一个用户对应多个订单，一个订单对应多个订单详情，多对多有多个用户对应多个订单详情，此处订单详情是指商品信息。

案例：【一对一放在多对多一起讲】
与上一篇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配置与增删查改操作

感谢学习。(✪ω✪)