导语:
关于<resultMap>标签映射,<association>&<collection>的使用:跳转文章查看
无论是什么关联关系,如果某方持有另一方的集合,则使用<collection>
标签完成映射,如果某方持有另一方的对象,则使用<association>
标签完成映射。
一、多对一查询:
“多”的一方为主表,“一”的一方为副表,主表关联副表,应该在主表中加入副表对象作为属性。
根据订单ID查询订单信息 (多) 及该订单所属的顾客的信息 (一) 。
实现思路:
方式一:association
- 创建订单类,存储订单信息:主表
注意我们在订单类中封装了该订单下的顾客的信息。
package com.user.pojo;
public class Order {
//订单信息
private Integer id;
private String orderNumber;
private Double orderPrice;
private Integer customer_id;
//订单所属的客户
private Customer customer;
@Override
public String toString() {
return "Order{" +
"id=" + id +
", orderNumber='" + orderNumber + '\'' +
", orderPrice=" + orderPrice +
", customer_id=" + customer_id +
", customer=" + customer +
'}';
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getOrderNumber() {
return orderNumber;
}
public void setOrderNumber(String orderNumber) {
this.orderNumber = orderNumber;
}
public Double getOrderPrice() {
return orderPrice;
}
public void setOrderPrice(Double orderPrice) {
this.orderPrice = orderPrice;
}
public Integer getCustomer_id() {
return customer_id;
}
public void setCustomer_id(Integer customer_id) {
this.customer_id = customer_id;
}
public Customer getCustomer() {
return customer;
}
public void setCustomer(Customer customer) {
this.customer = customer;
}
public Order(Integer id, String orderNumber, Double orderPrice, Integer customer_id, Customer customer) {
this.id = id;
this.orderNumber = orderNumber;
this.orderPrice = orderPrice;
this.customer_id = customer_id;
this.customer = customer;
}
public Order() {
}
}
- 创建顾客类,存储顾客信息:副表
package com.user.pojo;
import java.util.List;
public class Customer {
//顾客信息
private Integer id;
private String name;
private Integer age;
@Override
public String toString() {
return "Customer{" +
"id=" + id +
", name='" + name + '\'' +
", age=" + age +
'}';
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public Customer(Integer id, String name, Integer age) {
this.id = id;
this.name = name;
this.age = age;
}
public Customer() {
}
}
- 动态代理接口:
package com.user.mapper;
import com.user.pojo.Customer;
import com.user.pojo.Order;
public interface OrderAndCustomerMapper{
//根据主键查订单并同时查询该订单的客户信息
Order getById(Integer id);
}
- Mapper.xml文件:
因为我们的查询结果包括该订单信息和订单所属顾客的信息,所以我们的返回值需要自定义一个map集合来存储。我们这里使用<resultMap>
标签映射的方式创建我们的集合customermap
,并将该集合作为我们的返回值类型。
<?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.user.mapper.OrderAndCustomerMapper">
<!--
type:map集合中存储的数据单位,是order对象
id:该map结构的标识符
-->
<resultMap id="ordermap" type="order">
<!--
主键用id标签绑定,非主键用result标签绑定,集合用collection标签绑定,
这里没有用collection标签,而是用了association,因为返回的只有一行数据(一个顾客的信息),不需要集合存储
property是order类中的属性,column是属性对应存储的数据库表中的列名,javaType是返回的数据用什么存储
-->
<!--主键绑定-->
<id property="id" column="id"></id>
<!--非主键绑定-->
<result property="orderNumber" column="orderNumber"></result>
<result property="orderPrice" column="orderPrice"></result>
<result property="customer_id" column="customer_id"></result>
<!--顾客数据,不在order表内-->
<association property="customer" javaType="customer">
<id property="id" column="id"></id>
<result property="name" column="name"></result>
<result property="age" column="age"></result>
</association>
</resultMap>
<select id="getById" parameterType="Integer" resultMap="ordermap">
select orders.id ,orderNumber ,orderPrice ,customer_id ,customer.id ,name,age
from orders left join customer on orders.customer_id = customer.id
where orders.id = #{id}
</select>
</mapper>
- 测试类:
package com.user;
import com.user.mapper.OrderAndCustomerMapper;
import com.user.pojo.Order;
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;
public class MyTest {
SqlSession sqlSession;
OrderMapper orderMapper;
@Before
public void openSqlSession() throws IOException {
InputStream in = Resources.getResourceAsStream("SqlMapConfig.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
sqlSession = factory.openSession();
orderMapper = sqlSession.getMapper(OrderMapper.class);
}
@After
public void closeSqlSession(){
sqlSession.close();
}
@Test
public void testGetOrderById(){
Order order = orderMapper.getById(11);
System.out.println(order);
}
}
- 其余基础配置文件:
<?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="jdbc.properties"></properties>
<settings>
<setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>
<typeAliases>
<package name="com.user.pojo"/>
<package name="com.user.mapper"/>
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"></transactionManager>
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driverClassName}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
</environments>
<mappers>
<package name="com.user.mapper"></package>
</mappers>
</configuration>
方式二:分布查询+延迟加载:常用
- 分布查询+延迟加载优点:
- 复用性强,多条语句可重复利用。
- 充分利用延迟加载机制,提高性能。
延迟加载:又叫懒加载,顾名思义就是很懒。多表关联查询中查询的是多个表,如果我们在使用中只用到了第一个表的查询结果,那么只执行第一条sql,后续表的查询将不会执行。
代码同方式一,只需要改三个地方:
- 动态代理接口:
package com.user.mapper;
import com.user.pojo.Customer;
import com.user.pojo.Order;
public interface OrderAndCustomerMapper{
//因为是分布查询,所以我们就需要两条sql分开执行
//根据主键查订单
Order getOrderById(Integer id);
//根据主键查客户信息
Customer getCustomerById(Integer id);
}
- Mapper.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.user.mapper.OrderAndCustomerMapper">
<!--association标签:
property:order类中的属性
select:下一条要查询的语句
column:指定下一条查询语句的查询条件
-->
<resultMap id="ordermap" type="order">
<id property="id" column="id"></id>
<result property="orderNumber" column="orderNumber"></result>
<result property="orderPrice" column="orderPrice"></result>
<result property="customer_id" column="customer_id"></result>
<association property="customer"
select="com.user.mapper.OrderMapper.getCustomerById"
column="customer_id"
></association>
</resultMap>
<!--因为是分布查询,所以我们就需要两条sql分开执行-->
<select id="getOrderById" parameterType="Integer" resultMap="ordermap">
select id ,orderNumber ,orderPrice ,customer_id
from orders
where orders.id = #{id}
</select>
<select id="getCustomerById" parameterType="Integer" resultType="Customer">
select id ,name,age
from customer
where id = #{id}
</select>
</mapper>
- 核心配置文件中启动懒加载:
<!--
这里将所有分布查询都设置为懒加载。
如果某个分布查询我们不希望它懒加载,那么我们可以在association 标签中设置属性 fetchType="eager"
-->
<settings>
<setting name="lazyLoadingEnabled" value="true"/>
</settings>
二、一对多查询:
“一”的一方为主表,“多”的一方为副表,主表关联副表,应该在主表中加入副表对象的集合作为属性。
根据顾客ID查询顾客信息 (一) ,同时将顾客名下所有订单查出 (多) 。
实现思路:
方式一:collection
- 创建顾客类,存储顾客信息:
注意我们在顾客类中用List集合封装了顾客名下的订单信息。
package com.user.pojo;
import java.util.List;
public class Customer {
//customer表中的三个列
private Integer id;
private String name;
private Integer age;
//该客户名下所有订单集合
private List<Order> orderList;
@Override
public String toString() {
return "Customer{" +
"id=" + id +
", name='" + name + '\'' +
", age=" + age +
", orderList=" + orderList +
'}';
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public List<Order> getOrderList() {
return orderList;
}
public void setOrderList(List<Order> orderList) {
this.orderList = orderList;
}
public Customer(Integer id, String name, Integer age, List<Order> orderList) {
this.id = id;
this.name = name;
this.age = age;
this.orderList = orderList;
}
public Customer() {
}
}
- 创建订单类,存储订单信息:
package com.user.pojo;
public class Order {
private Integer id;
private String orderNumber;
private Double orderPrice;
@Override
public String toString() {
return "Order{" +
"id=" + id +
", orderNumber='" + orderNumber + '\'' +
", orderPrice=" + orderPrice +
'}';
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getOrderNumber() {
return orderNumber;
}
public void setOrderNumber(String orderNumber) {
this.orderNumber = orderNumber;
}
public Double getOrderPrice() {
return orderPrice;
}
public void setOrderPrice(Double orderPrice) {
this.orderPrice = orderPrice;
}
public Order(Integer id, String orderNumber, Double orderPrice) {
this.id = id;
this.orderNumber = orderNumber;
this.orderPrice = orderPrice;
}
public Order() {
}
}
- 动态代理接口:
package com.user.mapper;
import com.user.pojo.Customer;
public interface OrderAndCustomerMapper{
//根据客户id查询客户所有信息并同时查询该客户名下的所有订单
Customer getById(Integer id);
}
- Mapper.xml文件:
因为我们的查询结果包括该顾客信息和顾客所有的订单信息,所以我们的返回值需要自定义一个map集合来存储。我们这里使用<resultMap>
标签映射的方式创建我们的集合customermap
,并将该集合作为我们的返回值类型。
<?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.user.mapper.OrderAndCustomerMapper">
<!--
type:map集合中存储的数据单位,是customer对象
id:该map结构的标识符
-->
<resultMap id="customermap" type="customer">
<!--
主键用id标签绑定,非主键用result标签绑定,集合用collection标签绑定
property是customer类中的属性,column是属性对应存储的数据库表中的列名,ofType是集合中存储的数据类型
-->
<!--主键绑定-->
<id property="id" column="id"></id>
<!--非主键绑定-->
<result property="name" column="name"></result>
<result property="age" column="age"></result>
<!--订单数据,不在customer表内-->
<collection property="orderList" ofType="order">
<id property="id" column="id"></id>
<result property="orderNumber" column="orderNumber"></result>
<result property="orderPrice" column="orderPrice"></result>
</collection>
</resultMap>
<select id="getById" parameterType="Integer" resultMap="customermap">
select customer.id ,name,age ,orders.id ,orderNumber ,orderPrice ,customer_id
from customer left join orders on customer.id = orders.customer_id
where customer.id = #{id}
</select>
<!--#{id}为占位符,随便写的,表示所要查询的顾客ID-->
</mapper>
- 测试程序:
import java.io.InputStream;
import java.util.List;
public class MyTest {
SqlSession sqlSession;
CustomerMapper customerMapper;
@Before
public void openSqlSession() throws IOException {
InputStream in = Resources.getResourceAsStream("SqlMapConfig.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
sqlSession = factory.openSession();
customerMapper = sqlSession.getMapper(CustomerMapper.class);
}
@After
public void closeSqlSession(){
sqlSession.close();
}
@Test
public void testGetCustomerById(){
Customer customer = customerMapper.getById(1);
System.out.println(customer);
}
}
- 其余基础配置文件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="jdbc.properties"></properties>
<settings>
<setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>
<typeAliases>
<package name="com.user.pojo"/>
<package name="com.user.mapper"/>
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"></transactionManager>
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driverClassName}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
</environments>
<mappers>
<package name="com.user.mapper"></package>
</mappers>
</configuration>
方式二:分布查询+延迟加载:常用
代码同方式一,只需要改三个地方:
- 动态代理接口:
package com.user.mapper;
import com.user.pojo.Customer;
import com.user.pojo.Order;
public interface OrderAndCustomerMapper{
//因为是分布查询,所以我们就需要两条sql分开执行
//根据主键查订单
Order getOrderById(Integer id);
//根据主键查客户信息
Customer getCustomerById(Integer id);
}
- Mapper.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.user.mapper.OrderAndCustomerMapper">
<!--association标签:
property:customer类中的属性
select:下一条要查询的语句
column:指定下一条查询语句的查询条件
-->
<resultMap id="customermap" type="customer">
<id property="id" column="id"></id>
<result property="name" column="name"></result>
<result property="age" column="age"></result>
<collection property="orderList"
select="com.user.mapper.OrderMapper.getOrderById"
column="id"
></collection>
</resultMap>
<!--因为是分布查询,所以我们就需要两条sql分开执行-->
<select id="getCustomerById" parameterType="Integer" resultMap="customermap">
select id ,name,age
from customer
where id = #{id}
</select>
<select id="getOrderById" parameterType="Integer" resultType="Order">
select id ,orderNumber ,orderPrice ,customer_id
from orders
where customer_id = #{id}
</select>
</mapper>
- 核心配置文件中启动懒加载:
<!--
这里将所有分布查询都设置为懒加载。
如果某个分布查询我们不希望它懒加载,那么我们可以在association 标签中设置属性 fetchType="eager"
-->
<settings>
<setting name="lazyLoadingEnabled" value="true"/>
</settings>
三、一对一查询:
同多对一。
- 实体类:
- 动态代理接口:
- mapper.xml文件:
四、多对多查询:
多对多关联中,需要通过中间表化解关联关系。中间表描述两张主键表的关联。