Mybatis study notes

Keywords: Mybatis

Mybatis-9.28

Environmental Science:

JDK1.8
Mysql 5.7
maven 3.6.1
IDEA

Review:

JDBC
Mysql
Java Foundation
Maven
Junit

SSM framework: of configuration files. The best way: look at the official website documents;

1. Introduction

1.1. What is Mybatis

MyBatis is an excellent persistence layer framework
It supports customized SQL, stored procedures, and advanced mapping.
MyBatis avoids almost all JDBC code and manually setting parameters and getting result sets.
MyBatis can use simple XML or annotations to configure and map native types, interfaces and Java POJO s (Plain Old Java Objects) as records in the database.
MyBatis was apache An open source project iBatis In 2010, the project was migrated from apache software foundation to google code and renamed MyBatis.
Moved to Github in November 2013.

How do I get Mybatis?

maven warehouse:

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

Github : https://github.com/mybatis/mybatis-3/releases
Chinese documents: https://mybatis.org/mybatis-3/zh/index.html

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.
Life: refrigerated. Canned.

Why persistence?

There are some objects that he can't lose.
Memory is too expensive

1.3. Durable layer

Dao layer, Service layer, Controller layer

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

1.4 why do you need Mybatis?

The helper program stores the data into the database.
convenient
Traditional JDBC code is too complex. simplify. Frame. Automation.
Not Mybatis. Easier to use. There is no distinction between high and low technology
advantage:
- Easy to learn
- flexible
- The separation of sql and code improves maintainability.
- Provide mapping labels to support the mapping of orm fields between objects and databases
- Provide object relationship mapping labels to support object relationship construction and maintenance
- Provide xml tags to support writing dynamic sql.

The most important point: many people use it!

Spring SpringMVC SpringBoot

2. The first Mybatis program

Idea: build an environment -- > Import mybatis -- > write code -- > test!

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,'Mad God','123456'),
(2,'Zhang San','123456'),
(3,'Li Si','123890')

New project

Create a normal maven project
Delete src directory

Import maven dependencies

    <!--Import dependency-->
    <dependencies>
        <!--mysql drive-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>
        <!--mybatis-->
        <!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.2</version>
        </dependency>
        <!--junit-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>
    </dependencies>

2.2. Create a module

Write the core configuration file of mybatis

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<!--configuration Core profile-->
<configuration>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis?useSSL=true&amp;useUnicode=true&amp;characterEncoding=UTF-8"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>

</configuration>

Write mybatis tool class

//sqlSessionFactory --> sqlSession
public class MybatisUtils {

    private static SqlSessionFactory sqlSessionFactory;

    static{
        try {
            //Step 1 of using Mybatis: get sqlSessionFactory object
            String resource = "mybatis-config.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

    //Now that we have SqlSessionFactory, as the name suggests, we can get an instance of SqlSession from it.
    // SqlSession completely contains all the methods required to execute SQL commands for the database.
    public static SqlSession  getSqlSession(){
        return sqlSessionFactory.openSession();
    }

}

2.3. Code writing

Entity class

package com.kuang.pojo;

//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();
}

The interface implementation class is transformed from the original UserDaoImpl to a Mapper configuration file

<?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.kuang.dao.UserDao">

<!--select Query statement-->
   <select id="getUserList" resultType="com.kuang.pojo.User">
       select * from mybatis.user
   </select>

</mapper>

2.4 test

Note:

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

What is MapperRegistry?

junit test

@Test
public void test(){
    //Step 1: get SqlSession object
    SqlSession sqlSession = MybatisUtils.getSqlSession();


    //Method 1: getMapper
    UserDao userDao = sqlSession.getMapper(UserDao.class);
    List<User> userList = userDao.getUserList();

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



    //Close SqlSession
    sqlSession.close();
}

You can the problems you will encounter:

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.kuang.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();
    }

3,Insert

    <!--The properties in the object can be taken out directly-->
    <insert id="addUser" parameterType="com.kuang.pojo.User">
        insert into mybatis.user (id, name, pwd) values (#{id},#{name},#{pwd});
    </insert>

4,update

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

5,Delete

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

Note:

Add, delete and modify transactions need to be submitted!

6. Analysis error

Don't match the labels incorrectly
resource binding mapper requires a path!
The program configuration file must comply with the specification!
NullPointerException, not registered to resource!
There is a problem of Chinese garbled code in the output xml file!
maven resources have no export problem!

7. Universal Map

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

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

    <!--The properties in the object can be taken out directly    transmit map of key-->
    <insert id="addUser" parameterType="map">
        insert into mybatis.user (id, pwd) values (#{userid},#{passWord});
    </insert>

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

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


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

        map.put("userid",5);
        map.put("passWord","2222333");

        mapper.addUser2(map);

        sqlSession.close();
    }

Pass parameters from Map and directly get the key from sql! [parameterType="map"]

Object transfer parameters, you can 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!

Use Map or annotation for multiple parameters!

8. Thinking questions

How to write fuzzy query?

When executing Java code, pass wildcard%%

List<User> userList = mapper.getUserLike("%Lee%");

Use wildcards in sql splicing!

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

4. Configuration resolution

1. Core profile

mybatis-config.xml

The MyBatis configuration file contains settings and attribute information that deeply affect MyBatis behavior.

configuration(Configuration)
properties(Properties)
settings(Settings)
typeAliases(Type alias)
typeHandlers(Type (processor)
objectFactory(Object factory)
plugins(Plug in)
environments(Environment configuration)
environment(Environment variables)
transactionManager(Transaction manager)
dataSource((data source)
databaseIdProvider(Database (vendor ID)
mappers(Mapper)

2. Environment configurations

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.

Learn to use and configure multiple operating environments!

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

3. properties

We can reference the configuration file through the properties property

These properties are externally configurable and dynamically replaceable. They can be configured in a typical Java property file or passed through the child elements of the properties element. [db.properties]

Write a configuration file

db.properties

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

Mapping in core configuration file

    <!--Import external profile-->
    <properties resource="db.properties">
        <property name="username" value="root"/>
        <property name="pwd" value="11111"/>
    </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

A type alias is a short 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.kuang.pojo.User" alias="User"/>
    </typeAliases>

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

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

<!--You can alias an entity class-->
<typeAliases>
    <package name="com.kuang.pojo"/>
</typeAliases>

The first method is used when there are few entity classes.

If there are many entity classes, the second one is recommended.

The first can be a DIY alias, and the second can't. If you have to change it, you need to add annotations on the entity

@Alias("user")
public class User {}

5. Set

These are extremely important tuning settings in MyBatis, which change the runtime behavior of MyBatis.

6. Other configurations

typeHandlers
objectFactory (object factory)
plugins plug-in
- mybatis-generator-core
- mybatis-plus
- General mapper

7. mappers

MapperRegistry: register and bind our Mapper file;

Method 1: [recommended]

<!--every last Mapper.XML All need to be in Mybatis Register in the core configuration file!-->
<mappers>
    <mapper resource="com/kuang/dao/UserMapper.xml"/>
</mappers>

Method 2: register with class file binding

<!--every last Mapper.XML All need to be in Mybatis Register in the core configuration file!-->
<mappers>
    <mapper class="com.kuang.dao.UserMapper"/>
</mappers>

Note:

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

Method 3: use scan package for injection binding

<!--every last Mapper.XML All need to be in Mybatis Register in the core configuration file!-->
<mappers>
    <package name="com.kuang.dao"/>
</mappers>

Note:

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

Practice time:

Bringing a database configuration file into an external database
Entity class alias
Ensure that the UserMapper interface and UserMapper. XML are changed to be consistent! And put it under the same bag!

8. Lifecycle and scope

Life cycle, and scope, are crucial because incorrect use can lead to very serious concurrency problems.

SqlSessionFactoryBuilder:

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

SqlSessionFactory:

To put it bluntly, 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.
You need to close it immediately after use, otherwise the resources will be occupied!

Each Mapper in this represents a specific business!

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

1. Question

Fields in the database

Create a new project and test the inconsistency of entity class fields before copying

public class User {
    
    private int id;
    private String name;
    private String password;
}

There is a problem with the test

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

resolvent:

Alias

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

2,resultMap

Result set mapping

id   name   pwd
id   name   password

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

<select id="getUserById" resultMap="UserMap">
    select * from mybatis.user where id = #{id}
</select>

The resultMap element is the most important and powerful element in MyBatis
The design idea of ResultMap is that there is no need to configure explicit result mapping for simple statements, but only need to describe their relationship for more complex statements.
The best thing about ResultMap is that although you already know it well, you don't need to explicitly use them at all.
If only the world were always so simple.

6. Log

6.1 log factory

If an exception occurs in a database operation, we need to troubleshoot it. Log is the best assistant!

Once: South, debug

Now: log factory!

SLF4J
LOG4J [Master]
LOG4J2
JDK_LOGGING
COMMONS_LOGGING
STDOUT_LOGGING [mastering]
NO_LOGGING

Which log implementation is used in Mybatis? Set it in settings!

STDOUT_LOGGING standard log output

In the mybatis core configuration file, configure our logs!

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

6.2,Log4j

What is Log4j?

Log4j yes Apache By using Log4j, we can control the destination of log information delivery Console , documents GUI assembly
We can also 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>

log4j.properties

#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

#Settings related to 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

#Settings related to 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=""/>
</settings>

Use of Log4j!, Run the query directly

Simple use

In the class to use log4j, import the package import org.apache.log4j.Logger;

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

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

log level

logger.info("info:Entered testLog4j");
logger.debug("debug:Entered testLog4j");
logger.error("error:Entered testLog4j");

7. Pagination

Think: why pagination?

Reduce data processing

7.1. Use Limit paging

Syntax: SELECT * from user limit startIndex,pageSize;
SELECT * from user limit 3;  #[0,n]

Pagination using Mybatis, core SQL

Interface

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

Mapper.xml

<!--//Paging -- >
<select id="getUserByLimit" parameterType="map" resultMap="UserMap">
    select * from  mybatis.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",1);
    map.put("pageSize",2);

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

    sqlSession.close();
    }

7.2 rowboundaries paging

No longer use SQL for paging

Interface

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

mapper.xml

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

test

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

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

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

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

    sqlSession.close();
    }

7.3 paging plug-in

Just understand. If the company's architects say they want to use it in the future, you need to know what it is!

8. Using annotation development

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 does not 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 to system designers;
-The cooperative 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 means programming according to this idea.

Understanding of interfaces

-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

Annotations are implemented on the interface

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

You need to bind the interface in the core configuration file!

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

test

Essence: reflection mechanism implementation

Bottom layer: dynamic agent!

Mybatis detailed execution process!

8.3,CRUD

We can automatically commit transactions when the tool class is created!

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

Write the interface and add comments

public interface UserMapper {

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

    // Method has multiple parameters. All parameters must be preceded by @ Param("id") annotation
    @Select("select * from user where id = #{id}")
    User getUserByID(@Param("id") int id);


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

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

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

Test class

[Note: we must bind the interface registration to our core configuration file!]

About @ Param() annotation

Parameters of basic type or String type need to be added
Reference types do not need to be added
If there is only one basic type, it can be ignored, but it is recommended that everyone add it!
What we refer to in SQL is the attribute name set in @ Param()!

#{} ${} difference

9,Lombok

Project Lombok is a java library that automatically plugs into your editor and build tools, spicing up your java.
Never write another getter or equals method again, with one annotation your class has a fully featured builder, Automate your logging variables, and much more.

java library
plugs
build tools
with one annotation your class

Use steps:

Install Lombok plug-in in IDEA!

Import the jar package of lombok in the project

<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.10</version>
</dependency>

Just annotate the entity class!

@Data
@AllArgsConstructor
@NoArgsConstructor

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

explain:

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

10. Many to one processing

Many to one:

Multiple students correspond to one teacher
For students, multiple students and one teacher [many to one]
For teachers, a collection, a teacher, has many students [one to many]

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');

Test environment construction

Import lombok
New entity class Teacher, Student
Establish Mapper interface
Create Mapper.XML file
Bind and register our Mapper interface or file in the core configuration file! [there are many ways to choose]
Test whether the query is successful!

Nested processing by query

<!--
    thinking:
        1. Query all student information
        2. According to the student's tid，Find the corresponding teacher! Subquery
    -->

<select id="getStudent" resultMap="StudentTeacher">
    select * from student
</select>

<resultMap id="StudentTeacher" type="Student">
    <result property="id" column="id"/>
    <result property="name" column="name"/>
    <!--For complex properties, 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
    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"/>
    </association>
</resultMap>

Review the Mysql many to one query method:

Subquery
Join table query

11. One to many processing

For example: a teacher has multiple students!

For teachers, it is a one to many relationship!

Environment construction

The environment is the same as just now

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
    private List<Student> students;
}

Nested processing according to results

    <!--Nested query by result-->
    <select id="getTeacher" 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"/>
        <!--For complex properties, we need to deal with objects separately: association Set: collection
        javaType="" Specify the type of attribute!
        Collection, we use ofType obtain
        -->
        <collection property="students" ofType="Student">
            <result property="id" column="sid"/>
            <result property="name" column="sname"/>
            <result property="tid" column="tid"/>
        </collection>
    </resultMap>

Nested processing by query

<select id="getTeacher2" resultMap="TeacherStudent2">
    select * from mybatis.teacher where id = #{tid}
</select>

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

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

Summary

association - association [many to one]
Set - collection [one to many]
javaType & ofType
1. JavaType is used to specify the type of attribute in the entity class
2. ofType is used to specify the pojo type mapped to List or collection, and the constraint type in generic type!

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!
If the problem is difficult to troubleshoot, log can be used. Log4j is recommended

Slow SQL 1s 1000s

Interview frequency

Mysql engine
InnoDB underlying principle
Indexes
Index optimization!

12. Dynamic SQL

What is dynamic SQL: dynamic SQL refers to generating different SQL statements according to different conditions

Using the feature of dynamic SQL can completely get rid of this pain.

dynamic SQL Element and JSTL Or based on similar XML Similar to the text processor. stay MyBatis In previous versions, there were many elements that needed time to understand. MyBatis 3 The element types are greatly simplified. Now you only need to learn half of the original elements. MyBatis 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

Create a basic project

Guide Package
Write configuration file

Writing entity classes

@Data
public class Blog {
    private int id;
    private String title;
    private String author;
    private Date createTime;
    private int views;
    
    
}

Write Mapper interface and Mapper.XML file corresponding to entity class

IF

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

choose (when, otherwise)

    <select id="queryBlogChoose" parameterType="map" resultType="blog">
        select * from mybatis.blog
        <where>
            <choose>
                <when test="title != null">
                    title = #{title}
                </when>
                <when test="author != null">
                    and author = #{author}
                </when>
                <otherwise>
                    and views = #{views}
                </otherwise>
            </choose>
        </where>
    </select>

trim (where,set)

select * from mybatis.blog
<where>
    <if test="title != null">
        title = #{title}
    </if>
    <if test="author != null">
        and author = #{author}
    </if>
</where>

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

The so-called dynamic SQL is still an SQL statement in essence, but we can execute a logical code at the SQL level

where ， set ， choose ，when

SQL fragment

Sometimes, we may extract some functions for reuse!

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 mybatis.blog
    <where>
        <include refid="if-title-author"></include>
    </where>
</select>

matters needing attention:

It is best to define SQL fragments based on a single table!
Do not have a where tag

Foreach

select * from user where 1=1 and 

  <foreach item="id" collection="ids"
      open="(" separator="or" close=")">
        #{id}
  </foreach>

(id=1 or id=2 or id=3)

<!--
        select * from mybatis.blog where 1=1 and (id=1 or id = 2 or id=3)

        We are now passing on a universal message map ， this map There can be a collection in!
-->
<select id="queryBlogForeach" parameterType="map" resultType="blog">
    select * from mybatis.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 SQL format

Recommendations:

Now write a complete SQL in Mysql, and then modify it accordingly to become our dynamic SQL, which can be used for general purpose!

13. Cache (understand)

13.1 introduction

Query: connect to the database and consume resources!
	The result of a query will be temporarily stored in a place that can be directly accessed!--> Memory: caching
	
When we query the same data again, we directly go to the cache instead of the database

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 from the disk (relational database data file) but from the cache to query the data, so as to improve the query efficiency and solve the performance problem of high concurrency system.
Why cache?
- Reduce the number of interactions with the database, reduce system overhead and improve system efficiency.
What kind of data can be cached?
- Frequently queried and infrequently changed data. [cache can be used]

13.2. Mybatis cache

MyBatis includes a very powerful query caching feature that makes it easy to customize and configure caching. 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 manually enabled and configured. 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

The L1 cache is also called the local cache: SqlSession
- 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;

Test steps:

Open log!
The test queries the same record twice in a session
View log output

Cache invalidation:

Query different things
Adding, deleting and modifying may change the original data, so the cache will be refreshed!
Query different Mapper.xml
Manually clean up the cache!

Summary: the L1 cache is enabled by default. It is only valid in one SqlSession, that is, the interval from getting the connection to closing the connection!

The first level cache is a Map.

13.4 L2 cache

L2 cache is also called global cache. The scope of L1 cache is too low, so L2 cache was born
Based on the namespace level cache, a namespace corresponds to a 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 the 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 global cache enabled-->
<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
1. Problem: we need to serialize the entity class! Otherwise, an error will be reported!
```
Caused by: java.io.NotSerializableException: com.kuang.pojo.User
```

Summary:

As long as the L2 cache is enabled, it is valid under the same Mapper
All data will be put in the first level cache first;
Only when the session is submitted or closed will it be submitted to the secondary buffer!

13.5 cache principle

13.6. User defined cache ehcache

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

To use ehcache in the program, you must first import the package!

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

Specify our ehcache cache implementation in mapper!

<!--At present Mapper.xml Using L2 cache in-->
<cache type="org.mybatis.caches.ehcache.EhcacheCache"/>

ehcache.xml

<?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 (in seconds) of the object before expiration. 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:Sets the allowable survival time (in seconds) of an object before it expires. 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: 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 for 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 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: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 cache element has a timestamp. When the cache capacity is full and it is necessary to make room for caching new elements, the element with the farthest timestamp from the current time in the existing cache element will be cleared out of the cache.
   -->

</ehcache>

Redis database for caching! K-V

Exercise: 29 exercises!

Posted by simmosn on Tue, 09 Nov 2021 16:35:54 -0800

Programmer Group

Mybatis study notes

Mybatis-9.28

1. Introduction

1.1. What is Mybatis

1.2 persistence

1.3. Durable layer

1.4 why do you need Mybatis?

2. The first Mybatis program

2.1 construction environment

2.2. Create a module

2.3. Code writing

2.4 test

3,CRUD

1,namespace

2,select

3,Insert

4,update

5,Delete

6. Analysis error

7. Universal Map

8. Thinking questions

4. Configuration resolution

1. Core profile

2. Environment configurations

3. properties

4. Type aliases

5. Set

6. Other configurations

7. mappers

8. Lifecycle and scope

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

1. Question

2,resultMap

6. Log

6.1 log factory

6.2,Log4j

7. Pagination

7.1. Use Limit paging

7.2 rowboundaries paging

7.3 paging plug-in

8. Using annotation development

8.1 interface oriented programming

8.2 development using annotations

8.3,CRUD

9,Lombok

10. Many to one processing

Test environment construction

Nested processing by query

Nested processing according to results

11. One to many processing

Environment construction

Nested processing according to results

Nested processing by query

Summary

12. Dynamic SQL

Build environment

IF

choose (when, otherwise)

trim (where,set)

SQL fragment

Foreach

13. Cache (understand)

13.1 introduction

13.2. Mybatis cache

13.3. L1 cache

13.4 L2 cache

13.5 cache principle

13.6. User defined cache ehcache

Exercise: 29 exercises!

Hot Keywords