一、按照配置文件的方式
在Java端mybatis框架里写联查sql语句,通过orm模型解析,这里需要考虑一对一以及一对多的关系,按student和score表举例,student对于score是一对多的关系,score对于student来说是一对一的关系;
1,pojo
创建成绩类和学生类
一对一
2.score作为主表,student作为子表,存在一对一关系映射,使用association标签。
mapper文件:
public interface ScoreMapper {
public List<Score> fundAllScore();
}
<?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="mapper.ScoreMapper">
<resultMap type="Score" id="studentmap">
<result property="sid" column="sid" />
<result property="cname" column="cname" />
<result property="sc" column="sc" />
<association property="student"
javaType="Student">
<id column="sid" property="sid" />
<result property="sname" column="sname" />
<result property="birthday" column="birthday" />
<result property="ssex" column="Ssex" />
<result property="classid" column="classid" />
</association>
</resultMap>
<select id="fundAllScore" resultMap="score_student_map">
select score.*,student.sname,student.birthday,student.Ssex,student.classid
from score LEFT JOIN student on student.Sid = score.sid
</select>
</mapper>
一对多
student作为主表,score作为子表,存在一对多关系映射,使用collection标签
3.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="mapper.StudentMapper">
<resultMap type="Student" id="scoremap">
<result property="sid" column="sid" />
<result property="sname" column="sname" />
<result property="birthday" column="birthday" />
<result property="ssex" column="ssex" />
<result property="classid" column="classid" />
<collection property="slist" ofType="Score">
<id property="sid" column="sid" />
<result property="cname" column="cname" />
<result property="sc" column="sc" />
</collection>
</resultMap>
<select id="fundAllStudent" resultMap="student_score_map">
select student.*,score.cname,score.sc from student
LEFT JOIN score on
student.Sid = score.sid;
</select>
</mapper>
二、使用注解的方式
一对一
score为主表时,存在一对一关系,使用@One注解
public interface ScoreAnnotationMapper {
@Select("select * from score")
@Results({
@Result(column = "sid",property = "sid"),
@Result(column = "cname",property = "cname"),
@Result(column = "sc",property = "sc"),
@Result(property = "student",column ="sid",one = @One(select = "ScoreAnnotationMapper.fundStudentById") )
})
public List<Score> fundAllScore();
@Select("select * from student where sid = #{sid}")
public Student fundStudentById(@Param("sid")int sid);
}
一对多
student作为主表,score作为子表,存在一对多关系映射,使用@Many注解
public interface StudentAnnotationMapper {
@Select("select * from student")
@Results({
@Result(column = "sid",property = "sid"),
@Result(column = "sname",property = "sname"),
@Result(column = "birthday",property = "birthday"),
@Result(column = "ssex",property = "ssex"),
@Result(column = "classid",property = "classid"),
@Result(property = "scList",column ="sid",many = @Many(select = "
StudentAnnotationMapper.fundScoreById") )
})
public List<Student> fundAllStudent();
@Select("select * from score where sid = #{sid}")
public Score fundScoreById(@Param("sid")int sid);
}