一. 数据准备
首先我们的数据表还是采用《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}
请登录之后再进行评论