Project Dependencies

To begin using MyBatis with MySQL, add the following libraries to your build:

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

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.28</version>
</dependency>

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

XML-Based MyBatis Setup

Step 1: Define a Domain Model

Create a POJO matching your database column names:

package com.miao.mybatis.pojo;

public class User {
    private Integer id;
    private String username;
    private String password;
    private Integer age;
    private String gender;
    private String email;

    public User() {}

    public User(Integer id, String username, String password, Integer age, String gender, String email) {
        this.id = id;
        this.username = username;
        this.password = password;
        this.age = age;
        this.gender = gender;
        this.email = email;
    }

    public Integer getId() { return id; }
    public void setId(Integer id) { this.id = id; }

    public String getUsername() { return username; }
    public void setUsername(String username) { this.username = username; }

    public String getPassword() { return password; }
    public void setPassword(String password) { this.password = password; }

    public Integer getAge() { return age; }
    public void setAge(Integer age) { this.age = age; }

    public String getGender() { return gender; }
    public void setGender(String gender) { this.gender = gender; }

    public String getEmail() { return email; }
    public void setEmail(String email) { this.email = email; }

    @Override
    public String toString() {
        return "User{" +
            "id=" + id +
            ", username='" + username + '\'' +
            ", password='" + password + '\'' +
            ", age=" + age +
            ", gender='" + gender + '\'' +
            ", email='" + email + '\'' +
            '}';
    }
}

Step 2: Main Configuration File

The mybatis-config.xml defines environments, type aliases, and mapper references:

<?xml version="1.0" encoding="UTF-8" ?>
<configuration>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://101.43.117.227:3308/SSM?serverTimezone=UTC"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper resource="mappers/UserMapper.xml"/>
    </mappers>
</configuration>

Step 3: Mapper Interface

package com.miao.mybatis.mapper;

public interface UserMapper {
    int addUser();
}

Step 4: SQL Mapping File

The namespace points to the mapper interface, and statement IDs match method names:

<?xml version="1.0" encoding="UTF-8" ?>
<mapper namespace="com.miao.mybatis.mapper.UserMapper">
    <insert id="addUser">
        insert into t_user values(null, 'admin', '123456', 23, '男', '123456@qq.com');
    </insert>
</mapper>

Step 5: Executing Operations

Build a SqlSessionFactory from the configuration, then obtain a session and mapper proxy:

package com.miao.mybatis.test;

import com.miao.mybatis.mapper.UserMapper;
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.InputStream;

public class MyBatisTest {
    @Test
    public void testAdd() throws Exception {
        InputStream configStream = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(configStream);

        try (SqlSession sqlSession = sessionFactory.openSession()) {
            UserMapper mapper = sqlSession.getMapper(UserMapper.class);
            int affected = mapper.addUser();
            System.out.println("Inserted rows: " + affected);
            sqlSession.commit();
        }
    }
}

Log Configuration

Place a log4j.properties file in the classpath to capture SQL execution details:

log4j.rootLogger=DEBUG,console,file

log4j.appender.console=org.apache.log4j.ConsoleAppender
log4j.appender.console.Target=System.out
log4j.appender.console.Threshold=DEBUG
log4j.appender.console.layout=org.apache.log4j.PatternLayout
log4j.appender.console.layout.ConversionPattern=[%c]-%m%n

log4j.appender.file=org.apache.log4j.RollingFileAppender
log4j.appender.file.File=./log/dao.log
log4j.appender.file.MaxFileSize=10mb
log4j.appender.file.Threshold=DEBUG
log4j.appender.file.layout=org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern=[%p][%d{yy-MM-dd}][%c]%m%n

log4j.logger.org.mybatis=DEBUG
log4j.logger.java.sql=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.ResultSet=DEBUG
log4j.logger.java.sql.PreparedStatement=DEBUG

Extended Configurasion Elements

Element order within <configuration> must follow: properties, settings, typeAliases, typeHandlers, objectFactory, objectWrapperFactory, reflectorFactory, plugins, environments, databaseIdProvider, mappers.

External Properties

<properties resource="jdbc.properties"/>

Type Alias Shortcuts

<typeAliases>
    <package name="com.miao.mybatis.pojo"/>
</typeAliases>

Mapper Registration

<mappers>
    <package name="com.miao.mybatis.mapper"/>
</mappers>

Settings for Mapping Conventions and Cache

<settings>
    <setting name="mapUnderscoreToCamelCase" value="true"/>
    <setting name="lazyLoadingEnabled" value="true"/>
    <setting name="aggressiveLazyLoading" value="false"/>
    <setting name="cacheEnabled" value="true"/>
</settings>

Interceptor Plugins

<plugins>
    <plugin interceptor="com.github.pagehelper.PageInterceptor"></plugin>
</plugins>

Configuraton and Mapper Templates

mybatis-config.xml

<?xml version="1.0" encoding="UTF-8" ?>
<configuration>
    <properties resource="jdbc.properties"/>

    <settings>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
        <setting name="lazyLoadingEnabled" value="true"/>
        <setting name="aggressiveLazyLoading" value="false"/>
        <setting name="cacheEnabled" value="true"/>
    </settings>

    <typeAliases>
        <package name="com.miao.mybatis.pojo"/>
    </typeAliases>

    <plugins>
        <plugin interceptor="com.github.pagehelper.PageInterceptor"></plugin>
    </plugins>

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

    <mappers>
        <package name="com.miao.mybatis.mapper"/>
    </mappers>
</configuration>

XxxMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<mapper namespace="com.miao.mapper.UserMapper">
</mapper>

Parameter Binding Approaches

MyBatis offers two placeholder styles:

• ${} performs string interpolation, which can lead to SQL injection.
• #{} uses prepared statement placeholders, safe against injection.

Binding Scenarios

Single literal argument

User findByUsername(String username);

<select id="findByUsername" resultType="User">
    SELECT * FROM t_user WHERE username = #{username}
</select>

Multiple literal arguments — access via arg0, arg1 or param1, param2:

User authenticate(String username, String password);

<select id="authenticate" resultType="User">
    SELECT * FROM t_user WHERE username = #{param1} AND password = #{param2}
</select>

Map parameter — use the map’s keys directly:

User authenticateWithMap(Map<String, String> credentials);

<select id="authenticateWithMap" resultType="User">
    SELECT * FROM t_user WHERE username = #{username} AND password = #{password}
</select>

Entity parameter — references property names:

int persistUser(User user);

<insert id="persistUser">
    INSERT INTO t_user VALUES(#{id}, #{username}, #{password}, #{age}, #{gender}, #{email})
</insert>

Named parameters with @Param — preferred for clarity:

User authenticateWithParam(@Param("login") String username, @Param("pass") String password);

<select id="authenticateWithParam" resultType="User">
    SELECT * FROM t_user WHERE username = #{login} AND password = #{pass}
</select>

Query Result Types

Single entity

User fetchById(@Param("id") Integer id);

<select id="fetchById" resultType="User">
    SELECT * FROM t_user WHERE id = #{id}
</select>

List of entities

List<User> listAll();

<select id="listAll" resultType="User">
    SELECT * FROM t_user
</select>

Scalar value

Integer countAll();

<select id="countAll" resultType="Integer">
    SELECT COUNT(*) FROM t_user
</select>

Single row as a map

Map<String, Object> fetchByIdAsMap(@Param("id") Integer id);

<select id="fetchByIdAsMap" resultType="map">
    SELECT * FROM t_user WHERE id = #{id}
</select>

Multiple rows as list of maps, or a keyed map with @MapKey

@MapKey("id")
Map<String, Object> listAllAsMap();

List<Map<String, Object>> listAllAsMapList();

<select id="listAllAsMap" resultType="map">
    SELECT * FROM t_user
</select>

Special SQL Operations

Like Search

List<User> searchByKeyword(@Param("keyword") String keyword);

<select id="searchByKeyword" resultType="User">
    SELECT * FROM t_user WHERE username LIKE CONCAT('%', #{keyword}, '%')
</select>

Batch Deletion

void deleteByIds(@Param("idList") String idList);

<delete id="deleteByIds">
    DELETE FROM t_user WHERE id IN (${idList})
</delete>

Dynamic Table Name

List<User> fetchFromTable(@Param("table") String table);

<select id="fetchFromTable" resultType="User">
    SELECT * FROM ${table}
</select>

Retrieving Auto-Generated Keys

void addUserAndReturnKey(User user);

<insert id="addUserAndReturnKey" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO t_user VALUES(null, #{username}, #{password}, #{age}, #{gender}, #{email})
</insert>

Custom Result Mapping

When column names differ from property names, use either automatic mapping or explicit <resultMap> definitions.

Automatic Underscore-to-Camel Mapping

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

Defining a Result Map

<resultMap id="empMap" type="Emp">
    <id column="emp_id" property="empId"/>
    <result column="emp_name" property="empName"/>
    <result column="emp_age" property="empAge"/>
    <result column="emp_gender" property="empGender"/>
</resultMap>
<select id="findEmpById" resultMap="empMap">
    SELECT * FROM t_emp WHERE emp_id = #{empId}
</select>

Many-to-One Associations

When a Emp entity holds a Dept reference, there are several mapping strategies:

Cascading property assignment

<resultMap id="empDeptCascade" type="Emp">
    <id column="emp_id" property="empId"/>
    <result column="emp_name" property="empName"/>
    <result column="dept_id" property="dept.deptId"/>
    <result column="dept_name" property="dept.deptName"/>
</resultMap>

Using <association>

<resultMap id="empDeptAssoc" type="Emp">
    <id column="emp_id" property="empId"/>
    <result column="emp_name" property="empName"/>
    <association property="dept" javaType="Dept">
        <id column="dept_id" property="deptId"/>
        <result column="dept_name" property="deptName"/>
    </association>
</resultMap>

Stepwise queries with lazy loading

<resultMap id="empDeptStep" type="Emp">
    <id column="emp_id" property="empId"/>
    <result column="emp_name" property="empName"/>
    <association property="dept"
                 select="com.miao.mybatis.mapper.EmpMapper.fetchDept"
                 column="dept_id"/>
</resultMap>
<select id="fetchEmpStepOne" resultMap="empDeptStep">
    SELECT * FROM t_emp WHERE emp_id = #{empId}
</select>
<select id="fetchDept" resultType="Dept">
    SELECT * FROM t_dept WHERE dept_id = #{deptId}
</select>

Enable lazy loading globally; use fetchType="eager" on <association> to override for specific statements.

One-to-Many Collections

When a Dept includes a list of employees:

<resultMap id="deptEmps" type="Dept">
    <id column="dept_id" property="deptId"/>
    <result column="dept_name" property="deptName"/>
    <collection property="emps" ofType="Emp">
        <id column="emp_id" property="empId"/>
        <result column="emp_name" property="empName"/>
    </collection>
</resultMap>
<select id="fetchDeptWithEmps" resultMap="deptEmps">
    SELECT * FROM t_dept LEFT JOIN t_emp ON t_dept.dept_id = t_emp.dept_id WHERE t_dept.dept_id = #{deptId}
</select>

Stepwise version:

<resultMap id="deptEmpsStep" type="Dept">
    <id column="dept_id" property="deptId"/>
    <collection property="emps"
                select="com.miao.mybatis.mapper.EmpMapper.listEmpsByDept"
                column="dept_id"/>
</resultMap>

Dynamic SQL Construction

Conditional Clauses with <where> and <trim>

<select id="filterEmployees" resultType="Emp">
    SELECT * FROM t_emp
    <where>
        <if test="empName != null and empName != ''">
            emp_name = #{empName}
        </if>
        <if test="empAge != null">
            AND emp_age = #{empAge}
        </if>
    </where>
</select>

With <trim>:

<select id="filterEmployeesTrim" resultType="Emp">
    SELECT * FROM t_emp
    <trim prefix="WHERE" suffixOverrides="AND |OR ">
        <if test="empName != null and empName != ''">
            emp_name = #{empName} AND
        </if>
        <if test="empAge != null">
            emp_age = #{empAge}
        </if>
    </trim>
</select>

Switch-like Logic with <choose>

<select id="findByPriority" resultType="Emp">
    SELECT * FROM t_emp
    <where>
        <choose>
            <when test="empName != null and empName != ''">
                emp_name = #{empName}
            </when>
            <when test="empAge != null">
                emp_age = #{empAge}
            </when>
            <otherwise>
                1=0
            </otherwise>
        </choose>
    </where>
</select>

Loop Constructs for Bulk Operations

Batch insert

<insert id="batchInsertEmployees">
    INSERT INTO t_emp (emp_name, emp_age, emp_gender) VALUES
    <foreach collection="employees" item="e" separator=",">
        (#{e.empName}, #{e.empAge}, #{e.empGender})
    </foreach>
</insert>

Batch delete

<delete id="batchDeleteByIds">
    DELETE FROM t_emp WHERE emp_id IN
    <foreach collection="idArray" item="id" open="(" close=")" separator=",">
        #{id}
    </foreach>
</delete>

Reusable SQL Fragments

<sql id="empColumns">emp_id, emp_name, emp_age, dept_id</sql>

<select id="fetchMinimal" resultType="Emp">
    SELECT <include refid="empColumns"/> FROM t_emp
</select>

Caching Mechanisms

First-Level Cache

This cache operates at the SqlSession level and is always active. If the same query is executed twice within the same session without any intervening INSERT, UPDATE, or DELETE, the second call returns the cached result. The cache is invalidated by:

• Using a different SqlSession
• Different query parameters
• Any write operation between the queries
• Explicit sqlSession.clearCache() calls

Emp firstCall = session.getMapper(EmpDao.class).findById(10);
Emp secondCall = session.getMapper(EmpDao.class).findById(10);
// secondCall comes from cache unless session was modified

Second-Level Cache

A factory-wide cache that survives across sessions. Enable it globally with <setting name="cacheEnabled" value="true"/> and add <cache/> inside the mapper XML. Cached data becomes available after the originating session commits or closes. Entity classes must implement Serializable.

Invalidation occurs if any write operation touches the mapped table. The cache can be bypassed for individual statements using useCache="false".

<cache/>
<select id="findById" resultType="Emp" useCache="true">
    SELECT * FROM t_emp WHERE emp_id = #{id}
</select>

Example demonstrating cross-session caching:

InputStream input = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sf = new SqlSessionFactoryBuilder().build(input);

Emp result;
try (SqlSession s1 = sf.openSession()) {
    result = s1.getMapper(EmpDao.class).findById(10);
}

try (SqlSession s2 = sf.openSession()) {
    Emp cachedResult = s2.getMapper(EmpDao.class).findById(10);
    // cachedResult is served from second-level cache
}