首先要明确一对多和多对一的概念,最常用的例子就是学生和班级、员工和部门,一个员工只能对应一个部门但是一个部门可以对应很多员工,所以部门针对员工就是一对多的,相反的员工针对部门就是多对一。而一对一和多对多就更好理解,例如员工与员工档案就属于一对一,而员工和所掌握的技术属于多对多。两个实体之间的关系都是相对而言。
一对多查询
现有商品表和商品描述表,一件商品对应多个描述,如果我们想在查询一个商品的同时查出该商品的所有描述,可以如下进行
商品类:
package com.mybatis.entity;
import java.util.List;
public class Good {
private Integer goodsId;
private String title;
private String subTitle;
private Float originalCost;
private Float currentPrice;
private Float discount;
private Integer isFreeDelivery;
private Integer categoryId;
private List<GoodsDetail> goodsDetails;
// 省略get/set方法
}
因为一个商品对应的是多条详情,所以GoodsDetail类用集合来表示
Mapper 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="good">
<resultMap id="rmOneToMany" type="com.mybatis.entity.Good">
<id property="goodsId" column="goods_id" />
<collection property="goodsDetails" select="goodsDetails.selectById" column="goods_id"/>
</resultMap>
<select id="selectOneToMany" resultMap="rmOneToMany">
SELECT * FROM t_goods limit 10
</select>
</mapper>
<?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="goodsDetails">
<select id="selectById" parameterType="Integer" resultType="com.mybatis.entity.GoodsDetail">
SELECT * FROM t_goods_detail WHERE goods_id = #{value}
</select>
</mapper>
在Good类查询时,使用resultMap
标签来进行字段关联,这里的List集合用collection
标签来表示,属性property
表示Good类的属性list,属性column
表示与明细表查询的关联字段的字段名,属性select
表示子查询的查询语句的来源。
两张表的关联字段为商品Id(goods_id)
测试类:
package com.mybatis;
import com.mybatis.dto.GoodDTO;
import com.mybatis.entity.Good;
import com.mybatis.entity.GoodsDetail;
import com.mybatis.utils.MyBatisUtils;
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 org.junit.Test;
import java.io.IOException;
import java.io.Reader;
import java.sql.Connection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class TestMyBatis {
@Test
public void testOneToMany() {
SqlSession sqlSession = null;
try {
sqlSession = MyBatisUtils.getSqlSession();
List<Good> list = sqlSession.selectList("good.selectOneToMany");
for (Good good : list)
System.out.println(good.getTitle() + ":" + good.getGoodsDetails().size());
} catch (Exception e) {
e.printStackTrace();
} finally {
MyBatisUtils.close(sqlSession);
}
}
}
由此可以将子查询语句的结果集封装到实体类中,简化了查询语句与方法。
多对一
如果反过来,我们想要查找商品详情所指向的商品时,这时就需要用到多对一的关联查询。
商品详情的实体类:
package com.mybatis.entity;
public class GoodsDetail {
private Integer gdId;
private Integer goodsId;
private String gdPicUrl;
private Integer gdOrder;
private Good good;
// 省略get/set方法
}
Mapper 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="good">
<select id="selectById" parameterType="Integer" resultType="com.mybatis.entity.Good">
SELECT * FROM t_goods WHERE goods_id = #{value}
</select>
</mapper>
<?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="goodsDetails">
<resultMap id="ManyToOne" type="com.mybatis.entity.GoodsDetail">
<id property="gdId" column="gd_id" />
<association property="good" column="goods_id" select="good.selectById"/>
</resultMap>
<select id="selectAll" resultMap="ManyToOne">
SELECT * FROM t_goods_detail limit 10
</select>
</mapper>
同样使用resultMap
标签进行字段关联,当查找某一实体类时可以使用association
标签,属性property
表示GoodsDetail类的good属性,属性column
表示子查询中关联的字段,属性select
表示子查询语句指向的SQL语句,这里指向good命名空间的selectById语句。
测试类:
package com.mybatis;
import com.mybatis.dto.GoodDTO;
import com.mybatis.entity.Good;
import com.mybatis.entity.GoodsDetail;
import com.mybatis.utils.MyBatisUtils;
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 org.junit.Test;
import java.io.IOException;
import java.io.Reader;
import java.sql.Connection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class TestMyBatis {
@Test
public void testManyToOne() {
SqlSession sqlSession = null;
try {
sqlSession = MyBatisUtils.getSqlSession();
List<GoodsDetail> list = sqlSession.selectList("goodsDetails.selectAll");
for (GoodsDetail goodsDetail : list)
System.out.println(goodsDetail.getGdId() + ":" + goodsDetail.getGood().getTitle());
} catch (Exception e) {
e.printStackTrace();
} finally {
MyBatisUtils.close(sqlSession);
}
}
}
由此可以得到每个详情所对应的商品。