一. 数据准备
首先创建两个数据表：
1：品牌表（brand）
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. 商品表（product）
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;
接下来插入几条数据：
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);
3. 编写对应的实体类：
Product ：
package io.zhangjia.entity;
import java.sql.Timestamp;
public class Product {
private Integer pid;
private String name;
private Double price;
private Integer status;
private Timestamp createTime;
private Brand brand;
public Integer getPid() {
return pid;
}
public void setPid(Integer pid) {
this.pid = pid;
}
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 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 Brand getBrand() {
return brand;
}
public void setBrand(Brand brand) {
this.brand = brand;
}
@Override
public String toString() {
return "Product{" +
"pid=" + pid +
", name='" + name + '\'' +
", price=" + price +
", status=" + status +
", createTime=" + createTime +
", brand=" + brand +
'}';
}
}
Brand：
package io.zhangjia.entity;
import java.sql.Timestamp;
public class Brand {
private Integer bid;
private String name;
private Integer status;
private Timestamp createTime;
public Integer getBid() {
return bid;
}
public void setBid(Integer bid) {
this.bid = bid;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
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 "Brand{" +
"bid=" + bid +
", name='" + name + '\'' +
", status=" + status +
", createTime=" + createTime +
'}';
}
}
二. 功能实现
先来说一下我们要实现的功能：
根据任意商品的ID，查询该商品的全部信息和该商品的所属的品牌分类的全部信息，所以在上面的Product实体类中，将品牌分类已经作为了其私有属性，也就是说，一个商品和一个分类是一对一的关系。
1. 编写对应接口
public interface ProductMapper {
Product queryByPid(Integer pid);
}
2. 编写ProductMapper.xml
因为brand属性不是简单类型，而是一个Java类，所以我们不能直接使用result 标签对其映射，所以需要用到association元素，association是一个复杂类型的关联，可以用于处理“有一个”类型的关系。 比如商品有一个品牌，那么这个品牌就可以使用association进行包装。association的property的值就是Product实体类中的Brand 对象名brand，而javaType的值就是Brand实体类的全类名（这里只写了brand，是因为起了别名）
<?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.ProductMapper">
<resultMap id="productResultMap" type="product">
<id column="pid" property="pid" />
<result column="product_name" property="name" />
<result column="price" property="price" />
<result column="product_status" property="status" />
<result column="product_create_time" property="createTime" />
<association property="brand" javaType="brand">
<id column="bid" property="bid" />
<result column="brand_name" property="name" />
<result column="brand_status" property="status" />
<result column="brand_create_time" property="createTime" />
</association>
</resultMap>
<select id="queryByPid" parameterType="int" resultMap="productResultMap" >
# 不能直接写SELECT * FROM .... 因为有多个同名字段
SELECT pid,
product.name product_name,
price,
product.status product_status,
product.create_time product_create_time,
brand.bid,
brand.name brand_name,
brand.status brand_status,
brand.create_time brand_create_time
FROM product,
brand
WHERE brand.bid = product.bid
AND pid = #{pid}
</select>
</mapper>
接下来配置mybatis-config.xml，配置内容依旧和《Mybatis：增删改查》一文中的第四节内容基本相同，不同之处只是需要在mappers标签中将新添加的ProductMapper.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="ProductMapper.xml"/>
</mappers>
</configuration>
3. 最后编写测试类：
package io.zhangjia.util;
import io.zhangjia.entity.Book;
import io.zhangjia.entity.Product;
import io.zhangjia.mapper.BookMapper;
import io.zhangjia.mapper.ProductMapper;
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.ArrayList;
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);
ProductMapper mapper = sqlSession.getMapper(ProductMapper.class);
Product product = mapper.queryByPid(1);
System.out.println("(product) = " + JSON.toJSONString(product));
sqlSession.close();
inputStream.close();
}
}
输出：
{"brand":{"bid":1,"createTime":1562050565000,"name":"苹果","status":1},"createTime":1562050565000,"name":"iPhone XS","pid":1,"price":10.0,"status":1}
请登录之后再进行评论