Mybatis madness lecture notes (incomplete)

1. Introduction

1.1 what is Mybatis

MyBatis is an excellent persistence layer framework
It supports custom SQL, stored procedures, and advanced mapping.
MyBatis eliminates almost all JDBC code and the work of setting parameters and obtaining result sets.
MyBatis can configure and map primitive types, interfaces and Java POJO s (Plain Old Java Objects) to records in the database through simple XML or annotations.
MyBatis was originally a part of apache Open source project iBatis, 2010 project Migrated from apache software foundation to [Google Code]( https://baike.baidu.com/item/google Code / 2346604) and renamed MyBatis.
Moved to in November 2013 Github.

advantage:

Easy to learn
flexible
Decouple sql from program code
The separation of sql and code improves maintainability.
Provide mapping labels to support the mapping between objects and orm fields in the database
Provide object relationship mapping labels to support the establishment and maintenance of object relationships
Provide xml tags to support writing dynamic sql.

How to get Mybatis:

maven warehouse:

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

Github:https://github.com/mybatis/mybatis-3
Chinese documents: https://mybatis.org/mybatis-3/

1.2 persistence

Data persistence

Persistence is the process of transforming program data in persistent state and transient state
Memory: loss upon power failure
Database (JDBC), io file persistence.

1.3 durable layer

Dao layer, Service layer, Controller layer

Code block that completes the persistence work
The layer boundary is very obvious.

2. The first Mybatis program

2.1 construction environment

Build database

CREATE DATABASE `mybatis`;

USE `mybatis`;

CREATE TABLE `user`(
  `id` INT(20) NOT NULL PRIMARY KEY,
  `name` VARCHAR(30) DEFAULT NULL,
  `pwd` VARCHAR(30) DEFAULT NULL
)ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO `user`(`id`,`name`,`pwd`) VALUES(1,'WangTwo ','123456'),(2,'Zhang San','1234567'),
(3,'Li Si','12345678')

Import dependency

 <!--Import dependency-->
    <dependencies>
       <!--mysql drive-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.13</version>
        </dependency>
       <!--Mybatis-->
        <!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.8</version>
        </dependency>
       <!--Junit-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.13.1</version>
            <scope>test</scope>
        </dependency>
    </dependencies>

Write Mybatis tool class

public class MybatisUtils {

    private static SqlSessionFactory sqlSessionFactory;

    static {
        try {
            //Get SqlSessionFactory object
            String resource = "mybatis-config.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static SqlSession getSqlSession(){
        return sqlSessionFactory.openSession();
    }
}

2.2 coding

Entity class

public class User {
    private int id;
    private String name;
    private String pwd;

    public User() {
    }

    public User(int id, String name, String pwd) {
        this.id = id;
        this.name = name;
        this.pwd = pwd;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getPwd() {
        return pwd;
    }

    public void setPwd(String pwd) {
        this.pwd = pwd;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", pwd='" + pwd + '\'' +
                '}';
    }
}

Dao interface

public interface UserDao {
    List<User> getUserList();
}

Convert mapopper's configuration file to an interface implemented by DAPER

<?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 corresponding Dao/Mapper Interface-->
<mapper namespace="com.lyd.dao.UserDao">
<!--select Query statement-->
    <select id="getUserList" resultType="com.lyd.pojo.User">
        select * from mybatis.user
    </select>
</mapper>

test

Note:

org.apache.ibatis.binding.BindingException: Type interface com.kuang.dao.UserDao is not known to the MapperRegistry.

What is MapperRegistry?

  ```xml
  <mappers>
          <mapper resource="com/lyd/dao/UserMapper.xml"/>
      </mappers>
  ```

junit test

public class UserDaoTest {

    @Test
    public void test(){

        //1. Get SqlSession object
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        //2. Execute SQL
        // Method 1: getMapper
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        List<User> userList = userDao.getUserList();

        //Mode 2:
//        List<User> userList = sqlSession.selectList("com.lyd.dao.UserDao.getUserList");

        for (User user : userList) {
            System.out.println(user);
        }

        //Close sqlSession
        sqlSession.close();
    }

}

Possible problems:

The profile is not registered
Binding interface error
. wrong method name
Wrong return type
Maven export resource problem

3.CRUD

1. namespace

The package name in the namespace should be consistent with the package name of Dao/mapper interface

2. select

Select, query statement:

id: is the method name in the corresponding namespace
resultType: return value of sql statement execution
parameterType: parameter type

Write interface

 //Query user by id
   User getUserById(int id);

Write the sql statement in the corresponding mapper

<select id="getUserById" parameterType="int" resultType="com.lyd.pojo.User">
        select * from  mybatis.user where id = #{id}
    </select>

test

 @Test
    public void getUserById(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();

        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        User user = mapper.getUserById(1);
        System.out.println(user);

        sqlSession.close();
    }

    //Add, delete and modify transactions that need to be submitted
    @Test
    public void addUser(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();

        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        int res = mapper.addUser(new User(4, "Xiaofei", "123456"));
        if (res>0){
            System.out.println("Insert successful!");
        }

        //Commit transaction
        sqlSession.commit();
        sqlSession.close();

    }

    @Test
    public void updateUser(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();

        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        int res = mapper.updateUser(new User(4, "Xiao Gang", "123123"));
        if (res>0){
            System.out.println("Modified successfully!");
        }
        sqlSession.commit();
        sqlSession.close();
    }

    @Test
    public void deleteUser(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();

        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        int res = mapper.deleteUser(4);
        if (res>0){
            System.out.println("Deleted successfully!");
        }

        sqlSession.commit();
        sqlSession.close();
    }

3. insert

<!--Insert statement-->
    <insert id="addUser" parameterType="com.lyd.pojo.User">
        insert into mybatis.user(id,name ,pwd) values (#{id},#{name},#{pwd});
    </insert>

4. update

<!--Modify user-->
    <update id="updateUser" parameterType="com.lyd.pojo.User">
        update mybatis.user set name = #{name},pwd = #{pwd} where id = #{id};
    </update>

5. delete

<!--delete user-->
    <delete id="deleteUser" parameterType="int">
        delete from mybatis.user where id = #{id};
    </delete>

6. Universal Map

Assuming that there are too many tables, fields or parameters in the entity class or database, we should consider using Map

//Universal Map
   int addUser2(Map<String,Object> map);

<!--transmit map of key value-->
   <insert id="addUser2" parameterType="map">
       insert into mybatis.user (id,name,pwd) values (#{userId},#{userName},#{passWord})
   </insert>

@Test
    public void addUser2(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();

        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        HashMap<String, Object> map = new HashMap<String, Object>();

        map.put("userId",5);
        map.put("userName","Xiao Zhang");
        map.put("passWord","232323");

        mapper.addUser2(map);

        sqlSession.commit();
        sqlSession.close();
    }

Map passes parameters and directly takes the key from sql [parameterType = "map"]

Pass parameters to the Object and directly get the attribute of the Object in sql [parameterType = "Object"]

If there is only one basic type parameter, you can get it directly in sql

Map or annotation is used for multiple parameters

4. Configuration analysis

1. Core profile

mybatis-config.xml
Mybatis's configuration file contains settings and properties that deeply affect mybatis's behavior

configuration
- properties
- settings
- typeAliases
- typeHandlers
- objectFactory (object factory)
- plugins
- environments
  - Environment (environment variable)
    - Transaction manager
    - dataSource
- databaseIdProvider (database vendor ID)
- mappers

2. environments

MyBatis can be configured to adapt to a variety of environments

However, remember that although multiple environments can be configured, only one environment can be selected for each SqlSessionFactory instance.

The default transaction manager of Mybatis is JDBC, and the connection pool is POOLED

3. properties

You can reference the configuration file through the properties property

These properties can be configured externally and can be replaced dynamically. You can configure these properties in a typical Java property file or set [db.properties] in the child element of the properties element

Write a configuration file

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/mybatis?userSSL=true&useUnicode=true&characterEncoding=UTF-8serverTimezone=UTC
username=root
password=root

Introduce in core configuration file

<!--Import external profile-->
    <properties resource="db.properties">
        <property name="username" value="root"/>
        <property name="password" value="root"/>
    </properties>

You can import external files directly
You can add some attribute configurations
If two files have the same field, the external configuration file is preferred

4. Type aliases

Type alias sets an abbreviated name for a Java type
The meaning of existence is only to reduce the redundancy of class fully qualified names

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

You can also specify a package name. MyBatis will search for the required Java beans under the package name

Scan the package of an entity class, and its default alias is the class name of this class, with the first letter in lowercase

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

The first type is rarely used for entity classes. If there are many, the second type is used.

The first one can customize the alias, while the second one cannot. If you have to change it, you need to annotate the entity class

@Alias("user")
public class User {

5. mappers

MapperRegistry: register and bind our Mapper file

Method 1: (recommended)

<mappers>
        <mapper resource="com/lyd/dao/UserMapper.xml"/>
    </mappers>

Method 2: register with class file binding

<mappers>
        <mapper class="com.lyd.dao.UserMapper"/>
    </mappers>

Note:

Interface and its Mapper configuration file must have the same name
The interface and its Mapper configuration file must be in the same package

Method 3: use scanning package for injection binding

6. Life cycle and scope

Scope and lifecycle categories are critical, because incorrect use can lead to very serious concurrency problems.

SqlSessionFactoryBuilder:

Once SqlSessionFactory is created, it is no longer needed
local variable

SqlSessionFactory:

It can be imagined as: database connection pool
Once SqlSessionFactory is created, it should always exist during the operation of the application. There is no reason to discard it or recreate another instance.
Therefore, the best scope of SqlSessionFactory is the application scope.
The simplest is to use singleton mode or static singleton mode.

SqlSession:

A request to connect to the connection pool
The instance of SqlSession is not thread safe, so it cannot be shared, so its best scope is the request or method scope.
It needs to be closed after use, otherwise it will occupy resources

5. Solve the problem of inconsistency between attribute name and field name

resolvent:

Alias

<select id="getUserById" resultType="com.lyd.pojo.User">
        select id,name,pwd as password from  mybatis.user where id = #{id}
    </select>

resultMap (result set mapping)

<!--Result set mapping-->
    <resultMap id="UserMap" type="User">
<!--column Fields in the database, property Properties in entity classes-->
        <result column="id" property="id"/>
        <result column="name" property="name"/>
        <result column="pwd" property="password"/>
    </resultMap>
    <select id="getUserById" resultMap="UserMap">
        select * from  mybatis.user where id = #{id}
    </select>

6. Log

6.1 log factory

<settings>
<!--Standard log factory implementation-->
        <setting name="logImpl" value="STDOUT_LOGGING"/>
    </settings>

6.2Log4j

What is Log4j

Log4j yes Apache By using Log4j, we can control the destination of log information transmission Console , documents GUI Components.
You can control the output format of each log.
By defining the level of each log information, we can control the log generation process in more detail.
Through a configuration file To flexibly configure without modifying the application code.

Import the package of log4j first

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

configuration file

#Output the log information with the level of DEBUG to the two destinations of console and file. The definitions of console and file are in the following code
log4j.rootLogger=DEBUG,console,file

#Relevant settings of console output
log4j.appender.console = org.apache.log4j.ConsoleAppender
log4j.appender.console.Target = System.out
log4j.appender.console.Threshold=DEBUG
log4j.appender.console.layout = org.apache.log4j.PatternLayout
log4j.appender.console.layout.ConversionPattern=[%c]-%m%n

#Relevant settings for file output
log4j.appender.file = org.apache.log4j.RollingFileAppender
log4j.appender.file.File=./log/kuang.log
log4j.appender.file.MaxFileSize=10mb
log4j.appender.file.Threshold=DEBUG
log4j.appender.file.layout=org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern=[%p][%d{yy-MM-dd}][%c]%m%n

#Log output level
log4j.logger.org.mybatis=DEBUG
log4j.logger.java.sql=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.ResultSet=DEBUG
log4j.logger.java.sql.PreparedStatement=DEBUG

Configure log4j as the implementation of log

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

Use of Log4j

static Logger logger = Logger.getLogger(UserMapperTest.class);

    @Test
    public void getUserLike(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();

        logger.info("Test, enter getUserLike Method success!");

        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        User user = mapper.getUserById(1);
        System.out.println(user);

        sqlSession.close();
    }

    @Test
    public void testLog4j(){
        logger.info("info:Entered testLog4j");
        logger.debug("debug:Entered testLog4j");
        logger.error("error:Entered testLog4j");
    }

7. Pagination

7.1 using limit paging

effect:

Reduce data processing

Paging with Limit

Syntax: select * from user limit startIndex,pageSize;
use: select * from user limit 0,2;

Mybatis paging implementation

Interface

//paging
   List<User> getUserByLimit(Map<String,Integer> map);

Mapper.xml

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

test

@Test
    public void getUserByLimit(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        HashMap<String, Integer> map = new HashMap<String, Integer>();
        map.put("startIndex",0);
        map.put("pageSize",2);

        List<User> userList = mapper.getUserByLimit(map);
        for (User user : userList) {
            System.out.println(userList);
        }

        sqlSession.close();

7.2 rowboundaries paging

Paging is no longer implemented using SQL

Interface

//Pagination 2
   List<User> getUserByRowBounds();

mapper.xml

 <!--Pagination 2-->
    <select id="getUserByRowBounds" resultMap="UserMap">
        select * from user 
    </select>

test

@Test
    public void getUserByRowBounds(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        //RowBounds implementation
        RowBounds rowBounds = new RowBounds(1, 2);

        //Paging through java code level
        List<User> userlist = sqlSession.selectList("com.lyd.dao.UserMapper.getUserByRowBounds", null, rowBounds);

        for (User user : userlist) {
            System.out.println(user);
        }

        sqlSession.close();
    }

8. Development using annotations

8.1 interface oriented programming

Everyone has studied object-oriented programming and interface before, but in real development, we often choose interface oriented programming
Root cause: decoupling, expandable, improved reuse. In layered development, the upper layer doesn't care about the specific implementation. Everyone abides by common standards, making the development easier and more standardized
In an object-oriented system, various functions of the system are completed by many different objects. In this case, how each object implements itself is not so important for system designers;
The collaborative relationship between various objects has become the key of system design. From the communication between different classes to the interaction between modules, we should focus on it at the beginning of system design, which is also the main work of system design. Interface oriented programming refers to programming according to this idea.

Understanding of interface

From a deeper understanding, the interface should be the separation of definition (specification, constraint) and Implementation (the principle of separation of name and reality).
The interface itself reflects the system designer's abstract understanding of the system.
There shall be two types of interfaces:
- The first type is the abstraction of an individual, which can correspond to an abstract class;
- The second is the abstraction of an aspect of an individual, that is, the formation of an abstract interface;
An individual may have multiple Abstract faces. Abstract body and abstract surface are different.

Three oriented differences

Object oriented means that when we consider a problem, we take the object as the unit and consider its attributes and methods
Process oriented means that when we consider a problem, we consider its implementation in a specific process (transaction process)
Interface design and non interface design are aimed at reuse technology, and object-oriented (process) is not a problem It is more reflected in the overall architecture of the system

8.2 development using annotations

Annotation is implemented on the interface

@Select("select * from user")
   List<User> getUsers();

Configuration interface

<!--Binding interface-->
    <mappers>
        <mapper class="com.lyd.dao.UserMapper"/>
    </mappers>

test

@Test
    public void test(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<User> users = mapper.getUsers();
        for (User user : users) {
            System.out.println(user);
        }
        sqlSession.close();
    }

8.3 CRUD

Implement automatic transaction submission when the tool class is created

public static SqlSession getSqlSession(){
        return sqlSessionFactory.openSession(true);
    }

Write the interface and add comments

public interface UserMapper {

   //query
   @Select("select * from user")
   List<User> getUsers();

   @Select("select * from user where id = #{id}")
   User getUserById(@Param("id") int id);

   //add to
   @Insert("insert into user(id,name,pwd) values(#{id},#{name},#{password})")
   int addUser(User user);

   //modify
   @Update("update user set name = #{name},pwd = #{password} where id = #{id}")
   int updateUser(User user);

   //delete
   @Delete("delete from user where id = #{id}")
   int deleteUser(@Param("id") int id);

}

test

@Test
    public void test(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<User> users = mapper.getUsers();
        for (User user : users) {
            System.out.println(user);
        }
        sqlSession.close();
    }

    @Test
    public void getUserById(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        User userById = mapper.getUserById(1);
        System.out.println(userById);

        sqlSession.close();
    }

    @Test
    public void addUser(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        mapper.addUser(new User(6,"complete","123123"));

        sqlSession.close();
    }

    @Test
    public void updateUser(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        mapper.updateUser(new User(6,"Wang Wu","1234567"));

        sqlSession.close();
    }

    @Test
    public void deleteUser(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        mapper.deleteUser(6);

        sqlSession.close();
    }

9. Lombok

Notes:

@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

explain:

@Data:Nonparametric structure, get，set，toString，hashcode，equals
@AllArgsConstructor
@NoArgsConstructor
@EqualsAndHashCode
@ToString
@Getter

10. Many to one processing

Many to one understanding:

Multiple students correspond to one teacher
For students, it is a many to one phenomenon, that is, a teacher is associated with students

Database design

SQL:

CREATE TABLE `teacher` (
`id` INT(10) NOT NULL,
`name` VARCHAR(30) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO teacher(`id`, `name`) VALUES (1, 'Miss Qin');

CREATE TABLE `student` (
`id` INT(10) NOT NULL,
`name` VARCHAR(30) DEFAULT NULL,
`tid` INT(10) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fktid` (`tid`),
CONSTRAINT `fktid` FOREIGN KEY (`tid`) REFERENCES `teacher` (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8


INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('1', 'Xiao Ming', '1');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('2', 'Xiao Hong', '1');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('3', 'Xiao Zhang', '1');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('4', 'petty thief', '1');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('5', 'Xiao Wang', '1');

Nested processing by query

<!--
Idea:
    1. Query all student information
    2. According to the student's tid，Find the corresponding teacher
-->
    <select id="getStudent" resultMap="StudentTeacher">
        select * from student
    </select>

    <resultMap id="StudentTeacher" type="Student">
        <result property="id" column="id"/>
        <result property="name" column="name"/>
        <!--For complex attributes, we need to deal with objects separately: association  Set: collection-->
        <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
    </resultMap>

    <select id="getTeacher" resultType="Teacher">
        select * from teacher where id = #{id}
    </select>

Nested processing according to results

<!--Nested processing according to results-->
    <select id="getStudent2" resultMap="StudentTeacher2">
        select s.id sid,s.name sname,t.name tname,t.id tid 
        from student s,teacher t
        where s.tid = t.id
    </select>

    <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 property="id" column="tid"/>
        </association>
    </resultMap>

11. One to many processing

One to many understanding:

A teacher has more than one student
For teachers, it is a one to many phenomenon, that is, having a group of students (Collection) under a teacher

Entity class

@Data
public class Student {

    private int id;
    private String name;
    private int tid;
}

@Data
public class Teacher {

    private int id;
    private String name;

    //A teacher has more than one student
    public List<Student> students;
}

Nested query by result

<!--Nested query by result-->
    <select id="getTeacher2" resultMap="TeacherStudent">
        select s.id sid,s.name sname,t.name tname,t.id tid
        from student s,teacher t
        where s.tid = t.id and t.id = #{tid}
    </select>

    <resultMap id="TeacherStudent" type="Teacher">
        <result property="id" column="tid"/>
        <result property="name" column="tname"/>

<!--javaType: Specifies the generic information in the type collection of the attribute, which 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>

Nested processing by query

<select id="getTeacher1" resultMap="TeacherStudent1">
        select * from teacher where id = #{tid}
    </select>

    <resultMap id="TeacherStudent1" type="Teacher">
        <collection property="students" javaType="ArrayList" ofType="Student" select="getStudentByTeacherId" column="id"/>
    </resultMap>

    <select id="getStudentByTeacherId" resultType="Student">
        select * from student where tid = #{tid}
    </select>

Summary

association - association [many to one]
Set collection [one to many]
JavaType & ofType
1. javaType: Specifies the type of the attribute
2. ofType: used to specify pojo types mapped to lists or collections, and constraint types in generic types

Note:

Ensure the readability of SQL and make it easy to understand as much as possible
Note the problem of attribute names and fields in one to many and many to one
Try to use Log4j to troubleshoot errors

12. Dynamic SQL

What is dynamic SQL: generate different SQL statements according to different conditions

Official website description:
dynamic SQL Element and JSTL Or based on similar XML Similar to our text processor. stay MyBatis In previous versions, there were many elements that took time to understand. MyBatis 3 The element types have been greatly simplified. Now you only need to learn half of the original elements. MyBatis Adopt powerful based OGNL To eliminate most of the other elements.
  -------------------------------
  - if
  - choose (when, otherwise)
  - trim (where, set)
  - foreach

Build 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 'Views'
) ENGINE=InnoDB DEFAULT CHARSET=utf8

if

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

where

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

    </select>

set

<update id="updateBlog" parameterType="map">
        update blog
        <set>
            <if test="title != null">
                title = #{title},
            </if>
            <if test="author != null">
                author = #{author}
            </if>
        </set>
        where id = #{id}
    </update>

SQL fragment

Extract common parts using SQL Tags

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

Use the Include tag reference where necessary

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

Foreach

<!--
select * from blog where 1 = 1 and (id = 1 or id = 2 or id = 3)
-->
    <select id="queryBlogForeach" parameterType="map" resultType="blog">
        select * from blog
        <where>
            <foreach collection="ids" item="id" open="and (" close=")" separator="or">
                id = #{id}
            </foreach>
        </where>
    </select>

Dynamic SQL is splicing SQL statements. We just need to ensure the correctness of SQL and arrange and combine them according to the format of SQL

13. Cache

13.1 introduction

1. What is Cache?

There is temporary data in memory.
Put the data frequently queried by users in the cache (memory), and users do not need to query the data from the disk (relational database data file) but from the cache, so as to improve the query efficiency and solve the performance problem of high concurrency system.

2. Why cache?

Reduce the number of interactions with the database, reduce system overhead and improve system efficiency.

3. What kind of data can be cached?

Data that is frequently queried and not frequently changed.

13.2 Mybatis cache

MyBatis includes a very powerful query caching feature, which can easily customize and configure the cache. Caching can greatly improve query efficiency.
Two levels of cache are defined by default in MyBatis system: L1 cache and L2 cache
- By default, only L1 cache is on. (SqlSession level cache, also known as local cache)
- L2 cache needs to be started and configured manually. It is based on namespace level cache.
- In order to improve scalability, MyBatis defines the Cache interface Cache. We can customize the L2 Cache by implementing the Cache interface

13.3 L1 cache

L1 cache is also called local cache:

The data queried during the same session with the database will be placed in the local cache.
In the future, if you need to obtain the same data, you can get it directly from the cache. You don't have to query the database again;

13.4 L2 cache

L2 cache is also called global cache. The scope of L1 cache is too low, so L2 cache was born
Cache based on namespace level, one namespace corresponds to one L2 cache;
Working mechanism
- When a session queries a piece of data, the data will be placed in the first level cache of the current session;
- If the current session is closed, the L1 cache corresponding to this session is gone; But what we want is that the session is closed and the data in the L1 cache is saved to the L2 cache;
- The new session query information can get the content from the L2 cache;
- The data found by different mapper s will be placed in their corresponding cache (map);

Steps:

Turn on global cache

<!--Show enable global cache-->
        <setting name="cacheEnabled" value="true"/>

Turn on in Mapper where you want to use L2 cache

<!--At present Mapper.xml Using L2 cache in-->
    <cache/>

You can also customize parameters

<!--At present Mapper.xml Using L2 cache in-->
    <cache eviction="FIFO"
           flushInterval="60000"
           size="512"
           readOnly="true"
    />

test

All entity classes serialize interfaces first

@Test
    public void Test2(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        SqlSession sqlSession2 = MybatisUtils.getSqlSession();

        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        User user = mapper.queryUserById(1);
        System.out.println(user);
        sqlSession.close();


        UserMapper mapper2 = sqlSession2.getMapper(UserMapper.class);
        User user2 = mapper2.queryUserById(2);
        System.out.println(user2);
        sqlSession2.close();
    }

conclusion

As long as the L2 cache is enabled, our queries in the same Mapper can get data in the L2 cache
The detected data will be put in the first level cache by default
Only after the session is committed or closed, the data in the L1 cache will be transferred to the L2 cache

13.5 Ehcache

Ehcache is a widely used java distributed cache, which is used for general cache;

Guide Package

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

Use the corresponding cache in Mapper

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

Write ehcache XML file

<?xml version="1.0" encoding="UTF-8"?>
<ehcache xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:noNamespaceSchemaLocation="http://ehcache.org/ehcache.xsd"
         updateCheck="false">
    <!--
       diskStore: Is the cache path, ehcache There are two levels: memory and disk. This attribute defines the cache location of the disk. The parameters are explained as follows:
       user.home – User home directory
       user.dir – User's current working directory
       java.io.tmpdir – Default temporary file path
     -->
    <diskStore path="./tmpdir/Tmp_EhCache"/>

    <defaultCache
            eternal="false"
            maxElementsInMemory="10000"
            overflowToDisk="false"
            diskPersistent="false"
            timeToIdleSeconds="1800"
            timeToLiveSeconds="259200"
            memoryStoreEvictionPolicy="LRU"/>

    <cache
            name="cloud_user"
            eternal="false"
            maxElementsInMemory="5000"
            overflowToDisk="false"
            diskPersistent="false"
            timeToIdleSeconds="1800"
            timeToLiveSeconds="1800"
            memoryStoreEvictionPolicy="LRU"/>
    <!--
       defaultCache: Default cache policy, when ehcache This cache policy is used when the defined cache cannot be found. Only one can be defined.
     -->
    <!--
      name:Cache name.
      maxElementsInMemory:Maximum number of caches
      maxElementsOnDisk: Maximum number of hard disk caches.
      eternal:Whether the object is permanently valid, but once it is set, timeout Will not work.
      overflowToDisk:Whether to save to disk when the system crashes
      timeToIdleSeconds:Set the allowed idle time of the object before expiration (unit: seconds). Only if eternal=false It is used when the object is not permanently valid. It is an optional attribute. The default value is 0, that is, the idle time is infinite.
      timeToLiveSeconds:Set the allowable survival time of the object before invalidation (unit: seconds). The maximum time is between creation time and expiration time. Only if eternal=false Used when the object is not permanently valid. The default is 0.，That is, the survival time of the object is infinite.
      diskPersistent: Whether to cache virtual machine restart data Whether the disk store persists between restarts of the Virtual Machine. The default value is false.
      diskSpoolBufferSizeMB: This parameter setting DiskStore(Cache size of disk cache). The default is 30 MB. each Cache Each should have its own buffer.
      diskExpiryThreadIntervalSeconds: The running time interval of disk failure thread is 120 seconds by default.
      memoryStoreEvictionPolicy: When reached maxElementsInMemory When restricted, Ehcache The memory will be cleaned up according to the specified policy. The default policy is LRU(Least recently used). You can set it to FIFO(First in first out) or LFU(Less used).
      clearOnFlush: Whether to clear when the amount of memory is maximum.
      memoryStoreEvictionPolicy:The optional strategies are: LRU(Least recently used, default policy) FIFO(First in first out) LFU(Minimum number of visits).
      FIFO，first in first out，This is the most familiar, first in, first out.
      LFU， Less Frequently Used，This is the strategy used in the above example. To put it bluntly, it has always been the least used. As mentioned above, the cached element has a hit Properties, hit The smallest value will be flushed out of the cache.
      LRU，Least Recently Used，The least recently used element in the cache has a timestamp. When the cache capacity is full and it needs to make room for caching new elements, the element with the farthest timestamp from the current time in the existing cache elements will be cleared out of the cache.
   -->

</ehcache>

Keywords: Java Mybatis

Added by Shadow Wolf on Sun, 06 Feb 2022 23:53:52 +0200

Programming VIP

Mybatis madness lecture notes (incomplete)

1. Introduction

1.1 what is Mybatis

1.2 persistence

1.3 durable layer

2. The first Mybatis program

2.1 construction environment

2.2 coding

3.CRUD

1. namespace

2. select

3. insert

4. update

5. delete

6. Universal Map

4. Configuration analysis

1. Core profile

2. environments

3. properties

4. Type aliases

5. mappers

6. Life cycle and scope

5. Solve the problem of inconsistency between attribute name and field name

6. Log

6.1 log factory

6.2Log4j

7. Pagination

7.1 using limit paging

7.2 rowboundaries paging

8. Development using annotations

8.1 interface oriented programming

8.2 development using annotations

8.3 CRUD

9. Lombok

10. Many to one processing

Nested processing by query

Nested processing according to results

11. One to many processing

12. Dynamic SQL

Build environment

if

where

set

SQL fragment

Foreach

13. Cache

13.1 introduction

13.2 Mybatis cache

13.3 L1 cache

13.4 L2 cache

13.5 Ehcache

Popular Keywords