brief introduction

What is Mybatis?

• MyBatis is an excellent persistence layer framework.
• It supports custom SQL, stored procedures, and advanced mapping.
• MyBatis eliminates almost all JDBC code and the work of setting parameters and obtaining result sets.
• MyBatis can configure and map primitive types, interfaces and Java POJO s (Plain Old Java Objects) to records in the database through simple XML or annotations.
• MyBatis was originally an open source project ibatis of apache. In 2010, the project was migrated from apache software foundation to google code and renamed MyBatis.
• Moved to Github in November 2013.

How do I get Mybatis?

• maven get:

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

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

Persistence

• Persistence is the process of transforming program data in persistent state and concurrent state
• Memory: breakpoint is lost
• Database (jdbc), io file persistence

Why persistence?

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

Persistent layer

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

Why use Mybatis?

• Easy to learn
• flexible
• SQL and code are separated to improve maintainability
• Provide mapping labels to support the mapping of orm fields between objects and databases
• Provide object relationship mapping labels to support object relationship construction and maintenance
• Provide xml tags to support writing dynamic sql
• Many people use it!

Basic practice

The first Mybatis program

Build environment

Build database

create DATABASE mybatis;

use mybatis;

create table user (
	id int(20) not null primary key,
	name VARCHAR(30) DEFAULT null,
	pwd VARCHAR(30) DEFAULT null
)ENGINE=INNODB DEFAULT charset=utf8;

insert into user(id, `name`, pwd) values
(1, 'name1', '1'),
(2, 'name2', '2'),
(3, 'name3', '3')

New project

1. Create a normal maven project
2. Delete src directory
3. Import maven dependencies

<!--mysql-->
<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.5.2</version>
</dependency>
<!--junit-->
<dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>4.12</version>
</dependency>

Create a module

• Write the core configuration file of mybatis

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <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://192.168.43.200:3306/mybatis?useSSL=false&amp;useUnicode=true&amp;characterEncoding=UTF-8"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>
</configuration>

• Write mybatis tool class

public class MybatisUtils {
    private static SqlSessionFactory sqlSessionFactory;

    static {
        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 SqlSessionFactory, as the name suggests, we can get an instance of SqlSession from it.
     * SqlSession Provides all the methods required to execute SQL commands in the database. You can directly execute the mapped SQL statements through the SqlSession instance.
     */
    public static SqlSession getSqlSession() {
        return sqlSessionFactory.openSession();
    }
}

Write code

• Entity class

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

    public User() {
    }

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

    public int getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public String getPwd() {
        return pwd;
    }

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

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

• dao interface

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

• dao implementation class

<?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 Bound a mapper Interface-->
<mapper namespace="com.xubadou.mapper.UserMapper">

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

</mapper>

test

Note:
org.apache.ibatis.binding.BindingException: Type interface com.xubadou.mapper.UserMapper is not known to the MapperRegistry.
This exception indicates that we have not configured mapper in the core configuration file

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

The following exceptions may occur during test execution. After the project is compiled, there is no UserMapper.xml file:
At this time, you need to add the following code to the pom.xml file to release the file filtering:

<!--stay build Medium configuration resources，To prevent the failure of resource export-->
<build>
    <resources>
        <resource>
            <directory>src/main/resources</directory>
            <includes>
                <include>**/*.properties</include>
                <include>**/*.xml</include>
            </includes>
            <filtering>true</filtering>
        </resource>
        <resource>
            <directory>src/main/java</directory>
            <includes>
                <include>**/*.properties</include>
                <include>**/*.xml</include>
            </includes>
            <filtering>true</filtering>
        </resource>
    </resources>
</build>

Junit test code:

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

    //2.getMapper
    UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
    List<User> userList = userMapper.getUserList();
    for (User user : userList) {
        System.out.println(user);
    }

    //3. Close SqlSession
    sqlSession.close();
}

Add, delete, modify and query

namespace

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

select

Query statement

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

1. Write interface

//Get user by user Id
User getUserByID(int id);

2. Write the SQL statement in the corresponding mapper

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

3. Test

@Test
public void getUserByID() {
    //1. Get SqlSession object
    SqlSession sqlSession = MybatisUtils.getSqlSession();

    //2.getMapper
    UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
    User user = userMapper.getUserByID(1);
    System.out.println(user);

    //3. Close SqlSession
    sqlSession.close();
}

insert

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

update

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

delete

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

Note: add, delete, or modify transactions that need to be committed

sqlSession.commit();

Universal Map

int insertUser2(Map map);

<insert id="insertUser2" parameterType="map" >
    insert into mybatis.user(id, name, pwd) values(#{uId}, #{uName}, #{uPwd})
</insert>

@Test
public void insertUser2() {
    //1. Get SqlSession object
    SqlSession sqlSession = MybatisUtils.getSqlSession();

    //2.getMapper
    UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

    Map<String, Object> user = new HashMap<String, Object>(16);
    user.put("uId", 4);
    user.put("uName", "xxx");
    user.put("uPwd", "45333");
    int code = userMapper.insertUser2(user);
    System.out.println(code);

    //Commit transaction
    sqlSession.commit();

    //3. Close SqlSession
    sqlSession.close();
}

Pass parameters from map and directly get the key from sql! [parameterType=“map”]
Transfer parameters to the object, and directly take out the attributes of the object in sql! [parameterType=“com.xubadou.mapper.UserMapper”]
If there is only one basic type parameter, you can get it directly in sql!
Use Map or annotation for multiple parameters!

Fuzzy query

1. When executing java code, pass wildcard%%

List<User> userList = maper.getUserLike("%Lee%")；

2. Use wildcards in SQL splicing

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

Configuration resolution

Core profile

• mybatis-config.xml
• The mybatis configuration file contains settings and attribute information that deeply affect mybatis behavior

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

Environment configurations

Mybatis can be configured to use a variety of environments
Although multiple environments can be configured, only one environment can be selected for each SqlSessionFactory instance
Learn to use and configure multiple operating environments
The default transaction manager of Mybatis is JDBC, and the connection pool is POOLED

properties

The reference configuration file can be implemented through the properties property
These properties are externally configurable and dynamically replaceable. They can be configured in a typical Java properties file or passed through the word tag of the properties tag

Write configuration file
db.properties

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

Import in core configuration file

<properties resource="db.properties">
    <property name="username" value="root"/>
    <property name="password" value="12345"/>
</properties>

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

Type alias

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

<!--Alias an entity class-->
<typeAliases>
    <typeAlias type="com.xubadou.pojo.User" alias="user"/>
</typeAliases>

You can also specify a package name to search for the required Java beans
Scan the package of an entity class, and its default alias is the class name of this class, with the first letter in lowercase!

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

The first method is used when there are few entity classes
If there are many entity classes, the second one is recommended
The first can be a DIY alias, and the second can't. If you have to change it, you need to add annotations on the entity class

@Alias("user")
public class User {

mappers

MapperRegistry: register and bind our Mapper file
Mode 1:

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

Method 2: register with class file binding

<mappers>
    <mapper class="com.xubadou.mapper.UserMapper"/>
</mappers>

Note:

• Interface and its Mapper configuration file must have the same name
• Interface and its Mapper configuration file must be under the same package

Method 3: use scan package for injection binding

<mappers>
    <package name="com.xubadou.mapper"/>
</mappers>

Note:

• Interface and its Mapper configuration file must have the same name
• Interface and its Mapper configuration file must be under the same package

Lifecycle and scope

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

SqlSessionFactoryBuilder

• Once SqlSessionFactory is created, it is no longer needed
• Local method variable

SqlSessionFactory

• Similar to database connection pool
• Once created, it should always exist during the operation of the application. There is no reason to discard it or recreate another instance
• The best scope is the application scope
• The simplest is to use singleton mode or static singleton mode

SqlSession

• Similar to a request to a connection pool
• Each thread should have its own SqlSession instance
• The instance of SqlSession is not thread safe, so it cannot be shared, so its best scope is the request or method scope
• You need to close it quickly after use

ResultMap result set mapping

Solve the problem that the attribute name is inconsistent with the field name

When the attributes and fields are inconsistent, the following problems occur:

resolvent:

Alias together

<select id="getUserList" resultType="user">
    select id, name, pwd password from mybatis.user
</select>

Method 2: use resultMap

<!--Result set mapping-->
<resultMap id="userMap" type="user">
    <result property="password" column="pwd"/>
</resultMap>

<!--select Query statement-->
<select id="getUserList" resultMap="userMap">
    select id, name, pwd from mybatis.user
</select>

• The resultMap element is the most important and powerful element in MyBatis
• The design idea of ResultMap is to achieve zero configuration for simple statements. For more complex statements, you only need to describe the relationship between statements
• That's the beauty of ResultMap -- you don't have to explicitly configure them

paging

Limit implements paging

Interface

//Paging query
List<User> getUserPage(Map<String, Object> map);

mapper.xml

<select id="getUserPage" parameterType="map" resultMap="userMap">
    select id, name, pwd from user limit #{startIndex}, #{pageSize}
</select>

test

@Test
public void getUserPage() {
     //1. Get SqlSession object
     SqlSession sqlSession = MybatisUtils.getSqlSession();

     //2.getMapper
     UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
     Map<String, Object> params = new HashMap<String, Object>(16);
     params.put("startIndex", 1);
     params.put("pageSize", 2);
     List<User> userList = userMapper.getUserPage(params);
     for (User user : userList) {
         System.out.println(user);
     }

     //3. Close SqlSession
     sqlSession.close();
 }

RowBounds paging

Implement paging at the java code level

@Test
public void getUserPageByRowBounds() {
    //1. Get SqlSession object
    SqlSession sqlSession = MybatisUtils.getSqlSession();

    //2.getMapper
    List<User> userList = sqlSession.selectList("com.xubadou.mapper.UserMapper.getUserList", null, new RowBounds(1, 2));
    for (User user : userList) {
        System.out.println(user);
    }

    //3. Close SqlSession
    sqlSession.close();
}

Annotation development

• Using annotations to map simple statements makes the code appear more concise
• But for slightly more complex statements, Java annotations are not only inadequate, but also make your already complex SQL statements more chaotic
• If you need to do some complex operations, it's best to use XML to map statements.

Modify interface

//Get user list
@Select("select * from user")
List<User> getUserList();

Modify core profile

<mappers>
    <mapper class="com.xubadou.mapper.UserMapper"/>
</mappers>

Mybatis execution process analysis

Annotation enables addition, deletion, modification and query

Complex query

Many to one query

Create a test table and insert the test data

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

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

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

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

Test environment construction

1. Import lombok
2. New entity class Teacher, Student
3. Establish Mapper interface
4. Create Mapper.XML file
5. Bind and register our Mapper interface or file in the core configuration file!
6. Test whether the query is successful!