Mybatis is a persistent layer framework that needs to interact with a database, so to test MyBatis, we should first create a database table and insert some data into the table for our use.

I. Preparation stage

1. Create a database and create a user table

create table user( id int(5) primary key, name varchar(20), age int(2), description varchar(100) ) default charset=utf8;  Insert into user values(1,' 1 ',21,' 1 '); Insert into user values(1, 2, 1); Insert into user values(3,' ultraman ',56,' not bad '); Insert into user values(4,' dika ',42,' not bad '); Insert into user values(5,' Jackie ',66,' nice ');Copy the code

2. Create a regular Maven project

Once we have created the project, we need to do a few steps.

  1. Adding a dependency package
<dependencies> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.11</version> </dependency> <dependency> <groupId>org.mybatis</ artifactId> <version>3.5.3</version> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId> <version>1.18.16</version> </dependency> <dependency> <groupId>junit</groupId> <artifactId> The < version > 4.12 < / version > < scope > test < / scope > < / dependency > < / dependencies >Copy the code

The first two are required dependencies, lombok was introduced to simplify bean development and junit was introduced for testing purposes.

  1. Create two configuration files in resource

Database. properties is configured as follows.

driver=com.mysql.cj.jdbc.Driver
url = jdbc:mysql://localhost:3306/lvjianyou
username=root
password=root
Copy the code

Mybatis. XML is configured as follows (mybatis main configuration file, must! Filename can be changed)

<? The XML version = "1.0" encoding = "utf-8"? > <! DOCTYPE configuration PUBLIC "- / / mybatis.org//DTD Config / 3.0 / EN" "http://mybatis.org/dtd/mybatis-3-config.dtd" > <configuration> <! Properties --> <properties resource="database.properties"/> < Settings > <! <setting name="mapUnderscoreToCamelCase" value="true"/> <setting name="cacheEnabled" value="false"/> <! Setting name="logImpl" value="STDOUT_LOGGING" /> </ Settings > <! -- Configure aliases in the same order > <typeAliases> <package name="com.jianyou.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 resource="mapper/ usermapper.xml "/> <! - the class attribute specifies read mapping rules from a class, used to annotate the binding of SQL - > < mapper class = "com. Jianyou. Mapper. UserMapper" / > <! <package name="com.jianyou.mapper"/> </mappers> </configuration>Copy the code
  1. Write entity classes corresponding to database tables for easy mapping
@AllArgsConstructor
@NoArgsConstructor
@Data
public class User {
  private int id;
  private String name;
  private int age;
  private String description;
}
Copy the code

We don’t need to write get and set methods anymore. That’s why Lombok was introduced

  1. Write a Mapper interface
public interface UserMapper {
  @Select("select * from user")
  List<User> findAllUser();
}
Copy the code

The overall project structure is as follows:

3.. Use Junit for testing

Create a MabatisTest in the Test package to test

@Test public void test1() throws IOException { InputStream is = Resources.getResourceAsStream("mybatis.xml"); SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is); SqlSession sqlSession = factory.openSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); List<User> allUser = mapper.findAllUser(); for (User user : allUser) { System.out.println(user); }}Copy the code

The test results are as follows

We can also use XML to configure SQL statements and create a usermapper.xml file

<? The 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.jianyou.mapper.UserMapper"> <select id="findAllUser" resultType="com.jianyou.pojo.User"> select * from user </select> </mapper>Copy the code

Note: Keep the XML file in the same directory as the Mapper interface!

The basic configuration is successful!

Dynamic SQL

  • if
  • choose (when, otherwise)
  • trim(where,set)
  • foreach

2.1 the if

The most common use of if is in conditional queries. We provide users with multiple queries, and users tend to do some filtering queries. Add userByCondition to UserMapper; add SQL to usermapper. XML;

<select id="findUserByCondition" resultType="com.jianyou.pojo.User" parameterType="userVo"> select * from user where id>0 <if test="name ! = null "> / / if the incoming user name is not null and name =" '" + # {name} + "' "/ / stitching on name < / if > < if test =" age! </select * from (select * from (select * from (select * from (select * from (select * from (select * from (select * from (select * from (select * from)))));Copy the code

The result is as follows:

It can be seen that Mybatis has stitched up the SQL statement for us automatically, and we have successfully queried the result.

2.2 choose the when otherwise

There are times when we don’t want to use all of the conditional statements and just want to select one or two. In this case, Mybatis provides the Choose element, which is a bit like the Switch statement in Java.

<select id="findUserByChoose" resultType="com.jianyou.pojo.User" parameterType="UserVo"> select * from user <where> <choose> <when test="name! Name like #{name} </when> <when test="age! =0"> age > #{age} </when> <otherwise> age = 21 </otherwise> </choose> </where> </select>Copy the code

Let’s add an age to the condition

The query results are as follows:

So choose has come into play.

2.3 the trim where the set

Let’s test where and look at the following SQL statement

<select id="findUserSecond" resultType="com.jianyou.pojo.User" parameterType="userVo"> select * from user where <if test="age ! =0"> and age= #{age} </if> <if test="name ! = null"> and name=#{name} </if> </select>Copy the code

What happens if we don’t pass in age and name? Select * from user where (select * from user where (select * from user where (select * from user where)) Select * from user where name =? All the purpose of where is to insert the “where” clause only if an if condition is worthwhile. Also, if subsequent content begins with “AND” or “or”, the WHERE element knows how to remove them.

Where is obvious:

1. Automatically add where words in SQL based on our criteria.

2. The first AND after where will be automatically removed to make it meet our conditions.

The set tag is similar to where, except that set is used for modifying statements.

  1. Automatically add a set after a modification condition
  2. Remove a “, “from SQL.

Trim tabs

attribute describe
prefix Prefix to concatenate SQL statements
suffix Suffix to concatenate SQL statements
prefixOverrides Removes the keyword or character before the SQL statement specified by the prefixOverrides attribute, assuming that the attribute is specified as “AND”. When the SQL statement begins with “AND”, the trim tag removes the “AND”.
suffixOverrides Remove keyword or character from SQL statement specified by suffixOverrides attribute. If SQL statement ends with “,”, trim tag removes “,”

foreach

When we need to iterate over a collection, it’s usually when building In conditional statements, such as

<select id="findUserByIds" resultType="com.jianyou.pojo.User"> select * from user where id in <foreach collection="list"  item="item" index="index" open="(" separator="," close=")"> #{item} </foreach> </select>Copy the code

If we execute it, the result is as follows:

When doing mybatis mapper. XML file, we often use such a situation: dynamic generation of SQL statement query conditions, this time we can use Mybatis foreach

The main attributes of foreach element are item, index, collection, open, separator, and close.

  • Item: Alias for iterating elements in a collection. This parameter is mandatory.
  • Index: In list and array,index is the sequence number of an element. In map,index is the key of an element. This parameter is optional
  • Open: the opening symbol for foreach code, usually used with (close=”)”. Often used in(),values(). This parameter is optional
  • Separator: separates elements. For example, in(), separator=”,” automatically separates elements with “,” to avoid manually entering commas and SQL errors such as in(1,2,). This parameter is optional.
  • Close: The close symbol for foreach code, usually used with open=”(“. Often used in(),values(). This parameter is optional.
  • collection:For foreach objects, List objects default to “List” as keys, array objects default to “array” as keys, and Map objects have no default keys. Of course, @param (“keyName”) can be used as an input parameter to set the key. After setting the keyName, the list and array will be invalidated. In addition to the case of an input parameter, there is also the case of a field as a parameter object. For example, if the User has the attribute List IDS. The input parameter is the User object, then the collection = “IDS “.If the User has the attribute Ids Ids; Where Ids is an object, Ids has an attribute List ID; The input parameter is the User object, so collection = “ids.id”

When using foreach, the most critical and error-prone attribute is the collection attribute, which must be specified. However, the value of this attribute is different in different cases, mainly in the following three cases:

  • If a single argument is passed and the argument type is a List, the collection property value is List.
  • If a single argument is passed and the argument type is array, the collection property value is array.
  • If multiple parameters are passed in, we need to encapsulate them into a Map, of course, single parameter can also be encapsulated into a Map, in fact, if you pass in the parameter, MyBatis will encapsulate it into a Map, Map key is the parameter name, In this case, the collection property value is the key of the List or array object in its own encapsulated map.

Three cache

Mybatis cache is divided into level 1 cache and level 2 cache, the following are introduced respectively.

3.1 Level 1 Cache

Mybatis level 1 cache scope is session, in the same session, if the same SQL(same statement and parameter) is executed, Mybatis will not execute SQL, but directly from the cache hit return.

Principle: Mybatis will execute the query to cache hit, if hit, return directly, no hit return SQL, query from the database.

Level 1 caching is enabled by default in Mybatis and cannot be turned off. Note that there must be two conditions for level 1 caching to be satisfied!

  1. The same session
  2. Same SQL statement

3.2 Level 2 Cache

The scope of mybatis level 2 cache is a namespace of mapper. SQL in the same namespace can be hit from the cache. Enable level 2 cache:

  1. Add it in a namespace (you can also configure it in a global configuration file.)

  1. Java entity classes must implement serialization

Advanced result Mapping ResultMap

4.1 One-to-one Mapping

@Data public class Order { private int id; private Integer userId; private String number; private Date createTime; private String note; private User user; // Only one user can place an order.Copy the code

Do this in mapper.xml

<! -- One order for one user --> <! - the property: Column: a specific column in the database --> <resultMap ID ="OrderUserResultMap" type=" Order ">< ID Property =" ID "column=" ID "></ ID >< result property="createTime" column="create_time"></result> <result property="note" column="note"></result> <result property="userId" column="user_id"></result> <result property="number" column="number"></result> <! -- Assocition: configure one-to-one properties --> <! -- Property: order property name --> <! -- javaType: JavaType ="user"> < ID Property =" ID "column=" ID "></ ID >< result Property ="name" column="name"/> <result property="age" column="age"/> <result property="description" column="description"/> </association> </resultMap>Copy the code

4.2 One-to-many Mapping

@Data public class User implements Serializable { private int id; private String name; private int age; private String description; private List<Order> orders; // a user has multiple orders}Copy the code

Configuration in mapper.xml

<! <resultMap ID ="UserOrderResultMap" type="user"> < ID Property =" ID "column=" ID "/> <result property="description" column="description"/> <result property="name" column="name"/> <result property="age" column="age"/> <! Start configuring one-to-many relationships --> <! -- Property: the name of the collection in the entity class --> <! -- Javatype: Type of collection --> <! - ofType: JavaType ="list" ofType="order"> < ID Property =" ID "column=" ID "/> <result property="number" column="number"/> <result property="createTime" column="create_time"/> <result property="note" column="node"/> </collection> </resultMap>Copy the code

Five often meet to introduce the questions

What is the difference between #{} and ${}?

${} is a string substitution.

  1. Mybatis will replace #{} with? Call the set method in PreparedStatement to assign the value. Using #{} can effectively prevent SQL injection, improve system security;

  2. MyBatis replaces {} with the value of a variable.

5.2 How many pagination methods are there in Mybatis?

  1. An array of paging
  2. SQL paging
  3. Interceptor paging
  4. RowBounds paging

5.3 How does Mybatis paginate? How does paging plug-ins work?

MyBatis uses the RowBounds object for paging, which is memory paging performed against a ResultSet ResultSet rather than physical paging. Physical paging can be done by writing parameters with physical paging directly in SQL, or physical paging can be done using paging plug-ins.

The basic principle of the paging plug-in is to use the plug-in interface provided by MyBatis to implement a custom plug-in to intercept the SQL to be executed in the interception method of the plug-in, and then rewrite the SQL to add the corresponding physical paging statement and physical paging parameters according to the dialect.

5.4 What is the difference between logical paging and physical paging in Mybatis?

  1. Physical pages are not necessarily faster than logical pages, and logical pages are not necessarily faster than physical pages.

  2. Physical paging is always better than logical paging: there is no need to impose pressure on the application side that belongs to the database side, even if there is an advantage in speed, but other performance advantages more than make up for it.

5.5 What is the principle of delayed loading of Mybatis?

Mybatis only supports lazy loading of association associative objects and collection associative objects. Association refers to one-to-one and collection refers to one-to-many queries. In MyBatis configuration file, you can configure whether to enable lazy-loading lazyLoadingEnabled = true | false. ]

It works like this: Use CGLIB to create a proxy object for the target object. When the target method is called, the interceptor method is entered, such as a.geb ().getName(). The interceptor invoke() method finds that A.geb () is null. A.setb (); a.getName (); a.getname (); a.getb (); This is the basic principle of lazy loading.

5.6 What is mybatis level 1 cache and level 2 cache?

The range of level 1 cache is a session, when the same session and the same SQL statement is executed, Mybatis will not query the database, but directly return from the cache, always open by default.

Level 2 cache is a namespace and is not enabled by default. To enable level 2 cache, you can configure it in the main configuration file or in an XML file. The corresponding Java entity class must implement the Serializable interface.

Cache update: When cruD operations are performed in a session or namespace scope, all select operations in that scope are cleared by default.

5.7 What actuators do Mybatis have?

Mybatis has three basic actuators.

  1. SimpleExecutor: Opens a Statement object every time an update or select is executed, and closes the Statement object immediately.
  2. ReuseExecutor: Performs an update or select operation, uses the SQL as the key to search for a Statement object, uses it if it exists, and creates a Statement object if it does not exist. After the Statement object is used, it is stored in the Map for future use. In short, the Statement object is reused;
  3. BatchExecutor: Update (no SELECT, JDBC batch does not support SELECT), add all SQL to the batch (addBatch()), and wait for execution (executeBatch()), which caches multiple Statement objects. Each Statement object is addBatch(), and the executeBatch() batch is executed one by one. Same as JDBC batch processing.