多表关联查询&动态sql

多表关联查询

嵌套查询

将一次查询拆分成三次

查学生

StudentDao

public interface StudentDao {
   
  List<Student> findStudents();
  List<Student> findStudents1();
}

StudentMapper.xml

<resultMap id="studentmap1" type="Student">
    <id property="id" column="id"></id>
    <result property="number" column="no"></result>
    <result property="name" column="name"></result>
    <result property="gender" column="gender"></result>
    <result property="phone" column="phone"></result>
    <result property="address" column="address"></result>
    <result property="regTime" column="reg_time"></result>
    <association property="grade" javaType="Grade" select="findGradeById" column="gradeid">
    </association>
    <association property="user" javaType="User" select="findUserById" column="userid">
    </association>
</resultMap>
<select id="findStudents1" resultMap="studentmap1">
    SELECT s.id,
   s.no,
   s.name,
   s.gender,
   s.phone,
   s.address,
   s.reg_time,
   s.gradeid,
   s.userid
    FROM student s
</select>
<select id="findGradeById" parameterType="int" resultType="Grade">
        select id,name from grade where id=#{
   id}
</select>
<select id="findUserById" parameterType="int" resultType="User">
    select id,account from user where id=#{
   id}
</select>

TestStudent

public class TestStudent {
   
    public static void main(String[] args) {
   
        SqlSession sqlSession = mybatisUtil.getSqlSession();
        //生产接口的代理对象
        StudentDao studentDao = sqlSession.getMapper(StudentDao.class);
        List<Student> students = studentDao.findStudents1();
        for(Student student:students)
        {
   
            System.out.println(student.getId());
            System.out.println(student.getGrade().getName());
            System.out.println(student.getUser().getAccount());
        }
        sqlSession.close();
    }
}

查年级

创建Grade

package mybatisPro.model;

import java.util.List;

public class Grade {
   
    private int id;
    private String name;
    private List<Student> students;//一个年级对应多个学生

    public  Grade()
    {
   
        System.out.println("Grade无参构造");
    }

    public List<Student> getStudents() {
   
        return students;
    }

    public void setStudents(List<Student> students) {
   
        this.students = students;
    }

    public int getId() {
   
        System.out.println("getId");
        return id;
    }

    public void setId(int id) {
   
        System.out.println("setId");
        this.id = id;
    }

    public String getName() {
   
        System.out.println("getName");
        return name;
    }

    public void setName(String name) {
   
        System.out.println("setName");
        this.name = name;
    }

    @Override
    public String toString() {
   
        return "Grade{" +
                "id=" + id +
                ", name='" + name + '\'' +
                '}';
    }
}

创建GradeDao

package mybatisPro.Dao;

import mybatisPro.model.Grade;

import java.util.List;

public interface GradeDao {
   
        List<Grade> findGrades();//表一起查
    
    	List<Grade> findGrades1