Hello, today to share Mybatis summary, quickly take out a small book to write it down!
Mybatis
MyBatis, is the most popular durable layer frame in China
ORM is used to solve the problem of entity class and database table mapping. JDBC has been encapsulated, shielding the JDBCAPI low-level access details, avoid us and JDBCAPI cross, can complete the data persistence operation.
O – Object Java objects
R-relation is a table in a database
M – mapping mapping
Start fast
Mybatis official help document: mybatis.org/mybatis-3/z…
1. Create Maven project
2. Import Maven dependencies
Here, we import mybatis dependencies, mysql classes, and unit test dependencies
3. Configure the Maven plug-in
Here, we configure Maven’s compiled plug-in. We specify that both the source and compiled files are Java 1.8
<plugins> <! Plugins </groupId> <artifactId>maven-compiler-plugin</artifactId> <version>3.8.1</version> <configuration> <source>1.8</source> <target>1.8</target> </configuration> </plugins> </build>Copy the code4. Create a database and import the table
CREATE TABLE team (
TeamId int NOT NULL AUTO_INCREMENT COMMENT ‘teamId ‘,
TeamName varchar(50) DEFAULT NULL COMMENT ‘teamName ‘,
Location varchar(50) DEFAULT NULL COMMENT ‘team location ‘,
CreateTime date DEFAULT NULL COMMENT ‘createTime ‘,
PRIMARY KEY (teamId)
) ENGINE=InnoDB AUTO_INCREMENT=1003 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
5. Write the Mybatis configuration file
Examples of configuration files can be found directly on the official website
During future development, you are advised to create a document to store the rules for writing configuration files for convenient development
6. Write entity classes
@AllArgsConstructor
@NoArgsConstructor
@Data
public class Team {
private Integer teamId;
private String teamName;
private String location;
private Date createTime;
Copy the code}
7, write mapper interface
public interface TeamMapper {
@return */ List< team > getAll();Copy the code}
8. Write mapper implementation
The implementation is written in.xml files
Id: non-method on the interface
ResultTyoe: The return value of the interface method (the full class name must be written before configuration)
Note: xxxmapper. XML must have the same name as the corresponding mapper interface
<select id="getAll" resultType="top.faroz.pojo.Team">
select * from team
</select>
Copy the codeYou can also put the.xml implementation under Resources, but be sure to use the same name as the package:
9. Add mapper mapping to Mybatis configuration file
After writing the.xml implementation, be sure to add a Mapper map to the configuration file
<mappers>
<package name="top.faroz.dao"/>
</mappers>
Copy the code
10. Write Mybatis utility class
The utility class is used to create a singleton factory for sqlSession
And add a method to get the sqlSession connection
11, test,
@Test
public void getAllTest() {
SqlSession sqlSession = MybatisUtil.getSqlSession();
TeamMapper mapper = sqlSession.getMapper(TeamMapper.class);
List all = mapper.getAll();
for (Team team : all) {
System.out.println(team);
Copy the code}
sqlSession.close();
}
The test results are as follows:
2. Log adding
1. Add Maven dependencies
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
Copy the code2. Add log4j configuration
We can adjust the level of log output, including INFO, WARNING, and ERROR in addition to DEBUG
Global logging configuration info warning error
log4j.rootLogger=DEBUG,stdout
Console output…
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] – %m%n
Now it’s output on the console, and in the future, maybe our project will be deployed on the client’s server, we can fix the log information to some external file
3. Configure LOG in Mybatis
<settings>
<setting name="logImpl" value="LOG4J"/>
</settings>
Copy the code
4. Perform tests
The console displays detailed log information
3. Object analysis of Mybatis
1, the Resources,
2, the SqlSessionFactoryBuilder is
The person who created the SqlSessionFactory will only be used once in the entire project to create the SqlSessionFactory. After using the throw
3, SqlSessionFactory
Create a singleton factory for sqlSession. There should be only one singleton of SqlSessionFactory in the entire project
4, SqlSession
A SqlSession corresponds to a database session, which begins with the creation of the SqlSession object and ends with the closing of the SqlSession object.
The SqlSession interface object is thread unsafe, so it needs to be closed by calling its close() method immediately before each database session ends. The session is needed again and created again.
4. Improved MybatisUtil
In the quick Start section, we have encapsulated the process of obtaining Mybatis sqlSession object into a utility class
However, sqlSession is not stored as a member variable in MybatisUtil, so we have to manually call our external sqlSession to execute the close. We can turn sqlSession into a member variable and then, in MybatisUtil, write a close method.
If sqlSession is used as a static member variable, it is not thread safe. If sqlSession is used as a static member variable, it is not thread safe. Putting it in MybatisUtil is bound to cause thread-related problems.
To solve this problem, we need to introduce ThreadLocal
1, ThreadLocal
2. Use ThreadLocal to overwrite
3, summary
I personally don’t recommend using sqlSession as a static member variable, and the official documentation doesn’t recommend it either. Again, the main purpose of this section is to introduce ThreadLocal
Input mapping
So in our previous tests, the input was a single value, so what if we had multiple values in our input?
1. Use subscripts (not recommended)
If you have more than one parameter, you can use:
# # {arg0}, {arg1}, # {arg2}… # or # {param0}, {param2}, # {param3}… To obtain different parameters
However, this method is not recommended
2. Use annotations
We can annotate the @param annotation in front of the parameters of the corresponding Mapper interface, and use the parameter names defined in the annotation directly in the.xml implementation class
Interface:
Team selectByNameAndLocation(@Param(“name”) String name,@Param(“location”) String location);
Implementation:
Testing:
@Test
public void selectByNameAndLocationTest() {
SqlSession sqlSession = MybatisUtil.getSqlSession();
TeamMapper mapper = sqlSession.getMapper(TeamMapper.class);
Team Team = mapper. SelectByNameAndLocation (” bucks “, “Milwaukee, Wisconsin,”);
System.out.println(team);
sqlSession.close();
}
3. Use Map
The parameters passed in can be those obtained from the map. XML file and must be consistent with the key value in the map
Interface:
Team selectByName(Map map);
Implementation:
select * from team;
Copy the code3) Output map type
When we only need to query several columns of data in the table, we can use the SQL query result as the key and value of the Map. Generally, Map<Object,Object> is used.
Map is returned by the interface. The query result of the SQL statement can contain only one record at most. Greater than one record throws a TooManyResultsException. If there are multiple lines, use List<Map<Object,Object>>.
Map<Object,Object> queryTwoColumn(int teamId);
List<Map<Object,Object>> queryTwoColumnList();
2, resultMap
ResultMap allows you to customize the mapping between SQL results and Java object attributes. More flexibility in assigning column values to specified attributes.
Generally, id is used for primary key columns and result is used for other columns
Column: indicates the name of the column in the database table, which is case-insensitive. Property: indicates the name of the corresponding property in the entity class, which is case-insensitive. JavaType: indicates the type of the corresponding property in the entity class, which can be omitted. Mybatis will infer that jdbcType=” type of column in database”
<! -- Primary key column, use id--> < ID column="teamId" property="teamId" javaType="java.lang.Integer"></ ID ><! -- The remaining columns, Use result- >< result column="teamName" property="teamName" javaType=" java.lang.string "></result> <result column="location" property="location" javaType="java.lang.String"></result> <result column="createTime" property="createTime" javaType="java.util.Date"></result>Copy the codeWhen used, the return value is changed from the original resultType to our resultMap:
Using resultMap for attribute mapping can also solve the problem that attribute names are inconsistent with database table column names
3, database table column and entity class attribute inconsistent processing
1) Use resultMap to solve the problem
I said that, but I’m not going to show it here
2) Create an alias in SQL
If the name of the query is underlined, and the name of the poJO is humped, we can alias the result as follows:
select user_id as userId,user_name as userName,user_age as userAge from users where user_id=#{id};
${$} ${$} ${$
This question is often examined by interview questions
#{} : represents a placeholder that tells Mybatis to use the actual parameter value instead. And execute SQL statements using the PrepareStatement object, #{… } instead of ‘? ‘in SQL statements . This is the preferred method in Mybatis, safe and fast.
In this way, YOU can prevent SQL injection.
: indicates that the string is replaced as is, notifying Mybatis to use {} : indicates that the string is replaced as is, notifying Mybatis to use: indicates that the string is replaced as is, notifying Mybatis to replace the location with the contained “string”. Connect SQL statements to the contents of ${} using Statement or PreparedStatement. Generally used for replacing table names, column names, sorting different columns and so on.
In this way, we can modify the columns of the SQL statement, for example, we have the following requirement:
To query user information based on name, age, address, etc., you need to write three SQL statements
select * from xxx where name = #{name}
select * from xxx where age = #{age}
select * from xxx where address = #{address}
As you can see, the only difference is after where. Here, we can replace where with ${}, so we don’t need to write as much SQL
select * from xxx where ${column} = #{columnValue}
${} is not a string concatenation method, so do not use the parameter, otherwise it may cause SQL injection problems. But why can it be used elsewhere in SQL? This is because if you write an SQL injected statement in a non-parameter place, it will cause an SQL statement error and therefore an error.
Mybatis global configuration file
1. Configuration overview
Configuration, a few configuration does not matter, but be sure to follow the following configuration
The configuration (configuration)
Properties: Load external configuration files, For example, load database connection information Settings-- global configuration parameters: for example log configuration typeAliases-- type alias typeHandlers---- type handler objectFactory----- objectFactory Plugins------ Plugins such as paging Plugins Environments---- Environment collection properties objects transactionManager dataSource Mappers-- mapper: Register mapping filesCopy the code2. Properties
Mybatis can read external configuration files via property configuration, such as loading database connection information
1) Create a configuration file
In the Resources folder, create the jdbc.properties configuration file
jdbc.driver=com.mysql.cj.jdbc.Driver
The JDBC url = JDBC: mysql: / / 127.0.0.1:3306 / mybatis_study? useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT
jdbc.username=admin
jdbc.password=123
2) Introduce configuration file information in Mybatis configuration
3) Read the configuration file
3. Settings
Important tuning Settings in MyBatis that change the runtime behavior of MyBatis. For example, our configured logging is one of the applications. For other information, refer to the configuration document
Mybatis.org/mybatis-3/z…
4, typeAliases
A type alias sets 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.
1) Supported aliases
A type alias sets 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. Such as:
When configured this way, blogs can be used anywhere doma.blog.blog is used.
(Below 👇 this method should be more convenient)
You can also specify a package name, MyBatis will search for the desired Java Bean under the package name, for example:
<package name="top.faroz.pojo"/>
Copy the code5. Mappers
Method 2: Use class file binding
Note:
The interface has the same name as the configuration file
The interface and its Mapper configuration file must be in the same package
Method 3: Inject binding using scan packages
6.
There are three kinds of built-in data types (type = “[UNPOOLED | POOLED | JNDI]”) :
UNPOOLED – The implementation of this data source opens and closes connections on each request. It’s a bit slow, but for simple applications that don’t require high availability of database connections
POOLED – This data source implementation leverages the concept of “pooling” to organize JDBC connection objects, avoiding the initialization and authentication time required to create new connection instances. (Database connection pool)
JNDI – This data source implementation is intended for use in a container such as an EJB or application server, which can centrally or externally configure the data source and then place a data source reference to the JNDI context.
POOLED is used by default
7. TransactionManager
1) Manual transaction commit is required by default
Mybatis framework is the encapsulation of JDBC, so the transaction control mode of Mybatis framework, The commit mode itself is set using the COMMIT () and setAutoCommit() methods of the ROLLBACK ().connection objects of JDBC.
Automatic commit and manual commit, this tag is used to specify the transaction manager used by MyBatis. MyBatis supports two transaction manager types :JDBC and MANAGED.
JDBC: Using the TRANSACTION management mechanism of JDBC, the Connection object is committed by the commit() method and rolled back by the rollback() method. By default, MyBatis has turned off the automatic commit function and changed it to manual commit. As can be seen from the log, we all need to commit or roll back transactions by ourselves in the application.
2) Automatically commit transactions
Relational mapping
1. One-to-one relationship
There’s a requirement that there’s a bunch of students, a bunch of students that correspond to a particular teacher, and now we’re going to query the properties of the students and the corresponding teachers
The SQL statement:
public class Student {
private int id;
private String name;
private Teacher teacher;
Copy the code}
Teacher:
public class Teacher {
private int id;
private String name;
Copy the code}
1), according to query nesting processing
Interface:
List getAll();
XML realize
This is equivalent to executing the query again in a resultMap
This approach is similar to a union table query
But in terms of performance, the second one performs better (after all, you only need to query the database once)
2. One-to-many relationship
One-to-many is almost the same as many-to-one
For example, one teacher has more than one student
Environment setup, same as before
Writing entity classes
public class Student {
private int id;
private String name;
private int tid;
}
Interface:
List getAll();
XML realize
OfType refers to the generic type of the element in the collection
@Test
public void getAllTest() { SqlSession sqlSession = MybatisUtil.getSqlSession(); TeacherMapper = sqlSession. GetMapper (teachermapper.class); / / TeacherMapper = sqlsession.getMapper (teachermapper.class); List<Teacher> all = mapper.getAll(); for (Teacher teacher : all) { System.out.println(teacher); } sqlSession.close(); }Copy the code
Interface:
List getAll();
XML implementation:
select * from teacher <where> <if test="name! =null">Copy the codeConcatenate multiple strings using the concat function
and name like concat(concat('%',#{name}),'%')
</if>
</where>
Copy the codeThe use of the set tag in update
Batch add
INSERT INTO team (teamName,location) VALUES <! --collection: a collection to walk through; Separator: Delimits the result of the walk with, Separator ="list" item="t" separator=","> (#{t.name},#{t.ocation}) </foreach>Copy the codeBatch delete
delete from team where teamId in <! --collection: a collection to walk through; Separator: Separates the result of the traversal. Separator: Separates the result of the traversalCopy the codeOpen =”(” close=”)”: wrap the traversal result with open close –>
<foreach collection="list" item="teamId" separator="," open="(" close=")">
#{teamId}
</foreach>
Copy the codeSQL fragment
Extract SQL fragment:
title = #{title}
Copy the code and author = #{author}
Copy the codeReference SQL fragment:
Note:
It is better to define SQL fragments based on a single table to improve the reusability of fragments
Do not include WHERE in SQL fragments
11. Paging plug-ins
Paging plug-in, we use pageHelper
1. Start fast
Maven dependency
< the groupId > com. Making. Pagehelper < / groupId > < artifactId > pagehelper < / artifactId > < version > 5.1.10 < / version >Copy the code2. Add plugin configuration to Mybatis global configuration file
<! -- pageHelper plugin --> <! Note that it is a PageInterceptor interceptor. Prior to version 5.0, it was a PageHelper. After 5.0 to replace PageInterceptor - > < plugin interceptor = "com. Making. Pagehelper. PageInterceptor" > <! --reasonable: paging rationalization parameter (default value is false). Query based on the parameter directly. When this parameter is set to true, the first page is queried when pageNum<=0, and the last page is queried when pageNum> Pages exceeds the total number. Dialects can be omitted and are automatically inferred from the parameter URL of the connection data --> <! --<property name="reasonable" value="true"/>--> </plugin> </plugins>Copy the code3. Use plug-ins
2, PageHelper introduction
PageHelper intercepts query statements and adds paging statements
3. PageInfo Introduction
Can be used to print information about pages, such as total number of pages, current page, total page, etc
Xii. Cache
Through the use of cache, you can query the information in the first time, into the cache, so that in the second query, the cache will be removed, so as to reduce database pressure, improve query efficiency, solve the problem of high concurrency.
MyBatis also has level 1 cache and level 2 cache, and has reserved an interface to integrate third party cache.
1. Level 1 cache
Automatically enable sqlSession-level cache
The sqlSession object is constructed when the database is manipulated, and there is a (memory area) data structure (HashMap) in the object to store cached data. The cache data area (HashMap) between different SQLsessions does not affect each other.
The scope of level 1 cache is the same SqlSession. When the same SQL statement is executed twice in the same SqlSession, the data queried in the database will be written to the cache (memory) after the first execution, and the data will be obtained from the cache and will not be queried from the database for the second time, thus improving the query efficiency.
When a sqlSession ends, the level 1 cache in that sqlSession ceases to exist. Mybatis enables level 1 cache by default. The local cache stored in memory cannot be turned off. You can call clearCache() to clear the local cache, or change the scope of the cache.
1) Introduction to level 1 cache
When the user sends the second query team=1001, the user will first search the cache to see if there is any object team=1001, because the data has been stored in the cache after the first successful query, so the user can directly obtain the data from the cache, which means that there is no need to send the query statement to the database.
If the SqlSession executes commit(adding, deleting, or modifying an operation), the cache area corresponding to the SqlSession is cleared to avoid dirty reads.
** Prerequisite :**SqlSession is not closed.
2) The way to clear the cache
1s, session.clearcache ();
Execute update;
3, the session. The close ();
FlushCache =”true”;
5, the rollback;
6, commit.
2. Level 2 cache
Mapper level cache, which can cross sqlSession
1) Introduction to level 2 cache
Level 2 cache is shared by multiple SQLSessions and is scoped to the same namespace as mapper.
Different SQLSessions execute the SQL statement of the same namespace twice with the same parameters, that is, the same SQL statement is executed at last. After the first execution, the data queried in the database is written to the cache (memory). After the second execution, the data will be retrieved from the cache and will not be queried from the database, improving the query efficiency.
Level 2 caching is not enabled in Mybatis by default. You need to configure level 2 caching in setting. If there is data in the cache, it does not need to fetch it from the database, greatly improving system performance.
As with level 1 caching, once the add, delete, and commit is made, the cached data is cleared to avoid dirty data reads
Its schematic diagram is as follows:
Why do I need to disable level 2 caching?
In some cases, some data is modified very frequently. Once the second level cache is enabled, the operation of clearing the cache is also very frequent, which increases the pressure on the database. We can disable the second level cache for these SQL queries:
Set useCache=false to set useCache=false to set useCache=false to set useCache=false to set useCache=false to set useCache=false to set useCache=false to set useCache=false to disable level 2 caching for the current Select statement. The default value of useCache is true. For some very important data do not put in the level 2 cache.
4) Cache property configuration
<property name="eviction" value="LRU"/><! LRU--> <property name="flushInterval" value="60000"/><! <property name="size" value="1024"/><! <property name="readOnly" value="true"/><! - read only - >Copy the codeThe source code is as follows:
Namespaces are really just XML implementations
// Reference the cache in my namespace.
Reverse generator
Using the reverse generator, you can automatically generate the code for the persistence layer from the database tables
1, configuration,
slightly
2, use,
Here are some of the points to note
1) Dynamic update/insert
The selective keyword indicates dynamic change
// Dynamic insert
mapper.insertSelective(User user);
// Dynamic update
mapper.updateByPrimaryKeySelective(User user);
For insert, do not write insert SQL where the property of the insert object is empty
In the case of update, the metadata area is not overwritten with the empty part of the new object
2), multi-condition query
Multi-condition query, need to use XxxExample
// create an Example of the query object
EbookExample ebookExample = new EbookExample();
// create a container for query conditions
EbookExample.Criteria criteria = ebookExample.createCriteria();
//3, in the container, prevent multiple query conditions
criteria.andNameLike(“spring”); // fuzzy query
criteria.andNameEqualTo(“spr”); / / equal to
/ /… There are many more, with an equal number of query criteria to choose from for each attribute
// select * from ‘Example’
mapper.selectByExample(ebookExample);
Well, that’s all for today’s article, hoping to help those of you who are confused in front of the screen