记录MyBatis多表操作

MyBatis的一对一，一对多，多对多的操作过程。
1.数据库表结构
t_user

CREATE TABLE `t_user` (
  `id` int(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  `address` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=gbk

t_items

CREATE TABLE `t_items` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `price` varchar(10) DEFAULT NULL,
  `detail` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=gbk

t_orders

CREATE TABLE `t_orders` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `userid` int(10) NOT NULL,
  `des` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `userid` (`userid`),
  CONSTRAINT `t_orders_ibfk_1` FOREIGN KEY (`userid`) REFERENCES `t_user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=gbk

t_ordersdetail

CREATE TABLE `t_ordersdetail` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `ordersid` int(10) NOT NULL,
  `itemsid` int(10) NOT NULL,
  `itemsnum` int(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ordersid` (`ordersid`),
  KEY `itemsid` (`itemsid`),
  CONSTRAINT `t_ordersdetail_ibfk_1` FOREIGN KEY (`ordersid`) REFERENCES `t_user` (`id`),
  CONSTRAINT `t_ordersdetail_ibfk_2` FOREIGN KEY (`itemsid`) REFERENCES `t_items` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=gbk

2.一对一查询需求：查询一个订单对应的用户

image.png

在OrdersMapperCustom.xml中的配置：

<!-- 一对一resultMap的定义 -->
    <resultMap type="entiy.Orders" id="onetooneresultmap">
        <!-- 查询结果中orders信息的映射 -->
        <id column="id" property="id"/>
        <result column="userid" property="userid"/>
        <result column="des" property="des"/>
        
        <!-- 查询结果中属于映射的部分 -->
        <!-- property指的orders中的成员user，javaType对应其User类 -->
        <association property="user" javaType="entiy.User">
            <id column="userid" property="id"/><!-- 关联查询用户的id映射到user类中的id -->
            <result column="name" property="name"/>
            <result column="address" property="address"/>
        </association>
    </resultMap>
    
    <!-- 一对一订单查询用户 -->
    <select id="findOrderToUser" resultMap="onetooneresultmap">
        SELECT t_orders.* ,t_user.`name`,t_user.`address` 
        FROM t_orders,t_user 
        WHERE t_orders.`userid` = t_user.`id`
    </select>

在OrdersMapperCustom.java中的配置

//一对一查询
    public List<Orders> findOrderToUser();

在测试类中

//一对一查询测试
    @Test
    public void testOnetoOne() throws IOException {
        String resource = "SqlMapConfig.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        //创建SqlsessionFactory
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        
        SqlSession sqlSession = sessionFactory.openSession();
        
        OrdersMapperCustom ordersMapperCustom = sqlSession.getMapper(OrdersMapperCustom.class);
        List<Orders> orders = ordersMapperCustom.findOrderToUser();
        System.out.println(orders);
    }

3.一对多的需求：查询一个订单对于多个订单详情

image.png

在Orders类中增加orderdetail的集合

image.png

在OrdersMapperCustom.xml中的配置：

<!-- 一对多resultMapper定义 -->
    <resultMap type="entiy.Orders" id="onetomanymap">
        <!-- 查询结果中orders的映射 -->
        <id column="id" property="id"/>
        <result column="userid" property="userid"/>
        <result column="des" property="des"/>
        
        <!-- 查询结果ordersdetail的映射 -->
        <!-- 一对多中使用collection和oftype -->
        <collection property="ordersDetaits" ofType="entiy.OrdersDetail">
            <id column="detailsid" property="id"/>
            <result column="itemsid" property="itemsid"/>
            <result column="itemsnum" property="itemsnum"/>
        </collection>
        
    </resultMap>
    
    <!-- 一对多订单查询订单详情 -->
    <select id="findOdersAndOrdersDetail" resultMap="onetomanymap">
        SELECT a.*,b.id detailsid ,b.itemsid,b.itemsnum FROM t_orders a ,t_ordersdetail b WHERE a.`id` = b.`ordersid`
    </select>

在OrdersMapperCustom.java中的配置:

//一对多查询
    public List<Orders> findOdersAndOrdersDetail();

测试类：

//一对多查询测试
    @Test
    public void testOnetoMany() throws IOException {
        String resource = "SqlMapConfig.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        //创建SqlsessionFactory
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        
        SqlSession sqlSession = sessionFactory.openSession();
        
        OrdersMapperCustom ordersMapperCustom = sqlSession.getMapper(OrdersMapperCustom.class);
        List<Orders> orders = ordersMapperCustom.findOdersAndOrdersDetail();
        System.out.println(orders);
        for(Orders order : orders) {
            System.out.println(order.getId());
            for(OrdersDetail detail: order.getOrdersDetaits()) {
                System.out.println(detail.getOrdersid());
            }
        }
    }

4.多对多需求：查询用户且与其对应购买的商品信息

image.png

在User类中添加商品信息

image.png

在OrdersMapperCustom.xml中的配置：

<!-- 多对多查询用户购买的商品信息 -->
    <resultMap type="entiy.User" id="manytomanymap">
        <!-- 查询出来的用户信息 -->
        <id column="userid" property="id"/>
        <result column="username" property="name"/>
        <result column="useraddress" property="address"/>
        <!-- 一个用户对于多个订单 -->
        <collection property="orders" ofType="entiy.Orders">
            <!-- 查询出来的订单信息 -->
            <id column="id" property="id"/>
            <result column="userid" property="userid"/>
            <result column="des" property="des"/>
            
            <!-- 一个订单对于多个订单详情 -->
            <collection property="ordersDetaits" ofType="entiy.OrdersDetail">
                <id column="detailid" property="id"/>
                <result column="ordersid" property="ordersid"/>
                <result column="itemsnum" property="itemsnum"/>
                
                <!-- 一个订单详情对于一个商品信息 -->
                <association property="items" javaType="entiy.Items">
                    <id column="itemsid" property="id"/>
                    <result column="name" property="name"/>
                    <result column="detail" property="detail"/>
                </association>              
            </collection>           
        </collection>
    </resultMap>
    
    <!-- 多对多查询 -->
    <select id="findUsertoitems" resultMap="manytomanymap">
        SELECT a.*,b.`name` username,b.`address` useraddress ,c.`id` detailid ,c.`ordersid`,c.`itemsnum`,d.`id` itemsid,d.`name`,d.`detail`
        FROM t_orders a,t_user b,t_ordersdetail c,items d
        WHERE a.`userid` = b.`id` AND c.`ordersid` = a.`id` AND d.`id` = c.`itemsid`
    </select>

在OrdersMapperCustom.java中的配置:

//多对多查询
    public List<User> findUsertoitems();

测试类：

//多对多查询测试
    @Test
    public void testManytoMany() throws IOException {
        String resource = "SqlMapConfig.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        //创建SqlsessionFactory
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        
        SqlSession sqlSession = sessionFactory.openSession();
        
        OrdersMapperCustom ordersMapperCustom = sqlSession.getMapper(OrdersMapperCustom.class);
        List<User> users = ordersMapperCustom.findUsertoitems();
        System.out.println(users);
        
    }

注意：一对一时使用的是<association property="items" javaType="entiy.Items">
对多时使用的是<collection property="ordersDetaits" ofType="entiy.OrdersDetail">

5.延迟加载的xml配置：

    <!-- 延迟加载 -->
    <!-- 延迟加载使用的resultmap -->
    <resultMap type="entiy.Orders" id="lazymap">
        <id column="id" property="id"/>
        <result column="userid" property="userid"/>
        <result column="des" property="des"/>
        
        <!-- 实现对用户信息的延迟加载 -->
        <association property="user" javaType="entiy.User" column="userid" select="Mapper.UserMapper.findUserById">
        
        </association>
        
    </resultMap>
    
    <!-- 延迟加载的map -->
    <select id="findOrderslazy" resultMap="lazymap">
        select * from t_orders
    </select>

初始化时只执行select*from t_orders，当只有需要用到user时，会调用select="Mapper.UserMapper.findUserById"中的select * from t_user where id ="#{id}"这条语句。