one to one - association ; //單對一,使用association one to many -
collection ; //單對多,使用collectionNested results - column is not necessary ,javaType is necessary !
//使用巢狀結果,column 不是必需的,但是JavaType是必需的;nested queries - column is necessary ,javaType is not necessary !
//使用巢狀查詢,column是必需的,JavaType不是必須的,子查詢自定義resultType即可!!
- 表結構:
t_student 表擁有屬性 class_id 對應 t_class表 t_id
【1】更改Classes,新增屬性
public class Classes {
private int id;
private String name;
private Teacher teacher;
private List<Student> list;
...
}
【2】巢狀結果-獲取Classes,Teacher AND list
<select id="getClass4" parameterType="int" resultMap="getClass4Map">
select * from t_class c,t_student s ,t_teacher where c.c_id = s.class_id and c.t_id = t_teacher.t_id and c.c_id = #{id}
</select>
<resultMap type="Classes" id="getClass4Map">
<result property="id" column="c_id" javaType="int" jdbcType="INTEGER"/>
<result property="name" column="c_name" javaType="string" jdbcType="VARCHAR"/>
<!-- here column is not necessary !!!-->
<association property="teacher" column="t_id" javaType="Teacher" >
<id property="id" column="t_id" javaType="int" jdbcType="INTEGER"/>
<result property="name" column="t_name" javaType="string" jdbcType="VARCHAR"/>
</association>
<!-- here, no column !!! -->
<collection property="list" ofType="Student" >
<result property="id" column="s_id" javaType="int" jdbcType="INTEGER"/>
<result property="name" column="s_name" javaType="string" jdbcType="VARCHAR"/>
</collection>
</resultMap>
【3】巢狀查詢–獲取Classes,Teacher AND list
<select id="getClass5" parameterType="int" resultMap="getClass5Map">
select * from t_class c where c.c_id = #{id}
</select>
<select id="getTeacher" parameterType="int" resultType="Teacher">
select t_id id,t_name name from t_teacher where t_id = #{id}
</select>
<select id="getStudent" parameterType="int" resultType="Student">
select s_id id,s_name name from t_student t where t.class_id = #{id}
</select>
<resultMap type="Classes" id="getClass5Map">
<result property="id" column="c_id" javaType="int" jdbcType="INTEGER"/>
<result property="name" column="c_name" javaType="string" jdbcType="VARCHAR"/>
<!-- here,column is necessary !!! 有屬性select = getTeacher-->
<association property="teacher" column="t_id" javaType="Teacher" select="getTeacher">
</association>
<!-- here,column is necessary !!! 有屬性select = getStudent -->
<collection property="list" ofType="Student" select="getStudent" column="c_id">
</collection>
<!-- pay attention to the Query condition of SQL statement t.class_id = #{id} not t.s_id = #{id} !!-->
</resultMap>
【4】Test
- 獲取SqlSessionFactory的工具類:
public static SqlSessionFactory getFactory(){
/* flow the src dir*/
String resource = "mybatis.xml";
/*MybatisUtils.class.getResourceAsStream(resource)----- it's wrong !!!!
* please distinguish the two up and down
* */
InputStream inputStream = MybatisUtils.class.getClassLoader().getResourceAsStream(resource);
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(inputStream);
return factory;
}
-
測試方法
@Test public void testSelect4(){ /*set auto commit ,which equals to the above*/ SqlSession session = MybatisUtils.getFactory().openSession(true); String statement = "com.web.mapper.classMapper.getClass4"; /*return the effect rows*/ Classes classes = session.selectOne(statement, 1); Teacher teacher = classes.getTeacher(); List<Student> list = classes.getList(); System.out.println("result.."+classes+','+classes.getClass()); System.out.println(teacher); System.out.println(list); }
result as follows :
> result..Classes [id=1, list=[Student [id=1, name=stu1], Student [id=2,
> name=stu2], Student [id=3, name=stu3]], name=計算機, teacher=Teacher
> [id=1, name=李明]],class com.web.model.Classes Teacher [id=1, name=李明]
> [Student [id=1, name=stu1], Student [id=2, name=stu2], Student [id=3,
> name=stu3]]