一、一对一
前提:mybatis入门项目搭建完成(https://blog.csdn.net/chlei_/article/details/100187907)。
目录:
表格:
实体类:
public class Scores implements Serializable {
private Integer id;
private Integer score;
private Integer sid;
//从表实体类应该包含一个主表实体类的对象引用
private Student student;
public Student getStudent() {
return student;
}
public void setStudent(Student student) {
this.student = student;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public Integer getScore() {
return score;
}
public void setScore(Integer score) {
this.score = score;
}
public Integer getSid() {
return sid;
}
public void setSid(Integer sid) {
this.sid = sid;
}
@Override
public String toString() {
return "Scores{" +
"id=" + id +
", score=" + score +
", sid=" + sid +
'}';
}
}
public class Student implements Serializable {
private Integer id;
private String name;
private String sex;
private Date birthday;
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 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;
}
@Override
public String toString() {
return "Student{" +
"id=" + id +
", name='" + name + '\'' +
", sex='" + sex + '\'' +
", birthday=" + birthday +
'}';
}
}
接口:
public interface ScoresDao {
/**
* 查询所有分数
* @return
*/
List<Scores> findAll();
}
配置ScoresDao.xml文件:
<?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="com.chenlei.dao.ScoresDao">
<!--定义封装Student和Scores的resultMap-->
<resultMap id="StudentScores" type="com.chenlei.domain.Scores">
<id property="id" column="scId"></id>
<result property="sid" column="sid"></result>
<result property="score" column="score"></result>
<!--一一对应的关系映射:配置封装student的内容-->
<association property="student" column="sid" javaType="com.chenlei.domain.Student">
<id property="id" column="id"></id>
<result property="name" column="name"></result>
<result property="sex" column="sex"></result>
<result property="birthday" column="birthday"></result>
</association>
</resultMap>
<!-- 查询所有 -->
<select id="findAll" resultMap="StudentScores">
select st.*,sc.id as scId ,sc.sid,sc.score from scores sc ,student st where sc.sid=st.id;
</select>
</mapper>
测试类:
@Test
public void findAll() {
//执行查询所有对象方法
List<Scores> scores = scoresDao.findAll();
for (Scores scorez : scores) {
System.out.println("-------------");
System.out.println(scorez);
System.out.println(scorez.getStudent());
}
}
结果:
二、一对多
实体类:
public class Student implements Serializable {
private Integer id;
private String name;
private String sex;
private Date birthday;
//一对多的关系映射:主表实体应该包含从表实体的集合引用
private List<Scores> scores;
public List<Scores> getScores() {
return scores;
}
public void setScores(List<Scores> scores) {
this.scores = scores;
}
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 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;
}
@Override
public String toString() {
return "Student{" +
"id=" + id +
", name='" + name + '\'' +
", sex='" + sex + '\'' +
", birthday=" + birthday +
'}';
}
}
public class Scores implements Serializable {
private Integer id;
private Integer score;
private Integer sid;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public Integer getScore() {
return score;
}
public void setScore(Integer score) {
this.score = score;
}
public Integer getSid() {
return sid;
}
public void setSid(Integer sid) {
this.sid = sid;
}
@Override
public String toString() {
return "Scores{" +
"id=" + id +
", score=" + score +
", sid=" + sid +
'}';
}
}
StudentDao.xml文件:
<?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="com.chenlei.dao.StudentDao">
<!--定义Student的resultMap-->
<resultMap id="studentScoresMap" type="com.chenlei.domain.Student">
<id property="id" column="id"></id>
<result property="name" column="name"></result>
<result property="sex" column="sex"></result>
<result property="birthday" column="birthday"></result>
<!--配置student对象中scores集合的映射-->
<collection property="scores" ofType="com.chenlei.domain.Scores">
<id property="id" column="scid"></id>
<result property="sid" column="sid"></result>
<result property="score" column="score"></result>
</collection>
</resultMap>
<!-- 查询所有 -->
<select id="findAll" resultMap="studentScoresMap">
select * from student st left outer join scores sc on sc.sid = st.id
</select>
</mapper>
接口:
public interface StudentDao {
/**
* 查找所有操作
* @return
*/
List<Student> findAll();
}
测试类:
@Test
public void findAll() {
//执行查询所有对象方法
List<Student> students = studentDao.findAll();
for (Student student : students) {
System.out.println(student);
System.out.println(student.getScores());
}
}
结果:
三、多对多查询
数据库表格:
实体类:
public class Teacher implements Serializable {
private Integer id;
private String name;
private List<Student> students;
public List<Student> getStudents() {
return students;
}
public void setStudents(List<Student> students) {
this.students = students;
}
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;
}
@Override
public String toString() {
return "Teacher{" +
"id=" + id +
", name='" + name + '\'' +
'}';
}
}
public class Student implements Serializable {
private Integer id;
private String name;
private String sex;
private Date birthday;
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 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;
}
@Override
public String toString() {
return "Student{" +
"id=" + id +
", name='" + name + '\'' +
", sex='" + sex + '\'' +
", birthday=" + birthday +
'}';
}
}
接口:
public interface TeacherDao {
/**
* 查询所有分数
* @return
*/
List<Teacher> findAll();
}
TeacherDao.xml:
<?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="com.chenlei.dao.TeacherDao">
<!--定义teacher表的resultMap-->
<resultMap id="teacherMap" type="com.chenlei.domain.Teacher">
<id property="id" column="id"></id>
<result property="name" column="name"></result>
<collection property="students" ofType="com.chenlei.domain.Student">
<id property="id" column="id"></id>
<id property="name" column="name"></id>
<id property="sex" column="sex"></id>
<id property="birthday" column="birthday"></id>
</collection>
</resultMap>
<!-- 查询所有 -->
<select id="findAll" resultMap="teacherMap">
select s.*,t.id as tid, t.name as tname from teacher t left outer join student_teacher st on t.id = st.tid left outer join student s on s.id = st.sid
</select>
</mapper>
测试类:
@Test
public void findAll() {
//执行查询所有对象方法
List<Teacher> teachers = teacherDao.findAll();
for (Teacher teacher: teachers) {
System.out.println("........................");
System.out.println(teacher);
System.out.println(teacher.getStudent());
}
}
最后在多表查询的时候自己也是碰到空指针异常问题,这里记录提示一下:
大家可以看到如果在teache表中增加一条记录如果没有对应的student值那么这个时候查询,就会报空指针异常了,所以总结就是:参照查询表一定得有对应的值。
这个时候查询就来就没问题了。
这里顺带提一下别名,简便我们代码(在SqlMapConfig.xml文件中配置):
结果:
注解实现多表链接:https://blog.csdn.net/chlei_/article/details/100538780