1、 brief introduction

1.1 What is? Mybatis

  • MyBatis Is an excellent persistence layer framework ;
  • It supports customization SQL、 Stored procedures and high-level mappings .MyBatis It dispenses with almost everything JDBC Code and the work of setting parameters and getting result sets .MyBatis It can be done by simple XML Or annotations to configure and map primitive types 、 Interface and Java POJO(Plain Old Java Objects, Ordinary old-fashioned Java object ) For records in the database .

1.2 Persistence

Data persistence

  • Persistence is the process of transforming program data into persistent state and transient state
  • Memory : If you cut off the power, you will lose
  • database (Jdbc),io File persistence .

Why persistence ?

  • There are some objects , Don't let him throw away
  • Memory is too expensive

1.3 Persistence layer

Dao layer 、Service layer 、Controller layer

  • The code block that does the persistence work
  • The boundary of the layers is very clear

1.4 Why MyBatis

  • Help programmers store data in a database

  • convenient

  • Conventional JDBC The code is too complicated , simplify , frame , automation

  • no need MyBatis It's fine too , There is no difference in technology

  • advantage :

    • Easy to learn
    • flexible
    • sql Separation from code , Improved maintainability .
    • Provide mapping label , Support object and database orm Field relation mapping
    • Provide object relation mapping labels , Support object relationship building and maintenance
    • Provide xml label , Support writing dynamics sql

2、 first Mybatis Program

Ideas : Set up the environment --> Import MyBatis --> Write code --> test

2.1 Set up the environment

New projects

  1. Create a normal maven project

  2. Delete src Catalog ( This project can be regarded as the parent project , Then create a subproject )

  3. Import maven rely on

    <!-- Import dependence --><dependencies><!-- --><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>8.0.21</version></dependency><!-- --><dependency><groupId>org.mybatis</groupId><artifactId>mybatis</artifactId><version>3.5.5</version></dependency><!-- --><dependency><groupId>junit</groupId><artifactId>junit</artifactId><version>4.12</version></dependency></dependencies>
  4. Create a Module

2.2 Create a module

  • To write mybatis Core profile for

    <?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE configuration
            PUBLIC "-// Config 3.0//EN"
            ""><configuration><environments default="development"><environment id="development"><transactionManager type="JDBC"/><dataSource type="POOLED"><property name="driver" value="com.mysql.cj.jdbc.Driver"/><property name="url"  value="jdbc:mysql://localhost:3306/mybatis?useSSL=true&amp;useUnicode=true&amp;characterEncoding=UTF-8"/><property name="username" value="root"/><property name="password" value="root"/></dataSource></environment></environments><mappers><mapper resource="com/zhy/dao/UserMapper.xml"/></mappers></configuration>
  • To write mybatis Tool class

    package com.zhy.utils;import;import org.apache.ibatis.session.SqlSession;import org.apache.ibatis.session.SqlSessionFactory;import org.apache.ibatis.session.SqlSessionFactoryBuilder;import;import;//SqlSessionFactory --> sqlSessionpublic class MybatisUtils {private static SqlSessionFactory sqlSessionFactory;static {try {// obtain SqlSessionFactory object String resource = "mybatis-config.xml";InputStream inputStream = Resources.getResourceAsStream(resource);sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);} catch (IOException e) {e.printStackTrace();}}// Given the  SqlSessionFactory, seeing the name of a thing one thinks of its function , We can get  SqlSession  Example //SqlSession  Provides execution in the database  SQL  All methods required for the command public static SqlSession getSqlSession() {return sqlSessionFactory.openSession();}}

2.3 Write code

  • Entity class

  • Mapper Interface

    package com.zhy.dao;import com.zhy.pojo.User;import java.util.List;import java.util.Map;public interface UserMapper {List<User> getUserLike(String value);// Query all users List<User> getUserList();// according to ID Query the user User getUserById(int id);//insert A user int addUser(Map<String,Object> map);// Modify a user int updateUser(User user);// Delete a user int deleteUserById(int id);}
  • Interface implementation class ( From the original UserDaoImpl Turn into a Mapper.xml The configuration file )

    <?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE mapper
            PUBLIC "-// Mapper 3.0//EN"
            ""><mapper namespace="com.zhy.dao.UserMapper"><select id="getUserLike" resultType="com.zhy.pojo.User">select * from mybatis.user where name like "%"#{value}"%"</select><select id="getUserList" resultType="com.zhy.pojo.User">select * from mybatis.user</select><select id="getUserById" parameterType="int" resultType="com.zhy.pojo.User">select * from mybatis.user where id = #{id}</select><insert id="addUser" parameterType="com.zhy.pojo.User">insert into mybatis.user (id,name,pwd) values (#{id},#{name},#{pwd})</insert><update id="updateUser" parameterType="map">update mybatis.user set name = #{name},pwd = #{pwd} where id = #{id};</update><delete id="deleteUserById" parameterType="int">delete from mybatis.user where id = #{id}</delete></mapper>
  • test

    Be careful :org.apache.ibatis.binding.BindingException: Type interface

    com.zhy.dao.UserMapper is not known to the MapperRegistry.

    MapperRegistry What is it? ?

    Register in the core profile mappers

  • junit test

    package com.zhy.dao;import com.zhy.pojo.User;import com.zhy.utils.MybatisUtils;import org.apache.ibatis.session.SqlSession;import org.junit.Test;import java.util.HashMap;import java.util.List;import java.util.Map;public class UserDaoTest {@Testpublic void getUserLike() {SqlSession sqlSession = MybatisUtils.getSqlSession();UserMapper mapper = sqlSession.getMapper(UserMapper.class);List<User> userLike = mapper.getUserLike(" Li ");for (User user : userLike) {System.out.println(user);}sqlSession.close();}@Testpublic void getUserList() {// get SqlSession object SqlSession sqlSession = MybatisUtils.getSqlSession();// Mode one :getMapperUserMapper mapper = sqlSession.getMapper(UserMapper.class);List<User> userList = mapper.getUserList();// Mode two : Not recommended //List<User> userList = sqlSession.selectList("com.zhy.dao.UserDao.getUserList");for (User user : userList) {System.out.println(user);}// close SqlSessionsqlSession.close();}@Testpublic void getUserById() {SqlSession sqlSession = MybatisUtils.getSqlSession();UserMapper mapper = sqlSession.getMapper(UserMapper.class);User userById = mapper.getUserById(1);System.out.println(userById);sqlSession.close();}@Testpublic void addUser() {SqlSession sqlSession = MybatisUtils.getSqlSession();UserMapper mapper = sqlSession.getMapper(UserMapper.class);Map<String, Object> stringObjectMap = new HashMap<String, Object>();stringObjectMap.put("id", 6);stringObjectMap.put("name", "zhy");stringObjectMap.put("pwd", "123456");int result = mapper.addUser(stringObjectMap);System.out.println(result);List<User> userList = mapper.getUserList();for (User user : userList) {System.out.println(user);}// Commit transaction sqlSession.commit();sqlSession.close();}@Testpublic void updateUser() {SqlSession sqlSession = MybatisUtils.getSqlSession();UserMapper mapper = sqlSession.getMapper(UserMapper.class);int result = mapper.updateUser(new User(4, "Hello", "132456"));System.out.println(result);sqlSession.commit();sqlSession.close();}@Testpublic void deleteUserById() {SqlSession sqlSession = MybatisUtils.getSqlSession();UserMapper mapper = sqlSession.getMapper(UserMapper.class);int result = mapper.deleteUserById(4);System.out.println(result);sqlSession.commit();sqlSession.close();}}


1. namespace

namespace The package name in and Dao/Mapper The package name of the interface is the same

2. select

choice , Query statement ;

  • id: That's the corresponding namespace Method name in ;

  • resultType : Sql The return value of statement execution ;

  • parameterType : Parameter type ;

  1. Writing interface

    public interface UserMapper {// Query all users public List<User> getUserList();// Insert user public void addUser(User user);}
  2. Write corresponding mapper Medium sql sentence

    <insert id="addUser" parameterType="com.kuang.pojo.User">insert into user (id,name,password) values (#{id}, #{name}, #{password})</insert>
  3. test

    @Testpublic void test2() {SqlSession sqlSession = MybatisUtils.getSqlSession();UserMapper mapper = sqlSession.getMapper(UserMapper.class);User user  = new User(3," The spots ","666");mapper.addUser(user);// Adding, deleting and modifying must be submitted sqlSession.commit();// close sqlSessionsqlSession.close();}

    Be careful : Add, delete, change and check must submit transaction :


3. Insert

4. update

5. Delete

6. universal Map

  1. UserMapper Interface
// With everything Map Insert user public void addUser2(Map<String,Object> map);

  1. UserMapper.xml
<!-- The properties in the object can be taken out directly   Pass on map Of key--><insert id="addUser2" parameterType="map">insert into user (id,name,password) values (#{userid},#{username},#{userpassword})</insert>

  1. test
@Testpublic void test3(){SqlSession sqlSession = MybatisUtils.getSqlSession();UserMapper mapper = sqlSession.getMapper(UserMapper.class);HashMap<String, Object> map = new HashMap<String, Object>();map.put("userid",4);map.put("username"," Wang Hu ");map.put("userpassword",789);mapper.addUser2(map);// Commit transaction sqlSession.commit();// close resource sqlSession.close();}

Map Pass parameters , Directly in sql Remove from key that will do ! 【parameter=“map”】

Object passing parameters , Directly in sql You can get the properties of the object from the ! 【parameter=“Object”】

With only one basic type parameter , Can be directly in sql Take from

Multiple parameters with Map , Or annotation !

7. Fuzzy query

Fuzzy query is written like this ?

  1. Java When the code executes , Pass wildcard % %

    List<User> userList = mapper.getUserLike("% Li %");
  2. stay sql Using wildcards in stitching

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

4、 Configuration analysis

1. Core profile

  • mybatis-config.xml

  • Mybatis The configuration file contains will deeply affect MyBatis Behavior settings and attribute information .

    configuration( To configure )
        properties( attribute )
        settings( Set up )
        typeAliases( Type the alias )
        typeHandlers( Type processor )
        objectFactory( Object factory )
        plugins( plug-in unit )
        environments( Environment configuration )
        	environment( environment variable )
        		transactionManager( Transaction manager )
        		dataSource( data source )
        databaseIdProvider( Database vendor identification )
        mappers( mapper )

2. Environment configuration environments

MyBatis It can be configured to accommodate a variety of environments

But remember : Although multiple environments can be configured , But every SqlSessionFactory The instance can only choose one environment

Learn how to use and configure multiple operating environments !

MyBatis The default transaction manager is JDBC , Connection pool :POOLED

3. attribute properties

We can go through properties Property to implement the reference configuration file

These properties can be configured externally , And it can be replaced dynamically . You can either be in the typical Java Configure these properties in the properties file , It can also be in properties Set in the child element of the element .【db.poperties】

  1. Write a configuration file Note that the code should be changed to UTF-8!!!)

  2. Introduce... In the core configuration file

    <!-- Reference external configuration file --><properties resource=""><property name="username" value="root"/><property name="password" value="root"/></properties>
  • You can import external files directly
  • You can add some property configurations
  • If two files have the same field , Priority is given to the use of external configuration files

4. Type the alias typeAliases

  • The type alias can be Java Type sets an abbreviated name . It's only for XML To configure .

  • Fully qualified class name writing intended to reduce redundancy .

    <!-- You can alias entity classes --><typeAliases><typeAlias type="com.kuang.pojo.User" alias="User"/></typeAliases>

    You can also specify a package , Every one in the bag Medium Java Bean, In the absence of annotations , Will use Bean The first and lowercase unqualified class name is used as its alias . such as Its alias is author,; If you have any notes , Is alias to its annotation value . See the following example :

    <typeAliases><package name="com.zhy.pojo"/></typeAliases>

    When there are fewer entity classes , Use the first method .

    If there are many entity classes , It is recommended to use the second way to scan the package .

    The first one can DIY Alias , The second one doesn't work , If you have to change , You need to add annotations to the entity .

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

5. Set up Settings

This is a MyBatis The most important adjustment settings in , They will change MyBatis Runtime behavior for .

 Insert picture description here

6. Other configuration

7. mapper mappers

MapperRegistry: Register to bind our Mapper file ;

Mode one :【 Recommended 】

<!-- every last Mapper.xml Need to be in MyBatis Register in the core profile --><mappers><mapper resource="com/zhy/dao/UserMapper.xml"/></mappers>

Mode two : Use class File binding registration

<!-- every last Mapper.xml Need to be in MyBatis Register in the core profile --><mappers><mapper class="com.zhy.dao.UserMapper"/></mappers>

Be careful : Mode 3 is also applicable

  • Interface and his Mapper The configuration file must have the same name
  • Interface and his Mapper The configuration file must be under the same package

Mode three : Use packet scanning for Injection

<mappers><package name="com.zhy.dao"/></mappers>

8. Scope and lifecycle

 Insert picture description here

Life cycle and scope are critical , Because wrong use can lead to very serious Concurrency issues .


  • Once created SqlSessionFactory, You don't need it anymore
  • local variable


  • To put it bluntly, it can be imagined as : Database connection pool
  • SqlSessionFactory Once created, it should exist for the duration of the application , There's no reason to discard it or recreate an instance .
  • therefore SqlSessionFactory The best scope for is the application scope (ApplocationContext).
  • The simplest is to use The singleton pattern Or static singleton mode .


  • A request to connect to the connection pool
  • SqlSession Is not thread safe , So it can't be shared , So its best scope is request or method scope .
  • It needs to be shut down after use , Otherwise, the resources will be occupied !

5、 Solve the problem of inconsistency between property name and field name

1. problem

Fields in the database

 Insert picture description here

Create a new project , Copy the previous , Test for inconsistent entity class fields

 Insert picture description here

There was a problem with the test
 Insert picture description here

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

resolvent :

  • names
<select id="getUserById" resultType="com.zhy.pojo.User">select id,name,pwd as password from USER where id = #{id}</select>

2. ResultMap

Result set mapping

database :id name pwd

pojo:id name password

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

  • resultMap The element is MyBatis The most important and powerful element in .
  • ResultMap The design idea of is , Zero configuration for simple statements , For more complex statements , Just describe the relationship between the statements .
  • ResultMap The excellence of —— You don't have to configure them explicitly .
  • If only the world were always so simple .

6、 journal

6.1 Log factory

If a database operation , Something is wrong , We need to sort out the mistakes , The journal is the best assistant !

once :sout、debug

Now? : Log factory

 Insert picture description here

  • SLF4J
  • LOG4J 【 master 】
  • LOG4J2
  • STDOUT_LOGGING 【 master 】

stay MyBatis Which log to use in the implementation , Set... In settings


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

 Insert picture description here

6.2 Log4j

What is? Log4j?

  • Log4j yes Apache An open source project of , By using Log4j, We can control the destination of log information transmission Console 、 file 、GUI Components ;
  • We can also control the output format of each log ;
  • By defining the level of each log message , We can control the log generation process more carefully ;
  • The most interesting thing is , These can be configured flexibly through a configuration file , Without changing the code of the application .
  1. First import log4j My bag


    # Rank as DEBUG Log information output to console and file These two destinations ,console and file Is defined in the following code 
    # Related settings of console output 
    log4j.appender.console = org.apache.log4j.ConsoleAppender
    log4j.appender.console.Target = System.out
    log4j.appender.console.layout = org.apache.log4j.PatternLayout
    # File output related settings 
    log4j.appender.file = org.apache.log4j.RollingFileAppender
    # Log output level
  3. To configure settings by log4j Realization

  4. test run

Log4j Easy to use

  1. In use Log4j In the class , Import package import org.apache.log4j.Logger;

  2. Log object , The parameter is of the current class class object

    Logger logger = Logger.getLogger(UserDaoTest.class);
  3. The level of logging"info:  test log4j");logger.debug("debug:  test log4j");logger.error("error: test log4j");
    1. info
    2. debug
    3. error

7、 Pagination

reflection : Why paging ?

  • Reduce the amount of data processing

7.1 Use Limit Pagination

SELECT * from user limit startIndex,pageSize

Use MyBatis Implement paging , The core SQL

  1. Interface

    // Pagination List<User> getUserByLimit(Map<String,Integer> map);
  2. Mapper.xml

    <!-- Paging query --><select id="getUserByLimit" parameterType="map" resultMap="UserMap">select * from user limit #{startIndex},#{pageSize}</select>
  3. test

    @Testpublic void getUserByLimit(){SqlSession sqlSession = MybatisUtils.getSqlSession();UserMapper mapper = sqlSession.getMapper(UserMapper.class);HashMap<String, Integer> map = new HashMap<String, Integer>();map.put("startIndex",1);map.put("pageSize",2);List<User> list = mapper.getUserByLimit(map);for (User user : list) {System.out.println(user);}}

7.2 RowBounds Pagination

No longer use SQL Implement paging

  1. Interface

    // Pagination 2
    List<User> getUserByRowBounds();
  2. mapper.xml

    <!-- Paging query 2--><select id="getUserByRowBounds">select * from user limit #{startIndex},#{pageSize}</select>
  3. test

    public void getUserByRowBounds(){SqlSession sqlSession = MybatisUtils.getSqlSession();//RowBounds Realization RowBounds rowBounds = new RowBounds(1, 2);// adopt Java Pagination at the code level List<User> userList = sqlSession.selectList("com.kaung.dao.UserMapper.getUserByRowBounds", null, rowBounds);for (User user : userList) {System.out.println(user);}sqlSession.close();}

7.3 Paging plug-ins

MyBatis Paging plug-ins PageHelper

8、 Develop... Using annotations

8.1 Interface oriented development

Three aspects of differentiation

  • Object oriented means , When we think about it , In units of objects , Consider its properties and methods ;
  • Process oriented means , When we think about it , In a specific process ( Business process ) In units of , Consider its implementation ;
  • Interface design and non interface design are for reuse technology , And object-oriented ( The process ) It's not a problem , More is the overall architecture of the system ;

8.2 Develop... Using annotations

  1. Annotations are implemented on interfaces

    @Select("select * from user")List<User> getUsers();
  2. You need to bind the interface in the core configuration file

    <mappers><mapper class="com.kuang.dao.UserMapper"/></mappers>
  3. test

The essence : Reflection mechanism implementation

Bottom : A dynamic proxy

 Insert picture description here

MyBatis Detailed implementation process :

 Insert picture description here

8.3 annotation CURD

// Method has multiple parameters , All parameters must be preceded by @Param() annotation @Delete("delete from user where id = ${uid}")int deleteUser(@Param("uid") int id);

About @Param( ) annotation

  • Parameters of the basic type or String type , Need to add
  • Reference types don't need to add
  • If there is only one basic type , You can ignore , But I suggest you all add
  • We are SQL What is quoted in is our @Param() Property name set in

#{} and ${}: As far as possible with #{}, Security


Lombok The project is a Java library , It's automatically inserted into the editor and build tool ,Lombok Provides a useful set of comments , To eliminate Java A lot of boilerplate code in class . Only five characters (@Data) You can replace hundreds of lines of code to produce clean , Simple and easy to maintain Java class .

Use steps :

  1. stay IDEA Install in Lombok plug-in unit

  2. Import in the project lombok Of jar package

  3. Annotate the program

@Getter and @Setter@FieldNameConstants@ToString@EqualsAndHashCode@AllArgsConstructor, @RequiredArgsConstructor and @NoArgsConstructor@Log, @Log4j, @Log4j2, @Slf4j, @XSlf4j, @CommonsLog, @JBossLog, @Flogger, @CustomLog@Data@Builder@SuperBuilder@Singular@Delegate@Value@Accessors@Wither@With@SneakyThrows@val

explain :

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

 Insert picture description here

10、 Many to one processing

Many students, one teacher ;

alter table student ADD CONSTRAINT fk_tid foreign key (tid) references teacher(id)

1. Test environment construction

  1. Import lombok
  2. New entity class Teacher,Student
  3. establish Mapper Interface
  4. establish Mapper.xml file
  5. Bind and register our Mapper Interface or file 【 There are many ways , Choose what you want 】
  6. Test whether the query can succeed

2. Nested by query

      Ideas :
        1.  Query all student information 
        2.  According to the student's tid Looking for specific teachers  ( Subquery )
    --><select id="getStudent" resultMap="StudentTeacher">select * from student</select><resultMap id="StudentTeacher" type="student"><result property="id" column="id"/><result property="name" column="name"/><!-- Complex properties , We need to be alone   object :association  aggregate :collection--><collection property="teacher" column="tid" javaType="teacher" select="getTeacher"/></resultMap><select id="getTeacher" resultType="teacher">select * from teacher where id = #{id}</select>

3. Nested according to the result

    <!-- Query according to the results --><select id="getStudent2" resultMap="StudentTeacher2">select sid , sname, tname
        from student s,teacher t
        where</select><!-- Result encapsulation , Encapsulate the queried columns into object properties --><resultMap id="StudentTeacher2" type="student"><result property="id" column="sid"/><result property="name" column="sname"/><association property="teacher" javaType="teacher"><result property="name" column="tname"></result></association></resultMap>

review Mysql Many to one query mode :

  • Subquery ( Nested by query )
  • League table query ( Nest according to the results )

11、 One to many processing

One teacher, many students ;

For teachers , It's a one to many relationship ;

1. Environment building

Entity class

@Datapublic class Student {private int id;private String name;private int tid;}@Datapublic class Teacher {private int id;private String name;// A teacher has more than one student private List<Student> students;}

2. Nested according to the results

<!-- Nested queries by results --><select id="getTeacher" resultMap="StudentTeacher">SELECT sid, sname, tname, tid FROM student s, teacher t
    WHERE s.tid = AND tid = #{tid}</select><resultMap id="StudentTeacher" type="Teacher"><result property="id" column="tid"/><result property="name" column="tname"/><!-- Complex properties , We need to deal with it alone   object :association  aggregate :collection
    javaType="" Specifies the type of property !
     Generic information in the collection , We use ofType obtain 
    --><collection property="students" ofType="Student"><result property="id" column="sid"/><result property="name" column="sname"/><result property="tid" column="tid"/></collection></resultMap>


  1. relation - association 【 For one more 】
  2. aggregate - collection 【 One to many 】
  3. javaType & ofType
    1. JavaType Used to specify the type in the entity class
    2. ofType Used to specify the mapping to List Or in a collection pojo type , Constraint types in generics

Be careful :

  • Guarantee SQL Readability , Try to be easy to understand
  • Pay attention to one to many and many to one , Problems with property names and fields
  • If the problem is not good, check the error , Logs are available , It is recommended to use Log4j

Interview frequency

  • Mysql engine
  • InnoDB Underlying principle
  • Indexes
  • Index optimization

12、 dynamic SQL

What is dynamic SQL: dynamic SQL It is to generate different... According to different conditions SQL sentence

The so-called dynamic SQL, In essence SQL sentence , It's just that we can be in SQL level , To execute a logic code

dynamic SQL yes MyBatis One of the powerful features of . If you have JDBC Or something like that , You should be able to understand how to splice according to different conditions SQL How painful is the sentence , For example, when splicing, make sure you don't forget to add the necessary spaces , Also notice to remove the comma from the last column name of the list . Using dynamics SQL, You can get rid of this pain .

Set up the environment

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

Create a foundation project

  1. Guide pack

  2. Writing configuration files

  3. Write entity class

    @Datapublic class Blog {private String id;private String title;private String author;private Date createTime;//  Property name and field name are inconsistent private int views;}
  4. Write entity class correspondence Mapper Interface and Mapper.xml file


<select id="queryBlogIF" parameterType="map" resultType="blog">select * from blog<where><if test="title!=null">and title = #{title}</if><if test="author!=null">and author = #{author}</if></where></select>

choose (when, otherwise)


SQL fragment

sometimes , We may extract some parts of the function , Convenient reuse !

  1. Use SQL The common part of tag extraction can

    <sql id="if-title-author"><if test="title!=null">title = #{title}</if><if test="author!=null">and author = #{author}</if></sql>
  2. Use where needed Include Tag reference is enough

    <select id="queryBlogIF" parameterType="map" resultType="blog">select * from blog<where><include refid="if-title-author"></include></where></select>

matters needing attention :

  • It's best to define... Based on a single table SQL fragment
  • Don't exist where label

dynamic SQL It's just stitching SQL sentence , We just want to make sure that SQL The correctness of the , according to SQL The format of , Just go and arrange

Suggest :

  • First in Mysql Write the complete SQL, And then modify it to our dynamic SQL To achieve universal

13、 cache

13.1 brief introduction

Inquire about : Connect to database , Resource consumption

The result of a query , Give him a place where he can get it --> Memory : cache

When we query the same data again , Go directly to cache , No more databases

  1. What is caching [Cache]?
  • There is temporary data in memory
  • Put the data that users often query in the cache ( Memory ) in , Users don't need to query data from disk ( Relational database file ) Inquire about , Query from cache , So as to improve query efficiency , It solves the performance problem of high concurrency system
Why cache ?
  • Reduce the number of interactions with the database , Reduce system overhead , Improve system efficiency
What kind of data can be cached ?
  • Data that is frequently queried and rarely changes 【 You can use caching 】

13.2 MyBatis cache

  • MyBatis Contains a very powerful query caching feature , It's very convenient to customize and configure caching , Caching can greatly improve query efficiency .

  • MyBatis Two levels of cache are defined by default in the system :

    First level cache


    Second level cache

    • By default , Only level 1 cache is enabled (SqlSession Level cache , Also known as local cache )
    • The second level cache needs to be opened and configured manually , He is based on namespace Level cache .
    • To improve scalability ,MyBatis Cache interface defined Cache. We can do that by implementing Cache Interface to define the L2 cache .

13.3 First level cache

  • First level cache is also called local cache :SqlSession
    • The data queried during the same session with the database will be placed in the local cache
    • If you need to get the same data later , Take it directly from the cache , There's no need to check the database again

testing procedure :

  1. Open log

  2. Test in a Session Query records twice in

        @Testpublic void test1() {SqlSession sqlSession = MybatisUtils.getSqlSession();UserMapper mapper = sqlSession.getMapper(UserMapper.class);User user = mapper.getUserById(1);System.out.println(user);System.out.println("=====================================");User user2 =  mapper.getUserById(1);System.out.println(user2 == user);}
  3. View log output

 Insert picture description here

Cache failures :

  1. Looking for different things

  2. Add, delete and change operation , It may change the original data , So the cache must be refreshed

  3. Query different Mapper.xml

  4. Manually clean up the cache


13.4 Second level cache

  • Second level cache is also called global cache , The first level cache scope is too low , So the second level cache was born
  • be based on namespace Level cache , A namespace , It corresponds to a second level cache
  • Working mechanism
    • A session queries a piece of data , This data will be placed in the first level cache of the current session
    • If the session is closed , The first level cache corresponding to this member is gone ; But what we want is , The conversation is closed , The data in the first level cache is saved to the second level cache
    • New session query information , You can get the content from the secondary cache
    • Different mapper The query data will be put in its own corresponding cache (map) in

The first level cache is on (SqlSession Level cache , Also known as local cache )

  • The second level cache needs to be opened and configured manually , He is based on namespace Level cache .
  • To improve scalability ,MyBatis Cache interface defined Cache. We can do that by implementing Cache Interface to define the L2 cache .

step :

  1. Turn on global cache

    <!-- Open global cache shown --><setting name="cacheEnabled" value="true"/>
  2. stay Mapper.xml Using cache in

    <!-- At present Mapper.xml Second level cache is used in --><cache   eviction="FIFO"   flushInterval="60000"   size="512"   readOnly="true"/>
  3. test

    1. problem : We need to serialize the entity class , Otherwise you will report an error

Summary :

  • As long as the secondary cache is enabled , In the same Mapper It's going to work
  • All the data will be in the first level cache
  • Only the current session is committed , Or when it's closed , Will be submitted to the L2 cache

13.5 Caching principle

 Insert picture description here

Be careful :

  • Only queries have caching , Depending on whether the data needs to be cached ( Whether to modify frequently, select whether to open )useCache=“true”

        <select id="getUserById" resultType="user" useCache="true">select * from user where id = #{id}</select>

13.6 Custom cache -ehcache

Ehcache Is a widely used open source Java Distributed cache . Mainly for general purpose cache

  1. Guide pack

  2. stay mapper Use our ehcache Cache implementation

    <cache type="org.mybatis.caches.ehcache.EhcacheCache"/>