11.2 注解的使用多对多关联关系
示例多对多关联关系
- 项目结构
- 数据库表
创建用户表
插入用户
创建商品表
插入商品
创建订单表
插入订单
创建订单-商品关系表
插入商品-订单
保存为db.properties文件,然后在mybatis-config.xml中通过下面标签引入:
">
下面的标签放在mybatis-config.xml文件的environments标签的environment子标签的子标签dataSource标签中
">
```
- [log4j.xml](/blog/6d72c603/#log4j-xml)
- [mybatis-config.xml](/blog/6d72c603/#mybatis-config-xml)
```

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

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

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

示例多对多关联关系

项目结构

E:\workspace_web2\MyAManyToManyTest
├─src
│ ├─db.properties
│ ├─domain
│ │ ├─Article.java
│ │ ├─Order.java
│ │ └─User.java
│ ├─fractory
│ │ └─SqlSessionFratoryTools.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

数据库表

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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
# 插入用户
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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
# 插入商品
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
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
# 插入订单
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
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
# 插入商品-订单
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');

持久化对象

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()
    {}
    /**
     * @param username
     * @param loginname
     * @param password
     * @param phone
     * @param address
     */
    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

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()
    {}
    /**
     * @param code
     * @param total
     * @param user
     */
    public Order(String code, double total, User user)
    {
        super();
        this.code = code;
        this.total = total;
        this.user = user;
    }
    // 此处省略getter和setter方法,请自己补上
    @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();
    }
    /**
     * @param name
     * @param price
     * @param remark
     */
    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
                + "]";
    }
}

工具类

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 SqlSessionFratoryTools {
    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

<?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

<?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接口

package mapper;
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.User;
public interface UserMapper {
    @Select("select * from tb_user where id=#{id}")
    @Results(
        {@Result(
            property = "id",
            column = "id",
            id = true
        ), @Result(
            property = "username",
            column = "username"
        ), @Result(
            property = "loginname",
            column = "loginname"
        ), @Result(
            property = "password",
            column = "password"
        ), @Result(
            property = "phone",
            column = "phone"
        ), @Result(
            property = "address",
            column = "address"
        ), @Result(
            property = "orders",
            column = "id",
            many = @Many(
                select = "mapper.OrderMapper.selectOrdersByUserId",
                fetchType = FetchType.LAZY
            )
        )}
    )
    User selectUserById(Integer id);
}

测试类

package test;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import domain.Order;
import domain.User;
import fractory.SqlSessionFratoryTools;
import mapper.UserMapper;
public class SelectUserByIdTest {
    public static void main(String[] args)
    {
        SqlSession sqlSession = null;
        try
        {
            // 加载mybatis-config.xml,获取SqlSession实例
            sqlSession = SqlSessionFratoryTools.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

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(
            property = "id",
            column = "id",
            id = true
        ), @Result(
            property = "code",
            column = "code"
        ), @Result(
            property = "total",
            column = "total"
        ), @Result(
            property = "user",
            column = "user_id",
            // 根据订单中的user_id外键查询用户信息.
            one = @One(
                select = "selectUserByOrderId",
                fetchType = FetchType.LAZY
            )
        ), @Result(
            property = "articles",
            column = "id",
            // 根据订单的编号查询对应的商品信息
            many = @Many(
                select = "selectArticlesByOrderId",
                fetchType = FetchType.LAZY
            )
        )}
    )
    Order selectOrderById(Integer id);
    /**
     * 根据用户编号,查询用户信息.
     * @param  id
     * @return
     */
    @Select("select * from tb_user where id=#{id}")
    User selectUserByOrderId(Integer id);
    /**
     * 根据订单编号查询文章信息.
     * @param  id
     * @return
     */
    @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);
}

测试类

package test;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import domain.Article;
import domain.Order;
import domain.User;
import fractory.SqlSessionFratoryTools;
import mapper.OrderMapper;
public class SelectOrderByIdTest {
    public static void main(String[] args)
    {
        SqlSession sqlSession = null;
        try
        {
            // 加载mybatis-config.xml,获取SqlSession实例
            sqlSession = SqlSessionFratoryTools.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的著作]

测试查询商品

mapper接口

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(
            property = "id",
            column = "id",
            id = true
        ), @Result(
            property = "name",
            column = "name"
        ), @Result(
            property = "price",
            column = "price"
        ), @Result(
            property = "remark",
            column = "remark"
        ), @Result(
            property = "orders",
            column = "id",
            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(
            property = "id",
            column = "id",
            id = true
        ), @Result(
            property = "code",
            column = "code"
        ), @Result(
            property = "total",
            column = "total"
        )}
    )
    /**
     * 根据商品的编号来查询包含该商品的所有订单.
     * @param  id
     * @return
     */
    List<Order> selectOrderByArticleId(Integer id);
}

测试类

package test;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import domain.Article;
import domain.Order;
import fractory.SqlSessionFratoryTools;
import mapper.ArticleMapper;
public class SelectArticleByIdTest {
    public static void main(String[] args)
    {
        SqlSession sqlSession = null;
        try
        {
            // 加载mybatis-config.xml,获取SqlSession实例
            sqlSession = SqlSessionFratoryTools.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]

原文链接: 11.2 注解的使用示例4 多对多关联关系

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

示例 多对多关联关系

项目结构

数据库表

持久化对象

User.java

Order.java

Article.java

工具类

mybatis相关配置文件

db.propertis

log4j.xml

mybatis-config.xml

测试查询用户

UserMapper接口

测试类

运行效果

测试查询订单

OrderMapper.java

测试类

运行结果

测试查询商品

mapper接口

测试类

运行结果

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

示例多对多关联关系