一. 数据准备
1. 品牌表
CREATE TABLE brand( bid INT(6) PRIMARY KEY AUTO_INCREMENT, name varchar(100), status INT(1) DEFAULT 1, create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) DEFAULT charset utf8;
2. 商品表
CREATE TABLE product ( pid INT(6) PRIMARY KEY AUTO_INCREMENT, bid INT(6), name VARCHAR(200), price DOUBLE(6, 2), status INT(1) DEFAULT 1, create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_product_bid FOREIGN KEY (bid) REFERENCES brand (bid) ) DEFAULT charset utf8;
3. 用户表
CREATE TABLE user_info ( user_id INT(6) PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100), password VARCHAR(100), status INT(1) DEFAULT 1, create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) DEFAULT charset = utf8;
4. 收货地址表
CREATE TABLE address ( address_id INT(6) PRIMARY KEY AUTO_INCREMENT, user_id INT(6), name VARCHAR(100), tel VARCHAR(11), addr VARCHAR(200), status INT(1) DEFAULT 1, create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_user_id FOREIGN KEY (user_id) REFERENCES user_info (user_id) ) DEFAULT charset = utf8;
5. 订单表
CREATE TABLE orders ( order_id INT(6) PRIMARY KEY AUTO_INCREMENT, user_id INT(6), address_id INT(6), status INT(1) DEFAULT 1, create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_orders_user_id FOREIGN KEY (user_id) REFERENCES user_info (user_id), CONSTRAINT fk_orders_address_id FOREIGN KEY (address_id) REFERENCES address (address_id) ) DEFAULT charset = utf8;
6. 订单明细表
CREATE TABLE order_detail ( dbid INT(6) PRIMARY KEY AUTO_INCREMENT, order_id INT(6), product_id INT(6), name VARCHAR(200), price DOUBLE(6, 2), quantity INT(3), status INT(1) DEFAULT 1, create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_od_order_id FOREIGN KEY (order_id) REFERENCES orders (order_id), CONSTRAINT fk_od_product_id FOREIGN KEY (product_id) REFERENCES product(pid) ) DEFAULT charset = utf8;
7. 插入数据
INSERT INTO brand(name) VALUE ('苹果');
INSERT INTO brand(name) VALUE ('华为');
INSERT INTO product(bid, name, price)
VALUES (1, 'iPhone XS', 10);
INSERT INTO product(bid, name, price)
VALUES (1, 'iPhone XS Max', 20);
INSERT INTO product(bid, name, price)
VALUES (2, 'huawei P30', 30);
INSERT INTO product(bid, name, price)
VALUES (2, 'huawei P30 Pro', 40);
INSERT INTO user_info(name, password)
VALUES ('tom', '123456');
INSERT INTO uSer_info(name, password)
VALUES ('jerry', '123456');
INSERT INTO orders (user_id, address_id)
VALUES (1, 1);
INSERT INTO order_detail (order_id, product_id, name, price, quantity)
VALUES (1, 1, 'iPhone XS', 10, 1);
INSERT INTO order_detail (order_id, product_id, name, price, quantity)
VALUES (1, 3, 'huawei P30', 10, 1);
INSERT INTO address(user_id,name,tel,addr)VALUES(1,'张三','18666666666','山东济宁');
INSERT INTO address(user_id,name,tel,addr)VALUES(1,'张小三','13666666666','山东济南');
8. Product实体类
package io.zhangjia.entity;
import java.sql.Timestamp;
public class Product {
private Integer productId;
private String name;
private Double price;
private Integer quantity;
private Integer status;
private Timestamp createTime;
public Integer getProductId() {
return productId;
}
public void setProductId(Integer productId) {
this.productId = productId;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Double getPrice() {
return price;
}
public void setPrice(Double price) {
this.price = price;
}
public Integer getQuantity() {
return quantity;
}
public void setQuantity(Integer quantity) {
this.quantity = quantity;
}
public Integer getStatus() {
return status;
}
public void setStatus(Integer status) {
this.status = status;
}
public Timestamp getCreateTime() {
return createTime;
}
public void setCreateTime(Timestamp createTime) {
this.createTime = createTime;
}
@Override
public String toString() {
return "Product{" +
"productId=" + productId +
", name='" + name + '\'' +
", price=" + price +
", quantity=" + quantity +
", status=" + status +
", createTime=" + createTime +
'}';
}
}
9. Address实体类
package io.zhangjia.entity;
import org.omg.PortableInterceptor.INACTIVE;
import java.sql.Timestamp;
public class Address {
private Integer addressId;
private String name;
private String tel;
private String addr;
private Integer status;
private Timestamp createTime;
public Integer getAddressId() {
return addressId;
}
public void setAddressId(Integer addressId) {
this.addressId = addressId;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getTel() {
return tel;
}
public void setTel(String tel) {
this.tel = tel;
}
public String getAddr() {
return addr;
}
public void setAddr(String addr) {
this.addr = addr;
}
public Integer getStatus() {
return status;
}
public void setStatus(Integer status) {
this.status = status;
}
public Timestamp getCreateTime() {
return createTime;
}
public void setCreateTime(Timestamp createTime) {
this.createTime = createTime;
}
@Override
public String toString() {
return "Address{" +
"addressId=" + addressId +
", name='" + name + '\'' +
", tel='" + tel + '\'' +
", addr='" + addr + '\'' +
", status=" + status +
", createTime=" + createTime +
'}';
}
}
10. Order实体类
package io.zhangjia.entity;
import java.sql.Timestamp;
import java.util.List;
public class Order {
private Integer orderId;
private Integer status;
private Timestamp createTime;
private Address address;
private List<Product> products; //注意list里面放的不是订单详情，而是product
public Integer getOrderId() {
return orderId;
}
public void setOrderId(Integer orderId) {
this.orderId = orderId;
}
public Integer getStatus() {
return status;
}
public void setStatus(Integer status) {
this.status = status;
}
public Timestamp getCreateTime() {
return createTime;
}
public void setCreateTime(Timestamp createTime) {
this.createTime = createTime;
}
public Address getAddress() {
return address;
}
public void setAddress(Address address) {
this.address = address;
}
public List<Product> getProducts() {
return products;
}
public void setProducts(List<Product> products) {
this.products = products;
}
@Override
public String toString() {
return "Order{" +
"orderId=" + orderId +
", status=" + status +
", createTime=" + createTime +
", address=" + address +
", products=" + products +
'}';
}
}
二. 功能实现
我们要实现的功能也很简单，根据指定订单编号查询订单详情，订单详情包括：地址（一对一）以及订单的商品（一对多）。
在以往的项目中，这种需要用到多个数据表的操作，我们一般才采用多表连接的方式，这次我们换一种方式，将每个表需要用到的数据单独查询出来，然后在resultMap中使用关联的嵌套 Select 查询将结果映射。
1. OrderMapper接口
public interface OrderMapper {
Order queryByOrderId(Integer orderId);
List<Product> queryProductsByOrderId(Integer orderId);
}
2. AddressMapper接口
public interface AddressMapper {
Address queryByAddressId(Integer addressId);
}
3. 编写AddressMapper.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="io.zhangjia.mapper.AddressMapper">
<select id="queryByAddressId" parameterType="int" resultType="address">
SELECT * FROM address WHERE address_id=#{addressId}
</select>
</mapper>
4. 编写OrderMapper.xml
此时的association和collection 不再是双标签，都采用单标签的形式，其中select用于加载映射语句的 ID（如果是在当前文件中，比如下面的queryProductsByOrderId，则不需要写包名，但如果是其他的Mapper.xml文件中的ID，则需要添加全类名，比如下面的：io.zhangjia.mapper.AddressMapper.queryByAddressId）它会从 column 属性指定的列中检索数据，并将从column中检索的数据作为参数传递给目标 select 语句。
<?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="io.zhangjia.mapper.OrderMapper">
<resultMap id="orderResultMap" type="order">
<id column="order_id" property="orderId"/>
<result column="status" property="status"/>
<result column="create_time" property="createTime"/>
<association property="address" javaType="address"
select="io.zhangjia.mapper.AddressMapper.queryByAddressId"
column="address_id"/>
<collection property="products"
ofType="product"
select="queryProductsByOrderId"
column="order_id"/>
</resultMap>
<select id="queryByOrderId" parameterType="int" resultMap="orderResultMap">
SELECT *
FROM orders
WHERE order_id = #{orderId}
</select>
<select id="queryProductsByOrderId" parameterType="int" resultType="product">
SELECT *
FROM order_detail
WHERE order_id = #{orderId}
</select>
</mapper>
其中的association和collection中的column的address_id和order_id就是queryByOrderId中查询结果的两个列的值
5. 配置mybatis-config.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" />
<settings>
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
<typeAliases>
<package name="io.zhangjia.entity"/>
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="AddressMapper.xml"/>
<mapper resource="OrderMapper.xml"/>
</mappers>
</configuration>
6. 编写测试类
package io.zhangjia.util;
import com.alibaba.fastjson.JSON;
import io.zhangjia.entity.Order;
import io.zhangjia.entity.Product;
import io.zhangjia.mapper.OrderMapper;
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 java.io.IOException;
import java.io.InputStream;
import java.util.List;
public class Main {
public static void main(String[] args) throws IOException {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory build = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = build.openSession(true);
OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);
Order s = mapper.queryByOrderId(2);
List<Product> products = mapper.queryProductsByOrderId(2);
System.out.println("products = " + products);
System.out.println("s = " + s);
System.out.println("sJSON= " + JSON.toJSONString(s));
sqlSession.close();
inputStream.close();
}
}
输出：
products = [Product{productId=1, name='iPhone XS', price=10.0, quantity=1, status=1, createTime=2019-07-03 10:54:16.0}, Product{productId=3, name='huawei P30', price=10.0, quantity=1, status=1, createTime=2019-07-03 10:54:16.0}]
s = Order{orderId=2, status=1, createTime=2019-07-03 10:50:53.0, address=Address{addressId=1, name='张三', tel='18666666666', addr='山东济宁', status=1, createTime=2019-07-03 10:50:21.0}, products=[Product{productId=1, name='iPhone XS', price=10.0, quantity=1, status=1, createTime=2019-07-03 10:54:16.0}, Product{productId=3, name='huawei P30', price=10.0, quantity=1, status=1, createTime=2019-07-03 10:54:16.0}]}
sJSON= {"address":{"addr":"山东济宁","addressId":1,"createTime":1562122221000,"name":"张三","status":1,"tel":"18666666666"},"createTime":1562122253000,"orderId":2,"products":[{"createTime":1562122456000,"name":"iPhone XS","price":10.0,"productId":1,"quantity":1,"status":1},{"createTime":1562122456000,"name":"huawei P30","price":10.0,"productId":3,"quantity":1,"status":1}],"status":1}
三. 懒加载
可能看到这里大家会比较迷惑，为什么我们要将查询语句分开，不再使用之前的一次查询的方式呢？或者说，这样做有什么优点呢？原因如下：
通过这种将sql语句分开执行的方式，我们可以使用Mybatis懒加载（延时加载）功能。所谓懒加载是指Mybatis可以根据需要加载指定的sql语句，能够节省资源和开销。举例如下：
首先修改测试类如下：
public class Main {
public static void main(String[] args) throws IOException {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory build = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = build.openSession(true);
OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);
Order s = mapper.queryByOrderId(2);
sqlSession.close();
inputStream.close();
}
}
在上面的测试类中，我们只查询Order的相关数据，在未开启懒加载的时候，所有的sql语句都会被执行，一共三条如下图：
接下来，我们开启懒加载，在 mybatis-config.xml的<configuration>标签下的<settings>中 加入以下内容，代表开启懒加载
<setting name="lazyLoadingEnabled" value="true"/>
还是使用上面的测试类，可以看到，因为我们只查询了Order的相关数据，所以只执行了一条，也就是order的sql语句：
接下来，我们再修改测试类，添加 s.getAddress()，用来获取地址
public class Main {
public static void main(String[] args) throws IOException {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory build = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = build.openSession(true);
OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);
Order s = mapper.queryByOrderId(2);
s.getAddress();
sqlSession.close();
inputStream.close();
}
}
可以看到，因为我们查询了地址，所以sql语句执行了两条
同理，如果添加s.getProducts()，便会执行三条sql语句
总结一下，比如你现在只想获取订单的信息，并不想获取地址、订单详情，使用懒加载便可以只执行order的sql语句，但是有两种情况需要注意:
Order s = mapper.queryByOrderId(2); //如果只有这一条语句，则只执行一条sql
// System.out.println("s = " + s); //如果将s输出，那么会执行三条语句，因为会调用getAddress和getProducts
// System.out.println("sJSON= " + JSON.toJSONString(s));//如果将s转换为JSON字符串，那么也会执行三条sql语句
请登录之后再进行评论