Application of mybatis under spring boot

Time:2021-10-27

As a front-end developer and pseudo back-end developer, you need to understand some front-end and back-end technologies. In the early days of java development, the SSH framework was mainly used, and I didn’t know much about SSM. I wrote some summaries about springboot in my previous blog. I was deeply attracted by the simplified configuration of springboot. Recently, I have time to continue to improve the springboot series. Previously, we mentioned the creation of HelloWorld of springboot, the construction of three-tier architecture and the application of spring security. After mastering the above three, you can build a simple springboot development framework to practice. Now let’s refine it one by one. The refinement here is not to preach technical points, but to say what is used according to the development needs. Let’s start with the ORM framework mybatis.

1. Create a mybatis project in the spring boot environment

Very simply, open dos and use the spring init command to create a springboot project. As follows:

$ spring init -g=com.briup.apps -a=app03 -d=mybatis,mysql app03
$ cd app03
$ mvn install

The above meaning means that the group ID of Maven project is com.briup.apps, and the artifact ID is app03. It depends on mybatis and MySQL drivers, and the project name is app03. After the project is created, enter the app03 directory to install the dependencies required for app03
In this way, a project of app03 is created, and then the project can be opened through eclipse.

Application of mybatis under spring boot

At this time, if you run the project directly, an error will be reported, indicating that the data source configuration is wrong. This problem was mentioned in the chapter of building a three-tier architecture. The solution is to add the following configuration in the application.properties file

#Configuration of data source related parameters
spring.datasource.driverClassName=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/poll2.0
spring.datasource.username=root
spring.datasource.password=root

#Location of mybatis mapping address
mybatis.mapper-locations= classpath:/mapper/**/*.xml

2. Build project structure

2.1 data modeling

Among the common data models, one to many relationship and many to many relationship are the most commonly used relationships. Therefore, the cases I test here are the cases in the course of introduction to database system of the University, and the relationship between classes, students and courses is as follows:

Application of mybatis under spring boot
Build the table according to the ER diagram above

-- ----------------------------
-- Table structure for tbl_clazz
-- ----------------------------
DROP TABLE IF EXISTS `tbl_clazz`;
CREATE TABLE `tbl_clazz` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for tbl_course
-- ----------------------------
DROP TABLE IF EXISTS `tbl_course`;
CREATE TABLE `tbl_course` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `credit` int(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for tbl_sc
-- ----------------------------
DROP TABLE IF EXISTS `tbl_sc`;
CREATE TABLE `tbl_sc` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` int(11) DEFAULT NULL,
  `course_id` int(11) DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `student_id` (`student_id`),
  KEY `course_id` (`course_id`),
  CONSTRAINT `tbl_sc_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `tbl_student` (`id`) ON DELETE SET NULL,
  CONSTRAINT `tbl_sc_ibfk_2` FOREIGN KEY (`course_id`) REFERENCES `tbl_course` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for tbl_student
-- ----------------------------
DROP TABLE IF EXISTS `tbl_student`;
CREATE TABLE `tbl_student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `gender` varchar(255) DEFAULT NULL,
  `clazz_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `clazz_id` (`clazz_id`),
  CONSTRAINT `tbl_student_ibfk_1` FOREIGN KEY (`clazz_id`) REFERENCES `tbl_clazz` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

2.2 ORM mapping

Build the project structure and create classes according to the table structure
Application of mybatis under spring boot

Because it is a study of mybatis, I focus on beans and Dao, that is, the analysis and testing of the data access layer.

//Class class
public class Clazz {
    private Long id;
    private String name;
    //Corresponding setters, Getters
}

//Courses
public class Course {
    private Long id;
    private String name;
    private Integer credit;
    //Corresponding setters, Getters
}

//Student class
public class Student {
    private Long id;
    private String name;
    private String gender;
    private Long clazz_id;
    //Corresponding setters, Getters
}

//Student course selection
public class StudentCourse {
    private Long id;
    private Long student_id;
    private Long course_id;
    //Corresponding setters, Getters
}

2.3 coding of Dao layer and application of mybatis

In mybatis, we only need to provide mapping interfaces and mapping files, and we can hand over the transaction processing and sqlsession processing to springboot. This way allows us to quickly build the code of the data access layer.

2.3.1 addition, deletion, modification and query of simple single table

package com.briup.apps.app03.dao;

import java.util.List;

import com.briup.apps.app03.bean.Student;

public interface StudentMapper {
    List<Student> findAll();
    
    Student findById(long id);

    void save(Student student);
    
    void update(Student student);
    
    void deleteById(long id);
}

You can see from the official website of mybatis that the corresponding select, insert, update and delete elements need to be provided in XML to realize addition, deletion and query. The ID name is the same as the method name defined in the mapping interface. The resulttype attribute represents the return value type and the parametertype represents the parameter type. For more explanations of other attributes, please refer to the official documents

<?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.briup.apps.app03.dao.StudentMapper">
  <!--  Query all students -- >
  <select id="findAll" resultType="com.briup.apps.app03.bean.Student">
    select * from tbl_student
  </select>

  <!--  Query student information through ID -- >
  <select id="findById" resultType="com.briup.apps.app03.bean.Student">
    select * from tbl_student where id = #{id}
  </select>
  
  <!--  Save student information -- >
  <insert id="save" parameterType="com.briup.apps.app03.bean.Student">
      insert into tbl_student values(null,#{name},#{gender},#{clazz_id})
  </insert>
  
  <!--  Modify student information -- >
  <update id="update" parameterType="com.briup.apps.app03.bean.Student">
      update tbl_student set name = #{name}, gender = #{gender} ,clazz_id = #{clazz_id} where id = #{id}
  </update>
  
  <!--  Delete student information by ID -- >
  <delete id="deleteById" parameterType="long">
      delete from tbl_student where id = #{id}
  </delete>
</mapper>

2.3.2 simple many to one query

Many times, the addition, deletion, modification and query of a single table can not meet the needs of the project. For example, when querying student information, we not only need to get clazz_ For the foreign key ID, we also need to know the class name of the student, so we need to use multi table query.
A student corresponds to a class. In order to map the query structure, we need to redefine a POJO class to represent this many to one relationship.
As follows:

package com.briup.apps.app03.bean.vm;

import com.briup.apps.app03.bean.Clazz;
import com.briup.apps.app03.bean.Student;

public class StudentVM {
    //Student information
    private Student student;
    //Class information corresponding to students
    private Clazz clazz;
    //setters getters    
}
package com.briup.apps.app03.dao.extend;

import java.util.List;

import com.briup.apps.app03.bean.vm.StudentVM;

public interface StudentVMMapper {
    List<StudentVM> findAll();
}
<?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.briup.apps.app03.dao.extend.StudentVMMapper">
  <!--  Query all students -- >
  <select id="findAll" resultMap="studentVMResultType">
    select 
        s.id as s_id,
        s.name as s_name,
        s.gender,
        c.id as c_id,
        c.name as c_name
    from tbl_student as s ,tbl_clazz as c
    where s.clazz_id = c.id
  </select>
  <resultMap type="com.briup.apps.app03.bean.vm.StudentVM" id="studentVMResultType">
      <association property="student" javaType="com.briup.apps.app03.bean.Student">
          <id column="s_id" property="id"/>
          <result column="s_name" property="name"/>
          <result column="gender" property="gender"/>
      </association>
      <association property="clazz" javaType="com.briup.apps.app03.bean.Clazz">
          <id column="c_id" property="id"/>
          <result column="c_name" property="name"/>
      </association>
  </resultMap>
</mapper>

In this case, there is another way to write the mapping file:

<?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.briup.apps.app03.dao.extend.StudentVMMapper">
  <!--  Query all students -- >
  <select id="findAll" resultMap="studentVMResultType">
    select * from tbl_student
  </select>
  
  <resultMap type="com.briup.apps.app03.bean.vm.StudentVM" id="studentVMResultType">
      <association 
          property="student" 
          column="id" 
          javaType="com.briup.apps.app03.bean.Student" 
          select="com.briup.apps.app03.dao.StudentMapper.findById" ></association>
          
      <association 
          property="clazz"
          column="clazz_id"
          javaType="com.briup.apps.app03.bean.Clazz"
          select="com.briup.apps.app03.dao.ClazzMapper.findById"></association>
</resultMap>
  
  
</mapper>

2.3.3 simple one to many query

In the development process, we often encounter one to many queries. For example, when querying a topic, we need to query the information of all options under the topic. When querying a class, we cascade to query all student information under the class.

POJO class

package com.briup.apps.app03.bean.vm;

import java.util.List;

import com.briup.apps.app03.bean.Student;

public class ClazzVM {
    private Long id;
    private String name;
    private List<Student> students;
    //setters getters
}

Mapping interface

package com.briup.apps.app03.dao.extend;

import com.briup.apps.app03.bean.vm.ClazzVM;

public interface ClazzVMMapper {
    ClazzVM findById(long id);
}

Mapping file

<?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.briup.apps.app03.dao.extend.ClazzVMMapper">
  <!--  Query all classes and all students in the class -- >
  <select id="findById" resultMap="clazzVMResultType">
    select * from tbl_clazz where id = #{id}
  </select>
  
  <resultMap type="com.briup.apps.app03.bean.vm.ClazzVM" id="clazzVMResultType">
      <id column="id" property="id"/>
      <result column="name" property="name"/>
      <collection 
          property="students" 
          column="id"
          javaType="ArrayList"
          ofType="com.briup.apps.app03.bean.Student"
          select="com.briup.apps.app03.dao.StudentMapper.findByClazzId"></collection>
  </resultMap>
</mapper>

3. Test

For the test here, I use the unit test function of springboot, as follows

package com.briup.apps.app03.test;

import java.util.List;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import com.briup.apps.app03.bean.Student;
import com.briup.apps.app03.bean.vm.StudentVM;
import com.briup.apps.app03.dao.StudentMapper;
import com.briup.apps.app03.dao.extend.StudentVMMapper;

import net.minidev.json.JSONArray;

@RunWith(SpringRunner.class)
@SpringBootTest
public class StudentMapperTest {
    @Autowired
    private StudentMapper studentMapper;
    @Autowired
    private StudentVMMapper studentVMMapper;
    
    //@Test
    public void save(){
        Student student = new Student();
        Student.setname ("Zhang San");
        Student.setgender ("male");
        studentMapper.save(student);
        System. Out. Println ("-- saved successfully");
    }
    
    //@Test
    public void update(){
        Student student = new Student();
        student.setId(8L);
        Student.setname ("Zhang Sansan");
        Student.setgender ("male");
        studentMapper.update(student);
        System. Out. Println ("-- modification succeeded");
    }
    
    //@Test
    public void delete(){
        studentMapper.deleteById(8L);
        System. Out. Println ("-- deletion succeeded");
    }
    
    @Test
    public void test1(){
        List<Student> list = studentMapper.findAll();
        String json = JSONArray.toJSONString(list);
        System.out.println("student:"+json);
    }
    
    @Test
    public void test2(){
        List<StudentVM> list = studentVMMapper.findAll();
        String json = JSONArray.toJSONString(list);
        System.out.println("studentVM:"+json);
    }
}

4. Code structure

In order to make the code more extensible, I store the class of single table operation and the class of associated operation separately. In order to realize agile development, we can use mybatis generator to realize the automatic generation of POJO classes, mapping files and mapping interfaces for single table operation.

Application of mybatis under spring boot