一. 数据准备
首先我们的数据表还是采用《Mybatis：一对一查询》一文中第一节创建的两个数据表，但是实体类要稍作修改
将Product 中的Brand属性去掉：
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;
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;
}
@Override
public String toString() {
return "Product{" +
"pid=" + pid +
", name='" + name + '\'' +
", price=" + price +
", status=" + status +
", createTime=" + createTime +
'}';
}
}
给Brand添加products属性：
package io.zhangjia.entity;
import java.sql.Timestamp;
import java.util.List;
public class Brand {
private Integer bid;
private String name;
private Integer status;
private Timestamp createTime;
private List<Product> products;
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;
}
public List<Product> getProducts() {
return products;
}
public void setProducts(List<Product> products) {
this.products = products;
}
@Override
public String toString() {
return "Brand{" +
"bid=" + bid +
", name='" + name + '\'' +
", status=" + status +
", createTime=" + createTime +
", products=" + products +
'}';
}
}
二. 功能实现
先来说一下我们要实现的功能：
根据任意品牌的ID，查询该品牌的全部信息和该品牌所对应的全部商品信息，所以在上面的Brand实体类中，将List<Product>作为了其私有属性，也就是说，一个品牌和商品是一对多的关系
1. 编写对应接口
public interface BrandMapper {
Brand queryByBid(Integer bid);
}
2. 编写BrandMapper.xml
因为Brand类中含有List<Product>属性，所以我们需要使用集合元素collection来处理。collection除了新增的 “ofType” 属性外，其他地方和关联（association）是完全相同的。其中：
property的值就是Brand类中的List<Product> products，而ofType就是List<Product> 中的Product的全类名。
<?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.BrandMapper">
<resultMap id="brandResultMap" type="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"/>
<collection property="products" ofType="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"/>
</collection>
</resultMap>
<select id="queryByBid" parameterType="int" resultMap="brandResultMap">
SELECT brand.bid,
brand.name brand_name,
brand.status brand_status,
brand.create_time brand_create_time,
pid,
product.name product_name,
price,
product.status product_status,
product.create_time product_create_time
FROM product,
brand
WHERE brand.bid = product.bid
AND brand.bid = #{bid}
</select>
</mapper>
接下来配置mybatis-config.xml，配置内容依旧和《Mybatis：增删改查》一文中的第四节内容基本相同，不同之处只是需要在mappers标签中将新添加的BrandMapper.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="BrandMapper.xml"/>
</mappers>
</configuration>
3. 最后编写测试类：
package io.zhangjia.util;
import com.alibaba.fastjson.JSON;
import io.zhangjia.entity.Brand;
import io.zhangjia.mapper.BrandMapper;
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;
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);
BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);
Brand brand = mapper.queryByBid(1);
System.out.println("(product) = " + JSON.toJSONString(brand));
sqlSession.close();
inputStream.close();
}
}
输出：
(product) = {"bid":1,"createTime":1562050565000,"name":"苹果","products":[{"createTime":1562050565000,"name":"iPhone XS","pid":1,"price":10.0,"status":1},{"createTime":1562050565000,"name":"iPhone XS Max","pid":2,"price":20.0,"status":1}],"status":1}
请登录之后再进行评论