Mybatis learning record

Mybatis related

catalogue

preface

Plan to spend some time learning about the popular persistence layer framework Mybatis

Video reference: Shangsi Valley leifengyang Mybatis tutorial

Other references: Official Chinese document of Mybatis3

In addition, where the article quotes the big man's article, it has posted a link. Thank you!

The italicized text is extracted from the official documents of MyBatis3

The notes are for reference only. If there is any error, please point it out!

Common persistence layer technologies

Mybatis tutorial-P1 comparison of common persistence technologies

  • JDBC

    The native persistence layer technology is also a specification

    Everyone who has written projects using JDBC knows that the DAO layer needs to write a series of interfaces and implementation classes, and each method needs to pay attention to the core sql statements,

    It is also necessary to frequently create non core operations such as connection, Preparedstatement and resultset. There is a lot of repetitive work

    • Third party toolkit for JDBC: dbutil (APACHE), JDBC template (spring)

      I haven't used it. I don't give a personal evaluation

  • Hibernate (not used, no evaluation)

    • Fully automatic: we only need to tell hibernate the correspondence between JavaBeans and records in the database, and the corresponding sql statements are automatically generated by the framework

      This means that it is difficult to optimize sql (HQL provided by Hibernate can be used for relevant optimization, which increases the learning cost)

    • Full mapping: it means that all the fields of the table are queried every time, which is inefficient. Partial mapping requires the help of HQL

      ORM: Object Relational Mapping
      By matching database tables with entity classes and attributes of entity classes, we can implement operation entity classes, that is, operation data tables

  • Mybatis (semi-automatic frame)

    Mybatis will hand over the core work of writing sql to programmers, and decouple it in xml instead of hard coding sql in Java code like JDBC

Mybatis start

First Mybatis Demo

Here's a reference Mybatis official documentation - getting started In the first section, we start our study of Mybatis by making a query and encapsulating the corresponding results in the way of Mybatis

  • Firstly, the dependency of Mybatis is introduced

    `<dependency>
        <groupId>org.mybatis</groupId>
        <artifactId>mybatis</artifactId>
        <version>3.5.7</version>
    </dependency>
    
  • Next, configure the global configuration file for Mybatis

    The global configuration file contains the core settings of the MyBatis system, including the data source to obtain the database connection instance and the transaction manager to determine the transaction scope and control mode. Details will be discussed later

    <?xml version="1.0" encoding="UTF-8" ?>
    <!DOCTYPE configuration
            PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
            "http://mybatis.org/dtd/mybatis-3-config.dtd">
    <configuration>
        <properties resource="dbconfig.properties"/> <!-- with properties Form of document, Give the relevant configuration information of the database -->
        <environments default="development">
            <environment id="development">
                <transactionManager type="JDBC"/>
                <dataSource type="POOLED">
                    <property name="driver" value="${jdbc.driver}"/>
                    <property name="url" value="${jdbc.url}"/>
                    <property name="username" value="${jdbc.username}"/>
                    <property name="password" value="${jdbc.password}"/>
                </dataSource>
            </environment>
        </environments>
        <mappers> <!--Contains a set of mappers( mapper),Of these mappers XML The mapping file contains SQL Code and mapping definition information -->
            <mapper resource="studentMapper.xml"/> 
        </mappers>
    </configuration>
    
    #Bloody lesson!!! Driver must be capitalized, or ClassNotFoundException will always appear
    jdbc.driver=com.mysql.cj.jdbc.Driver  
    jdbc.url=jdbc:mysql://localhost:3306/qdu?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=UTF-8
    jdbc.username=root
    jdbc.password=root
    
  • The mapping file studentMapper.xml is given below

    <?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.shy.mybatis.studentMapper"> <!-- namespace:Call mapped sql Namespace for statement.id -->
        <!-- id: Should ql Statement here namespace Unique identification under -->
        <!-- resultType: this sql The return value type that the statement expects to encapsulate -->
        <select id="selectStudentById" resultType="com.shy.entity.Student">
            SELECT * FROM student WHERE id = #{id} <!-- #{id} is a dynamically entered parameter -- >
        </select>
    </mapper>
    

    student table:

  • The configuration file is ready. Next, start creating SqlSessionFactory,

    public class MybatisUtil {
    
        private static SqlSessionFactory sqlSessionFactory;
        
        public static synchronized SqlSessionFactory getSqlSessionFactory() throws IOException {
            // Gets the input stream of the mybatis global configuration file
            // Mybatis provides the Resources tool class to easily obtain the input stream of the configuration file
            InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
            // Create SqlSessionFactory in singleton mode (recommended in official documents)
            if(null == sqlSessionFactory){
                // The only use of SqlSessionFactoryBuilder is to create SqlSessionFactory
                sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
            }
            return sqlSessionFactory;
        }
    }
    

  • SqlSessionFactory, as its name implies, is used to create a SqlSession factory, so next, create a SqlSession and perform a query encapsulation output test

    @Test
    public void test01() throws IOException {
        SqlSessionFactory sqlSessionFactory = MybatisUtil.getSqlSessionFactory();
        // Use SqlSessionFactory to obtain sqlsession, which is equivalent to one session of the database
        SqlSession sqlSession = sqlSessionFactory.openSession();
        try {
            // Call the SQL statement in mapper and pass in the parameters required by the SQL statement
            Student student = sqlSession.selectOne("com.shy.mybatis.studentMapper.selectStudentById",1);
            System.out.println(student.toString());
        }finally {
            // Be sure to close after using sqlSession
            sqlSession.close();
        }
    }
    //Student{id=1, name='Lando', gender='male', age=20}
    

    Q: Why do you have to close it manually after using sqlSession?

    A: reference resources

Using Mapper interface

Student student = sqlSession.selectOne("com.shy.mybatis.studentMapper.selectStudentById",1);

In the first demo, we used "com.shy.mybatis.studentMapper.selectStudentById" to call the sql statement:

<mapper namespace="com.shy.mybatis.studentMapper"> 
    <select id="selectStudentById" resultType="com.shy.entity.Student">
        SELECT * FROM student WHERE id = #{id}
    </select>
</mapper>

However, a more concise and popular way is to use interfaces that match the parameters and return values of the specified sql statement

demonstration:

  • First, write the corresponding interface StudentMapper.java

    public interface StudentMapper {
        // The return value is the same as resultType
        // The method name is the same as the id
        // The parameter list is consistent with the parameters of the Sql statement
        public Student selectStudentById(int id);
    }
    
  • Bind the interface to the mapper's configuration file

    Change the mapper's namespace in studentMapper.xml to the fully qualified name of the interface

    <mapper namespace="com.shy.dao.StudentMapper">  <!-- Dynamic binding -->
        <select id="selectStudentById" resultType="com.shy.entity.Student">
            SELECT * FROM student WHERE id = #{id}
        </select>
    </mapper>
    
  • When you are ready, create a SqlSession to test

    @Test
    public void test03() throws IOException {
        SqlSessionFactory sqlSessionFactory = MybatisUtil.getSqlSessionFactory();
        SqlSession sqlSession = sqlSessionFactory.openSession();
        try{
            // Call getmapper(), and the underlying layer of mybatis will create the corresponding implementation class (proxy design pattern) of the StudentMapper interface through AOP
            StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
            Student student = studentMapper.selectStudentById(1);
            System.out.println(student.toString());
        }finally {
            sqlSession.close();
        }
    }
    // Student{id=1, name='Lando', gender='male', age=20}
    

Configuring Sql statements with annotations

public interface StudentMapper {
    @Select("SELECT * FROM Student WHERE id = #{id}")
    public Student selectStudentById(int id);
}

If you configure sql statements with annotations, you do not need the studentMapper.xml mapping file

When registering mapper, use the fully qualified name of the interface because there is no xml Mapping file

<mappers>    
    <mapper class="com.shy.dao.StudentMapper"/>
</mappers>

Test:

@Testpublic void test03() throws IOException {    SqlSessionFactory sqlSessionFactory = MybatisUtil.getSqlSessionFactory();    SqlSession sqlSession = sqlSessionFactory.openSession();    try{        // Call getmapper(), and mybatis bottom layer will create corresponding implementation classes (proxy design pattern) of StudentMapper interface through AOP. StudentMapper = sqlsession.getmapper (StudentMapper. Class); student = StudentMapper.selectstudentbyid (1); system.out.println (student. Tostring());}finally {sqlsession. Close() ;    }}// Student{id=1, name='Lando', gender='male', age=20}

Mybatis global profile

reference resources: Mybatis official document - XML configuration

This configuration file determines how Mybatis works

structure

The Mybatis global configuration file mainly includes the following attributes

Properties

< Properties > is mainly used to configure some properties

The most common way is to introduce an external. properties file (decoupling), such as dbconfig.properties, which is the relevant information of the database

<properties resource="dbconfig.properties"/>
<!-- resource Classpath   url network path/Disk path -->
jdbc.driver=com.mysql.cj.jdbc.Driver  
jdbc.url=jdbc:mysql://localhost:3306/mybatis
jdbc.username=root
jdbc.password=root

You can also write properties within the < Properties > tag

<properties>
    <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
    ...
</properties>

Or passed in as a method parameter

public SqlSessionFactory build(InputStream inputStream, Properties properties) {
    return build(inputStream, null, properties);
}

If more than one place is configured, Mybatis will be loaded in the following order:

The attributes passed through the method parameters have the highest priority, followed by the configuration file specified in the resource/url attribute, and the attributes specified in the properties element have the lowest priority (high priority overrides low priority)

Settings

< Settings > is used to configure some properties that can affect the working mode of Mybatis

  • mapUnderscoreToCamelCase "Hump" policy mapping

    By default, Mybatis maps the field value of the table to the same attribute as the field name in Java Bean

    student table id field → \rightarrow → on the id attribute of the Student entity

    In practice, the database fields are mostly written as student_ In the form of ID, the attributes of Java Bean are mostly written in the form of studentId in the form of hump

    under these circumstances:

    student table_ ID field ↛ \nrightarrow ↛ on the studentId attribute of the Student entity

    At this time, you can set mapUnderscoreToCamelCase to enable the automatic mapping of hump naming

    <settings>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>
    

    For more attribute details, please refer to Official documents

  • autoMappingBehavior auto mapping

  • Lazy loading enabled deferred loading

  • aggressiveLazyLoading

Typealiases

Type alias sets an abbreviated name for a Java type. It is only used for XML configuration and is intended to reduce redundant writing of fully qualified class names (but it is better to write fully qualified names with clear semantics)

<typeAliases>
    <!-- Aliases are used by default Bean The initial lowercase unqualified class name of student
	<!--alias Specify an alias-->
    <typeAlias type="com.shy.entity.Student" alias="student"/>
    <package name="com.shy"/> <!-- Give each class under this package an alias -->
</typeAliases>

You can also use @ Alias annotations

@Alias("student")
public class Student{}

In addition, Mybatis does not provide a default alias for the common built-in types in Java. Specifically See documentation

Typehandlers

typeHandlers are used to map JDBC types (such as varchar) and Java types (such as String) to complete type conversion, so as to encapsulate field values into JavaBean s

MyBatis provides us with a series of by default Built in typeHandlers

See [Mybatis type processor] (#Mybatis type processor) for details

*Objectfactory (object factory)

Every time MyBatis creates a new instance of the result object, it uses an object factory instance to complete the instantiation

The default object factory only needs to instantiate the target class, either through the default parameterless construction method, or through the existing parameter mapping to call the construction method with parameters.

If you want to override the default behavior of the object factory, you can create your own object factory by inheriting DefaultObjectFactory

Plugins

MyBatis allows you to intercept calls (AOP) at some point during the execution of mapping statements

see Mybatis plug-in development

Environments

environment

MyBatis can be configured to adapt to a variety of environments. This mechanism helps to apply SQL mapping to a variety of databases. In reality, there are many reasons to do so. For example, development, test and production environments need different configurations; Or you want to use the same SQL mapping in multiple production databases with the same Schema. There are many similar usage scenarios

<environments default="development"> <!-- Specify the default environment -->
    <environment id="development"> <!-- Unique identification of the environment -->
        <transactionManager type="JDBC"/> <!-- Transaction manager -->
        <dataSource type="POOLED"> <!-- data source -->
            <property name="driver" value="${jdbc.driver}"/>
            <property name="url" value="${jdbc.url}"/>
            <property name="username" value="${jdbc.username}"/>
            <property name="password" value="${jdbc.password}"/>
        </dataSource>
    </environment>
    <environment id="test">
        <transactionManager type="JDBC"/>
        <dataSource type="POOLED">
            ...
        </dataSource>
    </environment>
</environments>

You can specify environment when creating SqlSessionFactory

SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("mybatis-config.xml"), "test");

transactionManager

The understanding of affairs is not too deep. It is directly moved to the official document

When Spring integrates Mybatis, it will use the relevant functions of Spring to manage transactions

datasource

Resources used to configure JDBC connection objects

Three built-in data source types( Refer to the document for details)

UNPOOLED – the implementation of this data source will open and close connections every time it is requested. Although it is a little slow, it is a good choice for simple applications that do not require high database connection availability. Performance depends on the database used. For some databases, the use of connection pool is not important. This configuration is very suitable for this situation

POOLED – the implementation of this data source uses the concept of pool to organize JDBC connection objects, avoiding the initialization and authentication time required to create new connection instances. This processing method is very popular and can enable concurrent Web applications to respond to requests quickly

JNDI - this data source implementation is to be used in containers such as EJB or application server. The container can configure the data source centrally or externally, and then place a data source reference of JNDI context.

In addition, you can customize the data source by inheriting UnpooledDataSourceFactory

databaseIdProvider

MyBatis can execute different statements according to different database vendors

Be sure to set the property alias, otherwise it will not be recognized

<databaseIdProvider type="DB_VENDOR">
    <property name="MySQL" value="mysql"/> <!-- name Property cannot be specified arbitrarily -->
    <property name="SQL Server" value="sqlserver"/>
    <property name="DB2" value="db2"/>
    <property name="Oracle" value="oracle" />
</databaseIdProvider>
sqlSession.getConnection().getMetaData().getDatabaseProductName(); // You can get the name attribute of the current database
<!-- databaseId="mysql" The query will only MySQL Executed in environment -->
<select id="selectStudentById" resultType="com.shy.entity.Student" databaseId="mysql">
    SELECT * FROM mybatis.student WHERE id = #{id}
</select>

If databaseIdProvider is configured, the built-in parameter _databaseproviderrepresents the name of the current database

mappers

mapper is used to tell mybatis the location of SQL mapping files or corresponding interfaces

<mappers>
    <!-- resource Apply to StudentMapper.xml And StudentMapper The interface is not in the same path -->
    <mapper resource="StudentMapper.xml"/>     <!-- According to my experience in three or four days, This is IDEA More commonly used under -->
    <mapper class="com.shy.dao.StudentMapper"/> 
    <package name="com.shy.dao"/>
</mappers>

The above three configuration methods are applicable to different situations. For details, please refer to Boss blog

Mybatis mapping file

The real strength of MyBatis lies in its statement mapping, which is its magic. Due to its extraordinary power, the XML file of the mapper is relatively simple. If you compare it with JDBC code with the same function, you will immediately find that nearly 95% of the code is saved. MyBatis is committed to reducing the use cost and enabling users to focus more on SQL code.

structure

The Mybatis mapping file is the core part. The learning of this part mainly refers to the Mybatis tutorial of Lei Fengyang in Shangsi valley. The order is slightly different from the structure in the figure above

insert/update/delete

These three elements are used to map insert / update / delete statements respectively

<!--    public int insertStudent(Student student);-->
<!-- commonly parameterType Omit, Mybatis You can automatically identify the type of the incoming parameter -
<insert id="insertStudent" parameterType="com.shy.entity.student">
    INSERT INTO student(name, gender, age)
    VALUES (#{name}, #{gender}, #{age})
</insert>

<!--    public int updateStudentById(Student student);-->
<update id="updateStudentById">
    UPDATE student
    SET name   = #{name},
    gender = #{gender},
    age    = #{age}
    WHERE id = #{id}
</update>

<!--    public int deleteStudentById(int id);-->
<delete id="deleteStudentById">
    DELETE
    FROM student
    WHERE id = #{id}
</delete>

Attribute overview

matters needing attention

  • Pass multiple parameters by passing in a Java Bean

    In the above insert and update methods, we directly pass in the Student object (encapsulating multiple parameters), so that #{bean attribute} can be used to pass values in Sql statements

  • The addition, deletion and modification shall be submitted to the commit in time

    If sqlsession. Commit() is not called, the SQL statement will execute successfully, but there will be no corresponding modification in the database

    This is because if you do not commit, the rollback will be rolled back automatically in sqlSession.close()

    // Related source code
    @Override
    public void close(boolean forceRollback) {
     try {
       if (forceRollback) { // No commit, forceRollback = true
         tcm.rollback();
       } else {
         tcm.commit();
       }
     } finally {
       delegate.close(forceRollback);
     }
    }
    
    try {
        StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
        Student student = new Student("Norris","male",25);
        int rows = studentMapper.insertStudent(student);
        System.out.println(rows); 
        sqlSession.commit(); // Be sure to commit
    }finally {
        sqlSession.close();
    }
    

    Of course, you can also set autoCommit when creating SqLSession

    SqlSession sqlSession = sqlSessionFactory.openSession(true);
    
  • The return value of addition, deletion and modification can be void/Integer(int)/Long/Boolean

    You only need to specify the return value type on the method corresponding to the interface

    public int/Boolean updateStudentById(Student student);
    
    // Related source code
    private Object rowCountResult(int rowCount) {
        final Object result;
        if (method.returnsVoid()) {
            result = null;
        } else if (Integer.class.equals(method.getReturnType()) || Integer.TYPE.equals(method.getReturnType())) {
            result = rowCount;
        } else if (Long.class.equals(method.getReturnType()) || Long.TYPE.equals(method.getReturnType())) {
            result = (long) rowCount;
        } else if (Boolean.class.equals(method.getReturnType()) || Boolean.TYPE.equals(method.getReturnType())) {
            result = rowCount > 0;
        } else {
            throw new BindingException("Mapper method '" + command.getName() + "' has an unsupported return type: " + method.getReturnType());
        }
        return result;
    }
    
  • Get the self incrementing primary key (assign the self incrementing primary key to the attribute of Java Bean)

    <!-- keyProperty Assign self incrementing primary key to Java Bean Which attribute of -->
    <insert id="insertStudent" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO student(name, gender, age)
        VALUES (#{name}, #{gender}, #{age})
    </insert>
    

    This method is only applicable to databases that support self increment of primary key (such as MySQL and sqlserver)

    For databases that do not support self incrementing of primary keys, use selectKey

    [the external chain picture transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the picture and upload it directly (img-kBNOdtA0-1631105867439)(D:/Typora%20Img/image-20210820215214427.png)]

Parameter passing in Sql statements

reference resources: Do you understand several ways of transmitting parameters of Mybatis- Know

Source code analysis: Mybatis parameters are passed and encapsulated into a Map

When writing Sql statements in the XXXMapper.xml file, the parameter transfer of #{xx} is often involved, which is briefly discussed below

  • Single parameter

    #The key in {key} can be in any form, but it is generally consistent with the input parameter name

    <!--    public Student selectStudentById(int id);-->
    <select id="selectStudentById" resultType="com.shy.entity.Student">
        SELECT *FROM student WHERE id = #{id}
    </select>
    
  • Multiple parameters

    • By default

      #{param1/args0} corresponds to the first parameter, #{param2/args1} corresponds to the second parameter

      <!--    public Student selectStudentByNameAndGender(String Name, String Gender);-->
      <select id="selectStudentByNameAndGender" resultType="com.shy.entity.Student">
          #     SELECT * FROM student WHERE name = #{arg0} AND gender = #{arg1}
          #     SELECT * FROM student WHERE name = #{param1} AND gender = #{param2}
      </select>
      

      This parameter is not recommended because it is not semantic enough

    • Interface parameters are annotated with @ Param

      #The value in {key} can be a value in @ Param("")

      <!--    public Student selectStudentByNameAndGender(@Param("name") String Name, @Param("gender") String Gender);-->
      <select id="selectStudentByNameAndGender" resultType="com.shy.entity.Student">
          SELECT * FROM student WHERE name = #{name} AND gender = #{gender}
      </select>
      

In addition, there may be many types of interface parameters

  • The interface parameter is POJO

    POJO: plain sequential Java object simple Java Bean

    #The value in {key} corresponds to an attribute of POJO

    public class Student {
        private int id;
        private String name;
        private String gender;
        private int age;
        ...
    }
    
    <!--    public int updateStudentById(Student student);-->
    <update id="updateStudentById">
        UPDATE student SET name = #{name},gender = #{gender},age = #{age}WHERE id = #{id}
    </update>
    

    Parameters marked with @ Param("student") can be mapped with #{student.name}

  • The interface parameters are encapsulated into a Map

    If the parameter we want to pass is the data in an entity and is not often used, we can encapsulate it into a Map for transmission

    #The key in {key} corresponds to the key encapsulated in the Map

    <!--    public Student selectStudentByMap(Map<String,Object> map);-->
    <select id="selectStudentByMap" resultType="com.shy.entity.Student">
        SELECT * FROM student WHERE name = #{name} AND age = #{age}
    </select>
    

    Parameters marked with @ Param("map") can be mapped with #{map.name}

    // test
    Map<String, Object> map = new HashMap<>();
    map.put("name","Fati");
    map.put("age",18);
    Student student = studentMapper.selectStudentByMap(map);
    System.out.println(student.toString());
    // Student{id=2, name='Fati', gender='male', age=18}
    

    If it is often used, we can encapsulate it into a to (data transmission object) for transmission (such as paging class Page)

  • The interface parameter type is Collection(List/Set) / Array

    If the parameter type is List, use #{list[0]}/#{collection[0]} to refer to the first element

    If the parameter type is set, #{collection[0]} refers to the first element

    If the parameter type is array, use #{array[0]} to refer to the first element

    Of course, if @ Param() is used for annotation, the above rules can be ignored

    // #{actorIdList[0]}
    public List<Actor> selectActorListByActorIdList(@Param("actorIdList") List<Integer> actorIdList);
    
    // Related source code
    public static Object wrapToMapIfCollection(Object object, String actualParamName) {
        if (object instanceof Collection) {
            ParamMap<Object> map = new ParamMap<>();
            map.put("collection", object);
            if (object instanceof List) {
                map.put("list", object);
            }
            Optional.ofNullable(actualParamName).ifPresent(name -> map.put(name, object));
            return map;
        } else if (object != null && object.getClass().isArray()) {
            ParamMap<Object> map = new ParamMap<>();
            map.put("array", object);
            Optional.ofNullable(actualParamName).ifPresent(name -> map.put(name, object));
            return map;
        }
        return object;
    }
    

#{} and ${}

reference resources: The difference between #{} and ${} in MyBatis_ siwuxie095’s blog

#{} similar to in PreparedStatement? Placeholders can prevent sql injection, but table names, field names and keywords (such as sorting method) cannot be used #{}

When you need to dynamically replace these fields in an sql statement, you can use ${}

${} is essentially string splicing

<!--    public List<Actor> selectActorListByColumnAndOrder(String column, String order);-->
<select id="selectActorListByColumnAndOrder" resultType="com.shy.entity.Actor">
    SELECT * FROM actor ORDER BY ${column} ${order}
</select>

#{} also supports setting some other properties

Processing null values

Mybatis maps null values in Java to Other types in JDBC by default, which is not recognized by some databases (such as Oracle)

At this point, you can set to map null values to null types in JDBC

#{name, jdbcType=NULL}

Or modify the global default configuration

<settings>
    <setting name="jdbcTypeForNull" value="NULL"/>
</settings>

select return List/Map

<!--    public List<Actor> selectActorList();-->
<select id="selectActorList" resultType="com.shy.entity.Actor"> <!-- resultType by List Types included -->
    SELECT * FROM actor;
</select>

In addition, Mybatis also supports encapsulating the return value List into a Map by specifying the Key of the Map

@MapKey("ActorId")
public Map<Integer,Actor> selectActorMap();
<select id="selectActorMap" resultType="com.shy.entity.Actor">
    SELECT * FROM actor
</select>

resultMap

resultMap is the most important and powerful element in Mybatis. It allows you to customize the mapping rules between database fields and JavaBean attributes

When the automatic mapping rules cannot meet the requirements, you can use resultMap to customize the mapping rules

id/result

id is used to specify the primary key column

result is used to specify common fields

<!-- Custom mapping rules.   type: return type -->
<resultMap id="actorResultMap" type="com.shy.entity.Actor">
<!-- property: JavaBean A property of column:A field in a table -->
<id property="ActorId" column="aid"/> <!-- id Used to specify the primary key column -->
<result property="ActorChineseName" column="aChineseName"/> <!-- result Used to specify common fields -->
<!-- Other fields and attributes with the same name, It is automatically mapped according to the rules. Of course, we can also specify -->
<result property="ActorOriginName" column="ActorOriginName" />
<result property="ActorInfo" column="ActorInfo" />
</resultMap>
<select id="selectActorList" resultMap="actorResultMap">
    SELECT * FROM actor
</select>

1: 1. Association query

Suppose there is a Student entity

public class Student {
    private int id;
    private String name;
    private String gender;
    private int age;
    private School school; // A student studies in only one school
}

public class School {
    private int schoolId;
    private String schoolName;
}

There are corresponding tables student and school in the database

Now, if you want to find out the student information with id=1, you need to query multiple tables

SELECT * FROM student, school WHERE student.schoolId = school.schoolId AND id = 1;

And encapsulate the found information into the Student object with the help of Mybatis

According to what we have learned before, we may write as follows:

<select id="selectStudentById" resultType="com.shy.entity.Student">
    SELECT * FROM student, school WHERE student.schoolId = school.schoolId AND id = #{id}
</select>

But after the test, it was found that school could not be encapsulated correctly

Student{id=1, name='Pedri', gender='male', age=18, school=null}

In this case, we need to use resultType to customize the mapping rules

  • Cascade attributes encapsulate query results

    <resultMap id="studentResultMap" type="com.shy.entity.Student">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="gender" column="gender"/>
        <result property="age" column="age"/>
        <!-- school Properties of entities -->
        <result property="school.schoolId" column="schoolId"/> 
        <result property="school.schoolName" column="schoolName"/>
    </resultMap>
    
  • Encapsulating query results with association

    • nested result mappings

      <resultMap id="studentResultMap" type="com.shy.entity.Student">
          <id property="id" column="id"/>
          <result property="name" column="name"/>
          <result property="gender" column="gender"/>
          <result property="age" column="age"/>
          <association property="school" javaType="com.shy.entity.School">
              <id property="schoolId" column="schoolId"/>
              <result property="schoolName" column="schoolName"/>
          </association>
      </resultMap>
      
      <!-- It can also be defined externally, For reuse -->
      <resultMap id="schoolResultMap" type="com.shy.entity.School">
          <id property="schoolId" column="schoolId"/>
          <result property="schoolName" column="schoolName"/>
      </resultMap>
      
      <resultMap id="studentResultMap" type="com.shy.entity.Student">
          <id property="id" column="id"/>
          <result property="name" column="name"/>
          <result property="gender" column="gender"/>
          <result property="age" column="age"/>
          <association property="school" resultMap="schoolResultMap"/> <!-- quote schoolResultMap -->
      </resultMap>
      
    • Nested Select query

      To put it simply, a multi table query is decomposed into multiple simple queries

      # Original multi table query
      SELECT * FROM student, school WHERE student.schoolId = school.schoolId AND id = #{id}; 
      # Query after decomposition
       1.SELECT * FROM student WHERE id = #{id}
       2.SELECT * FROM school WHERE schoolId = #{schoolId}
      
      <!-- studentMapper.xml-->
      <select id="selectStudentById" resultMap="studentResultMap">
          SELECT * FROM student WHERE id = #{id}
      </select>
      
      <resultMap id="studentResultMap" type="com.shy.entity.Student">
          <id property="id" column="id"/>
          <result property="name" column="name"/>
          <result property="gender" column="gender"/>
          <result property="age" column="age"/>
          
      <!--take selectStudentById Found out schoolId Pass to com.shy.dao.SchoolMapper.selectSchoolBySchoolId Make a query-->
          <association property="school"
                       select="com.shy.dao.SchoolMapper.selectSchoolBySchoolId" column="schoolId"/>
      </resultMap>
      
      <!-- schoolMapper.xml-->
      <select id="selectSchoolBySchoolId" resultType="com.shy.entity.School">
          SELECT * FROM school WHERE schoolId = #{schoolId}
      </select>
      

      # In simple terms, delayed loading means that the second sql. Is executed only when the relevant properties of school are used, so as to improve efficiency
      1.SELECT * FROM student WHERE id = #{id}
      2.SELECT * FROM school WHERE schoolId = #{schoolId}
      
      <association ... fetchType="lazy"/> <!-- Turn on delayed loading -->
      <setting name="lazyLoadingEnabled" value="TRUE"/> <!-- Or enable deferred loading through global configuration --->
      

      In addition, sometimes nested subqueries may require multiple parameters, which can be passed by column = {attribute 1 = field 1, attribute 2 - field 2}

1:n Association query

Suppose there is a football club entity

public class FootballClub {
    private int id;
    private String name;
    private List<Player> players; // A team has more than one player
}

public class Player {
    private int id;
    private String name;
}

There is a corresponding table football in the database_ club ,player

Now we want to query the information of the football club with id=1 and use Mybatis to encapsulate the result into an object

SELECT * FROM football_club, player WHERE football_club.fc_id = player.fc_id AND football_club.fc_id = 1

At this time, we need to use collection to encapsulate the relevant information into the List collection. We can also use nested result mapping and nested SELECT mapping

  • nested result mappings

    <select id="selectFootballClubByFootBallClubId" resultMap="FootballClubResultMap">
        SELECT * FROM football_club, player WHERE football_club.fc_id = player.fc_id AND football_club.fc_id = #{id}
    </select>
    
    <resultMap id="FootballClubResultMap" type="com.shy.entity.FootballClub">
        <id property="id" column="fc_id"/>
        <result property="name" column="fc_name"/>
        <!-- ofType Specifies the type of encapsulation within the collection(Must specify!)-->
        <collection property="players" ofType="com.shy.entity.Player">
            <id property="id" column="player_id"/>
            <result property="name" column="player_name"/>
        </collection>
    </resultMap>
    
  • Nested SELECT mapping

    <!-- FootballClub.xml -->
    <resultMap id="FootballClubResultMap" type="com.shy.entity.FootballClub">
        <id property="id" column="fc_id"/>
        <result property="name" column="fc_name"/>
        <!-- take fc_id Pass to selectPlayerListByFootballClubId Find out the list and encapsulate it to players -->
        <collection property="players"  select="com.shy.dao.PlayerMapper.selectPlayerListByFootballClubId" column="fc_id" 
                    ofType="com.shy.entity.Player"/>
    </resultMap>
    <select id="selectFootballClubByFootBallClubId" resultMap="FootballClubResultMap">
        SELECT * FROM football_club WHERE fc_id = #{fc_id}
    </select>
    
    <!-- PlayerMapper.xml-->
    <resultMap id="PlayerResultMap" type="com.shy.entity.Player">
        <id property="id" column="player_id"/>
        <result property="name" column="player_name"/>
    </resultMap>
    <select id="selectPlayerListByFootballClubId" resultMap="PlayerResultMap">
        SELECT * FROM player WHERE fc_id = #{fc_id}
    </select>
    

Discriminator

The discriminator can associate different resultmaps according to the situation

sql reusable snippet

The < SQL > tag can be used to define reusable SQL code fragments for use in other statements

The < include > tag is used to refer to the < SQL > tag that should be used, and different parameter values can be defined in it

<sql id="studentColumns"> <!-- Extract query fields -->
    ${table}.id,${table}.name,${table}.gender,${table}.age,${table}.schoolId
</sql>
<select id="selectStudentById" resultMap="studentResultMap">
    SELECT
    <include refid="studentColumns"> <!-- quote <sql>fragment -->
        <property name="table" value="student"/> <!-- Set parameters -->
    </include>
    FROM student WHERE id = #{id}
</select>

Dynamic Sql

Mybatis provides dynamic Sql technology to splice Sql statements in different situations, so as to execute different Sql statements

if

The most common application scenario of if is to include a part of the where clause according to the condition

public List<Student> selectStudentListByIfConditions
    (@Param("name")String name, @Param("gender")String gender, @Param("age") Integer age);
<select id="selectStudentListByIfConditions" resultType="com.shy.entity.Student">
    SELECT * FROM student WHERE
    <if test="name != null">name = #{name}</if>
    <if test="gender != null">AND gender = #{gender}</if>
    <if test="age != null">AND age = #{age}</if>
</select>
// test
List<Student> studentList = studentMapper.selectStudentListByIfConditions("messi", "male", null);
// SELECT * FROM student WHERE name = "messi" AND gender = "male"
// age is empty and not used as filter criteria

Follow in test OGNL Grammar of

Or update dynamically updates part of the data

public int updateStudentInfoByIfConditions
    (@Param("name") String name, @Param("gender")String gender, @Param("age") Integer age, @Param("id") Integer id);
<update id="updateStudentInfoByIfConditions">
    UPDATE student SET
    <if test="name != null">name = #{name},</if>
    <if test="gender != null">gender = #{gender},</if>
    <if test="age != null">age = #{age}</if>
    WHERE id = #{id}
</update>
// test
int rows = studentMapper.updateStudentInfoByIfConditions("Leo Messi",null,18,7);
// UPDATE student SET name = "Leo Messi", age = 18 WHERE id = 7;
// gender is empty and not updated

where/set/trim

  • where

    The WHERE element inserts the "WHERE" clause only if the child element returns anything. Moreover, if the clause starts with "AND" OR ", the WHERE element will also remove them (only the redundant AND OR at the beginning will be removed)

    <select id="selectStudentListByIfConditions" resultType="com.shy.entity.Student">
        SELECT * FROM student WHERE
        <if test="name != null">name = #{name}</if>
        <if test="gender != null">AND gender = #{gender}</if>
        <if test="age != null">AND age = #{age}</if>
    </select>
    Consider this SELECT, If there is only one test condition gender="male", Then what will be executed sql Statement is:
    SELECT * FROM student WHERE AND gender = "male"
    obviously AND It's redundant. This is what we have to rely on <where> label, It will automatically remove the beginning AND.
    <select id="selectStudentListByIfConditions" resultType="com.shy.entity.Student">
        SELECT * FROM student
        <where>
            <if test="name != null">name = #{name}</if>
            <if test="gender != null">AND gender = #{gender}</if>
            <if test="age != null">AND age = #{age}</if>
        </where>
    </select>
    Executed at this time sql Statement is:
    SELECT * FROM student WHERE gender = "male"
    
  • set

    The SET element will dynamically insert the SET keyword at the beginning of the line and delete the extra comma (only the comma at the end of the sentence will be removed)

    <update id="updateStudentInfoByIfConditions">
        UPDATE student SET
        <if test="name != null">name = #{name},</if>
        <if test="gender != null">gender = #{gender},</if>
        <if test="age != null">age = #{age}</if>
        WHERE id = #{id}
    </update>
    Consider this UPDATE, If there is only one test condition name="Messi", Then what will be executed sql Statement is:
    UPDATE student SET name = "Messi", WHERE id = 7;
    obviously , It's redundant, Then we have to rely on<set>label, It will automatically remove the redundant at the end ,
    <update id="updateStudentInfoByIfConditions">
        UPDATE student
        <set>
            <if test="name != null">name = #{name},</if>
            <if test="gender != null">gender = #{gender},</if>
            <if test="age != null">age = #{age}</if>
        </set>
        WHERE id = #{id} <!--  Be sure to put it outside the set tag, otherwise it cannot be the end -- >
    </update>
    Executed at this time sql Statement is:
    UPDATE student SET name = "Messi" WHERE id = 7;
    
  • trim

    Sometimes < where > and < set > may not meet our needs

    We can use < trim > to customize the pre suffix and the characters to be removed from the beginning and end

<!-- Equivalent to <update> -->
<trim prefix="SET" suffixOverrides=",">
<!-- Equivalent to <where> -->
<trim prefix="WHERE" prefixOverrides="AND">
<trim prefix="WHERE" prefixOverrides="OR">

choose

Select one of multiple conditions to use, similar to switch

<select id="selectStudentListByChooseCondition" resultType="com.shy.entity.Student">
    SELECT * FROM student
    <trim prefix="WHERE" prefixOverrides="AND">
        <choose>
            <when test="name != null">name = #{name}</when>
            <when test="gender != null">gender = #{gender}</when>
            <otherwise>age = #{age}</otherwise>
        </choose>
    </trim>
</select>

foreach

  • Query with in

    <!--    public int selectStudentByIdList(@Param("idList") List<Integer> IdList);-->
    <select id="selectStudentByIdList" resultType="com.shy.entity.Student">
        SELECT * FROM student WHERE id IN
        <foreach collection="idList" item="id" open="(" close=")" separator=",">
            #{id}
        </foreach>
    </select>
    SQL: SELECT * FROM SELECT * FROM student WHERE id IN (1, 2, 3)
    
  • Batch insert

    <!--    public int insertStudentList(@Param("studentList")List<Student> studentList);-->
    <insert id="insertStudentList">
        INSERT INTO student(name, gender, age) VALUES
        <foreach collection="studentList" item="student"  separator=",">
            (#{student.name},#{student.gender},#{student.age})
        </foreach>
    </insert>
    SQL:  INSERT INTO student(name, gender, age) VALUES ("Messi","male",34),("Xavi","male",40);
    
    
    <!--    public int insertStudentList2(@Param("studentList")List<Student> studentList);-->
    <insert id="insertStudentList2">
        <foreach collection="studentList" item="student" separator=";">
      INSERT INTO student(name, gender, age) VALUES (#{student.name}, #{student.gender}, #{student.age})
        </foreach>
    </insert>
    SQL: INSERT INTO student(name, gender, age) VALUES ("Messi","male",34);
         INSERT INTO student(name, gender, age) VALUES ("Xavi","male",40),
    <!-- This method needs to be set MySQL Global properties for allowMultiQueries=true(Set this property,It needs to be attached to jdbc.url after) -->
    

bind

bind allows you to create a variable using an OGNL expression to reference it when needed

<!-- The example is directly moved to the official website -->
<select id="selectBlogsLike" resultType="Blog">
  <bind name="pattern" value="'%' + _parameter.getTitle() + '%'" />
  SELECT * FROM BLOG
  WHERE title LIKE #{pattern}
</select>

_ Parameter is a built-in parameter of Mybatis. Without @ Param annotation for method parameters:

If there is only one parameter, then_ Parameter represents this parameter

If there are multiple parameters, then_ parameter represents the map encapsulated by these parameters

Mybatis caching mechanism

Consider the following application scenario:

Sometimes when we query, we may execute the same query twice to get the same data. If the second query can directly reference the previous results, the query efficiency will be greatly increased

MyBatis has built-in a powerful transactional query cache mechanism, which can effectively improve query efficiency

MyBatis Cache mechanism includes L1 Cache and L2 Cache, and supports the implementation of Cache interface custom L2 Cache

L1 cache

  • Local cache. The default scope is SqlSession

    • The data queried during the same session will be put into the L1 cache. Later, if the same data needs to be obtained, it will be obtained directly from the L1 cache

      studentMapper.selectStudentById(1);
      studentMapper.selectStudentById(1);
      Execute the same query twice, Mybatis Only one query request was sent to the database
      

    • The L1 cache can change the scope through the globally configured localCacheScope

      If set localCacheScope = STATEMENT,
      Even if two identical queries, You also need to request the database twice, L1 cache invalidation.
      

  • The L1 cache cannot be closed, but it can be cleared manually

    • sqlSession.clearCache() manually clears the L1 cache

    • Flush cache attribute of CRUD tag manually clears L1 / L2 cache

      CRUD means create, retrieve, update and delete

      <!-- SELECT Do not refresh by default Cache -->
      <select id="selectStudentByName" resultType="com.shy.entity.Student" flushCache="false">
          SELECT *
          FROM student
          WHERE name = #{name}
      </select>
      
      <!-- INSERT/UPDATE/DELETE Default refresh Cache -->
      <insert id="insertStudent" useGeneratedKeys="true" keyProperty="id" flushCache="true">
          INSERT INTO student(name, gender, age)
          VALUES (#{student.name, jdbcType=NULL}, #{student.gender}, #{student.age})
      </insert>
      
  • Four situations of L1 cache invalidation

L2 cache

  • The scope of L2 cache is based on namespace, that is, a Mapper interface corresponds to a L2 cache

  • L2 cache needs to be configured manually

    • In the global configuration file, enable the L2 cache by cacheEnabled = true

      Although the default value is true, it is recommended to explicitly configure this property, even if it is the same as the default value

    • Add the < cache / > tag to the mapper.xml file that needs to use L2 cache to configure L2 cache

      • Properties of < cache > tag

    • The corresponding POJO should implement the Serializable interface

  • Workflow of L2 cache

    • All the data in the L2 cache comes from the L1 cache: when the SqlSession is closed, the data in the L1 cache corresponding to the session will be saved to the corresponding L2 cache
    • When Mybatis performs CRUD operation, it will look for cache in L2 cache / L1 cache in turn. If there is no corresponding cache, execute SQL statement
    // After the L2 cache is enabled, the cache hit rate (cache hit / lookup times) will be printed in the console for each CRUD operation
    [main] [com.shy.dao.StudentMapper]-[DEBUG] Cache Hit Ratio [com.shy.dao.StudentMapper]: 0.5
    
  • L2 cache supports manual deletion

    • Flush cache attribute of CRUD tag manually clears L1 / L2 cache
    • The useCache attribute of the SELECT tag can configure whether the query uses L2 cache
    • After the insert/update/delete operation, the L2 cache will be refreshed

Custom cache

MyBatis supports customizing the Cache by implementing the Cache interface

public class MyCache implements Cache {
    @Override
    public String getId() {
        return null;
    }

    @Override
    public void putObject(Object key, Object value) {}
    
    @Override
    public Object getObject(Object key) {
        return null;
    }

    @Override
    public Object removeObject(Object key) {
        return null;
    }

    @Override
    public void clear() {}

    @Override
    public int getSize() {
        return 0;
    }
}

However, in practice, we often choose a better third-party cache to integrate into Mybatis

stay github warehouse in Mybatis Many consolidation solutions can be found in

Cache mechanism process

SSM integration

Since Spring MVC has not been learned, this chapter will be added later

Mybatis Generator

Refer to for more details Official documentation of Mybatis Generator (English only)

  • Introduce dependency

    <dependency>
      <groupId>org.mybatis.generator</groupId>
      <artifactId>mybatis-generator-core</artifactId>
      <version>1.4.0</version>
    </dependency>
    
  • Writing the Mybatis Generator configuration file

    <?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE generatorConfiguration
            PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
            "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">
    
    <generatorConfiguration>
        <!-- Import related from classpath JAR But we can't use it maven Administration JAR package-->
        <!-- <classPathEntry location="/Program Files/IBM/SQLLIB/java/db2java.zip" />-->
    
        <!-- Read external.properties file-->
        <properties resource="dbconfig.properties"/>
    
        <!-- context For configuration in MBG Some properties of   targetRuntime appoint MBG Style of generated code(Specific reference documents)-->
        <context id="MysqlTables" targetRuntime="MyBatis3">
    
            <!--Specify how to connect to the target database-->
            <jdbcConnection driverClass="${jdbc.driver}"
                            connectionURL="${jdbc.url}"
                            userId="${jdbc.username}"
                            password="${jdbc.password}">
            </jdbcConnection>
    
            <!-- appoint MBG how JDBC Type and JAVA The policy when types are converted to each other. Here we use the default configuration,Refer to the documentation for details-->
            <javaTypeResolver >
                <property name="forceBigDecimals" value="false" />
            </javaTypeResolver>
    
            <!-- appoint JavaBean Location of generated packages and projects, as well as JavaBean Strategy when(Default configuration, Refer to the documentation for details)-->
            <!-- The path must be back, Otherwise nothing will be generated
            <javaModelGenerator targetPackage="com.shy.model" targetProject="src/main/java">
                <property name="enableSubPackages" value="true" />
                <property name="trimStrings" value="true" />
            </javaModelGenerator>
    
            <!-- appoint mapper The location of the packages and projects generated by the mapping file, And the strategy at the time of generation(Default configuration, Refer to the documentation for details)-->
            <sqlMapGenerator targetPackage="mapper"  targetProject="src/main/resources">
                <property name="enableSubPackages" value="true" />
            </sqlMapGenerator>
    
            <!-- appoint Mapper Interface generated type, Location of packages and works, And the strategy at the time of generation(Default configuration, Refer to the documentation for details)-->
            <javaClientGenerator type="XMLMAPPER" targetPackage="com.shy.mapper"  targetProject="src/main/java">
                <property name="enableSubPackages" value="true" />
            </javaClientGenerator>
    
            <!-- table Used to specify which tables to create from JavaBean as well as Mapper-->
            <!-- schema Which schema in the database  tableName Which table domainObjectName Generated Bean Name of -->
            <table schema="mybatis" tableName="student" domainObjectName="Student" >
                <!-- Refer to the documentation for some generation policy details-->
                <!--            <property name="useActualColumnNames" value="true"/>-->
                <!--            <generatedKey column="ID" sqlStatement="DB2" identity="true" />-->
                <!--            <columnOverride column="DATE_FIELD" property="startDate" />-->
                <!--            <ignoreColumn column="FRED" />-->
                <!--            <columnOverride column="LONG_VARCHAR_FIELD" jdbcType="VARCHAR" />-->
            </table>
            <table tableName="actor" domainObjectName="Actor"/>
    
        </context>
    </generatorConfiguration>
    
  • Run Mybatis Generator

    There are many ways to run MBG. Here we choose Running MBG from Java with an XML Configuration File

    Refer to other methods MyBatis Generator Core – Running MyBatis Generator

    // Generate code copy official website
    @Test
    public void test() throws Exception {
        List<String> warnings = new ArrayList<String>();
        boolean overwrite = true;
        File configFile = new File("src/main/resources/MBGConfig.xml"); // Load MBG configuration file
        ConfigurationParser cp = new ConfigurationParser(warnings);
        Configuration config = cp.parseConfiguration(configFile);
        DefaultShellCallback callback = new DefaultShellCallback(overwrite);
        MyBatisGenerator myBatisGenerator = new MyBatisGenerator(config, callback, warnings);
        myBatisGenerator.generate(null);
    }
    
  • Generated entity,mapper interface, mapper mapping file

    We can construct any query condition through the generated XXXexample class

    reference resources: Detailed explanation of example class in mybatis - Zhihu (zhihu.com)

    Use and resolution of Example class automatically generated by Mybatis_ Mo Liyu's blog - CSDN blog

CRUD operation with the generated mapper interface

As can be seen from mapper.xml, MBG constructs conditions through dynamic sql

<sql id="Example_Where_Clause">
    <!--
      WARNING - @mbg.generated
      This element is automatically generated by MyBatis Generator, do not modify.
      This element was generated on Sat Aug 28 08:43:33 CST 2021.
    -->
    <where>
        <foreach collection="oredCriteria" item="criteria" separator="or">
            <if test="criteria.valid">
                <trim prefix="(" prefixOverrides="and" suffix=")">
                    <foreach collection="criteria.criteria" item="criterion">
                        <choose>
                            <when test="criterion.noValue">
                                and ${criterion.condition}
                            </when>
                            <when test="criterion.singleValue">
                                and ${criterion.condition} #{criterion.value}
                            </when>
                            <when test="criterion.betweenValue">
                                and ${criterion.condition} #{criterion.value} and #{criterion.secondValue}
                            </when>
                            <when test="criterion.listValue">
                                and ${criterion.condition}
                                <foreach close=")" collection="criterion.value" item="listItem" open="(" separator=",">
                                    #{listItem}
                                </foreach>
                            </when>
                        </choose>
                    </foreach>
                </trim>
            </if>
        </foreach>
    </where>
</sql>
  • test
@Test
public void test16() throws IOException {
    SqlSessionFactory sqlSessionFactory = MybatisUtil.getSqlSessionFactory();
    SqlSession sqlSession = sqlSessionFactory.openSession();
    try {
        StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
        StudentExample studentExample = new StudentExample();

        // Criteria is used to encapsulate query conditions, which are internally connected with and
        StudentExample.Criteria studentExampleCriteria = studentExample.createCriteria();
        studentExampleCriteria.andGenderEqualTo("male");
        studentExampleCriteria.andAgeEqualTo(18);

        StudentExample.Criteria studentExampleCriteria1 = studentExample.createCriteria();
        studentExampleCriteria1.andNameLike("%e%");
        // or can be used to connect different Criteria
        studentExample.or(studentExampleCriteria1);

        studentMapper.selectByExample(studentExample);
    }finally {
        sqlSession.close();
    }
}
// sql executed by Mybatis:
//[com.shy.mapper.StudentMapper.selectByExample]-[DEBUG] ==>  Preparing: select id, name, gender, age, schoolId from student WHERE ( gender = ? and age = ? ) or( name like ? )
//[main] [com.shy.mapper.StudentMapper.selectByExample]-[DEBUG] ==> Parameters: male(String), 18(Integer), %e%(String)

Operation principle of Mybatis

This section is recommended for reference Mybatis source code analysis video

/**
     * DEBUG The mode is simple. Learn the operation principle of Mybatis
     */
@Test
public void test17() throws IOException {
    SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
    // 1. Get sqlSessionFactory object
    SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(Resources.getResourceAsStream("mybatis-config.xml"));
    // 2. Get sqlSession object
    SqlSession sqlSession = sqlSessionFactory.openSession();
    // 3. Get the proxy object of Mapper interface
    ActorMapper actorMapper = sqlSession.getMapper(ActorMapper.class);
    // 4. Execute CRUD operation
    List<Actor> actorList = actorMapper.selectActorList();
}

When using Mybatis for CRUD operation, the above four steps are often required. Next, combined with the timing diagram in the above video, briefly learn the operation principle of Mybatis in debug mode

  • Get sqlSessionFactory object

    Mybatis parses the information in the global configuration file and in each mapper.xml through the relevant parser

    (including MappedStatement objects that encapsulate sql statements and mappedregistry that encapsulates factories that create proxy objects for each mapper interface),

    Encapsulate and save this information into the Configuration object, and create the DefaultSqlSessionFactory object containing the Configuration object through the build() method

  • Get sqlSession object

    DefaultSqlSessionFactory creates DefaultSqlSession object, which contains Configuration object, Executor object, etc

    The Executor object is used to perform CRUD operations

  • Gets the proxy object for the Mapper interface

    The DefaultSqlSession object creates a dynamic proxy object mapperProxy corresponding to the mapper interface through the getMapper() method,

    It contains the sqlsession (which has an executor so that you can add, delete, modify and query) object

  • Perform CRUD operation

Mybatis plug-in development

Develop the first plug-in

Implement Interceptor interface → \rightarrow → write the @ Intercepts annotation to specify which methods to intercept which objects → \rightarrow → register the Interceptor

package com.shy.plugin;

import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.javassist.tools.reflect.Metaobject;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;
import org.apache.ibatis.session.ResultHandler;

import java.sql.Statement;
import java.util.Properties;
// Specifies which methods of which objects (Executor/ParameterHandler/ResultSetHandler/StatementHandler) are intercepted
@Intercepts({
    @Signature(type = StatementHandler.class, method = "parameterize", args = {Statement.class})
})
public class MyPlugin implements Interceptor {

    /**
* Only the target method of the target object will be intercepted
* @param invocation Contains some information about the target object and method
*/
    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        //        System.out.println("execute code before the original method is executed");
        System.out.println("invocation.getArgs() = " + invocation.getArgs());
        System.out.println("invocation.getMethod() = " + invocation.getMethod());
        System.out.println("invocation.getTarget() = " + invocation.getTarget());
        System.out.println("invocation.getClass() = " + invocation.getClass());
        // The metaObject obtained by SystemMetaObject.forObject encapsulates an originalObject, in this case, the implementation class of StatementHandler
        MetaObject metaObject = SystemMetaObject.forObject(invocation.getTarget());

        // metaObject.getValue get property
        Object boundSql = metaObject.getValue("boundSql");
        System.out.println("boundSql.sql = " + boundSql);
        //        metaObject.setValue("key","value");  Properties can be set, provided that there is a set method
        Object result = invocation.proceed(); // Execute the original method of the original object
        //        System.out.println("execute code after the original method is executed");

        return result;
    }

    /**
* Create proxy object for target
* @param target Target object
* @return If target is the object to be intercepted, the proxy object of target is returned. Otherwise, target is returned directly
*/
    @Override
    public Object plugin(Object target) {
        Object wrap = Plugin.wrap(target, this); // wrap is to create a dynamic proxy object for the target
        return wrap;
    }

/**
* Set the properties in the < plugin > tag
*/
    @Override
    public void setProperties(Properties properties) {
        Interceptor.super.setProperties(properties);
    }
}
<configuration>
    <plugins>
        <plugin interceptor="com.shy.plugin.MyPlugin">
            <property name="username" value="root"/>
            <property name="password" value="root"/>
        </plugin>
    </plugins>
</configuration>

Before writing a plug-in, you must have a general understanding of the operation principle of Mybatis, otherwise its operation process may be damaged

In particular, you should be familiar with the creation time of the four objects Executor/ParameterHandler/ResultSetHandler/StatementHandler and the general functions of some methods

The plug-in written will be created in SqlSessionFactory through configuration.addInterceptor(interceptorInstance); Encapsulated in a Configuration object

In addition, after the Executor/ParameterHandler/ResultSetHandler/StatementHandler is created, it will call the interceptorChain.pluginAll() method to determine which objects to create a proxy for according to the registered Interceptor

If multiple plug-ins intercept the same object, the proxy object of the proxy will be created. Therefore, when executing the method, execute SecondPlugin first, and then execute the code inserted by FirstPlugin

PageHelper third party paging plug-in

Briefly learn how to use PageHelper. For details, please refer to: MyBatis paging plug-in PageHelper official documentation

  • Introduce dependency

    <dependency>
        <groupId>com.github.pagehelper</groupId>
        <artifactId>pagehelper</artifactId>
        <version>5.2.1</version>
    </dependency>
    
  • Register plug-ins

    PageHelper is essentially an Interceptor that uses dynamic proxies to add paging logic

    <configuration>
        <plugins>
            <plugin interceptor="com.github.pagehelper.PageInterceptor"></plugin>
        </plugins>
    </configuration>
    
  • Simple use

    /**
         * Test PageHelper
         */
    @Test
    public void test() throws IOException {
        SqlSessionFactory sqlSessionFactory = MybatisUtil.getSqlSessionFactory();
        SqlSession sqlSession = sqlSessionFactory.openSession();
        try {
            ActorMapper actorMapper = sqlSession.getMapper(ActorMapper.class);
            // Query the first page, 5 pieces of data per page
            PageHelper.startPage(1,5); 
            List<Actor> actorList = actorMapper.selectActorList();
            for (Actor actor : actorList) {
                System.out.println(actor.toString());
            }
            // The PageInfo class encapsulates information about paging
            PageInfo pageInfo = new PageInfo(actorList,5);
            // The number of pages that the navigation bar should display (e.g. 3 4 5 6 7)
            int[] navigatepageNums = pageInfo.getNavigatepageNums(); 
        }finally {
            sqlSession.close();
        }
    }
    /**
    Actor{ActorId=16, ActorChineseName='Li Xuejian ', ActorOriginName='Xuejian Li', ActorGender = 'male'}
    Actor{ActorId=17, ActorChineseName='Chen Daoming ', ActorOriginName='Daoming Chen', ActorGender = 'male'}
    Actor{ActorId=18, ActorChineseName='Jason Fleming ', ActorOriginName='Jason Flemyng', ActorGender = 'male'}
    Actor{ActorId=19, ActorChineseName='Matthew McConaughey ', actor origin name ='matthew McConaughey', actor gender = 'male'}
    Actor{ActorId=20, ActorChineseName='Charlie Hannam ', ActorOriginName='Charlie Hunnam', ActorGender = 'male'}
    **/
    

Mybatis batch Sql

I'm learning Dynamic sql:foreach It was introduced to batch process Sql statements by splicing Sql, but sometimes the database does not support too long statements

In fact, when creating a SQLSession, we can specify ExecutorType=Batch to batch process Sql

  • Batch processing mode

    @Test
    public void test20() throws IOException {
        SqlSessionFactory sqlSessionFactory = MybatisUtil.getSqlSessionFactory();
        SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);
        long start = System.currentTimeMillis();
        try {
            StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
            for (int i = 0; i < 100; i++) {
                studentMapper.insertStudent(new Student("test","male",18));
            }
            sqlSession.commit();
            long end = System.currentTimeMillis();
            System.out.println("execution time: " + (end - start) + "ms");
        }finally {
            sqlSession.close();
        }
    }
    
    In this way commit Only after sql, I've been preparing parameters before
    [main] [com.shy.dao.StudentMapper.insertStudent]-[DEBUG] ==>  Preparing: INSERT INTO student(name, gender, age) VALUES (?, ?, ?)
    [main] [com.shy.dao.StudentMapper.insertStudent]-[DEBUG] ==> Parameters: test(String), male(String), 18(Integer)
    [main] [com.shy.dao.StudentMapper.insertStudent]-[DEBUG] ==> Parameters: test(String), male(String), 18(Integer)
    [main] [com.shy.dao.StudentMapper.insertStudent]-[DEBUG] ==> Parameters: test(String), male(String), 18(Integer)
    [main] [com.shy.dao.StudentMapper.insertStudent]-[DEBUG] ==> Parameters: test(String), male(String), 18(Integer)
    execution time: 3642ms
    
  • Original way

    [main] [com.shy.dao.StudentMapper.insertStudent]-[DEBUG] ==>  Preparing: INSERT INTO student(name, gender, age) VALUES (?, ?, ?)
    [main] [com.shy.dao.StudentMapper.insertStudent]-[DEBUG] ==> Parameters: test(String), male(String), 18(Integer)
    [main] [com.shy.dao.StudentMapper.insertStudent]-[DEBUG] <==    Updates: 1
    [main] [com.shy.dao.StudentMapper.insertStudent]-[DEBUG] ==>  Preparing: INSERT INTO student(name, gender, age) VALUES (?, ?, ?)
    [main] [com.shy.dao.StudentMapper.insertStudent]-[DEBUG] ==> Parameters: test(String), male(String), 18(Integer)
    [main] [com.shy.dao.StudentMapper.insertStudent]-[DEBUG] <==    Updates: 1
     execution time: 7153ms
    

Mybatis type processor

The type handler is used to map JDBC types (such as varchar) and Java types (such as String) to complete type conversion, so as to encapsulate field values into JavaBean s

That is, you can customize how database fields are mapped to Java attributes

TypeHandler of enumeration type

The default TypeHandler used by Enum is EnumTypeHandler

// EnumTypeHandler source code
public class EnumTypeHandler<E extends Enum<E>> extends BaseTypeHandler<E> {
    //...
    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, E parameter, JdbcType jdbcType) throws SQLException {
        // Save the name of the enumeration in the database
        if (jdbcType == null) {
            ps.setString(i, parameter.name());
        } else {
            ps.setObject(i, parameter.name(), jdbcType.TYPE_CODE);
        }
        //...
    }

In addition, we can also change Enum's TypeHandler to EnumOrdinalTypeHandler, and the ordinal of the enumeration will be saved in the database

<typeHandlers>
    <!-- javaType inform Handler To process java type com.shy.entity.Status Enumeration class for custom-->
    <typeHandler handler="org.apache.ibatis.type.EnumOrdinalTypeHandler" javaType="com.shy.entity.Status"/>
</typeHandlers>
// com.shy.entity.Status
public enum Status {
    LOGIN, LOGOUT
}
// EnumOrdinalTypeHandler source code
public class EnumOrdinalTypeHandler<E extends Enum<E>> extends BaseTypeHandler<E> {
    //...
    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, E parameter, JdbcType jdbcType) throws SQLException {
        // Save the sequence number of the enumeration in the database
        ps.setInt(i, parameter.ordinal());
    }
    //...
}

Custom TypeHandler

// If we change the Status enumeration as shown below, we now want to save the Status code in the database instead of the name / serial number
// And you want to return the corresponding enumeration object through the code in the database
// We need to use custom TypeHandler 
public enum Status {
    LOGOUT(404,"Logout"), LOGIN(200,"Sign in");
    private Integer code;
    private String msg;

    Status(Integer code, String msg) {
        this.code = code;
        this.msg = msg;
    }

    public Integer getCode() {
        return code;
    }

    public void setCode(Integer code) {
        this.code = code;
    }

    public String getMsg() {
        return msg;
    }

    public void setMsg(String msg) {
        this.msg = msg;
    }

    // Returns an enumeration object according to the status code
    public static Status getStatusByCode(Integer code){
        if(code == 404) {
            return LOGOUT;
        }else{
            return LOGIN;
        }
    }
}

Customize TypeHandler. You can implement TypeHandler interface or inherit BaseTypeHandler

public class StatusEnumTypeHandler implements TypeHandler {
    /**
* Defines how Java types are saved into the database
*/
    @Override
    public void setParameter(PreparedStatement ps, int i, Object parameter, JdbcType jdbcType) throws SQLException {
        Status status = (Status) parameter;
        ps.setString(i,status.getCode().toString()); // Save the status code, i is index
    }

    /**
* Defines how database fields map to Java types
*/
    @Override
    public Object getResult(ResultSet rs, String columnName) throws SQLException {
        int code = Integer.parseInt(rs.getString(columnName));
        // Returns the Status object according to the Status code
        Status status = Status.getStatusByCode(code);
        return status;
    }

    @Override
    public Object getResult(ResultSet rs, int columnIndex) throws SQLException {
        int code = Integer.parseInt(rs.getString(columnIndex));
        Status status = Status.getStatusByCode(code);
        return status;
    }

    @Override
    public Object getResult(CallableStatement cs, int columnIndex) throws SQLException {
        int code = Integer.parseInt(cs.getString(columnIndex));
        Status status = Status.getStatusByCode(code);
        return status;
    }
}

Register the TypeHandler

<typeHandlers>
    <typeHandler handler="com.shy.typeHandler.StatusEnumTypeHandler" javaType="com.shy.entity.Status" />
</typeHandlers>

Test insertion

@Test
public void test22() throws IOException {
    SqlSessionFactory sqlSessionFactory = MybatisUtil.getSqlSessionFactory();
    try(SqlSession sqlSession = sqlSessionFactory.openSession()){
        StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
        Student student = new Student("Russell","male",22,Status.LOGIN);
        studentMapper.insertStudent(student);
        sqlSession.commit();
    }
}

Test query

@Test
public void test22() throws IOException {
    SqlSessionFactory sqlSessionFactory = MybatisUtil.getSqlSessionFactory();
    try(SqlSession sqlSession = sqlSessionFactory.openSession()){
        StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
        Student Russell = studentMapper.selectStudentById(20);
        System.out.println(Russell.toString());
    }
}
// Student{id=20, name='Russell', gender='male', age=22, status=LOGIN} 

Postscript to Mybatis

This part is the problems and knowledge gained in the actual combat after the systematic study of Mybatis

Try with resource close session

When closing the session before, use try finally to close the session, but exceptions may occur in finally

SqlSession sqlSession = sqlSessionFactory.openSession();
try {
   // .......
}finally {
    sqlSession.close(); // Exceptions may occur
}

After JDK7, the try with resource syntax sugar is introduced. Mybatis official recommends using this method to close the session

try(SqlSession sqlSession = sqlSessionFactory.openSession()){ // The bottom layer automatically calls close to close
 	// .......
}

Detailed reference: Try with resource syntax of Java Foundation_ The blog called black brother in the Jianghu - CSDN blog

Tags: Java MySQL Mybatis Back-end

Posted on Wed, 08 Sep 2021 17:09:59 -0400 by DJP1986