MyBatis core configuration

The core components of MyBatis

MyBatis projects typically include the following parts:

• Core configuration files: Configure the runtime environment, data sources, transaction manager and Mapper.
• Mapper interface: Declare database operation methods.
• Mapper XML: Write SQL and establish mappings between methods, parameters, and return results.
• SqlSessionFactory: Create SqlSession based on configuration.
• SqlSession: Executes SQL, obtains Mapper agents, and manages transactions.

core configuration file

The core configuration file is often named mybatis-config.xml. The file name can be customized, but the actual file name must be used when reading the configuration.

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <environments default="mysql">
        <environment id="mysql">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/book_shop"/>
                <property name="username" value="root"/>
                <property name="password" value="1234"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper resource="mapping/BookMapper.xml"/>
    </mappers>
</configuration>

environments can be configured with multiple operating environments, and the value of default must be consistent with the id of a certain environment. The above example selects a mysql environment.

MyBatis not only reads the core configuration file itself, but also loads external property files, Mapper XML and other resources based on the configuration therein. For example, <mapper resource="..."/> means loading the specified mapping file from the classpath, rather than writing the mapping file content directly into the core configuration file.

Using external properties files

Database connection information is usually placed in db.properties to avoid writing directly in XML.

jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/book_shop
jdbc.username=root
jdbc.password=1234

Introduce and use these attributes in the core configuration file:

<configuration>
    <properties resource="db.properties"/>

    <environments default="mysql">
        <environment id="mysql">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>
</configuration>

mapper interface

The Mapper interface is used to declare database operations. MyBatis creates proxy objects for interfaces at runtime, so handwritten implementation classes are usually not required.

public interface BookMapper {
    List<TbBook> queryAllBook();

    TbBook queryById(Integer id);

    int insertBook(TbBook book);
}

The method name, parameter type, and return type should be consistent with the configuration of the corresponding statement in Mapper XML.

Mapper XML

Mapper XML is responsible for defining SQL and result mappings.

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.oracle.dao.BookMapper">
    <select id="queryAllBook" resultType="com.oracle.entity.TbBook">
        SELECT *
        FROM tb_book
    </select>

    <select id="queryById"
            parameterType="java.lang.Integer"
            resultType="com.oracle.entity.TbBook">
        SELECT *
        FROM tb_book
        WHERE id = #{id}
    </select>

    <insert id="insertBook" parameterType="com.oracle.entity.TbBook">
        INSERT INTO tb_book(book_name, author, price)
        VALUES (#{bookName}, #{author}, #{price})
    </insert>
</mapper>

namespace and id

namespace should fill in the fully qualified class name of the Mapper interface, and id should be consistent with the interface method name. Only when BookMapper.queryAllBook() is called can MyBatis locate the corresponding SQL statement.

parameter placeholders

#{} uses pre-compiled parameters to correctly handle types and reduce the risk of SQL injection. #{} should be used first for general business parameters.

${} is a direct concatenation of strings and is usually only used in locations where parameter placeholders cannot be used, such as sorting fields that have undergone strict whitelist verification. Do not put user input directly into ${}.

MyBatis core API

Create SqlSessionFactory

SqlSessionFactoryBuilder creates SqlSessionFactory based on configuration. Factory objects are costly to create, and only one is usually created in applications.

public final class MyBatisUtil {
    private static final SqlSessionFactory SQL_SESSION_FACTORY;

    static {
        try (InputStream inputStream =
                     Resources.getResourceAsStream("mybatis-config.xml")) {
            SQL_SESSION_FACTORY =
                    new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            throw new ExceptionInInitializerError(e);
        }
    }

    private MyBatisUtil() {
    }

    public static SqlSession openSession() {
        return SQL_SESSION_FACTORY.openSession();
    }
}

Using SqlSession

SqlSession is not a thread-safe object and should be created during a business operation and closed in time. After a write operation is performed, the transaction needs to be committed; an exception should be rolled back.

public class BookService {
    public List<TbBook> queryAllBook() {
        try (SqlSession sqlSession = MyBatisUtil.openSession()) {
            BookMapper mapper = sqlSession.getMapper(BookMapper.class);
            return mapper.queryAllBook();
        }
    }

    public int insertBook(TbBook book) {
        try (SqlSession sqlSession = MyBatisUtil.openSession()) {
            BookMapper mapper = sqlSession.getMapper(BookMapper.class);
            try {
                int rows = mapper.insertBook(book);
                sqlSession.commit();
                return rows;
            } catch (RuntimeException e) {
                sqlSession.rollback();
                throw e;
            }
        }
    }
}

You can also use openSession(true) to turn on automatic commit, but manual commit should be used in businesses that require multiple SQL’s to remain atomic.

result mapping

automatic mapping

When the column name of the query result is consistent with the Java attribute name, resultType can be used directly.

<select id="queryById" resultType="com.oracle.entity.TbBook">
    SELECT id, book_name AS bookName, author, price
    FROM tb_book
    WHERE id = #{id}
</select>

If the underscore to hump configuration is enabled, book_name can be automatically mapped to bookName.

<settings>
    <setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>

manual mapping

resultMap should be used when database column names differ significantly from Java attribute names, or complex mappings are required.

<mapper namespace="com.oracle.dao.UserMapper">
    <resultMap id="userMapping" type="com.oracle.entity.User">
        <id property="userId" column="user_id"/>
        <result property="userName" column="user_name"/>
        <result property="realName" column="real_name"/>
        <result property="password" column="password"/>
        <result property="phone" column="phone"/>
        <result property="remain" column="remain"/>
        <result property="operator" column="operator"/>
        <result property="createTime" column="create_time"/>
    </resultMap>

    <select id="queryUserById" resultMap="userMapping">
        SELECT *
        FROM tb_user
        WHERE user_id = #{id}
    </select>
</mapper>

Both resultType and resultMap are used to describe returned results, but only one of them is usually selected for the same query. Use resultType for simple mapping, and use resultMap when you need to clearly specify the correspondence between columns and attributes.

common problems

Unable to find mapping statement

When Invalid bound statement appears, check in turn:

• Whether Mapper XML is located in the correct resource directory.
• Whether the core configuration loads the Mapper.
• Whether namespace is the fully qualified class name of the interface.
• Whether the id on the SQL label is consistent with the interface method name.
• Maven builds whether the XML file is copied to the classpath.

The query result attribute is null

Check whether the database column name, Java attribute name and resultMap are consistent; when using hump naming, also check whether mapUnderscoreToCamelCase is turned on.

The write operation did not take effect

By default, SqlSession will not be automatically submitted. After adding, modifying or deleting, commit() should be called; when an exception occurs, rollback() should be called, and finally the session should be closed.