java进阶|Mybatis系列文章(三)表关联查询操作

一,Mybatis框架介绍

MyBatis是支持定制化SQL,存储过程以及高级映射的优秀的持久层框架。MyBatis避免了几乎所有的JDBC代码和手动参数以及获取结果集。
MyBatis可以对配置和原生Map使用简单的XML或注解，将接口和Java的POJOS(Plain Old Java Objects，普通的Java对象)映射成数据库中的记录。

二，项目所依赖的jar信息

      <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.2</version>
        </dependency>


        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>

三，项目的数据库连接配置信息

server:
  port: 8080
spring:
  application:
    name: mybatis-study
  datasource:
    url: jdbc:mysql://localhost:3306/study?serverTimezone=UTC
    username: root
    password: root
    driver-class-name: com.mysql.cj.jdbc.Driver
mybatis:
  mapper-locations: classpath:/mapper/*.xml

四，项目依赖的sql文件语句

Student.sql 学生表
create table if not exists `student`
(
    id         int(11) auto_increment primary key comment '学生id',
    name       varchar(255) not null comment '学生姓名',
    classes_id int(11)
) engine = InnoDb
  charset = utf8;
  
Classes.sql 班级表
create table if not exists `classes`
(
    id         int(11) auto_increment primary key comment '班级id',
    name       varchar(255) not null comment '班级名称',
    teacher_id int(11)
) engine = InnoDb
  charset = utf8;
  
 Teacher.sql
create database study;
use study;
create table if not exists `teacher`
(
    id   int(11) auto_increment primary key comment '班级id',
    name varchar(255) not null comment '班级名称'
) engine = InnoDb charset =utf8;

五，项目所依赖的基础类定义 

(1),Student.class

package com.wpw.mybatisstudy.entity;


import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.experimental.Accessors;


import java.io.Serializable;


/**
 * @author wpw
 */
@AllArgsConstructor
@NoArgsConstructor
@Data
@Builder
@Accessors(chain = true)
public class Student implements Serializable {
    private Integer id;
    private String name;
    private Classes classes;
}

(2)Teacher.class

package com.wpw.mybatisstudy.entity;


import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.experimental.Accessors;


import java.io.Serializable;


/**
 * @author wpw
 */
@AllArgsConstructor
@NoArgsConstructor
@Data
@Builder
@Accessors(chain = true)
public class Teacher implements Serializable {
    private Integer id;
    private String name;
}

(3)Classes.class基础类

package com.wpw.mybatisstudy.entity;


import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.experimental.Accessors;


import java.io.Serializable;
import java.util.List;


/**
 * @author wpw
 */
@AllArgsConstructor
@NoArgsConstructor
@Data
@Builder
@Accessors(chain = true)
public class Classes implements Serializable {
    private Integer id;
    private String name;
    private Teacher teacher;
    private List<Student> studentList;
}

六，项目需要的Mapper操作db的操作类

package com.wpw.mybatisstudy.mapper;


import com.wpw.mybatisstudy.entity.Classes;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;


/**
 * @author wpw
 */
@Mapper
@Repository
public interface ClassesMapper {
    /**
     * 根据班级id查询班级信息
     *
     * @param id 班级id
     * @return 班级信息
     */
    Classes selectClasses(Integer id);


    /**
     * 根据班级id查询班级信息
     *
     * @param id 班级id
     * @return 班级信息
     */
    @Results({
            @Result(property = "id", column = "cid"),
            @Result(property = "name", column = "cname"),
            @Result(property = "teacher", column = "teacher_id", one = @One(select = "com.wpw.mybatisstudy.mapper.TeacherMapper.selectTeacherById"))
    })
    @Select(value = "select c.id cid,c.name cname,c.teacher_id teacher_id,t.id tid,t.name tname from classes c,teacher t where c.teacher_id=t.id and c.id=#{id}")
    Classes selectClassesByAnnotation(Integer id);


    /**
     * 根据班级id查询班级信息
     *
     * @param id 班级id
     * @return 班级信息
     */
    @SelectProvider(type = ClassesSqlProvider.class, method = "selectById")
    @Results(id = "resultMap", value = {
            @Result(property = "id", column = "cid"),
            @Result(property = "name", column = "cname"),
            @Result(property = "teacher", column = "teacher_id", one = @One(select = "com.wpw.mybatisstudy.mapper.TeacherMapper.selectTeacherById"))
    })
    Classes selectClassesByProvider(Integer id);


    /**
     * 根据班级id获取班级信息
     *
     * @param id 班级id
     * @return 班级信息(老师信息 ， 学生信息)
     */
    Classes getUserListByClassesId(Integer id);


    /**
     * 根据班级id获取班级信息
     *
     * @param id 班级id
     * @return 班级信息
     */
    Classes selectUserList(Integer id);
}

package com.wpw.mybatisstudy.mapper;


import org.apache.ibatis.jdbc.SQL;


/**
 * @author wpw
 */
public class ClassesSqlProvider {
    public String selectById(Integer id) {
        SQL sql = new SQL() {{
            SELECT("c.id cid,c.name cname,c.teacher_id teacher_id,t.id tid,t.name tname");
            FROM("classes c,teacher t");
            WHERE("c.teacher_id=t.id and c.id=#{id}");
        }};
        return sql.toString();
    }
}

操作老师类Teacher的Mapper接口信息

package com.wpw.mybatisstudy.mapper;


import com.wpw.mybatisstudy.entity.Teacher;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;


/**
 * @author wpw
 */
@Mapper
@Repository
public interface TeacherMapper {
    /**
     * 根据教师id查询教师信息
     *
     * @param id 教师id
     * @return teacher信息
     */
    @Select(value="select * from teacher where id=#{id}")
    Teacher selectTeacherById(Integer id);
}

整个项目所需要的xml文件进行数据的增删改查操作ClassesMapper.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，namespace的值习惯上设置成包名+sql映射文件名，
这样就能够保证namespace的值是唯一的
 -->
<mapper namespace="com.wpw.mybatisstudy.mapper.ClassesMapper">
    <!--根据班级id查询班级信息(带老师的信息)-->
    <resultMap id="classesResultMap" type="com.wpw.mybatisstudy.entity.Classes">
        <id property="id" column="cid"></id>
        <result property="name" column="cname"></result>
        <association property="teacher" javaType="com.wpw.mybatisstudy.entity.Teacher">
            <id property="id" column="tid"></id>
            <result property="name" column="tname"></result>
        </association>
    </resultMap>


    <select id="selectClasses" parameterType="int" resultMap="classesResultMap">
        select c.id cid, c.name cname, t.id tid, t.name tname
        from classes c,
             teacher t
        where c.teacher_id = t.id
          and c.id = #{id}
    </select>


    <resultMap id="secondResultMap" type="com.wpw.mybatisstudy.entity.Classes">
        <id property="id" column="cid"></id>
        <result property="name" column="cname"></result>
        <association property="teacher" column="teacher_id" select="getTeacher"></association>
    </resultMap>


    <select id="select" parameterType="int" resultMap="secondResultMap">
        select c.id cid, c.name cname c.teacher_id teacher_id
        from classes c
        where c.id = #{id}
    </select>


    <resultMap id="teacherResultMap" type="com.wpw.mybatisstudy.entity.Teacher">
        <id property="id" column="tid"></id>
        <result property="name" column="tname"></result>
    </resultMap>


    <select id="getTeacher" parameterType="int" resultMap="teacherResultMap">
        select t.id tid, t.name tname
        from teacher
        where t.id = #{id}
    </select>


    <select id="getUserListByClassesId" parameterType="int" resultMap="map">
        select c.id         cid,
               c.name       cname,
               c.teacher_id teacher_id,
               t.id         tid,
               t.name       tname,
               s.id         sid,
               s.name       sname,
               s.classes_id classes_id
        from classes c,
             teacher t,
             student s
        where c.teacher_id = t.id
          and c.id = s.classes_id
          and c.id = #{id}
    </select>


    <resultMap id="map" type="com.wpw.mybatisstudy.entity.Classes">
        <id property="id" column="cid"></id>
        <result property="name" column="cname"></result>
        <association property="teacher" column="teacher_id" javaType="com.wpw.mybatisstudy.entity.Teacher">
            <id property="id" column="tid"></id>
            <result property="name" column="tname"></result>
        </association>
        <collection property="studentList" ofType="com.wpw.mybatisstudy.entity.Student">
            <id property="id" column="sid"></id>
            <result property="name" column="sname"></result>
        </collection>
    </resultMap>


    <select id="selectUserList" parameterType="int" resultMap="mapOfUserList">
        select c.id cid, c.name cname, c.teacher_id teacher_id
        from classes c
        where c.id = #{id}
    </select>


    <resultMap id="mapOfUserList" type="com.wpw.mybatisstudy.entity.Classes">
        <id property="id" column="cid"/>
        <result property="name" column="cname"/>
        <association property="teacher" column="teacher_id" javaType="com.wpw.mybatisstudy.entity.Student"
                     select="optionTeacher"/>
        <collection property="studentList" ofType="com.wpw.mybatisstudy.entity.Student" column="cid"
                    select="optionStudent"/>
    </resultMap>


    <select id="optionTeacher" parameterType="int" resultMap="teacherResultMap">
        select t.id tid, t.name tname
        from teacher t
        where t.id = #{id}
    </select>


    <select id="optionStudent" parameterType="int" resultMap="studentMap">
        select s.id sid, s.name sname
        from student s
        where s.classes_id = #{id}
    </select>


    <resultMap id="studentMap" type="com.wpw.mybatisstudy.entity.Student">
        <id property="id" column="sid"></id>
        <result property="name" column="sname"></result>
    </resultMap>
</mapper>

整个项目的测试流程是基于单元测试用例进行测试的，由于比较简单一些，所以这里不做过多的说明了。

package com.wpw.mybatisstudy;


import com.wpw.mybatisstudy.entity.Classes;
import com.wpw.mybatisstudy.mapper.ClassesMapper;
import jdk.nashorn.internal.ir.annotations.Ignore;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;


@SpringBootTest
class MybatisStudyApplicationTests {


    @Test
    void contextLoads() {
    }


    @Autowired
    private ClassesMapper classesMapper;


    @Test
    @Ignore
    public void selectClasses() {
        Classes classes = classesMapper.selectClasses(1);
        System.out.println("classes = " + classes);
    }


    @Test
    @Ignore
    public void selectClassesByAnnotation() {
        Classes classes = classesMapper.selectClassesByAnnotation(1);
        System.out.println("classes = " + classes);
    }


    @Test
    @Ignore
    public void selectClassesByProvider() {
        Classes classes = classesMapper.selectClassesByProvider(1);
        System.out.println("classes = " + classes);
    }


    @Test
    @Ignore
    public void selectClassesBySecond() {
        Classes classes = classesMapper.selectClasses(1);
        System.out.println("classes = " + classes);
    }


    @Test
    @Ignore
    public void getStudentListByClassesId() {
        Classes classes = classesMapper.getUserListByClassesId(1);
        System.out.println("classes = " + classes);
    }


    @Test
    @Ignore
    public void getStudentList() {
        Classes classes = classesMapper.selectUserList(1);
        System.out.println("classes = " + classes);
    }
}

整个项目的搭建和代码过程就结束了，这里贴下项目的gitHub地址，需要代码的可以自己去下载。

gitHub地址:https://github.com/myownmyway/mybatis-study.git

测试数据sql----classes.sql

insert into classes(name, teacher_id)
values ("class_a", 1);
insert into classes(name, teacher_id)
values ("class_b", 2);
##补加外键关联
alter table classes ADD constraint fk_teacher_id FOREIGN KEY (teacher_id) REFERENCES teacher (id);##添加外键约束
alter table  classes drop constraint fk_teacher_id ;##删除外键约束

测试数据sql----teacher.sql

insert into teacher(name) values ('teacher1');
insert into teacher(name) values ('teacher2');

测试数据sql----student.sql

insert into  student(name,classes_id) values('student_A',1);
insert into  student(name,classes_id) values('student_B',1);
insert into  student(name,classes_id) values('student_C',1);
insert into  student(name,classes_id) values('student_D',2);
insert into  student(name,classes_id) values('student_E',2);
insert into  student(name,classes_id) values('student_F',2);

项目结构图如下：