Mybatis 多表查询及查询结果映射

使用MyBatis这一强大的框架可以解决很多赋值的问题，其中对于子配置文件中的小细节有很多需要注意的地方，使用这个框架后主要就是对于配置文件的编写和配置。
今天我写了一个多表查询，表的基本结构如下：
用户表t_user:用户编号uid，账号account，昵称nickname，性别gender
订单表t_order:订单编号oid，下单时间gen_time，总价total_price，用户编号user_id
订单详情表:详情编号id，订单编号order_id,商品编号product_id,下单数量amount
商品表:商品编号pid，商品名称pname，商品价格price，商品描述description
其中订单表user_id是外键对应用户表的uid;订单详情表的order_id,product_id分别对应订单表的oid和商品表的pid

 

程序中分别定义了四个实体来映射这四个表
查询语句是：

SELECT  
    uid,  
    account,  
    nickname,  
    gender,  
    oid,  
    gen_time,  
    total_price,  
    user_id,  
    id,  
    order_id,  
    product_id,  
    amount,  
    pid,  
    pname,  
    price,  
    description  
FROM  
    t_user  
JOIN t_order ON t_user.uid = t_order.user_id  
JOIN t_order_product ON t_order_product.order_id = t_order.oid  
JOIN t_product ON t_product.pid = t_order_product.product_id  
WHERE  
    uid = 1  

User类：

package cn.edu360.pojo;

import java.util.List;

public class User {
	private Long id;
	private String account;
	private String nickname;
	private String gender;
	private List<Order> orders;

	/*
	 * (non-Javadoc)
	 * 
	 * @see java.lang.Object#toString()
	 */
	@Override
	public String toString() {
		return "[用户编号:" + id + ", 账号:" + account + ", 昵称:" + nickname + ", 性别:" + gender + ", 所下订单:"
				+ orders + "]";
	}

	/**
	 * @return the orders
	 */
	public List<Order> getOrders() {
		return orders;
	}

	/**
	 * @param orders
	 *            the orders to set
	 */
	public void setOrders(List<Order> orders) {
		this.orders = orders;
	}

	public Long getId() {
		return id;
	}

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

	public String getAccount() {
		return account;
	}

	public void setAccount(String account) {
		this.account = account;
	}

	public String getNickname() {
		return nickname;
	}

	public void setNickname(String nickname) {
		this.nickname = nickname;
	}

	public String getGender() {
		return gender;
	}

	public void setGender(String gender) {
		this.gender = gender;
	}

}

　　

Order类：

package cn.edu360.pojo;

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

public class Order {
    private Long id;
    private Date genTime;
    private Double totalPrice;
    private Long userId;
    private List<Orderitems> orderitems;

    /*
     * (non-Javadoc)
     * 
     * @see java.lang.Object#toString()
     */
    @Override
    public String toString() {
        return "订单详情 [订单号:" + id + ", 下单时间:" + genTime + ", 订单总价:" + totalPrice + ", 所含商品:" + orderitems + "]";
    }

    /**
     * @return the orderitems
     */
    public List<Orderitems> getOrderitems() {
        return orderitems;
    }

    /**
     * @param orderitems
     *            the orderitems to set
     */
    public void setOrderitems(List<Orderitems> orderitems) {
        this.orderitems = orderitems;
    }

    public Long getId() {
        return id;
    }

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

    public Date getGenTime() {
        return genTime;
    }

    public void setGenTime(Date genTime) {
        this.genTime = genTime;
    }

    public Double getTotalPrice() {
        return totalPrice;
    }

    public void setTotalPrice(Double totalPrice) {
        this.totalPrice = totalPrice;
    }

    public Long getUserId() {
        return userId;
    }

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

}

Orderitems类：

package cn.edu360.pojo;

import java.util.List;

public class Orderitems {
    private Long id;
    private Long oid;
    private Long pid;
    private Long amount;
    private List<Product> product;

    /*
     * (non-Javadoc)
     * 
     * @see java.lang.Object#toString()
     */
    @Override
    public String toString() {
        return "[数量:" + amount + product +"]" ;
    }

    /**
     * @return the product
     */
    public List<Product> getProduct() {
        return product;
    }

    /**
     * @param product
     *            the product to set
     */
    public void setProduct(List<Product> product) {
        this.product = product;
    }

    public Long getId() {
        return id;
    }

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

    public Long getOid() {
        return oid;
    }

    public void setOid(Long oid) {
        this.oid = oid;
    }

    public Long getPid() {
        return pid;
    }

    public void setPid(Long pid) {
        this.pid = pid;
    }

    public Long getAmount() {
        return amount;
    }

    public void setAmount(Long amount) {
        this.amount = amount;
    }
}

Product类：

package cn.edu360.pojo;

public class Product {
    private Long id;
    private String name;
    private Double price;
    private String description;

    /*
     * (non-Javadoc)
     * 
     * @see java.lang.Object#toString()
     */
    @Override
    public String toString() {
        return "[商品编号:" + id + ", 商品名称:" + name + ", 价格:" + price + ", 描述:" + description + "]";
    }

    public Long getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Double getPrice() {
        return price;
    }

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

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }

}

用户的实现接口，根据输入ID号查询当前用户的信息以及他的历史订单和订单里所包含的商品明细

也即Dao层接口：

package cn.edu360.mapper;

import cn.edu360.pojo.User;

public interface UserMapper {

    public User getByIdWithOrdersWithProducts(Long id);
}

 用户接口的配置文件

<?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="cn.edu360.mapper.UserMapper">
    <resultMap type="user" id="userOrderitemsResultMap">
        <id property="id" column="uid" />
        <result property="account" column="account" />
        <result property="nickname" column="nickname" />
        <result property="gender" column="gender" />
        <collection property="orders" ofType="Order">
            <id property="id" column="oid" />
            <result property="genTime" column="gen_time" />
            <result property="totalPrice" column="total_price" />
            <result property="userId" column="user_id" />
            <collection property="orderitems" ofType="Orderitems">
                <id property="id" column="id" />
                <result property="oid" column="order_id" />
                <result property="pid" column="product_id" />
                <result property="amount" column="amount" />
                <collection property="product" ofType="Product">
                    <id property="id" column="pid" />
                    <result property="name" column="pname" />
                    <result property="price" column="price" />
                    <result property="description" column="description" />
                </collection>
            </collection>
        </collection>
    </resultMap>
    <select id="getByIdWithOrdersWithProducts" resultMap="userOrderitemsResultMap"
        parameterType="long">
        SELECT
        uid,
        account,
        nickname,
        gender,
        oid,
        gen_time,
        total_price,
        user_id,
        id,
        order_id,
        product_id,
        amount,
        pid,
        pname,
        price,
        description
        FROM
        t_user
        JOIN t_order ON t_user.uid = t_order.user_id
        JOIN t_order_product ON t_order_product.order_id = t_order.oid
        JOIN t_product ON t_product.pid = t_order_product.product_id
        WHERE
        uid = 1
    </select>
</mapper>

核心配置文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <typeAliases>
        <typeAlias type="cn.edu360.pojo.User" alias="User"/>
        <typeAlias type="cn.edu360.pojo.Product" alias="Product"/>
        <typeAlias type="cn.edu360.pojo.Order" alias="Order"/>
        <typeAlias type="cn.edu360.pojo.Orderitems" alias="Orderitems"/>
    </typeAliases>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC" />
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver" />
                <property name="url" value="jdbc:mysql://localhost:3306/storemybatis?characterEncoding=utf-8" />
                <property name="username" value="root" />
                <property name="password" value="12580" />
            </dataSource>
        </environment>
    </environments>
    <!--  <mappers>
        <mapper resource="ProductMapper.xml"/>
    </mappers>  -->
    <mappers>
        <package name="cn.edu360.mapper"/>
    </mappers>
</configuration>

JUnit测试方法：

package cn.edu360.mapper;

import java.io.IOException;

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 cn.edu360.pojo.User;

public class UserMapperTest {

    private SqlSessionFactory sqlSessionFactory = null;
    
    @Before
    public void init() throws IOException {
        sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("SqlMapConfig.xml"));
    }

    @Test
    public void testGetByIdWithOrdersWithProducts() {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper userImpl = sqlSession.getMapper(UserMapper.class);
        User user = userImpl.getByIdWithOrdersWithProducts(1L);
        System.out.println(user);
        sqlSession.close();
    }

}

基本上面的就可以输出当前用户的信息和所有历史订单以及订单的商品详情。