这篇主要讲的是表的关联查询,一对一,一对多,多对多以及查询方式
主要的表示用户,商品,订单,订单详情
先来看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<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: 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<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: 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<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: 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<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