本文的商品表和商品详情表结构:数据库表设计(实现存储产品的多种规格组合)-CSDN博客
说明一下一对多和多对一:
在本文中,一个商品实体拥有多个商品详情,一个商品详情只归属于一个商品实体。
因此,当主表为商品实体时,需要连接商品详情表进行多表查询时,在resultmap中需要使用collection标签将"多"个商品详情进行拆卸 ofType里写集合中泛型的类型 column中填连接两个表的字段
当主表为商品详情时,需要连接商品实体表进行多表查询时,在resultmap中需要使用association标签将"一"个商品实体联系起来 javaType中写与之联系的实体类 column中填连接两个表的字段
Mybatis实现多表查询:
一对多:
mapper接口:
public List<Product> selectAllProductAndDetails();
mapper配置文件:
<mapper namespace="com.example.testshop.mapper.ProductMapper">
<resultMap id="selectAllProductAndDetailsMap" type="com.example.testshop.entity.Product">
<id property="pid" column="pid" />
<result property="pname" column="pname" />
<result property="sid" column="sid"/>
<collection property="details" ofType="com.example.testshop.entity.ProductDetail">
<id property="pdid" column="pdid"/>
<result property="pid" column="pid"/>
<result property="groupid" column="groupid"/>
<result property="price" column="price"/>
<result property="introduce" column="introduce"/>
<result property="inventory" column="inventory"/>
<result property="status" column="status"/>
<result property="sellNum" column="sellnum"/>
</collection>
</resultMap>
<!-- 一对多-->
<select id="selectAllProductAndDetails" resultMap="selectAllProductAndDetailsMap">
select * from product p left join product_detail pd on p.pid=pd.pid
</select>
多对一:
mapper接口:
public List<ProductDetail> selectAllDetailsAndProduct();
mapper配置文件:
<resultMap id="selectAllDetailsAndProduct" type="com.example.testshop.entity.ProductDetail">
<id property="pdid" column="pdid"/>
<result property="pid" column="pid"/>
<result property="groupid" column="groupid"/>
<result property="price" column="price"/>
<result property="introduce" column="introduce"/>
<result property="inventory" column="inventory"/>
<result property="status" column="status"/>
<result property="sellNum" column="sellnum"/>
<association property="product" javaType="com.example.testshop.entity.Product">
<id property="pid" column="pid" />
<result property="pname" column="pname" />
<result property="sid" column="sid"/>
</association>
</resultMap>
<select id="selectAllDetailsAndProduct" resultMap="selectAllDetailsAndProduct">
select * from product_detail pd left join product p on pd.pid=p.pid
</select>
测试代码:
@Test
public void testSelectAll(){
// List<Product> products = productMapper.selectAll();
// System.out.println(products);
// 一对多
List<Product> products = productMapper.selectAllProductAndDetails();
products.forEach(p->{
System.out.println(p);
});
// 多对1
List<ProductDetail> productDetails = productDetailMapper.selectAllDetailsAndProduct();
productDetails.forEach(pd->{
System.out.println(pd);
});
}
测试结果:
利用分布查询实现多表查询(只演示一对多):
要实现的效果:
select * from product p left join product_detail pd on p.pid=pd.pid;
mapper接口:
ProductMapper
public Product selectProductAndDetailsByIdStepOne(@Param("pid") int pid);
ProductDetailMapper
public ProductDetail selectProductAndDetailsByIdStepTwo(@Param("pid") int pid);
mapper映射文件:
ProductMapper.xml
<resultMap id="selectProductAndDetailsByIdStep" type="com.example.testshop.entity.Product">
<id property="pid" column="pid" />
<result property="pname" column="pname" />
<result property="sid" column="sid"/>
<!-- 这里查出来的detail成员的赋值要通过ProductDetailMapper.selectProductAndDetailsByIdStepTwo-->
<collection property="details" select="com.example.testshop.mapper.ProductDetailMapper.selectProductAndDetailsByIdStepTwo" column="pid">
<id property="pdid" column="pdid"/>
<result property="pid" column="pid"/>
<result property="groupid" column="groupid"/>
<result property="price" column="price"/>
<result property="introduce" column="introduce"/>
<result property="inventory" column="inventory"/>
<result property="status" column="status"/>
<result property="sellNum" column="sellnum"/>
</collection>
</resultMap>
<select id="selectProductAndDetailsByIdStepOne" resultMap="selectProductAndDetailsByIdStep">
select * from product where pid=#{pid}
</select>
ProductDetailMapper.xml
<select id="selectProductAndDetailsByIdStepTwo" resultType="com.example.testshop.entity.ProductDetail">
select * from product_detail where pid = #{pid}
</select>
就是把他拆成了两个sql
开启延时加载(懒加载):
1.导包
2.写配置文件
<settings>
<!-- 懒加载 -->
<setting name="lazyLoadingEnabled" value="true"/>
<!-- 激进的懒加载 -->
<setting name="aggressiveLazyLoading" value="false"/>
</settings>
测试上面的分布查询:
测试代码:
@Test
public void testSelectByStep() {
//一对多
Product product = productMapper.selectProductAndDetailsByIdStepOne(1);
System.out.println(product.getPname());
System.out.println("==================");
System.out.println(product.getDetails());
}
测试结果:
开启激进的懒加载: <setting name="aggressiveLazyLoading" value="true"/>
测试结果:
横线前面仍调用了step2的sql语句