7.1比如根据班级id查询班级信息(带老师的信息)一对一或多对一
way1:SQL语句中使用联表查询
*实体类中需要添加属性(数据类型为实体类名)
*映射文件中使用association标签(属性:property(实体类中添加的属性名)、javaType(添加的属性所在的实体类的全类名))
1 <select id="getClazz" resultMap="myMap"> 2 select * from class c,teacher t where c.teacher_id=t.t_id and c_id=#{cid} 3 </select> 4 <resultMap type="com.zhiyou.clg.bean.Clazz" id="myMap"> 5 <id column="c_id" property="id"/> 6 <result column="c_name" property="name"/> 7 <result column="teacher_id" property="teacherid"/> 8 <association property="teacher" javaType="com.zhiyou.clg.bean.Teacher"> 9 <id column="t_id" property="id"/> 10 <result column="t_name" property="name"/> 11 </association> 12 </resultMap>
way2:嵌套查询(查询的结果为其他查询的条件)
*实体类中需要添加属性(数据类型为实体类名)
*映射文件中使用association标签(属性:property、javaType(查询到的结果,此时作为查询条件)、 column、select(另一映射的语句的id,即另一select标签的id))
1 <select id="getClazz" resultMap="myMap2"> 2 select * from class where c_id=#{cid} 3 </select> 4 <select id="getTeacher" parameterType="int" resultType="com.zhiyou.clg.bean.Teacher"> 5 select t_id id,t_name name from teacher where t_id=#{tid} 6 </select> 7 <resultMap type="com.zhiyou.clg.bean.Clazz" id="myMap2"> 8 <id column="c_id" property="id"/> 9 <result column="c_name" property="name"/> 10 <result column="teacher_id" property="teacherid"/> 11 <association property="teacher" javaType="com.zhiyou.clg.bean.Teacher" 12 column="teacher_id" select="getTeacher"> 13 <id column="t_id" property="id"/> 14 <result column="t_name" property="name"/> 15 </association> 16 </resultMap>
7.2比如根据班级id查询班级信息(带班里学生的信息)一对多
way1:SQL语句中使用联表查询
*实体类中添加属性(数据类型为集合,泛型为实体类名)
*映射文件中使用collection标签(属性:property(添加的属性名)、ofType(集合泛型的实体 类的全类名))
1 <select id="getClazz" resultMap="myMap3"> 2 select *from class c,teacher t,student s where c.c_id=s.class_id and c.teacher_id=t.t_id and c.c_id=#{cid} 3 </select> 4 <resultMap type="com.zhiyou.clg.bean.Clazz" id="myMap3"> 5 <id column="c_id" property="id"/> 6 <result column="c_name" property="name"/> 7 <result column="teacher_id" property="teacherid"/> 8 <association property="teacher" javaType="com.zhiyou.clg.bean.Teacher"> 9 <id column="t_id" property="id"/> 10 <result column="t_name" property="name"/> 11 </association> 12 <collection property="students" ofType="com.zhiyou.clg.bean.Student"> 13 <id column="s_id" property="id"/> 14 <result column="s_name" property="name"/> 15 <result column="class_id" property="cid"/> 16 </collection> 17 </resultMap> -->
way2:嵌套查询(查询的结果为其他查询的条件)
*实体类中添加属性(数据类型为集合,泛型为实体类名)
*映射文件中使用collection标签(属性:property、ofType、column、select)
1 <select id="getClazz" resultMap="myMap4"> 2 select * from class where c_id=#{cid} 3 </select> 4 <select id="getTeacher" parameterType="int" resultType="com.zhiyou.clg.bean.Teacher"> 5 select t_id id,t_name name from teacher where t_id=#{tid} 6 </select> 7 <select id="getStudent" parameterType="int" resultType="com.zhiyou.clg.bean.Student"> 8 select s_id id,s_name name,class_id cid from student where class_id=#{cid} 9 </select> 10 <resultMap type="com.zhiyou.clg.bean.Clazz" id="myMap4"> 11 <id column="c_id" property="id"/> 12 <result column="c_name" property="name"/> 13 <result column="teacher_id" property="teacherid"/> 14 <association property="teacher" javaType="com.zhiyou.clg.bean.Teacher" 15 column="teacher_id" select="getTeacher"> 16 <id column="t_id" property="id"/> 17 <result column="t_name" property="name"/> 18 </association> 19 <collection property="students" ofType="com.zhiyou.clg.bean.Student" 20 column="c_id" select="getStudent"> 21 <id column="s_id" property="id"/> 22 <result column="s_name" property="name"/> 23 <result column="class_id" property="cid"/> 24 </collection> 25 </resultMap>