PS: Your state depends on your state of mind. If you want to stop being anxious, get your life rhythm in order.

The previous articles explored interface development, Thymeleaf templates, common syntax, template layouts, project internationalization, JDBC, and more. You can read the previous articles before reading this article:

  • Developing an interface
  • Getting started with the Thymeleaf template
  • Thymeleaf Common syntax
  • Thymeleaf template layout
  • Project internationalization
  • JDBC operation database

MyBatis is an excellent persistence layer framework. MyBatis uses XML or annotations to configure and map POJOs to records in the database.

  1. MyBatis workflow
  2. Dependencies and Configuration
  3. @ Mapper and @ MapperScan
  4. Entity class
  5. Mapper configuration file
  6. Mapper interfaces
  7. Mapper Mapping file
  8. Use of collection tags
  9. Multi-data Source Configuration
  10. The test results
  11. MyBatis is configured using annotations

MyBatis workflow

The working process of MyBatis is shown below:

  1. Read the mybatis-config. XML configuration file;
  2. Load Mapper mapping file or corresponding annotation content, which defines the corresponding SQL statement;
  3. Create a session factory based on the configuration informationSqlSessionFactory;
  4. Created from the session factorySqlSession, which contains all the methods needed to execute SQL;
  5. createExecutorExecutor, used to execute SQL statements in creating session factoriesSqlSessionFactory“Will create oneExecutor, its default executor type isExecutorType.SIMPLE;
  6. MappedStatementObject, which isExecutorParameters in the executor method are mainly the encapsulation of mapping information in Mapper XML file.
  7. Input parameter mapping;
  8. Output parameter mapping.

Dependencies and Configuration

Create a Spring Boot project and add MyBatis and MySQL driver dependencies to build.gradle as follows:

dependencies {
    // ...
    // myBaits
    // http://mybatis.org/spring-boot-starter/mybatis-spring-boot-autoconfigure/index.html
    implementation 'org. Mybatis. Spring. The boot: mybatis - spring - the boot - starter: 2.1.1'
    / / mysql driver
    runtime("mysql:mysql-connector-java")
    // ...
}
Copy the code

Then configure the database connection parameters and MyBatis configuration in the application. Properties file of the project as follows:

# database username spring. The datasource. The username = # root database password spring. The datasource. Password = admin # JDBC Driver spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
# JDBC URL
spring.datasource.url=jdbc:mysql://localhost:3306/db_student? serverTimezone=Asia/Shanghai
#spring.datasource.url=jdbc:mysql://localhost:3306/db_student? serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=trueCheck the status of the MyBatis XML configuration file, only check the status, defaultfalse
mybatis.check-config-location=true# mybatis - config. The location of the XML file. Mybatis config - location = classpath: mybatis/mybatis - config. # XML Mapper corresponding XML path mybatis.mapper-locations=classpath:mybatis/mapper/ * # set the path of an alias, XML can avoid writing the fully qualified class name mybatis. Type - aliases - package = com. Manu. Mybatisxml. ModelCopy the code

MyBatis mainly configures the path of configuration file MyBatis -config. XML and XML file corresponding to Mapper.

@ Mapper and @ MapperScan

The @mapper annotation is used to mark Mapper interfaces. The annotated interfaces will generate corresponding dynamic proxy classes. If there are multiple Mapper interfaces, the @mapper annotation should be used to mark them as follows:

@Mapper
public interface ClassMapper{
    ///
}
Copy the code

@mapperscan annotates the entry class of the project and can be configured with one or more packages for the interface to be scanned, or with the wildcard *, as follows:

@SpringBootApplication
// Scans the interface in the specified packet
@MapperScan("com.manu.mybatisxml.mapper")
// @MapperScan("com.manu.mybatisxml.*.mapper")
// @MapperScan({"pack1","pack2"})
public class SpringBootMybatisXmlApplication {
    public static void main(String[] args) { SpringApplication.run(SpringBootMybatisXmlApplication.class, args); }}Copy the code

Entity class

Example is the relationship between the Class and the student, i.e., one-to-many relationship. Define the Class Class as follows:

/**
 * 班级类
 */
public class Class {
    private String classId;
    private String name;
    private List<Student> students;
    public Class(a) {}public Class(String classId, String name) {
        this.classId = classId;
        this.name = name;
    }
    // ...
    // setter, getter, toString
}
Copy the code

The Student class is as follows:

/** ** class */
public class Student {
    private String classId;
    private String sno;
    private String name;
    private String grade;
    public Student(a) {}public Student(String classId, String sno, String name, String grade) {
        this.classId = classId;
        this.sno = sno;
        this.name = name;
        this.grade = grade;
    }
    // ...
    // setter, getter, toString
}
Copy the code

MyBatis configuration file

The MyBatis configuration file is MyBatis -config. XML. When using MyBatis in Spring Boot, most of the configurations in this configuration file can be configured in the application.properties file. So you can use this configuration file to simplify fully qualified class names in Spring Boot projects, as follows:


      <! DOCTYPEconfiguration PUBLIC
    "- / / mybatis.org//DTD Config / 3.0 / EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <typeAliases>
        <! Define aliases, avoid writing fully qualified class names -->
        <typeAlias alias="Integer" type="java.lang.Integer" />
        <typeAlias alias="Long" type="java.lang.Long" />
        <typeAlias alias="HashMap" type="java.util.HashMap" />
        <typeAlias alias="LinkedHashMap" type="java.util.LinkedHashMap" />
        <typeAlias alias="ArrayList" type="java.util.ArrayList" />
        <typeAlias alias="LinkedList" type="java.util.LinkedList" />
        <typeAlias alias="Student" type="com.manu.mybatisxml.model.Student" />
        <typeAlias alias="Class" type="com.manu.mybatisxml.model.Class" />
    </typeAliases>
</configuration>
Copy the code

Mapper interfaces

The Chinese method name in the Mapper interface corresponds to the method corresponding to the SQL statement in the Mapper mapping file, and the method name must be the same as the ID attribute in the CORRESPONDING SQL statement. ClassMapper:

/** * classmapper. XML corresponding Mapper interface */
public interface ClassMapper {
    /** * Insert a data *@param student student
     */
    void insertStudent(Student student);
    void insertClass(Class course);
    /** * Delete a record based on sNO *@param sno sno
     */
    void deleteStudent(String sno);
    /** * Update data *@param student student
     */
    void updateStudent(Student student);
    /** * query data * with name@param name name
     * @return* /
    Student findStudentByName(String name);
    /** * query all data *@return* /
    List<Student> findAllStudent(a);
    /** * select * from **@param name name
     * @return* /
    Class findClassStudents(String name);
    /** * set data nested query *@param classId classId
     * @return* /
    Class findClassStudents1(String classId);
}
Copy the code

Mapper Mapping file

Mapper mapping file is based on XML and uses SQL tags corresponding to SQL statements to flexibly build SQL statements. Some tags and their attributes are known by name. Common tags are as follows:

  • mapper: Configures the Mapper interface class corresponding to the Mapper mapping file.
  • resultMap: Query statement result set;
  • result: used to defineresultMapFields in the tag;
  • id: used to defineresultMapPrimary key field in the tag;
  • collection: Sets data, such asList<Student>Such data;
  • sql: Defines SQL statement blocks for use by other SQL statements.
  • insert: Insert statement;
  • delete: Delete statement;
  • update: Update statement;
  • select: Query statement.

For common attributes, see the comments in the following case. The Mapper mapping file corresponding to the Mapper interface class ClassMapper is as follows:


      <! DOCTYPEmapper PUBLIC "- / / mybatis.org//DTD Mapper / 3.0 / EN"
    "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.manu.mybatisxml.mapper.ClassMapper">
    <! Student POJO mapping result set -->
    <! --id: unique identifier -->
    <! --type: specific POJO object type -->
    <resultMap id="StudentResultMap" type="com.manu.mybatisxml.model.Student">
        <! --column: primary key column -->
        <! -- Property: property corresponding to POJO object -->
        <! --jdbcType: field type -->
        <id column="classId" property="classId" jdbcType="VARCHAR" />
        <! --column: column in table -->
        <result column="userName" property="name" jdbcType="VARCHAR" />
        <result column="sno" property="sno" jdbcType="VARCHAR" />
        <result column="grade" property="grade" jdbcType="VARCHAR" />
    </resultMap>

    <! Student POJO mapping result set with set result set -->
    <resultMap id="ClassWithCollectionResultMap" type="com.manu.mybatisxml.model.Class">
        <id column="classId" property="classId" jdbcType="VARCHAR" />
        <result column="name" property="name" jdbcType="VARCHAR" />
        <! --ofType: data type in the set -->
        <collection property="students" ofType="Student">
            <id column="sno" property="sno" jdbcType="VARCHAR" />
            <result column="userName" property="name" jdbcType="VARCHAR" />
            <result column="classId" property="classId" jdbcType="VARCHAR" />
            <result column="grade" property="grade" jdbcType="VARCHAR" />
        </collection>
    </resultMap>

    <! --Student POJO mapping result set, carrying set result set, nested query -->
    <resultMap id="ClassWithCollectionResultMap1" type="com.manu.mybatisxml.model.Class">
        <id column="classId" property="classId" jdbcType="VARCHAR" />
        <result column="name" property="name" jdbcType="VARCHAR" />
        <! --column: nested query condition -->
        <! Select * from 'select';
        <collection column="{classId = classId}" property="students" ofType="Student"
            select="getStudent" />
    </resultMap>

    <select id="getStudent" parameterType="String" resultMap="StudentResultMap">
        SELECT *
        FROM mybatis_student
        WHERE classId = #{classId}
    </select>

    <! -- Define base fields -->
    <sql id="BaseStudentColumn">
        sno,userName,classId,grade
    </sql>

    <! -- Insert data -->
    <! --id identifies method name in Mapper interface -->
    <insert id="insertClass" parameterType="Class">
        INSERT INTO mybatis_class(classId, name)
        VALUES (#{classId}, #{name})
    </insert>
    <insert id="insertStudent" parameterType="Student">
        INSERT INTO mybatis_student(classId, userName, sno, grade)
        VALUES (#{classId}, #{name}, #{sno}, #{grade})
    </insert>

    <! -- Delete data -->
    <delete id="deleteStudent" parameterType="String">
        DELETE
        FROM mybatis_student
        WHERE sno = #{sno}
    </delete>

    <! -- Update data -->
    <update id="updateStudent" parameterType="Student">
        UPDATE mybatis_student
        SET userName = #{name},
            classId  = #{classId},
            grade    = #{grade},
            sno      = #{sno}
        WHERE sno = #{sno}
    </update>

    <! Select * from data set;
    <select id="findClassStudents" parameterType="String" resultMap="ClassWithCollectionResultMap">
        SELECT mybatis_class.classId,
               mybatis_class.name,
               mybatis_student.sno,
               mybatis_student.userName,
               mybatis_student.grade
        FROM mybatis_student,
             mybatis_class
        WHERE mybatis_class.classId = mybatis_student.classId
          and mybatis_class.name = #{name}
    </select>

    <! Select * from data set;
    <select id="findClassStudents1" parameterType="String"
        resultMap="ClassWithCollectionResultMap1">
        SELECT mybatis_class.classId,
               mybatis_class.name,
               mybatis_student.sno,
               mybatis_student.userName,
               mybatis_student.grade
        FROM mybatis_student,
             mybatis_class
        WHERE mybatis_class.classId = mybatis_student.classId
          and mybatis_class.classId = #{classId}
    </select>

    <! -- Query single data -->
    <select id="findStudentByName" resultMap="StudentResultMap" parameterType="String">
        SELECT *
        FROM mybatis_student
        WHERE userName = #{name}
    </select>

    <! Select * from 'SQL';
    <select id="findAllStudent" resultMap="StudentResultMap">
        SELECT
        <include refid="BaseStudentColumn" />
        FROM mybatis_student
    </select>
</mapper>
Copy the code

Use of collection tags

By using this label, you can query the set of students in the specified class. The first method is:

<! Student POJO mapping result set with set result set -->
<resultMap id="ClassWithCollectionResultMap" type="Class">
    <id column="classId" property="classId" jdbcType="VARCHAR" />
    <result column="name" property="name" jdbcType="VARCHAR" />
    <! --ofType: data type in the set -->
    <collection property="students" ofType="Student">
        <id column="sno" property="sno" jdbcType="VARCHAR" />
        <result column="userName" property="name" jdbcType="VARCHAR" />
        <result column="classId" property="classId" jdbcType="VARCHAR" />
        <result column="grade" property="grade" jdbcType="VARCHAR" />
    </collection>
</resultMap>
Copy the code

The corresponding QUERY SQL mapping is as follows:

<! Select * from data set;
<select id="findClassStudents" parameterType="String" resultMap="ClassWithCollectionResultMap">
    SELECT mybatis_class.classId,
           mybatis_class.name,
           mybatis_student.sno,
           mybatis_student.userName,
           mybatis_student.grade
    FROM mybatis_student,
         mybatis_class
    WHERE mybatis_class.classId = mybatis_student.classId
      and mybatis_class.name = #{name}
</select>
Copy the code

The second way is as follows:

<! --Student POJO mapping result set, carrying set result set, nested query -->
<resultMap id="ClassWithCollectionResultMap1" type="com.manu.mybatisxml.model.Class">
    <id column="classId" property="classId" jdbcType="VARCHAR" />
    <result column="name" property="name" jdbcType="VARCHAR" />
    <! --column: nested query condition -->
    <! Select * from 'select';
    <collection column="{classId = classId}" property="students" ofType="Student"
        select="getStudent" />
</resultMap>

<select id="getStudent" parameterType="String" resultMap="StudentResultMap">
    SELECT *
    FROM mybatis_student
    WHERE classId = #{classId}
</select>
Copy the code

The corresponding QUERY SQL mapping is as follows:

<! Select * from data set;
<select id="findClassStudents1" parameterType="String"
    resultMap="ClassWithCollectionResultMap1">
    SELECT mybatis_class.classId,
           mybatis_class.name,
           mybatis_student.sno,
           mybatis_student.userName,
           mybatis_student.grade
    FROM mybatis_student,
         mybatis_class
    WHERE mybatis_class.classId = mybatis_student.classId
      and mybatis_class.classId = #{classId}
</select>
Copy the code

The corresponding collection of Student can be queried using findClassStudents defined in the Mapper interface.

Multi-data Source Configuration

Create multiple data source configuration files, generate multiple different data sources and different SqlSessionFactory, etc. The main data source configuration is as follows:

/ * * *@Primary* sqlSessionTemplateRef: Specifies the SqlSessionTemplate */ specified in the Mapper path
@Configuration
@MapperScan(basePackages = "com.manu.multimybatisxml.mapper.primary", sqlSessionTemplateRef = "primarySqlSessionTemplate")
public class PrimaryDataSourceConfig {

    @Primary
    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.primary")
    public DataSource primaryDataSource(a) {
        return DataSourceBuilder.create().build();
    }

    @Primary
    @Bean
    public SqlSessionFactory primarySqlSessionFactory(@Qualifier("primaryDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean sessionFactoryBean = new SqlSessionFactoryBean();
        sessionFactoryBean.setDataSource(dataSource);
        sessionFactoryBean.setMapperLocations(
                new PathMatchingResourcePatternResolver().getResources("classpath:mybatis/mapper/primary/*.xml"));
        return sessionFactoryBean.getObject();
    }

    @Primary
    @Bean
    public DataSourceTransactionManager primaryDataSourceTransactionManager(@Qualifier("primaryDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Primary
    @Bean
    public SqlSessionTemplate primarySqlSessionTemplate(@Qualifier("primarySqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
        return newSqlSessionTemplate(sqlSessionFactory); }}Copy the code

The configuration of the second data source is the same as above, except that @primary is not marked, and the name of the second data source and the corresponding Mapper mapping file are modified, which will not be described here.

Then configure multiple database connections in the application.properties file with the prefix specified in the above configuration as follows:

# dataSourceOne
spring.datasource.primary.username=root
spring.datasource.primary.password=admin
spring.datasource.primary.driver-class-name= com. Mysql. Cj, JDBC Driver # spring. The datasource. The JDBC url - multiple source used to rewrite the custom connection pool in the spring. The datasource. The primary. JDBC - url = JDBC: mysql://localhost:3306/data_source_one? serverTimezone=Asia/Shanghai

# dataSourceTwo
spring.datasource.secondary.username=root
spring.datasource.secondary.password=admin
spring.datasource.secondary.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.secondary.jdbc-url=jdbc:mysql://localhost:3306/data_source_two? serverTimezone=Asia/ShanghaiCheck the status of the MyBatis XML configuration file, only check the status, defaultfalse
mybatis.check-config-location=true# mybatis - config. The location of the XML file. Mybatis config - location = classpath: mybatis/mybatis - config. The path of the XML # set an alias, can avoid writing the fully qualified class name mybatis.type-aliases-package=com.manu.multimybatisxml.model
Copy the code

Specific content can reply keyword [Spring Boot] to obtain the source link.

The test results

The case is only to illustrate how to use, readers need not care about its rationality, write test classes as follows:

/** * MyBatisTest */
@RunWith(SpringRunner.class)
@SpringBootTest
public class MyBatisTest {
    @SuppressWarnings("SpringJavaInjectionPointsAutowiringInspection")
    @Autowired
    private ClassMapper mClassMapper;

    @Test
    public void insert(a) {
        Class class1 = new Class("class1"."Class");
        Class class2 = new Class("class2"."Class 2");
        mClassMapper.insertClass(class1);
        mClassMapper.insertClass(class2);

        List<Student> students = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            Student student;
            if (i % 2= =0) {
                student = new Student("class1"."sno" + i, "Student"+i, "A");
            } else {
                student = new Student("class2"."sno" + i, "Student"+i, "B"); } mClassMapper.insertStudent(student); }}@Test
    public void deleteStudentBySno(a) {
        mClassMapper.deleteStudent("sno0");
    }

    @Test
    public void updateStudent(a) {
        Student student = new Student("class1"."sno1"."student1"."C");
        mClassMapper.updateStudent(student);
    }

    @Test
    public void findStudentByName(a) {
        Student student = mClassMapper.findStudentByName("student5");
        System.out.println(student);
    }

    @Test
    public void findAllStudent(a) {
        List<Student> students = mClassMapper.findAllStudent();
        for(Student student : students) { System.out.println(student.toString()); }}@Test
    public void findClassStudents(a){
        Class clazz = mClassMapper.findClassStudents("Class");
        System.out.println("classId:"+clazz.getClassId()+",name:"+clazz.getName());

        List<Student> students = clazz.getStudents();
        for(Student student : students) { System.out.println(student.toString()); }}@Test
    public void findClassStudents1(a){
        Class clazz = mClassMapper.findClassStudents1("class1");
        System.out.println("classId:"+clazz.getClassId()+",name:"+clazz.getName());

        List<Student> students = clazz.getStudents();
        for(Student student : students) { System.out.println(student.toString()); }}}Copy the code

The findClassStudents method is used as an example to view the result:

Every: class1, name: a group of Student {every'class1', sno='sno1', name='student1', grade='C'}
Student{classId='class1', sno='sno2', name='Student2', grade='A'}
Student{classId='class1', sno='sno4', name='Student4', grade='A'}
Student{classId='class1', sno='sno6', name='Student6', grade='A'}
Student{classId='class1', sno='sno8', name='Student8', grade='A'}
Copy the code

Annotation configuration

MyBatis can be configured with annotations in addition to XML as follows:

@Mapper
public interface StudentMapper {
    /** * The SQL statement in the annotation automatically retrieves the attributes of the student object */
    @Insert("INSERT INTO mybatis_student(userName,sno,grade) VALUES(#{name},#{sno},#{grade})")
    void insert(Student student);

    /** * StudentFactory = student; /** * StudentFactory = student; /** * StudentFactory = student
    @InsertProvider(type = StudentFactory.class, method = "insert1")
    void insert1(Student student);

    Insert2 gets the value of the variable by #{variable name}. We can also concatenate SQL with StringBuffer, as in insert2 returns */
    @InsertProvider(type = StudentFactory.class, method = "insert2")
    void insert2(String sno, String name, String grade);
}

Copy the code

The above method can be implemented as follows:

public class StudentFactory {
    public String insert1(Student student) {
        String sql = new SQL() {{
            INSERT_INTO("mybatis_student");
            VALUES("sno"."#{sno}");
            VALUES("userName"."#{name}");
            VALUES("grade"."#{grade}");
        }}.toString();
        System.out.println("SQL:" + sql);
        return sql;
    }

    public String insert2(String sno,String name,String grade) {
        String sql = new SQL() {{
            INSERT_INTO("mybatis_student");
            VALUES("sno"."#{sno}");
            VALUES("userName"."#{name}");
            VALUES("grade"."#{grade}");
        }}.toString();
        System.out.println("SQL:" + sql);
        returnsql; }}Copy the code

Finally, perform the following tests:

@RunWith(SpringRunner.class)
@SpringBootTest
public class MyBatisAnnotationTests {

    @SuppressWarnings("SpringJavaInjectionPointsAutowiringInspection")
    @Autowired
    StudentMapper mStudentMapper;

    @Test
    public void insert(a) {
        Student student = new Student("sno0"."jzman0"."A");
        mStudentMapper.insert(student);
    }

    @Test
    public void insert1(a) {
        Student student = new Student("sno1"."jzman1"."A");
        mStudentMapper.insert1(student);
    }

    @Test
    public void insert2(a) {
        Student student = new Student("sno2"."jzman2"."A"); mStudentMapper.insert2(student.getSno(), student.getName(), student.getGrade()); }}Copy the code

MyBatis use annotation code less, but there are certain limitations in the flexibility of SQL, there is no practice not to do too much elaboration can be in the public number background reply [practice] reply keyword [Spring Boot] to obtain the corresponding case source link.