The article is hostedGitHub, you can check it out on GitHub! And search wechat public code out of Offer to receive a variety of learning materials!

I. Overview of the framework

1.1 What is a Framework?

The semi-finished product of software solves the problem of universality in the process of software development, thus simplifies the development steps and provides the efficiency of development

1.2 What is ORM Framework?

Object Relational Mapping (ORM) maps an Object in a program to a row of data in a table

ORM framework provides the mapping relationship between persistent classes and tables, and the object is persisted to the database by referring to the information of the mapping file at run time

1.3 Disadvantages of using JDBC for ORM

  • There is a lot of redundant code

  • Manually create a Connection or Statement

  • Manually encapsulate the result set into entity objects

  • Inefficient query, Not optimized for data access (Not Cache)

MyBatis overview

2.1 What is MyBatis

MyBatis was originally an open source project of Apache Software Foundation iBatis. In 2010, the project was migrated from Apache Software Foundation to Google Code and renamed MyBatis. Migrated to Github in November 2013. MyBatis is an excellent persistence layer framework that supports custom SQL, stored procedures, and advanced mapping. MyBatis eliminates almost all of the JDBC code and the work of setting parameters and fetching result sets. MyBatis can configure and map primitive types, interfaces, and Java POJOs (Plain Old Java Objects) to records in the database via simple XML or annotations

2.2 website

Official website: www.mybatis.org/mybatis-3/

Download: github.com/mybatis/myb…

2.3 the use of

As for the use of MyBatis, the Maven project we created does not need to download MyBatis, but can be used by adding MyBatis dependency in the POM file of Maven project! I will send you the development process of MyBatis later!

Iii. Build the MyBatis Project

3.1 Create a Maven project

File -> NewProject
Creating a Maven project

3.2 Importing the MyBatis dependency

Add the MyBatis core dependency and MyBatis Log dependency to the POP.xml file

  • Core dependency is the necessary dependency environment to use MyBatis, must be imported
  • MyBatis Log (Log) dependence can view MyBatis startup process, easy for us to correct errors, check bugs

Note: Remember to place dependencies in the

tag, MyBatis must interact with the database, so import mysql driver dependencies as well

<! MyBatis </artifactId> MyBatis </artifactId> <version>3.4.6</version> </dependency> <! Log4j </artifactId> log4j</artifactId> <version>1.2.17</version> </dependency> <! --> <dependency> <groupId> MySql </groupId> <artifactId>mysql-connector-java</artifactId> The < version > 5.1.47 < / version > < / dependency >Copy the code

3.3 Creating the MyBatis Profile

Create a configuration file named mybatis-config.xml and configure the following information

Note: mapper. XML is currently recommended to be stored in resources by default. The path cannot start with a /

<? The 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" > <! --MyBatis configuration --> <configuration> <! <environments default="MySqlDB"> <! <environment id="MySqlDB"> <! <transactionManager type="JDBC"/> <! - the connection pool - > < dataSource type = "org. Apache. Ibatis. The dataSource. Pooled. PooledDataSourceFactory" > < property name = "driver" value="com.mysql.jdbc.Driver"/> <! -- & escape &amp; --> <property name="url" value="jdbc:mysql://localhost:3306/x? useUnicode=true&amp; characterEncoding=utf8"/> <property name="username" value="root"/> <property name="password" value="123456"/> </dataSource> </environment> </environments> <! --Mapper register --> <mappers> <! < Mapper resource=" xxxmapper. XML "/> </mappers> </configuration>Copy the code

Table 3.4 built

Create a table to implement operations on the database

create table tb_user
(
    id       int auto_increment
        primary key,
    username varchar(30) null.password varchar(30) null,
    gender   char        null,
    birth    date        null
) charset = utf8;
Copy the code

3.5 Write entity class code

Here I’m using the Lombok plug-in to annotate getters, Setter methods, and no-argument, no-argument constructs

package com.mylifes1110.bean;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.util.Date;

@Data
@NoArgsConstructor
@AllArgsConstructor
public class User {
    private Integer id;
    private String username;
    private String password;
    private Boolean gender;
    private Date birth;
}
Copy the code

3.6 Defining Dao Layer Interfaces

Define a Dao layer interface and write a query method

Note: Since we are using the MyBatis framework, we do not need to create the Dao layer implementation class

package com.mylifes1110.dao;

import com.mylifes1110.bean.User;

public interface UserDao {
    User selectUserById(int id);
}
Copy the code

3.7 Creating and writing mapper.xml

MyBatis framework uses mapper. XML file to map objects to SQL, so we will create a usermapper. XML file in the Resources folder and configure it

<? The 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" > <! -- the namespace: the implementation of the interface required fully qualified name - > < mapper namespace = "com. Mylifes1110. Dao. UserDao" > <! --id: interface abstract method to override; ResultType: query after the return of object types - > < select id = "selectUserById resultType" = "com. Mylifes1110. Beans. User" > <! Select id = #{arg0}, id =? The meaning of --> <! --#{arg0} specifies that id is equal to the first parameter in the method, Select id, username, password, gender, birth from tb_user where id = #{arg0} </select> </mapper>Copy the code

3.7 registered Mapper

Register mapper.xml with the mybatis-config.xml core configuration

<! --Mapper register --> <mappers> <! < Mapper resource=" xxxmapper. XML "/> </mappers>Copy the code

3.8 Test Method 1 (Common)

package com.mylifes1110.dao;

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 org.junit.Test;

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

public class UserMapperTest {
    @Test
    public void selectUserByIdTest(a) throws IOException {
        // Get the stream object that reads the MyBatis core configuration file
        InputStream input = Resources.getResourceAsStream("mybatis-config.xml");
        // Build a factory for the SqlSession connection object from the stream object
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(input);
        // Get the connection object sqlSession from the factory
        SqlSession sqlSession = factory.openSession();
        // Get the interface implementation object from the connection object
        UserDao userDaoImpl = sqlSession.getMapper(UserDao.class);
        // Print the result
        System.out.println(userDaoImpl.selectUserById(1)); }}Copy the code

3.9 Test Method 2 (Understanding)

package com.mylifes1110.dao;

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 org.junit.Test;

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

public class UserMapperTest {
	@Test
    public void selectUserByIdTest2(a) throws IOException {
        // Get the stream object that reads the MyBatis core configuration file
        InputStream input = Resources.getResourceAsStream("mybatis-config.xml");
        // Build a factory for the SqlSession connection object from the stream object
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(input);
        // Get the connection object sqlSession from the factory
        SqlSession sqlSession = factory.openSession();
        // Call the methods in the interface directly through the connection object
        Object o = sqlSession.selectOne("com.mylifes1110.dao.UserDao.selectUserById".1);
        // Print the resultSystem.out.println(o); }}Copy the code

4, MyBatis framework use details

4.1 Solve the problem of reading mapper. XML in paths other than resources

The mapper. XML file in Maven’s resources directory will not be loaded and compiled into classes, so if we want to put mapper. XML file in a folder other than resources, we will not be compiled into MyBatis.

Mapper. XML can be compiled into the classes folder by declaring the mapper. XML file in the resources folder.

Action: Append the < build > tag to the end of the pom.xml file so that the XML file can be copied to classes and read correctly when the program runs.

Note: In order for the properties file to be compiled, you also need to include the path of the configuration file in the

tag.

<build> <resources> <resource> <directory>src/main/java</directory> <includes> <include>**/*.xml</include><! </includes> </filtering> </resource> <resource> <directory>src/main/resources</directory> <includes> <include>**/*.xml</include><! - the newly added * / * * 1 level directory/represented multistage directory - > < include > / *. * * the properties < / include > <! Properties file --> </includes> <filtering>true</filtering> </resource> </resources> </build>Copy the code

4.2 Solving the JDBC write Death Problem

MyBatis is now written in the core configuration, so we must load JDBC with jdbc.properties configuration file, as follows:

Create the jdbc.properties configuration file

#jdbc.properties jdbc.driver=com.mysql.jdbc.Driver jdbc.url=jdbc:mysql://localhost:3306/temp? useUnicode=true&characterEncoding=utf8 jdbc.username=root jdbc.password=123456Copy the code

Modify the mybatis-config. XML core configuration file

<? The 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" > <! --MyBatis configuration --> <configuration> <properties resource="jdbc.properties" /> <! <environments default="MySqlDB"> <! <environment id="MySqlDB"> <! <transactionManager type="JDBC"/> <! --> <dataSource type="POOLED"> <property name="driver" value="${jdbc.driver}"/> <! -- & escape &amp; --> <property name="url" value="${jdbc.url}"/> <property name="username" value="${jdbc.username}"/> <property name="password" value="${jdbc.password}"/> </dataSource> </environment> </environments> <! --Mapper register --> <mappers> <! < Mapper resource=" usermapper. XML "/> </mappers> </configuration>Copy the code

4.3 Type Alias

When we write labels, we often write a large number of ResultTypes, but this attribute needs to introduce a class package path, if the type alias can be added to save writing various packages, only the class name can be written. This defines aliases for entity classes to improve writing efficiency

Action: Add the following to the tag to use the type alias

Note: add the following configuration information in the following tag, specify the class name or automatic scan package class alias, generally better scan package, because the definition of the class will define multiple classes, very cumbersome

<!--定义别名二选一-->
<typeAliases>
    <!--定义类的别名-->
    <typeAlias type="com.mylifes1110.bean.User" alias="User" />
    
    <!--自动扫描包，将原类名作为别名-->
    <package name="com.mylifes1110.bean" />
</typeAliases>
Copy the code

4.4 Using Log4J log dependencies

We added a log dependency to the pom.xml file at the beginning, but we did not use it. If used, you need to create a log4j dependent configuration file and configure the following information

Note: The path after log4J.logger. in the configuration document is where the DAO layer interface resides. We can choose to execute specific interfaces to use logging, or we can choose to use logging for all DAO layer interfaces

Add log4j dependencies to the POM.xml file

<! - log4j logging rely on https://mvnrepository.com/artifact/log4j/log4j - > < the dependency > < groupId > log4j < / groupId > < artifactId > log4j < / artifactId > < version > 1.2.17 < / version > < / dependency >Copy the code

Create and configure log4j.properties

# Global logging configuration
log4j.rootLogger=DEBUG, stdout
# MyBatis logging configuration...
log4j.logger.com.mylifes1110.dao=TRACE
# Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n
Copy the code

Log information

level describe
ALL LEVEL Turn on all logging switches. Is the lowest level used to turn on all logging.
DEBUG Output debugging information. Pointing out fine-grained information events can be very helpful for debugging applications.
INFO Output a prompt message. Messages highlight the running process of the application at a coarse-grained level.
WARN Output warning messages; Indicates a potential error situation.
ERROR Output error information. Indicates that an error event does not affect the system running.
FATAL Output fatal error; Indicate that each critical error event will cause the application to exit.
OFF LEVEL Turn off all logging switches. Is the highest level used to turn off all logging.

4.5 registered Mapper

4.5.1 Registering Mapper by Scanning Packets

For registering a Mapper, we used a single Mapper registry in the previous tutorial. In fact, we can choose a package to register all mapper. XML files, which requires us to scan all mapper. XML files in the package. If we use this method to scan all mapper. XML files in the package, we need to follow the following rules:

  • The mapper. XML file is placed in the same package as the Dao layer interface
  • The mapper. XML file must be named the same as the Dao layer interface, such as Usermapper. XML and usermapper.java
<! --> <mappers> <! --dao layer path --> <package name="com.mylifes1110.dao"/> </mappers>Copy the code
4.5.2 Single Registered Mapper Path Problem

The single Mapper registry is as follows, but putting all the Mapper files in the Resources folder is messy. To solve this problem, We can create a folder called Mappers (dir) under the Resources folder to hold a large number of mapper.xml files. So the question is how do I write a path? As follows:

<! --Mapper register --> <mappers> <! < Mapper resource="mappers/ usermapper. XML "/> </mappers>Copy the code

CURD operation of MyBatis

Note: In the add, delete, or delete operation, the transaction must be committed using sqlSession before the operation can be completed

5.1 Parameter Binding

5.1.1 Binding serial Number Parameters

Ordinal argument binding is to replace the first argument, the second argument, and so on in the argument list with #{arg0}, #{arg1}, and so on. This method is not recommended because the parameters are not very readable!

/ / User Dao layer interface selectUserByUserNameAndPassword (String username, String password). // Mapper.xml <select id="selectUserByUserNameAndPassword1" resultType="User"> SELECT * FROM t_user WHERE username = #{arg0} and password = #{arg1} </select>Copy the code
5.1.2 Annotation Parameter binding

Annotated parameter binding is to use @param (” field “) to replace the corresponding parameter in the parameter list, and then set the parameter to use #{field} to take the corresponding value in the Sql statement. This method is recommended!

/ / User Dao layer interface selectUserByUserNameAndPassword (@ Param (" username ") String username, @ Param (" password ") String password). // Mapper.xml <select id="selectUserByUserNameAndPassword2" resultType="User"> SELECT * FROM t_user WHERE username = #{username} and password = #{password} </select>Copy the code
5.1.3 Map Parameter Binding

Map parameter binding encapsulates parameters using a Map set, passing a Map set into a method, and then using the encapsulated key to value the parameter in an Sql statement. This way to understand, cumbersome operation, do not recommend use!

/ / User Dao layer interface selectUserByUserNameAndPassword (String username, String password). Map<String, Object> Map = new HashMap<>(); Map. put("username", "root"); map.put("password", "123456"); User user = userDao.selectUserByUserNameAndPassword(map); // Mapper.xml <select id="selectUserByUserNameAndPassword3" resultType="User"> SELECT * FROM t_user WHERE username = #{username} and password = #{password} <! </select>Copy the code
5.1.4 Binding Object Parameters

Object parameter binding is passing in an object in the parameter list of the Dao layer interface method, so that the field values of the object can be arbitrarily retrieved in Sql statements. Because many scenarios need to pass in objects, so this method is easy to use, recommended!

/ / User Dao layer interface selectUserByUserNameAndPassword (User User); // Mapper.xml <select id="selectUserByUserNameAndPassword4" resultType="User"> SELECT * FROM t_user WHERE username = #{username} and password = #{password} <! </select>Copy the code

5.2 Query Operations

5.2.1 Querying labels

5.2.2 Common Query

A normal query is when we use MyBatis to query data in a single table or multiple tables

/ / User Dao layer interface selectUserByUserNameAndPassword (@ Param (" username ") String username, @ Param (" password ") String password). // Mapper.xml <select id="selectUserByUserNameAndPassword2" resultType="User"> SELECT * FROM t_user WHERE username = #{username} and password = #{password} </select>Copy the code
5.2.3 Querying the Number of Total Data items

You can use count(1) to query the total number of data items

// Dao layer interface
long selectUserCount(a);

// Mapper.xml
<select id="selectUserCount" resultType="java.lang.Long">
    select count(1) from tb_user
</select>
    
/ / test class
@Test
public void selectUserCount(a) throws IOException {
    InputStream input = Resources.getResourceAsStream("mybatis-config.xml");
    SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(input);
    SqlSession sqlSession = factory.openSession();
    UserDao userDao = sqlSession.getMapper(UserDao.class);
    System.out.println(userDao.selectUserCount());
}
Copy the code
5.2.4 Fuzzy Query

Fuzzy query in Sql is also available in MyBatis. Here is an example of a fuzzy query for username with annotation parameter binding

Note: Query the List collection, resultType is also passed in the generic object in the collection (special)

// Dao layer interface
List<User> selectUserListByUsername1(@Param("username") String username);

// Mapper.xml
<select id="selectUserListByUsername1" resultType="com.mylifes1110.bean.User">
    select id, username, password, gender, birth
    from tb_user
    where username like concat(The '%',#{username},The '%')
</select>
    
// query the username containing Z
@Test
public void selectUserListByUsername1(a) throws IOException {
    InputStream input = Resources.getResourceAsStream("mybatis-config.xml");
    SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(input);
    SqlSession sqlSession = factory.openSession();
    UserDao userDao = sqlSession.getMapper(UserDao.class);
    System.out.println(userDao.selectUserListByUsername1("Z"));
}
Copy the code

5.3 Deleting a vm

5.3.1 Deleting labels

<delete id=” interface method name “resultType=” return value type “>

Note: In the delete tag, the resultType attribute can be omitted, whereas the

tag generated by the IDEA shortcut key is omitted by default!

5.3.2 delete

Delete a user by id

// Dao layer interface int deleteUserById(@param ("id") int id); // Mapper.xml <delete id="deleteUserById"> delete from tb_user where id = #{id} </delete>Copy the code

5.4 Modifying Operations

5.4.1 Modifying labels

<update id=” interface method name “resultType=” return value type “>

Note: It is also possible to omit the resultType attribute value in the

tag

5.4.2 modify

Modify a user’s information by id

// Dao layer interface int updateUserById(User User); // Mapper.xml <update id="updateUserById"> update tb_user set username = #{username}, password = #{password}, gender = #{gender}, birth = #{birth} where id = #{id} </update>Copy the code

5.5 New Operations

5.5.1 Adding labels

<insert id=” interface method name “resultType=” return value type “>

Note: The resultType attribute value can also be omitted in the < INSERT > tag

5.5.2 new

Add a user information

// Dao layer interface
int insertUser(User user);

// Mapper.xml
<insert id="insertUser">
    insert into tb_user
    (username, password, gender, birth)
    values 
    (#{username}, #{password}, #{gender}, #{birth})
</insert>
Copy the code

5.6 Primary Key Backfill Operations

5.6.1 What is Primary Key Backfill?

About the primary key backfill is learning you may not know what it means, but I analyze a scenario, probably you will understand!

Scenario 1 (primary key of int type) : In development, primary keys of int type and increment are also common, but when you insert data without the primary key ID, Sql will automatically add the primary key ID for you. So one of the disadvantages of this is that if we want to insert a piece of data and get this primary key ID, we have to look it up again to get the primary key ID, which is obviously cumbersome. When you insert data, the primary key ID of type int will be queried separately during the insert process and the primary key ID will be returned. In this way, one Sql operation does two jobs and greatly improves development efficiency

Scenario 2 (String primary key) : In development, as in scenario 1, String primary keys are common, for example, an order ID is usually a long String. The order ID can be obtained by UUID() method and replace() method, because these two methods also exist in Sql, so we can also generate the order ID to fill in the database when inserting the order data, and we can also query the order ID after inserting the data, which greatly improves my development efficiency

5.6.2 Backfilling labels for primary Keys

< selectKey keyProperty = “primary key field name” resultType = “return value type” order = “BEFORE | AFTER” >

Note: When used, place the

tag inside the < INSERT > tag. A. after B. before C. after D. before The primary key is backfilled before the data is inserted and after the data is queried

5.6.3 Querying the Primary Key Using 1698218 (int)

Select 1698218 (int ID, int ID, int ID, int ID, int ID, int ID, int ID); Ensure that the ID of the data is queried after it is inserted by specifying the order attribute to be after using the

tag

// the table uses the User table

// Dao layer interface
int insertUser(User user);

// Mapper.xml
<insert id="insertUser">
<selectKey keyProperty="id" resultType="int" order="AFTER">
    select last_insert_id(a)<! Insert into --> </selectKey> insert intotb_user
    (username, password, gender, birth)
    values
    (#{username}, #{password}, #{gender}, #{birth})
</insert>
    
/ / test class
@Test
public void insertOrder(a) {
    OrderDao orderDao = MyBatisUtils.getMapper(OrderDao.class);
    Order order = new Order();
    order.setMoney(11.1D);
    order.setUserId(2);
    System.out.println(orderDao.insertOrder(order));
    System.out.println(order.getId());
    MyBatisUtils.commit();
}
Copy the code
5.6.4 Querying the Primary Key Using the UUID () (String)

UUID() and replace() are two built-in functions in MySQL. You can use the UUID() method to generate a UUID string, and replace() to replace the “-” in the UUID to generate a 32-bit order ID string. After the order ID is generated, SELECT REPLACE(UUID(),’-‘,”) to find the order ID added to the database

// Create the order table
create table tb_order
(
    id      varchar(32) not null
        primary key,
    money   double      null,
    user_id int         null
) charset = utf8;

// Dao layer interface
int insertOrder(Order order);

// Mapper.xml
<insert id="insertOrder" parameterType="com.mylifes1110.bean.Order">
<selectKey keyProperty="id" resultType="string" order="BEFORE">
    select replace(UUID(), '-', '')
</selectKey>
    insert into tb_order
    (id, money, user_Id)
    values
    (#{id}, #{money}, #{userId})
</insert>
Copy the code

6. Encapsulate tool classes

6.1 Package Tool Class Analysis

  • Resource: used to obtain the I/O object for reading configuration files. It consumes resources. You are advised to use I/O to read all required data at one time.

  • SqlSessionFactory: SqlSession factory class. It consumes a lot of memory and resources. You are advised to create only one object for each application.

  • SqlSession: a Connection that can control transactions, should be thread private, not shared by multiple threads.

  • Encapsulate methods such as get a connection, close a connection, commit a transaction, roll back a transaction, and get an interface implementation class.

6.2 MyBatis Utility class

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

/ * * *@ClassName MyBatisUtils
 * @DescriptionMyBatis utility class *@Author Ziph
 * @Date 2020/7/11
 * @Since 1.8
 * @Version1.0 * /
public class MyBatisUtils {
    // Get the SqlSession factory
    private static SqlSessionFactory factory;

    // Create a ThreadLocal bind to the SqlSession object in the current thread
    private static final ThreadLocal<SqlSession> tl = new ThreadLocal<SqlSession>();

    static {
        try {
            InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
            factory = new SqlSessionFactoryBuilder().build(is);
        } catch(Exception e) { e.printStackTrace(); }}// Get connection (get current thread SqlSession from TL)
    private static SqlSession openSession(a){
        SqlSession session = tl.get();
        if(session == null){
            session = factory.openSession();
            tl.set(session);
        }
        return session;
    }

    // Release connection (release SqlSession in current thread)
    public static void closeSession(a){
        SqlSession session = tl.get();
        session.close();
        tl.remove();
    }

    // Commit transaction (commit transaction managed by SqlSession in current thread)
    public static void commit(a){
        SqlSession session = openSession();
        session.commit();
        closeSession();
    }

    // Rollback transactions (rollback transactions managed by SqlSession in the current thread)
    public static void rollback(a){
        SqlSession session = openSession();
        session.rollback();
        closeSession();
    }

    // Get the interface implementation class object
    public static <T extends Object> T getMapper(Class<T> clazz){
        SqlSession session = openSession();
        returnsession.getMapper(clazz); }}Copy the code