10 Essential MyBatis Development Tips for Efficient Backend Programming

Introduction

Many developers use MyBatis but rarely exploit its hidden "treasure" features. Mastering these techniques can boost development efficiency and avoid common pitfalls. Below are ten eye‑opening MyBatis tips, each accompanied by concrete scenarios and code examples.

1. Flexible Use of Dynamic SQL

Instead of concatenating strings, which is cumbersome and vulnerable to SQL injection, MyBatis provides if , choose , and foreach tags to build SQL dynamically.

Example: Dynamic Conditional Query

String sql = "SELECT * FROM user WHERE 1=1";
if (name != null) {
    sql += " AND name = '" + name + "'";
}

The above Java string concatenation is unsafe. The MyBatis XML equivalent uses dynamic tags:

<select id="findUser" resultType="User">
    SELECT * FROM user
    WHERE 1=1
    <if test="name != null and name != ''">
        AND name = #{name}
    </if>
    <if test="age != null">
        AND age = #{age}
    </if>
</select>

2. Leveraging resultMap for Custom Result Mapping

When database columns use snake_case and Java fields use camelCase, the default resultType cannot map automatically. Defining a resultMap solves this.

Example: Custom Result Mapping

<resultMap id="userResultMap" type="User">
    <id column="id" property="id"/>
    <result column="user_name" property="userName"/>
    <result column="age" property="age"/>
</resultMap>

<select id="getUserById" resultMap="userResultMap">
    SELECT id, user_name, age FROM user WHERE id = #{id}
</select>

3. Using foreach for Batch Operations

To query a list of IDs without manual string concatenation, the foreach tag expands a collection into an IN clause automatically.

Example: Batch Query

<select id="findUsersByIds" resultType="User">
    SELECT * FROM user WHERE id IN
    <foreach item="id" collection="idList" open="(" separator="," close=")">
        #{id}
    </foreach>
</select>

4. MyBatis‑Plus Pagination Feature

Instead of writing LIMIT manually, MyBatis‑Plus provides a pagination plugin.

Example: Pagination

Page
page = new Page<>(1, 10); // page 1, 10 records per page
IPage
userPage = userMapper.selectPage(page, null);
System.out.println("Total records: " + userPage.getTotal());
System.out.println("Current page data: " + userPage.getRecords());

5. Using @Mapper Interface Proxies

For simple queries, pure annotation mode avoids XML configuration.

Example: Annotation‑Based Mapper

@Mapper
public interface UserMapper {
    @Select("SELECT * FROM user WHERE id = #{id}")
    User getUserById(int id);

    @Insert("INSERT INTO user(name, age) VALUES(#{name}, #{age})")
    void addUser(User user);
}

6. Second‑Level Cache

Enabling the second‑level cache reduces repeated queries across SqlSessions.

Example: Enable Cache

<configuration>
    <settings>
        <setting name="cacheEnabled" value="true"/>
    </settings>
</configuration>

<mapper namespace="com.example.mapper.UserMapper">
    <cache/>
    <select id="getUserById" resultType="User">
        SELECT * FROM user WHERE id = #{id}
    </select>
</mapper>

7. Dynamic Table Name Switching

For multi‑tenant scenarios, MyBatis can replace table names at runtime.

Example: Dynamic Table Name

<select id="getDataFromDynamicTable" resultType="Map">
    SELECT * FROM ${tableName} WHERE id = #{id}
</select>

8. Custom typeHandler for Type Conversion

When the database stores integers (1/0) but the application uses booleans, a custom typeHandler bridges the gap.

Example: Boolean TypeHandler

@MappedTypes(Boolean.class)
@MappedJdbcTypes(JdbcType.INTEGER)
public class BooleanTypeHandler extends BaseTypeHandler
{
    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, Boolean parameter, JdbcType jdbcType) throws SQLException {
        ps.setInt(i, parameter ? 1 : 0);
    }
    @Override
    public Boolean getNullableResult(ResultSet rs, String columnName) throws SQLException {
        return rs.getInt(columnName) == 1;
    }
}

9. Logging for Quick Debugging

Configuring MyBatis logging prints full SQL statements and parameters, simplifying troubleshooting.

Example: Enable STDOUT Logging

<configuration>
    <settings>
        <setting name="logImpl" value="STDOUT_LOGGING"/>
    </settings>
</configuration>

Output example:

==>  Preparing: SELECT * FROM user WHERE id = ?
==>  Parameters: 1(Integer)
<==      Total: 1

10. Multi‑DataSource Support

When an application needs to connect to multiple databases, MyBatis can be configured with separate SqlSession factories.

Example: Configure Two DataSources

@Configuration
@MapperScan(basePackages = "com.example.mapper", sqlSessionTemplateRef = "sqlSessionTemplate1")
public class DataSourceConfig1 {
    @Bean(name = "dataSource1")
    public DataSource dataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "sqlSessionFactory1")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("dataSource1") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        return bean.getObject();
    }

    @Bean(name = "sqlSessionTemplate1")
    public SqlSessionTemplate sqlSessionTemplate(@Qualifier("sqlSessionFactory1") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

Conclusion

MyBatis is simple and efficient, yet many developers only use its basic features. These ten tips help you unlock its full potential, making your code more elegant and performant.