The first figure: www.zcool.com.cn/work/ZNTE1M…

Mybatis

---

Benefits of frameworks

The framework is a semi-finished product, which has encapsulated the basic code and provided the corresponding API. When using the framework, developers directly call the encapsulated API, which can save a lot of code writing, thus improving the work efficiency and development speed

1. Reuse of code has increased, and software productivity and quality have improved.
2. The standardization of code structure reduces the cost of communication between programmers and future maintenance;
3. Faster development, fewer developers, lower maintenance costs,
4. Reduce development time and difficulty

Learning goals

Master:

Basic use of Mybatis, Java log processing framework, Mybatis configuration perfect, SqlSession common API, Mapper dynamic proxy, dynamic SQL, Mybatis cache, Mybatis multi-table associated query, the use of Mybatis annotations , the use of Mybatis Generator tool, PageHelper page plug-in, Mybatis and Servlet integration

The ORM, Object – Relationl Mapping

Advantages and disadvantages of MyBatis framework

---

Object relational mapping, its function is to make a mapping between relational database and object processing. Business entities in a project can be represented in two forms: objects and relational data, that is, objects in memory and relational data in a database.

Disadvantages of JDBC: The need to manually complete the object-oriented Java language, relational database data conversion between cumbersome code, affecting the development efficiency.

ORM: in Java object-oriented languages, data conversion between relationship oriented database is needed, the data in the database is not used directly, needs to be converted to object is required to use, but every time when development needs to establish a database and then in the data in the database into we can manipulate objects; ORM therefore acts as a bridge to transformation, eliminating the need for developers to deal with SQL statements.

ORM maps databases to objects

• Table > class
• Record –> Object
• Field –> Attributes of an object

Mybatis framework introduction

MyBatis is an excellent persistence layer framework that supports customized SQL, stored procedures, and advanced mapping. MyBatis avoids almost all of the JDBC code and manual setting of parameters and fetching result sets. MyBatis can configure and map native information using simple XML or annotations to map interfaces and Java’s POJOs (Plain OrdinaryJava objects) to records in the database. MyBatis is a semi-automatic ORM framework, its essence is the encapsulation of JDBC. Using MyBatis requires programmers to write SQL commands, not a single line of JDBC code.

Persistence layer frame: Optimizes the speed of access to the database, and reduces the database access frequency, relationship through object relational mapping data in the database into objects, then the contents of the object store is in the database (if you need to perform is stored into the memory), when the program needs a certain attributes of the object executes, no need to convert from the database access and you can get.

Compare with Hibernate:

Hibernate is a fully automated ORM framework. Because Hibernate creates a complete mapping between Java objects and database tables, the database can be manipulated in a completely object-oriented way, eliminating the need for programmers to write handwritten SQL statements. MyBatis also requires handwritten SQL statements, so it is semi-automated and requires more work than Hibernate. Why semi-automated Mybatis automated Hibernate popular?

MyBatis requires handwritten SQL statements, so it’s more work than Hibernate. However, it is precisely because of the custom SQL statements that it is more flexible and optimizable than Hibernate. MyBatis will leave the work of handwritten SQL statements to developers, which can define SQL more accurately, more flexible, and easier to optimize performance. The performance of two SQL statements that complete the same function may differ from ten to dozens of times. In the Internet system with high concurrency and fast response requirements, the impact on performance is more obvious. MyBatis provides good support for stored procedures.

Mybatis jar package introduction

1. Asm-7.1. jar: bytecode modification framework
2. Javassist-3.27.0-ga.jar: Used to examine, “dynamically” modify, and create Java classes. Function is similar to JDK reflection function, but more powerful than reflection function
3. Cglib-3.0.jar: a technique for implementing dynamic proxies, used for lazy loading
4. Ognl-3.2.14.jar: Short for Object Navigation Graph Language, powerful expression language toolkit. Used in dynamic SQL and ${param}
5. Commons-logging-1.2. jar: log package
6. Slf4j-api-1.7.30.jar: log package
7. Slf4j-log4j12-1.7.30.jar: log package
8. Log4j-1.2.17.jar: log package
9. Log4j-api-2.13.3. jar: log package
10. Log4j-core-2.13.3. jar: log package

Core API

SqlSessionFactoryBuilder

The purpose of the SqlSessionFactoryBuilder is to create an SqlSessionFactory object. When the SqlSessionFactory object is created, the SqlSessionFactoryBuilder is deactivated, so it can only exist in the method that created the SqlSessionFactory, not permanently. So the best scope for an instance of SqlSessionFactoryBuilder is the method scope.

SqlSessionFactory

Can be thought of as a database connection pool, which is used to create SqlSession interface objects. Once SqlSessionFactory is created, it should be stored for a long time until the MyBatis application is no longer used, so it can be considered that the SqlSessionFactory life cycle is equivalent to the MyBatis application cycle. Since SqlSessionFactory is an access pool for the database, it holds the access resource for the database. If more than one SqlSessionFactory is created, there will be more than one database connection pool, which is not conducive to the control of database resources, database connection resources will be consumed, the system downtime and other situations, so try to avoid this situation. So the SqlSessionFactory is a singleton that is shared across the application.

SqlSession

If SqlSessionFactory is a database Connection pool, SqlSession is a database Connection object. You can execute multiple SQL transactions in one transaction. The transaction is then committed or rolled back using its COMMIT and ROLLBACK methods. SqlSession should exist in a business request. After processing the complete request, close the connection and return it to the SqlSessionFactory. Otherwise, the database resources will be exhausted quickly and the system will crash. catch… finally… Statement to ensure that it closes correctly

Mapper

The mapping. It consists of a Java interface and AN XML file (or annotations) that needs to be given the corresponding SQL and mapping rules, and is responsible for sending the SQL to execute and returning the results. Since SqlSession is closed, its database connection resources will also disappear, so its lifetime should be less than or equal to the lifetime of SqlSession. Mapper represents business processing in a request, so it should be discarded in a request once the relevant business has been processed.

Mybatis configuration file

Global configuration files and mapping configuration files: if global configuration to smell is not connect to the database configuration has the environment Settings, then mapping the configuration file is to write SQL statements (JDBC SQL statements and code together and do CRUD, Mybatis is carried on the section I, is a function code has the availability, development of simple.

---

Global configuration file

The name of the global configuration file is custom and needs to be placed in the SRC directory in the JavaProject project. The global configuration file is used to complete some global configurations, such as setting the Mybatis framework, alias Settings, environment Settings, and specifying mapping configuration files.

      
<! DOCTYPEconfiguration 
PUBLIC "- / / mybatis.org//DTD Config / 3.0 / EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">

<configuration>
</configuration>
Copy the code

---

1. The properties TAB

Properties can be defined internally in the properties TAB, or externally in the Properties file. ${name} can be used to retrieve values for both internal and external definitions.

Internal definitions in the configuration file

<properties>
    <property name="jdbc.driver" value="com.mysql.jdbc.Driver"/>
    <property name="jdbc.url" value="jdbc:mysql://localhost:3306/bjsxt"/>
    <property name="jdbc.username" value="root"/>
    <property name="jdbc.password" value="root"/>
</properties>
Copy the code

External definitions in the configuration file

<properties resource="db.properties"></properties>
Copy the code

---

2. Settings TAB

The setting tag is used to configure some behaviors of MyBatis framework runtime, such as cache, lazy loading, result set control, actuator, paging Settings, naming rules and a series of control parameters. All the setting Settings are placed in the parent tag Settings tag (or can not be configured).

<settings>
    <setting name="cacheEnabled" value="true"/>
    <setting name="lazyLoadingEnabled" value="true"/>
    <setting name="multipleResultSetsEnabled" value="true"/>
    <setting name="useColumnLabel" value="true"/>
    <setting name="useGeneratedKeys" value="false"/>
    <setting name="autoMappingBehavior" value="PARTIAL"/>
    <setting name="autoMappingUnknownColumnBehavior" value="WARNING"/>
    <setting name="defaultExecutorType" value="SIMPLE"/>
    <setting name="defaultStatementTimeout" value="25"/>
    <setting name="defaultFetchSize" value="100"/>
    <setting name="safeRowBoundsEnabled" value="false"/>
    <setting name="mapUnderscoreToCamelCase" value="false"/>
    <setting name="localCacheScope" value="SESSION"/>
    <setting name="jdbcTypeForNull" value="OTHER"/>
    <setting name="lazyLoadTriggerMethods" value="equals,clone,hashCode,toString"/>
</settings>
Copy the code

---

3. TypeAliases label

A type alias sets an abbreviated name for a Java type.

<typeAliases>
	<typeAlias alias="user" type="com.bjsxt.pojo.User" />
</typeAliases>
Copy the code

You can also specify a package name under which MyBatis will search for the desired Java beans

<typeAliases>
	<package name="com.bjsxt.pojo"/>
</typeAliases>
Copy the code

---

4. Environments label

Configure the environment for connecting to the database. You can configure multiple environments, such as the environment for developing, testing, and releasing products

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

TransactionManager node

Transaction handler. There are two transaction managers in Mybatis, namely type = JDBC or type = MANAGED

• JDBC: This configuration directly uses JDBC commit and rollback transactions, which rely on connections obtained from data sources to manage transaction scope

• MANAGED: does not do transactions in Mybatis, obtains transactions according to the development framework in JavaEE development standards

---

5. The dataSource tag

Configuring the data connection source (resource for JDBC connection objects, obtaining the data connection pool)

• UNPOOLED: Direct connection
• POOLED: pool connection
• JNDI: Use the JNDI method to connect

---

6. Mapper label

Specify the mapping configuration file

Use the relative classpath to specify the mapping configuration file

<mappers>
	<mapper resource="com/bjsxt/mapper/UserMapper.xml"/>
</mappers>
Copy the code

Use the filter:/// protocol to specify the mapping profile

<mappers>
<mapper
	url="file:///D:\code\mybatis\src\com\bjsxt\mapper\UserMapper.xml"/>
</mappers>
Copy the code

Specifying the mapping Interface

<mappers>
	<mapper class="com.bjsxt.mapper.UserMapper"/>
</mappers>
Copy the code

Specify mapping interface by package name (specify mapping file)

<mappers>
	<package name="com.bjsxt.mapper"/>
</mappers>
Copy the code

---

Mapping configuration file

The mapping configuration file is mainly used to write SQL statements, specify the mapping of result sets, and configure some cache, etc.

      
<! DOCTYPEmapper
PUBLIC "- / / mybatis.org//DTD Mapper / 3.0 / EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.bjsxt.mapper.UserMapper">
    
</mapper>
Copy the code

• namespace

Mybatis can create a unique namespace for each mapping file. As long as the id of the namespace is unique, the SQL statement IDS of different mapping files will not conflict

---

1. ResultMap label

<resultMap id="userMapper" type="com.bjsxt.pojo.User">
    <id property="userid" column="user_id"/>
    <result property="username" column="user_name"/>
</resultMap>
Copy the code

The label that specifies the mapping between the query result set and the object

Type: The full name of a class, or a type alias, which can be understood as a table in the database converted to an object. The properties of this object, even if the field name of the table in the database is CRUD, will fill the corresponding field data into the changed object.

Id: unique identifier. During business processing, this tag specifies that the program gets to execute SQL statements and is unique in a mapping file

2. Id tag

Specifies the value in the primary key that identifies a result map.

Property: The value of this property corresponds to the alias given when operating on SQL statements

3. Select, INSERT, updata, delete tags

<select id="selectUser" parameterType="int" resultType="u">
	select * from users where userid = #{userid}
</select>
Copy the code

ParameterType: specifies the parameterType. This property is optional. Because MyBatis can infer the parameters of a specific incoming statement through a TypeHandler.

ResultType: The full name or alias of the class that is expected to return a result from this statement.

ResultMap: Use the resultMap tag to process the result set mapping.

Mybatis case

Add DTD constraint files

In the absence of networking, if DTD constraints continue to operate and label prompts appear, this can be done by importing a local DTD file and copying the downloaded DTD to a local directory

Idea Operation path: File- Settings- Languages & Frameworks. The URI copies the network address of the DTD. File Selects the local address of the DTD File

Note: MyBatis -3-config.dtd is provided in the MyBatis core JAR package

Add the jar package

Create the entity

public class Users {
    private int userid;
    private String username;
    private String usresex;
    
    public int getUserid(a) {
    	return userid;
    }
    
    public void setUserid(int userid) {
    	this.userid = userid;
    }
    
    public String getUsername(a) {
    	return username;
    }
    
    public void setUsername(String username) {
    	this.username = username;
    }
    
    public String getUsresex(a) {
    	return usresex;
    }
    
    public void setUsresex(String usresex) {
    	this.usresex = usresex; }}Copy the code

Create the Properties file

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

Create a global configuration file

The externally defined properties property is used here

      
<! DOCTYPEconfiguration
PUBLIC "- / / mybatis.org//DTD Config / 3.0 / EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <! Properties file -->
    <properties resource="db.properties"/>
    <! -- Environment configuration -->
    <environments default="development">
        <environment id="development">
            <! -- Configure transaction -->
            <transactionManager type="JDBC"></transactionManager>
            <! -- Configure data source -->
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>
    <! -- Import mapping configuration file -->
    <mappers>
    <! Different entity classes have different mapping configuration files. If there are too many entity analogies, it will be more difficult.
    <mapper resource="com/bjsxt/mapper/UsersMapper.xml"/>
    </mappers>
</configuration>
Copy the code

Create a mapping configuration file

      
<! DOCTYPEmapper
PUBLIC "- / / mybatis.org//DTD Mapper / 3.0 / EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.bjsxt.mapper.UserMapper">
    <! -- Query all users -->
	<select id="selectUsersAll" resultType="com.bjsxt.pojo.Users">
		select * from users
    </select>
</mapper>
Copy the code

Create the UsersDao interface

public interface UsersDao {
	List<Users> selectUsersAll(a)throws IOException;
}
Copy the code

Create the UsersDao interface implementation class

public class UsersDaoImpl implements UsersDao {
    /** * Query all users *@return* /
    @Override
    public List<Users> selectUsersAll(a)throws IOException {
        // Create the SqlSessionFactory object
        InputStream inputStream = Resources.getResourceAsStream("mybatis-cfg.xml");
        SqlSessionFactory sqlSessionFacotry = new SqlSessionFactoryBuilder().build(inputStream);
        // Get the SqlSession object
        SqlSession sqlSession = sqlSessionFacotry.openSession();
        // Perform operations on the database using the API of the SqlSession object
        List<Users> list = sqlSession.selectList("com.bjsxt.mapper.UserMapper.selectUsersAll");
        // Close the SqlSession object
        sqlSession.close();
        returnlist; }}Copy the code

Parameter binding in Mybatis

The syntax structures for binding parameters to SQL statements in the mapping configuration file are #{} and ${}.

#{} and ${}

#{} : parsed as a parameter marker placeholder for a PreparedStatement? . Enables SQL injection to be avoided in this manner

** ∗ : Only for a pure broken String replacement, variable replacement will be performed in the dynamic SQL parsing stage of Mybatis. {}** : Only for a pure broken String substitution, variable substitution will be performed during the dynamic SQL parsing phase of Mybatis. ∗∗ : Only for a pure broken String replacement, variable replacement will be carried out in the dynamic SQL parsing stage of Mybatis. {} has been replaced by variables before precompilation, which can cause SQL injection problems.

Mybatis tools

ThreadLocal is introduced

ThreadLocal provides the ability to store variables within a thread, the difference being that each thread reads the corresponding variables independently of each other. The values of the current thread can be obtained using the get and set methods

Use ThreadLocal to store SQLSessions

If multiple DML operations are part of a transaction, a SqlSession must be guaranteed because both COMMIT () and rollback() are performed by SqlSession. However, multiple different DML operations may occur in different classes of different methods, each of which will fetch a separate SqlSession. For example, when placing an order in the mall, it actually involves multiple DML operations, such as commodity inventory change, order addition, order details addition, payment, log addition, etc., distributed in different classes. How to use the same SqlSession between multiple DML operations can be stored using ThreadLocal. Ensure that all operations in a thread use a SqlSession.

Each request from the user in a Web project starts a new thread, such as clicking “Checkout” to complete shopping cart checkout. Starting main() in a Java project also automatically starts a main thread

public class MybatisUtils {
    private static ThreadLocal<SqlSession> threadLocal = new ThreadLocal<>();
    private static SqlSessionFactory sqlSessionFactory = null;
    static{
        / / create a SqlSessionFactory
        InputStream is = null;
        try{
        	is = Resources.getResourceAsStream("mybatis-cfg.xml");
        }catch (IOException e){
        	e.printStackTrace();
        }
        sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
    }
    
    / / get the SqlSession
    public static SqlSession getSqlSession(a){
    	SqlSession sqlSession = threadLocal.get();
        if(sqlSession == null){
            sqlSession = sqlSessionFactory.openSession();
            threadLocal.set(sqlSession);
        }
    	return sqlSession;
    }
    
    / / close the SqlSession
    public static void closeSqlSession(a){
        SqlSession sqlSession = threadLocal.get();
        if(sqlSession ! =null){
            sqlSession.close();
        	threadLocal.set(null); }}}Copy the code

Mybatis transaction commit mode

The default transaction commit mode in Mybatis is manual commit, which is different from JDBC. The default transaction commit mode in JDBC is auto commit.

• Manually commit transactions (default)

SqlSession sqlSession = sqlSessionFacotry.openSession();
Copy the code

• Auto-commit transaction

SqlSession sqlSession = sqlSessionFacotry.openSession(true);
Copy the code

Java logging framework

Common logging processing frameworks

1. Log4j: Log For Java(Java Log) is an open source Java mainstream logging framework provided by Apache.

2. Log4j2:

   Log4j defines eight log levels (except FOR OFF and ALL). The priority levels are OFF, FATAL, ERROR, WARN, INFO, DEBUG, TRACE, and ALL in descending order.

   In Log4j, it is recommended to use only four log levels: DEBUG, INFO, WARN, and ERROR

   • ALL Indicates the lowest level. It is used to enable ALL logging.
   • Grained designates finer-grained informational events than the DEBUG.Since:1.2.12
   • DEBUG indicates that fine-grained information events are very helpful for debugging applications. They are mainly used to print running information during development.
   • INFO messages highlight the running process of the application at a coarse-grained level. Print information that you are interested in or important to you. This can be used in a production environment to print important information about the application running, but do not overuse it to avoid printing too many logs
   • WARN indicates situations where potential errors occur, some of which are not error messages, but are also intended to give a hint to programmers.
   • ERROR Indicates that an ERROR event does not affect system running. Print errors and exceptions, and use this level if you don’t want to log too much.
   • FATAL indicates that every critical error event will cause the application to exit. That’s a higher level. Major fault

   No, you can stop the program at this level.

   • OFF Indicates the highest level. This parameter is used to disable all logging.

3. Commons

4. Logging,

5. Slf4j,

6. Logback.

7. Jul.

The use of Log4j

1. Log4j configuration file name: Log4j configuration file name: log4j.properties, Log4j configuration file location: SRC root directory of the project

2. Configure the root Logger:

   log4j.rootLogger = [level],appenderName,appenderName2,...
   Copy the code

   Level indicates the log priority. The priorities are DEBUG,INFO,WARN, and ERROR in ascending order. With the level defined here, you can control the switch on and off of the corresponding log level in the application. For example, if the INFO level is defined here, all DEBUG level logs in the application will not be printed. AppenderName specifies where the log information will be exported. Multiple output destinations can be specified simultaneously.

3. The Log4j appenders

   Org, apache log4j. ConsoleAppender (output to the console) org.. Apache log4j. FileAppender (output to a file) Org, apache log4j. DailyRollingFileAppender org. (every day to create a log file). The apache log4j. RollingFileAppender (file size to specified size to create a new file) Org, apache log4j. WriterAppender (the log information to flow format to send to any designated place) org.. Apache log4j. JDBC. JDBCAppender (add log information in the database)

4. An appender that outputs to the console

   Appender. console outputs to the console
   log4j.appender.console=org.apache.log4j.ConsoleAppender
   log4j.appender.console.layout=org.apache.log4j.PatternLayout
   log4j.appender.console.layout.ConversionPattern=<%d> %5p (%F:%L) [%t] (%c)- %m%n
   log4j.appender.console.Target=System.out
   Copy the code

5. An appender for output to a file

   ## appender.logfile outputs to logfile ###
   log4j.appender.logfile=org.apache.log4j.RollingFileAppender
   log4j.appender.logfile.File=SysLog.log
   log4j.appender.logfile.MaxFileSize=500KB
   log4j.appender.logfile.MaxBackupIndex=7
   log4j.appender.logfile.layout=org.apache.log4j.PatternLayout
   log4j.appender.logfile.layout.ConversionPattern=<%d> %p (%F:%L) [%t] %c - %m%n
   Copy the code

6. Appender for output to the database

   log4j.appender.logDB=org.apache.log4j.jdbc.JDBCAppender
   log4j.appender.logDB.layout=org.apache.log4j.PatternLayout
   log4j.appender.logDB.Driver=com.mysql.jdbc.Driver
   log4j.appender.logDB.URL=jdbc:mysql://localhost:3306/bjsxt
   log4j.appender.logDB.User=root
   log4j.appender.logDB.Password=root
   log4j.appender.logDB.Sql=INSERT INTO
   logs(project_name,create_date,level,category,file_name,thread_name,line,all_
   category,message)values('logDemo','%d{yyyy-MM-ddHH:mm:ss}','%p','%c','%F','%t','%L','%l','%m')
   Copy the code

7. The log output level is controlled by the package name

   log4j.logger.org.apache=FATAL
   log4j.logger.org.apache.commons=ERROR
   log4j.logger.org.springframework=ERROR
   log4j.logger.com.bjsxt=ERROR
   Copy the code

Mapper dynamic proxy

specification

1. The interface name must be the same as the name of the mapping configuration file
2. The namespace in the mapping configuration file must be the full name of the interface.
3. The method name in the interface is the same as the id of the label in the mapping configuration file.
4. The return value type in the interface is the same as the type specified in the resultType mapping configuration file.

Multi-parameter processing in Mapper dynamic proxy mode

Sequential parameter transfer method

In the mapping file, arguments in the SQL statement need to use arg0, arg1… Or param1, param2… Represents the order of arguments. This method is not recommended for development because it is not readable and requires the correct order of parameters

List selectUsersOrderParam(String username,String usersex);
select * from users where username = #{arg0} and usersex= #{arg1}
select * from users where username = #{param1} and usersex=#{param2}

@param annotate the pass-through method

Parameter names are defined in the @param annotation in the parameter list of the interface method, and parameter positions are specified in the SQL statement using the parameter names defined in the annotation. This method is intuitive when there are not many parameters and is recommended.

<! Select * from @param; select * from @param;
<select id="selectUsersAnnParam" resultType="users">
	select * from users where username = #{name} and usersex= #{sex}
</select>
Copy the code

List<Users> selectUsersAnnParam(@Param("name") String
username,@Param("sex") String usersex);
Copy the code

POJO parameter method

In Mapper dynamic proxy, POJO can also be used as the carrier of passing parameters. When binding parameters in SQL statements, the attribute name of POJO can be used as the parameter name. This method is recommended.

<! Select * from POJO where user name and gender = 'user';
<select id="selectUsersPOJOParam" resultType="users">
	select * from users where username = #{username} and usersex=#{usersex}
</select>
Copy the code

List<Users> selectUsersPOJOParam(Users users);
Copy the code

The Map parameter method

Mapper dynamic proxy can also use a Map as the carrier for transferring parameters. When binding parameters in SQL statements, use the Map Key as the parameter name. This method is suitable for passing multiple parameters, which can be used if no POJO can match the parameters. Recommended.

When MyBatis transmits the map parameter, if there is no corresponding key value in the parameter, the default value is NULL when the SQL statement is executed.

<! Select * from user by name and gender;
<select id="selectUsersMapParam" resultType="users">
	select * from users where username = #{keyname} and usersex=#{keysex}
</select>
Copy the code

List<Users> selectUsersMapParam(Map<String,String> map);
Copy the code

Using symbolic entities

We can use the entity of the symbol

<select id="selectUsers" resultType="users">
	select * from users where userid &gt; #{userid}
</select>
Copy the code

Dynamic SQL

---

What is Mybatis dynamic SQL? How does it work? What dynamic SQL is there?

---

Dynamic SQL functionality is provided in MyBatis. Concatenate SQL statements using Java code instead of using tag concatenate SQL statements in XML mapping files.

Dynamic SQL in MyBatis is written in Mapper. XML. Its syntax is similar to JSTL, but it is implemented based on powerful OGNL expressions.

OGNL (Object Graph Navigation Language) is an open source Expression Language used in Java. It is integrated in Struts2 and other frameworks. Function is to access data, it has type conversion, access object methods, operation collection objects and other functions.

1. If the label

If label single branch statement

<! -- Query the user based on the given condition -->
<select id="selectUsersByProperty" resultType="users">
    select * from users where 1=1
    <if test="userid ! = 0">
    	and userid = #{userid}
    </if>
    <if test="username ! = null and username ! = "">
    	and username = #{username}
    </if>
    <if test="usersex ! = null and usersex ! = "">
    	and usersex = #{usersex}
    </if>
</select>
Copy the code

2. Choose, WHEN, or otherwise tags

Select one of several criteria to use.

<! -- More than one condition -->
<select id="selectUsersByChoose" resultType="users">
    select * from users where 1=1
    <choose>
        <when test="username ! = null and username ! = "">
        	and username = #{username}
        </when>
        <when test="usersex ! = null and usersex ! = "">
        	and usersex = #{usersex}
        </when>
        <otherwise>
	        and userid = 1
        </otherwise>
    </choose>
</select>
Copy the code

3. Where the label

With the WHERE tag, there is no need to provide a condition where 1=1. If the condition is not empty, the where keyword is automatically added and the AND or OR before the first condition is automatically removed

<! SQL > select * from 'where';
<select id="selectUsersByPropertyWhere" resultType="users">
    select * from users
    <where>
        <if test="userid ! = 0">
        	and userid = #{userid}
        </if>
        <if test="username ! = null and username ! = "">
        	and username = #{username}
        </if>
        <if test="usersex ! = null and usersex ! = "">
       	 	and usersex = #{usersex}
        </if>
    </where>
</select>
Copy the code

4. The bind tag

The bind tag allows you to create a variable outside of the OGNL expression and bind it to the current SQL statement. Generally used for fuzzy queries, using bind to bind wildcards and query values.

<! Fuzzy query by user name -->
<select id="selectUsersByLikeName" resultType="users">
    <bind name="likeName" value="'%'+name+'%'"/>
    select * from users where username like #{likeName}
</select>
Copy the code

5. The set tag

The set tag is used in the UPDATE statement. With the if tag, you can update only fields that have specific values. The set tag automatically adds the set keyword, automatically removing the extra comma from the last if statement.

<! -- Select Update -->
<update id="usersUpdate">
    update users
    <set>
        <if test="username ! = null and username ! = "">
        	username = #{username},
        </if>
        <if test="usersex ! = null and usersex ! = "">
        	usersex = #{usersex},
        </if>
    </set>
    where userid = #{userid}
</update>
Copy the code

6. The foreach tag

The foreach tag is very powerful. We can pass any iterable such as a List, Set, Map, or array object as a collection parameter to the foreach tag for traversal. It also allows us to specify beginning and ending strings and separators between iterations of collection items.

Iterate over List and Set

<! Select * from user where ID = 1;
<select id="selectUsersByIdUseCollection" resultType="users">
    select * from users where userid in
    <foreach collection="collection" item="userid" open="(" separator="," close=")">
    	#{userid}
    </foreach>
</select>
Copy the code

Iterative array

<! Select * from user where ID = 1 and ID = 2;
<select id="selectUsersByIdUseArray" resultType="users">
    select * from users where userid in
    <foreach collection="array" item="userid" open="(" separator="," close=")">
    	#{userid}
    </foreach>
</select>
Copy the code

Mybatis cache

When an SQL query statement is executed, the query result is stored in memory or some cache medium (which cache medium Mybatis has). When the same SQL query is encountered next time, the SQL will not be executed, but the result will be directly obtained from the cache

MyBatis cache mode is divided into level 1 cache and level 2 cache, and can also be configured about the cache Settings.

Level 1 caches the results in the SqlSession object and level 2 caches the results in the SqlSessionFactory object. By default, MyBatis enables level 1 caching, not level 2 caching. When the amount of data is large, some third-party caching technologies can be used to help save the secondary cache data of Mybatis.

Use of level 1 cache

Level 1 cache is also called local cache, MyBatis level 1 cache is cached at the session level (SqlSession). In SqlSession there is a (memory area) data structure (HashMap) for storing cached data. The cache data area (HashMap) between different SQLsessions does not affect each other; MyBatis level 1 cache is enabled by default and does not require any configuration.

The life cycle of level 1 cache

• When MyBatis starts a database session, a new SqlSession object will be created. The SqlSession object will have a new Executor. Object. A new PerpetualCache is held in an Executor object. Object; When the session ends, the SqlSession object and its internal Executor and PerpetualCache objects are also released.

• If the SqlSession calls the close() method, the Tier 1 cache PerpetualCache object is freed and the tier 1 cache is not available.

• If a SqlSession calls clearCache(), the data in the PerpetualCache object is cleared, but the object is still available.

• If a commit operation (update(), delete(), insert()) is performed in the middle of the sqlSession, the data in the sqlSession is cleared. This is done to update the data in the cache (the PerpetualCache object) and avoid dirty reads

---

How can I tell if two queries are exactly the same?

Mybatis considers two queries to be identical if the following conditions are exactly the same.

1. The incoming statementId (what is statementId? .

2. The range of results in the result set required when querying.

3. The result of this query is the STRING of Sql statements that will eventually be passed to a PreparedStatement.

4. The parameter value to pass

Use of level 2 cache

MyBatis level 2 cache is the Application level cache, it can improve the efficiency of database query, to improve the performance of the Application. The secondary cache is the cache on the SqlSessionFactory and can be shared between different SQLsessions created by a SqlSessionFactory. It is disabled by default. SqlSession puts data into level 2 cache at commit() or close().

SqlSession shares the level-2 cache

How to configure level 2 cache

When implementing level 2 cache, MyBatis requires that the POJO of cache must be Serializable, that is, Serializable interface should be implemented. In order to take out the cache data, deserialization operation should be performed, because level 2 cache data storage media are various. It doesn’t have to be in memory, it might be in hard disk. This is enabled in the mapping configuration file.

Level 2 Cache features

1. All select statements in the mapping statement file will be cached.
2. All INSERT, UPDATE, and DELETE statements in the mapping statement file flush the cache.
3. Level 2 cache is based on namespace (global namespace). Operations in different namespaces do not affect each other
4. You can use useCache if you want individual select elements to be cached without adding tags

Property, set to false. 5. The default Least Recently Used (LRU) algorithm is Used to retrieve the cache. 6. According to the schedule, such as No Flush Interval (CNFI has No Flush Interval), the cache is not flushed in any chronological order.

7. Will the cache store 1024 references to list collections or objects (whatever the query method returns)
8. The cache is treated as a read/write cache, meaning that object retrieval is not shared and can be safely modified by the caller without interfering with potential changes made by other callers or threads.

---

Use of Mybatis annotations

If annotated development is used in Mybatis, annotations need to be added to abstract methods in the Mapper interface. In this way, the annotation can be given the SQL statement to execute, so that the mapping configuration file is not required. MyBatis supports pure annotation mode, supports pure mapping configuration file mode, also supports annotation and mapping configuration file mixed form. In mybatis-cfg. XML, you can import mappings by loading specified interface classes when only interfaces do not have mapping profiles. You can also use a specified loaded package.

The instance

1. Complete the query with annotations

@Select("select * from users")
List<Users> selectUsersAll(a);
Copy the code

Parameter passing during annotated development

Sequential parameter transfer method

@Select("select * from users where username = #{param1} and usersex= #{param2}")
List<Users> selectUsersByNameAndSexOrder(String username,String usersex);
@Select("select * from users where username = #{name} and usersex = #{sex}")
List<Users> selectUsersByNameAndSexOrder2(@Param("name") String username,@Param("sex") String usersex);
Copy the code

POJO parameter method

The parameter names in #{} should be the same as the attribute names of the entity class

@Select("select * from users where username = #{username} and usersex = #{usersex}")
List<Users> selectUsersByNameAndSexPOJO(Users users);
Copy the code

The Map parameter method

If the key does not exist, null is returned

@Select("select * from users where username = #{keyname} and usersex = #{keysex}")
List<Users> selectUsersByNameAndSexMap(Map<String,String> map);
Copy the code

The use of OpenSessionInView

What is an Open Session In View

Open Session In View mode:

Open Session In View binds a database Session object to the current thread of request and keeps the database Session object Open during the request so that the database Session object can be used for the entire duration of the request. The current database session object is closed until the response is generated