Mybatis complete notes

1. Introduction

1.1. What is Mybatis

MyBatis is an excellent persistence layer framework
It 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 POJO s (Plain Old Java Objects) to records in the database through simple XML or annotations.
MyBatis was originally an open source project ibatis of apache. In 2010, the project was migrated from apache software foundation to google code and renamed MyBatis.
Moved to Github in November 2013

How do I get Mybatis?

maven warehouse

<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.5.7</version>
</dependency>

Github: https://github.com/mybatis/mybatis-3/releases
Chinese documents: https://mybatis.org/mybatis-3/zh/index.html

1.2 persistence

Data persistence

Persistence is the process of transforming program data in persistent state and transient state
Memory: loss upon power failure
Database (jdbc), io file persistence

Why persistence?

Save data that cannot be lost
High memory price

1.3. Durable layer

Dao layer, Service layer, Controller layer

Code block that completes the persistence work
The boundary of the layer is very obvious

1.4 why do you need Mybatis

Help programmers store data into the database
Traditional JDBC code is too complex
Simplification, framework, automation
advantage
- Easy to learn
- flexible
- Decoupling sql and program code: the separation of sql and code improves maintainability.
- Provide mapping labels to support the mapping of orm fields between objects and databases
- Provide object relationship mapping labels to support object relationship construction and maintenance
- Provide xml tags to support writing dynamic sql.

2. The first Mybatis program

Idea: build environment – > Import Mybatis – > write code – > test

2.1 construction environment

Build database

CREATE DATABASE `mybatisnote`;

USE `mybatisnote`;

CREATE TABLE `user`(
	`id` INT(20) NOT NULL PRIMARY KEY,
	`name` VARCHAR(30) DEFAULT NULL,
	`pwd` VARCHAR(30) DEFAULT NULL
)ENGINE = INNODB DEFAULT CHARSET=utf8;

INSERT INTO `user`(`id`,`name`,`pwd`) VALUES
(1,'Zhang San','1234'),
(2,'Li Si','12345'),
(3,'Wang Wu','123456')

New project

Create a normal maven project
Delete src directory
Import maven dependencies

<dependencies>
    <!--mysql drive-->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.47</version>
    </dependency>
    <!--mybatis-->
    <dependency>
        <groupId>org.mybatis</groupId>
        <artifactId>mybatis</artifactId>
        <version>3.5.7</version>
    </dependency>
    <!--junit-->
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.12</version>
    </dependency>
</dependencies>

2.2. Create a module

Write the core configuration file of mybatis

<?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 Core profile-->
<configuration>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/mybatisnote?useSSL=false&amp;useUnicode=true&amp;characterEncoding-UTF-8"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>

    <!--Each Mapper.xml All need to be in mybatis Register in core profile-->
    <mappers>
        <mapper resource="com/ping/dao/userMapper.xml"/>
    </mappers>

</configuration>

Write mybatis tool class

//sqlSessionFactory --> sqlSession
public class MybatisUtils {

    private static SqlSessionFactory sqlSessionFactory;

    static {
        try {
            //The first step in using Mybatis: get the sqlSessionFactory object
            String resource = "mybatis-config.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    //Now that we have SqlSessionFactory, as the name suggests, we can get an instance of SqlSession from it.
    //SqlSession provides all the methods required to execute SQL commands in the database.

    public static SqlSession getSqlSession() {
        return sqlSessionFactory.openSession();
    }
}

2.3. Code writing

Entity class

public class User {
    private int id;
    private String name;
    private String pwd;

    public User() {
    }

    public User(int id, String name, String pwd) {
        this.id = id;
        this.name = name;
        this.pwd = pwd;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getPwd() {
        return pwd;
    }

    public void setPwd(String pwd) {
        this.pwd = pwd;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", pwd='" + pwd + '\'' +
                '}';
    }
}

Dao interface

public interface UserDao {
    List<User> getUserList();
}

The interface implementation class is transformed from the original UserDaoImpl to a Mapper configuration file

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<!--namespace=Bind a corresponding Dao/Mapper Interface-->
<mapper namespace="com.ping.dao.UserDao">

    <!--select Query statement-->
    <select id="getUserList" resultType="com.ping.pojo.User">
        select * from mybatisnote.user;
    </select>

</mapper>

2.4 test

Note:

org.apache.ibatis.binding.BindingException: Type interface com.ping.dao.UserDao is not known to the MapperRegistry.

MapperRegistry: registers mappers in the core configuration file

junit test

public class UserDaoTest {

    @Test
    public void test(){

        //1. Obtain sqlSession object
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        //Method 1: getMapper
        UserDao userDao= sqlSession.getMapper(UserDao.class);
        List<User> userList = userDao.getUserList();

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

        //Close sqlSession
        sqlSession.close();
    }
}

In maven, because the contract is larger than the configuration, the XML and properties configuration files cannot be exported or take effect. Solution: add in pom.xml

<!--stay build Medium configuration resources，To prevent the failure of resource export-->
    <build>
        <resources>
            <resource>
                <directory>src/main/resources</directory>
                <includes>
                    <include>**/*.properties</include>
                    <include>**/*.xml</include>
                </includes>
                <filtering>true</filtering>
            </resource>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.properties</include>
                    <include>**/*.xml</include>
                </includes>
                <filtering>true</filtering>
            </resource>
        </resources>
    </build>

3,CRUD

3.1,Mapper.xml

The package name in the namespace should be consistent with the package name of Dao/mapper interface!
id: is the method name in the corresponding namespace;
resultType: return value of Sql statement execution
parameterType: parameter type

3.2,select,insert,update,delete

Write interface

public interface UserMapper {
    //Query all users
    List<User> getUserList();

    //Query user by ID
    User getUserById(int id);

    //insert a user
    int addUser(User user);

    //Modify user
    int updateUser(User user);

    //Delete a user
    int deleteUser(int id);
}

Write the corresponding sql statement in mapper.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">

<!--namespace=Bind a corresponding Dao/Napper Interface-->
<mapper namespace="com.ping.dao.UserMapper">

    <!--select Query statement-->
    <select id="getUserList" resultType="com.ping.pojo.User">
        select * from user;
    </select>

    <select id="getUserById" parameterType="int" resultType="com.ping.pojo.User">
        select * from user where id = #{id}
    </select>

    <!--insert insert -->
    <!--The properties in the object can be taken out directly-->
    <insert id="addUser" parameterType="com.ping.pojo.User" >
        insert into user (id,name,pwd) values (#{id},#{name},#{pwd})
    </insert>

    <!--update to update-->
    <update id="updateUser" parameterType="com.ping.pojo.User">
        update user set name=#{name},pwd=#{pwd}  where id = #{id};
    </update>

    <!--delete delete-->
    <delete id="deleteUser" parameterType="int">
        delete from user where id = #{id};
    </delete>

</mapper>

test

public class UserMapperTest {

    @Test
    public void test(){
        SqlSession sqlSession = null;

        try {
            //1. Obtain sqlSession object
            sqlSession = MybatisUtils.getSqlSession();
            //Method 1: getMapper
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            List<User> userList = userMapper.getUserList();

            //Method 2: no longer use
            //List<User> userList = sqlSession.selectList("com.ping.dao.UserDao.getUserList");

            for (User user : userList) {
                System.out.println(user);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            //Close sqlSession
            sqlSession.close();
        }
    }

    @Test
    public void getUserById(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();

        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        User user = mapper.getUserById(1);
        System.out.println(user);

        sqlSession.close();
    }

    //Add, delete and modify transactions that need to be submitted (very important)
    @Test
    public void addUser(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        int res = mapper.addUser(new User(4, "Xiao Ming", "123456"));
        if(res>0){
            System.out.println("Insert successful");
        }

        //Commit transactions (very important)
        sqlSession.commit();

        sqlSession.close();
    }

    @Test
    public void updateUser(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        mapper.updateUser(new User(4,"Xiaotian","1234567"));

        sqlSession.commit();
        sqlSession.close();
    }

    @Test
    public void deleteUser(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();

        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        mapper.deleteUser(4);

        sqlSession.commit();

        sqlSession.close();
    }
}

Note: add, delete, or modify transactions that need to be committed

3.3,Map

Assuming that there are too many tables, fields or parameters in the entity class or database, Map or annotation should be considered;

Map passes parameters. Just get the key from sql directly, [parameterType = "map"]
To pass parameters to an Object, you can directly get the attribute of the Object in sql, [parameterType = "Object"]
If there is only one basic type parameter, you can get it directly in sql without writing parameterType

Interface

//Universal Map
int addUser2(Map<String,Object> map);

Write the corresponding sql statement in mapper.xml

<!--transmit map of key-->
<insert id="addUser" parameterType="map" >
    insert into user (id,pwd) values (#{userid},#{password})
</insert>

test

@Test
public void addUser2(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);

    HashMap<String, Object> map = new HashMap<>();
    map.put("userid",5);
    map.put("upassword","12345678");

    mapper.addUser2(map);

    //Commit transactions (very important)
    sqlSession.commit();
    sqlSession.close();
}

3.4. Fuzzy query

When executing Java code, pass wildcard%%

List<User> userList = mapper.getUserLike("%Lee%");

Using wildcards in sql splicing

select * from mybatisnote.user where name like "%"#{value}"%";

Take the code in 2 above as an example

Interface

//Fuzzy query
List<User> getUserLike(String value);

Write the corresponding sql statement in mapper.xml

<select id="getUserLike" resultType="com.ping.pojo.User">
    select * from mybatisnote.user where name like "%"#{value}"%";
</select>

test

@Test
public  void getUserLike(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);

    List<User> userList = mapper.getUserLike("Lee");
    for (User user : userList) {
        System.out.println(user);
    }

    sqlSession.close();
}

4. Configuration resolution

Detailed configuration Click here to view the official website document

4.1. Core configuration file

mybatis-config.xml
The MyBatis configuration file contains settings and attribute information that deeply affect MyBatis behavior. The top-level structure of the configuration document is as follows:
- configuration
  - properties
  - settings
  - typeAliases
  - typeHandlers
  - objectFactory (object factory)
  - plugins
  - environments
    - Environment (environment variable)
      - transactionManager (transaction manager type="[JDBC|MANAGED]")
      - dataSource (data source type="[UNPOOLED|POOLED|JNDI])
  - databaseIdProvider (database vendor ID)
  - mappers

4.2. Environment configuration

MyBatis can be configured to adapt to a variety of environments

However, remember that although multiple environments can be configured, only one environment can be selected for each SqlSessionFactory instance.
The default transaction manager of Mybatis is JDBC
Mybatis's default dataSource is POOLED

4.3. properties

The reference configuration file can be implemented through the properties property
These properties are externally configurable and dynamically replaceable. They can be configured in a typical Java property file or passed through the child elements of the properties element. [db.properties]

Write a configuration file [db.properties]

driver = com.mysql.jdbc.Driver
url = jdbc:mysql://localhost:3306/mybatisnote?useSSL=false&useUnicode=true&characterEncoding=UTF-8
username = root
password = 123456

Core profile

<?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 Core profile-->
<configuration>
    <!--Import external profile directly-->
    <properties resource="db.properties" />

    <environments default="test">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>
</configuration>

You can add some property configurations in properties

<properties resource="db.properties">
    <property name="username" value="root"/>
    <property name="password" value="123456"/>
</properties>

If two files have the same field, the external configuration file is preferred for the following reasons

If a property is configured in more than one place, MyBatis will load in the following order: first read the property specified in the properties element body. Then read the property file under the classpath according to the resource attribute in the properties element, or read the property file according to the path specified by the url attribute, and overwrite the previously read property with the same name.

4.4 type aliases

A type alias is a short name for a Java type.

The meaning of existence is only to reduce the redundancy of class fully qualified names.

<!--First: you can alias entity classes-->
<typeAliases>
    <typeAlias type="com.ping.pojo.User" alias="User"/>
</typeAliases>

You can also specify a package name. MyBatis will search for the required Java Bean under the package name
Scan the package of an entity class, and its default alias is the class name of the class (initial lowercase)
```

<typeAliases>
    <package name="com.ping.pojo"/>
</typeAliases>
```
When there are few entity classes, use the first method; If there are many entity classes, the second one is recommended
The first can customize aliases, and the second can't

If you have to change the alias, you can add annotations on the entity

import org.apache.ibatis.type.Alias;
//Third
@Alias("user")
public class User {}

4.5 setting

These are very important tuning settings in MyBatis, which will change the runtime behavior of MyBatis.

Setting name (key)	describe	Valid value (value)	Default value
cacheEnabled	Globally turn on or off any cache configured in all mapper profiles.	true\|false	true
lazyLoadingEnabled	Global switch for delayed loading. When on, all associated objects are loaded late. In a specific association, the switch state of the item can be overridden by setting the fetchType property.	true\|false	true
logImpl	Specify the specific implementation of the log used by MyBatis. If it is not specified, it will be found automatically.	SLF4J \| LOG4J \| LOG4J2 \| JDK_LOGGING \| COMMONS_LOGGING \| STDOUT_LOGGING \| NO_LOGGING	Not set

4.6 other configurations

typeHandlers
objectFactory (object factory)
plugins
- mybatis-generator-core
- mybatis-plus
- General mapper

4.7 mappers

MapperRegistry: register and bind Mapper files;

Method 1: [recommended]

<!--Each Mapper.xml All need to be in mybatis Register in core profile-->
<mappers>
    <mapper resource="com/ping/dao/userMapper.xml"/>
</mappers>

Method 2: register with class file binding

<!--Note:
	Interface and Mapper The profile must have the same name
 	Interface and Mapper The configuration file must be under the same package
-->
<mappers>
    <mapper class="com.ping.dao.UserMapper" />
</mappers>

Method 3: use package scanning to register and bind

<!--Note: the same as mode 2-->
<mappers>
    <package name="com.ping.dao"/>
</mappers>

4.8 life cycle and scope

Lifecycle and scope are crucial, because incorrect use can lead to very serious concurrency problems.

SqlSessionFactory:
- Once SqlSessionFactory is created, it is no longer needed
- local variable
SqlSessionFactory:
- To put it bluntly, it can be imagined as: database connection pool
- Once SqlSessionFactory is created, it should always exist during the operation of the application. There is no reason to discard it or recreate another instance.
- Therefore, the best scope of SqlSessionFactory is the application scope.
- The simplest is to use singleton mode or static singleton mode.
SqlSession
- A request to connect to the connection pool
- You need to close it immediately after it is used up, otherwise the resources will be occupied

5. Solve the problem of inconsistency between attribute name and field name

5.1 alias

Fields in the database

Create a new project and test the inconsistency of entity class fields before copying

public class User {
    private int id;
    private String name;
    private String password;

There is a problem with the test

//select * from mybatisnote.user where id = #{id}
//Type processor
//select id,name, pwd from mybatisnote.user where id = #{id}

solve the problem:

Alias

<select id="getUserById" parameterType="int" resultType="com.ping.pojo.User">
    select id,name,pwd as password from mybatisnote.user where id = #{id};
</select>

5.2. resultMap result set mapping

<!--Result set mapping-->
<resultMap id="UserMap" type="User">
    <!--column Fields in the database, property Properties in entity classes-->
    <!-- <result column="id" property="id"/>
        <result column="name" property="name"/>-->
    <result column="pwd" property="password"/>
</resultMap>

<select id="getUserById" parameterType="int" resultMap="UserMap">
    select * from mybatisnote.user where id = #{id};
</select>

The resultMap element is the most important and powerful element in MyBatis
The design idea of ResultMap is that there is no need to configure explicit result mapping for simple statements, but only need to describe their relationship for more complex statements
The best thing about ResultMap is that although you already know it well, you don't need to explicitly use them at all

6. Log

6.1 log factory

If an exception occurs in a database operation, we need to troubleshoot it. The log is the best assistant!

Once: South, debug
Now: log factory

Set name	describe	Effective value	Default value
logImpl	Specify the specific implementation of the log used by MyBatis. If it is not specified, it will be found automatically.	SLF4J \| LOG4J \| LOG4J2 \| JDK_LOGGING \| COMMONS_LOGGING \| STDOUT_LOGGING \| NO_LOGGING	Not set

logImpl valid values
- SLF4J
- LOG4J [Master]
- LOG4J2
- JDK_LOGGING
- COMMONS_LOGGING
- STDOUT_LOGGING
- NO_LOGGING
Which log implementation is used in Mybatis? Set it in settings!

STDOUT_LOGGING [standard log output]

In the mybatis core configuration file, configure the log!

<settings>
    <!--Configure standard log factory-->
    <setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>

6.2,LOG4J

What is Log4j?
- Log4j is an open source project of Apache. By using log4j, we can control that the destination of log information transmission is console, file and GUI components
- You can also control the output format of each log
- By defining the level of each log information, you can control the log generation process in more detail
- It can be flexibly configured through a configuration file without modifying the application code.

step

Import log4j dependency

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

Configuration file log4j.properties

#Output the log information with the level of DEBUG to the two destinations of console and file. The definitions of console and file are in the following code
log4j.rootLogger=DEBUG,console,file

#Settings related to console output
log4j.appender.console = org.apache.log4j.ConsoleAppender
log4j.appender.console.Target = System.out
log4j.appender.console.Threshold=DEBUG
log4j.appender.console.layout = org.apache.log4j.PatternLayout
log4j.appender.console.layout.ConversionPattern=[%c]-%m%n

#Settings related to file output
log4j.appender.file = org.apache.log4j.RollingFileAppender
log4j.appender.file.File=./log/ping.log
log4j.appender.file.MaxFileSize=10mb
log4j.appender.file.Threshold=DEBUG
log4j.appender.file.layout=org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern=[%p][%d{yy-MM-dd}][%c]%m%n

#Log output level
log4j.logger.org.mybatis=DEBUG
log4j.logger.java.sql=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.ResultSet=DEBUG
log4j.logger.java.sql.PreparedStatement=DEBUG

Configure log4j as the implementation of log in mybatis-config.xml

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

Use of log4j: direct test run query

Simple use

In the class to use log4j, import the package: import org.apache.log4j.Logger;
Log object. The loading parameter is the class of the current class
```
static Logger logger = Logger.getLogger(UserMapperTest.class);
```

log level

logger.info("info: Entered testLogtj method");
logger.debug("debug: Entered testLogtj method");
logger.error("error: Entered testLogtj method");

Test code

import org.apache.log4j.Logger;
import org.junit.Test;

public class UserMapperTest {

    static Logger logger = Logger.getLogger(UserMapperTest.class);
    @Test
    public void testLog4j(){
        logger.info("info: Entered testLogtj method");
        logger.debug("debug: Entered testLogtj method");
        logger.error("error: Entered testLogtj method");
    }
}

test result

7. Pagination

7.1. Limit paging

code

SELECT * from user limit startIndex,pageSize

Simple example:
- user table
- Query 1: query two rows of pageSize: 2 data starting from the first row of data [startIndex: 0]
```
SELECT * from user limit 0,2;
```
- Query 2: query two rows of pageSize: 2 data from the third row [startIndex: 2]
```
SELECT * from user limit 2,2;
```
- Query 3: only one parameter means to query three rows of [pageSize: 3] data from the first row of data
```
SELECT * from user limit 3;
```

Pagination using Mybatis, core SQL

Interface

public interface UserMapper {
    //paging
    List<User> getUserByLimit(Map<String,Integer> map);
}

Mapper.xml

<!--paging-->
<select id="getUserByLimit" parameterType="map" resultType="Package name.User">
    select * from user limit #{startIndex},#{pageSize};
</select>

test

public class UserMapperTest {
    @Test
    public void getUserByLimit() {
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        HashMap<String, Integer> map = new HashMap<>();
        map.put("startIndex", 0);
        map.put("pageSize", 2);

        List<User> userList = mapper.getUserByLimit(map);
        for (User user : userList) {
            System.out.println(user);
        }

        sqlSession.close();
    }
}

test result

7.2 rowboundaries paging (not recommended)

Do not use SQL to realize paging, and realize paging through java code level

Interface

//Page 2: rowboundaries
List<User> getUserByRowBounds();

Mapper.xml

<!--Pagination: RowBounds-->
<select id="getUserByRowBounds" resultMap="UserMap">
    select * from user
</select>

test

@Test
//Page 2: rowboundaries
public void getUserByRowBounds(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();

    //RowBounds implementation
    RowBounds rowBounds = new RowBounds(0, 2);

    //Paging through java code level
    List<User> userList = sqlSession.selectList("com.ping.dao.UserMapper.getUserByRowBounds",null,rowBounds);

    for (User user : userList) {
        System.out.println(user);
    }
    sqlSession.close();
}

test result

7.3. MyBatis paging plug-in PageHelper (just understand)

Detailed documentation: https://pagehelper.github.io/

8. Using annotation development

8.1 interface oriented programming

1. Overview

Root cause: decoupling, expandable, improved reuse. In layered development, the upper layer does not care about the specific implementation. Everyone abides by common standards, making the development easier and more standardized
In an object-oriented system, the various functions of the system are completed by the cooperation of many different objects. In this case, how each object implements itself is not so important to the system designer;
The cooperative relationship between various objects has become the key of system design. From the communication between different classes to the interaction between modules, we should focus on it at the beginning of system design, which is also the main work content of system design. Interface oriented programming refers to programming according to this idea.

2. Understanding of interfaces

From a deeper understanding, the interface should be the separation of definition (specification, constraint) and Implementation (the principle of separation of name and reality).
The interface itself reflects the system designer's abstract understanding of the system.
There shall be two types of interfaces:
- The first type is the abstraction of an individual, which can correspond to an abstract class;
- The second is the abstraction of an aspect of an individual, that is, the formation of an abstract interface;
- an individual may have multiple Abstract faces. Abstract body and abstract surface are different.

3. Three oriented differences

Object - oriented means that when we consider a problem, we take the object as the unit and consider its attributes and methods
Process oriented means that when we consider a problem, we consider its implementation in a specific process (transaction process)
Interface design and non interface design are aimed at reuse technology, and object-oriented (process) is not a problem. It is more reflected in the overall architecture of the system

8.2 development using annotations

Annotations are implemented on the interface

public interface UserMapper {

    @Select("select * from user")
    List<User> getUsers();
}

The interface needs to be bound in the core configuration file mybatis-config.xml

<!--Binding interface-->
<mappers>
    <mapper class="com.ping.dao.UserMapper"/>
</mappers>

test

public class UserMapperTest {
    @Test
    public void test(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();

        //The bottom layer mainly applies reflection
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<User> userList = mapper.getUsers();

        for (User user : userList) {
            System.out.println(user);
        }
        sqlSession.close();
    }
}

test result
Essence: reflection mechanism implementation; bottom layer: dynamic agent

8.3,CRUD

About @ Param() annotation
- Parameters of basic type or String type need to be added
- Reference types do not need to be added
- If there is only one basic type, it can be ignored, but it is recommended that everyone add it
- What we refer to in SQL is the attribute name set in @ Param("uid") here

You can automatically commit transactions when the tool class is created

public static SqlSession getSqlSession() {
    return sqlSessionFactory.openSession(true);
}

Write the interface and add comments

public interface UserMapper {

    //Method has multiple parameters. All parameters must be preceded by @ Param("id") annotation
    @Select("select * from user where id = #{uid} ")
    User getUserById(@Param("uid") int id );

    @Insert("insert into user(id,name,pwd) values (#{id},#{name},#{password})")
    int addUser(User user);

    @Update("update user set name=#{name},pwd=#{password} where id = #{id}")
    int updateUser(User user);

    @Delete("delete from user where id = #{uid}")
    int deleteUser(@Param("uid") int id);
}

The interface needs to be bound in the core configuration file mybatis-config.xml

<!--Binding interface-->
<mappers>
    <mapper class="com.ping.dao.UserMapper"/>
</mappers>

Test class: automatically submit transactions. Adding, deleting and modifying tests do not need to write sqlSession.commit();

public class UserMapperTest {
    @Test
    public void testGetUserById(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        User userById = mapper.getUserById(1);
        System.out.println(userById);

        sqlSession.close();
    }

    @Test
    public void testaddUser(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        mapper.addUser(new User(5,"Xiao Hong","12321"));

        sqlSession.close();
    }

    @Test
    public void testupdateUser(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        mapper.updateUser(new User(5,"Xiaojun","32123"));

        sqlSession.close();
    }

    @Test
    public void testdeleteUser(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        mapper.deleteUser(5);

        sqlSession.close();
    }
}

9,Lombok

Introduction to Lombok

Lombok is a java development plug-in, which enables Java developers to eliminate tedious and tedious code in business engineering through some annotations defined by it, especially for simple java model objects (POJOs) . after using Lombok plug-in in the development environment, Java developers can save a lot of time for repeatedly building methods such as hashCode and equals and accessor and ToString methods of various business object models. For these methods, it can automatically generate these methods for us during compiling source code, without reducing the performance of the program like reflection.
Lombok common notes
- @NonNull: before using a method parameter, the parameter will be automatically checked for non emptiness, and NPE(NullPointerException) will be thrown if it is empty
- @Cleanup: it is used to automatically manage resources. It is used before local variables. Before exiting, it will clean up resources and generate try finally code to close the flow
- @Getter/@Setter: used on properties. You don't need to write setter and getter methods by yourself. You can also specify the access range
- @ToString: used on a class to automatically override the toString method
- @EqualsAndHashCode: used on classes to automatically generate equals methods and hashCode methods
- @NoArgsConstructor,@RequiredArgsConstructor and@ AlArgsConstructor: used on classes to automatically generate parameterless constructors and parametric constructors that use all parameters.
- @Data: used on classes, it is equivalent to using @ ToString, @ EqualsAndHashCode, @ Getter, @ Setter and @ requiredargsconstructor annotations at the same time, which is very useful for POJO classes
- @Value: used on a class. It is an immutable form of @ Data. It is equivalent to adding a final declaration for a property. It only provides getter methods, not setter methods
- @SneakyThrows: automatically throw the detected exception without explicitly using the throws statement on the method
- @Synchronized: used on a method to declare the method synchronous and lock it automatically
- @Getter(lazy=true): it can replace the classic Double Check Lock template code

Use steps:

Installing Lombok plug-in in IDEA

Import the jar package of lombok in the project

<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.20</version>
</dependency>

Annotate entity classes

@Data
@AllArgsConstructor
@NoArgsConstructor
public class User {
    private int id;
    private String name;
    private String password;
}

Advantages and disadvantages of Lombok
- advantage:
  1. It can automatically generate constructors, getters / setters, equals, hashcode, toString and other methods in the form of annotations, which improves the development efficiency
  2. Make the code concise and don't pay too much attention to the corresponding methods
  3. When modifying properties, it also simplifies the maintenance of getter/setter methods generated for these properties
- Disadvantages:
  1. Overloading of multiple parameter constructors is not supported
  2. Although it saves the trouble of manually creating getter/setter methods, it greatly reduces the readability and integrity of the source code and reduces the comfort of reading the source code

10. Many to one processing (taking teachers and students as an example)

10.1. Basic environment construction

Multiple students correspond to one teacher
For students, multiple students are associated with a teacher [many to one] (associated)
For teachers, a teacher has many students [one to many] (Collection)

Database sql

CREATE TABLE `teacher` (
  `id` INT(10) NOT NULL,
  `name` VARCHAR(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO teacher(`id`, `name`) VALUES (1, 'Miss Wang');

CREATE TABLE `student` (
  `id` INT(10) NOT NULL,
  `name` VARCHAR(30) DEFAULT NULL,
  `tid` INT(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fktid` (`tid`),
  CONSTRAINT `fktid` FOREIGN KEY (`tid`) REFERENCES `teacher` (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO `student` (`id`, `name`, `tid`) VALUES (1, 'Xiao Ming', '1'); 
INSERT INTO `student` (`id`, `name`, `tid`) VALUES (2, 'Xiao Hong', '1'); 
INSERT INTO `student` (`id`, `name`, `tid`) VALUES (3, 'Xiao Zhang', '1'); 
INSERT INTO `student` (`id`, `name`, `tid`) VALUES (4, 'petty thief', '1'); 
INSERT INTO `student` (`id`, `name`, `tid`) VALUES (5, 'Xiao Wang', '1');

Test environment construction process

Import lombok

<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.20</version>
</dependency>

New entity class Teacher, Student

@Data

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

@Data
public class Student {
    private int id;
    private String name;

    //Students need to associate an object teacher
    private Teacher teacher;

}

Establish Mapper interface

public interface TeacherMapper {

}

public interface StudentMapper {

}

Create Mapper.XML file

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.ping.dao.TeacherMapper">
    
</mapper>

<?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.ping.dao.StudentMapper">
    
</mapper>

Bind and register Mapper interface or file in the core 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 Core profile-->
<configuration>

    <!--Import external profile-->
    <properties resource="db.properties" />

    <settings>
        <!--Standard log factory implementation-->
        <setting name="logImpl" value="STDOUT_LOGGING"/>

    </settings>

    <!--You can alias an entity class-->
    <typeAliases>
        <package name="com.ping.pojo"/>
    </typeAliases>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>

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

</configuration>

Test whether the query can succeed

10.2. Nested processing by query

Interface StudentMapper

public interface StudentMapper {

    //Query all student information and corresponding teacher information
    public List<Student> getStudent();
}

StudentMapper.xml file

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.ping.dao.StudentMapper">

    <!--
    Idea: sub query
        1.Query all student information
        2.According to the student's tid，Find the corresponding teacher
    -->
    
    <select id="getStudent" resultMap="StudentTeacher">
        select * from student;
    </select>

    <resultMap id="StudentTeacher" type="Student">
        <result property="id" column="id"/>
        <result property="name" column="name"/>
        <!--Complex attributes need to be handled separately. Objects: association，  Set: collection-->
        <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
    </resultMap>

    <select id="getTeacher" resultType="Teacher">
        select * from teacher where id = #{id};
    </select>

</mapper>

Test class

public class MyTest {

    @Test
    public void testStudent(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        List<Student> student = mapper.getStudent();
        for (Student student1 : student) {
            System.out.println(student1);
        }

        sqlSession.close();
    }
}

test result

10.3 nesting according to results

Interface StudentMapper

public interface StudentMapper {

    public List<Student> getStudent2();
}

StudentMapper.xml file

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.ping.dao.StudentMapper">

    <!--Nested processing according to results-->
    <select id="getStudent2" resultMap="StudentTeacher2">
        select s.id sid,s.name sname,t.id ti,t.name tname
        from student s,teacher t
        where  s.tid = t.id;
    </select>

    <resultMap id="StudentTeacher2" type="Student">
        <result property="id" column="sid"/>
        <result property="name" column="sname"/>
        <association property="teacher" javaType="Teacher">
            <result property="name" column="tname"/>
            <result property="id" column="ti"/>
        </association>
    </resultMap>

</mapper>

Test class

public class MyTest {

    @Test
    public void testStudent2(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        List<Student> student = mapper.getStudent2();
        for (Student student1 : student) {
            System.out.println(student1);
        }

        sqlSession.close();
    }
}

test result

11. One to many processing (taking teachers and students as an example)

Click here to view many to one processing (basic environment construction)
For teachers, a teacher has many students [one to many] (Collection)

11.1. Nested processing according to results

Interface TeacherMapper

public interface TeacherMapper {

    //Get the information of all students and teachers under the specified teacher
    Teacher getTeacher(@Param("tid") int id);

}

TeacherMapper.xml file

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.ping.dao.TeacherMapper">

    <!--Nested query by result-->
    <select id="getTeacher" resultMap="TeacherStudent">
        select s.id sid , s.name sname, t.name tname,t.id tid
        from student s,teacher t
        where  s.tid = t.id and t.id = #{tid};
    </select>
    <resultMap id="TeacherStudent" type="Teacher">
        <result property="id" column="tid"/>
        <result property="name" column="tname"/>
        <!--Complex attributes need to be handled separately. Objects: association，  Set: collection
            javaType="" Specifies the type of the property
            Generic information in the collection, using ofType obtain
        -->
        <collection property="students" ofType="Student">
            <result property="id" column="sid"/>
            <result property="name" column="sname"/>
            <result property="tid" column="tid"/>

        </collection>
    </resultMap>
</mapper>

Test class

public class Mytest {
    @Test
    public void test(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
        Teacher teacher = mapper.getTeacher(1);
        System.out.println(teacher);
        /*
        Teacher(id=1, name=Mr. Wang, students=[Student(id=1, name = Xiao Ming, tid=1),
        Student(id=2, name=Xiaohong, tid=1),
        Student(id=3, name=Xiao Zhang, tid=1),
        Student(id=4, name=Xiao Li, tid=1),
        Student(id=5, name=Xiao Wang, tid=1)])
        */

        sqlSession.close();
    }
}

test result

11.2. Nested processing by query

Interface TeacherMapper

public interface TeacherMapper {

    //Get the information of all students and teachers under the specified teacher
    Teacher getTeacher2(@Param("tid") int id);

}

TeacherMapper.xml file

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.ping.dao.TeacherMapper">

    <!--Nested processing by query-->
    <select id="getTeacher2" resultMap="TeacherStudent2">
        select * from teacher where id = #{tid};
    </select>
    <resultMap id="TeacherStudent2" type="Teacher">
        <collection property="students" javaType="ArrayList" ofType="Student" select="getStudentByTeacherId" column="id"/>
    </resultMap>

    <select id="getStudentByTeacherId" resultType="Student">
        select * from student where tid = #{tid}
    </select>
</mapper>

Test class

public class Mytest {
    @Test
    public void test2(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
        Teacher teacher = mapper.getTeacher2(1);
        System.out.println(teacher);
        /*
        Teacher(id=1, name=Mr. Wang, students=[Student(id=1, name = Xiao Ming, tid=1),
        Student(id=2, name=Xiaohong, tid=1),
        Student(id=3, name=Xiao Zhang, tid=1),
        Student(id=4, name=Xiao Li, tid=1),
        Student(id=5, name=Xiao Wang, tid=1)])
        */

        sqlSession.close();
    }
}

test result

12. Dynamic SQL

Dynamic SQL refers to generating different SQL statements according to different conditions
The so-called dynamic SQL is still an SQL statement in essence, but a logical code can be executed at the SQL level
Dynamic SQL is splicing SQL statements. As long as the correctness of SQL is guaranteed, it can be arranged and combined according to the format of SQL

12.1 construction environment

Build database

CREATE TABLE `blog`(
    `id` VARCHAR(50) NOT NULL COMMENT 'Blog id',
    `title` VARCHAR(100) NOT NULL COMMENT 'Blog title',
    `author` VARCHAR(30) NOT NULL COMMENT 'Blogger',
    `create_time` DATETIME NOT NULL COMMENT 'Creation time',
    `views` INT(30) NOT NULL COMMENT 'Views'
)ENGINE=InnoDB DEFAULT CHARSET=utf8

Create a basic project

Guide Package

Write 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 Core profile-->
<configuration>

    <!--Import external profile-->
    <properties resource="db.properties" />

    <settings>
        <!--Standard log factory implementation-->
        <setting name="logImpl" value="STDOUT_LOGGING"/>
        <!--Whether to enable automatic hump naming mapping, that is, from the classic database column name A_COLUMN Map to classic Java Attribute name aColumn. -->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>

    <!--You can alias an entity class-->
    <typeAliases>
        <package name="com.ping.pojo"/>
    </typeAliases>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper class="com.ping.dao.BlogMapper"/>
    </mappers>

</configuration

Authoring tool classes

public class MybatisUtils {

    private static SqlSessionFactory sqlSessionFactory;

    static{
        try {
            //The first step in using Mybatis: get the sqlSessionFactory object
            String resource = "mybatis-config.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    //Now that we have SqlSessionFactory, as the name suggests, we can get an instance of SqlSession from it.
    //SqlSession provides all the methods required to execute SQL commands in the database.

    public static SqlSession getSqlSession() {
        return sqlSessionFactory.openSession(true);
    }
}

//Get random ID
public class IDUtils {
    public static String getId(){
        return UUID.randomUUID().toString().replaceAll("-","");
    }

    @Test
    public void test(){
        System.out.println(IDUtils.getId());
        System.out.println(IDUtils.getId());
    }

}

Writing entity classes

@Data
public class Blog {
    private String id;
    private String title;
    private String author;
    private Date createTime; //The property name and field name are inconsistent
    private int views;
}

Write Mapper interface corresponding to entity class

public interface BlogMapper {
    //insert data
    int addBlog(Blog blog);
}

Mapper.xml file

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.ping.dao.BlogMapper">
    <insert id="addBlog" parameterType="Blog">
        insert into blog (id, title, author, create_time, views)
        values(#{id}, #{title}, #{author},#{createTime}, #{views});
    </insert>

</mapper>

Test class insert data

public class MyTest {
    @Test
    public void testaddBlog(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);

        Blog blog = new Blog();
        blog.setId(IDUtils.getId());
        blog.setTitle("study JavaEE");
        blog.setAuthor("Zhang San");
        blog.setCreateTime(new Date());
        blog.setViews(9999);

        mapper.addBlog(blog);

        blog.setId(IDUtils.getId());
        blog.setTitle("study Mybatis");
        blog.setViews(1111);
        mapper.addBlog(blog);

        blog.setId(IDUtils.getId());
        blog.setTitle("study Spring");
        blog.setViews(5555);
        mapper.addBlog(blog);

        blog.setId(IDUtils.getId());
        blog.setTitle("study Springboot");
        blog.setViews(7777);
        mapper.addBlog(blog);

        sqlSession.close();
    }
}

12.2 use of IF

BlogMapper interface

public interface BlogMapper {
    //Query Blog
    List<Blog> queryBlogIF(Map map);
}

BlogMapper.xml file

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.ping.dao.BlogMapper">
    <select id="queryBlogIF" parameterType="map" resultType="blog">
        select * from blog where 1=1
        
        <if test="title != null">
            and title = #{title}
        </if>
        
        <if test="author != null">
            and author = #{author}
        </if>
        
    </select>
</mapper>

Test class

public class MyTest {
    @Test
    public void testqueryBlogIF(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        HashMap map = new HashMap();
        map.put("title","study Mybatis");
        map.put("author","Zhang San");
        List<Blog> blogs = mapper.queryBlogIF(map);
        for (Blog blog : blogs) {
            System.out.println(blog);
        }
        sqlSession.close();
    }
}

12.3 use of choose (when, otherwise)

Not all conditions are used, but only one of multiple conditions is used

<select id="queryBlogChoose" parameterType="map" resultType="Blog">
    select * from blog
    <where>
        <choose>
            <when test="title!=null">
                title = #{title}
            </when>
            <when test="author!=null">
                and author = #{author}
            </when>
            <otherwise>
                and views = #{views}
            </otherwise>
        </choose>
    </where>
</select>

12.4 use of trim, where and set

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.
Use the where tag to modify the BlogMapper.xml file in 12.2

<?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.ping.dao.BlogMapper">
    <select id="queryBlogIF" parameterType="map" resultType="blog">
        select * from blog 
        <where>
            <if test="title != null">
                title = #{title}
            </if>

            <if test="author != null">
                and author = #{author}
            </if>
        </where>
    </select>
</mapper>

set tag

The SET element dynamically inserts the SET keyword at the beginning of the line and deletes additional commas (which are introduced when using conditional statements to assign values to columns)

<update id="updateBlog" parameterType="map">
    update blog
    <set>
        <if test="title != null">
            title = #{title},
        </if>
        <if test="author != null">
            author = #{author}
        </if>
    </set>
    where id = #{id}
</update>

trim tag

Customize the trim element to customize the function of the where element. For example, the user-defined trim element equivalent to the where element is:

<trim prefix="WHERE" prefixOverrides="AND |OR ">
  ...
</trim>
<!--prefixOverrides Property ignores text sequences separated by pipe characters (note that spaces are necessary in this example). The above example removes all prefixOverrides Property and insert prefix Property.-->

Custom trim element equivalent to set element

<trim prefix="SET" suffixOverrides=",">
  ...
</trim>
<!--The suffix value setting is overridden and the prefix value is customized. -->

12.5 SQL fragment

Sometimes, SQL statements of some functions may be extracted for reuse
- Extract common parts using SQL Tags
- Use the include tag reference where necessary

Modify the BlogMapper.xml file in 12.2 using the SOL fragment

<?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.ping.dao.BlogMapper">
    
     <!--use SQL Tag extraction common part-->
    <sql id="if-title-author">
        <if test="title != null">
            and title = #{title}
        </if>
        <if test="author != null">
            and author = #{author}
        </if>
    </sql>
    
    <!--Use where needed**include**Label reference is enough-->
    <select id="queryBlogIF" parameterType="map" resultType="blog">
        select * from blog
        <where>

            <include refid="if-title-author" />
        </where>
    </select>
</mapper>

matters needing attention
- It is best to define SQL fragments based on a single table
- Do not have a where tag

12.6,foreach

data

Mapper interface

public interface BlogMapper {
    //Query the Blog of records 1, 2 and 3
    List<Blog> queryBlogForeach(Map map);
}

Mapper.xml file

<!--realization sql sentence: select * from blog where 1=1 and (id=1 or id = 2 or id=3)-->
<select id="queryBlogForeach" parameterType="map" resultType="blog">
    select * from blog
    <where>
        <foreach collection="ids" item="id" open="and (" close=")" separator="or">
            id = #{id}
        </foreach>
    </where>
</select>
<!--foreach Specify a collection[ collection="ids"]，Declares a collection item that can be used within an element body[ item="id"]And index( index)Variable. It is also allowed to specify the beginning[ open="and ("] And ending[ close=")"] String and separator between iterations of collection items[ separator="or"].  -->

Test class

@Test
public void testqueryBlogForeach(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
    
    HashMap map = new HashMap();
    ArrayList<Integer> ids = new ArrayList<>();
    ids.add(1);
    ids.add(2);
    ids.add(3);
    
    map.put("ids",ids);
    
    List<Blog> blogs = mapper.queryBlogForeach(map);
    for (Blog blog : blogs) {
        System.out.println(blog);
    }
    sqlSession.close();
}

Test query results

13. Cache (understand)

13.1 introduction

What is Cache?
- There is temporary data in memory.
- Put the data frequently queried by users in the cache (memory), and users do not need to query from the disk (relational database data file) to query from the cache, so as to improve the query efficiency and solve the performance problem of high concurrency system.
Why cache?
- Reduce the number of interactions with the database, reduce system overhead and improve system efficiency.
What kind of data can be cached?
Frequently queried and infrequently changed data.

13.2. Mybatis cache

MyBatis contains a very powerful query caching feature, which can easily customize and configure the cache. Caching can greatly improve query efficiency.
Two levels of cache are defined by default in MyBatis system: L1 cache and L2 cache
- By default, only L1 cache is enabled. (SqlSession level cache is also called local cache
- The L2 cache needs to be manually enabled and configured. It is based on the namespace level cache
- In order to improve scalability, MyBatis defines the Cache interface Cache. We can customize the L2 Cache by implementing the Cache interface

13.3. L1 cache

A level-1 cache is also called a local cache:
- The data queried during the same session with the database will be placed in the local cache
- If you need to get the same data in the future, you can get it directly from the cache. You don't have to query the database

testing procedure

Open log

The test queries the same record twice in a session

@Test
public void testqueryUserById(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);

    User user= mapper.queryUserById(1);
    System.out.println(user);

    System.out.println("============================");
    User user2 = mapper.queryUserById(1);
    System.out.println(user2);

    System.out.println(user==user2);

    sqlSession.close();
}

View log output

Cache invalidation:
- Query different things
- Adding, deleting and modifying may change the original data, so the cache must be refreshed
- Query different mappers
- Manually clean up the cache: sqlSession.clearCache();
The L1 cache is enabled by default and is only valid for one SqlSession, that is, the interval from getting the connection to closing the connection!

13.4 L2 cache

L2 cache is also called global cache. The scope of L1 cache is too low, so L2 cache was born
Based on the namespace level cache, a namespace corresponds to a L2 cache
Working mechanism
- When a session queries a piece of data, the data will be placed in the first level cache of the current session;
- If the current session is closed, the L1 cache corresponding to the session will be gone; but what we want is that when the session is closed, the data in the L1 cache will be saved to the L2 cache;
- The new session query information can get the content from the L2 cache;
- The data found by different mapper s will be placed in their corresponding cache (map);

step

Turn on global cache

<!--Show global cache enabled-->
<setting name="cacheEnabled" value="true"/>

Open in Mapper.xml where you want to use L2 cache

<!--At present Mapper.xml Using L2 cache in-->
<cache/>

<!--Custom parameters-->
<cache eviction="FIFO"
       flushInterval="60000"
       size="512"
       readOnly="true"/>

test

@Test
public void queryUserById2(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);

    User user= mapper.queryUserById(1);
    System.out.println(user);
    sqlSession.close();


    SqlSession sqlSession2 = MybatisUtils.getSqlSession();
    UserMapper mapper2 = sqlSession2.getMapper(UserMapper.class);

    User user2= mapper2.queryUserById(1);
    System.out.println(user2);

    System.out.println(user == user2);

    sqlSession2.close();
}

problem
- If only < cache / > is used without parameters, an error will be reported
  
  Caused by: java.io.NotSerializableException: com.ping.pojo.User
- Solution: serialize the entity class
```
@Data
public class User implements Serializable {
    private int id;
    private String name;
    private String pwd;
}
```
Summary:
- As long as the L2 cache is enabled, it is valid under the same Mapper
- All data will be put in the first level cache first
- Only when the session is committed or closed will it be committed to the secondary buffer

13.5. User defined cache - ehcache

Ehcache is a widely used open source Java distributed cache, mainly for general cache

Using ehcache in programs

Guide Package

<dependency>
    <groupId>org.mybatis.caches</groupId>
    <artifactId>mybatis-ehcache</artifactId>
    <version>1.1.0</version>
</dependency>

Specify in Mapper.xml to use our ehcache cache implementation
```
<cache type="org.mybatis.caches.ehcache.EhcacheCache"/>
```

Configuration file: ehcache.xml

<?xml version="1.0" encoding="UTF-8"?>
<ehcache xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:noNamespaceSchemaLocation="http://ehcache.org/ehcache.xsd"
         updateCheck="false">

    <!--
    diskStore:Is the cache path, ehcache There are two levels: memory and disk. This attribute defines the cache location of the disk. The parameters are explained as follows:
    user.home -User home directory
    user.dir -User's current working directory
    java.io.tmpdir -Default temporary file path-->

    <diskStore path="./tmpdir/Tmp_EhCache"/>

    <defaultCache
            eternal="false"
            maxElementsInMemory="10000"
            overflowToDisk="false"
            diskPersistent="false"
            timeToIdleSeconds="1800"
            timeToLiveSeconds="259200"
            memoryStoreEvictionPolicy="LRU"/>

    <cache
            name="cloud_user"
            eternal="false"
            maxElementsInMemory="5000"
            overflowToDisk="false"
            diskPersistent="false"
            timeToIdleSeconds="1800"
            timeToLiveSeconds="1800"
            memoryStoreEvictionPolicy="LRU"/>

    <!--
        defaultcache: Default cache policy, when ehcache This cache policy is used when the defined cache cannot be found. Only one can be defined.
    -->
    <!--
    name:Cache name.
    maxELementsInMemory :Maximum number of caches
    maxELementsonDisk :Maximum number of hard disk caches.
    eternaL:Whether the object is permanently valid, but once it is set. timeout Will not work.
    overfLowToDisk:Whether to save to disk when the system goes down
    diskExpiryThreadIntervalSeconds:The running time interval of disk failure thread is 120 seconds by default.
    clearonFlush:Whether to clear when the maximum memory is available.
    memoryStoreEvictionPolicy : The optional strategies are:LRU(Least recently used, default policy) FIFO(fifo),LFU(Minimum visits)
    FIF0， first in first out，This is the most familiar, first in, first out.
    LFU， less Frequently Used，This is the strategy used in the above example. To put it bluntly, it has always been the least used.
    LRU，Least Recently Used，Least recently used
    -->
</ehcache>

Posted by gregor63 on Wed, 22 Sep 2021 07:16:35 -0700

Programmer Group

Mybatis complete notes

1. Introduction

1.1. What is Mybatis

1.2 persistence

1.3. Durable layer

1.4 why do you need Mybatis

2. The first Mybatis program

2.1 construction environment

2.2. Create a module

2.3. Code writing

2.4 test

3,CRUD

3.1,Mapper.xml

3.2,select,insert,update,delete

3.3,Map

3.4. Fuzzy query

4. Configuration resolution

4.1. Core configuration file

4.2. Environment configuration

4.3. properties

4.4 type aliases

4.5 setting

4.6 other configurations

4.7 mappers

4.8 life cycle and scope

5. Solve the problem of inconsistency between attribute name and field name

5.1 alias

5.2. resultMap result set mapping

6. Log

6.1 log factory

6.2,LOG4J

7. Pagination

7.1. Limit paging

7.2 rowboundaries paging (not recommended)

7.3. MyBatis paging plug-in PageHelper (just understand)

8. Using annotation development

8.1 interface oriented programming

8.2 development using annotations

8.3,CRUD

9,Lombok

10. Many to one processing (taking teachers and students as an example)

10.1. Basic environment construction

10.2. Nested processing by query

10.3 nesting according to results

11. One to many processing (taking teachers and students as an example)

11.1. Nested processing according to results

11.2. Nested processing by query

12. Dynamic SQL

12.1 construction environment

12.2 use of IF

12.3 use of choose (when, otherwise)

12.4 use of trim, where and set

12.5 SQL fragment

12.6,foreach

13. Cache (understand)

13.1 introduction

13.2. Mybatis cache

13.3. L1 cache

13.4 L2 cache

13.5. User defined cache - ehcache

Hot Keywords