Mybatis一对多与多对多

偏执的太偏执、 2022-06-08 00:49 212阅读 0赞

这次接着上次来，今天分享一对多以及多对多。

可能有的朋友看不下去，所以我先把一个巨大的坑告诉你们，免得走老路：

**数据库表的字段名千万不要相同，属性名最好也不要相同，比如Book和Author的主键不要都写ID，分别写为BookID,AuthorID比较好，反正不能相同，不然会出现查询不正确。**

数据库设计如下，Author和Book是一对多关系，数据库设计如下所示：

Book表:

![71923553.jpg][]

Author表:

![94855288.jpg][]

再写实体类：

public class Author {
        private int ID;
        private String AuthorName;
        private List<Book> books;
        //省略了getter和setter方法
    }
    
    ==============================
    
    public class Book {
        private int BookID;
        private String BookName;
        private Author author;
        //省略了getter和setter方法
    }

配置xml文件：

Author.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="Author">
        <resultMap type="com.entity.Author" id="getAuthor">
            <id column="ID" property="ID"/>
            <result column="AuthorName" property="AuthorName"/>
            
            <collection property="books" ofType="com.entity.Book">
                <id column="BookID" property="BookID" />
                <result column="BookName" property="BookName"/>
            </collection>
        </resultMap>
    
        <select id="queryAuthor" resultMap="getAuthor" parameterType="int">
            select a.*,b.* from Author a left join Book b
            on a.ID=b.authorID 
            where a.ID=#{ID}
        </select>
    </mapper>

Book.xml:

**记得一定要把这两个文档引进configuration.xml中**

Test代码：

public static void main(String[] args) {
            SqlSession session = null;
            try {
                InputStream inputStream = Resources.getResourceAsStream("configure/Configuration.xml");
                session = new SqlSessionFactoryBuilder().build(inputStream).openSession();
                Author author=session.selectOne("queryAuthor", 1);
                List<Book> books = author.getBooks();
                for (Book book : books) {
                    System.out.println(book.getID()+" "+book.getName());
                }
    
            } catch (IOException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            } finally {
                if (session!=null) {
                    session.close();
                }
            }
        }

以上就完成了一对多。

--------------------

接下来说说多对多，多对多需要一个中间表，然后构成两个一对多，构造中间表后就和一对多一致了。

先看数据库设计,Order和Product是多对多关系，所以搞了个中间表OrderDetail，就成了两个一对多：

Product:

![92480430.jpg][]

OrderDetail

![52214081.jpg][]

Order:

![2106056.jpg][]

先上实体类：

public class Order {
        private int OrderID;
        private String OrderName;
        private List<OrderDetail> orderDetails;
        //省略了getter和setter方法
    }
    ==============================================
      public class OrderDetail {
        private int OID;
        private int PID;
        private Order order;
        private Product product;
        //省略了getter和setter方法
      }
    ==============================================
      public class Product {
        private int ProductID;
        private String ProductName;
        private List<OrderDetail> orderDetails;
        //省略了getter和setter方法
      }

配置xml文件:

Order.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="Order">
    
        <resultMap type="com.entity.Order" id="Order">
            <id column="OrderID" jdbcType="INTEGER" property="OrderID"/>
            <result column="OrderName" jdbcType="NVARCHAR" property="OrderName"/>
            <collection property="orderDetails" ofType="com.entity.OrderDetail" resultMap="OrderDetail.OrderDetail"/>
        </resultMap>
    
        
        <select id="queryProdcutID" resultMap="Order" parameterType="int">
            select a.*,b.*,p.*
            from [Order] a 
            left join OrderDetail b
            on a.OrderID = b.OID
            left join Product p
            on B.PID = p.ProductID
            where a.OrderID=#{OrderID}
        </select>
    
    </mapper>

OrderDetail.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="OrderDetail">
    
        <resultMap type="com.entity.OrderDetail" id="OrderDetail">
            <result column="OID" jdbcType="INTEGER" property="OID"/>
            <result column="PID" jdbcType="INTEGER" property="PID"/>
            <association property="product" javaType="com.entity.Product" >
                <id column="ProductID" property="ProductID"/>
                <result column="ProductName" property="ProductName"/>
            </association>
        </resultMap>
    
    </mapper>

Product.xml:

以上就完成了所以的配置。

测试类：

public static void main(String[] args) {
            SqlSession sqlSession = null;
            try {
                InputStream inputStream = Resources.getResourceAsStream("configure/Configuration.xml");
                sqlSession = new SqlSessionFactoryBuilder().build(inputStream).openSession();
                Order order2 = (Order)sqlSession.selectOne("queryProdcutID",1);
                System.out.println(order2.getOrderName());
                List<OrderDetail> detail=order2.getOrderDetails();
                for (OrderDetail orderDetail : detail) {
                    System.out.println(orderDetail.getProduct().getProductName());
                }
    
            } catch (IOException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            } finally {
                if (sqlSession != null) {
                    sqlSession = null;
                }
            }
    
        }

以上就完成了多对多配置。

[71923553.jpg]: http://or0um8wrx.bkt.clouddn.com/17-9-9/71923553.jpg
[94855288.jpg]: http://or0um8wrx.bkt.clouddn.com/17-9-7/94855288.jpg
[92480430.jpg]: http://or0um8wrx.bkt.clouddn.com/17-9-9/92480430.jpg
[52214081.jpg]: http://or0um8wrx.bkt.clouddn.com/17-9-9/52214081.jpg
[2106056.jpg]: http://or0um8wrx.bkt.clouddn.com/17-9-9/2106056.jpg