业务背景:
分类与商品之间为一对多关系,一个分类下可以有多个商品。
数据库结构:
分类表:t_category
商品表:t_product
CREATE TABLE t_category (
cid int(11) NOT NULL AUTO_INCREMENT,
catename varchar(48) DEFAULT NULL,
PRIMARY KEY (cid)
) DEFAULT CHARSET=utf8 COMMENT='商品分类';
INSERT INTO t_category VALUES ('1', '手机');
INSERT INTO t_category VALUES ('2', '平板');
INSERT INTO t_category VALUES ('3', '穿戴设备');
CREATE TABLE t_product (
pid int(11) NOT NULL AUTO_INCREMENT,
pname varchar(200) DEFAULT NULL,
type varchar(80) DEFAULT NULL COMMENT '产品型号',
cateid int(11) DEFAULT NULL COMMENT '分类编号',
brandid int(11) DEFAULT NULL COMMENT '品牌编号',
storage int(11) DEFAULT NULL COMMENT '库存数量',
price float DEFAULT NULL,
PRIMARY KEY (pid)
) DEFAULT CHARSET=utf8 COMMENT='商品信息';
INSERT INTO t_product VALUES ('1', 'iPhone X', 'X', '1', '1', '9', '10081');
INSERT INTO t_product VALUES ('2', 'iPhone 8', '8', '1', '1', '16', '7399');
INSERT INTO t_product VALUES ('3', 'Mate10 Pro', 'BLA-AL00', '1', '2', '120', '5399');
INSERT INTO t_product VALUES ('4', 'P10 Plus', ' VKY-AL00', '1', '2', '150', '4488');
INSERT INTO t_product VALUES ('5', 'iPad', 'PGW2CH/A', '2', '1', '130', '3288');
INSERT INTO t_product VALUES ('6', 'iPad mini', 'MK9Q2CH/A', '2', '1', '139', '3188');
INSERT INTO t_product VALUES ('7', 'M3 青春版 平板电脑', 'BAH-W09', '2', '2', '59', '2199');
实体类:
Category.java
package com.cheese.pojo;
import java.util.List;
public class Category {
private int cid;
private String cateName;
private List<Product> productList;//一个分类关联多个产品,用集合存储“多方”对象
public int getCid() {
return cid;
}
public void setCid(int cid) {
this.cid = cid;
}
public String getCateName() {
return cateName;
}
public void setCateName(String cateName) {
this.cateName = cateName;
}
public List<Product> getProductList() {
return productList;
}
public void setProductList(List<Product> productList) {
this.productList = productList;
}
}
Product.java
package com.cheese.pojo;
public class Product {
private int pid;
private String pname;
private String type;//产品型号
private int storage;//库存数
private float price;
public int getPid() {
return pid;
}
public void setPid(int pid) {
this.pid = pid;
}
public String getPname() {
return pname;
}
public void setPname(String pname) {
this.pname = pname;
}
public String getType() {
return type;
}
public void setType(String type) {
this.type = type;
}
public int getStorage() {
return storage;
}
public void setStorage(int storage) {
this.storage = storage;
}
public float getPrice() {
return price;
}
public void setPrice(float price) {
this.price = price;
}
}
Mapper文件:categoryMapper.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.cheese.mapping.categoryMapper">
<!-- 根据cid查询对应的分类信息,以及该分类下的商品信息 -->
<!--
方式一: 嵌套结果: 使用嵌套结果映射来处理重复的联合结果的子集
select c.cid,c.catename,p.pid,p.pname,p.price from t_category c,t_product p
where c.cid=p.cateid and c.cid=1
-->
<select id="getCategory" parameterType="int" resultMap="CategoryResultMap">
select c.cid,c.catename,p.pid,p.pname,p.price from t_category c,t_product p
where c.cid=p.cateid and c.cid=#{cid}
</select>
<resultMap type="com.cheese.pojo.Category" id="CategoryResultMap">
<id property="cid" column="cid"/>
<result property="cateName" column="catename"/>
<!-- ofType指定productList集合中的对象类型 -->
<collection property="productList" ofType="com.cheese.pojo.Product">
<id property="pid" column="pid"/>
<result property="pname" column="pname"/>
<result property="type" column="type"/>
<result property="storage" column="storage"/>
</collection>
</resultMap>
<!--
方式二:嵌套查询:对于每一张关联表单独执行一个sql查询来返关联数据
select c.cid,c.catename from t_category c where c.cid=1;
select p.pid,p.pname,p.price from t_product p where cateid=1 //1 是上一个查询指定的cid的值
-->
<select id="getCategory2" parameterType="int" resultMap="CategoryResultMap2">
select c.cid,c.catename from t_category c where c.cid=#{cid}
</select>
<select id="getProductList" parameterType="int" resultType="com.cheese.pojo.Product">
select p.pid,p.pname,p.price from t_product p where cateid=#{cid}
</select>
<resultMap type="com.cheese.pojo.Category" id="CategoryResultMap2">
<id property="cid" column="cid"/>
<result property="cateName" column="catename"/>
<collection property="productList" ofType="com.cheese.pojo.Product" column="cid" select="getProductList"></collection>
</resultMap>
</mapper>
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>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC" />
<!-- 配置数据库连接信息 -->
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://localhost:3306/base6?useUnicode=true&characterEncoding=UTF8"/>
<property name="username" value="bts" />
<property name="password" value="b111" />
</dataSource>
</environment>
</environments>
<mappers>
<!-- 注册userMapper.xml文件 -->
<mapper resource="com/cheese/mapping/categoryMapper.xml"/>
</mappers>
</configuration>
测试程序:
package com.cheese.test;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import com.cheese.pojo.Category;
public class TestQuery2 {
@Test
public void getCategory(){
SqlSession sqlSession = SessionUtil.getSession();
/**
* com.cheese.mapping.brandMapper.getBrand
* 定位到brandMapper.xml 中 id 为 getBrand 的语句
*/
String getCategoryStr = "com.cheese.mapping.categoryMapper.getCategory";//映射sql的标识字符串
//执行查询,返回Brand对象
Category category = sqlSession.selectOne(getCategoryStr,1);//查询t_category表中id为1的记录
sqlSession.close();
System.out.println(category.getCateName() + " : "
+ category.getProductList().get(0).getPname());//打印Category对象的productList集合的第一个元素
}
@Test
public void getCategory2(){
SqlSession sqlSession = SessionUtil.getSession();
/**
* com.cheese.mapping.brandMapper.getBrand2
* 定位到brandMapper.xml 中 id 为 getBrand2 的语句
*/
String getCategoryStr = "com.cheese.mapping.categoryMapper.getCategory2";//映射sql的标识字符串
//执行查询,返回Brand对象
Category category = sqlSession.selectOne(getCategoryStr,1);//查询t_category表中id为1的记录
sqlSession.close();
System.out.println(category.getCateName() + " -> "
+ category.getProductList().get(0).getPname());//打印Category对象的productList集合的第一个元素
}
}
测试结果: