Pretend to be Xiaobai’s key to learning mybatis (I)

Time:2021-9-17

I found that I still didn’t understand mybatis well at work, so I planned to learn mybatis again.

brief introduction

Mybatis is an excellent persistence layer framework, which supports custom SQL, stored procedures and advanced mapping. Mybatis eliminates almost all JDBC code and the work of setting parameters and obtaining result sets. Mybatis can configure and map primitive types, interfaces and Java POJOs (plain old Java objects) to records in the database through simple XML or annotations.

First, mybatis defines itself as the persistence layer framework. What is the persistence layer? In short, it is the layer communicating with the database, that is, the Dao (data access object) layer in MVC mode. The code of the Dao layer is responsible for turning the business code into the operation of database tables. As for why it is called the persistence layer, I think it has something to do with the persistence characteristics of the database!

Before the Dao layer did not have any framework, we directly used the native JDBC to manipulate the database data, splice SQL, set parameters and obtain the repeatability of the result set, which is often very boring, but JDBC is good enough in terms of design ideas, so that users do not have to care about which database and take different operations, JDBC is a set of interfaces, and the corresponding implementation classes are provided by major database manufacturers. Therefore, it is unlikely to fully customize the operation, so the design idea of JDBC is a little broader.
Pretend to be Xiaobai's key to learning mybatis (I)
But we want it to be simpler, so if you watch the video to learn, after basically learning JDBC, you will talk about how to package a tool class jdbcutils to avoid repeated code writing, but this is not a pain point for Java programmers, right? The Java community also paid attention to this problem and began to expand and upgrade JDBC. This is the ORM framework such as mybatis, hibernate, spring data JPA, etc.
Pretend to be Xiaobai's key to learning mybatis (I)

Wait, you just mentioned another term, ORM framework. What is ORM framework? ORM object relational mapping is object relational mapping. It sounds so abstract! Don’t worry. Listen to me carefully. Java is an object-oriented language. The database we commonly use now is a relational database (tables are the main form). The idea of ORM is whether we can map tables to objects? A data record is an object.

Therefore, mybatis is an excellent persistence layer and ORM framework. It is extended and encapsulated on the basis of JDBC, which eliminates almost all JDBC code, setting parameters and obtaining result sets, and greatly simplifies the development of persistence layer code.

To simplify the development of persistence layer code, be simple, be simple, we all like simple things.

How to learn a technology?

Generally speaking, to learn a framework, it’s best to go to the official website. In the past, I was fast and went to station B to find the video. Now I find that the official tutorial written by mybatis is very good. What I like more is the Chinese version:
Pretend to be Xiaobai's key to learning mybatis (I)
So good that I think it’s still necessary to write this blog. But after much consideration, I still intend to write official documents to match my understanding and make my knowledge more systematic.

preparation

To use mybatis, we must first introduce mybatis. Mybatis is extended on the basis of native JDBC, so we need to introduce the corresponding database driver (database driver is JDBC implemented by database manufacturers). In this article, we use MySQL and druid to manage database connection. In this article, we still use Maven to build the project:
The corresponding dependencies are as follows:

<dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.6</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.2.5</version>
        </dependency>
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-api</artifactId>
            <version>1.7.30</version>
        </dependency>
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-log4j12</artifactId>
            <version>1.7.30</version>
            <scope>test</scope>
        </dependency>

Slf4j is a logging framework. The output information will be more detailed. It is recommended to introduce it.

If you can’t use maven

I suggest you learn Maven. See my blog: Maven learning notes, a very easy to understand introduction.

If you don’t want to learn Maven and want to use jar package mode, it’s OK. My blog is so considerate, ha ha.

  1. First, go to the official website of mybatis.

Pretend to be Xiaobai's key to learning mybatis (I)

  1. Pretend to be Xiaobai's key to learning mybatis (I)

3.Pretend to be Xiaobai's key to learning mybatis (I)

  1. Pretend to be Xiaobai's key to learning mybatis (I)

The first mybatis program

First, we need to create a configuration file

Pretend to be Xiaobai's key to learning mybatis (I)
Pretend to be Xiaobai's key to learning mybatis (I)

By the way, we also need to build a table. If you don’t know how to build a table, this article may not be suitable for you. The table I built is called blog.
I won’t let go of the table statements. I recently re learned SSM and wrote some examples, which are all put on GitHub. The following is the link:

  • https://github.com/CXK6013/SSM

Roughly explain the configuration file

<?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>
    <!-- Load profile -- >
    <properties resource="jdbc.properties"/>
    <!-- Specify the default environment. Generally, we have three sets of environments, dev development, UAT testing, prod production -- >
    <environments default="development">
        <environment id="development">
            <!--  Set the management mode of transaction manager -- >
            <transactionManager type="JDBC"/>
            <!--  Set the association method of data source connection to data pool -- >
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.dev.driver}"/>
                <property name="url" value="${jdbc.dev.url}"/>
                <property name="username" value="${jdbc.dev.username}"/>
                <property name="password" value="${jdbc.dev.password}"/>
            </dataSource>
        </environment>
    </environments>  
    <mappers>
        <!-- Set the scanned XML. Org / example / mybatis is the full class name of the package. This blogmapper.xml will say -- >
        <mapper resource="org/example/mybatis/BlogMapper.xml"/>
    </mappers>
</configuration>

Hello World

Create a new interface

public interface BlogMapper {
    Blog selectBlog(Long id);
}

Create a new XML

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--  The namespace here must be the full class name of blogmapper -- >
<mapper namespace = "org.example.mybatis.BlogMapper">
      <!--  Mybatis will automatically encapsulate the queried records as objects of the type set by resulttype. Here you can experience the idea of ORM -- >
    <select id = "selectBlog" resultType = "org.example.mvc.entity.Blog">
            select * from Blog where id = #{id}
    </select>
</mapper>

Test code

public static void main(String[] args) throws IOException {
        String resource = "mybatis-config.xml";
        //Read configuration file
        InputStream inputStream = Resources.getResourceAsStream(resource);
        //Build a sqlsessionfactory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        //Open a session
        SqlSession sqlSession = sqlSessionFactory.openSession();
        //Load the specified interface
        BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);
        //Calling the method in the interface is equivalent to executing the query statement in the corresponding mapper
        Blog blog = blogMapper.selectBlog(1L);
        //Print query statement
        System.out.println(blog);
    }

Execution results:
Pretend to be Xiaobai's key to learning mybatis (I)

To sum up

The first program may be dizzy. Follow it first, and then let’s explain what mybatis has done:
Pretend to be Xiaobai's key to learning mybatis (I)
How is blogmaper.java associated with blogmapper.xml? Remember the configuration above?
Pretend to be Xiaobai's key to learning mybatis (I)
Pretend to be Xiaobai's key to learning mybatis (I)
In essence, it is a dynamic proxy. The implementation class of the interface is created at runtime. If you don’t know what a dynamic proxy is, please refer to:

  • Agent mode – AOP introduction

Basic process of manipulating database with mybatis:

  • New interface
  • When creating XML, you should pay attention to the following constraints:

Pretend to be Xiaobai's key to learning mybatis (I)
It doesn’t matter if you don’t remember. You can copy it on the official website of mybatis:
Pretend to be Xiaobai's key to learning mybatis (I)
Pretend to be Xiaobai's key to learning mybatis (I)

  • In the mapper tab of the configuration file, open the scan tab, associated interface and XML file (which can be set in batch, which will be described later)
  • Then, the method of sqlsession object to obtain the specified interface can call the corresponding SQL statement.

After careful experience, is this clearer than the native JDBC.

Transfer parameters

When we call the selectblog in blogmapper above, we only write parameters in the interface, and mybatis can automatically replace #{id} with the parameters we pass. Is it very powerful! This method also supports multiple parameters, but the SQL requires seven or eight parameters. What should I do? Write seven or eight parameters on the interface method? In fact, it’s OK. What about an array or list? Can mybatis traverse it? Mybatis: of course.
For the database, query is always the most concerned, and the select tag has the most attributes, as shown in the following code:

<select id="selectPerson"
        parameterType="int"
        parameterMap="deprecated"
        resultType="hashmap"
        resultMap="personResultMap"
        flushCache="false"
        useCache="true"
        timeout="10"
        fetchSize="256"
        statementType="PREPARED"
        resultSetType="FORWARD_ONLY">
</select>

These attributes are described in detail on the mybatis official website:
Pretend to be Xiaobai's key to learning mybatis (I)
Pretend to be Xiaobai's key to learning mybatis (I)
We only introduce the commonly used ones here. In passing parameters, we introduce the parametertype attribute, which is optional. Mybatis can infer the passed parameters through the typehandler to replace the #{id} (which we will uniformly call placeholders below) in our SQL statements.
Note that for a single parameter, the parameter name in the placeholder does not need to be consistent with the parameter name in the method. You need to enable multiple parameters@Param[email protected] The attribute value in param should be consistent with the parameter name in the placeholder, otherwise mybatis gets two parameter values and cannot infer which value should be used to replace the placeholder.
Pretend to be Xiaobai's key to learning mybatis (I)

If you want to use objects

Directly transfer the object (the same is true for map), and then write the corresponding attribute name in the placeholder (key in map):

<!--  Note that at this time, the property name written in the placeholder must have a get method, otherwise the call will fail -- >
    <select id="selectBlogByObj"   resultType="org.example.mvc.entity.Blog"  >
            select * from Blog where id = #{id}
    </select>

Test code:

public static void main(String[] args) throws IOException {
        selectByObj();
    }
    private static void selectByObj() throws IOException {
        BlogMapper blogMapper = getMapper(BlogMapper.class);
        Blog blog = new Blog();
        blog.setId(1L);
        blog.setName("aa");
        blog = blogMapper.selectBlogByObj(blog);
        System.out.println(blog);
    }
    public static <T> T getMapper(Class<T> t) throws IOException {
        String resource = "mybatis-config.xml";
        //Read configuration file
        InputStream inputStream = Resources.getResourceAsStream(resource);
        //Build a sqlsessionfactory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        //Open a session
        SqlSession sqlSession = sqlSessionFactory.openSession();
        return (T) sqlSession.getMapper(t);
    }

Operation results:
Pretend to be Xiaobai's key to learning mybatis (I)
The following describes how to use it. The getmapper and main methods will not be pasted, but the corresponding methods will be written.

aggregate

Traverse list, array and map

Collections are often used to build in statements, so how to traverse in XML? Traverse through the foreach tag, as follows:

   <select id = "selectBlogByList" resultType="org.example.mvc.entity.Blog">
        select * from Blog where id in
        <foreach collection="list" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
    </select>

This foreach tag just maps the foreach in Java to XML. Item is an iteration element and list is a collection of iterations. Open and close are used to specify the string formed by splicing and the separator of collection item iteration. In other words, assuming that the list I passed in has two elements, 1 and 2, the resulting SQL statement will be as follows:

 select * from Blog where id in (1,2)

Look how smart mybatis is. If the collection type is map, index is the key and item is the value.
What I found above may be multiple objects. How should I receive them in mybatis? Only change the method corresponding to the tag ID toList<Blog>All right.

  List<Blog> selectBlogByArray(Long[] idLongArray);

By default, collection is pure lowercase of the collection name. For example, if the type of list is passed in, list should be written in the collection. If it is an array, you should write array. If the parameter name is specified with @ param, write the parameter name specified in @ param.

Return type

Map

We have talked about returning one piece of data and returning multiple pieces of data above. It seems a little counter intuitive to receive the return type with map. In fact, it is also completely in line with intuition. Let’s use map to receive and try:

<select id = "selectAllReturnMap" resultType="map">
     select * from Blog
</select>

Methods declared in the interface:

   Map<String,Object> selectAllReturnMap();
 private static void selectAllReturnMap() throws IOException {
        BlogMapper blogMapper = getMapper(BlogMapper.class);
        System.out.println(blogMapper.selectAllReturnMap());
    }

The test results are as follows:
Pretend to be Xiaobai's key to learning mybatis (I)
The main idea is to return three. You use one to receive, but you can’t receive.
Maybe at this time, students will ask, can’t map hold multiple groups of values? Why can’t we receive three records? Let’s think about whether the keys of the map can’t be repeated. The keys of the three records are attribute names. In a sense, a map is an object, key is the attribute name, and value is the attribute value. So we should take it this way:

List<Map<String,Object>> selectAllReturnMap();

Test results:
Pretend to be Xiaobai's key to learning mybatis (I)

{placeholder} vs ${placeholder}

The placeholders for the replacement parameters we used above all start with the # sign. Mybatis will replace the #{parameter name} with the parameter value passed when we call the corresponding method of the interface. Generally, we call them placeholders with the # sign. In fact, another one starts with $. We call it a dollar placeholder. By default, when using #{} parameter syntax, mybatis creates a placeholder for the Preparedstatement parameter and sets the parameter safely through the placeholder (just like using?).
Next, let’s use an example to feel the difference between the two. First, we configure the log, and the log outputs the real executed SQL, which is convenient for us to analyze the problem
Pretend to be Xiaobai's key to learning mybatis (I)
We introduce corresponding dependencies as required:

 <dependency>
            <groupId>org.apache.logging.log4j</groupId>
            <artifactId>log4j-core</artifactId>
            <version>2.12.1</version>
        </dependency>
        <dependency>
            <groupId>org.apache.logging.log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>2.14.0</version>
            <type>pom</type>
        </dependency>
        <dependency>
            <groupId>commons-logging</groupId>
            <artifactId>commons-logging</artifactId>
            <version>1.2</version>
        </dependency>

Then create a file log4j.properties in resources. The contents of the file are as follows:

#Global log configuration
log4j.rootLogger=DEBUG,ERROR, stdout
#The mybatis log configuration will output the details under the execution package
log4j.logger.org.mybatis.example=DEBUG 
#Console output
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n

This is just a paste. Introducing the log is not the content of this article. We use the log to study the differences between well mark placeholders and dollar placeholders.
Then open the log in the configuration file:

   <settings>
        <setting name="logImpl" value="LOG4J"/>
    </settings>

Pretend to be Xiaobai's key to learning mybatis (I)

Number placeholder

Code in XML:

  <select id="selectByMark"  resultType = "org.example.mvc.entity.Blog">
             select * from Blog where id = #{id} and name =  #{name}
    </select>

Code in blogmapper:

List<Blog> selectByMark(@Param("id") String id,@Param("name") String name);

Test code:

  private static void markVsDollar() throws IOException {
        BlogMapper blogMapper = getMapper(BlogMapper.class);
        blogMapper.selectByMark("1","aa; delete Blog;");
    // blogMapper.selectByDollar("1","aa; delete Blog");
    }

Execution results:

DEBUG [main] - Created connection 551479935.
DEBUG [main] - Setting autocommit to false on JDBC Connection [[email protected]]
DEBUG [main] - ==>  Preparing: select * from Blog where id = ? and name = ?
DEBUG [main] - ==> Parameters: 1(String), aa or 1 = 1(String)
DEBUG [main] - <==      Total: 0

So the final SQL statement is:select * from Blog where id = '1' and name = 'aa or 1 = 1'
There is no such data in our database, so no such data can be found.

$number placeholder

Code in XML:

  <select id="selectByDollar"  resultType = "org.example.mvc.entity.Blog">
             select * from Blog where id = ${id} and name = ${name}
    </select>
List<Blog> selectByDollar(@Param("id") String id, @Param("name") String name);

Test code:

  private static void markVsDollar() throws IOException {
        BlogMapper blogMapper = getMapper(BlogMapper.class);
        blogMapper.selectByDollar("1","1 or  1 = 1;");
    }

Execution results:

DEBUG [main] - Created connection 1327006586.
DEBUG [main] - Setting autocommit to false on JDBC Connection [[email protected]]
DEBUG [main] - ==>  Preparing: select * from Blog where id = 1 and name = 1 or 1 = 1;
DEBUG [main] - ==> Parameters: 
DEBUG [main] - <==      Total: 2

Please carefully compare the two actually executed SQL statements. One parameter has a value, has a parameter type, and the other does not. It is directly replaced as is.
The second is actually called SQL injection, which finds records that should not be found. However, the $placeholder is not useless. For example, when sorting, it is sorted according to the fields passed by the front end:

ORDER BY ${columnName}

Some students may say that there seems to be an injection risk. If you don’t follow the agreed fields, wear one casually, and then report an error. To avoid this problem, you can make a judgment in Java. Only the agreed fields will be passed to the really executed SQL.

To sum up

The pound sign placeholder will be automatically escaped to us. Judge whether it is escaped according to the type. If it is a string type, mybatis will automatically quote the parameters for us. If it is a number type, it will not be added.
The dollar placeholder is replaced as is, which has the risk of SQL injection, but sometimes when we don’t want mybatis to spell quotation marks for us, for example, sort according to the fields passed by the front end, so the $placeholder should be used with caution.

Update, delete, insert, call stored procedure

The implementation of data change statements insert, update and delete is very close:

<insert
  id="insertAuthor"
  parameterType="domain.blog.Author"
  flushCache="true"
  statementType="PREPARED"
  keyProperty=""
  keyColumn=""
  useGeneratedKeys=""
  timeout="20">

<update
  id="updateAuthor"
  parameterType="domain.blog.Author"
  flushCache="true"
  statementType="PREPARED"
  timeout="20">

<delete
  id="deleteAuthor"
  parameterType="domain.blog.Author"
  flushCache="true"
  statementType="PREPARED"
  timeout="20">

The parametertype is used in the same way as the parametertype of the select tag. We mainly talk about statementtype and usegeneratedkeys.
Statementtype: optional statement, prepared or callable. This will make mybatis use statement, Preparedstatement or callablestatement respectively. The default value is prepared.
Students who do not have a solid foundation in JDBC may ask what statement, Preparedstatement and callablestatement are?
In short, the statement interface provides basic methods for executing statements and obtaining results; The Preparedstatement interface adds a method for processing input parameters;
The callablestatement interface adds methods for calling stored procedure kernel functions and processing output parameters.

Usegeneratedkeys usage example

<!--
            Usegeneratedkeys enable receive PK
            On which property of the object does the keyproperty place the returned primary key
 -->
<insert id="insertEntity"  useGeneratedKeys="true" keyProperty="id" >
     insert into Blog (name)
     values (#{name})
</insert>

Java code:

private static void insertEntity() throws IOException {
        String resource = "mybatis-config.xml";
        //Read configuration file
        InputStream inputStream = Resources.getResourceAsStream(resource);
        //Build a sqlsessionfactory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        //Open a session
        SqlSession sqlSession = sqlSessionFactory.openSession();
        BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);
        Blog blog = new Blog();
        blog.setName("aaa");
        blogMapper.insertEntity(blog);
        //Remember to submit
        sqlSession.commit();
        //Close the session, otherwise the addition will not succeed
        sqlSession.close();
        System.out.println(blog);
    }

Therefore, we need to modify the above getmapper method. In actual use, that is, after the integration of SSM (spring MVC mybatis), after the introduction of database connection pool, the connection will be returned to the connection pool after use. We won’t modify it here.
Test results:
Pretend to be Xiaobai's key to learning mybatis (I)
Database results:
Pretend to be Xiaobai's key to learning mybatis (I)
This can be done if the database supports self incrementing primary key, but not if it does not support it.

Common labels

When we introduced that the parameter type is a collection, we have already introduced the traversal tag,<foreach>But if there is a loop, how can there be no switch. So how do you move logical judgments into XML? Turn judgment into a label? How many labels do you have to remember? This is what ibatis, the predecessor of mybatis, did. The change made by mybatis is to complete logical judgment through ognl expression and if tag.

Introduction to ognl expressions

Ognl (object graph Navigation Language) is an expression language (EL). In short, it is a simplified Java attribute value language. It is widely used in the traditional struts framework and mybatis. Arthas, an open-source Java diagnostic tool, also uses it for expression filtering. Its simple but flexible design has derived many high-level games.

Here we briefly introduce the common. For details, please refer to the official ognl document: http://commons.apache.org/pro…

Ognl is designed to simplify the value of Java attributes. For example, if you want to reference an object in the current context by name, you can type it directly. If you want to reference the attribute title of the object text in the current context, you can type text.title. If you call the method of an object, you can directly use the object. Method name ().
Access the array directly through the array name [index].

The judgment parameter is not null and the collection size is greater than 0

 <select id="selectBlogByList" resultType="org.example.mvc.entity.Blog">
        select * from Blog where id in
        <if test = "list != null and list.size() > 0">
            <foreach collection="list" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
    </select>

The parameter value is equal to a string

<select id="selectByMark" resultType="org.example.mvc.entity.Blog">
             select * from Blog where id = #{id}
             < if test = '"Zhang San". Equals (name) >
                 and name =  #{name}
             </if>
    </select>

choose、when、otherwise

Sometimes, we don’t want to use all the conditions, but just want to choose one from multiple conditions. In this case, mybatis provides a choose element, which is a bit like a switch statement in Java. In order to enrich our applicable scenarios, we added two fields to the blog table: title and status. Previously, there were only ID and name.
Example:

   <select id="testChoose" resultType="org.example.mvc.entity.Blog">
         select * from Blog  where status = 'active'
        <choose>
            <when test="title != null">
                AND title like #{title}
            </when>
            <when test=" name != null ">
                AND name like #{name}
            </when>
            <otherwise>
                AND status != 'active'
            </otherwise>
        </choose>
    </select>

If title is not empty, search title. If name is not empty, search name vaguely. If both are empty, search for status that is not active.

where、trim、set

We have solved the problem of splicing SQL in the previous example. Let’s take a look at the following SQL:

  select * from Blog where id in
        <if test = "list != null and list.size() > 0">
            <foreach collection="list" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>

If the condition does not hold, the SQL will become like this:

select * from Blog where id in

Obviously, this will report an error. In order to avoid this situation, should we splice a 1 = 1 after where? Then the SQL becomes as follows:

  select * from Blog where  1 = 1
        <if test = "list != null and list.size() > 0">
         id in
            <foreach collection="list" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>

But it’s not elegant. Mybatis provides<where>Tag, the where element inserts the “where” clause only if the child element returns anything. Moreover, if clause starts with the “and” or “, where element removes them. So our SQL can become like this:

   <select id="selectBlogByList" resultType="org.example.mvc.entity.Blog">
        select * from Blog 
        <where>    
            <if test = "list != null and list.size() > 0">
                id in
                <foreach collection="list" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
    </where>
    </select>

If the where element is different from what you expect, you can also customize the function of the where element by customizing the trim element. For example, the user-defined trim element equivalent to the where element is:

<trim prefix="WHERE" prefixOverrides="AND |OR ">
  ...
</trim>

The prefixoverrides property ignores text sequences separated by pipe characters (note that spaces are necessary in this example). The above example will remove all the contents specified in the prefixoverrides attribute and insert the contents specified in the prefix attribute.
A similar solution for dynamically updating statements is called set. The set element can be used to dynamically include columns that need to be updated and ignore other columns that do not need to be updated. For example:

<update id="updateAuthorIfNecessary">
  update Author
    <set>
      <if test="username != null">username=#{username},</if>
      <if test="password != null">password=#{password},</if>
      <if test="email != null">email=#{email},</if>
      <if test="bio != null">bio=#{bio}</if>
    </set>
  where id=#{id}
</update>

In this example, the set element will dynamically insert the set keyword at the beginning of the line and delete additional commas (which are introduced when assigning values to columns using conditional statements).
Take a look at the custom trim element equivalent to the set element:

<trim prefix="SET" suffixOverrides=",">
  ...
</trim>

Note that we have overridden the suffix value setting and customized the prefix value.

Bind tag

The bind element allows you to create a variable outside the ognl expression and bind it to the current context:

<select id="selectByMark" resultType="org.example.mvc.entity.Blog">
        select * from Blog where id = #{id}
         < if test = '"Zhang San". Equals (name) >
             <bind name="pattern" value="'%' + name + '%'" />
             and name =  #{pattern}
         </if>
</select>

alias

Remember the mappers we configured in the configuration file? In fact, we can also batch configure and batch associate:

<mappers>
        <!-- Batch Association -- >
        <package name = "org.example.mybatis"/>
 </mappers>

Our resulttype writes the full class name. Why not write so many? Yes, you can configure it in the configuration file as follows:

  <typeAliases>
        <package name="org.example.mvc.entity"/>
    </typeAliases>

Pretend to be Xiaobai's key to learning mybatis (I)
Note that this order can only be in the order of properties, settings and typealiases.
Then we write the value in resulttype and write the class name.

Type converter

Now let’s focus on mybatis, a set of core classes that implement ORM, type converters. As we mentioned above, mybatis turns table records into objects in Java. How is the data type right? Through the type converter:
Pretend to be Xiaobai's key to learning mybatis (I)
You can override the existing type processor or create your own type processor to handle unsupported or non-standard types. The specific method is to implement the org.apache.ibatis.type.typehandler interface, or inherit a convenient class org.apache.ibatis.type.basetypehandler, and (optionally) map it to a JDBC type.
It’s a little troublesome to directly implement the typehandler interface, so here we introduce inheriting basetypehandler to realize the powerful functions of type converter in mybatis. Basetypehandler overview:
Pretend to be Xiaobai's key to learning mybatis (I)
The converter is bidirectional, so there are from database to Java and from Java to database:
Setnonnullparameter is the remaining three get methods. We can infer from the name that they are from the database to Java.
The type converter we defined this time is to convert Java Boolean into int type in the database.

public class MyTypeHandler extends BaseTypeHandler<Boolean> {
    // java-DB
    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, Boolean parameter, JdbcType jdbcType) throws SQLException {
        if (parameter) {
            ps.setInt(i, 1);
        } else {
            ps.setInt(i, 0);
        }
    }

    // DB-java
    @Override
    public Boolean getNullableResult(ResultSet rs, String columnName) throws SQLException {
        return rs.getInt(columnName) == 1 ? true : false;
    }

    //  DB-java
    @Override
    public Boolean getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        return rs.getInt(columnIndex) == 1 ? true : false;
    }

    //  java-Db
    @Override
    public Boolean getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        return cs.getInt(columnIndex) == 1 ? true : false;
    }
}

Then add the corresponding fields in the blog entity and table. The blog is Boolean and the blog table is int. it will not be shown here.
Then we register this type converter in the configuration file:

<typeHandlers>
  <package name="org.mybatis.example"/>
</typeHandlers>

Test code:

private static void selectBlogByCollection() throws IOException {
    BlogMapper blogMapper = getMapper(BlogMapper.class);
    List<Long> idLongList = new ArrayList<>();
    idLongList.add(1L);
    System.out.println(blogMapper.selectBlogByList(idLongList));
}

Values in database:
Pretend to be Xiaobai's key to learning mybatis (I)
Test results:
Pretend to be Xiaobai's key to learning mybatis (I)
Conversion succeeded.

To sum up

This article mainly talks about the basic use of mybatis, how to configure, pass parameters, return types, placeholders, common tags and ognl expressions. Basically, the opening remarks are frequently used, which can be regarded as learning mybatis again. I used to learn mybatis to watch videos. Now I find that if the official documents are rich, it is a better choice to read the official documents. I hope it will be helpful to you

reference material

  • Mybatis official documents
  • Mybatis video tutorial
  • Ognl syntax specification

Recommended Today

Supervisor

Supervisor [note] Supervisor – H view supervisor command help Supervisorctl – H view supervisorctl command help Supervisorctl help view the action command of supervisorctl Supervisorctl help any action to view the use of this action 1. Introduction Supervisor is a process control system. Generally speaking, it can monitor your process. If the process exits abnormally, […]