首页 > 数据库 > 其他综合 >

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

2016-10-15

IntellijMybatis连接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: fanyafeng@live.cn
 */
public class User {
    private int id;
    private String username;
    private Date birthday;
    private String sex;
    private String address;

    private List ordersList;

    public List getOrdersList() {
        return ordersList;
    }

    public void setOrdersList(List 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: fanyafeng@live.cn
 */
public class Orders {
    private int id;
    private int userId;
    private String number;
    private Date createTime;
    private String note;

    private User user;

    private List orderDetailList;

    public List getOrderDetailList() {
        return orderDetailList;
    }

    public void setOrderDetailList(List 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: fanyafeng@live.cn
 */
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: fanyafeng@live.cn
 */
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: fanyafeng@live.cn
 */
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: fanyafeng@live.cn
 */
public interface IOrdersCustomMapper {

    public List findUserByOrderId();

    public List findUserByOrderIdResultMap();

    public List findOrderAndDetailResultMap();

    public List findUserAndOrderDetailResultMap();
}

查询接口所对应的xml:

这里主要的一个是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: fanyafeng@live.cn
 */
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 userCustomList = iOrdersCustomMapper.findUserByOrderIdResultMap();
//        for (int i = 0; i < userCustomList.size(); i++) {
//            System.out.println(userCustomList.get(i).toString());
//        }

//        List ordersList = iOrdersCustomMapper.findOrderAndDetailResultMap();
//        for (int i = 0; i < ordersList.size(); i++) {
//            System.out.println(ordersList.get(i).toString());
//        }

        List userList = iOrdersCustomMapper.findUserAndOrderDetailResultMap();

        sqlSession.close();
    }


}

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

相关文章
最新文章
热点推荐