mybatis 一二事（3） - 多表关联查询

db.properties

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/order
jdbc.username=root
jdbc.password=root

SqlMapConfig.xml

<?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>
    <!-- 加载数据库连接参数配置文件 -->
    <properties resource="db.properties" />
    
    <!-- 
        全局配置参数
        比如 二级缓存 延迟加载...等
        此全局参数会影响mybatis运行的性能，要谨慎配置    
     -->
<!--     <settings> -->
<!--     <setting name="" value=""/> -->
<!--     </settings> -->
    
    <!-- 定义别名 -->
    <typeAliases>
        <!-- 单个别名定义
        type：pojo的路径 
        alias：别名的名称
        -->
        <!-- <typeAlias type="cn.itcast.mybatis.po.User" alias="user"/> -->
        <!-- 批量别名定义
        name：指定包名，将包下边的所有pojo定义别名 ，别名为类名（首字母大写或小写都行）
         -->
        <package name="com.mybatis.bean"/>
    </typeAliases>
    
    <!-- 和spring整合后 environments配置将废除 -->
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC" />
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>

    <!-- 配置mapper映射文件 -->
    <mappers>
        <!-- resource方式
        在UserMapper.xml，定义namespace为mapper接口的地址，映射文件通过namespace找到对应的mapper接口文件
         -->
        <!-- <mapper resource="sqlmap/UserMapper.xml" /> -->
        <!-- class方式
        class：指定 mapper接口的地址
        遵循规则：将mapper.xml和mapper.java文件放在一个目录 且文件名相同
         -->
        <!-- <mapper class="cn.itcast.mybatis.mapper.UserMapper"/> -->
        
        <!--
            批量mapper扫描
            遵循规则：将mapper.xml和mapper.java文件放在一个目录 且文件名相同
            主要以这样的方式为主来加载mapper
          -->
        <package name="com.mybatis.mapper"/>
        
        
    </mappers>
</configuration>

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.mybatis.mapper.OrderMapper" >
    
    <!-- 
        ********
        1 对 1 查询建议使用 resultType
        ********
     -->
    
    <!-- type 为主类，即A类包含了B类作为属性，则type就写A类 -->
    <resultMap type="Orders" id="orderUserResultMap">
        <!-- 
            id 和 result 这两个标签是映射到 Orders 中去的 
            如果有多个字段决定id唯一，则写多个id标签
        -->
        <id column="id" property="id"/>
        <result column="order_number" property="order_number"/>
        <result column="user_id" property="user_id"/>

        <!-- 
            用于映射单个关联对象
            property: 将关联信息映射到哪个属性
            javaType: 属性映射的类型
         -->
        <association property="user" javaType="com.mybatis.bean.User">
            <id column="user_id" property="id"/>
            <result column="username" property="username"/>
            <result column="birthday" property="birthday"/>
        </association>
    </resultMap>    


    <!-- 
        extends： 继承上一个resultMap, 公用相同属性
     -->
    <resultMap type="Orders" id="orderUserDetailResultMap" extends="orderUserResultMap">

        <!-- 
            collection： 用于映射集合对象
            property: 将集合信息映射到哪个属性
            ofType: 这个集合对象的类型，也就是<>泛型
         -->
        <collection property="orderdetails" ofType="com.mybatis.bean.Orderdetail">
            <id column="order_detail_id" property="id"/>
            <result column="item_id" property="item_id"/>
            <result column="item_num" property="item_num"/>
            <result column="item_price" property="item_price"/>
        </collection>

    </resultMap>    

    <!-- 
        1 对 1 查询
     -->
    <select id="findOrderUserList" resultType="OrderUserCustom">
        select 
            o.id,
            o.order_number,
            o.user_id,
            u.username,
            u.birthday
        from 
            orders o 
        inner join 
            user u 
        on 
            o.user_id = u.id
    </select>

    <select id="findOrderUserListByResultMap" resultMap="orderUserResultMap">
        select 
            o.id,
            o.order_number,
            o.user_id,
            u.username,
            u.birthday
        from 
            orders o 
        inner join 
            user u 
        on 
            o.user_id = u.id
    </select>
    
    <!-- 
        1 对 多 查询
     -->
     <select id="findOrderItemsList" resultMap="orderUserDetailResultMap">
         select 
                o.id,
                o.order_number,
                o.user_id,
                u.username,
                u.birthday,
                od.item_id,
                od.item_num,
                od.item_price,
                od.id as order_detail_id
            from 
                orders o 
            inner join 
                user u 
            on 
                o.user_id = u.id 
            left join 
                orderdetail od 
            on 
                o.id = od.orders_id
    </select>
</mapper>

OrderMapper.java

package com.mybatis.mapper;

import java.util.List;

import com.mybatis.bean.OrderUserCustom;
import com.mybatis.bean.Orders;

public interface OrderMapper {
    
    public List<OrderUserCustom> findOrderUserList() throws Exception;
    
    public List<Orders> findOrderUserListByResultMap() throws Exception;
    
    public List<Orders> findOrderItemsList() throws Exception;
    
}

 

Items.java

package com.mybatis.bean;

/**
 * 商品信息
 * 
 * @author Thinkpad
 *
 */
public class Items {
    private int id;// 商品id
    private String item_name;// 商品名称
    private Float item_price;// 商品价格
    private String item_detail;// 商品明细

    public int getId() {
        return id;
    }

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

    public String getItem_name() {
        return item_name;
    }

    public void setItem_name(String item_name) {
        this.item_name = item_name;
    }

    public Float getItem_price() {
        return item_price;
    }

    public void setItem_price(Float item_price) {
        this.item_price = item_price;
    }

    public String getItem_detail() {
        return item_detail;
    }

    public void setItem_detail(String item_detail) {
        this.item_detail = item_detail;
    }

}

Orderdetail.java

package com.mybatis.bean;

/**
 * 订单明细
 * 
 * @author Thinkpad
 *
 */
public class Orderdetail {
    private int id;// 主键
    private int orders_id;// 订单id
    private int item_id;// 商品id
    private int item_num;// 商品数量
    private Float item_price;// 商品价格

    // 商品信息
    private Items items;// 明细对应的商品信息

    public int getId() {
        return id;
    }

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

    public int getOrders_id() {
        return orders_id;
    }

    public void setOrders_id(int orders_id) {
        this.orders_id = orders_id;
    }

    public int getItem_id() {
        return item_id;
    }

    public void setItem_id(int item_id) {
        this.item_id = item_id;
    }

    public int getItem_num() {
        return item_num;
    }

    public void setItem_num(int item_num) {
        this.item_num = item_num;
    }

    public Float getItem_price() {
        return item_price;
    }

    public void setItem_price(Float item_price) {
        this.item_price = item_price;
    }

    public Items getItems() {
        return items;
    }

    public void setItems(Items items) {
        this.items = items;
    }

}

Orders.java

package com.mybatis.bean;

import java.util.List;

/**
 * 订单信息
 * 
 * @author Thinkpad
 *
 */
public class Orders {
    private int id;// 订单id
    private int user_id;// 用户id
    private String order_number;// 订单号

    private User user;// 用户信息

    private List<Orderdetail> orderdetails;// 订单明细信息

    public int getId() {
        return id;
    }

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

    public int getUser_id() {
        return user_id;
    }

    public void setUser_id(int user_id) {
        this.user_id = user_id;
    }

    public String getOrder_number() {
        return order_number;
    }

    public void setOrder_number(String order_number) {
        this.order_number = order_number;
    }

    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;
    }

}

OrdersCustom.java

package com.mybatis.bean;

public class OrdersCustom extends User {

    private String user_id;// 用户id
    private String order_number;// 订单号

    public String getUser_id() {
        return user_id;
    }

    public void setUser_id(String user_id) {
        this.user_id = user_id;
    }

    public String getOrder_number() {
        return order_number;
    }

    public void setOrder_number(String order_number) {
        this.order_number = order_number;
    }

}

OrderUserCustom.java

package com.mybatis.bean;

import java.util.Date;

public class OrderUserCustom extends Orders {

    private String username;
    private Date birthday;
    public String getUsername() {
        return username;
    }
    public void setUsername(String username) {
        this.username = username;
    }
    public Date getBirthday() {
        return birthday;
    }
    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

}

User.java

package com.mybatis.bean;

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

public class User implements java.io.Serializable {
    private int id;
    private int[] ids;// 存储多个id
    private String username;// 用户姓名
    private String sex;// 性别
    private Date birthday;// 出生日期
    private String address;// 地址
    private String detail;// 详细信息
    private Float score;// 成绩

    // 订单信息
    private List<Orders> orders;

    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;
    }

    public String getDetail() {
        return detail;
    }

    public void setDetail(String detail) {
        this.detail = detail;
    }

    public Float getScore() {
        return score;
    }

    public void setScore(Float score) {
        this.score = score;
    }

    @Override
    public String toString() {
        return "User [id=" + id + ", username=" + username + ", sex=" + sex
                + ", birthday=" + birthday + ", address=" + address
                + ", detail=" + detail + ", score=" + score + "]";
    }

    public List<Orders> getOrders() {
        return orders;
    }

    public void setOrders(List<Orders> orders) {
        this.orders = orders;
    }

    public int[] getIds() {
        return ids;
    }

    public void setIds(int[] ids) {
        this.ids = ids;
    }

}

 

 

github地址：https://github.com/leechenxiang/mybatis003-order-reference-query