MyBatis高级映射（一对多查询）

1、需求

查询所有订单及订单下的明细信息，订单与订单明细为一对多关系

2、数据库结构

user用户表

orders订单表

orders订单表外键

orderdetail订单详情表

orderdetail订单详情表外键

3、SQL语句

4、开始编码

4.1、定义POJO

User

package org.mybatis.demo.po;

import java.util.Date;

public class User {
	
	//属性名和数据库表的字段对应
	private int id;
	private String username;// 用户姓名
	private String sex;// 性别
	private Date birthday;// 生日
	private String address;// 地址
	
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getUsername() {
		return username;
	}
	public void setUsername(String username) {
		this.username = username;
	}
	public String getSex() {
		return sex;
	}
	public void setSex(String sex) {
		this.sex = sex;
	}
	public Date getBirthday() {
		return birthday;
	}
	public void setBirthday(Date birthday) {
		this.birthday = birthday;
	}
	public String getAddress() {
		return address;
	}
	public void setAddress(String address) {
		this.address = address;
	}
	@Override
	public String toString() {
		return "User [id=" + id + ", username=" + username + ", sex=" + sex
				+ ", birthday=" + birthday + ", address=" + address + "]";
	}

}

Orders

package org.mybatis.demo.po;

import java.util.Date;
import java.util.List;

public class Orders {
	
    private Integer id;
    private Integer userId;
    private String number;
    private Date createtime;
    private String note;
    //用户信息
    private User user;
    //订单明细
    private List<Orderdetail> orderdetails;

    public Integer getId() {
        return id;
    }

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

    public Integer getUserId() {
        return userId;
    }

    public void setUserId(Integer userId) {
        this.userId = userId;
    }

    public String getNumber() {
        return number;
    }

    public void setNumber(String number) {
        this.number = number == null ? null : number.trim();
    }

    public Date getCreatetime() {
        return createtime;
    }

    public void setCreatetime(Date createtime) {
        this.createtime = createtime;
    }

    public String getNote() {
        return note;
    }

    public void setNote(String note) {
        this.note = note == null ? null : note.trim();
    }

	public User getUser() {
		return user;
	}

	public void setUser(User user) {
		this.user = user;
	}

	public List<Orderdetail> getOrderdetails() {
		return orderdetails;
	}

	public void setOrderdetails(List<Orderdetail> orderdetails) {
		this.orderdetails = orderdetails;
	}

	
    
    
}

OrdersDetail

package org.mybatis.demo.po;

public class Orderdetail {
    private Integer id;

    private Integer ordersId;

    private Integer itemsId;

    private Integer itemsNum;
    
    public Integer getId() {
        return id;
    }

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

    public Integer getOrdersId() {
        return ordersId;
    }

    public void setOrdersId(Integer ordersId) {
        this.ordersId = ordersId;
    }

    public Integer getItemsId() {
        return itemsId;
    }

    public void setItemsId(Integer itemsId) {
        this.itemsId = itemsId;
    }

    public Integer getItemsNum() {
        return itemsNum;
    }

    public void setItemsNum(Integer itemsNum) {
        this.itemsNum = itemsNum;
    }

	@Override
	public String toString() {
		return "Orderdetail [id=" + id + ", ordersId=" + ordersId
				+ ", itemsId=" + itemsId + ", itemsNum=" + itemsNum + "]";
	}
    
}

4.2、Mapper.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.mybatis.demo.mapper.OrdersMapper">
	
	<!-- 查询所有订单信息及订单下的订单明细信息，使用resultmap -->
	<select id="findOrdersAndOrderDetailResultMap"
	 resultMap="OrdersAndOrderDetailResultMap">
		SELECT 
		  orders.*,
		  USER.username,
		  USER.sex,
		  USER.address,
		  orderdetail.id orderdetail_id,
		  orderdetail.items_id,
		  orderdetail.items_num,
		  orderdetail.orders_id
		FROM
		  orders,
		  USER,
		  orderdetail
		WHERE orders.user_id = user.id AND orderdetail.orders_id=orders.id
	</select>
	
	<!-- 订单及订单明细的resultMap
	使用extends继承，不用在中配置订单信息和用户信息的映射
	 -->
	<resultMap type="org.mybatis.demo.po.Orders" id="OrdersAndOrderDetailResultMap"
		extends="OrdersUserResultMap">
		<!-- 订单信息 -->
		<!-- 用户信息 -->
		<!-- 使用extends继承，不用在中配置订单信息和用户信息的映射 -->
		
		<!-- 订单明细信息
		一个订单关联查询出了多条明细，要使用collection进行映射
		collection：对关联查询到多条记录映射到集合对象中
		property：将关联查询到多条记录映射到cn.itcast.mybatis.po.Orders哪个属性
		ofType：指定映射到list集合属性中pojo的类型
		 -->
		 <collection property="orderdetails" ofType="org.mybatis.demo.po.Orderdetail">
		 	<!-- id：订单明细唯 一标识
		 	property:要将订单明细的唯 一标识 映射到org.mybatis.demo.po.Orderdetail的哪个属性
		 	  -->
		 	<id column="orderdetail_id" property="id"/>
		 	<result column="items_id" property="itemsId"/>
		 	<result column="items_num" property="itemsNum"/>
		 	<result column="orders_id" property="ordersId"/>
		 </collection>
	</resultMap>
	
	<!-- 订单查询关联用户的resultMap
	将整个查询的结果映射到cn.itcast.mybatis.po.Orders中
	 -->
	<resultMap type="org.mybatis.demo.po.Orders" id="OrdersUserResultMap">
		<!-- 配置映射的订单信息 -->
		<!-- id：指定查询列中的唯 一标识，订单信息的中的唯 一标识，如果有多个列组成唯一标识，配置多个id
			column：订单信息的唯 一标识 列
			property：订单信息的唯 一标识 列所映射到Orders中哪个属性
		  -->
		<id column="id" property="id"/>
		<result column="user_id" property="userId"/>
		<result column="number" property="number"/>
		<result column="createtime" property="createtime"/>
		<result column="note" property="note"/>
		
		<!-- 配置映射的关联的用户信息 -->
		<!-- association：用于映射关联查询单个对象的信息
		property：要将关联查询的用户信息映射到Orders中哪个属性
		 -->
		<association property="user"  javaType="org.mybatis.demo.po.User">
			<!-- id：关联查询用户的唯 一标识
			column：指定唯 一标识用户信息的列
			javaType：映射到user的哪个属性
			 -->
			<id column="user_id" property="id"/>
			<result column="username" property="username"/>
			<result column="sex" property="sex"/>
			<result column="address" property="address"/>
		</association>
	</resultMap>
	
</mapper>

4.3、Mapper接口

package org.mybatis.demo.mapper;

import java.util.List;

import org.mybatis.demo.po.Orders;

public interface OrdersMapper {

	public List<Orders> findOrdersAndOrderDetailResultMap();
}

4.4、单元测试

package org.mybatis.demo.test;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

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 org.junit.Before;
import org.junit.Test;
import org.mybatis.demo.mapper.OrdersMapper;
import org.mybatis.demo.po.Orderdetail;
import org.mybatis.demo.po.Orders;

public class TestMyBatis {

	private SqlSessionFactory sessionFactory;

	@Before
	public void createSqlSessionFactory() throws IOException {
		//引入配置文件
		String resource = "SqlMapConfig.xml";
		InputStream ins = Resources.getResourceAsStream(resource);
		
		//创建SqlSession对象
		sessionFactory = new SqlSessionFactoryBuilder().build(ins);
	}
	
	@Test
	public void test1() {
		SqlSession session = null;
		try {
			session = sessionFactory.openSession();
			OrdersMapper mapper = session.getMapper(OrdersMapper.class);
			List<Orders> list = mapper.findOrdersAndOrderDetailResultMap();
			for(Orders order : list){
				System.out.println(order.getUser().getUsername());
				System.out.println("########");
				for(Orderdetail detial:order.getOrderdetails()){
					System.out.println("orderID:" + detial.getId());
				}
			}
		} finally {
			if(session != null){
				session.close();
			}
		}
	}

}

4.5、源代码

MyBatis高级映射（一对多查询）