Basic use of Mybatis

Learning objectives

1. Meet Mybatis

2. Learn about Mybatis features

3. Know why to learn Mybatis

4. Use Mybatis to write a simple program

5. Learn to use the basic functions of Mybatis

6. Understand the basic configuration of Mybatis

1, Meet Mybatis

Official documents

1. Introduction

MyBatis was apache An open source project iBatis In 2010, the project was migrated from apache software foundation to google code and renamed mybatis. In essence, mybatis made some improvements to ibatis.

MyBatis is an excellent persistence layer framework. It encapsulates the process of jdbc operating the database and shields the underlying access details of jdbc api, so that developers only need to pay attention to SQL itself, and do not need to spend energy to deal with complicated jdbc process codes such as registering drivers, creating connection s, creating statement s, manually setting parameters, result set retrieval and so on.

2. Principle

Mybatis configures various statements (statement, preparedStatemnt, CallableStatement) to be executed through xml or annotation, and maps java objects and sql in the statement to generate the final executed sql statement. Finally, the mybatis framework executes sql, maps the results into java objects and returns them.

3. Why use Mybatis

Native jdbc connection

import org.junit.jupiter.api.Test;

import java.sql.*;


public class MySQLTest {
    static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://localhost:3306/hist?characterEncoding=utf-8&useSSL=false&allowPublicKeyRetrieval=true" +
            "&serverTimezone=UTC";

    // The user name and password of the database need to be set according to your own settings
    static final String USER = "root";
    static final String PASS = "123456";

    /**
     * Test native sql query
     */
    @Test
    public void testQuery() {
        Connection conn = null;
        Statement stmt = null;
        try {
            // 1. Register JDBC Driver
            Class.forName(JDBC_DRIVER);

            // 2. Open link
            System.out.println("Connect to database...");
            conn = DriverManager.getConnection(DB_URL, USER, PASS);

            // 3. Execute query
            System.out.println(" instantiation  Statement object...");
            stmt = conn.createStatement();

            String sql;
            sql = "SELECT runoob_id, runoob_title, runoob_author FROM runoob_tbl WHERE runoob_title='study PHP'";
            ResultSet rs = stmt.executeQuery(sql);

            // 4. Expand the result set database
            while (rs.next()) {
                System.out.println(rs.getString("runoob_title"));
                // Retrieve by field
                int id = rs.getInt("runoob_id");
                String name = rs.getString("runoob_title");
                String author = rs.getString("runoob_author");

                // output data
                System.out.print("ID: " + id);
                System.out.print("name: " + name);
                System.out.print("author: " + author);
                System.out.print("\n");
            }
            // 5. Close after completion
            rs.close();
            stmt.close();
            conn.close();
        } catch (Exception se) {
            // Handling JDBC errors
            se.printStackTrace();
        }// Process class Forname error
        finally {
            // close resource
            try {
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException ignored) {
            }// Don't do anything?
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException se) {
                se.printStackTrace();
            }
        }
        System.out.println("Goodbye!");
    }
}

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

    <groupId>org.example</groupId>
    <artifactId>jdbctest</artifactId>
    <version>1.0-SNAPSHOT</version>
    <dependencies>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.11</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.13.2</version>
            <scope>test</scope>
        </dependency>
    </dependencies>
</project>

Existing problems

1. Frequent opening and closing of database connections will seriously affect the performance of the database.

2. Hard coding exists in the code, which is the hard coding of the database part and the hard coding of the SQL execution part.

Existence of Mybatis:

• Mybatis is to help us store data in the database and get data from the database

• MyBatis is a semi-automatic ORM framework (object relationship mapping) - > object relationship mapping

Of course, all things can still be done without Mybatis, but with it, all the implementation will be easier!

4. Advantages of mybatis

• Easy to learn: itself is small and simple. There is no third-party dependency. The simplest installation is just two jar files + several sql mapping files. 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 for unified management and optimization. All requirements for operating the database can be met through sql statements.

• Decouple sql and program code: by providing DAO layer, separate business logic and data access logic, so as to make the system design 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.

• Large number of users, the company needs

5. The core of mybatis

• Mybatis configuration file, including mybatis * * * * global configuration file and mybatis mapping file * *, where the global configuration file configures data source, transaction and other information; The mapping file configures information related to SQL execution.

• mybatis constructs SqlSessionFactory, that is, session factory, by reading configuration file information (global configuration file and mapping file).

• Through SqlSessionFactory, you can create a SqlSession, that is, a session. Mybatis operates the database through SqlSession.

• SqlSession itself cannot directly operate the database. It operates the database through the underlying Executor executor interface. The Executor interface has two implementation classes, one is a normal Executor and the other is a cache Executor (default).

• The SQL information to be processed by the Executor executor is encapsulated in an underlying object MappedStatement. The object includes: SQL statement, input parameter mapping information and output result set mapping information. The mapping types of input parameters and output results include HashMap collection object and POJO object type.

6. How to get mybatis

• Maven

<dependency>
  <groupId>org.mybatis</groupId>
  <artifactId>mybatis</artifactId>
  <version>x.x.x</version>
</dependency>

• Github: https://github.com/mybatis/mybatis-3

• Chinese notes of source code: https://github.com/tuguangquan/mybatis
  To use MyBatis, simply mybatis-x.x.x.jar The file can be placed in the classpath.

2, Getting started with Mybatis

Project structure

1. Environmental construction

• Create database

CREATE DATABASE `mybatis_01`;

USE `mybatis_01`;

DROP TABLE IF EXISTS `user`;

CREATE TABLE `user` (
`id` int(20) NOT NULL,
`name` varchar(30) DEFAULT NULL,
`phone` varchar(30) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert  into `user`(`id`,`name`,`phone`) values (1,'Zhang San','1536537156'),(2,'Li Si','1536537157'),(3,'Wang Wu','1536537158');

Create a Maven project as the parent project

• Import related Maven dependencies

	<dependencies>
        <!--   mybatis rely on     -->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.2</version>
        </dependency>
        <!-- Database driven-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <!-- <version>5.1.47</version>-->
            <version>8.0.11</version>

        </dependency>
        <!--        <dependency>-->
        <!--            <groupId>mysql</groupId>-->
        <!--            <artifactId>mysql-connector-java</artifactId>-->
        <!--            <version>8.0.12</version>-->
        <!--        </dependency>-->
        <!--Test dependency-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.11</version>
        </dependency>
    </dependencies>
    <!--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>

2. Create a module and write the MyBatis core configuration file

mybatis-config.xml

<?xml version="1.0" encoding="GBK" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <!-- to configure mybatis Environmental information -->
    <environments default="development">
        <environment id="development">
            <!-- to configure JDBC Transaction control, by mybatis Manage -->
            <transactionManager type="JDBC"/>
            <!-- Configure data sources using traditional javax. sql. DataSource Connection pool in specification -->
            <dataSource type="POOLED">
                <!--       If the database driver uses 8.0.12 The following drivers are required        -->
                <!--<property name="driver" value="com.mysql.cj.jdbc.Driver"/>-->
                <property name="driver" value="com.mysql.jdbc.Driver"/>

                <!--       If the database driver uses 8.0.12 The following configuration is required         -->
                <!--<property name="url" value="jdbc:mysql://localhost:3306/mybatis_01?useSSL=false&amp;useUnicode=true&amp;serverTimezone=UTC&amp;characterEncoding=utf8"/>-->
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis_01?useSSL=false&amp;useUnicode=true&amp;characterEncoding=utf8"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="com/hist/dao/UserMapper.xml"/>
    </mappers>
</configuration>

3. Write MyBatis tool class

MybatisUtil

public class MybatisUtil {
    private static SqlSessionFactory sqlSessionFactory;

    static {
        try {
            // 1. Read the configuration file
            String resource = "mybatis-config.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            // 2. Create sqlSessionFactory session factory through SqlSessionFactoryBuilder
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     *Function Description: get SqlSession connection
    */
    public static SqlSession getSession(){
        // Create SqlSession using sqlSessionFactory
        return sqlSessionFactory.openSession();
    }
}

4. Create entity class

User

public class User {
   
   private int id;  //id
   private String name;   //full name
   private String phone;   //cell-phone number
   
   //Structure, with and without parameters
   //set/get
   //toString()
   
}

5. Write Mapper interface

public interface UserMapper {
    /**
     *Function Description: query user collection
     * @return java.util.List<com.hist.entity.User>
    */
    List<User> selectUser();
}

6. Write mapper XML configuration file

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!-- 
	namespace: Namespace, which is used to SQL Classified management can be understood as SQL quarantine
	Note: use mapper During agent development, namespace Has a special and important role
 -->

<mapper namespace="com.hist.dao.UserMapper">
    <select id="selectUser" resultType="com.hist.entity.User">
      select * from user
     </select>
</mapper>

7. Write test class

public class UserTest {
    @Test
    public void selectUser() {
        SqlSession session = MybatisUtil.getSession();
        UserMapper mapper = session.getMapper(UserMapper.class);
        // Call SqlSession to operate the database
        List<User> users = mapper.selectUser();

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

8. Operation results

3, More case explanations

1. Query user by id

Add method in UserMapper

public interface UserMapper {
    /**
     *Function Description: query user collection
     * @return java.util.List<com.hist.entity.User	>
    */
    List<User> selectUser();
    /**
     *Function Description: query users by id
     * @param id User id
     * @return com.hist.entity.User
    */
    User selectUserById(int id);
}

In usermapper Add Select statement to XML

<!-- 
		[id]: statement of id，Requires to be unique within a namespace  
		[parameterType]: Participating java type
		[resultType]: Corresponding to a single result set queried java type
		[#{}]: represents a placeholder?
		[#{id}]: Indicates that the name of the parameter to be received by the placeholder is id. Note: if the parameter is a simple type,#The parameter name in {} can be defined arbitrarily
	 -->
	<select id="selectUserById" parameterType="int" resultType="com.hist.entity.User">
		SELECT * FROM USER WHERE id = #{id}
	</select>

Add in test class

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

Operation results

2. Query according to multiple parameters

Add in UserMapper

/**
     *Function Description: query users according to multiple parameters
     * @param name user name
     * @param phone cell-phone number
     * @return com.hist.entity.User
    */
    User selectUserByParams(String name, String phone);

In usermapper Add to XML

    <select id="selectUserByParams" resultType="com.hist.entity.User">
		SELECT * FROM USER WHERE name = #{name} and phone = #{phone}
	</select>

Test class

    @Test
    public void testSelectUserByParams() {
        SqlSession session = MybatisUtil.getSession();  //Get SqlSession connection
        UserMapper mapper = session.getMapper(UserMapper.class);
        String name = "Wang Wu";
        String phone = "1536537158";
        User user = mapper.selectUserByParams(name, phone);
        System.out.println(user);
        session.close();
    }

Operation results

Error reporting reason

Because java does not save the record of formal parameters, java will change the parameters in selectUserByParams(name, phone) to selectUserByParams(int arg0,int arg1) when running, so we can't pass multiple parameters.

Solution 1:

Add before parameter @Param Annotation mybatis provides the @ Param annotation to name the incoming parameters

/**
     * Function Description: query users according to multiple parameters
     *
     * @param name  user name
     * @param phone cell-phone number
     * @return com.hist.entity.User
     */
    User selectUserByParams(@Param("name") String name, @Param("phone") String phone);

Solution 2:

Using Map

User selectUserByParams2(Map<String,String> map);
-------
<select id="selectUserByParams2" parameterType="map" resultType="com.hist.entity.User">
    SELECT * FROM USER WHERE name = #{name} and phone = #{phone}
</select>
--------
Map<String,String> map = new HashMap<String, String>();
map.put("name","Wang Wu");
map.put("phone","1536537158");
User user = mapper.selectUserByParams2(map);

Comparison of the two methods:

The disadvantage of Map method lies in its poor readability. You must read its keys every time to understand its role. It is difficult to maintain in the later stage.

@Param, readable

When the parameters are 2-5, @ param is the best. When it is greater than 5, map will be selected

Map alternatives:

Use BO transfer

Create UserBo in entity

public class UserBO {
    private String name;
    private String phone;
    //Structure, with and without parameters
   //set/get
   //toString()
}

UserMapper

User selectUserByParams3(UserBO userBO);

UserMapper

<select id="selectUserByParams3" parameterType="com.hist.entity.UserBO" resultType="com.hist.entity.User">
    SELECT * FROM USER WHERE name = #{name} and phone = #{phone}
</select>

Test class

    @Test
    public void testSelectUserByParams3() {
        SqlSession session = MybatisUtil.getSession();  //Get SqlSession connection
        UserMapper mapper = session.getMapper(UserMapper.class);
        UserBO userDTO = new UserBO();
        userDTO.setName("Wang Wu");
        userDTO.setPhone("1536537158");
        User user = mapper.selectUserByParams3(userDTO);
        System.out.println(user);
        session.close();
    }

An article clearly explains the differences between VO, BO, PO, DO and DTO

3. Fuzzy query by name

Add in UserMapper

/**
 *Function Description: fuzzy query by name
 * @param name User name
 * @return com.hist.entity.User
*/
User selectUserLikeByName(String name);

In usermapper Add Select statement to XML

<!-- 
		[${}]: Indicates splicing SQL character string
	 	[${value}]: Indicates that a simple type parameter is to be spliced.
		 be careful:
		1,If the parameter is a simple type, ${}The parameter name inside must be value 
		2,${}Will cause SQL Injection, generally not recommended. However, some scenarios must be used ${}，such as order by ${colname}
	-->

<select id="selectUserLikeByName" parameterType="String" resultType="com.hist.entity.User">
    select * from USER where name like '%${value}%'
</select>

Add in test class

    @Test
    public void testSelectUserLikeByName() {
        SqlSession session = MybatisUtil.getSession();  //Get SqlSession connection
        UserMapper mapper = session.getMapper(UserMapper.class);
        String name = "Lee";
        User user = mapper.selectUserLikeByName(name);
        System.out.println(user);
        session.close();
    }

**#{} * * and ${}

#{}: equivalent to placeholder in preprocessing?.

#The parameter in {} represents the name of the received java input parameter.

#{} can accept parameters of HashMap and POJO types.

When a simple type parameter is accepted, #{} it can be either value or other parameters.

#{} can prevent SQL injection.

${}: it is equivalent to splicing SQL strings and outputting the passed in values without any explanation.

${} causes SQL injection, so use it with caution.

${} can accept parameters of HashMap and POJO types.

When accepting simple type parameters, ${} can only be value.

4. New user

Add in UserMapper

    /**
     *Function Description: add a new user
     * @param user User information
    */
    int addUser(User user);

In usermapper Add insert statement to XML

    <insert id="addUser" parameterType="com.hist.entity.User">
        insert into user (id,name,phone) values (#{id},#{name},#{phone})
    </insert>

If the primary key value is generated through MySQL auto increment mechanism, the id can no longer be explicitly assigned here

    <insert id="addUser" parameterType="com.hist.entity.User">
        insert into user (name,phone) values (#{name},#{phone})
    </insert>

Add in test class

    @Test
    public void testAddUser() {
        SqlSession session = MybatisUtil.getSession();
        UserMapper mapper = session.getMapper(UserMapper.class);
        User user = new User(4,"Saturday","15936537150");
        int i = mapper.addUser(user);
        System.out.println(i);
        //Commit transaction, focus! If it is not written, it will not be submitted to the database
        session.commit(); 
        session.close();
    }

5. Modify user

Add in UserMapper

/**
 *Function Description: user modification
 * @param user User information to be modified
 * @return int
*/
int updateUser(User user);

In usermapper Add update statement to XML

<update id="updateUser" parameterType="com.hist.entity.User">
    update user set name=#{name},phone=#{phone} where id = #{id}
</update>

Add in test class

    @Test
    public void testUpdateUser() {
        SqlSession session = MybatisUtil.getSession();
        UserMapper mapper = session.getMapper(UserMapper.class);
        User user = new User();
        user.setId(1);
        user.setName("Li sisi");
        user.setPhone("12345678910");
        int i = mapper.updateUser(user);
        System.out.println(i);
        // Commit transaction
        session.commit();
        session.close();
    }

6. Delete user

Add in UserMapper

    /**
     *Function Description: delete user
     * @param id User id to be deleted
     * @return int
    */
    int deleteUser(int id);

In usermapper Add delete statement to XML

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

Add in test class

    @Test
    public void testDeleteUser() {
        SqlSession session = MybatisUtil.getSession();
        UserMapper mapper = session.getMapper(UserMapper.class);
        int i = mapper.deleteUser(5);
        System.out.println(i);
        //Commit transaction, focus! If it is not written, it will not be submitted to the database
        session.commit(); 
        session.close();
    }

4, Configuration resolution

1. 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">
<configuration>
    <!-- to configure mybatis Environmental information -->
    <environments default="development">
        <environment id="development">
            <!-- to configure JDBC Transaction control, by mybatis Manage -->
            <transactionManager type="JDBC"/>
            <!-- Configure data sources using traditional javax. sql. DataSource Connection pool in specification -->
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis_01?useSSL=false&amp;useUnicode=true&amp;characterEncoding=utf8"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>
    <!-- Mapper : Define mapping SQL Statement file -->
    <mappers>
        <mapper resource="com/hist/dao/UserMapper.xml"/>
    </mappers>
</configuration>

dataSource has three built-in data source types

type="[UNPOOLED|POOLED|JNDI]"

• unpooled: the implementation of this data source only opens and closes the connection every time it is requested.

• pooled: the implementation of this data source uses the concept of "pool" to organize JDBC connection objects, which is a popular way to make concurrent Web applications respond to requests quickly.

• JNDI: this data source is implemented to be used in containers such as Spring or application server. The container can configure the data source centrally or externally, and then place a reference to the JNDI context.

• Data sources also have many third-party implementations, such as dbcp, c3p0, druid and so on

Usage of

<!-- 1. Use resource references relative to Classpaths -->
<mappers>
 <mapper resource="com/hist/dao/UserMapper.xml"/>
</mappers>

<!-- Not used
2. Use fully qualified path 
-->
<mappers>
 <mapper url="F:\teach\project\mybatis-01\src\main\java\com\hist\dao\UserMapper.xml"/>
</mappers>

<!--
3. Use the mapper interface to implement the fully qualified class name of the class
 need mapper Interface and mapper The mapping files have the same name and are placed in the same directory
-->
<mappers>
 <mapper class="com.hist.dao.UserMapper"/>
</mappers>

<!-- Recommended use
4. Register all the mapper interface implementations in the package as mappers
 need mapper Interface and mapper The mapping files have the same name and are placed in the same directory
-->
<mappers>
 <package name="com.hist.dao"/>
</mappers>

2. 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.hist.mapper.UserMapper">
   
</mapper>

After the namespace binds the interface, there is no need to write the interface implementation class. mybatis will automatically help you find the corresponding SQL statement to be executed through the binding

Namespace Chinese meaning: namespace:

• The name of a namespace must have the same name as an interface

• The method in the interface should correspond to the sql statement id in the mapping file one by one

<select id="selectUser" resultType="com.hist.entity.User">
    select * from user
</select>

• namespace naming rules: package name + class name

3. properties database file configuration

properties database file configuration, which can be replaced by external configuration

Create a new DB Properties file

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mybatis_01?useSSL=false&useUnicode=true&characterEncoding=utf8
username=root
password=123456

Modify mybatis config xml

4. typeAliases

Alias is used to specify the type of parameter and result set in the mapping file more conveniently, instead of writing a long full qualified name.

5. Log4j configuration

For the previous development process, we often use the debug mode to adjust and track our code execution process. But now using Mybatis is a source code execution process based on interface and configuration file. Therefore, we must choose logging tool as our tool to develop and adjust programs.

• Log4j is an open source project of Apache

• By using Log4j, we can control the destination of log information delivery: console, text, GUI components

• We can also control the output format of each log;

• By defining the level of each log information, we can control the log generation process in more detail. The most interesting thing is that these can be flexibly configured through a configuration file without modifying the application code.

Use steps:

1. Import log4j's package

<dependency>
   <groupId>log4j</groupId>
   <artifactId>log4j</artifactId>
   <version>1.2.17</version>
</dependency>

2. Configuration file writing

The log level is set to DEBUG in the development phase and INFO or ERROR in the production phase.

Log4j recommends using only four levels. The priority from high to low is ERROR, WARN, INFO and DEBUG. Through the levels defined here, you can control the switch to the corresponding level of log information in the application. For example, if the INFO level is defined here, the log information of all DEBUG levels in the application will not be printed, that is, the logs of levels greater than or equal to will be output

# Global log configuration
log4j.rootLogger=WARN, stdout
# For MyBatis log configuration, print a group of mapper logs. You only need to turn on the log function of the package where the mapper is located
log4j.logger.com.hist.dao.UserMapper=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

3. Setting setting log implementation

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

4 use Log4j for output in the program!

public class UserTest {
    static Logger logger = Logger.getLogger(UserTest.class);
    @Test
    public void selectUser() {
        logger.info("info: get into selectUser method");
        logger.debug("debug: get into selectUser method");
        logger.error("error: get into selectUser method");
        SqlSession session = MybatisUtil.getSession();
        UserMapper mapper = session.getMapper(UserMapper.class);
        List<User> users = mapper.selectUser();

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

5, ResultMap result set mapping

Resolve the inconsistency between the attribute name of entity class and the field name of database table

Scene restore

Create a User1 entity class

public class User1 {
    private int id;
    private String name;
    private String number; // The mobile phone number is different from that in the database
   //Construction method
   //set/get
   //toString()
}

Create an interface

 /**
     * Function Description: query users by id
     *
     * @param id User id
     * @return com.hist.module.User
     */
    User1 selectUser1ById(int id);

Create a query statement

<select id="selectUser1ById" parameterType="int" resultType="com.hist.entity.User1">
    SELECT * FROM USER WHERE id = #{id}
</select>

test

    @Test
    public void testSelectUser1ById() {
        SqlSession session = MybatisUtil.getSession();  //Get SqlSession connection
        UserMapper mapper = session.getMapper(UserMapper.class);
        User1 user = mapper.selectUser1ById(1);
        System.out.println(user);
        session.close();
    }

Operation results

analysis:

• select * from user where id = #{id} can be regarded as
  select id,name,phone from user where id = #{id}

• mybatis will find the set method 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 setPhone() cannot be found, number returns null; [automatic mapping]

Solution

1. Set the column alias (which is a basic SQL feature) in the SELECT statement to complete the matching

<select id="selectUser1ById" parameterType="int" resultType="com.hist.entity.User1">
  select
    id,
    name,
    phone as number
  from user
  where id = #{id}
</select>

1. Use result set mapping - > resultmap

<resultMap id="UserMap" type="com.hist.entity.User1">
    <!-- id As primary key -->
    <id column="id" property="id"/>
    <!-- column Is the column name of the database table , property Is the property name of the corresponding entity class -->
    <result column="name" property="name"/>
    <result column="phone" property="number"/>
</resultMap>
<select id="selectUser1ById" parameterType="int" resultMap="UserMap">
    SELECT * FROM USER WHERE id = #{id}
</select>

6, Lombok use (recommended)

https://zhuanlan.zhihu.com/p/146659383

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.16.10</version>
</dependency>

3. Add comments to the code

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

7, Annotation enables addition, deletion, modification and query

1. Check

 /**
     * Function Description: query user collection
     *
     * @return java.util.List<com.hist.module.User>
     */
    @Select("select * from user")
    List<User> selectUser();

Inject in the core configuration file of mybatis

<!--use class Binding interface-->
<mappers>
   <mapper class="com.hist.dao"/>
</mappers>

test

    @Test
    public void selectUser() {
        SqlSession session = MybatisUtil.getSession();
        UserMapper mapper = session.getMapper(UserMapper.class);
        List<User> users = mapper.selectUser();

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

2. Increase

Transform the getSession() method of MybatisUtils tool class and overload the implementation.

public static SqlSession getSession(){
    //Transaction auto commit
    return sqlSessionFactory.openSession(true);
}

Write interface addition method

//Add a user
@Insert("insert into user (id,name,phone) values (#{id},#{name},#{phone})")
int addUser(User user);

test

    @Test
    public void testAddUser() {
        SqlSession session = MybatisUtil.getSession();
        UserMapper mapper = session.getMapper(UserMapper.class);
        User user = new User(5, "Saturday", "15936537150");
        int i = mapper.addUser(user);
        System.out.println(i);
        session.close();
    }

3. Delete

Write delete interface

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

test

    @Test
    public void testDeleteUser() {
        SqlSession session = MybatisUtil.getSession();
        UserMapper mapper = session.getMapper(UserMapper.class);
        int i = mapper.deleteUser(5);
        System.out.println(i);
        session.close();
    }

4. Modification

Write modify interface

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

test

    @Test
    public void testUpdateUser() {
        SqlSession session = MybatisUtil.getSession();
        UserMapper mapper = session.getMapper(UserMapper.class);
        User user = new User();
        user.setId(1);
        user.setName("Li 2Si");
        user.setPhone("12345678910");
        int i = mapper.updateUser(user);
        System.out.println(i);
        session.close();
    }