Mybatis里面的一对多,一对一查询
围绕以下两个表中的数据展开的查询,两个表里面的学号将这个表里面的信息连接起来了
一个学生选了还几门课,这叫一对多,但每个学号只代表的是一个学生,这叫一对一
model
public class Student { //一个学生每门课程有一个成绩,所以是一对多
private Integer id;
private String name;
private List<Cour> courlist;
private String sex;
private Date birthday;
private String major;
private String note;
public Student() {
super();
}
public Student(Integer id, String name, List<Cour> courlist, String sex, Date birthday, String major, String note) {
super();
this.id = id;
this.name = name;
this.courlist = courlist;
this.sex = sex;
this.birthday = birthday;
this.major = major;
this.note = note;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public List<Cour> getCourlist() {
return courlist;
}
public void setCourlist(List<Cour> courlist) {
this.courlist = courlist;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public String getMajor() {
return major;
}
public void setMajor(String major) {
this.major = major;
}
public String getNote() {
return note;
}
public void setNote(String note) {
this.note = note;
}
@Override
public String toString() {
return "Student [id=" + id + ", name=" + name + ", courlist=" + courlist + ", sex=" + sex + ", birthday="
+ birthday + ", major=" + major + ", note=" + note + "]";
}
}
public class Cour { //由于每个学生有好几门课程,每门课程都会有一个成绩,每个成绩对应一个学生,一对一
private Integer id;
private String stu_code;
private String cour_code;
private Integer degree;
private Student student;
public Cour() {
super();
}
public Cour(Integer id, String stu_code, String cour_code, Integer degree, Student student) {
super();
this.id = id;
this.stu_code = stu_code;
this.cour_code = cour_code;
this.degree = degree;
this.student = student;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getStu_code() {
return stu_code;
}
public void setStu_code(String stu_code) {
this.stu_code = stu_code;
}
public String getCour_code() {
return cour_code;
}
public void setCour_code(String cour_code) {
this.cour_code = cour_code;
}
public Integer getDegree() {
return degree;
}
public void setDegree(Integer degree) {
this.degree = degree;
}
public Student getStudent() {
return student;
}
public void setStudent(Student student) {
this.student = student;
}
@Override
public String toString() {
return "Cour [id=" + id + ", stu_code=" + stu_code + ", cour_code=" + cour_code + ", degree=" + degree
+ ", student=" + student + "]";
}
}
映射文件
首先进行的是一对一的查询,根据stu_cour表里面的学号查询student表里面的内容
CourMapper.xml
<mapper namespace="com.mapper.CourMapper">
<!-- 对象级联 --><!-- 以下都是一对一的关系,通过stu_cour里面的学号,查找student里面的信息 -->
<!-- 第三种 --><!-- 把查询出来的课程表结果放进courlist这个变量里面 --><resultMap type="Cour" id="CourResult2">
<!-- 有时获取不到主键的时候,写一个<id/> -->
<id property="id" column="ID"/>
<association property="student" column="STU_CODE" select="com.mapper.StudentMapper.selectStuByCode"></association>
</resultMap> <!-- 先把学生表里面的那个学号列查出,column里面写查出的student学号那一列CODE,
property为student表里面的变量名,然后作为参数传进selectCourByCode -->
<select id="selectCour" resultMap="CourResult2">
<!-- 第三种方式: --><!-- 直接通过查询学生表,从而查出课程表的内容 --> select * from stu_cour
</select>
</mapper>
还有一种方式
<mapper namespace="com.mapper.StudentMapper">
<resultMap type="Cour" id="courResult"> <!-- type写的是实体类,id是自己定义的名字 -->
<id property="id" column="ID"/> <!-- 查出来的表里面的主键,
property里面写的是stu_cour里面的实体类的变量名,column里面写的是数据库表里面的列名 -->
<result property="stu_code" column="STU_CODE"/><!-- 表里面的其他列名 -->
<result property="cour_code" column="COUR_CODE"/>
<result property="degree" column="DEGREE"/>
<result property="student.id" column="ID"/> <!-- student实体类变量直接就是.取出来的变量名 -->
<result property="student.name" column="NAME"/>
<result property="student.sex" column="SEX"/>
<result property="student.birthday" column="BIRTHDAY"/>
<result property="student.major" column="MAJOR"/>
<result property="student.note" column="NOTE"/>
</resultMap>
<select id="selectCour" resultMap="courResult">
select * from student s,stu_cour st where s.code = st.stu_code
</select>
</mapper>
另一种
<mapper namespace="com.mapper.StudentMapper">
<resultMap type="Student" id="stuResult">
<id property="id" column="ID"/>
<result property="name" column="NAME"/>
<result property="sex" column="SEX"/>
<result property="birthday" column="BIRTHDAY"/>
<result property="major" column="MAJOR"/>
<result property="note" column="NOTE"/>
</resultMap>
<resultMap type="Cour" id="courResult"> <!-- type写的是实体类,id是自己定义的名字 -->
<id property="id" column="ID"/> <!-- 查出来的表里面的主键,
property里面写的是stu_cour里面的实体类的变量名,column里面写的是数据库表里面的列名 -->
<result property="stu_code" column="STU_CODE"/><!-- 表里面的其他列名 -->
<result property="cour_code" column="COUR_CODE"/>
<result property="degree" column="DEGREE"/>
<association property="student" resultMap="stuResult" />
<select id="selectCour" resultMap="courResult">
select * from student s,stu_cour st where s.code = st.stu_code
</select>
</mapper>
CourMapper.java
public interface CourMapper {
List<Cour> selectCour(); /*一对一*/
}
StudentMapper.java
public interface StudentMapper {
Student selectStuByCode(String code); //根据学号查询信息
}
StudentMapper.xml
<mapper namespace="com.mapper.StudentMapper">
<select id="selectStuByCode" resultType="Student"> <!-- 一对一 -->
select * from student s where s.code = #{code}
</select>
</mapper>
一对多的查询方式,通过student表里面的学号查询各科目的成绩
studentMapper.xml
<mapper namespace="com.mapper.StudentMapper">
<!-- 一对多,通过student表里面的学号,查出所有的学生信息 -->
<resultMap type="Student" id="StuResult">
<collection property="courlist" column="CODE" select="com.mapper.CourMapper.selectCourByCode"></collection>
</resultMap>
<select id="selectStuByCode" resultMap="StuResult">
select * from student s where s.code = #{code}
</select>
</mapper>
studentMapper.java
public interface StudentMapper {
Student selectStuByCode(String code); //根据学号查询信息
}
CourMapper.java
public interface CourMapper {
List<Cour> selectCourByCode(String stu_code); //一对多的情况使用
}
CourMapper.xml
<mapper namespace="com.mapper.CourMapper">
<select id="selectCourByCode" resultType="Cour">
select * from stu_cour sc where sc.stu_code = #{stu_code}
</select>
</mapper>
Junitl 测试
class Junit { //测试
private SqlSession sqlSession;
private StudentMapper stuMapper;
private CourMapper courMapper;
@Test
void test() {
/*List<Cour> list = courMapper.selectCour(); 一对一条件下查询用的
for (Cour cour : list) {
System.out.println(cour);
}*/
Student s = stuMapper.selectStuByCode("stu-1011"); //一对多查询用的
System.out.println(s);
}
@BeforeEach
void setUp() throws Exception {
sqlSession = MyBatisUtil.getSqlSession();
stuMapper = sqlSession.getMapper(StudentMapper.class);
courMapper = sqlSession.getMapper(CourMapper.class);
}
@AfterEach
void tearDown() throws Exception {
sqlSession.commit();
sqlSession.close();
}
}
