mybatis关联查询

笔记

 

多对一的处理

 

创建数据库表

 

create table mybatis.teacher
(
    id   int         not null
        primary key,
    name varchar(30) null
)
    charset = utf8;

create table mybatis.student
(
    id   int         not null
        primary key,
    name varchar(30) null,
    tid  int         null,
    constraint fktid
        foreign key (tid) references mybatis.teacher (id)
)
    charset = utf8;

INSERT INTO mybatis.teacher (id, name) VALUES (1, '陈老师');
INSERT INTO mybatis.teacher (id, name) VALUES (2, 'kk');

 

INSERT INTO mybatis.student (id, name, tid) VALUES (1, '小明', 1);
INSERT INTO mybatis.student (id, name, tid) VALUES (2, '小x', 1);
INSERT INTO mybatis.student (id, name, tid) VALUES (3, '小a', 1);
INSERT INTO mybatis.student (id, name, tid) VALUES (4, '小b', 1);
INSERT INTO mybatis.student (id, name, tid) VALUES (5, '小c', 1);
INSERT INTO mybatis.student (id, name, tid) VALUES (6, 'ii', 2);

 

1.创建maven项目导入依赖

 

<dependencies>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.12</version>
        </dependency>
        <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>
            <scope>test</scope>
        </dependency>

        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-web</artifactId>
            <version>5.1.9.RELEASE</version>
        </dependency>
<!--在build中配置resources，来防止我们资源导出失败的问题-->
    <build>
        <resources>
            <resource>
                <directory>src/main/resources</directory>
                <includes>
                    <include>**/*.properties</include>
                    <include>**/*.xml</include>
                </includes>
            </resource>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.properties</include>
                    <include>**/*.xml</include>
                </includes>
                <filtering>true</filtering>
            </resource>
        </resources>
    </build>

 

 

2.mybatis工具类

public class MybatisUtils {
    private static SqlSessionFactory sqlSessionFactory;

    static {
        try {
            String resource = "mybatis-config.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static SqlSession getSqlSession() {
        return sqlSessionFactory.openSession(true);
    }
}

 

mybatis-config.xml

<?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"/>
        <!--        <setting name="logImpl" value="LOG4J"/>-->
    </settings>

    <!--  给实体类起别名方式2:扫描包  -->
    <typeAliases>
        <package name="com.gg.pojo"/>
    </typeAliases>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${pwd}"/>
            </dataSource>

        </environment>
    </environments>

    <!--  绑定接口  -->
    <mappers>
        <mapper resource="com/gg/dao/TeacherMapper.xml"/>
        <mapper resource="com/gg/dao/StudentMapper.xml"/>
        <!--        <mapper class="com.gg.dao.StudentMapper"/>-->
        <!--        <mapper class="com.gg.dao.TeacherMapper"/>-->
    </mappers>

</configuration>

 

db.properties

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/mybatis?useUnicode=true&characterEncoding=UTF-8&useSSL=false&autoReconnect=true&failOverReadOnly=false&serverTimezone=GMT
username=root
pwd=123456

 

3.实体类

package com.gg.pojo;

import org.apache.ibatis.type.Alias;

//老师实体类
@Alias("teacher")
public class Teacher {
    private int id;
    private String name;

    public Teacher() {
    }

    public Teacher(int id, String name) {
        this.id = id;
        this.name = 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 + '\'' +
                '}';
    }
}



========================================================================




//学生实体类
@Alias("student")
public class Student {
    private int id;
    private String name;
    private Teacher teacher;

    public Student() {
    }

    public Student(int id, String name, Teacher teacher) {
        this.id = id;
        this.name = name;
        this.teacher = 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;
    }

    public Teacher getTeacher() {
        return teacher;
    }

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

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

 

4.mapper

public interface StudentMapper {

    //查询所有的学生信息，以及对应的老师信息
    public List<Student> getStudentList();

    public List<Student> getStudentList2();

}


=========================================


public interface TeacherMapper {
    @Select("select * from teacher where id=#{tid}")
    Teacher getTeacher(@Param("tid") int id);

}

 

5.mapper.xml

StudentMapper.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.gg.dao.StudentMapper">

    <!--  按照结果嵌套处理  -->
    <select id="getStudentList2" resultMap="studentTeacher2">
        select s.id sid,s.name sname,t.name tname,t.id tid from student s,teacher t
        where s.tid=t.id
    </select>

    <resultMap id="studentTeacher2" type="student">
        <result property="id" column="sid"/>
        <result property="name" column="sname"/>
        <association property="teacher" javaType="Teacher">
            <result property="id" column="tid"/>
            <result property="name" column="tname"/>
        </association>
    </resultMap>


    <!--
      思路：
        1.查询所有的学生信息
        2.根据查询出来的学生的tid，寻找对应的老师！
      -->
    <!--  按照查询嵌套处理  -->
    <select id="getStudentList" resultMap="studentTeacher">
        select * from student
    </select>

    <resultMap id="studentTeacher" type="student">
        <result property="id" column="id"/>
        <result property="name" column="name"/>
        <association property="teacher" column="tid" javaType="teacher" select="getTeacher"/>
    </resultMap>

    <select id="getTeacher" resultType="teacher">
        select * from teacher where id=#{tid}
    </select>


</mapper>

 

TeacherMapper.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.gg.dao.TeacherMapper">

</mapper>

 

6.测试

public class ggTest {
    @Test
    public void getTeacher() {
        SqlSession sqlSession = MybatisUtils.getSqlSession();

        TeacherMapper teacherMapper = sqlSession.getMapper(TeacherMapper.class);
        Teacher teacher = teacherMapper.getTeacher(1);
        System.out.println(teacher);

        sqlSession.close();
    }

    @Test
    public void getStudentList() {
        SqlSession sqlSession = MybatisUtils.getSqlSession();

        StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
        List<Student> studentList = studentMapper.getStudentList();

        for (Student student : studentList) {
            System.out.println(student);
        }

        sqlSession.close();
    }

    @Test
    public void getStudentList2() {
        SqlSession sqlSession = MybatisUtils.getSqlSession();

        StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
        List<Student> studentList = studentMapper.getStudentList2();

        for (Student student : studentList) {
            System.out.println(student);
        }

        sqlSession.close();
    }

}

 

7.输出结果

Opening JDBC Connection
Created connection 1074593562.
==>  Preparing: select * from student 
==> Parameters: 
<==    Columns: id, name, tid
<==        Row: 1, 小明, 1
====>  Preparing: select * from teacher where id=? 
====> Parameters: 1(Integer)
<====    Columns: id, name
<====        Row: 1, 陈老师
<====      Total: 1
<==        Row: 2, 小x, 1
<==        Row: 3, 小a, 1
<==        Row: 4, 小b, 1
<==        Row: 5, 小c, 1
<==        Row: 6, ii, 2
====>  Preparing: select * from teacher where id=? 
====> Parameters: 2(Integer)
<====    Columns: id, name
<====        Row: 2, kk
<====      Total: 1
<==      Total: 6
Student{id=1, name='小明', teacher=Teacher{id=1, name='陈老师'}}
Student{id=2, name='小x', teacher=Teacher{id=1, name='陈老师'}}
Student{id=3, name='小a', teacher=Teacher{id=1, name='陈老师'}}
Student{id=4, name='小b', teacher=Teacher{id=1, name='陈老师'}}
Student{id=5, name='小c', teacher=Teacher{id=1, name='陈老师'}}
Student{id=6, name='ii', teacher=Teacher{id=2, name='kk'}}
Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@400cff1a]
Returned connection 1074593562 to pool.

　　

 

一对多

1.创建一个新的项目,修改以下

 

2.mapper

public interface StudentMapper {


}


public interface TeacherMapper {
    //获取老师
//    List<Teacher> getTeacher();

    //获取指定老师下的所有学生及老师的信息
    Teacher getTeacher1(@Param("tid") int id);

    Teacher getTeacher2(@Param("tid") int id);
}

 

3.mapper.xml

studentMapper.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.gg.dao.StudentMapper">

</mapper>

 

teacherMapper.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.gg.dao.TeacherMapper">

    <select id="getTeacher1" resultMap="TeacherStudent">
        select s.id sid,s.name sname,t.name tname,t.id tid
        from student s,teacher t
        where s.tid=t.id and t.id=#{tid}
    </select>

    <resultMap id="TeacherStudent" type="teacher">
        <result property="id" column="tid"/>
        <result property="name" column="tname"/>
        <collection property="students" ofType="student">
            <result property="id" column="sid"/>
            <result property="name" column="sname"/>
            <result property="tid" column="tid"/>
        </collection>
    </resultMap>

    <!--  =====================子查询=================================  -->

    <select id="getTeacher2" resultMap="TeacherStudent2">
        select * from teacher where id=#{tid}
    </select>

    <resultMap id="TeacherStudent2" type="teacher">
        <result property="id" column="id"/>
        <collection property="students" javaType="ArrayList" ofType="student" select="getStudentByTeacherID"
                    column="id"/>
    </resultMap>

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