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

码农王同学

发布于 2020-04-27 10:26:56

7570

发布于 2020-04-27 10:26:56

一,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: 8080spring:  application:    name: mybatis-study  datasource:    url: jdbc:mysql://localhost:3306/study?serverTimezone=UTC    username: root    password: root    driver-class-name: com.mysql.cj.jdbc.Drivermybatis:  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.sqlcreate 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@Repositorypublic 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@Repositorypublic 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;
@SpringBootTestclass 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);

项目结构图如下：