Hands‑On Unit Tests for a Complete MyBatis Integration with Spring Boot 3

Version and Test Framework

All test code uses Spring Boot 3.x, Spring Framework 6.x, JDK 17+, JUnit 5 and Spring Boot Test.

Environment Configuration

Maven Dependencies

<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>3.2.4</version>
</parent>
<dependencies>
    <!-- Spring Boot Starter -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter</artifactId>
    </dependency>
    <!-- Spring Boot Web -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <!-- Spring Boot AOP -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-aop</artifactId>
    </dependency>
    <!-- Spring Data JPA -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>
    <!-- Configuration Processor (optional) -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-configuration-processor</artifactId>
        <optional>true</optional>
    </dependency>
    <!-- H2 Database (runtime) -->
    <dependency>
        <groupId>com.h2database</groupId>
        <artifactId>h2</artifactId>
        <scope>runtime</scope>
    </dependency>
    <!-- MyBatis Spring Boot Starter -->
    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>3.0.3</version>
    </dependency>
    <!-- Spring Boot Test (test scope) -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
    </dependency>
</dependencies>

Application Properties

spring.datasource.url=jdbc:h2:mem:testdb
spring.datasource.driver-class-name=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=
spring.h2.console.enabled=true
mybatis.mapper-locations=classpath:mapper/*.xml
mybatis.configuration.map-underscore-to-camel-case=true
mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl

test‑yml Configuration

# Spring AOP configuration
spring:
  aop:
    # Force JDK dynamic proxy when target class has interfaces
    proxy-target-class: false
# H2 database configuration
  datasource:
    url: jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE
    driver-class-name: org.h2.Driver
    username: sa
    password:
# JPA configuration
  jpa:
    hibernate:
      ddl-auto: create # create tables on each test run
    show-sql: true
    properties:
      hibernate:
        format_sql: true
    database-platform: org.hibernate.dialect.H2Dialect
# H2 console (optional, for debugging)
h2:
  console:
    enabled: true

SQL Scripts

The schema creates four tables and inserts three sample rows.

CREATE TABLE IF NOT EXISTS mp_product (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    category VARCHAR(50),
    deleted INT DEFAULT 0,
    create_time TIMESTAMP,
    update_time TIMESTAMP
);

CREATE TABLE IF NOT EXISTS product (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    category VARCHAR(50),
    category_id INT
);

CREATE TABLE IF NOT EXISTS category (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL
);

CREATE TABLE IF NOT EXISTS product_detail (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(100) NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    product_category VARCHAR(50)
);

DELETE FROM product;
ALTER TABLE product ALTER COLUMN id RESTART WITH 1;
INSERT INTO product (name, price, category) VALUES ('Laptop', 5999.00, 'Electronics');
INSERT INTO product (name, price, category) VALUES ('Phone', 3999.00, 'Electronics');
INSERT INTO product (name, price, category) VALUES ('Book', 49.90, 'Education');

Test Class MyBatisTest

Annotated with

@SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.NONE)

and extends an abstract DB test class. The class autowires ProductMapper, ProductDetailMapper and SqlSessionFactory. Ten ordered test methods verify core MyBatis features:

Mapper proxy execution – asserts that productMapper.findById(1) returns a non‑null Product with expected name and price, and that findAll() yields three initial rows.

#{} pre‑compiled parameters – normal query returns expected rows; an attempted injection string results in an empty list, demonstrating SQL‑injection protection.

${} string concatenation – shows that using ${} allows injection; the same malicious string returns all three rows.

Dynamic SQL if/where – tests various condition combinations: by category, by name like, by minimum price, combined conditions, no match, and all‑null parameters (which return all rows).

Dynamic SQL foreach – queries a list of IDs (returns two rows), a single ID (returns one row), and a non‑existent ID (returns empty).

Dynamic SQL choose/when/otherwise – priority logic: when id is provided it is used; otherwise when name is provided it is used; otherwise returns null.

Dynamic SQL set – selective updates: only name, only price, and multiple fields; each assertion checks that unchanged fields retain their previous values.

ResultMap mapping – fetches a product with a custom resultMap and verifies all fields; also confirms that a missing record yields null.

Camel‑case automatic mapping – retrieves ProductDetail without a resultMap and asserts correct mapping of product_name → productName, etc.

First‑level cache – uses a raw SqlSession to show that the same object reference is returned within one session, while a new session returns a different object, proving cache isolation.

POJO Classes

Simple Java beans for the tables.

public class Product {
    private Integer id;
    private String name;
    private Double price;
    private String category;
    // getters and setters omitted for brevity
}

public class ProductDetail {
    private Integer id;
    private String productName;
    private Double unitPrice;
    private String productCategory;
    // getters and setters omitted for brevity
}

Mapper Interfaces

ProductMapper

defines CRUD operations, dynamic‑SQL methods, and a custom resultMap query. ProductDetailMapper provides a simple findById. RawCacheMapper is used for the first‑level cache test.

@Mapper
public interface ProductMapper {
    Product findById(@Param("id") Integer id);
    List<Product> findAll();
    List<Product> findByCategory(@Param("category") String category);
    int insert(Product product);
    int update(Product product);
    int deleteById(@Param("id") Integer id);
    List<Product> findByCondition(@Param("name") String name,
                                 @Param("category") String category,
                                 @Param("minPrice") Double minPrice);
    List<Product> findByIds(@Param("ids") List<Integer> ids);
    Product findByPriority(@Param("id") Integer id,
                           @Param("name") String name);
    int updateSelective(Product product);
    List<Product> findByNameLikeRaw(@Param("name") String name);
    Product findByIdWithResultMap(@Param("id") Integer id);
}

@Mapper
public interface ProductDetailMapper {
    ProductDetail findById(@Param("id") Integer id);
}

@Mapper
public interface RawCacheMapper {
    Product findById(@Param("id") Integer id);
}

XML Mapper Files

product‑mapper.xml

contains the resultMap, CRUD statements, and all dynamic‑SQL fragments (if/where, foreach, choose/when/otherwise, set, ${} injection example, and the custom resultMap select).

<?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.springcontainer.mybatis.ProductMapper">
    <resultMap id="productMap" type="com.example.springcontainer.mybatis.Product">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="price" column="price"/>
        <result property="category" column="category"/>
    </resultMap>

    <select id="findById" resultMap="productMap">
        SELECT * FROM product WHERE id = #{id}
    </select>

    <select id="findAll" resultMap="productMap">
        SELECT * FROM product ORDER BY id
    </select>

    <select id="findByCategory" resultMap="productMap">
        SELECT * FROM product
        <where>
            <if test="category != null and category != ''">
                AND category = #{category}
            </if>
        </where>
    </select>

    <insert id="insert" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO product (name, price, category)
        VALUES (#{name}, #{price}, #{category})
    </insert>

    <update id="update">
        UPDATE product
        <set>
            <if test="name != null">name = #{name},</if>
            <if test="price != null">price = #{price},</if>
            <if test="category != null">category = #{category},</if>
        </set>
        WHERE id = #{id}
    </update>

    <delete id="deleteById">
        DELETE FROM product WHERE id = #{id}
    </delete>

    <!-- Dynamic SQL: if + where -->
    <select id="findByCondition" resultMap="productMap">
        SELECT * FROM product
        <where>
            <if test="name != null and name != ''">
                AND name LIKE CONCAT('%', #{name}, '%')
            </if>
            <if test="category != null and category != ''">
                AND category = #{category}
            </if>
            <if test="minPrice != null">
                AND price >= #{minPrice}
            </if>
        </where>
        ORDER BY id
    </select>

    <!-- Dynamic SQL: foreach -->
    <select id="findByIds" resultMap="productMap">
        SELECT * FROM product
        WHERE id IN
        <foreach item="id" collection="ids" open="(" separator="," close=")">
            #{id}
        </foreach>
        ORDER BY id
    </select>

    <!-- Dynamic SQL: choose / when / otherwise -->
    <select id="findByPriority" resultMap="productMap">
        SELECT * FROM product
        <where>
            <choose>
                <when test="id != null">AND id = #{id}</when>
                <when test="name != null and name != ''">AND name = #{name}</when>
                <otherwise>AND 1 = 0</otherwise>
            </choose>
        </where>
    </select>

    <!-- Dynamic SQL: set for selective update -->
    <update id="updateSelective">
        UPDATE product
        <set>
            <if test="name != null">name = #{name},</if>
            <if test="price != null">price = #{price},</if>
            <if test="category != null">category = #{category},</if>
        </set>
        WHERE id = #{id}
    </update>

    <!-- ${} string concatenation (SQL injection risk) -->
    <select id="findByNameLikeRaw" resultMap="productMap">
        SELECT * FROM product WHERE name LIKE '${name}'
    </select>

    <!-- ResultMap custom query -->
    <select id="findByIdWithResultMap" resultMap="productMap">
        SELECT id, name, price, category FROM product WHERE id = #{id}
    </select>
</mapper>

product_detail‑mapper.xml

demonstrates automatic camel‑case mapping without an explicit resultMap.

<?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.springcontainer.mybatis.ProductDetailMapper">
    <select id="findById" resultType="com.example.springcontainer.mybatis.ProductDetail">
        SELECT id, product_name, unit_price, product_category
        FROM product_detail
        WHERE id = #{id}
    </select>
</mapper>

raw_cache_mapper.xml

provides a simple select used by the cache test.

<?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.springcontainer.mybatis.RawCacheMapper">
    <select id="findById" resultType="com.example.springcontainer.mybatis.Product">
        SELECT * FROM product WHERE id = #{id}
    </select>
</mapper>

Running the Tests

Two ways to execute the suite:

Maven command: mvn test -Dtest=MyBatisTest IDEA: open the class, right‑click and choose “Run ‘MyBatisTest’” or “Debug ‘MyBatisTest’”.

Related Documentation

Reference article: “MyBatis Full Analysis”.