Intellij Mybatis连接Mysql数据库进行高级映射查询

这篇主要讲的是表的关联查询，一对一，一对多，多对多以及查询方式
主要的表示用户，商品，订单，订单详情
先来看model：
user.java

package com.fanyafeng.model;import java.util.Date;import java.util.List;/** * Author： fanyafeng * Data： 16/10/11 14:56 * Email: [e-mail] */public class User {    private int id;    private String username;    private Date birthday;    private String sex;    private String address;    private List<Orders> ordersList;    public List<Orders> getOrdersList() {        return ordersList;    }    public void setOrdersList(List<Orders> ordersList) {        this.ordersList = ordersList;    }    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 Date getBirthday() {        return birthday;    }    public void setBirthday(Date birthday) {        this.birthday = birthday;    }    public String getSex() {        return sex;    }    public void setSex(String sex) {        this.sex = sex;    }    public String getAddress() {        return address;    }    public void setAddress(String address) {        this.address = address;    }    @Override    public String toString() {        return "User{" +                "id=" + id +                ", username='" + username + '\'' +                ", birthday=" + birthday +                ", sex='" + sex + '\'' +                ", address='" + address + '\'' +                '}';    }}

订单，可以自己分析一下，正常的户一个用户应该关联多个订单，用户的id作为唯一的标识，进行订单的查询。
order.java

package com.fanyafeng.model;import java.util.Date;import java.util.List;/** * Author： fanyafeng * Data： 16/10/13 16:00 * Email: [e-mail] */public class Orders {    private int id;    private int userId;    private String number;    private Date createTime;    private String note;    private User user;    private List<OrderDetail> orderDetailList;    public List<OrderDetail> getOrderDetailList() {        return orderDetailList;    }    public void setOrderDetailList(List<OrderDetail> orderDetailList) {        this.orderDetailList = orderDetailList;    }    public User getUser() {        return user;    }    public void setUser(User user) {        this.user = user;    }    public int getId() {        return id;    }    public void setId(int id) {        this.id = id;    }    public int getUserId() {        return userId;    }    public void setUserId(int userId) {        this.userId = userId;    }    public String getNumber() {        return number;    }    public void setNumber(String number) {        this.number = number;    }    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;    }    @Override    public String toString() {        return "Orders{" +                "id=" + id +                ", userId=" + userId +                ", number='" + number + '\'' +                ", createTime=" + createTime +                ", note='" + note + '\'' +                ", user=" + user +                '}';    }}

一张订单会包含多个订单详情
userdetail.java

package com.fanyafeng.model;/** * Author： fanyafeng * Data： 16/10/13 15:58 * Email: [e-mail] */public class OrderDetail {    private int id;    private int ordersId;    private int itemsId;    private int itemsNum;    private Items items;    public Items getItems() {        return items;    }    public void setItems(Items items) {        this.items = items;    }    public int getId() {        return id;    }    public void setId(int id) {        this.id = id;    }    public int getOrdersId() {        return ordersId;    }    public void setOrdersId(int ordersId) {        this.ordersId = ordersId;    }    public int getItemsId() {        return itemsId;    }    public void setItemsId(int itemsId) {        this.itemsId = itemsId;    }    public int getItemsNum() {        return itemsNum;    }    public void setItemsNum(int itemsNum) {        this.itemsNum = itemsNum;    }    @Override    public String toString() {        return "OrderDetail{" +                "id=" + id +                ", ordersId=" + ordersId +                ", itemsId=" + itemsId +                ", itemsNum=" + itemsNum +                '}';    }}

一个订单详情又会包含多个商品
items.java

package com.fanyafeng.model;import java.util.Date;/** * Author： fanyafeng * Data： 16/10/13 15:56 * Email: [e-mail] */public class Items {    private int id;    private String name;    private float price;    private String detail;    private String pic;    private Date createTime;    public int getId() {        return id;    }    public void setId(int id) {        this.id = id;    }    public String getName() {        return name;    }    public void setName(String name) {        this.name = name;    }    public float getPrice() {        return price;    }    public void setPrice(float price) {        this.price = price;    }    public String getDetail() {        return detail;    }    public void setDetail(String detail) {        this.detail = detail;    }    public String getPic() {        return pic;    }    public void setPic(String pic) {        this.pic = pic;    }    public Date getCreateTime() {        return createTime;    }    public void setCreateTime(Date createTime) {        this.createTime = createTime;    }    @Override    public String toString() {        return "Items{" +                "id=" + id +                ", name='" + name + '\'' +                ", price=" + price +                ", detail='" + detail + '\'' +                ", pic='" + pic + '\'' +                ", createTime=" + createTime +                '}';    }}

model并不是一次全部建好的，都是根据相应的sql语句找的对应信息再去加的相应的属性。
还有一个order的扩展类，用来说明resulttype的
orderscustom.java

package com.fanyafeng.model;/** * Author： fanyafeng * Data： 16/10/13 16:06 * Email: [e-mail] */public class OrdersCustom extends Orders {    private String username;    private String sex;    private String address;    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 String getAddress() {        return address;    }    public void setAddress(String address) {        this.address = address;    }    @Override    public String toString() {        return "OrdersCustom{" +                "username='" + username + '\'' +                ", sex='" + sex + '\'' +                ", address='" + address + '\'' +                '}';    }}

再来看一下定义的查询接口

package com.fanyafeng.mapper;import com.fanyafeng.model.Orders;import com.fanyafeng.model.OrdersCustom;import com.fanyafeng.model.User;import java.util.List;/** * Author： fanyafeng * Data： 16/10/13 16:11 * Email: [e-mail] */public interface IOrdersCustomMapper {    public List<OrdersCustom> findUserByOrderId();    public List<Orders> findUserByOrderIdResultMap();    public List<Orders> findOrderAndDetailResultMap();    public List<User> findUserAndOrderDetailResultMap();}

查询接口所对应的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.fanyafeng.mapper.IOrdersCustomMapper">    <!--查询订单关联用户信息-->    <select id="findUserByOrderId" resultType="com.fanyafeng.model.OrdersCustom">      SELECT          orders.*, user.username, user.sex, user.address      FROM          orders,          user      WHERE          orders.user_id = user.id;    </select>    <resultMap id="findUserResultMap" type="com.fanyafeng.model.Orders">        <!--如果有多个列组成唯一标识,则配置多个id-->        <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="com.fanyafeng.model.User">            <id column="user_id" property="id"/>            <result column="username" property="username"/>            <result column="sex" property="sex"/>            <result column="address" property="address"/>        </association>    </resultMap>    <!--使用resultmap查询订单关联用户信息-->    <select id="findUserByOrderIdResultMap" resultMap="findUserResultMap">        SELECT        orders.*, user.username, user.sex, user.address        FROM        orders,        user        WHERE        orders.user_id = user.id;    </select>    <resultMap id="findOrderDetailResultMap" type="com.fanyafeng.model.Orders" extends="findUserResultMap">        <!--双id进行去重-->        <!--订单信息和用户信息可以继承-->        <!--如果有多个列组成唯一标识,则配置多个id-->        <!--<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="com.fanyafeng.model.User">-->        <!--<id column="user_id" property="id"/>-->        <!--<result column="username" property="username"/>-->        <!--<result column="sex" property="sex"/>-->        <!--<result column="address" property="address"/>-->        <!--</association>-->        <collection property="orderDetailList" ofType="com.fanyafeng.model.OrderDetail">            <id column="orderdetail_id" property="id"/>            <result column="orders_id" property="ordersId"/>            <result column="items_id" property="itemsId"/>            <result column="items_num" property="itemsNum"/>        </collection>    </resultMap>    <!--查询用户以及订单明细-->    <select id="findOrderAndDetailResultMap" resultMap="findOrderDetailResultMap">        SELECT          orders.*, user.username, user.sex, user.address,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 id="UserAndOrderDetailResultMap" type="com.fanyafeng.model.User">        <id column="user_id" property="id"/>        <result column="username" property="username"/>        <result column="sex" property="sex"/>        <result column="address" property="address"/>        <collection property="ordersList" ofType="com.fanyafeng.model.Orders">            <id column="order_id" property="id"/>            <result column="number" property="number"/>            <result column="createtime" property="createTime"/>            <result column="note" property="note"/>            <collection property="orderDetailList" ofType="com.fanyafeng.model.OrderDetail">                <id column="orderdetail_id" property="id"/>                <result column="orders_id" property="ordersId"/>                <result column="items_id" property="itemsId"/>                <result column="items_num" property="itemsNum"/>                <association property="items" javaType="com.fanyafeng.model.Items">                    <id column="items_id" property="id"/>                    <result column="createtime" property="createTime"/>                    <result column="items_price" property="price"/>                    <result column="items_detail" property="detail"/>                    <result column="item_name" property="name"/>                </association>            </collection>        </collection>    </resultMap>    <select id="findUserAndOrderDetailResultMap" resultMap="UserAndOrderDetailResultMap">        SELECT           orders.*,          user.username,          user.sex,          user.address,          orderdetail.id orderdetail_id,          orderdetail.items_id,          orderdetail.items_num,          orderdetail.orders_id,          items.name items_name,          items.detail items_detail,          items.price items_price      FROM          orders,          user,          orderdetail,          items        WHERE          orders.user_id = user.id        AND orderdetail.orders_id = orders.id        AND orderdetail.items_id = items.id;    </select>    <select id="findUserOrdersLazyLoad" resultMap="UserOrdersLazyLoadResultMap">        SELECT * FROM user    </select></mapper>

这里主要的一个是collection和association代码中都有注释，剩下的就是测试类了，回顾一下其实不难的，还有就是缓存和整合了，再来上一下测试代码：

package test.com.fanyafeng.mapper;import com.fanyafeng.mapper.IOrdersCustomMapper;import com.fanyafeng.model.Orders;import com.fanyafeng.model.OrdersCustom;import com.fanyafeng.model.User;import com.fanyafeng.model.UserCustom;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 java.util.List;import static org.junit.Assert.*;/** * Author： fanyafeng * Data： 16/10/13 16:23 * Email: [e-mail] */public class IOrdersCustomMapperTest {    private SqlSessionFactory sqlSessionFactory;    @Before    public void before() throws Exception {        sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsReader("Configuration.xml"));    }    @Test    public void testFindUserByOrderId() throws Exception {        SqlSession sqlSession = sqlSessionFactory.openSession();        IOrdersCustomMapper iOrdersCustomMapper = sqlSession.getMapper(IOrdersCustomMapper.class);//        List<OrdersCustom> userCustomList = iOrdersCustomMapper.findUserByOrderIdResultMap();//        for (int i = 0; i < userCustomList.size(); i++) {//            System.out.println(userCustomList.get(i).toString());//        }//        List<Orders> ordersList = iOrdersCustomMapper.findOrderAndDetailResultMap();//        for (int i = 0; i < ordersList.size(); i++) {//            System.out.println(ordersList.get(i).toString());//        }        List<User> userList = iOrdersCustomMapper.findUserAndOrderDetailResultMap();        sqlSession.close();    }}

是不是很简单，这里我是打了断点看的，后文稍微说一下debug，因为和eclipse有点不同，但是比eclipse方便简洁，功能更强大，这里面我借助了一个mysqlbench