专栏首页后端Coderjava进阶|Mybatis系列文章(三)表关联查询操作

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: 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);

项目结构图如下:

本文分享自微信公众号 - WwpwW(gh_245290c1861a),作者:后端Coder

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2020-04-14

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • java进阶|MyBatis系列文章(七)多表查询操作

    以上jar包依赖主要是mysql连接,支持mybatis操作以及简化get/set方法的lombok包。

    后端Coder
  • java进阶|MyBatis系列文章(五)注解版批量操作的增删改查

    六,由于这里都是基于自己的测试操作,写法上不满足正规的流程,比如说这里直接在controller层调用了userMapper的方法操作数据库,正常的应该还有一个...

    后端Coder
  • java进阶|基于mybatis思考一些事情

    没什么好介绍这个框架的,一款半自动化sql的关系映射框架,之所以会写这篇文章还是为了标注一下自己在写CRUD操作过程中的一些思考和理解。

    后端Coder
  • 大型项目技术栈第三讲 ztree的使用

    zTree 是一个依靠 jQuery 实现的多功能 “树插件”。优异的性能、灵活的配置、多种功能的组合是 zTree 最大优点。

    易兮科技
  • R语言 | 根据数据框的顺序进行筛选

    这里有两个数据框,两者有相同的列(ID),这里想把第一个数据框,按照第二个数据框的ID列进行提取,顺序和第二个数据框一致。

    邓飞
  • 无限级子商户的查询优化方法

    A 有 2 个直接下级B、C,    B有2个直接下级D、E,    C有2个直接下级F、G

    宣言言言
  • 那些可以绕过WAF的各种特性

    在攻防实战中,往往需要掌握一些特性,比如服务器、数据库、应用层、WAF层等,以便我们更灵活地去构造Payload,从而可以和各种WAF进行对抗,甚至绕过安全防御...

    Bypass
  • 分表查询统计的一个具体案例

    问题描述 mysql数据库在数据量较大的情况下,对数据表进行水平分表,按照年份,如下:

    后端技术探索
  • 分表查询统计的一个具体案例

    问题描述 mysql数据库在数据量较大的情况下,对数据表进行水平分表,按照年份,如下:

    后端技术探索
  • java进阶|MyBatis系列文章(七)多表查询操作

    以上jar包依赖主要是mysql连接,支持mybatis操作以及简化get/set方法的lombok包。

    后端Coder

扫码关注云+社区

领取腾讯云代金券