Mybatis--Universal Map--mappers--Cache-LOG4J--MyBatis Paging Plug-in--Developed with Annotations---#{} Difference from ${}--Lombok--Many-to-Many Processing Dynamic SQL

MyBatis

Environmental Science:

JDK1.8
Mysql 5.7
maven 3.6.1
IDEA

Review:

JDBC
Mysql
Java Foundation
Maven
Junit

SSM framework: Configuration file. Best way: see the official website documentation;

mybatis - MyBatis 3 | Getting Started

1. Introduction

1.1, What is Mybatis

MyBatis is an excellent persistence framework
. It supports customizing SQL, stored procedures, and advanced mappings.
MyBatis avoids almost all JDBC code and setting parameters manually and getting result sets.
oMyBatis can use simple XML or annotations to configure and map native types, interfaces, and Java POJOs (Plain Old JavaObjects, plain old Java objects) as records in the database.
oMyBatis was originally an open source project for apache, iBatis, which was migrated to google code by apache software foundation in 2010 and renamed MyBatis.
Migrated to Github in November 2013.

How do I get Mybatis?

maven repository:

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

.Github :Releases · mybatis/mybatis-3 · GitHub

Chinese Documents: mybatis - MyBatis 3 | Getting Started

1.2. Persistence

Data persistence

Persistence is the process of transforming program data into persistent and transient states
/ Memory: Lost when power is off
. Database (Jdbc), io file persistence.
Life: Refrigerate. Can.

Why do I need to persist?

.There are some objects that can't be thrown away.
* Memory is too expensive

1.3, Persistence Layer

Dao Layer, Service Layer, Controller Layer...

Code block to complete persistence
Layer boundaries are obvious.

1.4 Why do I need Mybatis?

Helper apes store data in databases.
convenient
Traditional JDBC code is too complex.
Simplify. Frame. Automation.
/ You can do without Mybatis.
It's easier to get started. There is no difference in technology

Advantages:

Simple and easy to learn.
flexible
Separation of sql and code improves maintainability.
Provides mapping labels to support object-to-database or field relationship mapping. Provides object relationship mapping labels to support maintenance of object relationship organization
Provides xml tags to support writing dynamic sql.

Most importantly, there are many people using it!

2. First Mybatis program

Think: Set up an environment --> Import Mybatis --> Write Code --> Test!

2.1. Setting up environment

Set up 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,'Wang Hailin','123456') ,
(2,'Hailin','124446') ,
(3,'Wang Lin','122256') ,
(4,'Wang Hai','123456')

New Project

1. Create a new normal maven project

2. Delete src directory

3. Import maven dependencies

<!--Import Dependent jar package-->
  <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.3</version>
    </dependency>

<!--    junit-->
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.13</version>
      <scope>test</scope>
    </dependency>
  </dependencies>

2.2. Create a module

In mybatis-config. Writing the core configuration file for mybatis in XML

<?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>
    <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/smbms?useSSL=false&amp;useUnicode=true&amp;characterEncoding=utf-8"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="org/mybatis/example/BlogMapper.xml"/>
    </mappers>
</configuration>

Writing mybatis tool class

package com.Wang.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;

//Tool class to get sqlSessionFactory object--for production--"sqlSession
public class MybatisUtils {

    private static SqlSessionFactory sqlSessionFactory;

    static {

        //The first step in using Mybatis ---) is to get the sqlSessionFactory object
        try {
            String resource = "mybatis-config.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

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

2.3. Writing Code

Entity Class

package com.Wang.pojo;

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 "com.Wang.pojo.User{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", pwd='" + pwd + '\'' +
                '}';
    }
}

Mapper interface (original dao interface)

package com.Wang.mapper;

import com.Wang.pojo.User;

import java.util.List;

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

Interface implementation class changed from UserDaolmpl 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">
<mapper namespace="com.Wang.mapper.UserMapper">//Interface to connect to
    //select is the statement of the query, the method to be implemented by the id connection, and resultType is the type returned
    <select id="getUserList" resultType="com.Wang.pojo.User">
        select * from mybatis.user
    </select>
</mapper>

parameterType and resultType description

parameterType: Specifies the input parameter type, and mybatis splices the parameter values from the input object into sql by ognl.

ResultType: Specifies the output result type, and mybatis maps a row of record data of the sql query result to an object of the resultType specified type. If there are multiple pieces of data, map them individually and place the objects in the container List

2.4. Testing

Note:

org.apache.ibatis.binding.BindingExcepion: ype TmelIatecInuutguUMapperRegistry.

What is MapperRegistry?

There may be an error here:

This is because there was a configuration error in Maven and you need to add:

<!--stay build Configuration in resources，To prevent our resource export from failing-->
<build>
  <resources>
    <resource>
      <directory>src/main/resources</directory>
      <includes>
        <include>**/*.properties</include>
        <include>**/*.xml</include>
      </includes>
    </resource>
    <resource>
      <directory>src/main/java</directory>
      <includes>
        <include>**/*.properties</include>
        <include>**/*.xml</include>
      </includes>
      <filtering>true</filtering>
    </resource>
  </resources>
</build>

junit test

package com.Wang.mapper;

import com.Wang.pojo.User;
import com.Wang.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.List;

public class UserMapperTest {
    @Test
    public void test(){

        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        List<User> userList = mapper.getUserList();

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

    }
}

The test was successful!

3,CRUD

1,namespace

The package name in the namespace should match the package name of the 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!

1. Write interfaces

//Query all user messages
List<User> getUserList();
//Query based on user id
User getUserById(int id);
//insert user
int insertUser(User user);
//delete user
int deleteUser(int id);
//update user
int updateUser(User id);

2. Write sql statements in the corresponding mapper

<?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">
<mapper namespace="com.Wang.mapper.UserMapper">
<!--    Query all users-->
    <select id="getUserList" resultType="com.Wang.pojo.User">
        select * from mybatis.user
    </select>
<!--    According to user id query-->
    <select id="getUserById" parameterType="int" resultType="com.Wang.pojo.User">
        select * from mybatis.user where id = #{id}
    </select>
<!--    insert user-->
    <insert id="insertUser" parameterType="com.Wang.pojo.User">
        insert into mybatis.user(id,name,pwd) value (#{id},#{name},#{pwd})
    </insert>
<!--    delete User-->
    <delete id="deleteUser" parameterType="int">
        delete from mybatis.user where id = #{id}
    </delete>
<!--    update user-->
    <update id="updateUser" parameterType="com.Wang.pojo.User">
        update mybatis.user set name = #{name},pwd = #{pwd} where id = #{id}
    </update>
</mapper>

3. Testing

package com.Wang.mapper;

import com.Wang.pojo.User;
import com.Wang.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.List;

public class UserMapperTest {
    @Test
    public void test(){

        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        List<User> userList = mapper.getUserList();

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

    }


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

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

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

        int zj = mapper.insertUser(new User(5, "Zhou Jie", "123453"));

        if (zj>0){
            System.out.println("Insertion succeeded!");
        }
        sqlSession.commit();


        sqlSession.close();
    }

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

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

        int i = mapper.deleteUser(4);
        if (i>0){
            System.out.println(":success");
        }


        sqlSession.commit();

        sqlSession.close();
    }

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

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

        int i = mapper.updateUser(new User(2, "Ha ha ha", "11111"));
        if (i>0){
            System.out.println("success");
        }


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


    }
}

3,Insert

<!--    insert user-->
    <insert id="insertUser" parameterType="com.Wang.pojo.User">
        insert into mybatis.user(id,name,pwd) value (#{id},#{name},#{pwd})
    </insert>

4,update

<!--    update user-->
    <update id="updateUser" parameterType="com.Wang.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>

Note:

Additions and deletions require transaction submission!

6. Analysis Errors

Don't mismatch labels
resource binds mapper, need to use path!
Program profile must conform to specifications!
NullPointerException, not registered with resource!
Output xml file has Chinese scrambling problem!.
There are no export problems for maven resources!

7. Universal Map

Assuming that we have too many entity classes or tables, fields, or parameters in our database, we should consider using Map!

//Omnipotent map
int addUser(HashMap<String,Object> map);

<!--    Object attributes can be taken out directly to the corresponding map-->
    <insert id="addUser" parameterType="map">
        insert into mybatis.user(id,name,pwd) value (#{userId},#{userName},#{Password})
    </insert>

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

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

    map.put("userId",6);
    map.put("userName","Zhou Xiaojie");
    map.put("Password","1122222");

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

    mapper.addUser(map);

    sqlSession.commit();

    sqlSession.close();


}

Map passes parameters, just take the key out of sql! [parameterType="map"]

Object pass parameters, just take the object's properties directly in sql! [parameterType="Object"]

With only one basic type parameter, it can be fetched directly in sql!

Use Map for multiple parameters, or annotate!

8. Think Questions

How to write a fuzzy query?

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

List<user> userList = mapper.getuserLike("%plum%");

2. Use wildcards in sql splicing!

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

4. Configuration resolution

1. Core Profile

mybatis-config.xml
The configuration file for MyBatis contains settings and attribute information that deeply affect the behavior of MyBatis.

configuration(Configuration)
properties(Attributes)
settings(Settings)
typeAliases(Type Alias)
typeHandlers(Type Processor)
objectFactory(Object Factory)
plugins(Plug-ins)
environments(Environment Configuration)
environment(Environment Variables)
transactionManager(Transaction Manager)
dataSource(Data Source)
databaseIdProvider(Database vendor identification)
mappers(Mapper)

2. Environment Configuration

MyBatis can be configured to accommodate multiple environments

Keep in mind, though you can configure multiple environments, that only one environment can be selected for each SqlSessionFactory instance.

A key:

Learn to use multiple operating environments!

The default transaction manager for Mybatis is JDBC, connection pool: POOLED

The environments element defines how the environment is configured.

<environments default="development">   //Default here to set the default configuration environment
  <environment id="development">
    <transactionManager type="JDBC">
      <property name="..." value="..."/>
    </transactionManager>
    <dataSource type="POOLED">
      <property name="driver" value="${driver}"/>
      <property name="url" value="${url}"/>
      <property name="username" value="${username}"/>
      <property name="password" value="${password}"/>
    </dataSource>
  </environment>
</environments>

Note some key points:

The environment ID used by default (for example, default="development").
The environment ID defined by each environment element (for example, id="development").
Configuration of the transaction manager (for example, type="JDBC").
Configuration of the data source (for example, type="POOLED").

The default environment and environment ID are as their names imply. Environments can be named arbitrarily, but be sure that the default environment ID matches one of them.

3. Properties

We can reference profiles by using the properties property

These properties are externally configurable and dynamically replaceable, either in a typical Java property file.

It can also be passed through 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=false&useUnicode=true&characterEncoding=utf-8
username=root
password=123456

Introduced in Core Profile

<!--    Introduce an external configuration file, which is db.properties-->
    <properties resource="db.properties">
        <property name="" value=""/>//Configuration files can also be set here
        <property name="" value=""/>
    </properties>

External files can be imported directly
. You can add some attribute configurations to it
If two files have the same field, use the external profile first!

4. Type Aliases

Type aliases are short names set for Java types.
It only makes sense to reduce redundancy in fully qualified class names.

<!--    Entity classes can be aliased-->
    <typeAliases>
        <typeAlias type="com.Wang.pojo.User" alias="User"/>
    </typeAliases>

You can also specify a package name under which MyBatis will search for the Java Bean s it needs, such as:

Scan for packages of entity classes whose default alias is the class name of this class, in lowercase!

<!--    Aliasing entity classes by package-->
    <typeAliases>
        <package name="com.Wang.pojo"/>
    </typeAliases>

Use the first option when there are fewer entity classes.

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

The first can be a DIY alias, the second can be **. No. If you don't want to change it, you need to add a comment on the entity**

import org.apache.ibatis.type.Alias;

@Alias("hello")
public class User {

5. settings

These are extremely important adjustment settings in MyBatis that change the runtime behavior of MyBatis.

Set Name	describe	Valid Value	Default value
cacheEnabled	Turn on or off any caches configured in all mapper profiles globally.	true \| false	true
lazyLoadingEnabled	Global switch for delayed loading. When opened, all associated objects are delayed loading. The fetchType property can be set to override the switch state of the item in a particular relationship.	true \| false	false

mapUnderscoreToCamelCase	Whether to turn on automatic mapping of hump naming, i.e. column name A_from a classic database COLUMN maps to the classic Java property name aColumn.	true \| false	False

6. Other configurations

mybatis-generator-core
mybatis-plus
Universal mapper

7. Mapper (mappers)

MapperRegistry: Register the Mapper files that bind us;

Mode 1:

<!--    Every last Mapper.XML Need to be in Mybatis Register in Core Profile!-->
    <mappers>
        <mapper resource="com/Wang/mapper/UserMapper.xml"/>
    </mappers>

Mode 2: Use class file binding to register

<!--    Every last Mapper.XML Need to be in Mybatis Register in Core Profile!-->
    <mappers>
        <mapper class="com.Wang.mapper.UserMapper"/>
    </mappers>

**Note:** When using packages, with class scans:

The interface and its Mapper profile must have the same name!
The interface and its Mapper profile must be in the same package!

Mode 3: Injection binding using scan packages

<!--    Every last Mapper.XML Need to be in Mybatis Register in Core Profile!-->
    <mappers>
        <package name="com.Wang.mapper"/>
    </mappers>

**Note:** When using packages, with class scans:

The interface and its Mapper profile must have the same name!
The interface and its Mapper profile must be in the same package!

8. Life Cycle and Scope

Lifecycle, and scope, are critical because incorrect use can cause very serious concurrency problems.

sqlSessionFactoryBuilder:

Once the SqlSessionFactory has been created, it is no longer needed
Local variables

sqlSessionFactory:

/ To put it plainly, think of it as: database connection pool
Once created, the.SqlSessionFactory should exist for as long as the application is running, and there is no reason to discard it or recreate another instance.
. Therefore, the best scope for SqlSessionFactory is the application scope.
The simplest is to use the singleton mode or the static singleton mode.

sqlSession

A request to connect to the connection pool!
. An instance of SqlSession is not thread-safe and therefore cannot be shared, so its best scope is the request or method scope.
/ You need to shut down immediately after you run out, or your resources will be occupied!

Each Mapper here represents a specific business!

5. Resolve the inconsistency between attribute name and field name

1. Questions

Fields in the database

Create a new project, copy previous, test for inconsistencies in entity class fields

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

There was a problem with the test:

Analysis Reason:

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

Solution:

Alias

select id,name,pwd as password from mybatis.user where id = #{id}

2.resultMap

In the database: id name pwd
 In the entity class: id name password

<!--    Result Set Mapping-->
    <resultMap id="UserMap" type="User">
<!--       column Corresponding to the field in the database, property Property of the corresponding entity class, by result Map Map it -->
        <result column="id" property="id"></result>
        <result column="name" property="name"></result>
        <result column="pwd" property="password"></result>
    </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
ResultMap is designed so that you don't need to configure explicit result mappings at all for simple statements, but for more complex statements, you just need to describe their relationships.
The best thing about Resu1tMap is that although you already know it well, you don't need to explicitly use it at all.
If only the world was always so simple.

6. Log

6.1. Log Factory

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

Once: sout. Debug

Now: Log Factory!

logImpl	Specify the specific implementation of the log used by MyBatis, which will be automatically searched if not specified.	SLF4J \| LOG4J \| LOG4J2 \| JDK_LOGGING \| COMMONS_LOGGING \| STDOUT_LOGGING \| NO_LOGGING	Not set

SLF4J
LOG4J (Master)
LOG4J2
JDK_LOGGING
COMMONS_LOGGING
STDOUT_LOGGING (Master)
NO_LOGGING

Use that log implementation specifically in Meybatis and set it in Settings!

STDOUT_LOGGING Standard Log Output

In the mybatis core configuration file, configure our log!

<!-- Implementation Configuration Log for Standard Log Files-->
    <settings>
        <setting name="logImpl" value="STDOUT_LOGGING"/>
    </settings>

6.2,Log4j

What is Log4j?

.Log4j is an open source project for Apache. By using Log4j, we can control the delivery of log information to consoles, files, GUI components
/ We can also control the output format of each log;
? By defining the level of each log information, we can control the generation of the log in more detail.
Configure flexibly through a configuration file without modifying the applied code.

1. Import the package of log4j first

<dependencies>
    <dependency>
        <groupId>log4j</groupId>
        <artifactId>log4j</artifactId>
        <version>1.2.12</version>
    </dependency>
</dependencies>

2.log4j.properties

#Export log information at the DEBUG level to two destinations console and file, defined in the following code
log4j.rootLogger=DEBUG,console,file

#Settings for 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 for file output
log4j.appender.file = org.apache.log4j.RollingFileAppender
log4j.appender.file.File=./log/Wang.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

3. Configure log4j as the implementation of logging

<!--    Configuration Log-->
    <settings>
        <setting name="logImpl" value="log4j"/>
    </settings>

4.Use of Log4j!, Direct test runs the query just now

Simple use

1. In the class to use Log4j, import the package

import org.apache.log4j.Logger;

2. Log object with class parameter of current class

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

3. Log level

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

7. Paging

Think: Why page breaks?

Reduce data processing

7.1 Use Limit Paging (Physical Paging)

Grammar:  select * from mybatis.user limit startIndex,pageSize;
      select * from mybatis.user limit 3; [Start Page, Page Size)

Paging with Mybatis, Core SQL

1. Interface

//Paging Query
List<User> getUserLimit(Map<String,Integer> map);

2.Mapper.xml

<resultMap id="UserMap" type="User"/>
    
    
    <select id="getUserLimit" resultMap="UserMap">
        select * from mybatis.user limit #{startIndex},#{pageSize}
    </select>

3. Testing

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

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


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

    map.put("startIndex",1);
    map.put("pageSize",3);


    List<User> userLimit = mapper.getUserLimit(map);

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


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

7.2, RowBounds Paging (Logical Paging)

Paging is no longer implemented using SQL

1. Interface

//rowBounds Paging
List<User> getUserRowBounds();

2.mapper.xml

<select id="getUserRowBounds" resultMap="UserMap">
    select * from mybatis.user
</select>

3. Testing

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

    RowBounds rowBounds = new RowBounds(1, 2);

    List<User> mapper = sqlSession.selectList("com.Wang.mapper.UserMapper.getUserRowBounds",null,rowBounds);


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




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

7.3 MyBatis Paging Plugin

PageHelper

Just know, in case the architect of the company says in the future, you need to know what it is!

8. Develop using annotations

8.1. Interface-oriented programming

-You've all learned object-oriented programming and interfaces before, but in real development, we often choose Interface-oriented programming
- root cause: decoupling, expanding, improving reuse, layered development, regardless of the specific implementation, everyone adheres to common standards, making development easier and more standardized
- In an object-oriented system, the various functions of the system are accomplished by the collaboration of many different objects. In this case, how each object implements itself is less important to system designers.
-Collaboration between objects is the key to system design. Communication between different classes and interaction between modules are important considerations 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 interfaces

-Interfaces should be understood at a deeper level by the separation of definition (specifications, constraints) from implementation (the principle of separation of name from reality).
-The interface itself reflects the abstract understanding of the system by the system designer.
-There should be two types of interfaces:
-The first is the abstraction of an individual, which can correspond to an abstract class;
-The second is the abstraction of an individual's aspect, that is, the formation of an abstract surface;
-A body may have more than one abstract surface. An abstract body is different from an abstract surface.

Three Directions of Difference

-Object-oriented means that when we consider a problem, we consider its properties and methods in terms of objects.
-Process-oriented means that when we consider a problem, we consider its implementation in terms of a specific process (transaction process).
-Interface design and non-interface design are for reuse technology and are not a problem with object-oriented (process). More reflect is the overall system architecture

8.2. Development using annotations

1. Annotations are implemented on interfaces

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

2. You need to bind interfaces in the core profile again!

//Binding interface
    <mappers>
        <mapper class="com.Wang.mapper.UserMapper"/>
    </mappers>

3. Testing

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

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

    List<User> users = mapper.getUsers();

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


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

Essential: Reflection Mechanism

Implement the bottom level: dynamic proxy!

8.3 CRUD

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

package com.Wang.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;

//Tool class to get sqlSessionFactory object--for production--"sqlSession
public class MybatisUtils {

    private static SqlSessionFactory sqlSessionFactory;

    static {

        //The first step in using Mybatis ---) is to get the sqlSessionFactory object
        try {
            String resource = "mybatis-config.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    //Now that we have the SqlSessionFactory, as the name implies, we can get an instance of SqlSession from it.
    // SqlSession provides all the methods required to execute SQL commands in a database.
    public static SqlSession getSqlSession(){
        //Set up automatic submission
       return sqlSessionFactory.openSession(true);
    }
}

Write interfaces and add comments

package com.Wang.mapper;

import com.Wang.pojo.User;
import org.apache.ibatis.annotations.*;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

public interface UserMapper {

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

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


    @Insert("insert into mybatis.user(id, name, pwd) VALUE (#{id},#{name},#{pwd})")
    int addUser(User user);

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

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


}

Test Class

package com.Wang.mapper;

import com.Wang.pojo.User;
import com.Wang.utils.MybatisUtils;
import org.apache.ibatis.session.RowBounds;
import org.apache.ibatis.session.SqlSession;

import org.junit.Test;

import java.util.HashMap;
import java.util.List;

public class UserMapperTest {


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

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

        int i = mapper.deleteUser(7);

        if (i>0){
            System.out.println("ok");
        }


        sqlSession.close();
    }


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

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

        int Zhou Xiaojie = mapper.updateUser(new User(7, "Zhou Xiaojie", "000000"));

        if (Zhou Xiaojie>0){
            System.out.println("ok");
        }


        sqlSession.close();
    }


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

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

        int user = mapper.addUser(new User(7, "Ha ha ha", "111111"));

        if (user>0){
            System.out.println("ok");
        }


        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 getUsers(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();

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

        List<User> users = mapper.getUsers();

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


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

[Note: We must talk about interface registration binding to our core profile!]

<!--    Every last Mapper.XML Need to be in Mybatis Register in Core Profile!-->
    <mappers>
        <mapper class="com.Wang.mapper.UserMapper"/>
    </mappers>

About the @Param() comment

A parameter of a basic type or a String type that needs to be added. A reference type that does 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 property name we set here in @Param()!

#{} differs from ${}

1. Compilation process

#{} is a placeholder: dynamic parsing - > precompilation - > execution
${} is the stitcher: dynamic parsing - > compilation - > execution

2. Whether to add single quotation marks automatically

***#{}* corresponds to a variable * that is * automatically quoted *
***${}* corresponds to a variable * without * single quotation marks*

3. Security

***#{}* * Prevents sql injection
***${}* * cannot * prevent sql injection

4. Mybatis defaults are different

*#{}* Default *arg0*, *arg1*, *arg2 or 0, 1*
*${}* Default *param1*, *param2*, *param3*

3. How to select #{} and ${}

Use #{} where you can #{} and ${} as little as possible

Table name as parameter or order by sort with ${}

Parameters are annotated with @Param(""). The @Param annotation is used to name the parameter so that the value of the parameter can be obtained by name (which is equivalent to adding a layer of density). Properly passing the parameter into the sql statement (usually by #{}, ${} can cause sql injection problems). The following:

Role selectById(@Param("id") String id);
       List<Role> selectByNameAndOrgId(@Param("name") String name, @Param("orgId") String orgId);

9.Lombok (used to simplify overrides of methods such as get set in entity classes.., simplify code)

Introduction to Lombok

Lombok is a Java development plug-in that allows Java developers to eliminate lengthy and cumbersome code in business projects, especially for simple Java model objects (POJOs), by defining annotations. Using Lombok plug-ins in a development environment saves Java developers a lot of time from duplicate builds, such as hashCode and equals, as well as accessor s and ToString s for various business object models. For these methods, it automatically generates them for us during source code compilation, without degrading program performance as reflected.

Steps to use:

1. Install Lombok plug-in in IDEA!

2. Import lombok's jar package into the project

<dependencies>
    <!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <version>1.18.20</version>
    </dependency>
</dependencies>

3.

@Getter and @Setter
@FieldNameConstants
@ToString
@EqualsAndHashCode
@AllArgsConstructor, @RequiredArgsConstructor and @NoArgsConstructor
@Log, @Log4j, @Log4j2, @Slf4j, @XSlf4j, @CommonsLog, @JBossLog, @Flogger, @CustomLog
@Data
@Builder
@SuperBuilder
@Singular
@Delegate
@Value
@Accessors
@Wither
@With
@SneakyThrows
@val
@var
experimental @var
@UtilityClass
Lombok config system
Code inspections
Refactoring actions (lombok and delombok)

Explain:

@Data: Parameter construction, get,set,tostring,hashcode，equals
     
@AllArgsConstructor  //Parametric construction
     
@NoArgsConstructor  //Parametric construction
    
Lombok Common Note Description
.@NonNull;A non-null check is automatically performed on the method parameter before it is used and thrown as null NPE (NullPointerException)
.@Cleanup:Automatically manages resources, which are cleaned up and generated before local variables are used, and before exiting immediately after execution within the current variable range try-finally Code Close Stream. @Getterl@Setter:Use on attributes instead of handwriting on your own setter and getter Method, you can also specify the scope of access
. @ToString:Used on classes, can be overridden automatically toString Method
@EqualsAndHashCode:Used on classes, automatically generated equals Methods and hashCode Method
·@NoArgsConstructor.@RequiredArgsConstructor and @aAllArgsConstructor:Used on classes to automatically generate parameterless constructs and use parameterized constructors for all parameters.·@ata:Used on a class, equivalent to using both@ToString,@EqualsAndHashCode,@Geter,@Setter and@RequiredArgsConstrutor These notes, for POJO Classes are useful
. @Value:For classes, yes@Data Invariant form, equivalent to adding an attribute final Statement, provided only getter Method instead of providing setter Method· @SneakyThrows:Automatically throw checked exceptions without explicitly using methods throws Sentence
. @Synchronized:Used on methods to declare methods synchronous and lock them automatically
. @Getter(lazy=true):Can replace classic Double Check Lock boilerplate code

    
@Data
@AllArgsConstructor
@NoArgsConstructor
@EqualsAndHashCode
@Getter
@Setter
@ToString

Advantages and disadvantages of Lombok

Advantage:

1. Constructors, getter/setter, equals, hashcode, toString can be generated automatically in the form of annotations to improve the efficiency of development.

2. Keep your code simple and don't focus too much on the appropriate method

3. When attributes are modified, it also simplifies the maintenance of getter/setter methods generated for these attributes, etc.

Disadvantages:

1. Overload of multiple parameter constructors is not supported

2. Although the manual creation of getter/setter methods is omitted, the readability and integrity of the source code are greatly reduced and the comfort of reading the source code is reduced.

summary

Although Lombok has many advantages, Lombok is more like an IDE plug-in and projects need to rely on the corresponding jar packages. Lombok relies on jar packages because it is compiled with annotations. Why is it similar to a plug-in? Because eclipse or Intelli] IDEA needs to have plug-ins installed when it is used, byte code generation is changed by manipulating AST (Abstract Grammar Tree) at compiler time, which means it is changing java grammar.
It is not a runtime feature like spring's dependency injection or mybatis's ORM - it is a compile-time feature. What I personally feel most uncomfortable about here is the dependence on plug-ins! Because Lombok only saves some of the hassles of manually generating code, IDE s also have shortcuts to help generate getter/setter and other methods, which are very convenient.
Knowingly, a great God has expressed some ideas about Lombok:
This is a low-level, fun plug-in and is not recommended. JAVa has developed to this day, and various plug-ins are emerging one after another. How can you identify the advantages and disadvantages of each plug-in? Optimize your design from architecture, improve application performance, and achieve highly encapsulated scalability... Like lombok, plug-ins like this are not just plug-ins, they change how you write source code. In fact, how do you write with less code? If the JAvA family is full of such things, it's just a piece of metal-colored shit that sooner or later will be replaced by other languages.
Although rough, it's not. Imagine a project with a lot of plug-ins like Lombok, which I think would really greatly reduce the comfort of reading the source code. Although it is highly recommended to write some business code on a property getter/setter, in many years of project practice, adding a little business code to a getter/setter can greatly simplify the code for some business scenarios. The so-called "trade-off" may be to discard certain specifications and obtain great convenience at this time.
I now firmly believe that any programming language or plug-in is just a tool, and that even the most powerful tool is the person who uses it, just like Millet plus Rifle can win an airplane cannon. Combined with the specific business scenario and the actual situation of the project, there is no need to blindly pursue high-tech, only suitable is the kingship.
Lombok has its unique advantages, but also has its shortcomings to avoid. Knowing its advantages and disadvantages, it is the kingdom to use it flexibly in the actual battle.

10. Many-to-one processing

Multiple students, one teacher
On the student side, associate...multiple students, associate a teacher [many-to-one]
For teachers, set, a teacher, there are many students [one-to-many]

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, 'Teacher 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', 'Little Red', '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', 'Xiaowang', '1');

Test environment setup

1. Import lombok

_. New Entity Class Teacher, Student3. Set up Mapper interface

4. Set up Mapper.XML file

5. Bind and register our Mapper interface or file in the core configuration file! [Many ways, choose what you like]

6. Test if the query succeeds!

Nested Processing by Query

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.Wang.mapper.StudentMapper">

<!--    thinking:
1．Query all student information
2．Based on the students'queries tid，Find a corresponding teacher!
        select s.id,s.name.t.name from mybatis.student s,mybatis.teacher t, 
        where s.tid = t.id

-->
    <select id="getStudent" resultMap="StudentTeacher">
        select * from student
    </select>
    <resultMap id="StudentTeacher" type="Student">
        <result property="id" column="id"></result>
        <result property="name" column="name"></result>
        <!--    Complex attributes, we need to deal with them separately
        object: association
        aggregate: collection
        -->
        <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
    </resultMap>

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

    
    
    
</mapper>

Nested processing by result (simpler, sql statements don't need to be split)

<!--    Nested Processing by Result-->
    <select id="getStudent2" resultMap="StudentTeacher2">
        select s.id sid,s.name sname,t.name tname
        from mybatis.student s,mybatis.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 Mysql many-to-one queries:

Subquery
Joint Table Query

11. One-to-many processing

For example: a teacher has more than one student! For teachers, it's a one-to-many relationship!

1. Set up the environment just like before

Entity Class

package com.Wang.pojo;


import lombok.Data;

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

    private int tid;
}

package com.Wang.pojo;


import lombok.Data;

import java.util.List;

@Data
public class Teacher {
    private int id;
    private String name;

    //One teacher for more than one student
    private List<Student> students;
}

Nested query by result

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.Wang.mapper.TeacherMapper">

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

<!--    The result set here is still a teacher-->
    <resultMap id="StudentTeacher" type="Teacher">
        <result property="id" column="tid"/>
        <result property="name" column="tname"/>
        <!--Complex attributes, we need to deal with them separately
           object:association
           aggregate:collection
           javaType=""Specify the type of attribute!
           Generic information in a collection, we use ofType Obtain
           -->

<!--When you get here, you will press student To query, so one should be returned student Of-->
        <collection property="students" ofType="Student">
            <result property="id" column="sid"/>
            <result property="name" column="sname"/>
            <result property="tid" column="tid"/>
        </collection>
    </resultMap>






</mapper>

Summary

1. association-Association

2. 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 a List or collection, the constraint type in a generic!

Note:

Keep SQL readable and as easy as possible to understand

Pay attention to one-to-many and many-to-one attribute names and fields!

/ If the problem is not good enough to troubleshoot errors, log can be used and Log4j is recommended

Slow SQL 1s 1000s
Interview frequency

.Mysql engine

. lnnoDB underlying principles

. Index

.Index optimization!

12. Dynamic SQL

What is dynamic SQL: Dynamic SQL means that different SQL statements are generated according to different conditions and can be completely rid of this pain by using the feature of dynamic SQL.

dynamic sQL Elements and JSTL Or based on similar XML Text processors are similar.

stay MyBatis Previous versions had many elements that took time to understand.

MyBatis 3 Element categories have been greatly simplified, and now you only need to learn half of the original elements.

MyBatis Use powerful based OGNL Expression to eliminate most other elements.

if
choose (when,otherwise)

 trim (where，set)
foreach

Set up 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 'Blog Author',
`create_time` DATETIME NOT NULL COMMENT 'Creation Time',
`views` INT(30) NOT NULL COMMENT 'Views'
)ENGINE=INNODB DEFAULT CHARSET=utf8

Create an infrastructure project

1. Guide Pack

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <parent>
        <artifactId>mybatis-study</artifactId>
        <groupId>com.Wang</groupId>
        <version>1.0-SNAPSHOT</version>
    </parent>
    <modelVersion>4.0.0</modelVersion>

    <artifactId>mybatis-demo08</artifactId>



    <dependencies>
        <!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.20</version>
        </dependency>
        <dependency>
            <groupId>org.junit.jupiter</groupId>
            <artifactId>junit-jupiter</artifactId>
            <version>RELEASE</version>
            <scope>compile</scope>
        </dependency>
    </dependencies>




    <!--stay build Configuration in resources，To prevent our resource export from failing-->
    <build>
        <resources>
            <resource>
                <directory>src/main/resources</directory>
                <includes>
                    <include>**/*.properties</include>
                    <include>**/*.xml</include>
                </includes>
            </resource>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.properties</include>
                    <include>**/*.xml</include>
                </includes>
                <filtering>true</filtering>
            </resource>
        </resources>
    </build>

</project>

2. Write a configuration file

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

    <!--    Introduce an external configuration file, which is db.properties-->
    <properties resource="db.properties">

    </properties>
    
<!--    Configuration Log-->
    <settings>
        <setting name="logImpl" value="STDOUT_LOGGING"/>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>

<!--    Entity classes can be aliased-->
    <typeAliases>
        <package name="com.Wang.pojo"/>
    </typeAliases>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>
<!--    Every last Mapper.XML Need to be in Mybatis Register in Core Profile!-->
    <mappers>
      <mapper class="com.Wang.mapper.BlogMapper"/>
    </mappers>
</configuration>

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

    <!--    Introduce an external configuration file, which is db.properties-->
    <properties resource="db.properties">

    </properties>
    
<!--    Configuration Log-->
    <settings>
        <setting name="logImpl" value="STDOUT_LOGGING"/>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>

<!--    Entity classes can be aliased-->
    <typeAliases>
        <package name="com.Wang.pojo"/>
    </typeAliases>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>
<!--    Every last Mapper.XML Need to be in Mybatis Register in Core Profile!-->
    <mappers>
      <mapper class="com.Wang.mapper.BlogMapper"/>
    </mappers>
</configuration>

3. Write entity classes

package com.Wang.pojo;

import lombok.Data;

import java.util.Date;

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

UUID usage

package com.Wang.utils;

import org.junit.jupiter.api.Test;

import java.util.UUID;

public class IdUtils {
    public static String getId(){
        return UUID.randomUUID().toString().replaceAll("-","");
    }
    @Test
    public void test(){
        System.out.println(IdUtils.getId());
        System.out.println(IdUtils.getId());
        System.out.println(IdUtils.getId());
        System.out.println(IdUtils.getId());
    }
}

4. Write entity classes corresponding to Mapper interfaces and Mapper.XML file

package com.Wang.mapper;

import com.Wang.pojo.Blog;

public interface BlogMapper {
    int addBlog(Blog blog);
}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.Wang.mapper.BlogMapper">

    <insert id="addBlog" parameterType="blog">
        insert into mybatis.blog (id,title,author,create_time,views)
        value (#{id},#{title},#{author},#{createTime},#{views});
    </insert>


</mapper>

If (Find Official Website, see Optimize)

<select id="queryBlog" 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>

if code optimization

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

choose,when,otherwise

Select one of several conditions to use

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

trim,where,set

Use of where

The where element inserts a "WHERE" clause only if the subelement returns anything. Moreover, if a clause begins with "AND" or "OR", the where element also removes them.

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

Use of set

The set element dynamically inserts the SET keyword at the beginning of the line and eliminates extra commas introduced when assigning values to columns using conditional statements.

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

The so-called dynamic SQL is essentially a SQL statement, but we can execute a logical code at the SQL level

where , set , choose , when

sql fragment

Sometimes, we may extract parts of some functions for easy reuse

1. Use SQL tags to extract common parts

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

2. Use the lnclude tag reference where you need it

<select id="queryBlog" parameterType="map" resultType="Blog">
    select * from mybatis.blog
    <where>
        <include refid="id-title-author"></include>
    </where>
</select>

Matters needing attention:

It is best to define SQL fragments based on a form!
Do not have where Tags

foreach

<!--
select * from mybatis.blog where 1=1 and (id = 1 or id = 2 or id = 3);
Now we're delivering an omnipotent message map ,this map There may 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>

Test:

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

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

    HashMap hashMap = new HashMap();

    ArrayList list = new ArrayList();

    list.add(1);
    list.add(3);
    list.add(3);


    hashMap.put("ids",list);


    List<Blog> blogs = mapper.queryBlogForeach(hashMap);

    for (Blog blog : blogs) {
        System.out.println(blog);
    }


    sqlSession.close();
}

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

Suggestions:

Now write out the complete SQL in Mysql and modify it accordingly to make our dynamic SQL implementation universal.

13. Caching (Understanding)

query:   Connect to the database,     Consumption of resources!
    
The result of a query, giving him temporary access to a place!-->Memory:cache
    
When we query the same data again, go directly to the cache instead of the database

13.1. Introduction

1. What is Cache?

Temporary data in memory exists.
By putting the data that users often query in the cache (memory), users can query the data from the cache instead of from the disk (relational database data files), which improves the query efficiency and solves the performance problem of high concurrent systems.

2. Why use caching?

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 queried frequently and changes infrequently.

13.2, Mybatis Cache

MyBatis includes a very powerful query cache feature that makes it very easy to customize and configure the cache. Caching can greatly improve query efficiency.

Two levels of cache are defined by default in MyBatis system: first-level cache and second-level cache
By default, only one level of cache is turned on. (SqlSession level cache, also known as local cache). Second-level caches need to be manually turned on and configured; they are namespace-based caches.
To improve scalability, MyBatis defines the cache interface Cache. We can customize the secondary cache by implementing the Cache interface

13.3, Level 1 cache.

A first-level cache is also called a local cache:

The data queried during the same session as the database is placed in the local cache.
If you need to get the same data later, you can get it directly from the cache without having to query the database.

Test steps:

1. Open the log!

_. Test querying the same record twice in a Sesion

3. View log output

Cache failure:

1. Query different things

2. Add or delete operations, may change the original data, so the cache must be refreshed!

3. Query different Mappers. XML

4. Clean up the cache manually!

Summary:

The first level cache is turned on by default and only works in one SqlSession, that is, to get the segment connected to a closed connection!

Level-1 cache is a Map.

13.4, Level 2 Cache

Open tab as

Second-level caches are also called global caches. Because the scope of first-level caches is too low, second-level caches are created.
A namespace-level cache, a namespace, corresponding to a secondary cache;

Working mechanism

When a session queries a piece of data, it is placed in the first level cache of the current session.
If the current session is closed, the corresponding first-level cache for that session will be gone. But what we want is that the session is closed and the data in the first level cache is saved in the second level cache.
A new session queries information to get content from the secondary cache.
Different mapper s find data in their own cache (map);

Steps:

1. Turn on global cache

<!--    Configuration Log-->
    <settings>
        <setting name="logImpl" value="STDOUT_LOGGING"/>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
<!--        Display Open Global Cache-->
        <setting name="cacheEnabled" value="true"/>
    </settings>

2. Open in Mapper to use secondary cache

<!--At present Mapper.xm1 Using a secondary cache-->
<cache readOnly="true"/>

You can also customize parameters

<cache
  eviction="FIFO"
  flushInterval="60000"
  size="512"
  readOnly="true"/>

3. Testing
1.Problem: We need to serialize the entity class! Otherwise you will make a mistake!

Summary:

As long as the second level cache is turned on, it will be valid under the same Mapper. All data will be placed in the first level cache first.
Only when the session is committed or closed will it be committed to the secondary buffer!

<cache/> Only in the same namespace Lower valid and required<cache readOnly="true"/>Use this way, otherwise error will occur

13.5. Caching principles

13.6, Custom Cache-ehcache

 Ehcache Is a widely used open source java Distributed caching. Mainly for generic caching

To use ehcache in your program, first guide the 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>

Specify using our ehcache cache implementation in mapper!

<!--At present Mapper.xm1 Using a secondary cache-->
<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: For the cache path, ehcache This property 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="java.io.tmpdir/Tmp_EhCache"/>
    <!--
       defaultCache: Default cache policy when ehcache This caching policy is used when a 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 set, timeout Will not work.
      overflowToDisk:Whether to save to disk when the system crashes
      timeToIdleSeconds:Sets the allowable idle time (in seconds) for the object before it expires. Only if eternal=false Object is not permanently valid, optional property, default value is 0, that is, infinite idle time.
      timeToLiveSeconds:Sets the allowable lifetime (in seconds) for an object before it fails. The maximum time is between creation time and expiration time. Only if eternal=false Used when the object is not permanently valid, defaulting to 0.，That is, the object has an infinite lifetime.
      diskPersistent: Whether to cache virtual machine restart period 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). Default is 30 MB. each Cache They should all have their own buffer.
      diskExpiryThreadIntervalSeconds: Disk failure threads run at intervals of 120 seconds by default.
      memoryStoreEvictionPolicy: When reached maxElementsInMemory When restricted, Ehcache Memory will be cleaned up according to the specified policy. The default policy is LRU(Least recently used). You can set it to FIFO(FIFO) or LFU(Less used).
      clearOnFlush: Whether to clear when the maximum amount of memory is available.
      memoryStoreEvictionPolicy:Optional strategies are: LRU(Least recently used, default policy), FIFO(FIFO), LFU(Minimum visits).
      FIFO，first in first out，This is the most familiar, FIFO.
      LFU， Less Frequently Used，That's the strategy used in the example above. To be frank, it's the least used strategy ever. As mentioned above, a cached element has one hit Properties, hit The minimum value will be flushed out of the cache.
      LRU，Least Recently Used，The least recently used cached element has a timestamp, and when the cache is full and you need to make room to cache new elements, the element with the farthest timestamp from the current time in the existing cached element will be flushed out of the cache.
   -->
    <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"/>

</ehcache>

oupId>org.mybatis.caches
mybatis-ehcache
1.2.1



#### Specify using our ehcache cache implementation in mapper!

```xml
<!--At present Mapper.xm1 Using a secondary cache-->
<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: For the cache path, ehcache This property 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="java.io.tmpdir/Tmp_EhCache"/>
    <!--
       defaultCache: Default cache policy when ehcache This caching policy is used when a 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 set, timeout Will not work.
      overflowToDisk:Whether to save to disk when the system crashes
      timeToIdleSeconds:Sets the allowable idle time (in seconds) for the object before it expires. Only if eternal=false Object is not permanently valid, optional property, default value is 0, that is, infinite idle time.
      timeToLiveSeconds:Sets the allowable lifetime (in seconds) for an object before it fails. The maximum time is between creation time and expiration time. Only if eternal=false Used when the object is not permanently valid, defaulting to 0.，That is, the object has an infinite lifetime.
      diskPersistent: Whether to cache virtual machine restart period 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). Default is 30 MB. each Cache They should all have their own buffer.
      diskExpiryThreadIntervalSeconds: Disk failure threads run at intervals of 120 seconds by default.
      memoryStoreEvictionPolicy: When reached maxElementsInMemory When restricted, Ehcache Memory will be cleaned up according to the specified policy. The default policy is LRU(Least recently used). You can set it to FIFO(FIFO) or LFU(Less used).
      clearOnFlush: Whether to clear when the maximum amount of memory is available.
      memoryStoreEvictionPolicy:Optional strategies are: LRU(Least recently used, default policy), FIFO(FIFO), LFU(Minimum visits).
      FIFO，first in first out，This is the most familiar, FIFO.
      LFU， Less Frequently Used，That's the strategy used in the example above. To be frank, it's the least used strategy ever. As mentioned above, a cached element has one hit Properties, hit The minimum value will be flushed out of the cache.
      LRU，Least Recently Used，The least recently used cached element has a timestamp, and when the cache is full and you need to make room to cache new elements, the element with the farthest timestamp from the current time in the existing cached element will be flushed out of the cache.
   -->
    <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"/>

</ehcache>

Keywords: Lombok Maven Mybatis HashMap

Added by JRS on Mon, 17 Jan 2022 10:25:04 +0200

Programming VIP

Mybatis--Universal Map--mappers--Cache-LOG4J--MyBatis Paging Plug-in--Developed with Annotations---#{} Difference from ${}--Lombok--Many-to-Many Processing Dynamic SQL

MyBatis

1. Introduction

1.1, What is Mybatis

1.2. Persistence

1.3, Persistence Layer

1.4 Why do I need Mybatis?

2. First Mybatis program

2.1. Setting up environment

2.2. Create a module

2.3. Writing Code

parameterType and resultType description

2.4. Testing

3,CRUD

1,namespace

2,select

3,Insert

4,update

5,Delete

6. Analysis Errors

7. Universal Map

8. Think Questions

4. Configuration resolution

1. Core Profile

2. Environment Configuration

A key:

3. Properties

4. Type Aliases

5. settings

6. Other configurations

7. Mapper (mappers)

8. Life Cycle and Scope

sqlSessionFactoryBuilder:

sqlSessionFactory:

sqlSession

5. Resolve the inconsistency between attribute name and field name

1. Questions

2.resultMap

6. Log

6.1. Log Factory

STDOUT_LOGGING Standard Log Output

6.2,Log4j

1. Import the package of log4j first

2.log4j.properties

3. Configure log4j as the implementation of logging

4.Use of Log4j!, Direct test runs the query just now

7. Paging

7.1 Use Limit Paging (Physical Paging)

7.2, RowBounds Paging (Logical Paging)

7.3 MyBatis Paging Plugin

8. Develop using annotations

8.1. Interface-oriented programming

8.2. Development using annotations

8.3 CRUD

#{} differs from ${}

1. Compilation process

2. Whether to add single quotation marks automatically

3. Security

4. Mybatis defaults are different

3. How to select #{} and ${}

9.Lombok (used to simplify overrides of methods such as get set in entity classes.., simplify code)

Introduction to Lombok

Steps to use:

1. Install Lombok plug-in in IDEA!

2. Import lombok's jar package into the project

3.

Explain:

Advantages and disadvantages of Lombok

summary

10. Many-to-one processing

Test environment setup

Nested Processing by Query

Nested processing by result (simpler, sql statements don't need to be split)

11. One-to-many processing

1. Set up the environment just like before

Nested query by result

Summary

12. Dynamic SQL

Set up environment