11.2.0 注解的使用示例4 多对多关联关系

11.2 注解的使用 多对多关联关系

例如商品和订单之间是多对多关系,即一个商品可以放在多个订单之中,而一个订单中放多个不同类型的商品。
对于这种多对多关联关系,

• 在查询商品时,使用@Many注解来关联查询包含该商品的所有订单。
• 在查询订单时,同样使用@Many注解来关联查询该订单中的所有商品信息。

示例 多对多关联关系

项目结构

G:\workspace_web2\MyAManyToManyTest
├─src
│ ├─db.properties
│ ├─domain
│ │ ├─Article.java
│ │ ├─Order.java
│ │ └─User.java
│ ├─fractory
│ │ └─SqlSessionFactoryTools.java
│ ├─log4j.xml
│ ├─manytomany.sql
│ ├─mapper
│ │ ├─ArticleMapper.java
│ │ ├─OrderMapper.java
│ │ └─UserMapper.java
│ ├─mybatis-config.xml
│ └─test
│   ├─SelectArticleByIdTest.java
│   ├─SelectOrderByIdTest.java
│   └─SelectUserByIdTest.java
└─WebContent
  └─WEB-INF
    └─lib
      ├─commons-logging-1.2.jar
      ├─log4j-1.2.17.jar
      ├─log4j-api-2.3.jar
      ├─log4j-core-2.3.jar
      ├─mybatis-3.4.5.jar
      └─mysql-connector-java-5.1.44-bin.jar

数据库表

-- 关闭外键检查
SET FOREIGN_KEY_CHECKS = 0;
use mybatis;
-- 创建用户表
DROP TABLE IF EXISTS `tb_user`;
CREATE TABLE `tb_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(18) DEFAULT NULL,
  `loginname` varchar(18) NOT NULL,
  `password` varchar(18) NOT NULL,
  `phone` varchar(18) DEFAULT NULL,
  `address` varchar(18) DEFAULT NULL,
  PRIMARY KEY (`id`)
);
-- 插入用户
INSERT INTO `tb_user` VALUES ('1', '小明', 'xiaoming', 'xiaoming', '123456789123', '北京');
-- 创建商品表
DROP TABLE IF EXISTS `tb_article`;
CREATE TABLE `tb_article` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(18) DEFAULT NULL,
  `price` double DEFAULT NULL,
  `remark` varchar(18) DEFAULT NULL,
  PRIMARY KEY (`id`)
);
-- 插入商品
INSERT INTO `tb_article` VALUES ('1', '商品1', '123.12', 'xxx的伟大著作');
INSERT INTO `tb_article` VALUES ('2', '商品2', '12.3', 'yyy的伟大著作');
INSERT INTO `tb_article` VALUES ('3', '商品3', '34.22', 'zzz的著作');
-- 创建订单表
DROP TABLE IF EXISTS `tb_order`;
CREATE TABLE `tb_order` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(32) DEFAULT NULL,
  `total` double DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `tb_order_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tb_user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
);
-- 插入订单
INSERT INTO `tb_order` VALUES ('1', 'abcseeeahoaugoeijgiej', '223.33', '1');
INSERT INTO `tb_order` VALUES ('2', 'sfaofosfhodsfuefie', '111.22', '1');
-- 创建订单-商品关系表
DROP TABLE IF EXISTS `tb_item`;
CREATE TABLE `tb_item` (
  `order_id` int(11) NOT NULL DEFAULT '0',
  `article_id` int(11) NOT NULL DEFAULT '0',
  `amount` int(11) DEFAULT NULL,
  PRIMARY KEY (`order_id`,`article_id`),
  KEY `article_id` (`article_id`),
  CONSTRAINT `tb_item_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `tb_order` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tb_item_ibfk_2` FOREIGN KEY (`article_id`) REFERENCES `tb_article` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
);
-- 插入商品-订单
INSERT INTO `tb_item` VALUES ('1', '1', '1');
INSERT INTO `tb_item` VALUES ('1', '2', '1');
INSERT INTO `tb_item` VALUES ('1', '3', '3');
INSERT INTO `tb_item` VALUES ('2', '1', '2');
INSERT INTO `tb_item` VALUES ('2', '2', '3');
-- 开启外键检查
SET FOREIGN_KEY_CHECKS = 1;

持久化对象

User.java

/MyAManyToManyTest/src/domain/User.java

package domain;

import java.util.List;

public class User {
    // 用户id
    private Integer id;
    // 用户姓名
    private String username;
    // 登录名
    private String loginname;
    // 密码
    private String password;
    // 手机号
    private String phone;
    // 地址
    private String address;
    // 用户的订单列表
    List<Order> orders;

    public User() {
    }

    public User(String username, String loginname, String password, String phone, String address) {
        super();
        this.username = username;
        this.loginname = loginname;
        this.password = password;
        this.phone = phone;
        this.address = address;
    }

    // 此处省略getter和setter方法,请自己补上
    @Override
    public String toString() {
        return "User [id=" + id + ", username=" + username + ", loginname=" + loginname + ", password=" + password
                + ", phone=" + phone + ", address=" + address + "]";
    }
}

Order.java

/MyAManyToManyTest/src/domain/Order.java

package domain;
import java.util.List;
public class Order {
    // 订单编号
    private Integer id;
    // 订单描述
    private String code;
    // 订单总金额
    private double total;
    // 该订单对应的用户
    private User user;
    // 该订单中的商品列表
    List<Article> articles;
    public Order() {
    }
    public Order(String code, double total, User user) {
        super();
        this.code = code;
        this.total = total;
        this.user = user;
    }
    // 此处省略getter和setter方法,请自己补上
    public List<Article> getArticles() {
        return articles;
    }
    public void setArticles(List<Article> articles) {
        this.articles = articles;
    }
    @Override
    public String toString() {
        return "Order [id=" + id + ", code=" + code + ", total=" + total + "]";
    }
}

Article.java

package domain;
import java.util.List;
public class Article {
    // 商品编号
    private Integer id;
    // 商品名称
    private String name;
    // 商品价格
    private double price;
    // 商品描述
    private String remark;
    // 包含该商品的订单列表
    List<Order> orders;
    
    public Article()
    {
        super();
    }

    public Article(String name, double price, String remark)
    {
        super();
        this.name = name;
        this.price = price;
        this.remark = remark;
    }
    // 此处省略getter和setter方法,请自己补上
    @Override
    public String toString()
    {
        return "Article [id=" + id + ", name=" + name + ", price=" + price + ", remark=" + remark
                + "]";
    }
}

工具类SqlSessionFactoryTools.java

/MyAManyToManyTest/src/fractory/SqlSessionFactoryTools.java

package fractory;

import java.io.IOException;
import java.io.InputStream;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

public class SqlSessionFactoryTools {
    private static SqlSessionFactory sqlSessionFactory = null;
    static
    {
        try
        {
            InputStream mybatisConfigXML = Resources.getResourceAsStream("mybatis-config.xml");
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(mybatisConfigXML);
        } catch (IOException e)
        {
            e.printStackTrace();
        }
    }
    public static SqlSession getSqlSession()
    {
        return sqlSessionFactory.openSession();
    }
}

mybatis相关配置文件

db.propertis

# 保存为db.properties文件,然后在mybatis-config.xml中通过下面标签引入:
# <properties resource="db.properties"/>
# 下面的标签放在mybatis-config.xml文件的environments标签的environment子标签的子标签dataSource标签中
# <property name="driver" value="${driver}"/><property name="url" value="${url}"/><property name="username" value="${username}"/><property name="password" value="${password}"/>
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mybatis
username=root
password=root

log4j.xml

/MyAManyToManyTest/src/log4j.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE log4j:configuration 
    PUBLIC "-//LOG4J//DTD LOG4J//EN" 
    "https://logging.apache.org/log4j/1.2/apidocs/org/apache/log4j/xml/doc-files/log4j.dtd" >
<!-- 请在mybatis-config.xml中配置如下设置 -->
<!-- <settings> -->
<!-- <setting -->
<!-- name="logImpl" -->
<!-- value="log4j"/> -->
<!-- </settings> -->
<log4j:configuration>
    <appender name="STDOUT" class="org.apache.log4j.ConsoleAppender">
        <layout class="org.apache.log4j.PatternLayout">
            <param name="ConversionPattern" value="%5p [%t] %m%n" />
        </layout>
    </appender>
    <logger name="mapper">
        <level value="DEBUG" />
    </logger>
    <root>
        <level value="ERROR" />
        <appender-ref ref="STDOUT" />
    </root>
</log4j:configuration>

mybatis-config.xml

/MyAManyToManyTest/src/mybatis-config.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
  PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-config.dtd">
<!-- 该配置文件包含对 MyBatis 系统的核心设置 -->
<configuration>
    <properties resource="db.properties" />
    <settings>
        <setting name="logImpl" value="log4j" />
    </settings>
    <environments default="mysql">
        <environment id="mysql">
            <transactionManager type="JDBC" />
            <dataSource type="pooled">
                <property name="driver" value="${driver}" />
                <property name="url" value="${url}" />
                <property name="username" value="${username}" />
                <property name="password" value="${password}" />
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper class="mapper.UserMapper" />
        <mapper class="mapper.OrderMapper" />
        <mapper class="mapper.ArticleMapper" />
    </mappers>
</configuration>

测试查询用户

UserMapper.java

/MyAManyToManyTest/src/mapper/UserMapper.java

package mapper;

import java.util.List;
import org.apache.ibatis.annotations.Many;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.mapping.FetchType;
import domain.Order;
import domain.User;

public interface UserMapper {
    @Select("select * from tb_user where id=#{id}")
    @Results({
        @Result(column = "id",property = "id", id = true),
        @Result(column = "username",property = "username"),
        @Result(column = "loginname",property = "loginname"),
        @Result(column = "password",property = "password"),
        @Result(column = "phone",property = "phone"),
        @Result(column = "address",property = "address"),
        @Result(column = "id",property = "orders", 
            many = @Many(select = "selectOrdersByUserId", fetchType = FetchType.LAZY)
        ) 
    })
    User selectUserById(Integer id);

    @Select("select id,code,total from tb_order where user_id=#{id}")
    List<Order> selectOrdersByUserId(Integer id);
}

SelectUserByIdTest.java

/MyAManyToManyTest/src/test/SelectUserByIdTest.java

package test;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import domain.Order;
import domain.User;
import fractory.SqlSessionFactoryTools;
import mapper.UserMapper;
public class SelectUserByIdTest {
    public static void main(String[] args)
    {
        SqlSession sqlSession = null;
        try
        {
            // 加载mybatis-config.xml,获取SqlSession实例
            sqlSession = SqlSessionFactoryTools.getSqlSession();
            // 获取mapper接口代理对象
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            User user = userMapper.selectUserById(1);
            System.out.println("    User [id=" + user.getId() + ", username=" + user.getUsername()
                    + ", loginname=" + user.getLoginname() + ", password=" + user.getPassword()
                    + ", phone=" + user.getPhone() + ", address=" + user.getAddress() + "]");
            System.out.println(
                "------------------------------------------------------------------------------");
            System.out.println("该用户的订单列表:");
            List<Order> orders = user.getOrders();
            orders.forEach(order -> System.out.println("    " + order));
            // 提交事务
            sqlSession.commit();
        } catch (Exception e)
        {
            // 出错回滚事务
            sqlSession.rollback();
            e.printStackTrace();
        } finally
        {
            // 关闭会话
            if (sqlSession != null)
                sqlSession.close();
        }
    }
}

运行效果

DEBUG [main] ==>  Preparing: select * from tb_user where id=? 
DEBUG [main] ==> Parameters: 1(Integer)
DEBUG [main] <==      Total: 1
    User [id=1, username=小明, loginname=xiaoming, password=xiaoming, phone=123456789123, address=北京]
------------------------------------------------------------------------------
该用户的订单列表:
DEBUG [main] ==>  Preparing: select id,code,total from tb_order where user_id=? 
DEBUG [main] ==> Parameters: 1(Integer)
DEBUG [main] <==      Total: 2
    Order [id=1, code=abcseeeahoaugoeijgiej, total=223.33]
    Order [id=2, code=sfaofosfhodsfuefie, total=111.22]

测试查询订单

OrderMapper.java

/MyAManyToManyTest/src/mapper/OrderMapper.java

package mapper;

import java.util.List;
import org.apache.ibatis.annotations.Many;
import org.apache.ibatis.annotations.One;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.mapping.FetchType;
import domain.Article;
import domain.Order;
import domain.User;

public interface OrderMapper {
    @Select("select * from tb_order where id=#{id}")
    @Results({
        @Result(column = "id",property = "id", id = true),
        @Result(column = "code",property = "code"),
        @Result(column = "total",property = "total"),
        @Result(column = "user_id",property = "user",
            one = @One(select = "selectUserByOrderId", fetchType = FetchType.LAZY)),
        @Result(column = "id",property = "articles", 
            many = @Many(select = "selectArticlesByOrderId", fetchType = FetchType.LAZY)
        ) 
    })
    Order selectOrderById(Integer id);

    @Select("select * from tb_user where id=#{id}")
    User selectUserByOrderId(Integer id);

    @Select("select id,name,price,remark from tb_article where id in(select article_id from tb_item where order_id=#{id})")
    List<Article> selectArticlesByOrderId(Integer id);
}

SelectOrderByIdTest.java

/MyAManyToManyTest/src/test/SelectOrderByIdTest.java

package test;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import domain.Article;
import domain.Order;
import domain.User;
import fractory.SqlSessionFactoryTools;
import mapper.OrderMapper;
public class SelectOrderByIdTest {
    public static void main(String[] args)
    {
        SqlSession sqlSession = null;
        try
        {
            // 加载mybatis-config.xml,获取SqlSession实例
            sqlSession = SqlSessionFactoryTools.getSqlSession();
            // 获取mapper接口代理对象
            OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class);
            Order order = orderMapper.selectOrderById(1);
            System.out.println("---------------------------------------------------------------");
            System.out.println("Order [id=" + order.getId() + ",code=" + order.getCode() + ",total="
                    + order.getTotal() + "]");
            System.out.println("---------------------------------------------------------------");
            User user = order.getUser();
            System.out.println("订单对应的用户:" + user);
            System.out.println("---------------------------------------------------------------");
            List<Article> articles = order.getArticles();
            System.out.println("订单中的商品列表:");
            articles.forEach(article -> System.out.println("    " + article));
            // 提交事务
            sqlSession.commit();
        } catch (Exception e)
        {
            // 出错回滚事务
            sqlSession.rollback();
            e.printStackTrace();
        } finally
        {
            // 关闭会话
            if (sqlSession != null)
                sqlSession.close();
        }
    }
}

运行结果

DEBUG [main] ==>  Preparing: select * from tb_order where id=? 
DEBUG [main] ==> Parameters: 1(Integer)
DEBUG [main] <==      Total: 1
---------------------------------------------------------------
Order [id=1,code=abcseeeahoaugoeijgiej,total=223.33]
---------------------------------------------------------------
DEBUG [main] ==>  Preparing: select * from tb_user where id=? 
DEBUG [main] ==> Parameters: 1(Integer)
DEBUG [main] <==      Total: 1
订单对应的用户:User [id=1, username=小明, loginname=xiaoming, password=xiaoming, phone=123456789123, address=北京]
---------------------------------------------------------------
DEBUG [main] ==>  Preparing: select id,name,price,remark from tb_article where id in(select article_id from tb_item where order_id=?) 
DEBUG [main] ==> Parameters: 1(Integer)
DEBUG [main] <==      Total: 3
订单中的商品列表:
    Article [id=1, name=商品1, price=123.12, remark=xxx的伟大著作]
    Article [id=2, name=商品2, price=12.3, remark=yyy的伟大著作]
    Article [id=3, name=商品3, price=34.22, remark=zzz的著作]

测试查询商品

ArticleMapper.java

/MyAManyToManyTest/src/mapper/ArticleMapper.java

package mapper;

import java.util.List;
import org.apache.ibatis.annotations.Many;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.mapping.FetchType;
import domain.Article;
import domain.Order;

public interface ArticleMapper {
    @Select("select * from tb_article where id=#{id}")
    @Results({
        @Result(column = "id",property = "id", id = true),
        @Result(column = "name",property = "name"),
        @Result(column = "price",property = "price"),
        @Result(column = "remark",property = "remark"),
        @Result(column = "id",property = "orders", 
            many = @Many(select = "selectOrderByArticleId", fetchType = FetchType.LAZY)
        ) 
    })
    Article selectArticleById(Integer id);

    @Select("select * from tb_order where id in(select order_id from tb_item where article_id=#{id})")
    @Results({
        @Result(column = "id",property = "id", id = true),
        @Result(column = "code",property = "code"),
        @Result(column = "total",property = "total") 
    })
    List<Order> selectOrderByArticleId(Integer id);
}

SelectArticleByIdTest.java

/MyAManyToManyTest/src/test/SelectArticleByIdTest.java

package test;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import domain.Article;
import domain.Order;
import fractory.SqlSessionFactoryTools;
import mapper.ArticleMapper;
public class SelectArticleByIdTest {
    public static void main(String[] args)
    {
        SqlSession sqlSession = null;
        try
        {
            // 加载mybatis-config.xml,获取SqlSession实例
            sqlSession = SqlSessionFactoryTools.getSqlSession();
            // 获取mapper接口代理对象
            ArticleMapper articleMapper = sqlSession.getMapper(ArticleMapper.class);
            Article article = articleMapper.selectArticleById(1);
            System.out.println("Article [id=" + article.getId() + ", name=" + article.getName()
                    + ", price=" + article.getPrice() + ", remark=" + article.getRemark() + "]");
            System.out.println("---------------------------------------------------");
            System.out.println("包含该商品的所有订单:");
            List<Order> orders = article.getOrders();
            orders.forEach(order -> System.out.println("    " + order));
            // 提交事务
            sqlSession.commit();
        } catch (Exception e)
        {
            // 出错回滚事务
            sqlSession.rollback();
            e.printStackTrace();
        } finally
        {
            // 关闭会话
            if (sqlSession != null)
                sqlSession.close();
        }
    }
}

运行结果

DEBUG [main] ==>  Preparing: select * from tb_article where id=? 
DEBUG [main] ==> Parameters: 1(Integer)
DEBUG [main] <==      Total: 1
Article [id=1, name=商品1, price=123.12, remark=xxx的伟大著作]
---------------------------------------------------
包含该商品的所有订单:
DEBUG [main] ==>  Preparing: select * from tb_order where id in(select order_id from tb_item where article_id=?) 
DEBUG [main] ==> Parameters: 1(Integer)
DEBUG [main] <==      Total: 2
    Order [id=1, code=abcseeeahoaugoeijgiej, total=223.33]
    Order [id=2, code=sfaofosfhodsfuefie, total=111.22]