MACRO NEPAL
Simplified Data Access: MyBatis SQL Mapping in Java
awiskar acharya
November 8, 2025
2:36 pm

Introduction

MyBatis is a powerful persistence framework that simplifies database access in Java applications. Unlike ORM frameworks that try to map objects to database tables automatically, MyBatis takes a different approach by mapping Java objects to SQL statements and stored procedures, giving developers full control over SQL while reducing boilerplate code.

Key Features

Advantages of MyBatis

  • Full SQL control - write and optimize SQL directly
  • Reduced boilerplate - eliminates repetitive JDBC code
  • Flexible mapping - complex object relationships easily handled
  • Stored procedure support - first-class support for database procedures
  • Dynamic SQL - build queries programmatically
  • Integration friendly - works well with Spring and other frameworks

Setup and Dependencies

Maven Dependencies

<properties>
<mybatis.version>3.5.13</mybatis.version>
<mybatis-spring.version>2.1.1</mybatis-spring.version>
</properties>
<dependencies>
<!-- MyBatis Core -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>${mybatis.version}</version>
</dependency>
<!-- MyBatis Spring Integration -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-spring</artifactId>
<version>${mybatis-spring.version}</version>
</dependency>
<!-- Spring JDBC (if using Spring) -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>5.3.23</version>
</dependency>
<!-- Database Driver -->
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>42.6.0</version>
</dependency>
<!-- Connection Pool -->
<dependency>
<groupId>com.zaxxer</groupId>
<artifactId>HikariCP</artifactId>
<version>5.0.1</version>
</dependency>
</dependencies>

Spring Boot Starter

<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.3.1</version>
</dependency>

Configuration

1. MyBatis Configuration Class

package com.example.config;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;
@Configuration
@MapperScan("com.example.mapper")
public class MyBatisConfig {
@Bean
public SqlSessionFactory sqlSessionFactory(DataSource dataSource) throws Exception {
SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
sessionFactory.setDataSource(dataSource);
// MyBatis configuration
org.apache.ibatis.session.Configuration configuration = 
new org.apache.ibatis.session.Configuration();
configuration.setMapUnderscoreToCamelCase(true);
configuration.setUseGeneratedKeys(true);
configuration.setDefaultFetchSize(100);
configuration.setDefaultStatementTimeout(30);
sessionFactory.setConfiguration(configuration);
// XML mapper locations
sessionFactory.setMapperLocations(
new PathMatchingResourcePatternResolver()
.getResources("classpath:mappers/*.xml")
);
// Type aliases package
sessionFactory.setTypeAliasesPackage("com.example.model");
return sessionFactory.getObject();
}
@Bean
public DataSourceTransactionManager transactionManager(DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
}

2. Application Properties

# application.yml
spring:
datasource:
url: jdbc:postgresql://localhost:5432/myapp
username: ${DB_USERNAME:app_user}
password: ${DB_PASSWORD:app_pass}
driver-class-name: org.postgresql.Driver
hikari:
maximum-pool-size: 20
minimum-idle: 5
connection-timeout: 30000
idle-timeout: 600000
max-lifetime: 1800000
mybatis:
mapper-locations: classpath:mappers/*.xml
type-aliases-package: com.example.model
configuration:
map-underscore-to-camel-case: true
use-generated-keys: true
default-fetch-size: 100
default-statement-timeout: 30

3. MyBatis Configuration XML

<!-- 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>
<settings>
<setting name="cacheEnabled" value="true"/>
<setting name="lazyLoadingEnabled" value="true"/>
<setting name="multipleResultSetsEnabled" value="true"/>
<setting name="useColumnLabel" value="true"/>
<setting name="useGeneratedKeys" value="true"/>
<setting name="autoMappingBehavior" value="PARTIAL"/>
<setting name="autoMappingUnknownColumnBehavior" value="WARNING"/>
<setting name="defaultExecutorType" value="SIMPLE"/>
<setting name="defaultStatementTimeout" value="25"/>
<setting name="defaultFetchSize" value="100"/>
<setting name="safeRowBoundsEnabled" value="false"/>
<setting name="mapUnderscoreToCamelCase" value="true"/>
<setting name="localCacheScope" value="SESSION"/>
<setting name="jdbcTypeForNull" value="OTHER"/>
<setting name="lazyLoadTriggerMethods" value="equals,clone,hashCode,toString"/>
</settings>
<typeAliases>
<package name="com.example.model"/>
</typeAliases>
<typeHandlers>
<typeHandler handler="org.apache.ibatis.type.EnumTypeHandler" 
javaType="com.example.model.UserStatus"/>
</typeHandlers>
<plugins>
<plugin interceptor="com.github.pagehelper.PageInterceptor">
<property name="helperDialect" value="postgresql"/>
<property name="reasonable" value="true"/>
<property name="supportMethodsArguments" value="true"/>
<property name="params" value="count=countSql"/>
</plugin>
</plugins>
</configuration>

Domain Models

1. Base Entity Classes

package com.example.model;
import java.time.LocalDateTime;
public abstract class BaseEntity {
private Long id;
private LocalDateTime createdAt;
private LocalDateTime updatedAt;
private String createdBy;
private String updatedBy;
// Getters and setters
public Long getId() { return id; }
public void setId(Long id) { this.id = id; }
public LocalDateTime getCreatedAt() { return createdAt; }
public void setCreatedAt(LocalDateTime createdAt) { this.createdAt = createdAt; }
public LocalDateTime getUpdatedAt() { return updatedAt; }
public void setUpdatedAt(LocalDateTime updatedAt) { this.updatedAt = updatedAt; }
public String getCreatedBy() { return createdBy; }
public void setCreatedBy(String createdBy) { this.createdBy = createdBy; }
public String getUpdatedBy() { return updatedBy; }
public void setUpdatedBy(String updatedBy) { this.updatedBy = updatedBy; }
}

2. User Entity

package com.example.model;
import java.time.LocalDate;
import java.util.List;
public class User extends BaseEntity {
private String username;
private String email;
private String passwordHash;
private String firstName;
private String lastName;
private LocalDate dateOfBirth;
private String phoneNumber;
private UserStatus status;
private List<Role> roles;
private Address address;
// Enums
public enum UserStatus {
ACTIVE, INACTIVE, SUSPENDED, PENDING
}
// Constructors
public User() {}
public User(String username, String email, String firstName, String lastName) {
this.username = username;
this.email = email;
this.firstName = firstName;
this.lastName = lastName;
this.status = UserStatus.ACTIVE;
}
// Getters and setters
public String getUsername() { return username; }
public void setUsername(String username) { this.username = username; }
public String getEmail() { return email; }
public void setEmail(String email) { this.email = email; }
public String getPasswordHash() { return passwordHash; }
public void setPasswordHash(String passwordHash) { this.passwordHash = passwordHash; }
public String getFirstName() { return firstName; }
public void setFirstName(String firstName) { this.firstName = firstName; }
public String getLastName() { return lastName; }
public void setLastName(String lastName) { this.lastName = lastName; }
public LocalDate getDateOfBirth() { return dateOfBirth; }
public void setDateOfBirth(LocalDate dateOfBirth) { this.dateOfBirth = dateOfBirth; }
public String getPhoneNumber() { return phoneNumber; }
public void setPhoneNumber(String phoneNumber) { this.phoneNumber = phoneNumber; }
public UserStatus getStatus() { return status; }
public void setStatus(UserStatus status) { this.status = status; }
public List<Role> getRoles() { return roles; }
public void setRoles(List<Role> roles) { this.roles = roles; }
public Address getAddress() { return address; }
public void setAddress(Address address) { this.address = address; }
// Utility methods
public String getFullName() {
return firstName + " " + lastName;
}
}

3. Related Entities

package com.example.model;
public class Role extends BaseEntity {
private String name;
private String description;
private List<Permission> permissions;
// Constructors, getters, and setters
public Role() {}
public Role(String name, String description) {
this.name = name;
this.description = description;
}
public String getName() { return name; }
public void setName(String name) { this.name = name; }
public String getDescription() { return description; }
public void setDescription(String description) { this.description = description; }
public List<Permission> getPermissions() { return permissions; }
public void setPermissions(List<Permission> permissions) { this.permissions = permissions; }
}
package com.example.model;
public class Address extends BaseEntity {
private String street;
private String city;
private String state;
private String zipCode;
private String country;
private AddressType type;
public enum AddressType {
HOME, WORK, BILLING, SHIPPING
}
// Constructors, getters, and setters
public Address() {}
public Address(String street, String city, String state, String zipCode, String country) {
this.street = street;
this.city = city;
this.state = state;
this.zipCode = zipCode;
this.country = country;
}
public String getStreet() { return street; }
public void setStreet(String street) { this.street = street; }
public String getCity() { return city; }
public void setCity(String city) { this.city = city; }
public String getState() { return state; }
public void setState(String state) { this.state = state; }
public String getZipCode() { return zipCode; }
public void setZipCode(String zipCode) { this.zipCode = zipCode; }
public String getCountry() { return country; }
public void setCountry(String country) { this.country = country; }
public AddressType getType() { return type; }
public void setType(AddressType type) { this.type = type; }
}

Mapper Interfaces

1. User Mapper Interface

package com.example.mapper;
import com.example.model.User;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;
import java.util.List;
import java.util.Optional;
@Repository
@Mapper
public interface UserMapper {
// Basic CRUD operations
@Select("SELECT * FROM users WHERE id = #{id}")
Optional<User> findById(Long id);
@Select("SELECT * FROM users WHERE username = #{username}")
Optional<User> findByUsername(String username);
@Select("SELECT * FROM users WHERE email = #{email}")
Optional<User> findByEmail(String email);
@Insert("INSERT INTO users (username, email, password_hash, first_name, last_name, " +
"date_of_birth, phone_number, status, created_at, created_by) " +
"VALUES (#{username}, #{email}, #{passwordHash}, #{firstName}, #{lastName}, " +
"#{dateOfBirth}, #{phoneNumber}, #{status}, CURRENT_TIMESTAMP, #{createdBy})")
@Options(useGeneratedKeys = true, keyProperty = "id")
void insert(User user);
@Update("UPDATE users SET " +
"username = #{username}, " +
"email = #{email}, " +
"first_name = #{firstName}, " +
"last_name = #{lastName}, " +
"date_of_birth = #{dateOfBirth}, " +
"phone_number = #{phoneNumber}, " +
"status = #{status}, " +
"updated_at = CURRENT_TIMESTAMP, " +
"updated_by = #{updatedBy} " +
"WHERE id = #{id}")
void update(User user);
@Update("UPDATE users SET status = #{status} WHERE id = #{id}")
void updateStatus(@Param("id") Long id, @Param("status") User.UserStatus status);
@Delete("DELETE FROM users WHERE id = #{id}")
void delete(Long id);
// Complex queries with result mapping
@Select("SELECT COUNT(*) FROM users WHERE email = #{email} AND id != #{excludeId}")
boolean existsByEmailExcludingId(@Param("email") String email, 
@Param("excludeId") Long excludeId);
// Custom methods for XML mappings
List<User> findAll();
List<User> findByCriteria(User criteria);
List<User> findUsersWithRoles();
List<User> findActiveUsers();
}

XML Mapper Files

1. User Mapper XML

<!-- src/main/resources/mappers/UserMapper.xml -->
<?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.example.mapper.UserMapper">
<!-- Result Maps -->
<resultMap id="UserResultMap" type="User" autoMapping="true">
<id property="id" column="id"/>
<result property="passwordHash" column="password_hash"/>
<result property="firstName" column="first_name"/>
<result property="lastName" column="last_name"/>
<result property="dateOfBirth" column="date_of_birth"/>
<result property="phoneNumber" column="phone_number"/>
<result property="createdAt" column="created_at"/>
<result property="updatedAt" column="updated_at"/>
<result property="createdBy" column="created_by"/>
<result property="updatedBy" column="updated_by"/>
<!-- Simple enum mapping -->
<result property="status" column="status" 
typeHandler="org.apache.ibatis.type.EnumTypeHandler"/>
</resultMap>
<resultMap id="UserWithRolesResultMap" type="User" extends="UserResultMap">
<collection property="roles" ofType="Role" 
resultMap="com.example.mapper.RoleMapper.RoleResultMap"
columnPrefix="role_"/>
</resultMap>
<!-- SQL fragments for reusability -->
<sql id="User_Columns">
id, username, email, password_hash, first_name, last_name,
date_of_birth, phone_number, status, created_at, updated_at,
created_by, updated_by
</sql>
<sql id="User_Table">users</sql>
<!-- XML Mapped Methods -->
<select id="findAll" resultMap="UserResultMap">
SELECT 
<include refid="User_Columns"/>
FROM <include refid="User_Table"/>
ORDER BY created_at DESC
</select>
<select id="findActiveUsers" resultMap="UserResultMap">
SELECT 
<include refid="User_Columns"/>
FROM <include refid="User_Table"/>
WHERE status = 'ACTIVE'
ORDER BY first_name, last_name
</select>
<select id="findUsersWithRoles" resultMap="UserWithRolesResultMap">
SELECT 
u.*,
r.id as role_id,
r.name as role_name,
r.description as role_description
FROM users u
LEFT JOIN user_roles ur ON u.id = ur.user_id
LEFT JOIN roles r ON ur.role_id = r.id
ORDER BY u.created_at DESC
</select>
<!-- Dynamic SQL with conditions -->
<select id="findByCriteria" parameterType="User" resultMap="UserResultMap">
SELECT 
<include refid="User_Columns"/>
FROM <include refid="User_Table"/>
<where>
<if test="username != null and username != ''">
AND username LIKE CONCAT('%', #{username}, '%')
</if>
<if test="email != null and email != ''">
AND email LIKE CONCAT('%', #{email}, '%')
</if>
<if test="firstName != null and firstName != ''">
AND first_name LIKE CONCAT('%', #{firstName}, '%')
</if>
<if test="lastName != null and lastName != ''">
AND last_name LIKE CONCAT('%', #{lastName}, '%')
</if>
<if test="status != null">
AND status = #{status, typeHandler=org.apache.ibatis.type.EnumTypeHandler}
</if>
<if test="phoneNumber != null and phoneNumber != ''">
AND phone_number LIKE CONCAT('%', #{phoneNumber}, '%')
</if>
</where>
ORDER BY 
<choose>
<when test="sortBy != null and sortBy == 'email'">
email
</when>
<when test="sortBy != null and sortBy == 'name'">
first_name, last_name
</when>
<otherwise>
created_at DESC
</otherwise>
</choose>
</select>
<!-- Batch operations -->
<insert id="insertBatch" parameterType="java.util.List">
INSERT INTO users (
username, email, password_hash, first_name, last_name,
date_of_birth, phone_number, status, created_at, created_by
) VALUES
<foreach collection="list" item="user" separator=",">
(
#{user.username}, #{user.email}, #{user.passwordHash}, 
#{user.firstName}, #{user.lastName}, #{user.dateOfBirth},
#{user.phoneNumber}, #{user.status}, CURRENT_TIMESTAMP, #{user.createdBy}
)
</foreach>
</insert>
<update id="updateBatch" parameterType="java.util.List">
<foreach collection="list" item="user" separator=";">
UPDATE users SET
username = #{user.username},
email = #{user.email},
first_name = #{user.firstName},
last_name = #{user.lastName},
date_of_birth = #{user.dateOfBirth},
phone_number = #{user.phoneNumber},
status = #{user.status},
updated_at = CURRENT_TIMESTAMP,
updated_by = #{user.updatedBy}
WHERE id = #{user.id}
</foreach>
</update>
<!-- Complex join with pagination -->
<select id="findUsersWithPagination" resultMap="UserWithRolesResultMap">
SELECT 
u.*,
r.id as role_id,
r.name as role_name,
r.description as role_description
FROM users u
LEFT JOIN user_roles ur ON u.id = ur.user_id
LEFT JOIN roles r ON ur.role_id = r.id
WHERE u.status = 'ACTIVE'
ORDER BY u.created_at DESC
LIMIT #{pageSize} OFFSET #{offset}
</select>
</mapper>

2. Role Mapper XML

<!-- src/main/resources/mappers/RoleMapper.xml -->
<?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.example.mapper.RoleMapper">
<resultMap id="RoleResultMap" type="Role" autoMapping="true">
<id property="id" column="id"/>
</resultMap>
<resultMap id="RoleWithPermissionsResultMap" type="Role" extends="RoleResultMap">
<collection property="permissions" ofType="Permission" 
resultMap="com.example.mapper.PermissionMapper.PermissionResultMap"
columnPrefix="perm_"/>
</resultMap>
<select id="findRolesByUserId" parameterType="long" resultMap="RoleResultMap">
SELECT r.*
FROM roles r
INNER JOIN user_roles ur ON r.id = ur.role_id
WHERE ur.user_id = #{userId}
</select>
<insert id="assignRoleToUser">
INSERT INTO user_roles (user_id, role_id, assigned_at, assigned_by)
VALUES (#{userId}, #{roleId}, CURRENT_TIMESTAMP, #{assignedBy})
</insert>
<delete id="removeRoleFromUser">
DELETE FROM user_roles 
WHERE user_id = #{userId} AND role_id = #{roleId}
</delete>
</mapper>

Dynamic SQL

1. Advanced Dynamic SQL Mapper

<!-- src/main/resources/mappers/UserDynamicMapper.xml -->
<?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.example.mapper.UserDynamicMapper">
<!-- Complex dynamic query with choose/when/otherwise -->
<select id="findUsersAdvanced" parameterType="map" resultMap="UserResultMap">
SELECT 
<include refid="com.example.mapper.UserMapper.User_Columns"/>
FROM users
<where>
<choose>
<when test="searchType == 'username'">
AND username LIKE CONCAT('%', #{searchTerm}, '%')
</when>
<when test="searchType == 'email'">
AND email LIKE CONCAT('%', #{searchTerm}, '%')
</when>
<when test="searchType == 'name'">
AND (first_name LIKE CONCAT('%', #{searchTerm}, '%') 
OR last_name LIKE CONCAT('%', #{searchTerm}, '%'))
</when>
<otherwise>
AND (username LIKE CONCAT('%', #{searchTerm}, '%')
OR email LIKE CONCAT('%', #{searchTerm}, '%')
OR first_name LIKE CONCAT('%', #{searchTerm}, '%')
OR last_name LIKE CONCAT('%', #{searchTerm}, '%'))
</otherwise>
</choose>
<if test="statusList != null and statusList.size() > 0">
AND status IN
<foreach item="status" collection="statusList" 
open="(" separator="," close=")">
#{status}
</foreach>
</if>
<if test="createdAfter != null">
AND created_at >= #{createdAfter}
</if>
<if test="createdBefore != null">
AND created_at &lt;= #{createdBefore}
</if>
</where>
<!-- Dynamic ordering -->
<trim prefix="ORDER BY">
<choose>
<when test="sortBy != null and sortBy == 'username'">
username
<if test="sortOrder != null and sortOrder == 'desc'">DESC</if>
</when>
<when test="sortBy != null and sortBy == 'email'">
email
<if test="sortOrder != null and sortOrder == 'desc'">DESC</if>
</when>
<when test="sortBy != null and sortBy == 'name'">
first_name, last_name
<if test="sortOrder != null and sortOrder == 'desc'">DESC</if>
</when>
<when test="sortBy != null and sortBy == 'created'">
created_at
<if test="sortOrder != null and sortOrder == 'desc'">DESC</if>
</when>
<otherwise>
created_at DESC
</otherwise>
</choose>
</trim>
<!-- Pagination -->
<if test="limit != null">
LIMIT #{limit}
<if test="offset != null">
OFFSET #{offset}
</if>
</if>
</select>
<!-- Dynamic update - only update provided fields -->
<update id="updateSelective" parameterType="User">
UPDATE users
<set>
<if test="username != null">username = #{username},</if>
<if test="email != null">email = #{email},</if>
<if test="passwordHash != null">password_hash = #{passwordHash},</if>
<if test="firstName != null">first_name = #{firstName},</if>
<if test="lastName != null">last_name = #{lastName},</if>
<if test="dateOfBirth != null">date_of_birth = #{dateOfBirth},</if>
<if test="phoneNumber != null">phone_number = #{phoneNumber},</if>
<if test="status != null">status = #{status},</if>
updated_at = CURRENT_TIMESTAMP,
<if test="updatedBy != null">updated_by = #{updatedBy}</if>
</set>
WHERE id = #{id}
</update>
</mapper>

Service Layer Implementation

1. User Service

package com.example.service;
import com.example.mapper.UserMapper;
import com.example.model.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.util.List;
import java.util.Optional;
@Service
@Transactional
public class UserService {
private final UserMapper userMapper;
@Autowired
public UserService(UserMapper userMapper) {
this.userMapper = userMapper;
}
public Optional<User> findById(Long id) {
return userMapper.findById(id);
}
public Optional<User> findByUsername(String username) {
return userMapper.findByUsername(username);
}
public Optional<User> findByEmail(String email) {
return userMapper.findByEmail(email);
}
public List<User> findAll() {
return userMapper.findAll();
}
public List<User> findActiveUsers() {
return userMapper.findActiveUsers();
}
public List<User> findByCriteria(User criteria) {
return userMapper.findByCriteria(criteria);
}
public User create(User user) {
userMapper.insert(user);
return user;
}
public User update(User user) {
userMapper.update(user);
return user;
}
public void updateStatus(Long userId, User.UserStatus status) {
userMapper.updateStatus(userId, status);
}
public void delete(Long userId) {
userMapper.delete(userId);
}
public boolean isEmailUnique(String email, Long excludeId) {
if (excludeId == null) {
return userMapper.findByEmail(email).isEmpty();
}
return !userMapper.existsByEmailExcludingId(email, excludeId);
}
@Transactional(readOnly = true)
public List<User> findUsersWithRoles() {
return userMapper.findUsersWithRoles();
}
}

2. Advanced User Service with Pagination

package com.example.service;
import com.github.pagehelper.Page;
import com.github.pagehelper.PageHelper;
import com.example.mapper.UserDynamicMapper;
import com.example.model.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.time.LocalDateTime;
import java.util.List;
import java.util.Map;
@Service
public class UserQueryService {
private final UserDynamicMapper userDynamicMapper;
@Autowired
public UserQueryService(UserDynamicMapper userDynamicMapper) {
this.userDynamicMapper = userDynamicMapper;
}
public Page<User> searchUsers(Map<String, Object> criteria, int pageNum, int pageSize) {
return PageHelper.startPage(pageNum, pageSize)
.doSelectPage(() -> userDynamicMapper.findUsersAdvanced(criteria));
}
public List<User> findUsersCreatedBetween(LocalDateTime start, LocalDateTime end) {
return userDynamicMapper.findUsersAdvanced(Map.of(
"createdAfter", start,
"createdBefore", end
));
}
public void updateUserSelective(User user) {
userDynamicMapper.updateSelective(user);
}
}

Custom Type Handlers

1. JSON Type Handler

package com.example.handler;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.MappedTypes;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Map;
@MappedTypes({Map.class, Object.class})
public class JsonTypeHandler extends BaseTypeHandler<Object> {
private static final ObjectMapper objectMapper = new ObjectMapper();
@Override
public void setNonNullParameter(PreparedStatement ps, int i, 
Object parameter, JdbcType jdbcType) throws SQLException {
try {
String json = objectMapper.writeValueAsString(parameter);
ps.setString(i, json);
} catch (JsonProcessingException e) {
throw new SQLException("Error converting parameter to JSON", e);
}
}
@Override
public Object getNullableResult(ResultSet rs, String columnName) throws SQLException {
String json = rs.getString(columnName);
return parseJson(json);
}
@Override
public Object getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
String json = rs.getString(columnIndex);
return parseJson(json);
}
@Override
public Object getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
String json = cs.getString(columnIndex);
return parseJson(json);
}
private Object parseJson(String json) {
if (json == null || json.trim().isEmpty()) {
return null;
}
try {
return objectMapper.readValue(json, Object.class);
} catch (JsonProcessingException e) {
throw new RuntimeException("Error parsing JSON from database", e);
}
}
}

2. Custom Enum Type Handler

package com.example.handler;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.MappedTypes;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
@MappedTypes(User.UserStatus.class)
public class UserStatusTypeHandler extends BaseTypeHandler<User.UserStatus> {
@Override
public void setNonNullParameter(PreparedStatement ps, int i, 
User.UserStatus parameter, JdbcType jdbcType) throws SQLException {
ps.setString(i, parameter.name());
}
@Override
public User.UserStatus getNullableResult(ResultSet rs, String columnName) throws SQLException {
String value = rs.getString(columnName);
return value == null ? null : User.UserStatus.valueOf(value);
}
@Override
public User.UserStatus getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
String value = rs.getString(columnIndex);
return value == null ? null : User.UserStatus.valueOf(value);
}
@Override
public User.UserStatus getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
String value = cs.getString(columnIndex);
return value == null ? null : User.UserStatus.valueOf(value);
}
}

Testing

1. Integration Test

package com.example.integration;
import com.example.mapper.UserMapper;
import com.example.model.User;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.ActiveProfiles;
import org.springframework.transaction.annotation.Transactional;
import java.time.LocalDate;
import java.util.List;
import java.util.Optional;
import static org.junit.jupiter.api.Assertions.*;
@SpringBootTest
@ActiveProfiles("test")
@Transactional
class UserMapperIntegrationTest {
@Autowired
private UserMapper userMapper;
@Test
void testInsertAndFindUser() {
// Given
User user = new User("testuser", "[email protected]", "John", "Doe");
user.setPasswordHash("hashedpassword");
// When
userMapper.insert(user);
Optional<User> found = userMapper.findById(user.getId());
// Then
assertTrue(found.isPresent());
assertEquals("testuser", found.get().getUsername());
assertEquals("[email protected]", found.get().getEmail());
}
@Test
void testUpdateUser() {
// Given
User user = new User("original", "[email protected]", "Original", "Name");
user.setPasswordHash("hash");
userMapper.insert(user);
// When
user.setEmail("[email protected]");
user.setFirstName("Updated");
userMapper.update(user);
// Then
Optional<User> updated = userMapper.findByUsername("original");
assertTrue(updated.isPresent());
assertEquals("[email protected]", updated.get().getEmail());
assertEquals("Updated", updated.get().getFirstName());
}
@Test
void testFindByCriteria() {
// Given
User user1 = new User("user1", "[email protected]", "Alice", "Smith");
user1.setPasswordHash("hash1");
userMapper.insert(user1);
User user2 = new User("user2", "[email protected]", "Bob", "Johnson");
user2.setPasswordHash("hash2");
userMapper.insert(user2);
// When
User criteria = new User();
criteria.setFirstName("Alice");
List<User> results = userMapper.findByCriteria(criteria);
// Then
assertEquals(1, results.size());
assertEquals("user1", results.get(0).getUsername());
}
}

2. Test Configuration

# application-test.yml
spring:
datasource:
url: jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE
username: sa
password: 
driver-class-name: org.h2.Driver
h2:
console:
enabled: true
sql:
init:
data-locations: classpath:test-data.sql
mybatis:
mapper-locations: classpath:mappers/*.xml
type-aliases-package: com.example.model
configuration:
map-underscore-to-camel-case: true

Best Practices

1. Performance Optimization

<!-- Use lazy loading for complex relationships -->
<settings>
<setting name="lazyLoadingEnabled" value="true"/>
<setting name="aggressiveLazyLoading" value="false"/>
</settings>
<!-- Use batch operations for bulk inserts -->
<insert id="insertBatch" useGeneratedKeys="true" keyProperty="id">
INSERT INTO users (...) VALUES
<foreach collection="list" item="item" separator=",">
(...)
</foreach>
</insert>

2. Security Considerations

// Use parameterized queries to prevent SQL injection
@Select("SELECT * FROM users WHERE username = #{username} AND password_hash = #{passwordHash}")
Optional<User> authenticate(@Param("username") String username, 
@Param("passwordHash") String passwordHash);
// Never do this (vulnerable to SQL injection):
// @Select("SELECT * FROM users WHERE username = '${username}'")

Conclusion

MyBatis provides a powerful and flexible approach to database access in Java applications. Key advantages include:

  • Full SQL control - write optimized, database-specific queries
  • Reduced boilerplate - eliminates repetitive JDBC code
  • Flexible mapping - handle complex object relationships easily
  • Dynamic SQL - build queries programmatically based on conditions
  • Integration friendly - works seamlessly with Spring and other frameworks

Best practices for MyBatis implementation:

  1. Use meaningful mapper and method names for better code readability
  2. Leverage XML for complex queries and annotations for simple ones
  3. Use result maps for complex object relationships
  4. Implement proper transaction management with Spring @Transactional
  5. Write comprehensive tests for all mapper methods
  6. Use pagination for large datasets to improve performance
  7. Implement proper error handling and logging

MyBatis strikes an excellent balance between the control of raw JDBC and the convenience of ORM frameworks, making it an ideal choice for applications where SQL optimization and control are important.

Category : JAVA

Leave a Reply

Your email address will not be published. Required fields are marked *


Macro Nepal Helper