introduce

• MyBatis is an excellent persistence framework;
• It supports custom SQL, stored procedures, and advanced mappings. MyBatis eliminates almost all of the JDBC code and the work of setting parameters and getting result sets. MyBatis configures and maps primitive types, interfaces, and Java POJOs to records in the database using simple XML or annotations.

The sample

Set up the environment

<! -- Import dependencies -->
<dependencies>
    <! - mysqlq driver - >
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.12</version>
    </dependency>
    <! --mybatis-->
    <dependency>
        <groupId>org.mybatis</groupId>
        <artifactId>mybatis</artifactId>
        <version>3.5.4</version>
    </dependency>
</dependencies>

Copy the code

Create a module

Write myBatis core configuration file myBatis -config.xml

      
<! DOCTYPEconfiguration
        PUBLIC "- / / mybatis.org//DTD Config / 3.0 / EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<! --configuration core configuration file -->
<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? userSSL=true&amp;useUnicode=true&amp;characterEncoding=UTF-8&amp;serverTimezone=UTC"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>
    </environments>
</configuration>

Copy the code

Write the MyBatis utility class

//sqlSessionFactory --> sqlSession
public class MybatisUtils {

    static SqlSessionFactory sqlSessionFactory = null;

    static {
        try {
            // Use Mybatis to obtain the sqlSessionFactory object
            String resource = "mybatis-config.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch(IOException e) { e.printStackTrace(); }}// Since we have an SqlSessionFactory, as the name implies, we can get an instance of SqlSession from it.
    // SqlSession provides all the methods needed to execute SQL commands in the database.
    public static SqlSession getSqlSession(a){
        returnsqlSessionFactory.openSession(); }}Copy the code

Write the code

The package name in the namespace must be the same as that of the Dao/Mapper interface

• Id: indicates the method name in the namespace.
• ResultType: return value of Sql statement execution.
• ParameterType: indicates the parameterType.

public interface UserDao {
    public List<User> getUserList(a); } <? xml version="1.0" encoding="UTF-8"? > <! DOCTYPE mapper PUBLIC"- / / mybatis.org//DTD Mapper / 3.0 / EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <! --namespace= Bind a Dao/Mapper interface --> < Mapper namespace="com.zwt.dao.UserDao">
    <select id="getUserList" resultType="com.kuang.pojo.User">
    select * from USER
  </select>
</mapper>

    
        @Test
    public void test(a){

        //1. Obtain the SqlSession object
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        / / 2. Execute SQL
        // Method 1: getMapper
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        List<User> userList = userDao.getUserList();
        for (User user : userList) {
            System.out.println(user);
        }

        / / close the sqlSession
        sqlSession.close();
    }

Copy the code

CURD

Note: Add, delete, and check must be committed to the transaction:

sqlSession.commit();
Copy the code

Universal Map

Assuming that our entity class or database has too many tables, fields, or parameters, we should consider using Map!

// Insert user with universal Map
public void addUser2(Map<String,Object> map); <! <insert id= <insert id= <insert id= <insert id="addUser2" parameterType="map">
    insert into user (id,name,password) values (#{userid},#{username},#{userpassword})
</insert>
Copy the code

Map pass parameter, directly in SQL extract key can be! 【 parameter = “map”

Object pass parameters, directly in SQL to take out the object properties can be! 【 parameter = “Object”

In the case of only one basic type parameter, it can be fetched directly in SQL

Multiple parameters are mapped, or annotated!

Configure the parsing

• mybatis-config.xml
• The Mybatis configuration file contains Settings and attributes that deeply affect the behavior of Mybatis.

Configuration properties Settings typeAliases typeHandlers objectFactory plugins Environments Environment variables transactionManager dataSource databaseIdProvider MappersCopy the code

Environment configuration

MyBatis can be configured for a variety of environments

Keep in mind, though: although multiple environments can be configured, only one environment can be selected per SqlSessionFactory instance

Learn how to configure multiple running environments!

MyBatis default transaction manager is JDBC, connection pool: POOLED

The attribute properties

The reference configuration file can be implemented using the properties property

These properties can be configured externally and can be dynamically replaced.

You can configure these properties either in a typical Java properties file or in a child element of the Properties element. 【 the poperties 】

TypeAliases typeAliases

• A type alias can set an abbreviated name for a Java type. It is only used for XML configuration.
• Intended to reduce redundant fully qualified class name writing.

<! You can alias an entity class -->
<typeAliases>
    <typeAlias type="com.zwt.pojo.User" alias="User"/>
</typeAliases>

Copy the code

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

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

Other configuration

• typeHandlers
• ObjectFactory = objectFactory
• The plugins plugin
  • mybatis-generator-core
  • mybatis-plus
  • General mapper

Mapper mappers

MapperRegistry: register binding to our Mapper files;

Method 1: [Recommended use]

<! -- Each mapper. XML needs to be registered in the MyBatis core configuration file -->
<mappers>
    <mapper resource="com/zwt/dao/UserMapper.xml"/>
</mappers>

Copy the code

Method 2: Register using class file binding

<! -- Each mapper. XML needs to be registered in the MyBatis core configuration file -->
<mappers>
    <mapper class="com.zwt.dao.UserMapper"/>
</mappers>Note: The interface and its Mapper profile must have the same name. The interface and its Mapper profile must be in the same packageCopy the code

Method 3: Use package scan for injection

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

Copy the code

Scope and lifecycle

SqlSessionFactoryBuilder:

• Once the SqlSessionFactory is created, it is no longer needed
• A local variable

SqlSessionFactory:

• To put it bluntly: database connection pooling
• Once created, the qlSessionFactory should exist for the duration of the application, and there is no reason to discard it or recreate an instance
• Therefore, the best scope for SqlSessionFactory is the application scope (ApplocationContext).
• The simplest is to use the singleton or static singleton pattern.

SqlSession:

• A request to connect to the connection pool
• Instances of SqlSession are not thread-safe and therefore cannot be shared, so its best scope is the request or method scope.
• After use, need to close quickly, otherwise the resource is occupied!

paging

Paging is used to reduce the amount of data processed

Paging with Limit

/ / paging
List<User> getUserByLimit(Map<String,Integer> map); <! <select id="getUserByLimit" parameterType="map" resultMap="UserMap">
    select * from user limit #{startIndex},#{pageSize}
</select>


    @Test
    public void getUserByLimit(a){
        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); }}Copy the code

RowBounds paging

/ / page 2
List<User> getUserByRowBounds(a); <! -- Paging query2-->
<select id="getUserByRowBounds">
    select * from user limit #{startIndex},#{pageSize}
</select>

    
    public void getUserByRowBounds(a){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        / / RowBounds implementation
        RowBounds rowBounds = new RowBounds(1.2);
        // Implement paging at the Java code level
        List<User> userList = sqlSession.selectList("com.zwt.dao.UserMapper.getUserByRowBounds".null, rowBounds);
        for (User user : userList) {
            System.out.println(user);
        }
        sqlSession.close();
    }

Copy the code

Paging plug-in

Annotations to develop

// Annotations are implemented on the interface
@Select("select * from user")
List<User> getUsers(a);


// Interfaces need to be bound in the core configuration file
<mappers>
    <mapper class="com.zwt.dao.UserMapper"/>
</mappers>

Copy the code

Essence: Reflection mechanism implementation

Bottom layer: dynamic proxy

MyBatis detailed execution process

Annotations CURD

// The method has multiple arguments. All arguments must be preceded by @param ("id") annotations
@Delete("delete from user where id = ${uid}")
int deleteUser(@Param("uid") int id);

Copy the code

About @param () annotations

• Parameter of basic type or String type, need to add
• Reference types do not need to be added
• If there is only one basic type, you can ignore it, but it is recommended that everyone add it
• What we’re referring to in SQL is the name of the property that we set here in @param ()

Many-to-one and many-to-many

Multiple students one teacher;

For one more

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

Test environment construction:

1. Import lombok
2. Create an entity class Teacher,Student
3. Example Establish a Mapper interface
4. Create the mapper.xml file
5. Bind and register our Mapper interface or file in the core profile.
6. Tests whether the query succeeds

@Data
public class Student {
    private int id;
    private String name;
    private int tid;
}


@Data
public class Student {
    private int id;
    private String name;
    private int tid;
}

Copy the code

// Follow the query nesting process<! - ideas:1.Query all student information2.Select * from student where id= id; select * from student where id= id; select * from student where id= id"getStudent" resultMap="StudentTeacher">
    select * from student
</select>
<resultMap id="StudentTeacher" type="student">
    <result property="id" column="id"/>
    <result property="name" column="name"/ > <! -- Complex property, we need to separate out the object: association collection: collection--> <collection property="teacher" column="tid" javaType="teacher" select="getTeacher"/>
</resultMap>
<select id="getTeacher" resultType="teacher">
    select * from teacher where id = #{id}
</select>

    
// The result is nested<! Select id=. Select id="getStudent2" resultMap="StudentTeacher2"> select s.id sid , s.name sname, t.name tname from student s,teacher t where s.tid=t.id </select> <! > <resultMap id= <resultMap id= <resultMap id= <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>

Copy the code

• Subqueries (nested by query)
• Join table query (nested by results)

More than a pair of

<! Select id= (select id= (select id= (select id= (select id=)))"getTeacher" resultMap="StudentTeacher">
    SELECT s.id sid, s.name sname,t.name tname,t.id tid FROM student s, teacher t
    WHERE s.tid = t.id AND tid = #{tid}
</select>
<resultMap id="StudentTeacher" type="Teacher">
    <result property="id" column="tid"/>
    <result property="name" column="tname"/ > <! -- Complex properties that we need to handle the object separately: Association collection: Collection javaType=""Specify the type of the property! For generic information in collection, we use ofType --> <collection property="students" ofType="Student">
        <result property="id" column="sid"/>
        <result property="name" column="sname"/>
        <result property="tid" column="tid"/>
    </collection>
</resultMap>

Copy the code

summary

1. Association-association [many-to-one]
2. A collection of one to many
3. javaType & ofType
   1. JavaType is used to specify the type in an entity class
   2. OfType specifies poJO types that map to lists or collections, and constraint types in generics

Dynamic SQL

What is dynamic SQL: Dynamic SQL is the generation of different SQL statements based on different conditions

So dynamic SQL is essentially SQL statements, but we can execute a logical code at the SQL level

For example IF

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

            
 <sql id="if-title-author">
    <if test="title! =null">
        title = #{title}
    </if>
    <if test="author! =null">
        and author = #{author}
    </if>
</sql>

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

Dynamic SQL is in the splicing of SQL statements, we only need to ensure the correctness of the SQL, according to the SQL format, to arrange the combination can be

Advice:

• First write the complete SQL in Mysql, and then modify the corresponding to our dynamic SQL implementation can be general

MyBatis cache

Query: Connects to the database and consumes resources

The result of a query is temporarily stored in a place where it can be fetched directly

When we query the same data again, we go directly to the cache instead of the database

• MyBatis includes a very powerful query cache feature, which can be very convenient to customize and configure the cache, caching can greatly improve the query efficiency.
• MyBatis system defines two levels of cache by default: level-1 cache and level-2 cache
• By default, only level-1 caching is enabled (SqlSession level caching, also known as local caching)
• Level 2 caching needs to be manually enabled and configured. It is namespace-level caching.
• To improve scalability, MyBatis defines the Cache interface. We can define the level 2 Cache by implementing the Cache interface.

Level 1 cache

• Level 1 cache is also called local cache: SqlSession
  • Data queried during the same session with the database is placed in the local cache
  • In the future, if you need to retrieve the same data, you can retrieve it directly from the cache without having to query the database

    @Test
    public void test1(a) {
        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);
    }

// Test, when log is enabled, you can see that JDBC is connected only once
Copy the code

Cache invalidation:

1. Query different things
2. Add, delete, and change operations may change the original data, so the cache must be refreshed
3. Query different mapper.xml
4. Manually clear the cache (sqlsession.clearcache ();)

The second level cache

• The level 2 cache is also called the global cache. The level 1 cache scope is too low, so the level 2 cache is born

• Namespace-based caching, where each namespace corresponds to a level 2 cache

• Working mechanism

  • A session queries a piece of data, and that data is placed in the level 1 cache of the current session

  • If the session is closed, the level 1 cache for the session is lost. But what we want is for the session to be closed and the data in the level 1 cache to be stored in the level 2 cache

  • The new session queries for information and retrieves the content from the level 2 cache

  • The data queried by each mapper is stored in its own cache (map)

Level 1 cache enabled (SqlSession level cache, also known as local cache)

• Level 2 caching needs to be manually enabled and configured. It is namespace-level caching.
• To improve scalability, MyBatis defines the Cache interface. We can define the level 2 Cache by implementing the Cache interface.

Steps:

<! Setting name= <setting name= <setting name= <setting name= <setting name="cacheEnabled" value="true"/ > <! -- Use level 2 cache in current mapper.xml --> <cache eviction="FIFO"
       flushInterval="60000"
       size="512"
       readOnly="true"/>
Copy the code

Summary:

• As long as the level 2 cache is enabled, it is valid under the same Mapper
• All data will be stored in the level 1 cache
• Only commits to the level 2 cache if the current session commits or if it is closed

Principle of cache

Note:

• UseCache = “true” useCache= “true”

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

Copy the code

Custom cache – Ehcache

Ehcache is a widely used open source Java distributed cache. Mainly for general purpose caches

<dependency>
    <groupId>org.mybatis.caches</groupId>
    <artifactId>mybatis-ehcache</artifactId>
    <version>1.21.</version>
</dependency>

// Specify in mapper to use our ehCache implementation
<cache type="org.mybatis.caches.ehcache.EhcacheCache"/>

Copy the code