当前位置： 首页 > article >正文

Mybatis学习笔记（二）

article 2024/11/3 0:23:02

八、多表联合查询

(一) 多表联合查询概述

在开发过程中单表查询不能满足项目需求分析功能，对于复杂业务来讲，关联的表有几张，甚至几十张并且表与表之间的关系相当复杂。为了能够实业复杂功能业务，就必须进行多表查询，在mybatis中提供了多表查询的结果时映射标签，可以实现表之间的一对一、一对多、多对一、多对多关系映射。

(二) MyBatis实现一对一查询

1. 构建数据库表

        person(个人表) IdCard(身份证表)

CREATE TABLE person(
p_id INT NOT NULL AUTO_INCREMENT,
p_name VARCHAR(30),
PRIMARY KEY(p_id) 
);

#IdCard表
CREATE TABLE idcard(
c_id INT NOT NULL AUTO_INCREMENT,
c_cardno VARCHAR(18),
c_uselife DATE,
c_person_id INT NOT NULL,
PRIMARY KEY(c_id),
FOREIGN KEY(c_person_id) REFERENCES person(p_id),
UNIQUE KEY(c_cardno)

);

INSERT INTO person(p_name) VALUES('张三'),('李四');

INSERT INTO idcard(c_cardno,c_uselife,c_person_id)
VALUES('110112199012127821','2029-10-10',1);
INSERT INTO idcard(c_cardno,c_uselife,c_person_id)
VALUES('120114199911103491','2030-12-01',2);

2.准备项目环境

3.嵌套结果方式查询

 3.1实体类创建

Person

package com.jn.entity;

public class Person {
    private Integer id;
    private String name;

    public Person() {
    }

    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 "Person{" +
                "id=" + id +
                ", name='" + name + '\'' +
                '}';
    }
}

IdCard 

package com.jn.entity;

import java.util.Date;

public class IdCard {
    private Integer id;
    private String cardno;
    private Date useLife;

    public IdCard() {
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getCardno() {
        return cardno;
    }

    public void setCardno(String cardno) {
        this.cardno = cardno;
    }

    public Date getUserLife() {
        return useLife;
    }

    public void setUserLife(Date userLife) {
        this.useLife = userLife;
    }

    @Override
    public String toString() {
        return "IdCard{" +
                "id=" + id +
                ", cardno='" + cardno + '\'' +
                ", userLife=" + useLife +
                '}';
    }
}
3.2编写sql语句

实现查询个人信息时，也要查询个人所对应的身份证信息。

select p.*,c.* from
person p,
idcard c
where p.p_id=c.c_person_id and p.p_id=1;

3.3编写PersonIdCard类
package com.jn.entity;

import java.util.Date;

public class PersonIdCard extends Person{
    private String cardno;
    private Date useLife;

    public String getCardno() {
        return cardno;
    }

    public void setCardno(String cardno) {
        this.cardno = cardno;
    }

    public Date getUseLife() {
        return useLife;
    }

    public void setUseLife(Date useLife) {
        this.useLife = useLife;
    }
}
3.4定义持久层接口

PersonDao

package com.jn.dao;

import com.jn.entity.PersonIdCard;

public interface PersonDao {

    public PersonIdCard getPersonById(int id);
}
3.5定义 PersonDao.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.jn.dao.PersonDao">
    <select id="getPersonById" resultMap="PersonResultMap">
        SELECT p.*,c.* from person p ,idcard c where p.p_id = c.c_person_id and p.p_id = 1;
    </select>
    <resultMap id="PersonResultMap" type="PersonIdCard">
        <id column="p_id" property="id"></id>
        <result column="p_id" property="id"></result>
        <result column="p_name" property="name"></result>
        <result column="c_cardno" property="cardno"></result>
        <result column="c_uselife" property="useLife"></result>
    </resultMap>
</mapper>
3.6创建 PersonTest 测试类
package com.jn.test;

import com.jn.dao.PersonDao;
import com.jn.entity.PersonIdCard;
import com.jn.utils.MyBatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

public class PersonTest {
    @Test
    public void testGetPersonById() throws Exception{
        //获取sqlSession对象
        SqlSession sqlSession = MyBatisUtils.getSession();
        //通过sqlSession获取PersonDao的代理对象
        PersonDao personDao = sqlSession.getMapper(PersonDao.class);

        PersonIdCard personIdCard = personDao.getPersonById(1);
        System.out.println(personIdCard);

        //commit
        sqlSession.close();
        MyBatisUtils.close(sqlSession);
    }
}
3.7测试结果

4.嵌套查询方式

前言

查的到底是什么？

        查的是一个对象Person，这个对象不仅包含了Person里面的属性，同时还包含了一个IdCard对象。然后把两个表里面的信息整合以便于后续处理。

4.1添加idCard属性

在person类里面添加idCard属性 

package com.jn.entity;

public class Person {
    private Integer id;
    private String name;
    private IdCard idCard;

    public Person() {
    }

    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 IdCard getIdCard() {
        return idCard;
    }

    public void setIdCard(IdCard idCard) {
        this.idCard = idCard;
    }

    @Override
    public String toString() {
        return "Person{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", idCard=" + idCard +
                '}';
    }
}
4.2持久层里面添加方法
package com.jn.dao;

import com.jn.entity.Person;
import com.jn.entity.PersonIdCard;

public interface PersonDao {

    public PersonIdCard getPersonById(Integer id);

    //嵌套查询的查询方法
    public Person getPersonById2(Integer id);
}
4.3持久层接口IdCardDao
package com.jn.dao;

import com.jn.entity.Person;

public interface IdCardDao {
    public Person getIdCardByPersonId(Integer id);
}
4.4定义 IdCardDao.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.jn.dao.IdCardDao">

<!--查询符合结果的IdCard对象并返回给命名空间IdCardDao下的getIdCardByPersonId函数，然后配合PersonDao.xml进行数据的联合查询
-->
    <select id="getIdCardByPersonId"  parameterType="int" resultMap="IdCardResult">
        select * from idcard  where c_person_id  = #{id}
    </select>
    <resultMap id="IdCardResult" type="IdCard">
        <id column="c_id" property="id"></id>
        <result column="c_id" property="id"></result>
        <result column="c_cardno" property="cardno"></result>
        <result column="c_uselife" property="useLife"></result>
    </resultMap>
</mapper>
4.5PersonDao.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.jn.dao.PersonDao">

<!--嵌套结果的查询-->
    <select id="getPersonById" resultMap="PersonResultMap">
        SELECT p.*,c.* from person p ,idcard c where p.p_id = c.c_person_id and p.p_id = 1;
    </select>
    <resultMap id="PersonResultMap" type="PersonIdCard">
        <id column="p_id" property="id"></id>
        <result column="p_id" property="id"></result>
        <result column="p_name" property="name"></result>
        <result column="c_cardno" property="cardno"></result>
        <result column="c_uselife" property="useLife"></result>
    </resultMap>


<!--嵌套查询的结果。根据id先查询到Person对象里面的字段信息，然后配合IdCard.xml查询返回的IdCard对象进行结合，然后返回一个getPersonById2函数的Person对象-->
    <select id="getPersonById2" parameterType="int" resultMap="PersonResultMap2">
        select * from person where p_id = #{id}
    </select>
    <resultMap id="PersonResultMap2" type="Person">
        <id column="p_id" property="id"></id>
        <result column="p_name" property="name"></result>

        <!--映射Person的复杂字段idCard对象属性-->
        <association property="idCard" javaType="IdCard" column="p_id" select="com.jn.dao.IdCardDao.getIdCardByPersonId"></association>
    </resultMap>
</mapper>

column:表示取上次查询出来的指定列的值，做为select属性所指定的查询的输入值。
select:表示指定的查询.

4.6加入测试方法
    //测试嵌套查询
    @Test
    public void testNestedQueryById() throws Exception{
        //获取sqlSession对象
        SqlSession sqlSession = MyBatisUtils.getSession();
        //通过sqlSession获取PersonDao的代理对象
        PersonDao personDao = sqlSession.getMapper(PersonDao.class);

        Person person = personDao.getPersonById2(1);
        System.out.println(person);
        MyBatisUtils.close(sqlSession);
    }
4.7测试结果

 到目前为止项目的结构目录

(三)MyBatis实现一对多查询

1.创建数据库表

department(部门表),employee(员工表)同时设定部门和员工表的关系

CREATE TABLE department(
d_id INT NOT NULL AUTO_INCREMENT,
d_name VARCHAR(100),
PRIMARY KEY(d_id)
);

CREATE TABLE employee(
e_id INT NOT NULL AUTO_INCREMENT,
e_name VARCHAR(30),
e_gender VARCHAR(6),
e_age INT,
e_depart_id INT,
PRIMARY KEY(e_id),
FOREIGN KEY(e_depart_id) REFERENCES department(d_id)
);
-- 向 department 表中插入数据
INSERT INTO department (d_name) VALUES ('研发部');
INSERT INTO department (d_name) VALUES ('销售部');
INSERT INTO department (d_name) VALUES ('财务部');
INSERT INTO department (d_name) VALUES ('市场部');
INSERT INTO department (d_name) VALUES ('人力资源部');

-- 向 employee 表中插入数据
INSERT INTO employee (e_name, e_gender, e_age, e_depart_id) VALUES ('张三', '男', 25, 1);
INSERT INTO employee (e_name, e_gender, e_age, e_depart_id) VALUES ('李四', '女', 30, 1);
INSERT INTO employee (e_name, e_gender, e_age, e_depart_id) VALUES ('王五', '男', 28, 2);
INSERT INTO employee (e_name, e_gender, e_age, e_depart_id) VALUES ('赵六', '女', 32, 2);
INSERT INTO employee (e_name, e_gender, e_age, e_depart_id) VALUES ('孙七', '男', 27, 3);
INSERT INTO employee (e_name, e_gender, e_age, e_depart_id) VALUES ('周八', '男', 26, 1);
INSERT INTO employee (e_name, e_gender, e_age, e_depart_id) VALUES ('吴九', '女', 29, 1);
INSERT INTO employee (e_name, e_gender, e_age, e_depart_id) VALUES ('郑十', '男', 31, 2);
INSERT INTO employee (e_name, e_gender, e_age, e_depart_id) VALUES ('钱十一', '女', 24, 3);
INSERT INTO employee (e_name, e_gender, e_age, e_depart_id) VALUES ('刘十二', '男', 33, 4);
INSERT INTO employee (e_name, e_gender, e_age, e_depart_id) VALUES ('陈十三', '女', 28, 4);
INSERT INTO employee (e_name, e_gender, e_age, e_depart_id) VALUES ('杨十四', '男', 30, 5);
INSERT INTO employee (e_name, e_gender, e_age, e_depart_id) VALUES ('胡十五', '女', 27, 5);

​

2.嵌套结果的方式

2.1实体类创建

Department类

package com.jn.entity;

public class Department {
    private int id;
    private String name;

    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;
    }
}

Employee类

package com.jn.entity;

public class Employee {
    private int id;
    private String name;
    private String gender;
    private Integer age;

    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 String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    @Override
    public String toString() {
        return "Employee{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", gender='" + gender + '\'' +
                ", age=" + age +
                '}';
    }
}
2.2编写sql查询语句
select d.*,e.* from department d,employee  e where d.d_id=e.e_depart_id and d.d_id=1;

2.3Department加入List

把Employee属性变为List集合作为Department的属性

package com.jn.entity;

import java.util.List;

public class Department {
    private int id;
    private String name;
    private List<Employee> emps;

    public Department() {
    }

    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<Employee> getEmps() {
        return emps;
    }

    public void setEmps(List<Employee> emps) {
        this.emps = emps;
    }

    @Override
    public String toString() {
        return "Department{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", emps=" + emps +
                '}';
    }
}
2.4持久层DepartmentDao 
package com.jn.dao;

import com.jn.entity.Department;

public interface DepartmentDao {
    public Department getDepartById(Integer id);
}
2.5映射文件
<?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.jn.dao.DepartmentDao">

    <select id="getDepartById" parameterType="int" resultMap="getDepartmentMap">
        select d.*,e.* from department d,employee  e where d.d_id=e.e_depart_id and d.d_id=#{id}
    </select>
    <resultMap id="getDepartmentMap" type="Department">
        <id column="d_id" property="id"></id>
        <result column="d_name" property="name"></result>
        <collection property="emps" ofType="Employee">
            <id column="e_id" property="id"></id>
            <result column="e_name" property="name"></result>
            <result column="e_gender" property="gender"></result>
            <result column="e_age" property="age"></result>
        </collection>
    </resultMap>
</mapper>
2.6测试方法
package com.jn.test;

import com.jn.dao.DepartmentDao;
import com.jn.entity.Department;
import com.jn.utils.MyBatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

public class DepartmentTest {
    @Test
    public void testGetDepartmentById() throws Exception{
        //获取sqlSession对象
        SqlSession sqlSession = MyBatisUtils.getSession();
        //通过sqlSession对象得到DepartmentDao的接口代理对象
        DepartmentDao departmentDao = sqlSession.getMapper(DepartmentDao.class);

        Department department = departmentDao.getDepartById(1);
        System.out.println(department.getName());
        department.getEmps().forEach(System.out::println);

        //close
        sqlSession.close();

    }
}
2.7测试结果

 

3.嵌套查询的方式

3.1定义EmployeeDao
package com.jn.dao;

import com.jn.entity.Employee;

import java.util.List;

public interface EmployeeDao {
    public List<Employee> getEmployeeById(Integer id);
}
3.2定义 EmployeeDao.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.jn.dao.EmployeeDao">

<!--配置employee的查询语句，返回一个Employee对象的集合-->
    <select id="getEmployeeById" parameterType="int" resultMap="getEmployeeResultMap">
        select * from employee where e_depart_id = #{id}
    </select>
    <resultMap id="getEmployeeResultMap" type="Employee">
        <id column="e_id" property="id"></id>
        <result column="e_name" property="name"></result>
        <result column="e_gender" property="gender"></result>
        <result column="e_age" property="age"></result>
    </resultMap>
</mapper>
3.3 DepartmentDao添加
package com.jn.dao;

import com.jn.entity.Department;

public interface DepartmentDao {
    public Department getDepartById(Integer id);
    public Department getDepartById2(Integer id);
}
3.4DepartmentDao.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.jn.dao.DepartmentDao">

    <select id="getDepartById" parameterType="int" resultMap="getDepartmentMap">
        select d.*,e.* from department d,employee  e where d.d_id=e.e_depart_id and d.d_id=#{id}
    </select>
    <resultMap id="getDepartmentMap" type="Department">
        <id column="d_id" property="id"></id>
        <result column="d_name" property="name"></result>
        <collection property="emps" ofType="Employee">
            <id column="e_id" property="id"></id>
            <result column="e_name" property="name"></result>
            <result column="e_gender" property="gender"></result>
            <result column="e_age" property="age"></result>
        </collection>
    </resultMap>

    <select id="getDepartById2" parameterType="int" resultMap="getDepartmentMap2">
        select * from department where d_id=#{id}
    </select>
    <resultMap id="getDepartmentMap2" type="Department">
        <id column="d_id" property="id"></id>
        <result column="d_name" property="name"></result>
        <collection property="emps" ofType="Employee" column="d_id" select="com.jn.dao.EmployeeDao.getEmployeeById">
        </collection>
    </resultMap>
</mapper>
3.5测试方法
    //嵌套查询
    @Test
    public void testGetDepartmentById2() throws Exception{
        //获取sqlSession对象
        SqlSession sqlSession = MyBatisUtils.getSession();
        //通过sqlSession对象得到DepartmentDao的接口代理对象
        DepartmentDao departmentDao = sqlSession.getMapper(DepartmentDao.class);

        Department department = departmentDao.getDepartById2(1);
        System.out.println(department.getName());
        department.getEmps().forEach(System.out::println);

        //close
        sqlSession.close();
    }
3.6测试结果

(三)MyBatis实现多对多查询

1.创建数据库表

CREATE TABLE student(
    sid INT NOT NULL AUTO_INCREMENT,
    sname VARCHAR(30),
    PRIMARY KEY (sid)
);

CREATE TABLE teacher(
    tid INT NOT NULL AUTO_INCREMENT,
    tname VARCHAR(30),
    PRIMARY KEY (tid)
);

CREATE TABLE student_teacher(
    s_id INT NOT NULL,
    t_id INT NOT NULL,
    PRIMARY KEY (s_id,t_id),
    FOREIGN KEY (s_id) REFERENCES student(sid),
    FOREIGN KEY (t_id) REFERENCES teacher(tid)
);
INSERT INTO student(sname) VALUES('张三'),('李四');
INSERT INTO teacher (tname) VALUES('刘老师'),('李老师');
INSERT INTO student(sname) VALUES('王五'),('赵六');
INSERT INTO teacher(tname) VALUES('张老师'),('王老师');
INSERT INTO student(sname) VALUES('孙七'),('周八');
INSERT INTO teacher(tname) VALUES('陈老师'),('杨老师');
INSERT INTO student(sname) VALUES('吴九'),('郑十');
INSERT INTO teacher(tname) VALUES('马老师'),('胡老师');
INSERT INTO student_teacher(s_id,t_id) 

VALUES(1,1),(1,2),(2,1),(3,3),(3,4),(4,3),(4,4),(5,5),(5,6),(6,5),(6,6),(7,7),(7,8),(8,7),(8,8);

2.嵌套结果方式

2.1创建数据模型

Student,Teacher,StudentTeacher

package com.jn.entity;

public class Student {
    private int id;
    private int name;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public int getName() {
        return name;
    }

    public void setName(int name) {
        this.name = name;
    }
}

package com.jn.entity;

public class Teacher {
    private int id;
    private String name;

    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;
    }

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



package com.jn.entity;

public class StudentTeacher {
    private int sid;
    private int tid;

    public int getSid() {
        return sid;
    }

    public void setSid(int sid) {
        this.sid = sid;
    }

    public int getTid() {
        return tid;
    }

    public void setTid(int tid) {
        this.tid = tid;
    }
}
2.2编写多对多的sql语句
select s.*,t.*,st.* from student s,teacher t,student_teacher st where s.sid = st.s_id and st.t_id=t.tid AND s.sid=1;

2.3S中加入List属性
package com.jn.entity;

import java.util.List;

public class Student {
    private int id;
    private String name;
    private List<StudentTeacher> studentTeacherList;

    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<StudentTeacher> getStudentTeacherList() {
        return studentTeacherList;
    }

    public void setStudentTeacherList(List<StudentTeacher> studentTeacherList) {
        this.studentTeacherList = studentTeacherList;
    }

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", name=" + name +
                ", studentTeacherList=" + studentTeacherList +
                '}';
    }
}
2.4ST加入Teacher属性
package com.jn.entity;

public class StudentTeacher {
    private int sid;
    private int tid;
    private Teacher teacher;

    public int getSid() {
        return sid;
    }

    public void setSid(int sid) {
        this.sid = sid;
    }

    public int getTid() {
        return tid;
    }

    public void setTid(int tid) {
        this.tid = tid;
    }

    public Teacher getTeacher() {
        return teacher;
    }

    public void setTeacher(Teacher teacher) {
        this.teacher = teacher;
    }

    @Override
    public String toString() {
        return "StudentTeacher{" +
                "sid=" + sid +
                ", tid=" + tid +
                ", teacher=" + teacher +
                '}';
    }
}
2.5tudentDao编写
package com.jn.dao;

import com.jn.entity.Student;

public interface StudentDao  {
    public Student getStudentById(Integer id);

}
2.6SudentDao.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.jn.dao.StudentDao">

    <select id="getStudentById" parameterType="int" resultMap="getStudentMap">
        select s.*,t.*,st.* from student s,teacher t,student_teacher st where s.sid = st.s_id and st.t_id=t.tid AND s.sid=#{id}
    </select>
    <resultMap id="getStudentMap" type="Student">
        <id column="sid" property="id"></id>
        <result column="sname" property="name"></result>
        <collection property="studentTeacherList" ofType="StudentTeacher">
            <result column="s_id" property="sid"></result>
            <result column="t_id" property="tid"></result>
            <association property="teacher" javaType="Teacher">
                <id column="tid" property="id"></id>
                <result column="tname" property="name"></result>
            </association>
        </collection>
    </resultMap>
</mapper>
2.7测试方法
package com.jn.test;

import com.jn.dao.StudentDao;
import com.jn.entity.Student;
import com.jn.entity.StudentTeacher;
import com.jn.utils.MyBatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.List;

public class StudentTest {

    @Test
    public void testGetStudentById()throws Exception{
        SqlSession sqlSession = MyBatisUtils.getSession();
        StudentDao studentDao = sqlSession.getMapper(StudentDao.class);

        Student student = studentDao.getStudentById(1);

        System.out.println(student.getName());
        List<StudentTeacher> studentTeacherList = student.getStudentTeacherList();
        studentTeacherList.forEach(System.out::println);
    }
}
2.8测试结果

3.嵌套查询方式

3.1TeacherDao
package com.jn.dao;

import com.jn.entity.Teacher;

public interface TeacherDao {
    public Teacher getTeacherById(Integer id);
}
 3.2TeacherDao.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.jn.dao.TeacherDao">
    <select id="getTeacherById" parameterType="int" resultType="Teacher">
        select tid id,tname name from teacher where tid =#{id}
    </select>
</mapper>
3.3StudentTeacherDao
package com.jn.dao;

import com.jn.entity.StudentTeacher;

import java.util.List;

public interface StudentTeacherDao {
    public List<StudentTeacher> getStudentTeacherBySid(Integer id);
}
3.4StudentTeacherDao.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.jn.dao.StudentTeacherDao">
    <select id="getStudentTeacherBySid" parameterType="int" resultMap="getStudentTeacherMap">
        select * from student_teacher where s_id =#{id}
    </select>
    <resultMap id="getStudentTeacherMap" type="StudentTeacher">
        <result column="s_id" property="sid"></result>
        <result column="t_id" property="tid"></result>
        <association property="teacher" column="t_id"  javaType="Teacher"
                     select="com.jn.dao.TeacherDao.getTeacherById"></association>
    </resultMap>
</mapper>
3.5StudentDao添加方法
package com.jn.dao;

import com.jn.entity.Student;

public interface StudentDao  {
    public Student getStudentById(Integer id);
    public Student getStudentById2(Integer id);

}
3.6StudentDao.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.jn.dao.StudentDao">

    <select id="getStudentById" parameterType="int" resultMap="getStudentMap">
        select s.*,t.*,st.* from student s,teacher t,student_teacher st where s.sid = st.s_id and st.t_id=t.tid AND s.sid=#{id}
    </select>
    <resultMap id="getStudentMap" type="Student">
        <id column="sid" property="id"></id>
        <result column="sname" property="name"></result>
        <collection property="studentTeacherList" ofType="StudentTeacher">
            <result column="s_id" property="sid"></result>
            <result column="t_id" property="tid"></result>
            <association property="teacher" javaType="Teacher">
                <id column="tid" property="id"></id>
                <result column="tname" property="name"></result>
            </association>
        </collection>
    </resultMap>



    <!--嵌套查询-->
    <select id="getStudentById2" parameterType="int" resultMap="getStudentMap2">
        select * from student where sid=#{id}
    </select>
    <resultMap id="getStudentMap2" type="Student">
        <id column="sid" property="id"></id>
        <result column="sname" property="name"></result>
        <collection property="studentTeacherList" ofType="StudentTeacher" column="sid"
        select="com.jn.dao.StudentTeacherDao.getStudentTeacherBySid"></collection>
    </resultMap>

</mapper>
3.7测试方法
    //多对多的嵌套测试
    @Test
    public void testGetStudentById2()throws Exception{
        SqlSession sqlSession = MyBatisUtils.getSession();
        StudentDao studentDao = sqlSession.getMapper(StudentDao.class);
        Student student = studentDao.getStudentById2(1);

        System.out.println(student.getName());
        List<StudentTeacher> studentTeacherList = student.getStudentTeacherList();
        studentTeacherList.forEach(System.out::println);

        sqlSession.close();
    }
3.8测试结果

九、延迟加载策略

(一)简介

1.什么是延迟加载?

        延迟加载(lazy load)是(也称为懒加载)关联关系对象默认的加载方式，延迟加载机制是为了避免一些无谓的性能开销而提出来的，所谓延迟加载就是当在真正需要数据的时候，才真正执行数据加载操作。
        延迟加载，可以简单理解为，只有在使用的时候，才会发出sql语句进行查询。

2.为什么要使用延迟加载?

        减少访问数据库的频率，我们要访问的数据量过大时，明显用缓存不太合适，因为内存容量有限为了减少并发量，减少系统资源的消耗。

(二)局部延时加载

        注意：只有在嵌套查询的时候才能用到延时加载

        在mybatis中使用resultMap来实现一对一，一对多，多对多关系的操作。主要是通过 association、collection 实现一对一及一对多映射。association、collection 具备延迟加载功能。

 1.现象演示

在进行查询上述Employee与Department关联信息的时候，正常查询结果：

显示了两条sql语句的查询

然后把department.getEmps().forEach(System.out::println);给注释了查看结果

还是查询了两条sql语句

 2.局部解决

</resultMap>
相关联的查询标签上加 fetchType=”lazy”
fetchType默认值为eager 立即加载,Lazy为延时加载。

然后查看结果：

        发现只执行了一条sql语句

然后不进行注释查看结果 ：

正常执行

（三）全局延时加载

        如果希望所有关联都需要延时加载，可以在mybatis的核心配置文件中进行配置，不用在collection或association中指定。默认全局开启。

1.配置setting

<settings>
<!--开启延时加载开关-->
<setting name="lazyLoadingEnabled" value="true"/>
<!--关闭立即加载，实施按需加载-->
<setting name="aggressiveLazyLoading" value="false"/>
</settings>

2.测试 

测试正常 


http://www.kler.cn/a/376527.html

相关文章：

  • MySQL数据类型中char与varchar、blob与text的区别
  • 软考（中级-软件设计师）计算机网络篇（1101）
  • lanqiaoOJ 3255：重新排队 ← STL list 单链表
  • 如何获取当前数据库版本？
  • CSS例子: 横向排列的格子
  • Python | Leetcode Python题解之第513题找树左下角的值
  • python项目实战——多协程下载美女图片
  • 【uniapp3】分享一个自己写的h5日历组件
  • CSS例子: 横向排列的格子
  • 安装fpm，解决*.deb=＞ *.rpm
  • 六、元素应用CSS的习题
  • 网络编程 UDP编程 Linux环境 C语言实现
  • 在AdaBoost中每轮训练后，为什么错误分类的样本权重会增大e^2αt倍
  • 使用socket库创建简单的客户端和服务器
  • 快速入门kotlin编程（精简但全面版）
  • 树莓派基本设置--8.播放音频和视频
  • 服务器数据恢复—DELL EqualLogic PS6100系列存储简介及如何收集故障信息？
  • 如何在Linux命令行中使用GhatGPT
  • 红队-windows基础篇
  • 基于uniapp微信小程序的校园二手书交易系统
  • jsp页面使用jQuery 给java后端传送数组怎么传送，
  • 数字马力二面面试总结
  • linux驱动—input输入子系统
  • 稀疏检索器除了 TF-IDF、BM25 算法外
  • django请求与响应
  • pdf怎么转换成ppt？有详细的步骤