一、场景描述
有两张表：
学生表 t_stu 和 班级表 t_clazz，学生表的cid和班级表的cid关联，
表示一个班级有多个学生
以学生表 t_stu为主表，即“多”，以班级表t_clazz为副表，即“一”，我们下面通过mybatis高级映射实现多对一的情况
二、实现多对一查询的三种方式
准备工作
两个类
学生类 Stu ：多对一，学生表为主表，Stu类里面需要加上班级属性
public class Stu {
private Integer sid;
private String name;
private Clazz clazz;
......
}
班级类 Clazz
public class Clazz {
private Integer cid;
private String name;
......
}
方式一、级联属性映射查询
StuMapper 接口
public interface StuMapper {
public Stu selectBySid(Integer sid);
}
StuMapper.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.powernode.mybatis.mapper.StuMapper">
<!--方式一：级联映射-->
<resultMap id="stuMap" type="Stu">
<id property="sid" column="sid"/>
<result property="name" column="name"/>
<result property="clazz.cid" column="cid"/>
<result property="clazz.name" column="name"/>
</resultMap>
<select id="selectBySid" resultMap="stuMap">
select s.sid,s.name,c.cid,c.name from t_stu s left join t_clazz c on s.cid = c.cid
where s.sid ={sid}
</select>
</mapper>
测试类跑一下
@Test
public void testAdvanceMapping(){
SqlSession sqlSession = SqlSessionUtil.openSqlSession();
StuMapper mapper = sqlSession.getMapper(StuMapper.class);
Stu stu = mapper.selectBySid(1);
System.out.println(stu.toString());
}
运行结果
11:23:30.891 default [main] DEBUG c.p.m.mapper.StuMapper.selectBySid - ==> Preparing: select s.sid,s.name,c.cid,c.name from t_stu s left join t_clazz c on s.cid = c.cid where s.sid = ?
11:23:30.982 default [main] DEBUG c.p.m.mapper.StuMapper.selectBySid - ==> Parameters: 1(Integer)
11:23:31.050 default [main] DEBUG c.p.m.mapper.StuMapper.selectBySid - <== Total: 1
Stu{
sid=1, name='张三', clazz=Clazz{
cid=1000, name='张三'}}
方式二、使用association属性 进行关联查询
StuMapper 接口
public interface StuMapper {
public Stu selectAssociationBySid(Integer sid);
}
StuMapper.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.powernode.mybatis.mapper.StuMapper">
<resultMap id="stuAssociationMap" type="Stu">
<id property="sid" column="sid"/>
<result property="name" column="name"/>
<association property="clazz" javaType="Clazz">
<id property="cid" column="cid"/>
<result property="name" column="name"/>
</association>
</resultMap>
<select id="selectAssociationBySid" resultMap="stuAssociationMap">
select s.sid,s.name,c.cid,c.name
from t_stu s
left join t_clazz c
on s.cid = c.cid
where s.sid ={sid}
</select>
</mapper>
测试类跑一下
@Test
public void testAdvanceMappingAssociation(){
SqlSession sqlSession = SqlSessionUtil.openSqlSession();
StuMapper mapper = sqlSession.getMapper(StuMapper.class);
Stu stu = mapper.selectAssociationBySid(1);
System.out.println(stu.toString());
}
运行结果
11:26:38.082 default [main] DEBUG c.p.m.m.S.selectAssociationBySid - ==> Preparing: select s.sid,s.name,c.cid,c.name from t_stu s left join t_clazz c on s.cid = c.cid where s.sid = ?
11:26:38.128 default [main] DEBUG c.p.m.m.S.selectAssociationBySid - ==> Parameters: 1(Integer)
11:26:38.171 default [main] DEBUG c.p.m.m.S.selectAssociationBySid - <== Total: 1
Stu{
sid=1, name='张三', clazz=Clazz{
cid=1000, name='张三'}}
方式三、分步查询
StuMapper 接口
public interface StuMapper {
public Stu selectBySidStep1(Integer sid);
}
ClazzMapper接口
public interface ClazzMapper {
public Clazz selectByCidStep2(Integer cid);
}
StuMapper.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.powernode.mybatis.mapper.StuMapper">
<!--分步骤查询-->
<resultMap id="selectBySidStepMap" type="Stu">
<id property="sid" column="sid"/>
<result property="name" column="name"/>
<association property="clazz"
select="com.powernode.mybatis.mapper.ClazzMapper.selectByCidStep2"
column="cid"
/>
</resultMap>
<select id="selectBySidStep1" resultMap="selectBySidStepMap">
select s.sid,s.name,s.cid from t_stu s
where s.sid ={sid}
</select>
</mapper>
ClazzMapper.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.powernode.mybatis.mapper.ClazzMapper">
<select id="selectByCidStep2" resultType="Clazz">
select * from t_clazz where cid ={cid}
</select>
</mapper>
测试类跑一下
@Test
public void testAdvanceMappingStep(){
SqlSession sqlSession = SqlSessionUtil.openSqlSession();
StuMapper mapper = sqlSession.getMapper(StuMapper.class);
Stu stu = mapper.selectBySidStep1(1);
System.out.println(stu.getName());
System.out.println(stu.getClazz());
}
运行结果
11:33:51.221 default [main] DEBUG c.p.m.m.StuMapper.selectBySidStep1 - ==> Preparing: select s.sid,s.name,s.cid from t_stu s where s.sid = ?
11:33:51.294 default [main] DEBUG c.p.m.m.StuMapper.selectBySidStep1 - ==> Parameters: 1(Integer)
11:33:51.419 default [main] DEBUG c.p.m.m.StuMapper.selectBySidStep1 - <== Total: 1
11:33:51.426 default [main] DEBUG c.p.m.m.ClazzMapper.selectByCidStep2 - ==> Preparing: select * from t_clazz where cid = ?
11:33:51.426 default [main] DEBUG c.p.m.m.ClazzMapper.selectByCidStep2 - ==> Parameters: 1000(Integer)
11:33:51.441 default [main] DEBUG c.p.m.m.ClazzMapper.selectByCidStep2 - <== Total: 1
Stu{
sid=1, name='张三', clazz=Clazz{
cid=1000, name='高三一班'}}