1.Mybatis

Environmental Science:

• JDK 1.8
• Mysql 5.7
• maven 3.6.1
• IDEA

2. Introduction

2.1 what is MyBatis

• MyBatis is an excellent persistence layer framework
• MyBatis avoids almost all JDBC code and the process of manually setting parameters and obtaining result sets
• MyBatis can use simple XML or annotations to configure and map native information, and map the interface and Java entity class [plain old Java objects] into records in the database.
• MyBatis was originally an open source project ibatis of apache. In 2010, this project was migrated from apache to google code and renamed MyBatis.
• Moved to Github in November 2013
• Official documents of Mybatis: http://www.mybatis.org/mybatis-3/zh/index.html
• GitHub : https://github.com/mybatis/mybatis-3

2.2 persistence

• Persistence is a mechanism to convert program data between persistent state and transient state.

  • That is to save data (such as objects in memory) to a permanent storage device (such as disk). The main application of persistence is to store the objects in memory in the database, or in disk files, XML data files and so on.
  • JDBC is a persistence mechanism. File IO is also a persistence mechanism.
    In life: refrigerate fresh meat and thaw it when eating. The same is true of canned fruit.

• Why do you need persistence services? That is caused by the defect of memory itself

  • Data will be lost after memory power failure, but some objects cannot be lost anyway, such as bank accounts. Unfortunately, people can't guarantee that memory will never power down.
  • Memory is too expensive. Compared with external memory such as hard disk and optical disc, the price of memory is 2 ~ 3 orders of magnitude higher, and the maintenance cost is also high. At least it needs to be powered all the time. Therefore, even if the object does not need to be saved permanently, it will not stay in memory all the time due to the capacity limitation of memory, and it needs to be persisted to cache it to external memory.

2.3 durable layer

• What is persistence layer?
  • Code block that completes the persistence work. --- > Dao layer [DAO (Data Access Object)]
  • In most cases, especially for enterprise applications, data persistence often means saving the data in memory to disk for solidification, while the implementation process of persistence is mostly completed through various relational databases.
  • However, there is one word that needs special emphasis, that is, the so-called "layer". For the application system, the data persistence function is mostly an essential part. In other words, our system has a natural concept of "persistence layer"? Maybe, but maybe that's not the case. The reason why we want to separate the concept of "persistence layer" instead of "persistence module" and "persistence unit" means that there should be a relatively independent logical level in our system architecture, focusing on the implementation of data persistence logic
  • Compared with other parts of the system, this level should have a clear and strict logical boundary. [to put it bluntly, it is used to operate the existence of the database!]

2.4 why do you need Mybatis

• Mybatis is to help the program store data in the database and get data from the database
• The traditional jdbc operation has many repeated code blocks For example, the encapsulation of data extraction, the establishment and connection of database, etc. through the framework, the repeated code can be reduced and the development efficiency can be improved
• MyBatis is a semi-automatic ORM framework (object relationship mapping) - > object relationship mapping
• All things can still be done without Mybatis, but with it, all the implementation will be easier! There is no difference in technology
  Points, only the people who use this technology have high and low differences
• Advantages of MyBatis:
  • Easy to learn: it is small and simple in itself. There is no third-party dependency. The simplest installation is as long as two jar files + several sql mapping files are configured. It is easy to learn and use. Through documents and source code, you can fully master its design idea and implementation
  • Flexibility: mybatis does not impose any impact on the existing design of the application or database. sql is written in xml to facilitate unified management and optimization. All the requirements of operating the database can be met through sql statements.
  • Decouple sql and program code: separate business logic and data access logic by providing DAO layer, so as to make the design of the system clearer, easier to maintain and easier to unit test. The separation of sql and code improves maintainability.
  • Provide xml tags to support writing dynamic sql.
• Most importantly, many people use it! The company needs!

3.MyBatis first program

Thought process: build environment - > Import Mybatis - > write code - > test

3.1 code demonstration

1. Build experimental database

CREATE DATABASE `mybatis`;

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


INSERT INTO `user`(`id`,`name`,`pwd`) VALUES
(1,'Mad God','123456'),
(2,'Zhang San','abcdef'),
(3,'Li Si','987654');

1. New project
   1. Create a normal Maven project
   2. Delete src directory
2. Import MyBatis related jar package
   • Where can I find it?

<!--Import dependency-->
    <dependencies>
<!--        mysql drive-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>
<!--        mybatis-->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.4.5</version>
        </dependency>
<!--        junit-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>
</dependencies>

3.2 create a new module

1. Create a normal maven module

2. Write the MyBatis core configuration file (just view the Chinese document on the official website)

   • Under resources, create mybatis config 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">
     <!-- congiguration 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>
     

3. Write mybatis tool class

public class MybatisUtils {
    private static SqlSessionFactory sqlSessionFactory;

    static {
        try {
            // Step 1: get the mysessionfactory object using mysqlis
            String resource = "mybatis-config.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            SqlSessionFactory 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(){
        SqlSession sqlSession = sqlSessionFactory.openSession();
        return sqlSession;
    }
}

3.3 coding

• Create entity class

  • New package 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 + '\'' +
                    '}';
        }
    }
    

• Write Mapper interface

  • New interface UserDao(UserMapper)

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

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

  • Write mapper XML configuration file

  • New usermapper xml

    <?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 id="getUserList" resultType="com.kuang.pojo.User">
            select * from mybatis.user;
        </select>
    </mapper>
    

3.4 testing

junit test

package com.kuang.dao;

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

import java.util.List;

public class UserDaoTest {
    @Test
    public void test(){
        //Step 1: get SqlSession object
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        //Method 1: execute SQL
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        List<User> userList = userDao.getUserList();

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

4.CRUD operation

4.1namespace

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

4.2select

The select statement has many properties, and each SQL statement can be configured in detail

• id: is the method name in the corresponding namespace
• Resulttype: return value of SQL statement execution [complete class name or alias]
• parameterType: parameter type passed in SQL statement

Requirement: query users by id

1. Add corresponding method in UserMapper

public interface UserMapper {
    // Query all users
    List<User> getUserList();
}        

1. In usermapper Add Select statement to XML

<mapper namespace="com.kuang.dao.UserMapper">
    <select id="getUserList" resultType="com.kuang.pojo.User">
        select * from mybatis.user;
    </select>
</mapper>

1. Test in test class

@Test
    public void test(){
        //Step 1: get SqlSession object
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        //Method 1: execute SQL
        UserMapper userDao = sqlSession.getMapper(UserMapper.class);
        List<User> userList = userDao.getUserList();

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

    }

4.3insert

Requirement: add a user to the database

1. Add the corresponding method in the UserMapper interface

public interface UserMapper{
    // insert
    int addUser(User user);
}

1. In usermapper Add insert statement to XML

<insert id="addUser" parameterType="com.kuang.pojo.User">
        insert into mybatis.user(id, name, pwd) VALUES (#{id},#{name},#{pwd})
</insert>

1. Test in test class

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

        int res = mapper.addUser(new User(5, "Ha ha 2", "1234"));
        if(res > 0) {
            System.out.println("Insert successful");
        }
        // Commit transaction
        sqlSession.commit();
        sqlSession.close();
    }

Note: adding, deleting, and modifying operations require transaction submission

4.4update

Requirement: modify user information

1. Add the corresponding method in the UserMapper interface

public interface UserMapper{
    // Modify user
    int updateUser(User user);
}

1. In usermapper Add update statement to XML

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

1. Test in test class

@Test
    public void updateUser(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        int res = mapper.updateUser(new User(4, "ha-ha", "123123"));
        if(res > 0){
            System.out.println("Modified successfully");
        }

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

4.5delete

Requirement: delete a user according to id

1. Add the corresponding method in the UserMapper interface

public interface UserMapper{
    // Delete a user
    int deleteUser(int id);
}

1. In usermapper Add update statement to XML

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

1. Test in test class

@Test
public void deleteUser(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    int res = mapper.deleteUser(4);
    if(res > 0){
        System.out.println("Deleted successfully");
    }

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

4.6 universal Map

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

1. Add corresponding method in UserMapper

public interface UserMapper{
    // Omnipotent map
    int addUser2(Map<String, Object> map);
    User getUserById2(Map<String,Object> map);
}

1. In usermapper Add insert statement to XML

<insert id="addUser2" parameterType="map">
        insert into mybatis.user(id, name, pwd) VALUES (#{id},#{name},#{pwd})
</insert>
<select id="getUserById2" parameterType="map" resultType="com.kuang.pojo.User">
        select * from mybatis.user where id = #{helloid} and name = #{name};
</select>

1. Test in test class

  // Pass the key of map
@Test
    public void addUser2(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        HashMap<String, Object> map = new HashMap<>();
        map.put("id",5);
        map.put("name","La La La");
        map.put("pwd","123456");
        sqlSession.close();
    }

@Test
    public void getUserById2(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        Map<String, Object> map = new HashMap<>();
        map.put("helloId",1);
        sqlSession.close();
    }

Transfer parameters from Map and directly take out the key from sql!

Pass parameters to the object and directly get the attribute of the object in sql!

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

Map or annotation is used for multiple parameters.

4.7 summary

• All addition, deletion and modification operations need to commit transactions!
• Sometimes, according to business requirements, you can consider using map to pass parameters
• In order to standardize the operation, we try to write the Parameter parameter and resultType in the SQL configuration file

4.8 fuzzy query

1. Add corresponding method in UserMapper

public interface UserMapper{
    List<User> getUserLike(String value);
}

1. In usermapper Add insert statement to XML

<select id="getUserLike" resultType="com.kuang.pojo.User">
        select * from mybatis.user where name like #{value}
</select>

1. Test in test class

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

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

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

5. Configuration analysis

5.1 core configuration file

• mybatis-config.xml system core configuration file
• The contents that can be configured are as follows

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 (manufacturer identification)
mappers(Mapper)
<!-- Pay attention to the order of element nodes! If the order is wrong, an error will be reported -->

5.2 environment configuration

Environment configuration - environments

• Mybatis can be configured to adapt to a variety of environments
• However, remember that although multiple environments can be configured, only one environment can be selected for each SqlSessionFactory instance
• The default transaction manager of Mybatis is JDBC, and the connection pool is POOLED

5.3 properties

Properties - properties

• We can reference the configuration file through the properties attribute
• These properties are externally configurable and can be dynamically replaced. 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

dbproperties

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

In the core configuration file mybatis config XML

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

• We can import external files directly
• If two files have the same field, the external configuration file is preferred

Alias type

• 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

The first way

• Configure in the core configuration file

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

The second way

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

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

• After aliasing in this way, the default alias is the class name of this class, with a lowercase initial!

difference

• When there are few entity classes, use the first method

• 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 need to add an annotation on the entity class, the alias is the annotation value

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

5.5mappers

MapperRegistry: register and bind our Mapper file

Mode 1:

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

Method 2: register with class file binding

<mappers>
    <mapper class = "com.kuang.dao.userMapper" />
</mappers>

Precautions for mode 2:

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

Method 3: register and bind with scanning package

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

Precautions for mode 3:

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

Ensure the UserMapper interface and UserMapper XML is consistent and placed under the same package

5.6 life cycle

Lifecycle and scope are critical, because incorrect use can lead to very serious concurrency problems

[the external chain picture transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the picture and upload it directly (img-sjl63phj-1621233180548) (crazy God says Mybatis.assets/image-20210513091906050.png)]

SqlSessionFactoryBuilder:

• Once SqlSessionFactory is created, it is no longer needed
• Equivalent to a 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 shut it down immediately after it is used up, otherwise the resources will be occupied

[the external chain picture transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the picture and upload it directly (img-esljx9zp-1621233180549) (crazy God says Mybatis.assets/image-20210513092900524.png)]

Each Mapper in this represents a specific business

6.ResultMap

Result set mapping

Problem to be solved: the attribute name and field name are inconsistent

6.1 null query

1. View previous database field names

[the external chain picture transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the picture and upload it directly (img-5r3pxp9o-1621233180549) (crazy God says Mybatis.assets/image-20210513094101461.png)]

1. Design of entity class in Java

public class User {
	private int id; //id
	private String name; //full name
	private String password; //The password is different from the database!
	//structure
	//set/get
	//toString()
}

1. UserMapper interface

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

1. mapper mapping file

<select id="selectUserById" resultType="user">
	select * from user where id = #{id}
</select>

1. test

@Test
public void testSelectUserById() {
SqlSession session = MybatisUtils.getSession(); //Get SqlSession connection
UserMapper mapper = session.getMapper(UserMapper.class);
User user = mapper.selectUserById(1);
System.out.println(user);
session.close();
}

result

• User{id=1,name = 'crazy God', password='null '}

analysis

• select * from user where id = #{id} can be regarded as
• select id,name,pwd from user where id = #{}
• mybatis will find the set value of the corresponding column name in the corresponding entity class according to the column name of these queries (the column name will be converted to lowercase, and the database is not case sensitive). Because setPwd() cannot be found, the password returns null

6.2 solutions

Scheme 1: specify an alias for the column name, which is consistent with the attribute name of the java entity class

<select id="selectUserById" resultType="User">
	select id , name , pwd as password from user where id = #{id}
</select>

Scheme 2: use result set mapping ➡ ResultMap (recommended)

<resultMap id="UserMap" type="User">
	<!-- id As primary key -->
	<id column="id" property="id"/>
	<!-- column Is the column name of the database table , property Is the attribute name of the corresponding entity class -->
	<result column="name" property="name"/>
	<result column="pwd" property="password"/>
</resultMap>

<select id="selectUserById" resultMap="UserMap">
	select id , name , pwd from user where id = #{id}
</select>

7. Log factory

Thinking: when we test SQL, if we can output SQL on the console, can we have faster troubleshooting efficiency?

The built-in log factory of Mybatis provides log function. The specific log implementation includes the following tools:

• SLF4J
• Apache Commons Logging
• Log4j 2
• Log4j
• JDK logging

The specific log implementation tool selected is determined by MyBatis's built-in log factory. It uses the first found (in the order listed above). If none is found, logging is disabled.

Standard log implementation

Specify which logging implementation MyBatis should use. If this setting does not exist, the logging implementation will be found automatically

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

7.1Log4j

Use steps:

1. Import log4j's package

• It can be found in maven warehouse

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

1. Configuration file writing

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

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

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

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

1. Setting setting log implementation

• Implemented in the core configuration file

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

1. Test in test class

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

7.2limit paging

Think: why pagination?

• When learning persistence layer frameworks such as mybatis, you will often add, delete, modify and query data, and the most used is to query the database
• If we query a large amount of data, we often use paging to query, that is, we process a small part of the data every time, so the pressure on the database is within the controllable range

Pagination using limit syntax

#grammar
SELECT * FROM table LIMIT stratIndex，pageSize
SELECT * FROM table LIMIT 5,10; 
# //Retrieving record lines 6-15
#In order to retrieve all record lines from an offset to the end of the Recordset, you can specify the second parameter as - 1
SELECT * FROM table LIMIT 95,-1; 
# //Retrieve record line 96 last

#If only one parameter is given, it indicates the maximum number of record lines returned:
SELECT * FROM table LIMIT 5; 
# //Retrieve the first 5 record lines
#In other words, LIMIT n is equivalent to LIMIT 0,n.

1. Add method in interface

public interface UserMapper {
    // paging
    List<User> getUserByLimit(Map(String,Integer) map);
}

1. In usermapper Register in XML

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

1. Test in test class

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

    HashMap<String, Integer> map = new HashMap<>();
    map.put("startIndex",0);
    map.put("pageSize",2);
    List<User> userList = mapper.getUserByLimit(map);
    for (User user : userList){
        System.out.println(user);
    }
}

8. Development using annotations

• The initial configuration information of mybatis is based on XML, and the mapping statement (SQL) is also defined in XML. MyBatis 3 provides a new annotation based configuration
• sql types are mainly divided into
  • @select()
  • @update()
  • @Insert()
  • @delete()

Note: mapper is not required for annotation development XML Mapping File

Steps:

1. Add annotations to the interface

public interface UserMapper {
    @Select("select * from user")
    List<User> getUsers();
}

1. Inject in the core configuration file

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

1. Test in test class

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

8.1CRUD

insert

1. Add annotations to the interface

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

1. Inject in the core configuration file

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

1. Test in test class

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

    mapper.addUser(new User(5,"hello","132123"));
    sqlSession.close();
}

update

1. Add annotations to the interface

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

1. Inject in the core configuration file

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

1. Test in test class

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

    mapper.updateUser(new User(5,"to","231545"));
    sqlSession.close();
}

delete

1. Add annotations to the interface

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

1. Inject in the core configuration file

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

1. Test in test class

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

    mapper.deleteUser(5);
    sqlSession.close();
}

Comment on @ Param()

• For parameters of basic type or String type, you need to add @ Param

• The reference type does not need to be added

• If there is only one basic type, it can be ignored, but it is recommended to add

8.2 # and $differences

• #The function of {} is mainly to replace the placeholder in the preparestatement? (recommended)

INSERT INTO user (name) VALUES (#{name});
INSERT INTO user (name) VALUES (?);

• #The function of {} is to replace the string directly

INSERT INTO user (name) VALUES ('${name}');
INSERT INTO user (name) VALUES ('kuangshen');

9.Lombok

1. IDEA installing Lombok plug-in
2. Introduce Maven dependency

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

1. Add comments to the code

@Data //GET,SET,ToString, parameterless construction
public class User {
    private int id;
    private String name;
    private String password;
    
}

10. Many to one processing

10.1 setting up test environment

Database design

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

1. Install Lombok plug-in

2. Introduce Maven dependency

3. Add comments to the code

@Data
public class Student {
    private int id;
    private String name;
    //Multiple students can be the same teacher, that is, many to one
    private Teacher teacher;
}

@Data //GET,SET,ToString, parameterless construction
public class Teacher {
private int id;
private String name;
}

1. Write Mapper interfaces corresponding to entity classes [two]

• Whether there is a need or not, you should write it down in case you only need it later

public interface StudentMapper {
}

public interface TeacherMapper {
}

1. Write Mapper corresponding to Mapper interface XML configuration file [two]

• Whether there is a need or not, you should write it down in case you only need it later

<?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.kuang.mapper.StudentMapper">
    
</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.kuang.mapper.TeacherMapper">
    
</mapper>

10.2 nested processing by query

1. Add method to StudentMapper interface

//Get the information of all students and corresponding teachers
public List<Student> getStudents();

1. Write the corresponding Mapper 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.kuang.mapper.StudentMapper">
<!--
Access to all teacher and student information
 Idea:
    1. Get information about all students
    2. According to the student information obtained by the teacher ID->Get the teacher's information
    3. Think about the problem. In this way, the result set of students should include teachers. How to deal with it? We usually use association query in the database?

1.Make a result set mapping:StudentTeacher
2.StudentTeacher The result set is of type Student
3.The attributes of teachers in students are teacher，The corresponding database is tid. 
4.Complex properties,We need to deal with it separately
    object:use association
    aggregate:use collection
-->
<select id="getStudents" resultMap="StudentTeacher">
    select * from student
</select>
    
<resultMap id="StudentTeacher" type="Student">
    <!--association Association attribute property Attribute name javaType Attribute type column Column names in tables with more than one side-->
    <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
</resultMap>
<!--
From here id，When there is only one attribute, any value can be written below
association in column Multi parameter configuration:
column="{key=value,key=value}"
In fact, it is the form of key value pairs, key Pass it on to the next one sql The name of the value, value It's in clip 1 sql The field name of the query.
-->
<select id="getTeacher" resultType="teacher">
    select * from teacher where id = #{id}
</select>
</mapper>

1. After writing, go to the Mybatis configuration file and register Mapper!

2. test

@Test
public void testGetStudents(){
    SqlSession session = MybatisUtils.getSession();
    StudentMapper mapper = session.getMapper(StudentMapper.class);
    List<Student> students = mapper.getStudents();
    for (Student student : students){
    System.out.println(
    "Student name:"+ student.getName()+"\t teacher:"+student.getTeacher().getName());
    }
}

10.3 nested query by result

1. Interface method compilation

public List<Student> 1 getStudents2();

1. Write the corresponding mapper file

<!--Method 2:Nested query by result-->
    <select id="getStudent2" resultMap="StudentTeacher2">
        select mybatis.student.id sid,
               mybatis.student.name sname,
               mybatis.teacher.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"></result>
        </association>
    </resultMap>

1. After writing, go to the Mybatis configuration file and register Mapper!
2. test

10.4 summary

• Nested processing by query is like subquery in SQL
• Nested processing according to the results is like a join table query in SQL

11. One to many processing

One to many understanding:

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

11.1 setting up test environment

1. Writing entity classes

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

@Data
    public class Teacher {
    private int id;
    private String name;
    //One teacher has more than one student
    private List<Student> students;
}

1. Write Mapper interface corresponding to entity class

public interface StudentMapper {
}

public interface TeacherMapper {
}

1. Write Mapper corresponding to Mapper interface XML configuration file

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

</mapper>

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

</mapper>

11.2 nested query by result

1. TeacherMapper interface writing method

public interface TeacherMapper {
    //Get the designated Teacher and all students under the teacher
 	Teacher getTeacher(int id);
}

1. Write Mapper configuration file corresponding to the interface

12. Dynamic SQL

Dynamic SQL is to generate different SQL statements according to different query conditions

• The SQL statements we wrote before are relatively simple. If there are complex businesses, we need to write complex SQL statements, which often need to be spliced. If we don't pay attention to splicing SQL, we will be wrong
• This requires the use of mybatis dynamic SQL. Through tags such as if, choose, when, otherwise, trim, where, set and foreach, it can be combined into very flexible SQL statements, which not only improves the accuracy of SQL statements, but also greatly improves the efficiency of developers

12.1 construction environment

New database table: blog

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 mybatis foundation project

[the external chain image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-vwruskun-1621233180551) (crazy God says Mybatis.assets/image-20210516211527958.png)]

IDUtils

@SuppressWarnings("all") // Suppression warning
public class IDUtils {
    public static String getID(){
        return UUID.randomUUID().toString().replaceAll("-","");
    }
}

1. Entity class writing

@Data
public class Blog {
    private String id;
    private String title;
    private String author;
    private Date createTime;   // The property name and field name are inconsistent
    private int views;
}

1. Write Mapper interface and xml file

public interface BlogMapper {
}

<?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.kuang.mapper.BlogMapper">
    
</mapper>

1. Underline file, mybatis, automatic hump conversion

settings>
	<!-- Start hump naming conversion-->
    <setting name="mapUnderscoreToCamelCase" value="true"/>
    <setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>
<!--register Mapper.xml-->
<mappers>
    <mapper resource="mapper/BlogMapper.xml"/>
</mappers>

1. Insert initial data

• Write interface

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

• In blogmapper Configuring sql statements in XML

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

• Initialize blog (insert data into table)

@Test
public void addBlogTest() {
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
    Blog blog = new Blog();
    blog.setId(IDUtils.getID());
    blog.setTitle("Mybatis");
    blog.setAuthor("Madness theory");
    blog.setCreateTime(new Date());
    blog.setViews(9999);
    mapper.addBlog(blog);

    blog.setId(IDUtils.getID());
    blog.setTitle("Java");
    mapper.addBlog(blog);

    blog.setId(IDUtils.getID());
    blog.setTitle("Spring");
    mapper.addBlog(blog);

    blog.setId(IDUtils.getID());
    blog.setTitle("Microservices");
    mapper.addBlog(blog);
    sqlSession.close();
}

Initialization data complete

12.2 if statement

Demand: according to the author's name and blog name to query the blog! If the author's name is empty, query only according to the blog name; otherwise, query according to the author's name

1. Writing interface classes

public interface BlogMapper {
//    Query blog
    List<Blog> queryBlogIF(Map map);
}

1. In mapper Write sql statements in XML

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

1. test

@Test
public void testQueryBlogIf(){
    SqlSession session = MybatisUtils.getSession();
    BlogMapper mapper = session.getMapper(BlogMapper.class);
    HashMap<String, String> map = new HashMap<String, String>();
    map.put("title","Mybatis So simple");
    map.put("author","Madness theory");
    List<Blog> blogs = mapper.queryBlogIf(map);
    System.out.println(blogs);
    session.close();
}

If the query is equal to null, then we can see that the query is equal to null

select * from mybatis.user where title = #{title}

However, if the title is empty, the query statement is

select * from mybatis.user where and author = #{author}

This is a wrong SQL statement. How to solve it? See the where statement below

12.3 where statement

Modify the above SQL statement

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

The where tag will know that if it contains a return value in the tag, it will insert a 'where'. In addition, if the content returned by the tag starts with and or, it will be eliminated

12.4 set statement

Similarly, the above query SQL statement contains the where keyword. If the update operation contains the set keyword, how can we deal with it?

1. Write interface method

public interface BlogMapper {
    int updateBlog(Map map);
}

1. In mapper Writing sql statements in XML

<!--be careful set Are separated by commas-->
<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>

1. test

@Test
public void testUpdateBlog(){
    SqlSession session = MybatisUtils.getSession();
    BlogMapper mapper = session.getMapper(BlogMapper.class);
    
    HashMap<String, String> map = new HashMap<String, String>();
    
    map.put("title","dynamic SQL");
    map.put("author","Qin Jiang");
    map.put("id","9d6a763f5e1347cebda43e2a32687a77");
    mapper.updateBlog(map);
    session.close();
}

12.5 choose statement

Sometimes, we don't want to use all the query conditions. We just want to select one of them. One of the query conditions meets the accounting section. Using the choose tag can solve such problems, which is similar to the switch statement of java

1. Write interface method

public interface BlogMapper {
    List<Blog> queryBlogChoose(1 Map map);
}

1. In mapper Writing sql statements in XML

<select id="queryBlogChoose" parameterType="map" resultType="blog">
    select * from 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>

1. test

@Test
public void testQueryBlogChoose(){
    SqlSession session = MybatisUtils.getSession();
    BlogMapper mapper = session.getMapper(BlogMapper.class);
    HashMap<String, Object> map = new HashMap<String, Object>();
    map.put("title","Java So simple");
    map.put("author","Madness theory");
    map.put("views",9999);
    List<Blog> blogs = mapper.queryBlogChoose(map);
    System.out.println(blogs);
    session.close();
}

12.6 sql fragment

Sometimes we may use a certain sql statement too much. In order to increase the reusability of the code and simplify the code, we need to extract these codes and call them directly when using them

Extract SQL fragment

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

Reference SQL fragment

<select id="queryBlogIf" parameterType="map" resultType="blog">
	select * from blog
    <where>
        <!-- quote sql Clip, if refid If the specified is not in this document, it needs to be preceded by namespace
        -->
        <include refid="if-title-author"></include>
        <!-- Other can be quoted here sql fragment -->
    </where>
</select>

be careful:

1. It is better to define sql fragments based on forms to improve the reusability of fragments
2. Do not include where in the sql fragment

12.7 foreach

Condition preparation: modify the IDs of the first three data in the database to 1, 2 and 3

Requirement: we need to query the blog information with id 1, 2 and 3 in the blog table

1. Write interface method

public interface BlogMapper {
    List<Blog> queryBlogForeach(Map map);
}

1. In mapper Writing sql statements in XML

<select id="queryBlogForeach" parameterType="map" resultType="blog">
	select * from blog
    <where>
        <!--
        collection:Specifies the collection properties in the input object
        item:The generated object is traversed each time
        open:Splice string at the beginning of traversal
        close:String spliced at the end
        separator:Traverse the strings that need to be spliced between objects
        select * from blog where 1=1 and (id=1 or id=2 or id=3)
        -->
        <foreach collection="ids" item="id" open="and (" close=")" separator="or">
        id=#{id}
        </foreach>
    </where>
</select>

1. test

@Test
public void testQueryBlogForeach(){
    SqlSession session = MybatisUtils.getSession();
    BlogMapper mapper = session.getMapper(BlogMapper.class);
    HashMap map = new HashMap();
    List<Integer> ids = new ArrayList<Integer>();
    ids.add(1);
    ids.add(2);
    ids.add(3);
    map.put("ids",ids);
    List<Blog> blogs = mapper.queryBlogForeach(map);
    System.out.println(blogs);
    session.close();
}

12.6 sql fragment

Sometimes we may use a certain sql statement too much. In order to increase the reusability of the code and simplify the code, we need to extract these codes and call them directly when using them

Extract SQL fragment

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

Reference SQL fragment

<select id="queryBlogIf" parameterType="map" resultType="blog">
	select * from blog
    <where>
        <!-- quote sql Clip, if refid If the specified is not in this document, it needs to be preceded by namespace
        -->
        <include refid="if-title-author"></include>
        <!-- Other can be quoted here sql fragment -->
    </where>
</select>

be careful:

1. It is better to define sql fragments based on forms to improve the reusability of fragments
2. Do not include where in the sql fragment

12.7 foreach

Condition preparation: modify the IDs of the first three data in the database to 1, 2 and 3

Requirement: we need to query the blog information with id 1, 2 and 3 in the blog table

1. Write interface method

public interface BlogMapper {
    List<Blog> queryBlogForeach(Map map);
}

1. In mapper Writing sql statements in XML

<select id="queryBlogForeach" parameterType="map" resultType="blog">
	select * from blog
    <where>
        <!--
        collection:Specifies the collection properties in the input object
        item:The generated object is traversed each time
        open:Splice string at the beginning of traversal
        close:String spliced at the end
        separator:Traverse the strings that need to be spliced between objects
        select * from blog where 1=1 and (id=1 or id=2 or id=3)
        -->
        <foreach collection="ids" item="id" open="and (" close=")" separator="or">
        id=#{id}
        </foreach>
    </where>
</select>

1. test

@Test
public void testQueryBlogForeach(){
    SqlSession session = MybatisUtils.getSession();
    BlogMapper mapper = session.getMapper(BlogMapper.class);
    HashMap map = new HashMap();
    List<Integer> ids = new ArrayList<Integer>();
    ids.add(1);
    ids.add(2);
    ids.add(3);
    map.put("ids",ids);
    List<Blog> blogs = mapper.queryBlogForeach(map);
    System.out.println(blogs);
    session.close();
}