Be careful, there are pits

1.mybatis enables transactions by default. If you add, delete, or modify a transaction, you need to submit the transaction manually.
2. After integration with spring, there is no need to process transactions manually.

Related blog posts

1. Use of mybatis

MyBatis use

2. mybatis parameter transfer

mybatis is the method of passing parameters;

Introduction: what is MyBatis?

1. MyBatis is an excellent persistence layer framework, which supports custom SQL, stored procedures and advanced mapping.
2. MyBatis eliminates almost all JDBC code and the work of setting parameters and obtaining result sets.
3. 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.

The source code can be seen on github...

introduction

install

<dependency>
  <groupId>org.mybatis</groupId>
  <artifactId>mybatis</artifactId>
  <version>x.x.x</version>
</dependency>

Building SqlSessionFactory from XML

It is generally placed in a tool class. See Mybatis-01 for details

String resource = "org/mybatis/example/mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

mybatis-config.xml:

<?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>
  <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 resource="org/mybatis/example/BlogMapper.xml"/>
  </mappers>
</configuration>

Build SqlSessionFactory without XML

DataSource dataSource = BlogDataSourceFactory.getBlogDataSource();
TransactionFactory transactionFactory = new JdbcTransactionFactory();
Environment environment = new Environment("development", transactionFactory, dataSource);
Configuration configuration = new Configuration(environment);
configuration.addMapper(BlogMapper.class);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(configuration);

Get SqlSession from SqlSessionFactory

previous

try (SqlSession session = sqlSessionFactory.openSession()) {
  Blog blog = (Blog) session.selectOne("org.mybatis.example.BlogMapper.selectBlog", 101);
}

But now there is a more concise way - use an interface that matches the parameters and return values of the specified statement (such as BlogMapper.class). Now your code is not only clearer and more type safe, but also you don't have to worry about the possible wrong string literal and forced type conversion.

try (SqlSession session = sqlSessionFactory.openSession()) {
  BlogMapper mapper = session.getMapper(BlogMapper.class);
  Blog blog = mapper.selectBlog(101);
}

The first MyBatis program

Namespace:

That is, the namespace in mapper.xml corresponding to mapper is used to bind the interface and the corresponding XML configuration file....

Scope and lifecycle

Tips:
The dependency injection framework can create thread safe, transaction based sqlsessions and mappers and inject them directly into your bean s, so you can directly ignore their life cycle.

SqlSessionFactoryBuilder
This class can be instantiated, used, and discarded. Once 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 SqlSessionFactory builder to create multiple SqlSessionFactory instances, 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.

Explanation: once created, it is no longer needed
Local invariant

SqlSessionFactory
SqlSessionFactory = = once created, it should always exist during the running of the application. There is no reason to discard it or recreate another instance==

The best practice of using SqlSessionFactory is not to create it repeatedly during application operation. Rebuilding SqlSessionFactory multiple 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.

Explanation:

1. It can be imagined as: database connection pool
2. Once SqlSessionFactory is created, it should always exist before the application runs. There is no reason to discard it or recreate another instance.
3. Therefore, the best scope of SqSessionFactory is the application scope
4. The simplest is to use singleton mode or static singleton mode...

SqlSession
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. Never put the reference of SqlSession instance in the static field of a class, or even the instance variable of a class. You must never put a reference to a SqlSession instance in any type of managed scope, such as HttpSession in the Servlet framework. If you are currently using a Web framework, 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==

Explanation:

1. A request to connect to the pool!
2. The instance of SqlSession is not thread safe, so it cannot be shared, so it is best used for request or method scope
3. It needs to be closed immediately after use

Each Mapper here represents a business.

XML configuration

This is for the configuration in mybatis-config.xml.

The order of various configurations in mybatis-config.xml must be as shown in the above figure, otherwise an error will be reported...

properties

We can reference the configuration file through the properties property.

These properties can be configured externally and can be replaced dynamically. You can configure these properties either in a typical Java properties file or in a child element of the properties element. For example:

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

<properties resource="db.properties"/>

<dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>

be careful:

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

1. First read the attribute specified in the properties element body.
2. 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.
3. Finally, read the attributes passed as method parameters and overwrite the previously read attributes with the same name.

Therefore, the attributes passed through the method parameters have the highest priority, followed by the configuration file specified in the resource/url attribute, and the attributes specified in the properties element have the lowest priority.

settings

Very important tuning settings in MyBatis, which change the runtime behavior of MyBatis. For example, log related settings are inside...

For example: lazy loading, caching, hump naming, automatic mapping

Log logilpl

Specify the specific implementation of Mybatis log. If it is not specified, it will be found automatically.

STDOUT_LOGGING master
SLF4J
LOG4J master
LOG4J2
JDK_LOGGING
COMMONS_LOGGING
NO_LOGGING

STDOUT_LOGGING (Mybatis-04)

Standard log output can be realized without configuration.

Log4j (Mybatis-04)

Introduction:

1. 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,

use:

1. Import package first

<dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>

1. log4j.properties

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/zhx.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

Type aliases

1. Type alias can set an abbreviated name for a Java type. It is only used for XML configuration and is intended to reduce redundant fully qualified class name writing. For example:

<typeAliases>
  <typeAlias alias="Author" type="domain.blog.Author"/>
  <typeAlias alias="Blog" type="domain.blog.Blog"/>
  <typeAlias alias="Comment" type="domain.blog.Comment"/>
  <typeAlias alias="Post" type="domain.blog.Post"/>
  <typeAlias alias="Section" type="domain.blog.Section"/>
  <typeAlias alias="Tag" type="domain.blog.Tag"/>
</typeAliases>

When configured in this way, Blog can be used anywhere domain.blog.Blog is used.

2. You can also specify a package name. MyBatis will search for the required Java beans under the package name, such as:

<typeAliases>
  <package name="domain.blog"/>
</typeAliases>

Each Java Bean in the package domain.blog will use the initial lowercase unqualified class name of the bean as its alias without annotation. For example, the alias of domain.blog.Author is author;

3. If there is an annotation, the alias is its annotation value. See the following example:

@Alias("author")
public class Author {
    ...
}

Environment configurations

Note: multiple environment s can be configured in the same xml file

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

However, remember that although multiple environments can be configured, only one environment can be selected for each SqlSessionFactory instance.

Therefore, if you want to connect two databases, you need to create two SqlSessionFactory instances, one for each database. If there are three databases, you need three instances, and so on. It's easy to remember:

Each database corresponds to a SqlSessionFactory instance
To specify which environment to create, simply pass it as an optional parameter to SqlSessionFactoryBuilder. The two method signatures that can accept environment configuration are:

SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader, environment);
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader, environment, properties);

If the environment parameter is ignored, the default environment will be loaded as follows:

SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader);
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader, properties);

The environments element defines how to configure the environment:

<environments default="development">
  <environment id="development">
    <transactionManager type="JDBC">
      <property name="..." value="..."/>
    </transactionManager>
    <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>

Tip: if you are using Spring + MyBatis, there is no need to configure the transaction manager, because the Spring module will use its own manager to override the previous configuration.

Transaction manager

There are two types of transaction managers in MyBatis (that is, type="[JDBC|MANAGED]):

1. JDBC – this configuration directly uses JDBC's commit and rollback facilities, which rely on connections obtained from data sources to manage transaction scopes.
3. MANAGED – this configuration does little. It never commits or rolls back a connection, but lets the container manage the entire life cycle of the transaction (such as the context of the JEE application server). By default, it closes the connection. However, some containers do not want the connection to be closed, so you need to set the closeConnection property to false to prevent the default closing behavior. For example:

<transactionManager type="MANAGED">
  <property name="closeConnection" value="false"/>
</transactionManager>

datasource data source (connect to database)

The dataSource element uses the standard JDBC data source interface to configure the resources of the JDBC connection object.

Common data sources: dbcp,c3p0,druid

There are three built-in data source types (that is, type=[UNPOOLED|POOLED|JNDI] "):

UNPOOLED POOLED

mappers (with a small pit)

Function: tell MyBatis where to find SQL mapping statements.

<!-- Use resource references relative to Classpaths -->
<mappers>
  <mapper resource="org/mybatis/builder/AuthorMapper.xml"/>
  <mapper resource="org/mybatis/builder/BlogMapper.xml"/>
  <mapper resource="org/mybatis/builder/PostMapper.xml"/>
</mappers>

<!-- Use the mapper interface to implement the fully qualified class name of the class -->
<mappers>
  <mapper class="org.mybatis.builder.AuthorMapper"/>
  <mapper class="org.mybatis.builder.BlogMapper"/>
  <mapper class="org.mybatis.builder.PostMapper"/>
</mappers>

Note: 1. The interface and its mapper configuration file must have the same name!
2. The interface and its mapper configuration file must be in the same package...

<!-- Register all the 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......

//Abandoned

<!-- 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>

Other configurations

plugins plug-in
mybatis-generator-core
mybatis-plus
Universal mapper

XML mapper

Cache – the cache configuration for this namespace.
Cache ref – refers to the cache configuration of other namespaces.
resultMap – describes how to load objects from the database result set. It is the most complex and powerful element.
parameterMap – old style parameter mapping. This element has been deprecated and may be removed in the future! Please use inline parameter mapping. This element will not be described in the document.
sql – a repeatable sentence block that can be referenced by other statements.
Insert – map insert statements.
Update – map update statements.
Delete – map delete statements.
select – map query statements.

parameter

mybatis pass parameters

All the statements we have seen before use simple parameter forms. In fact, parameters are very powerful elements of MyBatis. For most simple usage scenarios, you do not need to use complex parameters and specify paramType, such as:

<select id="selectUsers" resultType="User">
  select id, username, password
  from users
  where id = #{id}
</select>

The above example illustrates a very simple named parameter mapping. Since the parameter type (parameterType) will be automatically set to int, this parameter can be named at will. Original type or simple data type (such as Integer and String) Because there are no other attributes, their values will be used as parameters. However, if a complex object is passed in, the behavior will be a little different. For example:

<insert id="insertUser" parameterType="User">
  insert into users (id, username, password)
  values (#{id}, #{username}, #{password})
</insert>

If a parameter object of User type is passed into the statement, the id, username and password attributes are looked up, and their values are passed into the parameters of the preprocessing statement.

Pass parameters using map

Assuming that there are too many tables, fields or parameters in the entity class or database, we should consider using map!

int addUser1(Map<String,Object> map);

<insert id="addUser1" parameterType="map">
        insert into mybatis.user(id, name, pwd) VALUES (#{userId},#{userName},#{passWord})
    </insert>

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

        HashMap<String, Object> map = new HashMap<>();
        map.put("userId",7);
        map.put("userName","ten yi number");
        map.put("passWord","123412342135");

        userMapper.addUser1(map);
        //Commit transaction
        sqlSession.commit();//Very important

        sqlSession.close();
    }

String substitution

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?) . this is safer and faster. It is usually the first choice, but sometimes you just want to insert a non escaped string directly into the SQL statement. For example, ORDER BY clause. At this time, you can:

ORDER BY ${columnName}

Result map (solve the mismatch between the field name in the database and the attribute name in the entity class)

resultMap is not explicitly specified. 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 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 JavaBean or POJO (Plain Old Java Objects) As a domain model, 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 name in the select statement.

<!-- 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 columns to the properties of the JavaBean according to the property name. If the column name and property name cannot match, you can set the column alias (which is a basic SQL feature) in the SELECT statement to complete the matching. For example:

Method 1:

<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>

Method 2:
You will find that none of the above examples requires explicit configuration of ResultMap. The configuration of ResultMap is shown here

Note: you only need to configure unmatched fields, regardless of matching...

<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>

Advanced result mapping (focus on one to many and many to one)

cache

Dynamic SQL

reflection

How to write fuzzy query?

1. Pass wildcards when executing java code

 <select id="getUserLike" resultType="com.zhx.pojo.User" parameterType="String">
        select * from mybatis.user where name like #{value}
    </select>

@Test
    public void getUserLike(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        //Fuzzy query
        List<User> list = userMapper.getUserLike("%Zhang%");
        for (User user : list) {
            System.out.println(user);
        }

        sqlSession.close();
    }

1. Using wildcards in sql splicing

How does mybatis prevent sql injection

The Mybatis framework is widely used in daily development. For example, there is often a question in the interview: the difference between $and # is that # can prevent SQL injection. Today, let's take a look at how it implements SQL injection

What is SQL injection

Before discussing how to implement it, let's first understand what SQL injection is. We have a simple query operation: query a user's information according to id.
Its sql statement should be like this: select * from user where id =.
We fill in the id for query according to the incoming conditions.
If the normal operation passes in a normal id, such as 2, the statement becomes
select * from user where id =2.
This statement can run normally and meet our expectations.
However, if the passed in parameter becomes' or 1=1, the statement becomes select * from user where id = '' or 1=1.
Let's think about the execution result of this statement?
It will query all the data in our user table. Obviously, this is a big mistake. This is SQL injection.

How does Mybatis prevent SQL injection

As mentioned at the beginning, you can use # to prevent SQL injection. It is written as follows:

SELECT * FROM user where id = #{id} another way to query in mybatis is to use $, which is written as follows:

select * from user where id = ${id}

When we continue to call these two methods externally, we find that if we pass in safe parameters, the results are not different. If we pass in unsafe parameters, the first method used # can not query the results (select * from user where id = '' or 1=1), but the second parameter, namely $, will get all the results.

And if we print the sql, we will find that when adding # it, the sql executed to the database is: select * from user where id = '' or 1=1 ', which will add a layer of quotation marks outside our parameters. When using $, its executed sql is select * from user where id =' 'or 1=1.

Is it OK to discard $1

We use # also can complete the function, and use the function, and the use is still dangerous, so we won't use $in the future.

No, it only has problems in our scenarios, but it still plays an irreplaceable role in some dynamic query scenarios. For example, dynamically modify the table name select * from ${table} where id = #{id}. We can dynamically change the query table when the returned information is consistent, which is also the dynamic power of mybatis.

Solve the problem of inconsistency between attribute name and field name

Why do inconsistencies cause problems

1.MyBatis will automatically create a ResultMap behind the scenes, and then map the columns to the properties of JavaBean according to the property name. If the column name and property name cannot match. The value found will be NULL.
2. You can set the column alias (which is a basic SQL feature) in the SELECT statement to complete the matching

Corresponding entity class:

	private int id;
    private String name;
    private String password;

<select id="getUserById" parameterType="int" resultType="User">
		The type processor does not exist in the query result pwd
		//select id,name,pwd from mybatis.user where id = #{id}
        select * from mybatis.user where id = #{id}
    </select>

Result: User{id=1, name = 'Zhang', password = 'null'}

Solution: alias

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

Result: User{id=1, name = 'Zhang', password = '123456'}

Dynamic sql

What is dynamic SQL: dynamic SQL is to generate different SQL statements according to different conditions

Log factory

Paging implementation

mybatis-04

Think: why pagination?
1. Reduce the amount of data processing each time

Use limit

Use limit paging
Formula:

SELECT * from user limit startIndex, endIndex;

Using rowboundaries