Mybatis多表查询

 在进行多表查询之前我们首先要建立多表关系:

1.创建数据库表:

表admin 管理员表

表student 学生表

表grade 年级表

2.创建各自对应实体类

注:

   Mybatis中涉及关联关系时直接在属性中关联对应对象,而不用像之前我们需要在实体类中定义关联对象的所有属性

2.1 Admin

 package com.ffyc.mybatisdemo.model;
 ​
 public class Admin {
 ​
      private int id;
      private String account;
      private String password;
      private String xb;
      private String adminPhone;
      private Student student;  //此处直接关联Student对象
 ​
     public Student getStudent() {
         return student;
     }
 ​
     public void setStudent(Student student) {
         this.student = student;
     }
 ​
     public String getXb() {
         return xb;
     }
 ​
     public void setXb(String xb) {
         this.xb = xb;
     }
 ​
     public String getAdminPhone() {
         return adminPhone;
     }
 ​
     public void setAdminPhone(String adminPhone) {
         this.adminPhone = adminPhone;
     }
 ​
     public Admin() {
         System.out.println("Admin无参构造");
     }
 ​
     public Admin(String account, String password, String gender) {
         this.account = account;
         this.password = password;
         this.xb = gender;
     }
 ​
     public int getId() {
         return id;
     }
 ​
     public void setId(int id) {
         System.out.println("SetId");
         this.id = id;
     }
 ​
     public String getAccount() {
         return account;
     }
 ​
     public void setAccount(String account) {
         this.account = account;
     }
 ​
     public String getPassword() {
         return password;
     }
 ​
     public void setPassword(String password) {
         this.password = password;
     }
 ​
 ​
     @Override
     public String toString() {
         return "Admin{" +
                 "id=" + id +
                 ", account='" + account + '\'' +
                 ", password='" + password + '\'' +
                 ", xb='" + xb + '\'' +
                 ", adminPhone='" + adminPhone + '\'' +
                 '}';
     }
 }
 ​

2.2 studnet

 package com.ffyc.mybatisdemo.model;
 ​
 public class Student {
 ​
      private Integer id;
      private Integer no;
      private String name;
      private String gender;
      private int gradeid;
      private int adminid;
      private Grade grade;
      private Admin admin;
 ​
     public Integer getId() {
         return id;
     }
 ​
     public void setId(int id) {
         this.id = id;
     }
 ​
     public Integer getNo() {
         return no;
     }
 ​
     public void setNo(int no) {
         this.no = no;
     }
 ​
     public String getName() {
         return name;
     }
 ​
     public void setName(String name) {
         this.name = name;
     }
 ​
     public String getGender() {
         return gender;
     }
 ​
     public void setGender(String gender) {
         this.gender = gender;
     }
 ​
     public int getGradeid() {
         return gradeid;
     }
 ​
     public void setGradeid(int gradeid) {
         this.gradeid = gradeid;
     }
 ​
     public int getAdminid() {
         return adminid;
     }
 ​
     public void setAdminid(int adminid) {
         this.adminid = adminid;
     }
 ​
     @Override
     public String toString() {
         return "Student{" +
                 "id=" + id +
                 ", no=" + no +
                 ", name='" + name + '\'' +
                 ", gender='" + gender + '\'' +
                 ", gradeid=" + gradeid +
                 ", adminid=" + adminid +
                 ", grade=" + grade +
                 ", admin=" + admin +
                 '}';
     }
 }
 ​

2.3 grade

package com.ffyc.mybatisdemo.model;
 ​
 import java.util.List;
 ​
 public class Grade {
 ​
      private int id;
      private String name;
      private List<Student> students;
 ​
     public int getId() {
         return id;
     }
 ​
     public void setId(int id) {
         this.id = id;
     }
 ​
     public String getName() {
         return name;
     }
 ​
     public void setName(String name) {
         this.name = name;
     }
 ​
     public List<Student> getStudents() {
         return students;
     }
 ​
     public void setStudents(List<Student> students) {
         this.students = students;
     }
 ​
     @Override
     public String toString() {
         return "Grade{" +
                 "id=" + id +
                 ", name='" + name + '\'' +
                 ", students=" + students +
                 '}';
     }
 }
 ​

一对多关系查询

1.接口中的方法:

 //一对多关系查询
     List<Grade> findGradelist();

2.mapper.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.ffyc.mybatisdemo.dao.GradeDao">
     <resultMap id="GradeMap" type="Grade">
         <id column="id" property="id"></id>
         <result column="name" property="name"></result>
         <collection property="students" javaType="list" ofType="Student">
             <id column="sid" property="id"></id>
             <result column="sname" property="name"></result>
         </collection>
     </resultMap>
     <select id="findGradelist" resultMap="GradeMap">
         SELECT g.id,g.name,s.id sid,s.name sname
         FROM grade g LEFT JOIN student s
         ON s.gradeid = g.id
     </select>
 </mapper>

注:

此处使用到了resultMap中的新属性:

• collection: 一对多关系结果集映射

• association: 多对一关系结果集映射

3.测试类:

package com.ffyc.mybatisdemo.test;
 ​
 import com.ffyc.mybatisdemo.dao.GradeDao;
 import com.ffyc.mybatisdemo.dao.StudentDao;
 import com.ffyc.mybatisdemo.model.Grade;
 import com.ffyc.mybatisdemo.model.Student;
 import com.ffyc.mybatisdemo.util.MybatisUtil;
 import org.apache.ibatis.session.SqlSession;
 import org.junit.Test;
 ​
 import java.util.List;
 ​
 /*
     多表查询  1对多
  */
 public class TestGrade {
 ​
     @Test
     public void findGrade() {
         SqlSession sqlSession = MybatisUtil.getSqlSession();
         GradeDao GradeDao = sqlSession.getMapper(GradeDao.class);
         List<Grade> grades = GradeDao.findGradelist();
         System.out.println(grades);
         sqlSession.close();
     }
 }
 ​

多对一结果查询

1.接口中的方法:

 //多对一关系查询
      Student findStudentById(int id);

2.mapper.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.ffyc.mybatisdemo.dao.StudentDao">
     
     <sql id="selectStudent">
         SELECT s.id,s.name,s.gender,g.name gname
             FROM student s
             LEFT JOIN grade g
             ON s.gradeid = g.id
             LEFT JOIN admin a
             ON s.adminid = a.id
     </sql>
     <!--自定义结果映射-->
     <resultMap id="StudentMap" type="Student">
         <association property="gender" javaType="Grade">
             <result column="gname" property="name"></result>
         </association>
     </resultMap>
     <select id="findStudentById" resultType="com.ffyc.mybatisdemo.model.Student">
             <include refid="selectStudent"></include>
             where s.id = #{id}
     </select>
     
 </mapper>

3.测试类:

 package com.ffyc.mybatisdemo.test;
 ​
 import com.ffyc.mybatisdemo.dao.AdminDao;
 import com.ffyc.mybatisdemo.dao.StudentDao;
 import com.ffyc.mybatisdemo.dao.StudentDao2;
 import com.ffyc.mybatisdemo.model.Admin;
 import com.ffyc.mybatisdemo.model.Student;
 import com.ffyc.mybatisdemo.util.MybatisUtil;
 import org.apache.ibatis.session.SqlSession;
 import org.junit.Test;
 ​
 import java.util.ArrayList;
 import java.util.List;
 ​
 /*
     多表查询  多对1
  */
 public class TestStudent {
 ​
     @Test
     //单独使用resultMap处理
     public void findStudentlist() {
         SqlSession sqlSession = MybatisUtil.getSqlSession();
         StudentDao studentDao = sqlSession.getMapper(StudentDao.class);
         Student student = new Student();
         List<Student> students = studentDao.findStudentlist(student);
         System.out.println(students);
         sqlSession.close();
     }
 }
 ​