一对多映射
类定义
一方:
public class UserObj {
private int id;
private String name;
private int age;
private List<ResultObj> resultList = new ArrayList();
}
多方:
public class ResultObj {
private int id;
private int point;
private UserObj user;
}
一对多主表映射
<!--定义类别名-->
<typeAlias alias="users" type="com.map.UserObj"/>
<typeAlias alias="results" type="com.map.ResultObj"/>
<!--定义按用户ID查询用户。里面包括成绩集合-->
<select id="findById" resultMap="user_Result" >
select * from t_user where t_id=#{id}
</select>
定义用户结果集
<!--用户表类属性映射,extends表示可继承userMap所定义的映射 -->
<resultMap id="user_Result" type="user" extends="userMap">
<!--属性名resultList类型为List集合 该集合是根据UserObj的t_id进行ResultObj查询
column是本表要传入的select语句块中的参数,集合查询的定义为resultByUid -->
<association property="resultList" column="t_id" select="resultByUid"/>
</resultMap>
<!--定义按用户ID查询成绩,参数来自用户对象的ID -->
<select id="resultByUid" resultMap="resultData">
select * from t_result where uid=#{uid}
</select>
一对多从表映射
<!--定义按成绩ID查询成绩-->
<select id="findResultById" resultMap="rm" parameterClass="int">
select * from t_result where id=#{id};
</select>
<!-- column为外键列名,select表示引用另一个select标签查询结果填充该属性值- ->
<resultMap class="result" id="rm">
<association property="user" column="userId" select="findUserById"/>
</resultMap>
<!--定义按用户ID查询用户-->
<select id="findUserById" resultType="user" >
select * from t_user where id=#{id};
</select>
一对多从表记录添加
<!--定义按用户ID查询用户-->
<insert id="addResult" parameterType="results">
insert into t_result(pointer,userId) values(#{pointer},#{user.id});
</insert>
多对多映射
操作多对多关系中,中间表需要手动维护
类定义
老师表
public class TeacherBean {
private int id;
private String teacherName;
private List studentList;
}
学生表
public class StudentBean {
private int id;
private String studentName;
private List teacherList;
}
多对多映射查询
定义别名
<typeAlias alias="teacher" type="com.lovo.bean.TeacherBean"/>
<typeAlias alias="student" type="com.lovo.bean.StudentBean"/>
根据id查询老师,返回一个查询结果
<select id="findTeacher" resultMap="tm">
select * from t_teacher where id=#{id};
</select>
<resultMap class="teacher" id="tm">
<association property="studentList" column="id" select="findStudentByTeacher"/>
</resultMap>
联接中间表按老师查询学生集合
<select id="findStudentByTeacher" resultType="student">
SELECT s.* FROM t_student s JOIN t_teacher_student ts ON s.id=ts.studentId WHERE ts.teacherId=#{id};
</select>
多对多添加
定义添加语句,keyProperty指定填充自动增长列属性
<insert id="addTeacher" parameterType="teacher" keyProperty="id" useGeneratedKeys="true">
insert into t_teacher(teacherName) values(#{teacherName});
</insert>
TeacherBean userBean = new TeacherBean ("刘老师");
//添加 session.insert("user.addUser",userBean);
执行添加后,userBean中的id属性自动填充新加记录的主键。
循环添加中间表记录
<insert id="addcenter">
insert into t_teacher_student(teacherId,studentId) values
<foreach collection="students" separator="," item="studentId">
(#{id},#{studentId})
</foreach>
</insert>
删除老师记录和中间表记录
<delete id="deleteTeacher" >
delete from t_teacher_student where teacherId = #{id};
delete from t_teacher where id = #{id};
</delete>