MyBatis的关系映射——多对多

MyBatis的关系映射——多对多

多对多的关系

在A类中定义B类类型的集合，在B类中定义A类类型的集合

这次的博客直接在上一篇MyBatis的关系映射——一对多的基础上写，直接添加相关文件就可以

1、创建相关数据库

create table tb_product(
id int(32) primary key auto_increment,
name varchar(32),
price double
);
insert into tb_product values ('1','Java fundment','44.5');
insert into tb_product values ('2','Java Web fundment','38.5');
insert into tb_product values ('3','SSM prictence','50');
create table tb_ordersitem(
id int(32) primary key auto_increment,
orders_id int(32),
product_id int(32),
foreign key(orders_id) references tb_orders(id),
foreign key(product_id) references tb_product(id)
);
insert into tb_ordersitem values ('1','1','1');
insert into tb_ordersitem values ('2','1','3');
insert into tb_ordersitem values ('3','3','3');

2、在com.po包中创建类Product并重写Orders类

package com.po;

import java.util.List;

public class Product {
    private Integer id;
    private String username;
    private Double price;
    private List<Orders> orders;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public Double getPrice() {
        return price;
    }

    public void setPrice(Double price) {
        this.price = price;
    }

    public List<Orders> getOrders() {
        return orders;
    }

    public void setOrders(List<Orders> orders) {
        this.orders = orders;
    }

    @Override
    public String toString() {
        return "Product{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", price=" + price +
                ", orders=" + orders +
                '}';
    }
}

重写Orders类的主要目的是为了方便查看数据结果

package com.po;

import java.util.List;

public class Orders {
    private Integer id;    
    private String number;
    private List<Product> productList;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getNumber() {
        return number;
    }

    public void setNumber(String number) {
        this.number = number;
    }

    public List<Product> getProductList() {
        return productList;
    }

    public void setProductList(List<Product> productList) {
        this.productList = productList;
    }

    @Override
    public String toString() {
        return "Orders{" +
                "id=" + id +
                ", number='" + number + '\'' +
                ", productList=" + productList +
                '}';
    }
}

3、在com.mapper中创建实体映射文件OrdersMapper.xml和商品实体映射文件ProductMapper.xml

OrdersMapper.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.mapper.OrdersMapper">
	<!-- 多对多嵌套查询：通过执行另外一条SQL映射语句来返回预期的特殊类型 -->
	<select id="findOrdersWithProduct" parameterType="Integer"
              resultMap="OrdersWithProductResult">
		select * from tb_orders WHERE id=#{id}	
	</select>
	<resultMap type="Orders" id="OrdersWithProductResult">
		<id property="id" column="id" />
		<result property="number" column="number" />
		<collection property="productList" column="id" ofType="Product" 
		     select="com.mapper.ProductMapper.findProductById">
		</collection>
	</resultMap>
	
	<!-- 多对多嵌套结果查询：查询某订单及其关联的商品详情 -->
	<select id="findOrdersWithPorduct2" parameterType="Integer" 
	         resultMap="OrdersWithPorductResult2">
	    select o.*,p.id as pid,p.name,p.price
	    from tb_orders o,tb_product p,tb_ordersitem  oi
	    WHERE oi.orders_id=o.id 
	    and oi.product_id=p.id 
	    and o.id=#{id}
	</select>
	<!-- 自定义手动映射类型 -->
	<resultMap type="Orders" id="OrdersWithPorductResult2">
	    <id property="id" column="id" />
	    <result property="number" column="number" />
	    <!-- 多对多关联映射：collection -->
	    <collection property="productList" ofType="Product">
	        <id property="id" column="pid" />
	        <result property="name" column="name" />
	        <result property="price" column="price" />
	    </collection>
	</resultMap>
</mapper>

ProductMapper.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.mapper.ProductMapper">
	<select id="findProductById" parameterType="Integer" 
                                       resultType="Product">
		SELECT * from tb_product where id IN(
		   SELECT product_id FROM tb_ordersitem  WHERE orders_id = #{id}
		)
	</select>
</mapper>

4、将新创建的映射文件的文件路径添加到配置文件Mybatis-config.xml中

<mapper resource="com/mapper/OrdersMapper.xml" />
<mapper resource="com/mapper/ProductMapper.xml" />

5、在测试类中编写多对多关联查询的测试方法findOrdersTest()

    @Test
    public void findOrdersTest(){
        SqlSession session=MybatisUtils.getSession();
        Orders orders=session.selectOne("com.mapper."+"OrdersMapper.findOrdersWithProduct",1);
        System.out.println(orders);
        session.close();
    }

运行结果如下

遇到的问题

在这次过程中遇到如下问题

org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: java.lang.IllegalArgumentException: Mapped Statements collection does not contain value for com.mapper.OrdersMapper.findOrdersWithProduct
### Cause: java.lang.IllegalArgumentException: Mapped Statements collection does not contain value for com.mapper.OrdersMapper.findOrdersWithProduct

	at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:150)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:141)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:77)
	at com.test.MybatisAssociatedTest.findOrdersTest(MybatisAssociatedTest.java:36)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
	at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
	at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
	at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
	at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
	at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
	at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
	at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
	at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
	at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
	at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
	at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
	at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68)
	at com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:47)
	at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:242)
	at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70)
Caused by: java.lang.IllegalArgumentException: Mapped Statements collection does not contain value for com.mapper.OrdersMapper.findOrdersWithProduct
	at org.apache.ibatis.session.Configuration$StrictMap.get(Configuration.java:933)
	at org.apache.ibatis.session.Configuration.getMappedStatement(Configuration.java:726)
	at org.apache.ibatis.session.Configuration.getMappedStatement(Configuration.java:719)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:147)
	... 25 more

看到报错提示一直以为是数据库访问出现问题，删掉数据库重写一遍发现问题仍然存在，然后一直检查com.mapper.OrdersMapper.findOrdersWithProduct文档中的代码段发现并没有什么问题，谷歌了很多方案也并没有解决掉，当时放弃了，在看了几集《星际迷航：发现号》之后猛然发现这个地方出了问题
应该是 id=“findOrdersWithProductResult” 结果成了 id=“findrOdersWithProduct” 改掉之后运行成功

写在最后

需要找一个开源项目好好练习，可以做一个二手商城交易系统，然后以此为蓝本再做一个推荐系统的小项目出来。