Mybatis多对多

发表于 | 分类于 ，
字数统计: 1.2k 字 | 阅读时长 ≈ 7 分钟

0.前期准备

数据库：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
CREATE TABLE tb_article
(
  id     INT AUTO_INCREMENT
    PRIMARY KEY,
  name   VARCHAR(18) NULL,
  price  DOUBLE      NULL,
  remark VARCHAR(18) NULL,
  CONSTRAINT tb_article_id_uindex
  UNIQUE (id)
)
CREATE TABLE tb_consumer
(
  id        INT AUTO_INCREMENT
    PRIMARY KEY,
  username  VARCHAR(18) NULL,
  loginname VARCHAR(18) NULL,
  password  VARCHAR(18) NULL,
  phone     VARCHAR(18) NULL,
  address   VARCHAR(18) NULL,
  CONSTRAINT tb_consumer_id_uindex
  UNIQUE (id)
)
CREATE TABLE tb_order
(
  id      INT AUTO_INCREMENT
    PRIMARY KEY,
  code    VARCHAR(32) NULL,
  total   DOUBLE      NULL,
  user_id INT         NULL,
  CONSTRAINT tb_order_id_uindex
  UNIQUE (id),
  CONSTRAINT tb_order_tb_consumer_id_fk
  FOREIGN KEY (user_id) REFERENCES tb_consumer (id)
)
  ENGINE = InnoDB;

CREATE INDEX tb_order_tb_consumer_id_fk
  ON tb_order (user_id);

CREATE TABLE tb_item
(
  order_id   INT         NOT NULL,
  amount     INT         NULL,
  code       VARCHAR(18) NULL,
  article_id INT         NOT NULL,
  PRIMARY KEY (order_id, article_id)
)
  ENGINE = InnoDB;

其中，consumer和order是一对多关系，一个顾客有多个订单；order和article是多对多的关系，中间表是item，一个订单可以有多个商品，一个商品也可以在多个订单上，所以是多对多关系。
POJO类：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
//TbConsumer.java
public class TbConsumer {

    private long id;
    private String username;
    private String loginname;
    private String password;
    private String phone;
    private String address;
    private List<TbOrder> tbOrders;
//setter,getter


//TbOrder.java
public class TbOrder {

    private long id;
    private String code;
    private double total;
    private long userId;
    private TbConsumer tbConsumer;
    private List<TbArticle> articleList;
//setter,getter

//TbArticle.java
public class TbArticle {

    private long id;
    private String name;
    private double price;
    private String remark;
    private List<TbOrder> tbOrders;
//getter,setter

1.xml配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
//ConsumerMapper.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="org.fkit.mapper.ConsumerMapper">
    <resultMap id="consumerResultMap" type="org.fkit.domain.TbConsumer">
        <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 property="tbOrders" javaType="arrayList" column="id"
                    ofType="org.fkit.domain.TbOrder" fetchType="lazy"
                    select="org.fkit.mapper.OrderMapper.selectOrderByConsumerId">
            <id property="id" column="id"/>
            <result property="code" column="code"/>
            <result property="total" column="total"/>
        </collection>
    </resultMap>
    <select id="selectConsumerById" parameterType="int" resultMap="consumerResultMap">
        SELECT * from tb_consumer where id = #{id}
    </select>
</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="org.fkit.mapper.OrderMapper">
    <resultMap id="orderResultMap" type="org.fkit.domain.TbOrder">
        <id property="id" column="id"/>
        <result column="total" property="total"/>
        <result column="code" property="code"/>
        <association property="tbConsumer" javaType="org.fkit.domain.TbConsumer">
            <id property="id" column="id"/>
            <result column="address" property="address"/>
            <result column="loginname" property="loginname"/>
            <result column="password" property="password"/>
            <result column="username" property="username"/>
            <result column="phone" property="phone"/>
        </association>
        <collection property="articleList" javaType="ArrayList"
                    column="oid" ofType="org.fkit.domain.TbArticle"
                    select="org.fkit.mapper.ArticleMapper.selectArticleByOrderId"
                    fetchType="lazy">
            <id column="id" property="id"/>
            <result column="name" property="name"/>
            <result column="price" property="price"/>
            <result property="remark" column="remark"/>
        </collection>
    </resultMap>

    <select id="selectOrderById" resultMap="orderResultMap" parameterType="int">
        SELECT  u.* ,o.id as oid,code,total,user_id FROM tb_consumer u,
        tb_order o WHERE u.id = o.user_id and o.id = #{id}
    </select>

    <select id="selectOrderByConsumerId" parameterType="int" resultType="org.fkit.domain.TbOrder">
        select * from tb_order where user_id = #{id};
    </select>
</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="org.fkit.mapper.ArticleMapper">
    <select id="selectArticleByOrderId" parameterType="int" resultType="org.fkit.domain.TbArticle">
        SELECT * from tb_article where id in  (select article_id from tb_item where order_id = #{id})
    </select>
</mapper>

因为多表查询返回结果中tb_consumer中有id列，tb_order中也有id列，所以需要区分，例如使o.id as oid，resultMap中的column=”oid”就是指tb_order表中的id值。

相应Java类：

1
2
3
4
5
6
7
8
9
10
11
12
13
public interface ConsumerMapper {
    TbConsumer selectConsumerById(Integer id);
}

public interface OrderMapper {
    TbOrder selectOrderById(Integer id);

    TbOrder selectOrderByUserId(Integer id);
}

public interface ArticleMapper {
    TbArticle selectArticleByOrderId(Integer id);
}

2.测试

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
public class ManyToManyTest {
    public static void main(String args[]) {
        SqlSession sqlSession = FKSqlSessionFactory.getSqlSession();
        ManyToManyTest manyToManyTest = new ManyToManyTest();
        manyToManyTest.testSelectUserById(sqlSession);
        manyToManyTest.testSelectOrderById(sqlSession);
        sqlSession.commit();

        sqlSession.close();
    }

    public void testSelectUserById(SqlSession sqlSession) {
        ConsumerMapper consumerMapper = sqlSession.getMapper(ConsumerMapper.class);
        TbConsumer tbConsumer = consumerMapper.selectConsumerById(1);
        System.out.println(tbConsumer);
        List<TbOrder> tbOrders = tbConsumer.getTbOrders();
        for (TbOrder tbOrder : tbOrders) {
            System.out.println(tbOrder);
        }
    }

    public void testSelectOrderById(SqlSession sqlSession) {
        OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class);
        TbOrder tbOrder = orderMapper.selectOrderById(2);
        System.out.println(tbOrder);
        TbConsumer tbConsumer = tbOrder.getTbConsumer();
        System.out.println(tbConsumer);
        List<TbArticle> tbArticles = tbOrder.getArticleList();
        for (TbArticle tbArticle : tbArticles) {
            System.out.println("tbArticle:");
            System.out.println(tbArticle);
        }
    }
}

//output:
DEBUG [main] - ==>  Preparing: SELECT * from tb_consumer where id = ? 
DEBUG [main] - ==> Parameters: 1(Integer)
DEBUG [main] - <==      Total: 1
DEBUG [main] - ==>  Preparing: select * from tb_order where user_id = ?; 
DEBUG [main] - ==> Parameters: 1(Integer)
DEBUG [main] - <==      Total: 2
TbConsumer{id=1, username='peter', loginname='blue man', password='123', phone='123456', address='外星球', tbOrders=[TbOrder{id=2, code='23624', total=1.0, userId=1, tbConsumer=null}, TbOrder{id=3, code='12351', total=2.0, userId=1, tbConsumer=null}]}
TbOrder{id=2, code='23624', total=1.0, userId=1, tbConsumer=null}
TbOrder{id=3, code='12351', total=2.0, userId=1, tbConsumer=null}
DEBUG [main] - ==>  Preparing: SELECT u.* ,o.id as oid,code,total,user_id FROM tb_consumer u, tb_order o WHERE u.id = o.user_id and o.id = ? 
DEBUG [main] - ==> Parameters: 2(Integer)
DEBUG [main] - <==      Total: 1
DEBUG [main] - ==>  Preparing: SELECT * from tb_article where id in (select article_id from tb_item where order_id = ?) 
DEBUG [main] - ==> Parameters: 2(Integer)
DEBUG [main] - <==      Total: 2
TbOrder{id=1, code='23624', total=1.0, userId=0, tbConsumer=TbConsumer{id=1, username='peter', loginname='blue man', password='123', phone='123456', address='外星球', tbOrders=null}}
TbConsumer{id=1, username='peter', loginname='blue man', password='123', phone='123456', address='外星球', tbOrders=null}
tbArticle:
TbArticle{id=1, name='洗发水', price=23.3, remark='bilibili', tbOrders=null}
tbArticle:
TbArticle{id=2, name='java书', price=111.0, remark='blingbling', tbOrders=null}
-------------本文结束 感谢您的阅读-------------