CODE WITH SIBIN

Solving Real Problems with Real Code


Mastering MyBatis with Spring Boot: Setup, CRUD, Caching, Advanced Querying & Testing Guide
admin
March 24, 2025
CRUD java MyBatis Spring Boot

Table of Contents

  1. Introduction
  2. Setting Up the Spring Boot MyBatis Project
  3. Configuring MyBatis in Spring Boot
  4. Creating Database Entities and Mappers in Spring Boot MyBatis
  5. CRUD Operations in MyBatis
  6. Advanced Querying in MyBatis
  7. Using MyBatis with Spring Boot Services & REST Controllers
  8. Testing MyBatis with Spring Boot
  9. Caching in MyBatis
  10. Conclusion

1. Introduction to MyBatis with Spring Boot

What is MyBatis?

MyBatis is a persistence framework that simplifies the interaction between Java applications and relational databases. It acts as a middleware between Java objects and SQL databases, allowing developers to execute SQL queries with minimal boilerplate code.

Key Features of MyBatis:

✔️ SQL Mapping: Maps Java objects to SQL queries instead of using an ORM (Object-Relational Mapping).
✔️ XML and Annotation-based Configuration: Queries can be written in XML files or as Java annotations.
✔️ Dynamic SQL: Supports dynamic SQL queries for flexibility.
✔️ Result Mapping: Maps query results to Java objects efficiently.
✔️ Lightweight and Flexible: Unlike Hibernate, MyBatis doesn’t force an entity-to-table mapping.

Why Use MyBatis with Spring Boot?

Spring Boot provides seamless integration with MyBatis through the mybatis-spring-boot-starter, making it easy to use MyBatis in a Spring Boot application.

Advantages of Using MyBatis with Spring Boot:

🔹 Simplicity & Control: Developers have full control over SQL queries, making performance tuning easier.
🔹 Better for Complex Queries: MyBatis excels in handling complex SQL queries and stored procedures.
🔹 Lightweight & Flexible: Since it’s not a full ORM, it offers more flexibility in mapping objects.
🔹 Easy to Integrate: Works well with Spring Boot’s dependency injection and transaction management.
🔹 No Need for an ORM Model: Unlike JPA/Hibernate, MyBatis doesn’t require entity classes to be mapped to database tables.

Comparison: MyBatis vs JPA (Hibernate)

FeatureMyBatisJPA (Hibernate)
TypeSQL MapperObject-Relational Mapper (ORM)
Query ApproachCustom SQL queriesHQL/JPQL and Criteria API
Control Over SQLFull control (manual queries)Less control (generated queries)
PerformanceBetter for complex queriesCan be slow due to query generation
Entity MappingNot requiredRequired (annotations like @Entity)
Learning CurveEasier for SQL usersSteeper due to ORM concepts
Cache SupportFirst & second-level cacheFirst & second-level cache
Best ForHigh-performance SQL-heavy appsApps requiring automatic entity management

When to Use MyBatis?

✔️ If your application requires complex SQL queries and stored procedures.
✔️ If you need full control over database queries for optimization.
✔️ If performance tuning is critical and ORM overhead is not acceptable.

When to Use JPA/Hibernate?

✔️ If you want automatic entity-to-table mapping and less manual query writing.
✔️ If you prefer working with objects instead of SQL.
✔️ If your application requires cross-database portability.

2. Setting Up the Spring Boot MyBatis Project

To integrate MyBatis with Spring Boot, follow these steps:

Step 1: Creating a Spring Boot Project (Spring Initializr)

  1. Go to Spring Initializr
  2. Select the following settings:
    • Project: Maven
    • Language: Java
    • Spring Boot Version: Latest stable version
    • Dependencies:
      • Spring Web (for REST API)
      • Spring Boot MyBatis Starter (MyBatis integration)
      • Spring Data JDBC (Persist data in SQL stores with plain JDBC using Spring Data)
      • H2/MySQL/PostgreSQL Driver (Choose based on your database)
      • Lombok (Java annotation library which helps to reduce boilerplate code)
      • Spring Boot DevTools (Provides fast application restarts, LiveReload, and configurations for enhanced development experience)
  3. Click "Generate" to download the project.
  4. Extract the ZIP file and open it in your favorite IDE (IntelliJ, Eclipse, VS Code).

Step 2: Adding MyBatis and Database Dependencies

Open pom.xml and ensure it includes the necessary dependencies:

Complete pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0" 
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.example</groupId>
    <artifactId>spring-boot-mybatis</artifactId>
    <version>1.0.0</version>
    <packaging>jar</packaging>

    <name>Spring Boot MyBatis Example</name>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>3.2.0</version> <!-- Use latest version -->
        <relativePath/> 
    </parent>

    <properties>
        <java.version>17</java.version>  <!-- Use 8, 11, 17, or the latest supported version -->
        <mybatis-spring-boot.version>3.0.2</mybatis-spring-boot.version>
    </properties>

    <dependencies>
        <!-- Spring Boot Web for REST API -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <!-- MyBatis Spring Boot Starter -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>${mybatis-spring-boot.version}</version>
        </dependency>

        <!-- Spring Boot JDBC (Required for Database Connectivity) -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>

        <!-- Database Drivers (Choose One) -->

        <!-- H2 Database (for in-memory development/testing) -->
        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <scope>runtime</scope>
        </dependency>

        <!-- MySQL Database -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>

        <!-- PostgreSQL Database -->
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <scope>runtime</scope>
        </dependency>

        <!-- Lombok (Optional, for reducing boilerplate code in models) -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <scope>provided</scope>
        </dependency>

        <!-- Spring Boot DevTools (Optional, for live reload during development) -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
            <optional>true</optional>
        </dependency>

        <!-- Spring Boot Test (For unit testing) -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
</project>

Step 3: Configuring Database Connection

Now, configure the database connection in application.yml or application.properties.

Using H2 (In-Memory Database)

Use H2 when you need a lightweight, in-memory database for testing.

src/main/resources/application.yml

spring:
  datasource:
    url: jdbc:h2:mem:testdb
    driver-class-name: org.h2.Driver
    username: sa
    password: 
  h2:
    console:
      enabled: true  # Enables H2 Web Console (http://localhost:8080/h2-console)
  mybatis:
    mapper-locations: classpath:mappers/*.xml # Mapper files location
➡️ spring.datasource.url → Uses an in-memory H2 database (testdb).
➡️ spring.datasource.driver-class-name → H2 database driver (org.h2.Driver).
➡️ spring.datasource.username → Default H2 username (sa).
➡️ spring.datasource.password → No password (empty by default).
➡️ spring.h2.console.enabled → Enables H2 Web Console at http://localhost:8080/h2-console.
➡️ mybatis.mapper-locations → Specifies location for MyBatis XML mapper files (resources/mappers/).

Using MySQL

For a persistent database, use MySQL.

src/main/resources/application.yml

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/mydatabase
    driver-class-name: com.mysql.cj.jdbc.Driver
    username: root
    password: mypassword
  mybatis:
    mapper-locations: classpath:mappers/*.xml
    configuration:
      map-underscore-to-camel-case: true # Auto-convert snake_case to camelCase

➡️ spring.datasource.url → Connects to MySQL database (mydatabase on localhost:3306).
➡️ spring.datasource.driver-class-name → Uses MySQL JDBC driver (com.mysql.cj.jdbc.Driver).
➡️ spring.datasource.username → MySQL database username (root).
➡️ spring.datasource.password → MySQL database password (mypassword).
➡️ mybatis.mapper-locations → Specifies MyBatis XML mapper files location (resources/mappers/).
➡️ mybatis.configuration.map-underscore-to-camel-case → Converts snake_case column names to camelCase Java properties automatically.

Using PostgreSQL

For PostgreSQL, update the configuration:

src/main/resources/application.yml

spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/mydatabase
    driver-class-name: org.postgresql.Driver
    username: postgres
    password: mypassword
  mybatis:
    mapper-locations: classpath:mappers/*.xml
    configuration:
      map-underscore-to-camel-case: true

➡️ Ensure PostgreSQL is running, and update mydatabase, postgres, and mypassword as needed.

Step 4: Running the Application

After configuring the database, start the Spring Boot application:

mvn spring-boot:run

If everything is correct, the application will start without errors.

3. Configuring MyBatis in Spring Boot

Once we have set up the Spring Boot + MyBatis project and configured the database connection, we need to configure MyBatis properly.

➡️ MyBatis XML vs. Annotation-based Configuration

MyBatis allows two different ways to define SQL queries and mappings:

1️⃣ XML-based Configuration (Recommended for Complex Queries)

  • Queries and mappings are defined in .xml files inside resources/mappers/.
  • Useful for complex queries, stored procedures, and dynamic SQL.
  • Supports better separation of concerns (SQL is separate from Java code).

Example: UserMapper.xml (Inside src/main/resources/mappers/)

<?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">

    <!-- Select Query -->
    <select id="findById" resultType="com.example.model.User">
        SELECT * FROM users WHERE id = #{id}
    </select>

    <!-- Insert Query -->
    <insert id="insertUser">
        INSERT INTO users (name, email) VALUES (#{name}, #{email})
    </insert>

</mapper>

2️⃣ Annotation-based Configuration (For Simple Queries)

  • SQL is directly written inside Java code using annotations like @Select, @Insert, etc.
  • Faster and easier for small projects with simple queries.

Example: UserMapper.java (Using Annotations)

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

    @Insert("INSERT INTO users (name, email) VALUES (#{name}, #{email})")
    void insertUser(User user);
}

❓ Which One Should You Use?

✔️ Use XML-based configuration for complex queries (joins, stored procedures, dynamic SQL).
✔️ Use annotations for simple queries (basic CRUD operations).
✔️ You can combine both in a project as needed.

➡️ Setting Up SqlSessionFactory

MyBatis requires an SqlSessionFactory to manage database interactions.
Spring Boot automatically configures it, but we can customize it if needed.

Manual SqlSessionFactory Configuration (Optional)

If we need custom settings (like MyBatis type aliases, config locations), we define:

@Configuration
@MapperScan("com.example.mapper") // Scan for MyBatis Mappers
public class MyBatisConfig {

    @Bean
    public SqlSessionFactory sqlSessionFactory(DataSource dataSource) throws Exception {
        SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(dataSource);
        sessionFactory.setMapperLocations(
            new PathMatchingResourcePatternResolver().getResources("classpath:mappers/*.xml")
        );
        return sessionFactory.getObject();
    }
}

✔️ What This Does?
✔ Uses DataSource to connect to the database.
✔ Loads MyBatis XML mapper files from resources/mappers/.
✔ Registers SqlSessionFactory as a Spring bean.

➡️ Defining @MapperScan for Automatic Mapper Scanning

Instead of manually adding @Mapper to each mapper interface, we use @MapperScan to automatically scan and register all MyBatis mappers.

Example: Enabling @MapperScan in the Main Application Class

@SpringBootApplication
@MapperScan("com.example.mapper") // Scans all mapper interfaces in this package
public class MyBatisApplication {
    public static void main(String[] args) {
        SpringApplication.run(MyBatisApplication.class, args);
    }
}

✔️ What This Does?
✔ Automatically registers all interfaces in com.example.mapper as MyBatis Mappers.
✔ Eliminates the need to add @Mapper to each interface.

➡️ Using SqlSessionTemplate for Managing Database Operations

SqlSessionTemplate is a thread-safe implementation of SqlSession that manages database interactions and transactions.

If we need to manually manage MyBatis sessions (instead of relying on MyBatis Mapper interfaces), we can define it as a Spring bean:

Example: Configuring SqlSessionTemplate

@Configuration
public class MyBatisSessionConfig {

    @Bean
    public SqlSessionTemplate sqlSessionTemplate(SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

✔️ What This Does?
✔ Ensures MyBatis sessions are thread-safe.
✔ Automatically handles transactions and connection management.
✔ Required only if using manual MyBatis session management (not needed for typical Mapper interfaces).

🔹 Summary of Key Concepts in MyBatis Configuration

FeatureXML-basedAnnotation-based
Query DefinitionUses separate .xml filesWritten directly in Java code
Complex Queries✅ Best suited for complex queries❌ Hard to manage large queries
Simplicity❌ More setup required✅ Easier and faster
Flexibility✅ More control over SQL mapping❌ Limited to simple queries

✔️ Use @MapperScan to automatically register all mappers.
✔️ Use SqlSessionFactory if custom settings are needed.
✔️ Use SqlSessionTemplate only if managing MyBatis sessions manually.

4. Creating Database Entities and Mappers in Spring Boot MyBatis

Now that we have configured MyBatis in our Spring Boot application, we can start implementing database entities and MyBatis mappers to perform CRUD operations.

➡️ Defining Entity Classes (POJOs)

In MyBatis, entities (POJOs) represent database tables. These are plain Java classes with fields mapping to table columns.

Example: User Entity

package com.example.model;

public class User {
    private int id;
    private String name;
    private String email;

    // Constructors
    public User() {}
    public User(int id, String name, String email) {
        this.id = id;
        this.name = name;
        this.email = email;
    }

    // Getters & Setters
    public int getId() { return id; }
    public void setId(int id) { this.id = id; }

    public String getName() { return name; }
    public void setName(String name) { this.name = name; }

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

✔️ Why POJOs?
✔ Represent database tables as Java objects.
✔ Used by MyBatis for mapping SQL query results.
✔ Simple getter/setter methods for field access.

➡️ Creating MyBatis Mapper Interfaces

Mapper interfaces define database operations like SELECT, INSERT, UPDATE, and DELETE.

Example: UserMapper.java (Mapper Interface)

package com.example.mapper;

import com.example.model.User;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface UserMapper {
    // Fetch user by ID
    @Select("SELECT * FROM users WHERE id = #{id}")
    User findById(int id);

    // Fetch all users
    @Select("SELECT * FROM users")
    List<User> findAll();

    // Insert a new user
    @Insert("INSERT INTO users (name, email) VALUES (#{name}, #{email})")
    @Options(useGeneratedKeys = true, keyProperty = "id") // Auto-generate primary key
    void insertUser(User user);

    // Update user
    @Update("UPDATE users SET name = #{name}, email = #{email} WHERE id = #{id}")
    void updateUser(User user);

    // Delete user by ID
    @Delete("DELETE FROM users WHERE id = #{id}")
    void deleteUser(int id);
}

✔️ What This Does?
✔ Uses MyBatis annotations (@Select, @Insert, @Update, @Delete).
✔ Automatically maps SQL results to the User entity.
✔ Uses #{} to pass parameters dynamically.
✔ @Options(useGeneratedKeys = true) ensures MySQL auto-generates the primary key.

➡️ Writing SQL Queries in XML Mapper Files

For complex queries, it's better to use XML files instead of annotations.

Example: UserMapper.xml (Inside resources/mappers/)

<?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">

    <!-- Select Query -->
    <select id="findById" parameterType="int" resultType="com.example.model.User">
        SELECT * FROM users WHERE id = #{id}
    </select>

    <!-- Select All -->
    <select id="findAll" resultType="com.example.model.User">
        SELECT * FROM users
    </select>

    <!-- Insert -->
    <insert id="insertUser" parameterType="com.example.model.User" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO users (name, email) VALUES (#{name}, #{email})
    </insert>

    <!-- Update -->
    <update id="updateUser" parameterType="com.example.model.User">
        UPDATE users SET name = #{name}, email = #{email} WHERE id = #{id}
    </update>

    <!-- Delete -->
    <delete id="deleteUser" parameterType="int">
        DELETE FROM users WHERE id = #{id}
    </delete>

</mapper>

✔️ Why Use XML?
✔ Separates SQL queries from Java code.
✔ Easier to manage complex queries like joins or stored procedures.
✔ Allows dynamic SQL using <if> and <choose> tags.

➡️ Using @Insert, @Select, @Update, @Delete Annotations

We can use annotations directly in the mapper interface instead of XML.

1️⃣ @Select – Fetch Data

@Select("SELECT * FROM users WHERE id = #{id}")
User findById(int id);

✔️ Fetches a single user by ID.

2️⃣ @Insert – Insert Data

@Insert("INSERT INTO users (name, email) VALUES (#{name}, #{email})")
@Options(useGeneratedKeys = true, keyProperty = "id") // Auto-generate ID
void insertUser(User user);

✔️ Inserts a new user into the database.

3️⃣ @Update – Update Data

@Update("UPDATE users SET name = #{name}, email = #{email} WHERE id = #{id}")
void updateUser(User user);

✔️ Updates existing user data.

4️⃣ @Delete – Delete Data

@Delete("DELETE FROM users WHERE id = #{id}")
void deleteUser(int id);

✔️ Deletes a user by ID.

🔹 Summary of MyBatis Mappers

FeatureXML-basedAnnotation-based
Where SQL is Written?Separate XML fileInside Java interface
Best for?Complex queries, joins, stored proceduresSimple queries, CRUD operations
PerformanceSlightly better for bulk queriesFaster for basic queries
Flexibility✅ Supports dynamic SQL (<if>, <foreach>)❌ No dynamic SQL

5. CRUD Operations in MyBatis

Now that we have set up MyBatis mappers and database entities, let's implement CRUD (Create, Read, Update, Delete) operations using MyBatis in a Spring Boot application.

➡️ Simple CRUD Example with MyBatis

1️⃣ Create (INSERT) - Adding a New User

We use @Insert or XML <insert> to insert a record into the database.

Using Annotation-Based Mapper

@Insert("INSERT INTO users (name, email) VALUES (#{name}, #{email})")
@Options(useGeneratedKeys = true, keyProperty = "id") // Automatically get the generated ID
void insertUser(User user);

Using XML-Based Mapper

<insert id="insertUser" parameterType="com.example.model.User" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO users (name, email) VALUES (#{name}, #{email})
</insert>

2️⃣ Read (SELECT) - Fetching Users

To retrieve user data, we use @Select or XML <select>.

Find by ID (Single Record)

Annotation-Based
@Select("SELECT * FROM users WHERE id = #{id}")
User findById(int id);
XML-Based
<select id="findById" parameterType="int" resultType="com.example.model.User">
    SELECT * FROM users WHERE id = #{id}
</select>

Find All Users (Multiple Records)

Annotation-Based
@Select("SELECT * FROM users")
List<User> findAll();
XML-Based
<select id="findAll" resultType="com.example.model.User">
    SELECT * FROM users
</select>

3️⃣ Update - Modifying User Data

We use @Update or <update> to update existing records.

Annotation-Based
@Update("UPDATE users SET name = #{name}, email = #{email} WHERE id = #{id}")
void updateUser(User user);
XML-Based
<update id="updateUser" parameterType="com.example.model.User">
    UPDATE users SET name = #{name}, email = #{email} WHERE id = #{id}
</update>

4️⃣ Delete - Removing a User

We use @Delete or <delete> to delete records.

Annotation-Based
@Delete("DELETE FROM users WHERE id = #{id}")
void deleteUser(int id);
XML-Based
<delete id="deleteUser" parameterType="int">
    DELETE FROM users WHERE id = #{id}
</delete>

➡️ Using Result Maps for Custom Mappings

Sometimes, column names in a database don’t match Java field names. We use Result Maps to define custom mappings.

Example: Custom Mapping (Camel Case Conversion)

Suppose the database has column names user_id, full_name, user_email, but our Java entity uses id, name, email.

Define a Result Map in UserMapper.xml

<resultMap id="UserResultMap" type="com.example.model.User">
    <id property="id" column="user_id"/>
    <result property="name" column="full_name"/>
    <result property="email" column="user_email"/>
</resultMap>

<select id="findById" parameterType="int" resultMap="UserResultMap">
    SELECT user_id, full_name, user_email FROM users WHERE user_id = #{id}
</select>

✔️ What This Does?
✔ Maps user_id → id, full_name → name, user_email → email automatically.
✔ Fixes column mismatches without modifying the database schema.

➡️ Handling Auto-Generated Primary Keys

1️⃣ Auto-Generated IDs in MyBatis

When inserting data into a table with an auto-increment primary key, MyBatis can automatically fetch the generated ID.

Using @Options(useGeneratedKeys=true, keyProperty="id")

@Insert("INSERT INTO users (name, email) VALUES (#{name}, #{email})")
@Options(useGeneratedKeys = true, keyProperty = "id") // Fetch generated ID
void insertUser(User user);

✔ The @Options annotation ensures MyBatis retrieves the newly created primary key and assigns it to the id field.

Using XML Configuration

<insert id="insertUser" parameterType="com.example.model.User" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO users (name, email) VALUES (#{name}, #{email})
</insert>

🔹 Summary of MyBatis CRUD Operations

OperationAnnotation-BasedXML-Based
Create (INSERT)@Insert<insert>
Read (SELECT)@Select<select>
Update (UPDATE)@Update<update>
Delete (DELETE)@Delete<delete>
Custom Mapping❌ Not possible✅ Uses <resultMap>
Auto-Generated Keys✅ @Options(useGeneratedKeys=true)✅ useGeneratedKeys="true"

6. Advanced Querying in MyBatis

Now, we’ll explore advanced MyBatis features for writing dynamic queries, handling complex mappings, pagination, joins, and stored procedures.

➡️ Dynamic SQL using <if>, <choose>, <where>, <foreach>

Dynamic SQL is used when queries need to be built conditionally at runtime. MyBatis provides XML tags to dynamically construct SQL statements.

✔️ Conditional Queries with <if>

☑️ Use Case: Select users based on optional filters (name & email).

Example: Dynamic Query with <if>

<select id="findUsers" parameterType="map" resultType="com.example.model.User">
    SELECT * FROM users 
    WHERE 1=1 
    <if test="name != null">
        AND name = #{name}
    </if>
    <if test="email != null">
        AND email = #{email}
    </if>
</select>

✔ If name and email are provided, they are added to the query.
✔ If name is null, only the email filter is applied, and vice versa.

✔️ Switch-Case Logic Using <choose>, <when>, <otherwise>

☑️ Use Case: Fetch users based on a priority condition (ID > Name > Email).

Example: Using <choose> for Conditional Logic

<select id="findUserByPriority" parameterType="map" resultType="com.example.model.User">
    SELECT * FROM users 
    WHERE 
    <choose>
        <when test="id != null">
            id = #{id}
        </when>
        <when test="name != null">
            name = #{name}
        </when>
        <otherwise>
            email = #{email}
        </otherwise>
    </choose>
</select>

✔ If id is provided, it is used; if not, name is checked; otherwise, email is used.

✔️ Handling Optional Filters with <where> and <trim>

☑️ Use Case: Prevent unnecessary AND when dynamic conditions exist.

Example: Using <where> to Handle Dynamic Filters

<select id="findUsersDynamic" parameterType="map" resultType="com.example.model.User">
    SELECT * FROM users 
    <where>
        <if test="name != null">
            name = #{name}
        </if>
        <if test="email != null">
            AND email = #{email}
        </if>
    </where>
</select>

✔ <where> automatically removes leading AND if no conditions exist.

✔️ Iterating Over Collections with <foreach>

☑️ Use Case: Using an IN clause with a list of IDs.

Example: Using <foreach> for IN Clause

<select id="findUsersByIds" parameterType="list" resultType="com.example.model.User">
    SELECT * FROM users WHERE id IN 
    <foreach item="id" collection="list" open="(" separator="," close=")">
        #{id}
    </foreach>
</select>

✔ Iterates over a list of IDs and generates IN (1, 2, 3, 4, 5).

➡️ Using ResultMap for Complex Mappings

✔️ Mapping Custom Column Names to Java Fields

☑️ Use Case: The database has different column names than Java fields.

Example: Using <resultMap> for Custom Mappings

<resultMap id="UserResultMap" type="com.example.model.User">
    <id property="id" column="user_id"/>
    <result property="name" column="full_name"/>
    <result property="email" column="user_email"/>
</resultMap>

<select id="findById" parameterType="int" resultMap="UserResultMap">
    SELECT user_id, full_name, user_email FROM users WHERE user_id = #{id}
</select>

✔ Fixes column mismatches without modifying the database schema.

✔️ Handling Nested Objects (One-to-One & One-to-Many Relationships)

Example: One-to-One Mapping (User → Address)

<resultMap id="UserResultMap" type="com.example.model.User">
    <id property="id" column="user_id"/>
    <result property="name" column="full_name"/>
    <association property="address" column="address_id" javaType="com.example.model.Address" select="findAddressById"/>
</resultMap>

✔ Maps an Address object inside a User object.

Example: One-to-Many Mapping (User → Orders)

<resultMap id="UserWithOrders" type="com.example.model.User">
    <id property="id" column="user_id"/>
    <result property="name" column="full_name"/>
    <collection property="orders" column="user_id" javaType="List" ofType="com.example.model.Order" select="findOrdersByUserId"/>
</resultMap>

✔ Fetches a list of Order objects for each user.

➡️ Pagination Support (LIMIT, OFFSET)

✔️ MySQL & PostgreSQL Pagination

<select id="findUsersPaginated" resultType="com.example.model.User">
    SELECT * FROM users ORDER BY id LIMIT #{limit} OFFSET #{offset}
</select>

✔️ Oracle Pagination (ROWNUM)

<select id="findUsersPaginated" resultType="com.example.model.User">
    SELECT * FROM (
        SELECT a.*, ROWNUM rnum FROM (SELECT * FROM users ORDER BY id) a 
        WHERE ROWNUM <= #{offset} + #{limit}
    ) WHERE rnum > #{offset}
</select>

✔️ Integration with PageHelper Plugin

PageHelper.startPage(page, pageSize);
List<User> users = userMapper.findAll();

✔ Automatic pagination support for MyBatis.

➡️ Handling Complex Joins with MyBatis

✔️ Inner Join Example

<select id="findUsersWithOrders" resultMap="UserWithOrders">
    SELECT u.*, o.* FROM users u 
    INNER JOIN orders o ON u.id = o.user_id
</select>

✔ Fetches users with their orders.

✔️ Left Join Example

<select id="findUsersWithOptionalOrders" resultMap="UserWithOrders">
    SELECT u.*, o.* FROM users u 
    LEFT JOIN orders o ON u.id = o.user_id
</select>

✔ Fetches users, even if they don’t have orders.

➡️ Using Stored Procedures with MyBatis

✔️ Calling a Stored Procedure

<select id="getUserById" statementType="CALLABLE" parameterType="int" resultType="com.example.model.User">
    CALL getUserById(#{id})
</select>

✔ Calls a stored procedure named getUserById.

✔️ Handling IN/OUT Parameters

<procedure id="updateUserEmail" parameterType="map">
    CALL updateUserEmail(#{id, mode=IN}, #{email, mode=IN}, #{result, mode=OUT, jdbcType=INTEGER})
</procedure>

✔ Handles input and output parameters in stored procedures.

7. Using MyBatis with Spring Boot Services & REST Controllers

Now that we've explored MyBatis integration, let's implement a Service Layer and expose RESTful APIs using Spring Boot controllers.

➡️ Service Layer Implementation (@Service)

The Service Layer is responsible for business logic and acts as a bridge between the controller and MyBatis Mapper.

✔️ Creating a Service Interface

Define a service interface to declare operations for user management.

import java.util.List;

public interface UserService {
    User getUserById(int id);
    List<User> getAllUsers();
    void addUser(User user);
    void updateUser(User user);
    void deleteUser(int id);
}

✔️ Implementing the Service Layer (@Service)

Create a Service Implementation class using @Service.

import org.springframework.stereotype.Service;
import org.springframework.beans.factory.annotation.Autowired;
import java.util.List;

@Service
public class UserServiceImpl implements UserService {

    @Autowired
    private UserMapper userMapper; // Inject MyBatis Mapper

    @Override
    public User getUserById(int id) {
        return userMapper.findById(id);
    }

    @Override
    public List<User> getAllUsers() {
        return userMapper.findAll();
    }

    @Override
    public void addUser(User user) {
        userMapper.insertUser(user);
    }

    @Override
    public void updateUser(User user) {
        userMapper.updateUser(user);
    }

    @Override
    public void deleteUser(int id) {
        userMapper.deleteUser(id);
    }
}

✔ Why use a Service Layer?
✔ Separates business logic from controllers
✔ Improves code maintainability and reusability
✔ Enables transaction management for database operations

➡️ Using MyBatis with Spring Boot REST Controllers

Spring Boot controllers expose MyBatis operations via RESTful APIs.

✔️ Creating a REST Controller (@RestController)

Define an API endpoint to handle user operations.

import org.springframework.web.bind.annotation.*;
import org.springframework.beans.factory.annotation.Autowired;
import java.util.List;

@RestController
@RequestMapping("/users") // Base URL for User APIs
public class UserController {

    @Autowired
    private UserService userService;

    // Get all users
    @GetMapping
    public List<User> getAllUsers() {
        return userService.getAllUsers();
    }

    // Get user by ID
    @GetMapping("/{id}")
    public User getUserById(@PathVariable int id) {
        return userService.getUserById(id);
    }

    // Add a new user
    @PostMapping
    public String addUser(@RequestBody User user) {
        userService.addUser(user);
        return "User added successfully!";
    }

    // Update user
    @PutMapping("/{id}")
    public String updateUser(@PathVariable int id, @RequestBody User user) {
        user.setId(id);
        userService.updateUser(user);
        return "User updated successfully!";
    }

    // Delete user
    @DeleteMapping("/{id}")
    public String deleteUser(@PathVariable int id) {
        userService.deleteUser(id);
        return "User deleted successfully!";
    }
}

✔ REST API Endpoints:
📌 GET /users → Retrieve all users
📌 GET /users/{id} → Retrieve user by ID
📌 POST /users → Add a new user
📌 PUT /users/{id} → Update user
📌 DELETE /users/{id} → Delete user

➡️ Transaction Management (@Transactional)

MyBatis does not handle transactions automatically like JPA/Hibernate.
Spring Boot provides @Transactional for ensuring atomic operations.

✔️ Enabling Transactions in Spring Boot

Add the @EnableTransactionManagement annotation in your main application class.

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.transaction.annotation.EnableTransactionManagement;

@SpringBootApplication
@EnableTransactionManagement  // Enable transaction management
public class MyBatisApplication {
    public static void main(String[] args) {
        SpringApplication.run(MyBatisApplication.class, args);
    }
}

✔️ Using @Transactional in the Service Layer

☑️ Ensures atomicity – either all operations succeed, or none are applied.

Example: Transaction for Adding a User and Log Entry

import org.springframework.transaction.annotation.Transactional;

@Service
public class UserServiceImpl implements UserService {

    @Autowired
    private UserMapper userMapper;

    @Autowired
    private AuditLogMapper auditLogMapper;

    @Override
    @Transactional // Ensures both operations succeed or fail together
    public void addUser(User user) {
        userMapper.insertUser(user); // Insert user

        // Insert log entry (if this fails, user insert is rolled back)
        auditLogMapper.insertLog("User added: " + user.getName());
    }
}

✔ If the insertLog fails, insertUser is rolled back automatically.
✔ Prevents incomplete operations in case of errors.

➡️ Error Handling and Exception Handling

Spring Boot provides robust exception handling using @ControllerAdvice and @ExceptionHandler.

✔️ Custom Exception Class

Create a custom exception for handling user-related errors.

public class UserNotFoundException extends RuntimeException {
    public UserNotFoundException(String message) {
        super(message);
    }
}

✔️ Handling Exceptions in the Service Layer

Throw custom exceptions when a user is not found.

@Service
public class UserServiceImpl implements UserService {

    @Autowired
    private UserMapper userMapper;

    @Override
    public User getUserById(int id) {
        User user = userMapper.findById(id);
        if (user == null) {
            throw new UserNotFoundException("User with ID " + id + " not found");
        }
        return user;
    }
}

✔ Ensures clean error messages instead of returning null.

✔️ Global Exception Handling with @ControllerAdvice

To return custom error messages, use a global exception handler.

import org.springframework.web.bind.annotation.ExceptionHandler;
import org.springframework.web.bind.annotation.RestControllerAdvice;
import org.springframework.http.ResponseEntity;
import org.springframework.http.HttpStatus;

@RestControllerAdvice
public class GlobalExceptionHandler {

    @ExceptionHandler(UserNotFoundException.class)
    public ResponseEntity<String> handleUserNotFound(UserNotFoundException ex) {
        return new ResponseEntity<>(ex.getMessage(), HttpStatus.NOT_FOUND);
    }

    @ExceptionHandler(Exception.class)
    public ResponseEntity<String> handleGeneralException(Exception ex) {
        return new ResponseEntity<>("An error occurred: " + ex.getMessage(), HttpStatus.INTERNAL_SERVER_ERROR);
    }
}

✔ Returns custom HTTP responses when an error occurs.

8. Testing MyBatis with Spring Boot

Testing is crucial to ensure that MyBatis-based applications work as expected. Now we cover:

☑️ Unit Testing MyBatis Mappers
☑️ Using H2 Database for Integration Testing
☑️ Mocking MyBatis Queries with Mockito

➡️ Unit Testing MyBatis Mappers

Unit testing MyBatis Mappers helps validate individual database query methods without involving the entire application context.

✔️ Setting Up JUnit and MyBatis Dependencies

Ensure your pom.xml includes these dependencies:

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-test</artifactId>
    <scope>test</scope>
</dependency>

<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter-test</artifactId>
    <version>3.0.4</version>
    <scope>test</scope>
</dependency>

<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <scope>test</scope>
</dependency>

✔️ Creating a Test for MyBatis Mapper

Use JUnit and Spring Boot Test to test MyBatis Mapper methods.

import static org.assertj.core.api.Assertions.assertThat;
import org.junit.jupiter.api.Test;
import org.mybatis.spring.boot.test.autoconfigure.MybatisTest;
import org.springframework.beans.factory.annotation.Autowired;
import java.util.List;

@MybatisTest  // Loads only MyBatis-related components
public class UserMapperTest {

    @Autowired
    private UserMapper userMapper;  // Inject MyBatis Mapper

    @Test
    public void testInsertAndRetrieveUser() {
        User user = new User(1, "John Doe", "john@example.com");
        userMapper.insertUser(user);  // Insert user

        User retrievedUser = userMapper.findById(1);  // Retrieve user
        assertThat(retrievedUser).isNotNull();
        assertThat(retrievedUser.getName()).isEqualTo("John Doe");
    }

    @Test
    public void testFindAllUsers() {
        List<User> users = userMapper.findAll();
        assertThat(users).isNotEmpty();
    }
}

✔ Annotations Used
📌 @MybatisTest → Loads only MyBatis components, making tests faster
📌 @Autowired → Injects UserMapper for testing
📌 assertThat() → Performs assertions on test results

➡️ Using H2 Database for Testing

H2 is an in-memory database that helps test database operations without requiring an actual database server.

✔️ Configuring H2 for Testing

Modify src/test/resources/application-test.properties:

spring.datasource.url=jdbc:h2:mem:testdb
spring.datasource.driver-class-name=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=
spring.sql.init.mode=always  # Ensure schema is initialized

Now, H2 will be used only during testing.

✔️ Writing Integration Tests with H2

Use H2 to test database interactions without modifying the real database.

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.autoconfigure.jdbc.JdbcTest;
import org.springframework.test.context.jdbc.Sql;
import static org.assertj.core.api.Assertions.assertThat;
import java.util.List;

@JdbcTest  // Loads only JDBC-related components
public class UserRepositoryTest {

    @Autowired
    private UserMapper userMapper;

    @Test
    @Sql(scripts = "/test-schema.sql")  // Load test schema
    public void testInsertAndFetchUser() {
        User user = new User(2, "Alice", "alice@example.com");
        userMapper.insertUser(user);

        User retrievedUser = userMapper.findById(2);
        assertThat(retrievedUser).isNotNull();
        assertThat(retrievedUser.getName()).isEqualTo("Alice");
    }
}

✔ Why Use H2 for Testing?
☑️ No need for an actual database
☑️ Faster tests using in-memory storage
☑️ Resets data between tests

➡️ Mocking MyBatis Queries with Mockito

Mockito allows us to mock MyBatis Mappers to test the Service Layer without hitting the database.

✔️ Mocking UserMapper in Service Tests

Instead of testing with a real database, we mock UserMapper to simulate database responses.

import static org.mockito.Mockito.*;
import static org.assertj.core.api.Assertions.assertThat;
import org.junit.jupiter.api.Test;
import org.mockito.InjectMocks;
import org.mockito.Mock;
import org.mockito.MockitoAnnotations;
import java.util.Arrays;
import java.util.List;

public class UserServiceTest {

    @Mock
    private UserMapper userMapper; // Mock MyBatis Mapper

    @InjectMocks
    private UserServiceImpl userService; // Inject Mock into Service

    public UserServiceTest() {
        MockitoAnnotations.openMocks(this); // Initialize Mocks
    }

    @Test
    public void testGetAllUsers() {
        // Mock database response
        when(userMapper.findAll()).thenReturn(Arrays.asList(
            new User(1, "John Doe", "john@example.com"),
            new User(2, "Jane Doe", "jane@example.com")
        ));

        List<User> users = userService.getAllUsers();
        assertThat(users).hasSize(2);
        assertThat(users.get(0).getName()).isEqualTo("John Doe");

        verify(userMapper, times(1)).findAll(); // Ensure the method was called once
    }

    @Test
    public void testGetUserById() {
        when(userMapper.findById(1)).thenReturn(new User(1, "Alice", "alice@example.com"));

        User user = userService.getUserById(1);
        assertThat(user).isNotNull();
        assertThat(user.getName()).isEqualTo("Alice");

        verify(userMapper, times(1)).findById(1);
    }
}

✔ Mockito Features Used
📌 @Mock → Mocks UserMapper to simulate database responses
📌 @InjectMocks → Injects mocked dependencies into UserServiceImpl
📌 when(...).thenReturn(...) → Simulates method return values
📌 verify(..., times(N)) → Ensures methods were called N times

🔹 Summary: Testing MyBatis in Spring Boot

Test TypePurposeTools Used
Unit Testing MyBatis MappersTests MyBatis queries without Spring Boot@MybatisTest, @JdbcTest, H2
Integration Testing with H2Tests full database operationsH2, @Sql, @SpringBootTest
Mocking MyBatis QueriesTests Service Layer without DBMockito, @Mock, @InjectMocks

9. Caching in MyBatis

Caching in MyBatis helps improve performance by reducing redundant database queries. It supports two levels of caching:

☑️ First-Level Cache (Session-Level) – Default and per-session cache
☑️ Second-Level Cache (Application-Level) – Shared across multiple sessions

➡️ Enabling MyBatis First-Level Cache (Session-Level Caching)

✔️ How First-Level Cache Works?

  • The first-level cache is enabled by default in MyBatis.
  • It stores query results in the SqlSession scope.
  • If the same query is executed again in the same session, MyBatis returns the cached result instead of querying the database.
  • The cache is cleared when the session is closed or a commit/rollback occurs.

✔️ Example: First-Level Caching in MyBatis

SqlSession session1 = sqlSessionFactory.openSession();
UserMapper userMapper1 = session1.getMapper(UserMapper.class);
User user1 = userMapper1.findById(1); // Database query executed

User user2 = userMapper1.findById(1); // Cached result returned, no DB call
session1.close(); // Cache is cleared after session is closed

☑️ First-level cache is automatic and requires no additional configuration.

➡️ Enabling MyBatis Second-Level Cache (Application-Level Caching)

✔️ What is Second-Level Cache?

  • Shared across multiple sessions in the application.
  • Requires explicit configuration in mybatis-config.xml or in mapper files.
  • Supports custom caching providers like EhCache, Redis, etc.

✔️ Steps to Enable Second-Level Cache

1️⃣ Enable Cache in MyBatis Configuration (mybatis-config.xml)

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

2️⃣ Enable Cache in Mapper File (UserMapper.xml)

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

3️⃣ Enable Cache in Mapper Interface (UserMapper.java)

@CacheNamespace // Enables second-level cache
public interface UserMapper {
    @Select("SELECT * FROM users WHERE id = #{id}")
    User findById(int id);
}

☑️ Now, if the same query is executed multiple times, MyBatis fetches the result from the cache instead of querying the database.

10. Conclusion

In this guide, we explored how to integrate MyBatis with Spring Boot for efficient database management, covering everything from setup to advanced querying and caching. Let's summarize the key takeaways:

1️⃣ Setting Up & Configuring MyBatis

  • We started by setting up a Spring Boot project with MyBatis and the necessary database dependencies.
  • Configuration involved defining database connections and integrating MyBatis with SqlSessionFactory and @MapperScan for automatic mapper scanning.

2️⃣ Database Entities, Mappers & CRUD Operations

  • Defined POJOs (entities) to map database tables to Java objects.
  • Created MyBatis Mapper Interfaces and used both XML-based and annotation-based SQL queries.
  • Implemented CRUD operations efficiently with MyBatis, using Result Maps for custom mappings and auto-generated keys for primary key handling.

3️⃣ Advanced Querying with MyBatis

  • We explored Dynamic SQL using <if>, <choose>, <foreach> for building flexible queries.
  • ResultMap was used for complex mappings, including one-to-many and many-to-one relationships.
  • Pagination techniques were covered using LIMIT, OFFSET, and PageHelper plugin for different databases.
  • We also looked at handling complex joins and calling stored procedures with MyBatis.

4️⃣ Integrating MyBatis with Spring Boot Services & REST Controllers

  • Developed a Service Layer (@Service) to separate business logic from database operations.
  • Used Spring’s @Transactional annotation for proper transaction management.
  • Built RESTful APIs that interact with MyBatis to handle client requests efficiently.
  • Implemented error handling to ensure smooth exception management.

5️⃣ Testing MyBatis with Spring Boot

  • Used H2 Database for writing unit tests to avoid external dependencies.
  • Mocked MyBatis queries using Mockito to isolate database interactions.
  • Ensured database consistency and integrity through comprehensive test cases.

6️⃣ Optimizing Performance with Caching

  • Implemented first-level (session) and second-level (application) caching in MyBatis.
  • Compared Spring Cache (@Cacheable) vs. MyBatis Cache, understanding when to use each.

Final Thoughts

MyBatis offers simplicity, flexibility, and performance when working with relational databases in Spring Boot applications. With its powerful mapping capabilities, dynamic SQL support, and caching mechanisms, it provides fine-grained control over queries while maintaining high performance.

By following this structured approach, you can:
✔ Build scalable and efficient data-driven applications with Spring Boot & MyBatis.
✔ Use best practices for performance optimization, caching, and transaction management.
✔ Ensure robust application architecture with proper testing and error handling.

🔖Tags: CRUD Java MyBatis Spring Boot
Share on Facebook
Share on Twitter

Leave a Reply

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