brief introduction

• What is Mybatis?

MyBatis is an excellent persistence layer framework, which 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 history

Mybatis is an open source project under the Apache Software Foundation, formerly known as the ibatis framework. In 2010, the project was migrated from Apache Software Foundation to google code and renamed mybatis. In November 2013, it moved to github.

• Generally speaking, what can Mybatis do?

We usually use JDBC to access the database. In addition to writing SQL ourselves, we must also operate connection, statement and resultset. In fact, these are only auxiliary classes of means. Not only that, access to different tables, but also write a lot of the same code, which is cumbersome and boring.

After using mybatis, you only need to provide your own SQL statements. Other tasks, such as establishing connection Statement and JDBC related exception handling, are left to mybatis. Those repetitive tasks are also done by mybatis. Developers only need to focus on the operation level of addition, deletion, modification and query, Mybatis encapsulates the technical details in places we can't see.

Frame principle

Illustration:

1. Mybatis configuration file sqlmapconfig XML: as the global configuration file of mybatis, this file defines the basic environment information of mybatis operation, such as database link information. mapper.xml file. These files are sql mapping files. The file configures sql statements for operating the database. This file needs to be in sqlmapconfig Configuration loading in XML.

2. SqlSessionFactory is constructed through configuration information such as mybatis environment, that is, session factory.

3. sqlSession is created by the session factory, that is, the session. The database operation needs to be carried out through sqlSession.

4. The bottom layer of mybatis customizes the operation database of the Executor interface. The Executor interface has two implementations, one is the basic Executor and the other is the cache Executor.

5. The Mapped Statement is also an underlying encapsulation object of mybatis, which encapsulates the configuration information and sql mapping information of mybatis. mapper. An sql in the XML file corresponds to a Mapped Statement object, and the id of the sql is the id of the Mapped Statement.

6. The Mapped Statement defines the input parameters of sql execution, including HashMap, basic type and pojo. The Executor maps the input java objects into sql before executing sql through the Mapped Statement. The input parameter mapping is to set the parameters of preparedStatement in jdbc programming.

7. The Mapped Statement defines the output results of sql execution, including HashMap, basic type and pojo. The Executor maps the output results to java objects after executing sql through the Mapped Statement. The mapping process of the output results is equivalent to the parsing process of the results in jdbc programming.

Build an introductory case

Environmental description:

1. JDK: 1.8
2. mybatis: 3.5.7
3. maven project

1. Create maven project and import dependency

<dependencies>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.27</version>
    </dependency>
    <dependency>
        <groupId>org.mybatis</groupId>
        <artifactId>mybatis</artifactId>
        <version>3.4.5</version>
    </dependency>
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <version>1.18.22</version>
    </dependency>
</dependencies>

2. Create entity class

@ToString
@Getter
@Setter
@Builder
public class User {
    Integer id;
    String account;
    String name;
    String password;
}

3. User interface

public interface UserDao {

    /**
     * preservation
     */
    int savaUser(@Param("user") User user);

    /**
     * query
     */
    List<User> findByid(@Param("id") int id);

    /**
     * add to
     */
    int addUser(@Param("id") int id, @Param("user") User user);

    /**
     * delete
     */
    int deleteById(@Param("id") int id);
}

4. Write configuration file

In the resources folder, create the main configuration file sqlmapconfig. For mybatis xml. It is the core configuration file of mybatis. The content of the configuration file is data source and transaction management. Configuration environment:

• Configure mysql environment:
  1. Configure the type of transaction;
  2. Configure connection pool: configure four basic information of connection database;
• Specify the location of the mapping profile:

<?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>
    <!-- Configure data source -->
    <!--
      default: Current environment default database environment
    -->
    <environments default="mysql">
        <!-- id: Of each database environment ID -->
        <environment id="mysql">
            <!--   Transaction manager, transaction control
                 jdbc: Data source transaction manager, similar to Spring of DataSourceTransactiionManager -->
            <transactionManager type="JDBC"></transactionManager>
            <!--  type: Connection pool type
                  POOLED: use mybatis Self contained data source
                  UNPOOLED: Do not use data sources -->
            <dataSource type="POOLED">
                <!--  Database link configuration -->
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/test?autoReconnect=true"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <!--
       1,Specify the location of the mapping profile, which refers to each dao Stand alone profile
       2,The path must be a backslash
        -->
        <mapper resource="com/mobaijun/dao/mapper/UserDao.xml"/>
    </mappers>
</configuration>

5. Mapping file, directory address: com mobaijun. dao. mapper.*. 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">
<mapper namespace="com.mobaijun.dao.UserDao">
    <resultMap id="BaseResultMap" type="com.mobaijun.entity.User">
        <id column="id" property="id" jdbcType="INTEGER"/>
        <result column="name" property="name" jdbcType="VARCHAR"/>
        <result column="account" property="account" jdbcType="VARCHAR"/>
        <result column="password" property="password" jdbcType="VARCHAR"/>
    </resultMap>

    <sql id="Column_List">
        id,`name`,account,password
    </sql>

    <insert id="saveUser" parameterType="com.mobaijun.entity.User">
        INSERT INTO `user`(`id`, `account`, `name`, `password`)
        VALUES (#{id}, #{account}, #{name}, #{password});
    </insert>

    <delete id="deleteById">
        DELETE
        FROM user
        WHERE id = ${id}
    </delete>

    <select id="findByid" resultMap="BaseResultMap">
        SELECT
        <include refid="Column_List"/>
        FROM user WHERE id=#{id};
    </select>

    <select id="findAll" resultType="com.mobaijun.entity.User">
        SELECT
        <include refid="Column_List"/>
        FROM user
    </select>
</mapper>

• Parameter description

1. namespace: the name used to distinguish different classes
2. id: identifies the sql in the mapping file. It is called the id of the statement. The sql statement is encapsulated in the mappedStatement object, so the id is called the id of the statement
3. sql: it contains all the fields of the table. You can customize or add aliases.
4. parameterType: Specifies the type of input parameter
5. resultType: Specifies the output result type. mybatis maps a row of record data of sql query results to objects of the type specified by resultType. If there are multiple pieces of data, map them separately and put the objects into the container List

1,#{}:A placeholder. preparedStatement Set a value in the placeholder, automatically java Type and jdbc Type conversion.#{}Can effectively prevent sql Inject. #{}Can receive simple type values or pojo Property value. If parameterType Transfer a single simple type value,#{} in parentheses can be value or other names.

2,${}:Indicates splicing sql String, through ${}Can parameterType The incoming content is spliced in sql Medium and not jdbc Type conversion, ${}Can receive simple type values or pojo Property value, if parameterType Transfer a single simple type value, ${}Only in parentheses value. 

6. Write test class

Create the test class com. In the test - > java directory mobaijun. test. MybatisTest. There are 7 steps to realize business requirements.

1. scanning mappper configuration file`SqlMapConfig.xml`
2. establish`SqlSessionFactoryBuilder`factory
3. establish`SqlSessionFactory`factory
4. establish`SqlSession`,contain`CRUD`method
5. obtain Mapper Proxy object of interface
6. Use proxy execution CRUD operation
7. close resource

• Sample code

@Slf4j
@SpringBootTest
public class MybatisTest {

    /**
     * Query by id
     */
    @SneakyThrows
    @Test
    public void findById() {
        // 1. Read the configuration file
        InputStream in = Resources.getResourceAsStream("SqlMapConfig.xml");
        // 2. Create SqlSessionFactory factory
        SqlSessionFactory build = new SqlSessionFactoryBuilder().build(in);
        // 3. Use factory to produce SqlSession object
        SqlSession session = build.openSession();
        // 4. Execute Sql statement
        UserDao userDao = session.getMapper(UserDao.class);
        log.info("Proxy object:" + userDao.getClass());
        // 5. Print results
        List<User> list = userDao.findByid(1);
        // Output result: User(id=1, account=mobai, name = ink white, password=123456)
        list.forEach(System.out::println);
        // 6. Release resources
        session.close();
        in.close();
    }

    /**
     * delete
     */
    @SneakyThrows
    @Test
    public void deleteById() {
        // 1. Read the configuration file
        InputStream in = Resources.getResourceAsStream("SqlMapConfig.xml");
        // 2. Create SqlSessionFactory factory
        SqlSessionFactory build = new SqlSessionFactoryBuilder().build(in);
        // 3. Use factory to produce SqlSession object
        SqlSession session = build.openSession();
        // 4. Execute Sql statement
        UserDao userDao = session.getMapper(UserDao.class);
        log.info("Proxy object:" + userDao.getClass());
        // 5. Execute SQL
        userDao.deleteById(1);
        // 6. Release resources
        session.close();
        in.close();
    }

    /**
     * newly added
     */
    @SneakyThrows
    @Test
    @Rollback
    public void saveUser() {
        // 1. Read the configuration file
        InputStream in = Resources.getResourceAsStream("SqlMapConfig.xml");
        // 2. Create SqlSessionFactory factory
        SqlSessionFactory build = new SqlSessionFactoryBuilder().build(in);
        // 3. Use factory to produce SqlSession object
        SqlSession session = build.openSession();
        // Statement 4. Execute Sql
        UserDao userDao = session.getMapper(UserDao.class);
        User user;
        for (int i = 0; i < 100; i++) {
            user = User.builder()
                    .id(i + 1)
                    .account("mobai123")
                    .name("mobaijun")
                    .password("123456")
                    .build();
            log.info("Proxy object:" + userDao.getClass());
            // 5. Execute SQL
            userDao.saveUser(user);
            // 6. Submission of data
            session.commit();
            System.out.println(user);
        }
        // 7. Release resources
        session.close();
        in.close();
    }
}

CRUD implementation based on Original Dao

You need to implement dao interfaces and dao implementation classes, namely UserDao and UserDaoImpl implementation classes.

The original Dao development has the following problems

• Duplicate code exists in Dao method body: create SqlSession through SqlSessionFactory and call the database operation method of SqlSession
• To call the database operation method of sqlSession, you need to specify the id of the statement, which is hard coded and cannot be used for development and maintenance.

Persistence layer Dao interface

@Mapper
public interface TestUserDao {

    /**
     * Query a user by ID
     */
    TestUser findUserById(@Param("id") Integer id);

    /**
     * Fuzzy query user list according to user name
     */
    List<TestUser> findUserByUserName(@Param("name") String name);

    /**
     * Add user
     */
    int insertUser(TestUser user);

    /**
     * Update user
     */
    void updateUserById(TestUser user);

    /**
     * delete user
     */
    void deleteUserById(@Param("id") Integer id);
}

Implementation class

public class TestUserDaoImpl implements TestUserDao {

    private SqlSessionFactory sqlSessionFactory;

    /**
     * Injection by construction method
     */
    public TestUserDaoImpl(SqlSessionFactory sqlSessionFactory) {
        this.sqlSessionFactory = sqlSessionFactory;
    }

    @Override
    public TestUser findUserById(Integer id) {
        // sqlSession is thread unsafe, so its best use scope is in the method body
        SqlSession sqlSession = sqlSessionFactory.openSession();
        TestUserDao mapper = sqlSession.getMapper(TestUserDao.class);
        TestUser user = mapper.findUserById(id);
        sqlSession.close();
        return user;
    }

    @Override
    public List<TestUser> findUserByUserName(String name) {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        TestUserDao mapper = sqlSession.getMapper(TestUserDao.class);
        List<TestUser> userList = mapper.findUserByUserName(name);
        sqlSession.close();
        return userList;
    }

    @Override
    public int insertUser(TestUser user) {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        TestUserDao mapper = sqlSession.getMapper(TestUserDao.class);
        int i = mapper.insertUser(user);
        sqlSession.commit();
        sqlSession.close();
        return i;
    }

    @Override
    public void updateUserById(TestUser user) {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        TestUserDao mapper = sqlSession.getMapper(TestUserDao.class);
        mapper.updateUserById(user);
        sqlSession.commit();
        sqlSession.close();
    }

    @Override
    public void deleteUserById(Integer id) {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        TestUserDao mapper = sqlSession.getMapper(TestUserDao.class);
        mapper.deleteUserById(id);
        sqlSession.commit();
        sqlSession.close();
    }
}

Mapping 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.mobaijun.dao.TestUserDao">
    <resultMap id="BaseResultMap" type="com.mobaijun.entity.TestUser">
        <id column="id" property="id" jdbcType="INTEGER"/>
        <result column="name" property="name" jdbcType="VARCHAR"/>
        <result column="account" property="account" jdbcType="VARCHAR"/>
        <result column="password" property="password" jdbcType="VARCHAR"/>
    </resultMap>

    <sql id="Column_List">
        id,`name`,account,password
    </sql>

    <!--  Add user  -->
    <insert id="insertUser">
        INSERT INTO `user` (`id`, `account`, `name`, `password`)
        VALUES (#{id}, #{account}, #{name}, #{password});
    </insert>

    <!--  Update user  -->
    <update id="updateUserById">
        UPDATE `test`.`user`
        SET `account`  = #{account},
            `name`     =#{name},
            `password` = #{password}
        WHERE `id` = #{id};
    </update>

    <!-- according to id Delete a user -->
    <delete id="deleteUserById">
        delete
        from user
        where id = #{id}
    </delete>

    <!-- adopt Id Query a user -->
    <select id="findUserById" resultType="com.mobaijun.entity.TestUser">
        SELECT
        <include refid="Column_List"/>
        FROM `user`
        WHERE id = #{id}
    </select>

    <!-- Fuzzy query user list according to user name -->
    <select id="findUserByUserName" resultType="com.mobaijun.entity.TestUser">
        SELECT *
        FROM `user`
        WHERE name LIKE '%'#{name}'%'
    </select>

</mapper>

Mybatis configuration file

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <!-- Configure data source -->
    <environments default="mysql">
        <!--  to configure MySQL environment-->
        <environment id="mysql">
            <!--  Configure transaction type -->
            <transactionManager type="JDBC"></transactionManager>
            <!--  Configure connection pool -->
            <dataSource type="POOLED">
                <!--  Database link configuration -->
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/test?autoReconnect=true"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <!--
       1,Specify the location of the mapping profile, which refers to each dao Stand alone profile
       2,*Indicates a wildcard, indicating mapper All in the directory .xml Suffix terminated file
        -->
        <mapper resource="com/mobaijun/dao/mapper/UserDao.xml"/>
        <mapper resource="com/mobaijun/dao/mapper/TestUserDao.xml"/>
    </mappers>
</configuration>

Source address: spring-boot-mybatis