Preface: This article is a personal MyBatis learning notes, which is divided into three parts. The notes come from my self-study and school training courses. I hope to make progress with you.

7. mappers

Now that the behavior of MyBatis has been configured by the above elements, we will now define the SQL mapping statement. But first, we need to tell MyBatis where to find these statements. Java does not provide a good solution for automatically finding resources, so the best way is to directly tell MyBatis where to find the mapping file. You can use resource references relative to the classpath, or fully qualified resource locators (including URL s in the form of file:///), or class names and package names. For example:

<!-- Use resource references relative to Classpaths -->
<mappers>
    recommend!
  <mapper resource="org/mybatis/builder/AuthorMapper.xml"/>
  <mapper resource="org/mybatis/builder/BlogMapper.xml"/>
  <mapper resource="org/mybatis/builder/PostMapper.xml"/>
</mappers>
<!-- Use fully qualified resource locators( URL) -->
<mappers>
  <mapper url="file:///var/mappers/AuthorMapper.xml"/>
  <mapper url="file:///var/mappers/BlogMapper.xml"/>
  <mapper url="file:///var/mappers/PostMapper.xml"/>
</mappers>
<!-- Use the mapper interface to implement the fully qualified class name of the class -->
<mappers>
    use class For registration, the interface and its corresponding configuration file must have the same name! And under the same package
  <mapper class="org.mybatis.builder.AuthorMapper"/>
  <mapper class="org.mybatis.builder.BlogMapper"/>
  <mapper class="org.mybatis.builder.PostMapper"/>
</mappers>
<!-- Register all mapper interface implementations in the package as mappers -->
<mappers>
  <package name="org.mybatis.builder"/>
</mappers>

These configurations will tell MyBatis where to find the mapping file. The remaining details should be each SQL mapping file, which is what we will discuss next.

8. Lifecycle and scope

It is important to understand the different scopes and lifecycle categories we discussed earlier, because incorrect use can lead to very serious concurrency problems.

SqlSessionFactoryBuilder

Once the SqlSessionFactory is created, it is no longer needed. Therefore, the best scope of the SqlSessionFactoryBuilder instance is the method scope (that is, local method variables). You can reuse SqlSessionFactoryBuilder to create multiple instances of SqlSessionFactory, but it's best not to keep it all the time to ensure that all XML parsing resources can be released to more important things.

SqlSessionFactory

Think of it as a database connection pool

Once SqlSessionFactory is created, it should always exist during the running of the application. There is no reason to discard it or create another instance. The best practice of using SqlSessionFactory is not to create it repeatedly during the application run. Rebuilding SqlSessionFactory many times is regarded as a code "bad habit". Therefore, the best scope of SqlSessionFactory is the application scope. There are many ways to do this. The simplest is to use singleton mode or static singleton mode.

SqlSession

A request to connect to the connection pool

Each thread should have its own SqlSession instance. The instance of SqlSession is not thread safe, so it cannot be shared, so its best scope is the request or method scope. (close it immediately after use) never put the reference of SqlSession instance in the static field of a class, or even the instance variable of a class. You must not put the reference of SqlSession instance in any type of managed scope, such as HttpSession in Servlet framework. If you are using a Web framework now, consider putting SqlSession in a scope similar to HTTP requests. In other words, every time you receive an HTTP request, you can open a SqlSession and close it after returning a response. This close operation is very important. In order to ensure that the close operation can be performed every time, you should put this close operation into the finally block. The following example is a standard mode to ensure that SqlSession is closed:

try (SqlSession session = sqlSessionFactory.openSession()) {
  // Your application logic code
}

Following this usage pattern in all code can ensure that all database resources can be shut down correctly.

Mapper instance

Mappers are interfaces that bind mapping statements. The instance of the mapper interface is obtained from SqlSession. Although technically speaking, the maximum scope of any mapper instance is the same as the SqlSession requesting them. But the method scope is the most appropriate scope for the mapper instance. In other words, mapper instances should be obtained in the methods that call them, and can be discarded after use. The mapper instance does not need to be explicitly closed. Although there is no problem keeping mapper instances in the entire request scope, you will soon find that managing too many resources like SqlSession in this scope will make you busy. Therefore, it is best to put the mapper within the method scope. Like the following example:

try (SqlSession session = sqlSessionFactory.openSession()) {
  BlogMapper mapper = session.getMapper(BlogMapper.class);
  // Your application logic code
}

5,ResultMap

The resultMap element is the most important and powerful element in MyBatis. It can free you from 90% of the JDBC ResultSets data extraction code, and in some cases allows you to perform some operations that JDBC does not support. In fact, when writing mapping code for complex statements such as connections, a resultMap can replace thousands of lines of code that achieve the same function. The design idea of resultMap is to achieve zero configuration for simple statements. For more complex statements, you only need to describe the relationship between statements.

You have seen examples of simple mapping statements before, which do not explicitly specify resultMap. For example:

<select id="selectUsers" resultType="map">
  select id, username, hashedPassword
  from some_table
  where id = #{id}
</select>

The above statement simply maps all columns to the key of the HashMap, which is specified by the resultType attribute. Although it is sufficient in most cases, HashMap is not a good domain model. Your program is more likely to use JavaBeans or POJO s (Plain Old Java Objects) as domain models. MyBatis supports both. Take a look at the following JavaBean:

package com.someapp.model;
public class User {
  private int id;
  private String username;
  private String hashedPassword;

  public int getId() {
    return id;
  }
  public void setId(int id) {
    this.id = id;
  }
  public String getUsername() {
    return username;
  }
  public void setUsername(String username) {
    this.username = username;
  }
  public String getHashedPassword() {
    return hashedPassword;
  }
  public void setHashedPassword(String hashedPassword) {
    this.hashedPassword = hashedPassword;
  }
}

Based on the JavaBean specification, the above class has three attributes: id, username and hashedPassword. These attributes correspond to the column names in the select statement.

Such a JavaBean can be mapped to a ResultSet, which is as simple as mapping to a HashMap.

<select id="selectUsers" resultType="com.someapp.model.User">
  select id, username, hashedPassword
  from some_table
  where id = #{id}
</select>

Type alias is your good helper. Using them, you don't have to enter the fully qualified name of the class. For example:

<!-- mybatis-config.xml in -->
<typeAlias type="com.someapp.model.User" alias="User"/>

<!-- SQL mapping XML in -->
<select id="selectUsers" resultType="User">
  select id, username, hashedPassword
  from some_table
  where id = #{id}
</select>

In these cases, MyBatis will automatically create a ResultMap behind the scenes, and then map the column to the properties of the JavaBean according to the property name. If the column name and attribute name cannot match, you can set the column alias in the SELECT statement (this is a basic SQL feature) to complete the match. For example:

<select id="selectUsers" resultType="User">
  select
    user_id             as "id",
    user_name           as "userName",
    hashed_password     as "hashedPassword"
  from some_table
  where id = #{id}
</select>

After learning the above knowledge, you will find that none of the above examples need to explicitly configure ResultMap, which is the advantage of ResultMap - you can completely do without explicitly configuring them. Although the above example does not explicitly configure the ResultMap. But to explain, let's see what happens if we explicitly use an external ResultMap in the example just now. This is another way to solve the column name mismatch.

<resultMap id="userResultMap" type="User">
  <id property="id" column="user_id" />
  <result property="username" column="user_name"/>
  <result property="password" column="hashed_password"/>
</resultMap>

Then set the resultMap attribute in the statement that references it (note that we have removed the resultType attribute). For example:

<select id="selectUsers" resultMap="userResultMap">
  select user_id, user_name, hashed_password
  from some_table
  where id = #{id}
</select>

If only the world were always so simple.

6. Log

6.1 log factory

Mybatis provides logging capabilities by using the built-in logging factory. The built-in log factory will delegate the log work to one of the following implementations:

Write to the core configuration file mybatis-config.xml

https://mybatis.org/mybatis-3/zh/logging.html

• SLF4J
• Apache Commons Logging
• Log4j 2
• Log4j
• JDK logging

MyBatis built-in log factory will select log delegation implementation based on runtime detection information. It uses the first found implementation (in the order listed above). When these implementations are not found, logging will be disabled.

Many application servers (such as Tomcat and WebShpere) already include common logging in their Classpaths. Note that in this configuration environment, MyBatis will use common logging as a logging tool. This means that in an environment such as WebSphere, your Log4J configuration will be ignored because a private implementation of common logging is provided. At this time, you will feel very depressed: it seems that MyBatis has ignored your Log4J configuration (in fact, it is because in this configuration environment, MyBatis uses common logging as the logging Implementation). If your application is deployed in an environment where the classpath already contains common logging, and you want to use other logging implementations, you can use the MyBatis configuration file MyBatis config Add a setting to XML to select other logging implementations.

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

6.2 log4j

Maven: https://mvnrepository.com/artifact/log4j/log4j

What is log4i?

• Log4j yes Apache By using Log4j, we can control the destination of log information transmission Console , documents GUI Components, even socket servers NT Event recorder UNIX SyslogDaemons Etc;
• We can also control the output format of each log; By defining the level of each log information, we can control the log generation process in more detail.
• The most interesting thing is that these can be achieved through a configuration file To flexibly configure without modifying the application code.

1. Import the package of log4j first

<!-- https://mvnrepository.com/artifact/log4j/log4j -->
<dependency>
    <groupId>log4j</groupId>
    <artifactId>log4j</artifactId>
    <version>1.2.17</version>
</dependency>

1. Create log4j.properties under CLASSPATH. The contents are as follows:

   log4j.rootCategory=INFO, stdout , R
    
   log4j.appender.stdout=org.apache.log4j.ConsoleAppender
   log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
   log4j.appender.stdout.layout.ConversionPattern=[QC] %p [%t] %C.%M(%L) | %m%n
    
   log4j.appender.R=org.apache.log4j.DailyRollingFileAppender
   log4j.appender.R.File=D:\\Tomcat 5.5\\logs\\qc.log
   log4j.appender.R.layout=org.apache.log4j.PatternLayout
   log4j.appender.R.layout.ConversionPattern=%d-[TS] %p %t %c - %m%n
    
   log4j.logger.com.neusoft=DEBUG
   log4j.logger.com.opensymphony.oscache=ERROR
   log4j.logger.net.sf.navigator=ERROR
   log4j.logger.org.apache.commons=ERROR
   log4j.logger.org.apache.struts=WARN
   log4j.logger.org.displaytag=ERROR
   log4j.logger.org.springframework=DEBUG
   log4j.logger.com.ibatis.db=WARN
   log4j.logger.org.apache.velocity=FATAL
    
   log4j.logger.com.canoo.webtest=WARN
    
   log4j.logger.org.hibernate.ps.PreparedStatementCache=WARN
   log4j.logger.org.hibernate=DEBUG
   log4j.logger.org.logicalcobwebs=WARN
   

2. setting log implementation

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

3. Use log4j output in the program

   //Note the guide package: org.apache.log4j.Logger
   static Logger logger = Logger.getLogger(MyTest.class);
   @Test
   public void selectUser() {
   logger.info("info: get into selectUser method");
   logger.debug("debug: get into selectUser method");
   logger.error("error: get into selectUser method");
   SqlSession session = MybatisUtils.getSession();
   UserMapper mapper = session.getMapper(UserMapper.class);
   List<User> users = mapper.selectUser();
   for (User user: users){
   System.out.println(user);
   }
   session.close();
   }
   

4. Test and see the console output

   • Use Log4j to output logs
   • You can see that a log file is also generated [the log level of file needs to be modified]

7. Paging

7.1 limit paging

Think: why do I need pagination?

When learning persistence layer frameworks such as mybatis, we often add, delete, modify and query data, and the most used is to query the database. If we query a large amount of data, we often use paging to query, that is, we process a small part of data every time, so the pressure on the database is under control.

The client queries the data in the database table by passing two parameters: start (page number) and PageSize (number of items displayed per page). Then we know that MySql database provides the paging function limit m,n, but the usage of this function is different from our requirements, so we need to rewrite our own paging statements according to the actual situation. The specific analysis is as follows:

For example:

• The sql to query the data from items 1 to 10 is: select * from table limit 0,10; - > Our requirement is to query the data on the first page: select * from table limit (1-1)*10,10;

• The sql to query the data of items 10 to 20 is: select * from table limit 10,10; - > Our requirement is to query the data on the second page: select * from table limit (2-1)*10,10;

• The sql to query the data of items 20 to 30 is: select * from table limit 20,10; - > Our requirement is to query the data on the third page: select * from table limit (3-1)*10,10;

Through the above analysis, we can conclude that the paging sql format that meets our needs is: select * from table limit (start-1)*pageSize,pageSize; Where start is the page number and pageSize is the number of items displayed on each page.

Paging with Limit

#grammar
SELECT * FROM table LIMIT stratIndex，pageSize

SELECT * FROM table LIMIT 5,10; // Search record lines 6-15  

#If only one parameter is given, it indicates that the maximum number of record lines is returned:   
SELECT * FROM table LIMIT 5; //Retrieve the first 5 record lines  

#In other words, LIMIT n is equivalent to LIMIT 0,n. 

Steps:

1. Modify Mapper file

<select id="selectUser" parameterType="map" resultType="user">
  select * from user limit #{startIndex},#{pageSize}
</select>

2. Mapper interface, the parameter is map

//Select all users to realize paging
List<User> selectUser(Map<String,Integer> map);

3. Pass parameter test in test class

• Infer: start position = (current page - 1) * page size

//Paging query, two parameters StartIndex, PageSize
@Test
public void testSelectUser() {
   SqlSession session = MybatisUtils.getSession();
   UserMapper mapper = session.getMapper(UserMapper.class);

   int currentPage = 1;  //Page number
   int pageSize = 2;  //How many are displayed on each page
   Map<String,Integer> map = new HashMap<String,Integer>();
   map.put("startIndex",(currentPage-1)*pageSize);
   map.put("pageSize",pageSize);

   List<User> users = mapper.selectUser(map);

   for (User user: users){
       System.out.println(user);
  }

   session.close();
}

7.2 RowBounds realize paging

In mybatis, it is very convenient to use RowBounds for paging. You do not need to write limit in sql statements to complete the paging function. However, because it intercepts data based on all the results of sql query, it is not applicable in sql with large amount of data, and it is more suitable for queries that return less data results

The most important thing is that in the mapper interface layer, when parameters are passed, the RowBounds(int offset, int limit) object is passed in to complete paging

Note: since the maximum integer allowed by java is 2147483647, the maximum integer that limit can use is also 2147483647. Taking out a large amount of data at one time may cause memory overflow, so it should be used cautiously in big data query

Steps:

1. mapper interface

//Select all users RowBounds to realize paging
List<User> getUserByRowBounds();

2. mapper file

<select id="getUserByRowBounds" resultType="user">
	select * from user
</select>

3. Test class

Here, we need to use the RowBounds class

@Test
public void testUserByRowBounds() {
   SqlSession session = MybatisUtils.getSession();

   int currentPage = 2;  //Page number
   int pageSize = 2;  //How many are displayed on each page
   RowBounds rowBounds = new RowBounds((currentPage-1)*pageSize,pageSize);

   //Pass rowBounds through the session.** method, [this method is not recommended now]
   List<User> users = session.selectList("dao.UserMapper.getUserByRowBounds", null, rowBounds);

   for (User user: users){
       System.out.println(user);
  }
   session.close();
}