（六）mybatis之多对一关系（简单）

一、需求分析

• 需求：   　查询所有订单信息及订单下的订单明细信息
• 分析：      一条订单只能由一个消费者下单，但是一条订单有多条订单明细。

 

二、创建数据库表和实体对象

Customer.java

package com.shyroke.entity;

public class Customer {
    private int id;
    private String name;
    private String sex;
    private String birthday;

//隐藏setget方法和toString方法
}

OrderDesc.java

package com.shyroke.entity;

public class OrderDesc {
    private int id;
    private String num;
    private String itemsId;
    private String ordersId;

   

//隐藏setget方法和toString方法

}

Order.java

package com.shyroke.entity;

import java.util.List;

public class Order {
    private int orderId;
    private String Number;
    private String createTime;
    private String note;

    // 关联消费者信息，是一对一关系
    private Customer customer;

    // 关联订单明细信息，是一对多关系
    private List<OrderDesc> orderDescList;

//隐藏setget方法和toString方法

}

 

三、创建OrderMapper.java和OrderMapper.xml文件

OrderMapper.java

package com.shyroke.mapper;

import java.util.List;

import com.shyroke.entity.Order;

public interface OrderMapper {
    public List<Order> getOrderAndDescList(); 
}

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.shyroke.mapper.OrderMapper">
    <resultMap type="com.shyroke.entity.Order" id="orderMap">
        <id column="order_id" property="orderId" />
        <result column="order_number" property="Number" />
        <result column="order_createTime" property="createTime" />
        <result column="order_note" property="note" />

<!-- 关联消费者信息，一对一关系用association 标签 -->
        <association property="customer" javaType="com.shyroke.entity.Customer">
            <id column="user_id" property="id" />
            <result column="user_name" property="name" />
            <result column="user_sex" property="sex" />
            <result column="user_birthday" property="birthday" />
        </association>

        <!-- property: 要将关联信息映射到orders的哪个属性中 ofType: 集合中的pojo属性 -->
        <collection property="orderDescList" ofType="com.shyroke.entity.OrderDesc">
            <id column="orders_desc_id" property="id" />
            <result column="orders_desc_num" property="num" />
            <result column="orders_desc_items_id" property="itemsId" />
            <result column="orders_desc_orders_id" property="ordersId" />
        </collection>

    </resultMap>

<select id="getOrderAndDescList" resultMap="orderMap">
    select
        orders.*,ordersDesc.*,customer.*
    from
        orders,
        ordersDesc,
        customer
    where
       ordersDesc.orders_desc_orders_id=orders.order_id and customer.user_id=orders.user_id
    
</select>

</mapper>

• 一对一关系用association 标签，一对多关系用collection 标签

 

四、测试

package com.shyrolk.firstMybatis;

import java.awt.image.ImageProducer;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import javax.swing.plaf.synth.SynthSeparatorUI;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import com.shyroke.entity.Order;
import com.shyroke.mapper.OrderMapper;



/**
 * Hello world!
 *
 */
public class App 
{
    public static void main( String[] args ) throws IOException
    {
        String resource="resource/mybatis-config.xml";
        InputStream inputStream=Resources.getResourceAsStream(resource);
        SqlSessionFactory sessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
    
    SqlSession session=sessionFactory.openSession();
    
    OrderMapper orderMapper=session.getMapper(OrderMapper.class);
   List<Order> orderList= orderMapper.getOrderAndDescList();
    
   System.out.println(orderList);
   
   
    }
}

结果：