MyBatis一对多查询

1、创建maven工程，搭建mybatis框架环境

mybatis-config.xml配置文件以及db。properties配置

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <!--    引入外部配置文件-->
    <properties resource="db.properties"/>


<!--    配置日志文件，注意不要写错-->
    <settings>
<!--        标准日志工厂-->
        <setting name="logImpl" value="STDOUT_LOGGING"/>
    </settings>


    <typeAliases>
<!--        1、可以给实体类起别名,实体类少使用-->
<!--        <typeAlias type="pojo.User" alias="User"/>-->
<!--        2、可以给实体类起别名,默认别名为类名首字母小写
            若有注解，则以注解为别名-->
        <package name="pojo"/>
    </typeAliases>


    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
<!--            连接数据库，数据库使用的是mysql8.0-->
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>

    </environments>
<!--注册StudentMapper.xml-->
    <mappers>
        <mapper class="dao.TeacherMapper"/>
    </mappers>


</configuration>
复制代码

数据库使用使用MySQL8.0

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/表名?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC
username=数据库用户名
password=数据库密码
复制代码

pom.xml依赖

  <dependencies>
<!--        mysql驱动-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.11</version>
        </dependency>
<!--mybatis所需jar包-->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.2</version>
        </dependency>

        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>
    </dependencies>
复制代码

2、创建stdent和teacher数据表

CREATE TABLE student (

id int NOT NULL AUTO_INCREMENT,

name varchar(20) ，

tid int NULL DEFAULT NULL,

PRIMARY KEY (id) USING BTREE,

INDEX fktid(tid) USING BTREE,

CONSTRAINT fktid FOREIGN KEY (tid) REFERENCES teacher (id) ON DELETE RESTRICT ON UPDATE RESTRICT

)

CREATE TABLE teacher (

id int NOT NULL AUTO_INCREMENT,

name varchar(20) ,

PRIMARY KEY (id) USING BTREE

)

3、创建对应实体类，学生类

package pojo;


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

    public Student() {
    }

    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 Student(int id, String name) {
        this.id = id;
        this.name = name;
    }
}
复制代码

老师实体类，一个老师对应多个学生

package pojo;

import lombok.Data;

import java.util.List;


public class Teacher {
    private int id;
    private String name;
    //一个老师对应多个学生
    List<Student> students;

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

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

    public Teacher(int id, String name, List<Student> students) {
        this.id = id;
        this.name = name;
        this.students = students;
    }

    public Teacher() {
    }

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


}
复制代码

4、创建TeacherMapper接口

package dao;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import pojo.Teacher;

public interface TeacherMapper {
    //获取老师信息以及该老师对应的所有学生

    //方式一、嵌套查询
    Teacher getTeacher(@Param("id") int id);

    //方式二、子查询
    Teacher getTeacher2(@Param("id") int id);
}
复制代码

5、创建TeacherMapper.xml配置文件，写入sql语句以及配置

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="dao.TeacherMapper">
<!--    嵌套查询-->
    <select id="getTeacher" resultType="Teacher" resultMap="getTeachers">
        select t.id tid,t.name tname,s.name sname,s.id sid
        from teacher t,student s
        where t.id=s.tid and t.id=#{id}
    </select>

    <resultMap id="getTeachers" type="Teacher">
        <result column="tid" property="id"/>
        <result column="tname" property="name"/>
            <!--复杂的属性需要单独处理
            对象使用association
            集合用collection
            此处对象为集合，用collection-->
        <!--        javaType指定属性类型,集合中泛型使用ofType-->
        <collection property="students" ofType="Student">
            <result column="sname" property="name"/>
            <result column="sid" property="id"/>
        </collection>
    </resultMap>

<!--    子查询-->
    <select id="getTeacher2" resultMap="getTeacher2" resultType="Teacher">
        select * from teacher where id=#{id}
    </select>

    <resultMap id="getTeacher2" type="Teacher">
        <collection property="students" javaType="ArrayList" ofType="Student" select="getStudents" column="id"/>
    </resultMap>

    <select id="getStudents" resultType="Student">
        select * from student where tid=#{tid}
    </select>

</mapper>
复制代码

6、创建测试类测试

package dao;

import org.apache.ibatis.session.SqlSession;
import pojo.Student;
import pojo.Teacher;
import until.MyBatisUntil;

import java.util.List;

public class Test {

    @org.junit.Test
    public void test2(){
        SqlSession sqlSession = MyBatisUntil.getSqlSession();

        TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);

        Teacher teacher = mapper.getTeacher(1);

        System.out.println(teacher);

        sqlSession.close();
    }

    @org.junit.Test
    public void test3(){
        SqlSession sqlSession = MyBatisUntil.getSqlSession();

        TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);

        Teacher teacher = mapper.getTeacher2(1);

        System.out.println(teacher);

        sqlSession.close();
    }
}
复制代码

查询成功