通过顾客-订单关系来表示关联查询
1.建表
(1)tbl_customer表
(2)tbl_order表
其中customer_id为外键与tbl_customer表的id相关联
2.创建实体类
(1)Customer.java
package com.gem.entity;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.util.List;
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Customer {
private Long id;
private String name;
private String phone;
/** 关联(一对多)*/
private List<Order> orders;
}
(2)order.java
package com.gem.entity;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.util.Date;
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Order {
private Long id;
private String no;
private Date createdate;
private Double price;
//多对一
private Customer customer;
}
3.创建映射文件
CustomerMapper.java
package com.gem.mapper;
import com.gem.entity.Customer;
import com.gem.entity.Order;
import org.apache.ibatis.annotations.Param;
import java.util.List;
public interface CustomerMapper {
/**
* 关联查询
* 根据用户姓名(模糊)查询用户信息,如果该用户有订单,则级联查询出他所有的
*/
List<Customer> selectCustomerAndOrderByName(String name);
/**
* 关联查询
* 根据订单价格范围,查询该订单,并且级联查询出该所属客户
*/
List<Order> selectOrderAndCustomerByPrice(@Param("min") Double min,@Param("max") Double max);
/**
* 关联查询
* 查询用户信息,如果该用户有订单,则级联查询出他所有的订单信息(订单信息延迟加载)
*/
List<Customer> selectCustomerAndOrder();
}
4.创建xml文件
CustomerMapper.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接口保持一致-->
<mapper namespace="com.gem.mapper.CustomerMapper">
<select id="selectCustomerAndOrderByName" parameterType="String" resultMap="customerAndOrderMap">
select c.id cid,c.name,c.phone,o.id oid,o.no,o.createdate,o.price from tbl_customer c left join tbl_order o
on o.customer_id=c.id where c.name like "%"#{name}"%"
</select>
<!-- 定义ResultMap,作用是用来封装结果集-->
<resultMap id="customerAndOrderMap" type="customer">
<id column="cid" property="id" />
<result column="name" property="name"/>
<result column="phone" property="phone"/>
<!-- 一个顾客对应多个订单信息-->
<collection property="orders" ofType="order">
<id column="oid" property="id"/>
<result column="no" property="no"/>
<result column="createdate" property="createdate"/>
<result column="price" property="price"/>
</collection>
</resultMap>
<!-- 根据订单价格范围,查询该订单,并且级联查询出该所属客户-->
<select id="selectOrderAndCustomerByPrice" parameterType="Double" resultMap="orderAndcustomerMap">
select o.*,c.id cid,c.name,c.phone from tbl_order o join tbl_customer c
on o.customer_id=c.id where o.price between #{min} and #{max}
</select>
<!-- 定义ResultMap,作用是用来封装结果集-->
<!-- resultMap可以通过extends来己成,实现复用-->
<resultMap id="orderAndcustomerMap" type="order">
<id column="id" property="id"/>
<result column="no" property="no"/>
<result column="createdate" property="createdate"/>
<result column="price" property="price"/>
<!-- 一个订单只对应一个顾客-->
<association property="customer" javaType="customer">
<id column="cid" property="id" />
<result column="name" property="name"/>
<result column="phone" property="phone"/>
</association>
</resultMap>
</mapper>
5.生成测试类
CustomerMapperTest.java
package com.gem.mapper;
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.After;
import org.junit.Before;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import static org.junit.Assert.*;
public class CustomerMapperTest {
SqlSession sqlSession;
CustomerMapper customerMapper;
@Before
public void setUp() throws IOException {
//加载mybatis配置文件
InputStream in = Resources.getResourceAsStream("mybatis.xml");
//获取SqlSessionFactory连接工厂
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
//获取连接
sqlSession = sqlSessionFactory.openSession(true);
//获取mapper代理对象
customerMapper = sqlSession.getMapper(CustomerMapper.class);
}
@After
public void tearDown() {
if (sqlSession != null) {
sqlSession.close();
}
}
@Test
public void selectCustomerAndOrderByName() {
customerMapper.selectCustomerAndOrderByName("a").forEach(System.out::println);
}
@Test
public void selectCustomerByOrderPrice() {
customerMapper.selectOrderAndCustomerByPrice(1000.00,2000.00).forEach(System.out::println);
}
}
下面是项目文件