Two Development Approaches in MyBatis

When working with MyBatis in production environments, developers typically choose between two approaches:

Traditional DAO Implementation This approach requires manually writing DAO interface implementations with explicit SQL session management. The main drawbacks include excessive boilerplate code and hardcoded statement IDs that complicate maintenance.

Mapper Proxy Approach (Recommended) MyBatis can automatically generate proxy objects that implement your DAO interface at runtime. The framework follows specific conventions to locate and execute the corresponding SQL statements:

• The namespace attribute in the mapper XML must match the fully qualified name of the corresponding interface
• Each SQL statement's id must correspond exactly to a method name defined in the interface
• Input parameter types in the XML must align with the method's parameter types
• Return types in the XML must match the method's return types

MyBatis Main Configuration File Structure

The primary configuration file (mybatis-config.xml) organizes settings into several key secsions:

Properties Section External property files containing database connection information can be referenced using the <properties> element, enabling environment-specific configuration without modifying XML content.

Settings Section Global runtime behavior is controlled here, including cache configuration, camelCase to underscore naming convention mappings, and lazy loading strategies.

Plugin Configuration Third-party plugins such as pagination helpers (e.g., PageHelper) are registered in this section.

Environment Configuration Multiple database environments can be defined, each with its own transaction manager and data source configuration.

Mapper Registration All SQL mapping files are registered in this section using one of three methods: individual resource references, direct interface class referances, or package scanning for automatic discovery.

Mapping File Configuration Details

Input Parameters (parameterType)

• Single primitive parameters for straightforward queries
• Multiple parameters using posisional notation (#{0}, #{1})
• Complex object parameters including wrapper classes containing nested query criteria

Dynamic SQL Elements

• <if> combined with <where> for conditional query building
• <sql> fragments for reusable SQL sections
• <foreach> for iterating over collections in IN clauses

Output Parameters (resultType/resultMap)

• Primitive types: Integer, int, String, Long, long
• Custom object mappings
• HashMap collections for flexible result handling
• resultMap definitions for manual column-to-property mapping when naming conventions differ

Complete Implementation Example

Database Configuration

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/mybatis_demo
jdbc.username=root
jdbc.password=secret

Entity and DTO Classes

package com.example.mybatis.entity;

public class User implements Serializable {
    private static final long serialVersionUID = 1L;
    
    private Long userId;
    private String userName;
    private String loginName;
    private String password;
    private Long deptId;
    private Date birthday;
    private Timestamp lastUpdate;
    
    // getters and setters omitted
}

package com.example.mybatis.dto;

import com.example.mybatis.entity.User;
import java.io.Serializable;
import java.util.List;

public class UserQueryDto implements Serializable {
    private static final long serialVersionUID = 1L;
    
    private User criteria;
    private List<Long> idList;
    
    public User getCriteria() {
        return criteria;
    }
    
    public void setCriteria(User criteria) {
        this.criteria = criteria;
    }
    
    public List<Long> getIdList() {
        return idList;
    }
    
    public void setIdList(List<Long> idList) {
        this.idList = idList;
    }
}

DAO Interface

package com.example.mybatis.mapper;

import com.example.mybatis.dto.UserQueryDto;
import com.example.mybatis.entity.User;
import java.util.List;
import java.util.Map;

public interface UserDao {
    
    User findById(Long id);
    
    User authenticate(String loginName, String password);
    
    List<User> searchByCondition(UserQueryDto queryDto);
    
    Integer countAll();
    
    List<Map<String, Object>> fetchAllAsMap();
    
    List<User> fetchAllWithResultMap();
    
    List<User> listAll();
    
    void insert(User user);
    
    void delete(Long userId);
    
    void modify(User user);
}

MyBatis Configuration File

<?xml version="1.0" encoding="UTF-8"?>
<configuration>
    <properties resource="jdbc.properties"/>
    
    <settings>
        <!-- Enable automatic mapping from USER_ID to userId -->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>
    
    <typeAliases>
        <!-- Scan entire package for type aliases -->
        <package name="com.example.mybatis.entity"/>
    </typeAliases>

    <environments default="production">
        <environment id="production">
            <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>
    
    <mappers>
        <!-- Package scanning method: interfaces and XML files must share the same name and location -->
        <package name="com.example.mybatis.mapper"/>
    </mappers>
</configuration>

Mapper XML File

<?xml version="1.0" encoding="UTF-8"?>
<mapper namespace="com.example.mybatis.mapper.UserDao">
    
    <select id="findById" parameterType="long" resultType="User">
        SELECT USER_ID, USER_NAME, LOGIN_NAME, BIRTHDAY, LAST_UPDATE 
        FROM T_USERS 
        WHERE USER_ID = #{uid}
    </select>
    
    <select id="authenticate" resultType="User">
        SELECT USER_ID, USER_NAME, LOGIN_NAME, BIRTHDAY, LAST_UPDATE 
        FROM T_USERS 
        WHERE LOGIN_NAME = #{0} AND PASSWORD = #{1}
    </select>
    
    <select id="searchByCondition" parameterType="UserQueryDto" resultType="User">
        SELECT USER_ID, USER_NAME, LOGIN_NAME, PASSWORD, DEPT_ID, BIRTHDAY, LAST_UPDATE 
        FROM T_USERS
        <where>
            <if test="criteria != null">
                <if test="criteria.loginName != null">
                    AND LOGIN_NAME LIKE '%${criteria.loginName}%'
                </if>
                <if test="criteria.userName != null">
                    AND USER_NAME LIKE '%${criteria.userName}%'
                </if>
                <if test="criteria.birthday != null">
                    AND BIRTHDAY = #{criteria.birthday}
                </if>
                <if test="criteria.deptId != null">
                    AND DEPT_ID = #{criteria.deptId}
                </if>
            </if>
            <if test="idList != null">
                <foreach collection="idList" open="AND USER_ID IN (" close=")" item="id" separator=",">
                    #{id}
                </foreach>
            </if>
        </where>
    </select>
    
    <select id="countAll" resultType="int">
        SELECT COUNT(*) FROM T_USERS
    </select>
    
    <select id="listAll" resultType="User">
        SELECT USER_ID, USER_NAME, LOGIN_NAME, BIRTHDAY, LAST_UPDATE 
        FROM T_USERS
    </select>
    
    <select id="fetchAllAsMap" resultType="map">
        SELECT USER_ID, USER_NAME, LOGIN_NAME, BIRTHDAY, LAST_UPDATE 
        FROM T_USERS
    </select>
    
    <resultMap id="userMapping" type="User">
        <id column="USER_ID" property="userId"/>
        <result column="USER_NAME" property="userName"/>
        <result column="LOGIN_NAME" property="loginName"/>
        <result column="BIRTHDAY" property="birthday"/>
        <result column="LAST_UPDATE" property="lastUpdate"/>
    </resultMap>
    
    <select id="fetchAllWithResultMap" resultMap="userMapping">
        SELECT USER_ID, USER_NAME, LOGIN_NAME, BIRTHDAY, LAST_UPDATE 
        FROM T_USERS
    </select>
    
    <insert id="insert" parameterType="User">
        <selectKey keyProperty="userId" resultType="long" order="AFTER">
            SELECT LAST_INSERT_ID()
        </selectKey>
        INSERT INTO T_USERS (USER_NAME, LOGIN_NAME, PASSWORD, BIRTHDAY, LAST_UPDATE)
        VALUES (#{userName}, #{loginName}, #{password}, #{birthday}, #{lastUpdate})
    </insert>
    
    <delete id="delete" parameterType="long">
        DELETE FROM T_USERS WHERE USER_ID = #{uid}
    </delete>
    
    <update id="modify" parameterType="User">
        UPDATE T_USERS 
        SET USER_NAME = #{userName}, 
            LOGIN_NAME = #{loginName},
            BIRTHDAY = #{birthday} 
        WHERE USER_ID = #{userId}
    </update>
</mapper>

Unit Tests

package com.example.mybatis.test;

import com.example.mybatis.dto.UserQueryDto;
import com.example.mybatis.entity.User;
import com.example.mybatis.mapper.UserDao;
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.Before;
import org.junit.Test;
import java.io.InputStream;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

public class UserDaoMapperTest {
    
    private SqlSessionFactory factory;
    private SqlSession session;
    
    @Before
    public void setup() throws Exception {
        String config = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(config);
        factory = new SqlSessionFactoryBuilder().build(inputStream);
        session = factory.openSession();
    }
    
    @Test
    public void testFindById() {
        UserDao dao = session.getMapper(UserDao.class);
        User result = dao.findById(1L);
        System.out.println(result);
    }
    
    @Test
    public void testAuthenticate() {
        UserDao dao = session.getMapper(UserDao.class);
        User result = dao.authenticate("admin", "pass123");
        System.out.println(result);
    }
    
    @Test
    public void testSearchWithIds() {
        UserDao dao = session.getMapper(UserDao.class);
        UserQueryDto query = new UserQueryDto();
        
        List<Long> ids = new ArrayList<>();
        ids.add(1L);
        ids.add(2L);
        ids.add(3L);
        query.setIdList(ids);
        
        List<User> results = dao.searchByCondition(query);
        System.out.println(results);
    }
    
    @Test
    public void testCountAll() {
        UserDao dao = session.getMapper(UserDao.class);
        Integer count = dao.countAll();
        System.out.println("Total users: " + count);
    }
    
    @Test
    public void testInsert() {
        UserDao dao = session.getMapper(UserDao.class);
        User newUser = new User();
        newUser.setUserName("testuser");
        newUser.setLoginName("tu");
        newUser.setBirthday(new Timestamp(System.currentTimeMillis()));
        newUser.setLastUpdate(new Timestamp(System.currentTimeMillis()));
        
        dao.insert(newUser);
        System.out.println("Generated ID: " + newUser.getUserId());
        
        session.commit();
        session.close();
    }
}

Dynamic SQL Patterns

SQL Fragments for Reusability

Extract commonly used column lists or WHERE conditions into reusable <sql> fragments:

<sql id="commonColumns">
    USER_ID, USER_NAME, LOGIN_NAME, BIRTHDAY, LAST_UPDATE
</sql>

<sql id="searchConditions">
    <if test="criteria != null">
        <if test="criteria.loginName != null">
            AND LOGIN_NAME LIKE '%${criteria.loginName}%'
        </if>
        <if test="criteria.deptId != null">
            AND DEPT_ID = #{criteria.deptId}
        </if>
    </if>
</sql>

Retrieving Auto-Generated Primary Keys

MySQL uses LAST_INSERT_ID() after the insert operation (order="AFTER"), while Oracle requires sequences fetched before insertion (order="BEFORE"):

<!-- MySQL -->
<insert id="insert" parameterType="User">
    <selectKey keyProperty="userId" resultType="long" order="AFTER">
        SELECT LAST_INSERT_ID()
    </selectKey>
    INSERT INTO T_USERS (USER_NAME, LOGIN_NAME, BIRTHDAY)
    VALUES (#{userName}, #{loginName}, #{birthday})
</insert>

<!-- Oracle -->
<insert id="insert" parameterType="User">
    <selectKey keyProperty="userId" resultType="long" order="BEFORE">
        SELECT SEQ_USERS.NEXTVAL FROM DUAL
    </selectKey>
    INSERT INTO T_USERS (USER_ID, USER_NAME, LOGIN_NAME, BIRTHDAY)
    VALUES (#{userId}, #{userName}, #{loginName}, #{birthday})
</insert>

Performance Considerations

The ${} concatenation operator enables dynamic SQL construction but poses SQL injection risks and may impact query performance. Prefer the #{...} parameter binding approach whenever possible.