MyBatis——关联映射——ManyToMany

通过商品、订单与用户的关系了解MyBatis中多对多映射关系

第一步：创建数据库表

CREATE TABLE tb_user3(
  id int(11) PRIMARY KEY,
  username VARCHAR(18),
  loginname VARCHAR(18),
  password VARCHAR(18),
  phone VARCHAR(18),
  address VARCHAR(18)
)

CREATE TABLE tb_article(
  id INT PRIMARY KEY,
  name VARCHAR(18),
  price DOUBLE,
  remark VARCHAR(18)
)

CREATE TABLE tb_order(
  id INT PRIMARY KEY,
  code VARCHAR(18),
  total DOUBLE,
  user_id INT,
  FOREIGN KEY (user_id) REFERENCES tb_user3(id)
)

CREATE TABLE tb_item(
  order_id INT,
  article_id INT,
  amount INT,
  PRIMARY KEY(order_id,article_id),
  FOREIGN KEY(order_id) REFERENCES tb_order(id),
  FOREIGN KEY(article_id) REFERENCES tb_article(id)
)

 第二步：将要与数据库表映射的对象类

/MyBatisRelationMapping/src/com/web/mybatis/domain/User.java

    private Integer id;
    private String username;
    private String loginname;
    private String password;
    private String phone;
    private String address;
    private List<Order> orders;//关联订单，一对多关系

/MyBatisRelationMapping/src/com/web/mybatis/domain/Order.java

    private Integer id;
    private String code;
    private Double total;
    private User user;
    private List<Article> articles;//关联商品，多对多

/MyBatisRelationMapping/src/com/web/mybatis/domain/Article.java

    private Integer id;
    private String name;
    private Double price;
    private String remark;
    private List<Order> orders;//关联订单，多对多

第三步：创建mapper文件

/MyBatisRelationMapping/src/com/web/mybatis/mapper/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="com.web.mybatis.mapper.UserMapper">
    <resultMap type="com.web.mybatis.domain.User" id="userResultMap">
        <id property="id" column="id"/>
        <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" />
        <!-- 一对多关联映射：collection -->
        <collection property="orders" javaType="ArrayList"
            column="id" ofType="com.web.mybatis.domain.User"
            select="com.web.mybatis.mapper.OrderMapper.selectOrderByUserId">
          <id property="id" column="id"/>
          <result property="code" column="code" /> 
          <result property="total" column="total" /> 
        </collection>
    </resultMap>
    <select id="selectUserById" parameterType="int" resultMap="userResultMap">
        SELECT * FROM tb_user3 WHERE id=#{id}
    </select>
</mapper>

/MyBatisRelationMapping/src/com/web/mybatis/mapper/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.web.mybatis.mapper.OrderMapper">
    <resultMap type="com.web.mybatis.domain.Order" id="orderResultMap">
        <id property="id" column="oid"/>
        <result property="code" column="code" />
        <result property="total" column="total" />
        <!-- 多对一关联映射：association -->
        <association property="user" javaType="com.web.mybatis.domain.User">
            <id property="id" column="id"/>
            <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" />
        </association>
        <!-- 多对多关联映射：collection -->
        <collection property="articles" javaType="ArrayList"
            column="id" ofType="com.web.mybatis.domain.Article"
            select="com.web.mybatis.mapper.ArticleMapper.selectArticleByOrderId">
            <id property="id" column="id"/>
            <result property="name" column="name" />
            <result property="price" column="price" />
            <result property="remark" column="remark" />
        </collection>
    </resultMap>
    <select id="selectOrderById" parameterType="int" resultMap="orderResultMap">
        SELECT u.*,o.id AS oid,code,total,user_id FROM tb_user3 u,tb_order o WHERE u.id=o.user_id AND o.id=#{id}
    </select>
    <select id="selectOrderByUserId" parameterType="int" resultType="com.web.mybatis.domain.Order">
        SELECT * FROM tb_order WHERE user_id=#{id}
    </select>
</mapper>

/MyBatisRelationMapping/src/com/web/mybatis/mapper/ArticleMapper.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.web.mybatis.mapper.ArticleMapper">
    <select id="selectArticleByOrderId" parameterType="int" resultType="com.web.mybatis.domain.Article">
        SELECT * FROM tb_article WHERE id IN (
          SELECT article_id FROM tb_item WHERE order_id=#{id}
        )
    </select>
</mapper>

第四步：创建于mapper对应的接口类

/MyBatisRelationMapping/src/com/web/mybatis/mapper/UserMapper.java

package com.web.mybatis.mapper;

import com.web.mybatis.domain.User;

public interface UserMapper {
    User selectUserById(int id);
}

/MyBatisRelationMapping/src/com/web/mybatis/mapper/OrderMapper.java

package com.web.mybatis.mapper;

import com.web.mybatis.domain.Order;

public interface OrderMapper {
    Order selectOrderById(int id);
}

第五步：写测试代码

package com.web.mybatis.test;

import org.apache.ibatis.session.SqlSession;

import com.web.mybatis.domain.Article;
import com.web.mybatis.domain.Order;
import com.web.mybatis.domain.User;
import com.web.mybatis.factory.FKSqlSessionFactory;
import com.web.mybatis.mapper.OrderMapper;
import com.web.mybatis.mapper.UserMapper;

public class ManyToManyTest {
    public static void testSelectUserById() {
        SqlSession session = FKSqlSessionFactory.getSqlSession();
        //获取代理对象
        UserMapper um = session.getMapper(UserMapper.class);
        User user = um.selectUserById(1);
        System.out.println("用户："+user.toString());
        for(Order order : user.getOrders()) {
            System.out.println("--订单："+order.toString());
        }
        session.commit();
        session.close();
    }
    public static void testSelectOrderById() {
        SqlSession session = FKSqlSessionFactory.getSqlSession();
        //获取代理对象
        OrderMapper om = session.getMapper(OrderMapper.class);
        Order order = om.selectOrderById(1);
        System.out.println("订单："+order.toString());
        System.out.println("--用户:"+order.getUser().toString());
        for(Article articel : order.getArticles()) {
            System.out.println("----商品："+articel.toString());
        }
        session.commit();
        session.close();
    }
    public static void main(String[] args) {
//        testSelectUserById();
        testSelectOrderById();
    }
}