1, Introduction to MyBatis

==Qianfeng Taoge 039==

1.1 framework concept

Framework is the semi-finished product of software, which completes the general operation in the process of software development. Programmers can realize specific functions with little or no processing, so as to simplify the steps of developers in software development and improve development efficiency.

1.2 common frames

Front end framework, jQuery, React
Server side framework: servlet framework spring MVC

• MVC framework simplifies the development steps of Servlet.
  – Sturts
  – Struts 2 is not an upgrade, but another framework
  – SrpingMVC
• Persistence layer framework: a framework for completing database operations
  – apache DBUtils
  – Hibernate
  – Spring JPA
  – Mybatis
• Glue frame: Spring
  SSM framework = Spring + spring MVC + mybatis.
  ==040 ==

1.3 introduction to mybatis

MyBatis is a semi-automatic ORM framework. iBatis comes out first, followed by Hibernate automatic framework, but the efficiency is not high. With the emergence of Internet projects (high availability and high performance), some shortcomings are gradually exposed. You also think of MyBatis, which is still semi-automatic. Fortunately, it can be modified manually.
ORM Object Relational Mapping, object relational mapping, one-to-one correspondence between an object in java and a row record in the database table. ORM framework provides the mapping relationship between entity classes and data tables, and realizes the persistence of objects through the configuration of mapping files.

• MyBatis, formerly known as iBatis, is an open source project provided by the Apache Software Foundation.
• In 2010, it moved to Google code and officially changed its name to MyBatis
• Moved to Github hosting in 2013.
• MyBatis features.
  – support custom SQL and stored procedures.
  – close the original JDBC and eliminate almost all JDBC code.
  – support XML and annotation configuration, automatically complete ROM operation and realize result mapping.
  == 041 ==

2, MyBatis framework deployment

Framework deployment is to introduce the framework into our project.

2.1 create maven project

• Both Java project and web project

2.2 adding project dependencies

• Add dependencies to the project pom file

<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.46</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.4.6</version>
        </dependency>

2.3 create MyBatis configuration file

• Create the MyBatis configuration file in the resources folder

    <!--    stay environments Configure database connection information-->
    <!--    stay environments Multiple can be defined in a label environment,each environment Tags can define a set of connection configurations-->
    <!--    default Property to specify which to use environment label -->
    <environments default="mysql">
        <environment id="mysql">
            <!-- transactionManager Tags are used to configure how data is managed-->
            <transactionManager type="JDBC"></transactionManager>
            <!-- <dataSource Used to configure database connection information-->
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis?characterEncoding=utf-8"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>
    </environments>

== 042==

3, MyBatis framework uses

3.1 create data table

CREATE TABLE `student` (
`sid`  int(10) NULL COMMENT 'ID' ,
`stu_num`  varchar(30) NULL COMMENT 'number' ,
`stu_name`  varchar(30) NULL COMMENT 'full name' ,
`stu_gender`  varchar(30) NULL COMMENT 'Gender' ,
`stu_age`  int(10) NULL COMMENT 'Age' 
);

3.2 creating entity classes

Import lombok dependencies to implement get, set and tostring of entity classes through annotations.

<!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.22</version>
    <scope>provided</scope>
</dependency>

com.zhang.bean.Student.java

@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class Student {

    private  int stuId;
    private String stuNum;
    private String stuName;
    private String stuGender;
    private int stuAge;
}

3.3 create DAO and define operation method

• com.zhang.dao.StudentDAO.java

public interface StudentDAO {
    public int insertStudent(Student student); // add to
    public int deleteStudent(String stuNum);    // delete
}

3.4 create mapping file for DAO interface

• In the resources directory, create a new folder named mappers
• Create a new mapping file named StudentMapper.xml in mappers. (create file from template)
• Implement the methods defined in DAO in the mapping file.

<mapper namespace="com.zhang.dao.StudentDAO">
<!-- Relative DAO Implementation class of the interface, namespace Property to specify the implementation DAO Fully qualified name of the interface-->
    <insert id="insertStudent" parameterType="com.zhang.bean.Student">
        insert into student(sid,stu_num,stu_name,stu_gender,stu_age)
        values(#{stuId},#{stuNum},#{stuName},#{stuGender},#{stuAge});
    </insert>

    <insert id="deleteStudent" parameterType="com.zhang.bean.Student">
        delete from student where stu_num = #{stuName};
    </insert>

</mapper>

• The mapper file is equivalent to the implementation class of the interface file.

3.5 add mapping file to main configuration file

• resources/mybatis-config.xml.

    <mappers>
        <mapper resource="mappers/StudentMapper"></mapper>
    </mappers>

==043 ==

4, Unit test

4.1 adding dependencies

        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>

4.2 creating unit test classes

Right click the tested class and select generate -- select test

public class StudentDAOTest {

    @org.junit.Test
    public void insertStudent() {

        try {
            // Load mybatis configuration file
            InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
            SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
            // Connection factory
            SqlSessionFactory factory = builder.build(is);
            // Session (connection)
            SqlSession sqlSession = factory.openSession();
            StudentDAO studentDAO = sqlSession.getMapper(StudentDAO.class);
            System.out.println(studentDAO);

            // Testing methods in StudentDAO
            int i = studentDAO.insertStudent(new Student(0, "001", "Zhang Hengchao", "nan", 23));
            // Manual submission is required here
            sqlSession.commit();
            System.out.println(i);

        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    @org.junit.Test
    public void deleteStudent() {
    }
}

Problem Pit 1
The test results in an error, java.sql.SQLException: Incorrect string value: '\xE5\xB0‘
The reason is that the character set of database tables and fields is latin1. Changing to UTF-8 can solve the problem. Reference link

Problem Pit 2
Test times: You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true
The reason is that when the database is connected, the configuration description SSL attribute is required. Specifically, add useSSL=false in mybatis-config.xml to solve the problem. See the following for details.

     <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis?useUnicode=true&amp;characterEncoding=utf-8&amp;useSSL=false"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>

044

5, CRUD operation for MyBatis

5.1 adding

5.2 delete - delete according to student number

• Define the deletion method in StudentDAO.

public interface StudentDAO {
    public int insertStudent(Student student); // add to
    public int deleteStudent(String stuNum);    // delete
}

• Implement the interface method in StudentMapper.xml.

<detete id="deleteStudent" parameterType="com.zhang.bean.Student">
        delete from student where stu_num = #{stuName};
    </detete>

• Create a test method in the test class

@org.junit.Test
    public void eleteStudent() {
        try {
            InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
            //SqlSessionFactoryBuilder
            SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
            // SqlSessionFactory represents the session factory of MyBatis
            SqlSessionFactory factory = builder.build(is);
            // SqlSession represents the session between MyBatis and the database, and designs the pattern through the factory method.
            SqlSession sqlSession = factory.openSession();
            // Call the getMapper method through the SqlSession object to obtain the DAO interface object
            StudentDAO studentDAO = sqlSession.getMapper(StudentDAO.class);
            // Call the method under test
            int i = studentDAO.deleteStudent("002");
            System.out.println(i);
            // Manual submission is required here
            sqlSession.commit();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

==45 ==

5.3 modification

Modify other field information according to the student number. Generally, the student number and ID information are not modified
– adding methods to DAO

public interface StudentDAO {
    public int insertStudent(Student student); // add to
    public int deleteStudent(String stuNum);    // delete
    public int updateStudent(Student student);    // modify
}

• Add implementation in mapper

    <update id="updateStudent">
        update student
        set stu_name = #{stuName},
            stu_gender = #{stuGender},
            stu_age = #{stuAge}
        where stu_num = #{stuNum}
    </update>

• Add test method to test class

    @org.junit.Test
    public void updateStudent(){
        try {
            InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
            SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
            SqlSessionFactory factory = builder.build(is);
            SqlSession sqlSession = factory.openSession();
            StudentDAO studentDAO = sqlSession.getMapper(StudentDAO.class);
            int i = studentDAO.updateStudent(new Student(1, "001", "Li Wenhao", "female", 32));
            sqlSession.commit();
            assertEquals(1,i);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

== 46 ==

5.4 query all

• Method defined in DAO

public List<Student> listStudents();             // Query all

• Implementation method in Mapper

 <!--    resultType Specifies the entity class of the object encapsulated by the query result, which cannot be omitted-->
    <!--    resultSets Specifies the collection type returned by the current operation, which can be omitted-->
    <!--    Method 1: specify the corresponding relationship between database table fields and object attributes
    <select id="listStudents" resultType="com.zhang.bean.Student" resultSets="java.util.List">
        select sid stuId,stu_num stuNum,stu_name stuName
            ,stu_gender stuGender,stu_age stuAge
        from student;
    </select>
    -->

    <!--  Method 2   resultMap Labels are used to define the mapping relationship between objects and data tables-->
    <resultMap id="studentMap" type="com.zhang.bean.Student">
        <id column="sid" property="stuId"></id>
        <result column="stu_num" property="stuNum"></result>
        <result column="stu_name" property="stuName"></result>
        <result column="stu_gender" property="stuGender"></result>
        <result column="stu_age" property="stuAge"></result>
    </resultMap>

    <!-- resultMap The mapping relationship used to reference an entity has the above resultMap Definitions and references, resultType You can omit it-->
    <select id="listStudents" resultType="com.zhang.bean.Student" resultMap="studentMap">
        select sid,stu_num,stu_name,stu_gender,stu_age
        from student;
    </select>

• Implementing test methods in test classes

@org.junit.Test
    public void listStudents(){
        try {
            InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
            SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
            SqlSessionFactory factory = builder.build(is);
            SqlSession sqlSession = factory.openSession();
            StudentDAO studentDAO = sqlSession.getMapper(StudentDAO.class);
            List<Student> students = studentDAO.listStudents();
            sqlSession.commit();
            for (Student stu:students){
                System.out.println(stu);
            }
            // assertNotNull(students);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

== 047==

5.5 query a record by primary key

• Methods defined in Dao

    public Student queryStudent(String stuNum);      // Query a record

• Mapper definition implementation

     <select id="queryStudent" resultMap="studentMap">
        select sid,stu_num,stu_name,stu_gender,stu_age
        from student
        where stu_num = #{stuNum}
    </select>

• unit testing

@org.junit.Test
    public void queryStudent(){
        try {
            InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
            SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
            SqlSessionFactory factory = builder.build(is);
            SqlSession sqlSession = factory.openSession();
            StudentDAO studentDAO = sqlSession.getMapper(StudentDAO.class);
            Student stu = studentDAO.queryStudent("001");
            sqlSession.commit();
            System.out.println(stu);
            // assertNotNull(students);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

== 048==

5.7 query total entries

• DAO method

    public int getCount();  // Number of query records

• Mapper implementation

    <select id="getCount" resultType="int">
        select count(1)
        from student;
    </select>

• Test method test

    @org.junit.Test
    public void getCount(){
        try {
            InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
            SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
            SqlSessionFactory factory = builder.build(is);
            SqlSession sqlSession = factory.openSession();
            StudentDAO studentDAO = sqlSession.getMapper(StudentDAO.class);
            int count = studentDAO.getCount();
            sqlSession.commit();
            System.out.println(count);
            // assertNotNull(students);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

== 49==

5.6 query operation - multi parameter query

Paging query (when multiple parameters are required)

• The operation method is defined in the DAO. If the method has multiple parameters, the alias of the parameter is declared with the @ Param annotation.

    // The number of paged entries. Multiple parameters can be annotated to parameter aliases through @ Param - Parameter annotation
    public List<Student> listStudentsByPages(@Param("start") int start,@Param("pageSize") int pageSize);  // Number of paged records

• Implementation defined in Mapper

    <!-- Multi parameter paging query -->
    <select id="listStudents"  resultMap="studentMap">
        select sid,stu_num,stu_name,stu_gender,stu_age
        from student
        limit #{start},#{pageSize}
    </select>

Note: if the daor operation method does not specify a parameter alias through @ param, you can also obtain parameters through arg0,arg1 or param1,param2... In SQL.

• Test method and test

@org.junit.Test
    public void listStudentsByPages(){
        try {
            InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
            SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
            SqlSessionFactory factory = builder.build(is);
            SqlSession sqlSession = factory.openSession();
            StudentDAO studentDAO = sqlSession.getMapper(StudentDAO.class);
            List<Student> students = studentDAO.listStudentsByPages(0,1);
            sqlSession.commit();
            for (Student stu:students){
                System.out.println(stu);
            }
            // assertNotNull(students);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }Insert the code slice here

050

5.8 add the primary key id generated by the backfill operation

• In the mapper.xml insert operation tag, add useGeneratedKeys and keyProperty

        <!--    useGeneratedKeys Indicates whether the add operation needs to backfill the generated primary key-->
        <!--    keyProperty Sets which attribute of the parameter object the primary key value of the backfill is assigned to-->
    <insert id="insertStudent" useGeneratedKeys="true" keyProperty="stuId" parameterType="com.zhang.bean.Student">
        insert into student(sid,stu_num,stu_name,stu_gender,stu_age)
        values(#{stuId},#{stuNum},#{stuName},#{stuGender},#{stuAge});
    </insert>

• Test verification
  

051

6, Encapsulation of tool classes

• Build package com.zhang.utils
• Build tool class MyBatisUtil.java

public class MyBatisUtil {
    private static SqlSessionFactory factory;
    private static final ThreadLocal<SqlSession> local = new ThreadLocal<SqlSession>();
    static{
        try {
            // Load mybatis configuration file
            InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
            SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
            // Connection factory
            factory = builder.build(is);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    public static SqlSession getSqlSession(){
        SqlSession sqlSession = local.get();
        if(sqlSession == null){
            sqlSession = factory.openSession();
            local.set(sqlSession);
        }
        return sqlSession;
    }
    public static <T extends Object>T getMapper(Class<T> c){
        SqlSession sqlSession = getSqlSession();
        return sqlSession.getMapper(c);
    }
}

• Call tool class
  In the insert method requiring transaction management

public void insertStudent() {
        SqlSession sqlSession = MyBatisUtil.getSqlSession();
        StudentDAO studentDAO = sqlSession.getMapper(StudentDAO.class);
        System.out.println(studentDAO);

In the select method without transaction management

 public void listStudents(){
        StudentDAO studentDAO = MyBatisUtil.getMapper(StudentDAO.class);
        List<Student> students = studentDAO.listStudents();
        for (Student stu:students){
            System.out.println(stu);
        }

052

Role of SqlSession object

• getMapper(DAO.class) can get mapper (instance of Dao interface)
• Transaction management.
  When we get the sqlSession object, the transaction is enabled by default.
  After the operation is completed, it needs to be submitted manually.

7.1 manually commit transactions

Commit the transaction after the operation is completed. When the exception fails, sqlsession commit();, Rollback transaction sqlSession rollback()

7.2 auto commit transactions

• Modify MyBatisUtil.xml

public class MyBatisUtil {
    private static SqlSessionFactory factory;
    private static final ThreadLocal<SqlSession> local = new ThreadLocal<SqlSession>();
    static{
        try {
            // Load mybatis configuration file
            InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
            SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
            // Connection factory
            factory = builder.build(is);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static SqlSessionFactory getFactory(){
        return factory;
    }

    private static SqlSession getSqlSession(boolean isAutoCommit){
        SqlSession sqlSession = local.get();
        if(sqlSession == null){
            // When SqlSessionFactory calls the openSession method to obtain the sqSession object, you can set whether the transaction is automatically committed through parameters
            // The default value is false. Transactions are not automatically committed. If true, transactions are automatically committed
            sqlSession = factory.openSession(isAutoCommit);
            local.set(sqlSession);
        }
        return sqlSession;
    }
    // Manual transaction commit
    public static SqlSession getSqlSession(){
        return getSqlSession(false);
    }

    // Automatic transaction commit
    public static <T extends Object>T getMapper(Class<T> c){
        SqlSession sqlSession = getSqlSession(true);
        return sqlSession.getMapper(c);
    }
}

== 053==

8, MyBatis master profile

mybatis-config.xml is the main configuration file of mybatis framework, which is mainly used to configure mybats data source and attribute information.

8.1 properties tab

• The properties tag is used to set key value pairs or reference the property file. After reference, you can obtain the key value pairs in the property file during configuration.
  1) Create the jdbc.properties file in the resources directory and create key value pairs.

mysql_driver=com.mysql.jdbc.Driver
mysql_url=jdbc:mysql://localhost:3306/mybatis?useUnicode=true&characterEncoding=utf-8&useSSL=false
mysql_username=root
mysql_password=root

2) In mybatis-config.xml, reference the key value pairs in the properties file through the properties tag

<!-- properties Label 1.Key value pairs can be defined, 2.Property files can be referenced-->
    <properties resource="jdbc.properties">

3) Modify environment

<dataSource type="POOLED">
                <property name="driver" value="${mysql_driver}"/>
                <property name="url" value="${mysql_url}"/>
                <property name="username" value="${mysql_username}"/>
                <property name="password" value="${mysql_password}"/>
            </dataSource>

8.2 settings tab

 <settings>
        <!--  Set start L2 cache -->
        <setting name="cacheEnabled" value="true"/>  
        <!--  Start delay loading-->
        <setting name="lazyLoadEnabled" value="true"/>  
    </settings>

8.3 typeAliases label

Type alias

    <!-- typeAliases It is used to alias the entity class. In the mapping file, you can directly use the alias instead of the fully qualified name of the entity class-->
    <typeAliases>
        <typeAlias type="com.zhang.bean.Student" alias="Student"></typeAlias>
    </typeAliases>

8.4 plugins tab

    <!-- plugins Configuration plug-in for configuring mybatis Plug ins, such as paging plug-ins-->
    <plugins>
        <plugin interceptor=""></plugin>
    </plugins>

8.5 environment label

Configure data connection environment

<!--    stay environments Configure database connection information-->
    <!--    stay environments Multiple can be defined in a label environment,each environment Tags can define a set of connection configurations-->
    <!--    default Property to specify which to use environment label -->
    <environments default="mysql">
        <environment id="mysql">
            <!-- transactionManager Tags are used to configure how data is managed-->
            <!-- transactionManager type=JDBC You can commit and rollback transactions
            transactionManager type=MANAGED It relies on the container for transaction management and does not commit or rollback transactions-->
            <transactionManager type="JDBC"></transactionManager>
            <!-- <dataSource Used to configure database connection information-->
            <dataSource type="POOLED">
                <property name="driver" value="${mysql_driver}"/>
                <property name="url" value="${mysql_url}"/>
                <property name="username" value="${mysql_username}"/>
                <property name="password" value="${mysql_password}"/>
            </dataSource>
        </environment>
    </environments>

8.6 mapper label

It is used to load mapping files. The number of mapping files in the project depends on the number of lines configured in this tab.

    <mappers>
        <mapper resource="mappers/StudentMapper.xml"></mapper>
    </mappers>

==054 ==

9, Mapping file mapper

mapper files must be configured according to the specifications of mybatis.

9.1 MyBatis initialization

== 055==

9.2 mapper root label

The mapper file is equivalent to the implementation class of the DAO interface, and the namespace attribute specifies the fully qualified name of the DAO interface to be implemented.

9.3 insert tag

Declare add operation (sql:insert)
Common properties
id attribute to bind the method of the corresponding DAO interface.
The parameterType property is used to specify the parameter type of the corresponding method in the interface (can be omitted).
useGeneratedKeys property, which sets whether the generated primary key needs to be backfilled for the add operation.
The keyProperty property sets the id of the backfill to which property in the parameter object.
Timeout property to set the timeout of this operation. If it is not set, it will wait all the time.
There are two ways to backfill primary keys
Mode 1

    <insert id="insertStudent" >
        <selectKey keyProperty="stuId" resultType="java.lang.Integer">
            select last_insert_id()
        </selectKey>
        insert into student(sid,stu_num,stu_name,stu_gender,stu_age)
        values(#{stuId},#{stuNum},#{stuName},#{stuGender},#{stuAge});
    </insert>

Mode II

        <!--    useGeneratedKeys Indicates whether the add operation needs to backfill the generated primary key-->
        <!--    keyProperty Sets which attribute of the parameter object the primary key value of the backfill is assigned to-->
    <insert id="insertStudent" useGeneratedKeys="true" keyProperty="stuId" parameterType="com.zhang.bean.Student">
        insert into student(sid,stu_num,stu_name,stu_gender,stu_age)
        values(#{stuId},#{stuNum},#{stuName},#{stuGender},#{stuAge});
    </insert>

9.4 delete tag

Declare delete operation

9.5 upage label

Declare modification action

9.6 select tag

Declare query operation

• id specifies the method name of the bound method
• parameterType property to set the parameter type
• resultType specifies the object type encapsulated by the data returned by the current sql
• resultMap specifies the correspondence from the data table to entity classes and attributes
• useCache specifies whether the query operation requires caching
• Timeout sets the timeout

9.7 resultMap label

Used to declare the mapping relationship between entity attributes and entity class tables.

    <!--  Method 2   resultMap Labels are used to define the mapping relationship between objects and data tables-->
    <resultMap id="studentMap" type="com.zhang.bean.Student">
        <id column="sid" property="stuId"></id>
        <result column="stu_num" property="stuNum"></result>
        <result column="stu_name" property="stuName"></result>
        <result column="stu_gender" property="stuGender"></result>
        <result column="stu_age" property="stuAge"></result>
    </resultMap>

9.8 cache label

Sets the cache property settings when the current DAO performs data operations

    <cache size="" readOnly="">       
    </cache> 

9.9 sql and include tags

sql tag, define the sql fragment, and then refer to it through include.

• Define sql Tags

  <sql id="stuitem">
        sid,stu_num,stu_name,stu_gender,stu_age
    </sql>

• include reference sql fragment

    <select id="listStudents" resultType="com.zhang.bean.Student" resultMap="studentMap">
        select <include refid="stuitem"/>
        from student;
    </select>

056

10, Paging plug-in

The paging plug-in is a third-party plug-in independent of the myBatis framework.

10.1 add dependency of paging plug-in

PageHelper

<!-- https://mvnrepository.com/artifact/com.github.pagehelper/pagehelper -->
<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper</artifactId>
    <version>5.3.0</version>
</dependency>

10.2 configuring plug-ins

Configure in mybatis-config.xml

<plugins>
        <plugin interceptor="com.github.pagehelper.PageInterceptor"></plugin>
</plugins>

10.3 paging instance

Paging query of student information

    @Test
    public void listStudentByPages(){
        StudentDAO studentDAO = MyBatisUtil.getMapper(StudentDAO.class);
        PageHelper.startPage(1,4);
        List<Student> students = studentDAO.listStudents();
        PageInfo<Student> pageInfo = new PageInfo<Student>(students);
        // pageInfo contains data and paging information
        List<Student> list = pageInfo.getList();
        for(Student stu:list){
            System.out.println(stu);
        }
    }

**Conditional paging**
In fact, the difference between with and without conditions is the query statement in mapper.xml.

==057 ==

11, Association mapping

11.1 entity relationship

Entity – data entity. Entity relationship refers to the relationship between data and data.
**One to one Association**
The relationship between student ID card, person and ID card, user basic information and details.

• Primary key association is associated by primary key ID. for example, when the primary key in user table is the same as that in detail table, it indicates that it is matching data.
  One to many association is equal to many to one association
  Example: class to student, student to class
  Data table relationship: add a foreign key at the end of the multi to associate with a field of one.
  Many to many Association
  Users and roles

Create the third relational table and add two foreign keys to associate with the primary keys of the two tables.
058

11.2 create project and deploy MyBatis project

• New maven project
• Set the packaging method to war in pom.xml
• Create web.xml
  
• There are two ways to add web support, one is as shown in the figure below, and the other is to add JSP and servlet dependency.

• maven, remember to refresh
  

• Add server configuration
  
  
  

• Deploy MyBatis framework
  – add MyBatis dependency to pom

<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.7</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.46</version>
        </dependency>

– create jdbc.properties and mybatis-config.xml in the resources folder

– create com.zhang.utils.MyBatilUtil.java wrapper class

The following code should be mastered. Why is it written like this.

package com.zhang.utils;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.IOException;
import java.io.InputStream;

public class MyBatisUtil {
    private static SqlSessionFactory factory;
    private static final ThreadLocal<SqlSession> local = new ThreadLocal<SqlSession>();
    static{
        try {
            // Load mybatis configuration file
            InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
            SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
            // Connection factory
            factory = builder.build(is);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static SqlSessionFactory getFactory(){
        return factory;
    }

    private static SqlSession getSqlSession(boolean isAutoCommit){
        SqlSession sqlSession = local.get();
        if(sqlSession == null){
            // When SqlSessionFactory calls the openSession method to obtain the sqSession object, you can set whether the transaction is automatically committed through parameters
            // The default value is false. Transactions are not automatically committed. If true, transactions are automatically committed
            sqlSession = factory.openSession(isAutoCommit);
            local.set(sqlSession);
        }
        return sqlSession;
    }
    // Manual transaction commit
    public static SqlSession getSqlSession(){
        return getSqlSession(false);
    }

    // Automatic transaction commit
    public static <T extends Object>T getMapper(Class<T> c){
        SqlSession sqlSession = getSqlSession(true);
        return sqlSession.getMapper(c);
    }
}

059

11.3 one to one Association

11.3.1 create table

-- User account table
CREATE TABLE `user` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'user id',
  `user_name` varchar(20) COLLATE utf8_bin NOT NULL COMMENT 'user name',
  `user_pwd` varchar(20) COLLATE utf8_bin NOT NULL COMMENT 'User password',
  `user_realname` varchar(20) COLLATE utf8_bin NOT NULL COMMENT 'User name',
  `user_img` varchar(100) COLLATE utf8_bin NOT NULL COMMENT 'User image',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
-- User information details
CREATE TABLE `user_info` (
`detail_id`  int NOT NULL AUTO_INCREMENT COMMENT 'User information id' ,
`user_addr`  varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'User address' ,
`user_tel`  char(11) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'Subscriber telephone' ,
`user_desc`  varchar(200) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'User description' ,
`user_id`  int NOT NULL UNIQUE  COMMENT 'user id' ,
PRIMARY KEY (`detail_id`)
)
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_bin;

11.3.2 create entity class

com.zhang.pojo.User.java
com.zhang.pojo.UserInfo.java

@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class User {
    private int userId;
    private String userName;
    private String userPwd;
    private String userRealname;
    private String userImg;

}

@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class UserInfo {
   
    private int detailId;
    private String userAddr;
    private String userTel;
    private String userDesc;
    private int userId;
}

11.3.3 creating DAO

public interface UserDao {
    public int insertUser(User user);
    public User queryUser(String userName);
}

060

    @Test
    public void testInsertUser() {

        User user = new User(0,"lisi","123123","Wang Wu","01.jpg",null);
        UserInfo userInfo = new UserInfo(0,"Xi'an, Shaanxi","18710056688","You can grow only with personality",0);
        SqlSession sqlSession = MyBatisUtil.getSqlSession();
        try {
            UserDao userDao = sqlSession.getMapper(UserDao.class);
            int i = userDao.insertUser(user);
            System.out.println(i);
            userInfo.setUserId(user.getUserId());
            UserInfoDao userInfoDao = sqlSession.getMapper(UserInfoDao.class);
            int j = userInfoDao.insertUserInfo(userInfo);
            System.out.println(j);
            sqlSession.commit();
        }catch (Exception e){
            e.printStackTrace();
            sqlSession.rollback();
        }
    }

061

11.3.4 Association query

When querying the user, the corresponding details are associated and queried

• entity

@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class User {
    private int userId;
    private String userName;
    private String userPwd;
    private String userRealname;
    private String userImg;
    private UserInfo userInfo;  // Use userinfo as the attribute of user
}

• Mapping file

<resultMap id="userMap" type="User">
        <id column="user_id" property="userId"/>
        <result column="user_name" property="userName"/>
        <result column="user_pwd" property="userPwd"/>
        <result column="user_realname" property="userRealname"/>
        <result column="user_img" property="userImg"/>
        <result column="detail_id" property="userInfo.detailId"/>
        <result column="user_addr" property="userInfo.userAddr"/>
        <result column="user_desc" property="userInfo.userDesc"/>
        <result column="user_tel" property="userInfo.userTel"/>

    </resultMap>

    <select id="queryUser" resultMap="userMap">
        select a1.user_id,a1.user_name,a1.user_pwd,a1.user_realname,a1.user_img
             ,a2.detail_id,a2.user_addr,a2.user_desc,a2.user_tel
        from user a1 left join user_info a2 on a1.user_id = a2.user_id
        where a1.user_name = #{userName}
    </select>

11.4 one to many

Case: class to student

11.4.1 create data table

CREATE TABLE `classes` (
  `cid` int(11) NOT NULL AUTO_INCREMENT COMMENT 'class id',
  `cname` varchar(30) COLLATE utf8_bin NOT NULL COMMENT 'Class name',
  `cdesc` varchar(100) COLLATE utf8_bin NOT NULL COMMENT 'Class description', 
  PRIMARY KEY (`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE `students` (
  `sid` int(11) NOT NULL AUTO_INCREMENT COMMENT 'student id',
  `sname` varchar(30) COLLATE utf8_bin NOT NULL COMMENT 'Student name',
  `sage` varchar(100) COLLATE utf8_bin NOT NULL COMMENT 'Student age', 
  `scid` int(11) NOT NULL COMMENT 'Class to which the student belongs id', 
  PRIMARY KEY (`sid`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

11.4.2 create entity class

@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Classes {
    private int cId;
    private String cName;
    private String cDesc;
}

@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Students {
    private int sId;
    private String sName;
    private int sAge;
    private int sCid;
}

11.4.3 Association query

Query all students in a class by association

Transform classes entity class

@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Classes {
    private int cId;
    private String cName;
    private String cDesc;
    private List<Students> stus; //Store all student information under the class
}

• Connection query mapper
  Add alias configuration in mybatis-config.xml

 <typeAlias type="com.zhang.pojo.Classes" alias="Classes"></typeAlias>
        <typeAlias type="com.zhang.pojo.Students" alias="Students"></typeAlias>

Add mapping file configuration

<mapper resource="mappers/ClassMapper.xml"></mapper>

Perfecting query logic in ClassMapper.xml

	<resultMap id="classMap" type="Classes">
		<id column="cid" property="cId"/>
		<result column="cname" property="cName"/>
		<result column="cdesc" property="cDesc"/>
		<!-- classes Object stus Property is a List Collection, need to use collection label-->
		<!-- collection Tagged ofType Property declares the type of the element in the collection-->
		<collection property="stus" ofType="Students">
			<result column="sid" property="sId"/>
			<result column="sname" property="sName"/>
			<result column="sage" property="sAge"/>
		</collection>
	</resultMap>
	<select id="queryClass" resultMap="classMap">
		select a1.cid,a1.cname,a1.cdesc,a2.sid,a2.sname,a2.sage
		from classes a1 left join students a2 on a1.cid = a2.scid
		where a1.cid = #{cId}
	</select>

• Test class

public class ClassDaoTest {

    @Test
    public void testQueryClass() {
        ClassDao classDao = MyBatisUtil.getMapper(ClassDao.class);
        Classes classes = classDao.queryClass(1);
        System.out.println(classes);
    }
}Insert the code slice here

• Subquery
  063

11.5 many to one Association

When querying student information, query the class information to which the student belongs

11.5.1 entity information modification

The class attribute of the student is an entity class

@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Students {
    private int sId;
    private String sName;
    private int sAge;
    private Classes classes; // The class attribute of the student is an entity class
}

You need to comment out the associated student entity class information in the class class

// private List<Students> stus; // Store all student information under the class

11.5.2 Association query

• mybatis-config.xml
  Ensure that the < typealias < mapper is configured correctly as follows

        <typeAlias type="com.zhang.pojo.Classes" alias="Classes"></typeAlias>
        <typeAlias type="com.zhang.pojo.Students" alias="Students"></typeAlias>
..................................
        <mapper resource="mappers/StudentMapper.xml"></mapper>

• mapper

<resultMap id="StudentMap" type="Students">
		<id column="sid" property="sId"/>
		<result column="sname" property="sName"/>
		<result column="sage" property="sAge"/>
		<result column="cid" property="classes.cId"/>
		<result column="cname" property="classes.cName"/>
		<result column="cdesc" property="classes.cDesc"/>
	</resultMap>
	<select id="queryStudentById" resultMap="StudentMap">
		select a1.sid,a1.sname,a1.sage,a1.scid,a2.cid,a2.cname,a2.cdesc
		from  students a1 left join classes a2 on a1.scid = a2.cid
		where a1.sid = #{sId}
	</select>

• test class

    @Test
    public void queryStudentById() {
        StudentDao studentDao = MyBatisUtil.getMapper(StudentDao.class);
        Students students = studentDao.queryStudentById(1001);
        System.out.println(students);
    }

064

11.6 many to many Association

Example: the relationship between students and courses

11.6.1 create data table

CREATE TABLE `courses` (
  `course_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'curriculum id',
  `course_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT 'Course name',
  PRIMARY KEY (`course_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Course information sheet';

CREATE TABLE `grades` (
  `sid` int(11) NOT NULL COMMENT 'student id',
  `course_id` int(11) NOT NULL COMMENT 'curriculum id',
  `score` int(11) NOT NULL COMMENT 'achievement'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

11.6.2 create entity class

Omit part of the content and wait for practice.

12, Dynamic SQL

Screen your favorite object, gender, age, city,
For e-commerce websites, when screening goods, the screening conditions can be more or less.

12.1 what is dynamic sql

Dynamically complete SQL splicing according to query conditions.

12.2 dynamic sql cases

Copy the project and rename the source file after copying
Keep only the pom.xml and src folders.
Change mybatis-demo3 in the pom file to the desired name
Open the newly copied project with IDEA

12.2.1 create entity table

CREATE TABLE `members` (
  `member_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'member id',
  `member_nick` varchar(30) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT 'What's the scale',
  `member_gender` char(2) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT 'Gender',
  `member_age` int(11) NOT NULL  COMMENT 'Age',
  `member_city` varchar(30) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT 'city',
  PRIMARY KEY (`member_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Member information form';

12.2.2 create entity class

@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Member {
    private int memberId;
    private String memeberNick;
    private String memeberGender;
    private int memberAge;
    private String memberCity;
}

066

12.2.3 creating DAO interface

Create an interface for multi condition query in DAO interface

public interface MemberDAO {
    public Member queryMemberByNick(String nick);

    // In multi condition query, if the query conditions are uncertain, you can directly use HashMap as a parameter
    // Advantages: HashMap does not need to define a class to pass query criteria separately
    public List<Member> searchMember(HashMap<String,Object> params);

    // You can also define a special entity class for query criteria to store query parameters
    // Disadvantages: you need to define an entity class separately to encapsulate parameters
    public List<Member> searchMember(MemberSearchConfition params);
}

-67

12.3 if tag usage

    <resultMap id="memberMap" type="Member">
        <id column="member_id" property="memberId"/>
        <result column="member_nick" property="memberNick"/>
        <result column="member_gender" property="memberGender"/>
        <result column="member_age" property="memberAge"/>
        <result column="member_city" property="memberCity"/>
    </resultMap>

    <select id="searchMember" resultMap="memberMap">
        Select a1.member_id,a1.member_nick,a1.member_gender,a1.member_age,a1.member_city
        from members a1
        <trim prefix="where" prefixOverrides="and | or" suffix="order by member_age">
            <if test="gender != null">
                and member_gender = #{gender}
            </if>
            <if test="minAge != null">
                and member_age &gt;= #{minAge}
            </if>
            <if test="maxAge != null">
                and member_age &lt;= #{maxAge}
            </if>
            <if test="city != null">
                and member_city = #{city}
            </if>
        </trim>
    </select>

• In dynamic sql

12.4 where label

<where></where> -- The label automatically sets the first dynamic condition and Eliminate

• The trim tag is used as follows, and the first and or will be removed automatically

<trim prefix="where" prefixOverrides="and | or" suffix="order by member_age">

12.5 foreach label

test class

    @Test
    public void searchMemberByCity(){
        ArrayList<String> cities = new ArrayList<>();
        cities.add("Wuhan");
        cities.add("Xi'an");
        MemberDAO memberDAO = MyBatisUtil.getMapper(MemberDAO.class);
        List<Member> members = memberDAO.searchMemberByCity(cities);
        for (Member m:members){
            System.out.println(m);
        }
    }

mapper implementation

    <select id="searchMemberByCity" resultMap="memberMap">
        Select a1.member_id,a1.member_nick,a1.member_gender,a1.member_age,a1.member_city
        from members a1
        where member_city in
            <foreach collection="list" item="cityName" separator="," open="(" close=")">
                #{cityName}
            </foreach>
    </select>

== 069==

Thirteen. Fuzzy query $

Query member information by nickname

    // Query user information according to the name -- fuzzy query
    // The ${key} value is required for fuzzy query, which is spliced with sql
    // When using ${key}, even if there is only one parameter, you need to declare the parameter key with the @ Param annotation (you can not declare it when it is not a String object parameter)
    public List<Member> searchMemberByNick(@Param("keyword") String keyword);

13.1 through HashMap

    @Test
    public void searchMemberByNick(){
        MemberDAO memberDAO = MyBatisUtil.getMapper(MemberDAO.class);
        HashMap<String,Object> params = new HashMap<String,Object>();
        params.put("keyword","flower");
        List<Member> members = memberDAO.searchMemberByNick(params);
        for (Member m:members){
            System.out.println(m);
        }
    }

    <!-- ${key} Indicates to get the parameter. First get the value of the parameter and splice it to sql Statement, and then compile and execute SQL Statement that may cause SQL Injection problem-->
    <!-- #{key} means to obtain parameters. First complete the SQL compilation (precompiling), and then set the obtained parameters to the SQL statement after precompiling, so as to avoid the SQL injection problem -- >
    <select id="searchMemberByNick" resultMap="memberMap">
        Select a1.member_id,a1.member_nick,a1.member_gender,a1.member_age,a1.member_city
        from members a1
        where member_nick like "%${keyword}%"
    </select>

13.2 through String

    @Test
    public void searchMemberByNick(){
        MemberDAO memberDAO = MyBatisUtil.getMapper(MemberDAO.class);
        List<Member> members = memberDAO.searchMemberByNick("flower");
        for (Member m:members){
            System.out.println(m);
        }
    }

Special note: parameterType="java.lang.String" should be added

    <select id="searchMemberByNick" parameterType="java.lang.String" resultMap="memberMap">
        Select a1.member_id,a1.member_nick,a1.member_gender,a1.member_age,a1.member_city
        from members a1
        where member_nick like "%${keyword}%"
    </select>

== 070==

14, MyBatis log configuration

MyBatis is an encapsulated ORM framework, and its running process cannot be tracked. In order to let developers understand the execution process of MyBatis and the work completed in each execution step, MyBatis framework itself supports log4j log framework to track and record the running process. We only need to configure the relevant logs of MyBatis to see the log information during the operation of MyBatis.

14.1 add log framework dependency

<!-- https://mvnrepository.com/artifact/log4j/log4j -->
<dependency>
    <groupId>log4j</groupId>
    <artifactId>log4j</artifactId>
    <version>1.2.17</version>
</dependency>

14.2 add log profile

• Create a file named log4j.properties in the resources directory
• Log output mode

# Global logging configuration
# Declare the output level and output mode of the log. stdout indicates input to the console
log4j.rootLogger=DEBUG, stdout
# Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
# Define the print format of the log.% t represents the thread name and% 5p represents the output log level
log4j.appender.stdout.layout.ConversionPattern=[%t] %5p  - %msg \:%n%m

14.3 level of log information

level	explain
DEBUG	Output debugging information
INFO	Output prompt information
WARN	Output warning information
ERROR	General error message
FATAL	Fatal error message

071

15, Configure database connection pool - consolidate Druid

As an ORM framework, MyBatis needs to connect with data during database operations. Frequent creation and destruction of database connections will consume certain resources.
When configuring the MyBatis data source, you can use the built-in connection pool of MyBatis to manage connections as long as the type attribute of the datasource tag is configured as POOLED.
If we want to use a third-party database connection, we need to make a custom configuration.

15.1 common connection pools

• DBCP
• C3P0 is simple to use and has low efficiency
• Druid is relatively efficient. Based on configuration, it is widely used in enterprises and provides a more convenient monitoring system.
• Hikari is translated from Japanese and has the highest performance,

15.2 add druid dependency

<!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.2.8</version>
</dependency>

15.3 create druid connection pool factory

public class DruidDateSourceFactory extends PooledDataSourceFactory {

    public DruidDateSourceFactory() {
        this.dataSource = new DruidDataSource();
    }
}

15.4 configure DruidDateSourceFactory to MyBatis data source

            <environment id="mysql">
			<transactionManager type="JDBC"></transactionManager>
            <!-- <dataSource Used to configure database connection information-->
            <!-- POOLED use MyBatis Built in connection pool-->
            <!-- MyBatis You need a connection pool factory that can generate database connection pools PooledDataSourceFactory -->
            <dataSource type="com.zhang.utils.DruidDateSourceFactory">
                <property name="driverClass" value="${mysql_driver}"/>
                <property name="jdbcUrl" value="${mysql_url}"/>
                <property name="username" value="${mysql_username}"/>
                <property name="password" value="${mysql_password}"/>
            </dataSource>
        </environment>

16, MyBatis cache

MyBatis is a JDBC based package, which makes database operation more convenient. MyBatis not only encapsulates JDBC operation steps, but also optimizes its performance.

• The cache mechanism is introduced into MyBatis to improve the retrieval efficiency of MyBatis.
• The delayed loading mechanism is introduced into MyBatis to reduce unnecessary access to the database.

16.1 how cache works

Cache is actually the memory that stores data.

16.2 MyBatis cache is divided into L1 cache and L2 cache

16.2.1 L1 cache

The first level cache is also called SqlSession level cache. The cache memory allocated separately for each SqlSession can be used directly without manual account opening. The caches of multiple sqlsessions are not shared.
characteristic
1. If multiple queries use the same SqlSesson object, the data will be stored in the cache after the first query, and subsequent queries will directly access the data in the cache.
2. If the object is modified after the first query, the modification will affect the cache. For the second query, the query is the data in the cache, but the data has not been persisted to the database. In this way, the results of the first query are inconsistent with those of the second query and the actual values in the database.
3. When we query again, if we want to pass the cache, we can clear the cache through sqlSession.clearCache().
4. If the current sqlsession is used to modify after the first query and before the second query, this modification will invalidate the data queried and cached for the first time. Therefore, the second query will access the database again and query the data again.

     @Test
    public void searchMemberById(){
         SqlSession sqlSession = MyBatisUtil.getSqlSession();
         MemberDAO memberDAO = sqlSession.getMapper(MemberDAO.class);
         Member members1 = memberDAO.searchMemberById(1);
         System.out.println(members1);
         members1.setMemberAge(99);
         //sqlSession.clearCache();
         System.out.println("+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++");
         Member members2 = memberDAO.searchMemberById(1);
         System.out.println(members2);
     }

– the data is modified before the second query

     @Test
    public void searchMemberById(){
         SqlSession sqlSession = MyBatisUtil.getSqlSession();
         MemberDAO memberDAO = sqlSession.getMapper(MemberDAO.class);
         Member members1 = memberDAO.searchMemberById(1);
         System.out.println(members1);
         members1.setMemberAge(99);
         //sqlSession.clearCache();
         memberDAO.updateMemberById(1,99);
         sqlSession.commit();  //After commit(), the cache will fail, and the database will be re queried the second time
         System.out.println("+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++");
         Member members2 = memberDAO.searchMemberById(1);
         System.out.println(members2);
     }

074

16.2.2 inconsistency between secondary query and database data

Question:
After the first query, the database data has been modified, but the data before modification is still displayed during the second query.

analysis:
The modification operation and query operation are not the same thread, so different pairs are used (using different sqlsession s). Therefore, the modification operation will not invalidate the cache of query operation. During the second query, the cache is still accessed instead of the database.

Solution:
1. Let the modify operation and query operation use the same sqlsession. (unreasonable)
2. Let you empty the cache after each query operation. Let's check the database directly when querying again.
75
MyBatis cache has some limitations and can take effect, but we generally don't use it.
Concurrent access, one thread, multithreading, leads to the problem of resource competition. Each thread uses its own session.
L2 cache,

16.2.3 L2 cache

The second level cache is also called SqlSessionFactory level cache. Sqlsessions obtained through the same factory object can share the second level cache. In the application server, SqlSessionFactory itself is a singleton, so the L2 cache can be shared globally.
characteristic:
1. The L2 cache is not enabled by default and needs to be enabled in the setting tag in mybatis-config.xml.
2. The L2 cache can only cache objects that implement the serialization interface
3,

• Open in the setting tag in mybatis-config.xml.

<settings>
        <setting name="cacheEnabled" value="true"/>
    </settings>

• Configure the cache tag in the Mapper file that needs to use L2 cache.

<cache/> 

• The cached entity class implements the serialization interface.

public class Member implements Serializable {
    private int memberId;
    private String memberNick;
    private String memberGender;
    private int memberAge;
    private String memberCity;
}

• Test L2 cache

     @Test
    public void searchMemberById(){
         SqlSessionFactory factory = MyBatisUtil.getSqlSessionFactory();
         // Multiple sqlSession objects must come from the same sqlSessionFactory
         SqlSession sqlSession1 = factory.openSession(true);
         SqlSession sqlSession2 = factory.openSession(true);
         MemberDAO memberDAO1 = sqlSession1.getMapper(MemberDAO.class);
         Member members1 = memberDAO1.searchMemberById(1);
         System.out.println(members1);
         sqlSession1.commit(); //Executing commit after the first query will cache the current query results to the L2 cache.

         System.out.println("+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++");

         MemberDAO memberDAO2 = sqlSession1.getMapper(MemberDAO.class);
         Member members2 = memberDAO2.searchMemberById(1);
         System.out.println(members2);
     }

16.3 cache label of query operation

Only the select tag can be used.
useCache = "false" force the current operation not to use cache information. If true, force the use of cache.

    <select id="searchMemberById" resultMap="memberMap" useCache="false">
        Select a1.member_id,a1.member_nick,a1.member_gender,a1.member_age,a1.member_city
        from members a1
        where member_id =  #{mid}
    </select>

76

17, Delayed loading mechanism (only effective in subquery)

Delayed loading -- if delayed loading is enabled in MyBatis, only the first query will be executed by default when the sub query is executed (at least twice). When the query result of the sub query is used, the execution of the sub query will be triggered; If you do not need to use the subquery results, the subquery will not be executed.

– fetchType = "lazy" delay loading label

<resultMap id="classMap" type="Classes">
        <id column="cid" property="cId"/>
        <result column="cname" property="cName"/>
        <result column="cdesc" property="cDesc"/>
        <collection property="stus" select="com.zhang.dao.StudentDAO.queryStudentsByCid" column="cid" fetchType="lazy"/>
    </resultMap>
    <select id="queryClassById" resultMap="classMap" >
        select cid,cname,cdesc
        from classes
        where cid = #{cid}
    </select>