一,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);
项目结构图如下: