Dynamic SQL with Conditional Logic

Dynamic SQL in MyBatis enables flexible query construction based on runtime conditions. Unlike raw JDBC where manual string concatenation leads to errors like missing spaces or trailing commas, MyBatis provides built-in tags for clean, maintainable code.

Key tags include:

• if: Conditionally includes fragments.
• where: Automatically adds WHERE clause and trims leading AND/OR.
• choose/when/otherwise: Implements switch-like logic.
• foreach: Iterates over collections for IN clauses.
• trim: Customizes prefix/suffix and removes unwanted operators.
• set: Generates SET clause while avoiding trailing commas.

For example, a query that filters monsters by age only when the input is valid:

<select id="findMonsterByAge" parameterType="int" resultType="monster">
    SELECT * FROM monster WHERE 1 = 1
    <if test="age >= 0">
        AND age > #{age}
    </if>
</select>

Similarly, using where ensures proper syntax even if some conditions are absent.

One-to-One Relationships via XML Mapping

In scenarios such as Person ↔ ID Card, MyBatis supports cascaded queries through <association> elements within resultMap.

Define the entity classes with mutual references:

public class Person {
    private Integer id;
    private String name;
    private IdenCard card;
    // getters and setters
}

public class IdenCard {
    private Integer id;
    private String card_sn;
    private Person person;
    // getters and setters
}

Map the relationship using a custom resultMap:

<resultMap id="PersonResultMap" type="Person">
    <id property="id" column="id" />
    <result property="name" column="name" />
    <association property="card" javaType="IdenCard">
        <result property="id" column="id" />
        <result property="card_sn" column="card_sn" />
    </association>
</resultMap>

This allows retrieving a person along with thier associated ID card in a single query.

Many-to-One Relationships Using Result Maps

For relationships like User → Pets (one user owns many pets), use <collection> to map listss.

Entities:

public class User {
    private Integer id;
    private String name;
    private List<Pet> pets;
    // getters and setters
}

public class Pet {
    private Integer id;
    private String nickname;
    private User user;
    // getters and setters
}

Map the association:

<resultMap id="userMap" type="User">
    <id property="id" column="id" />
    <result property="name" column="name" />
    <collection property="pets" column="id" ofType="Pet"
                select="com.mapper.PetMapper.getPetByUserId" />
</resultMap>

This enables loading a user and all their pets in one round trip.

Caching Mechanisms: Level 1 and Level 2

MyBatis includes two levels of caching:

Level 1 (Session-Level)

• Enabled by default.
• Cached within a single SqlSession.
• Clears automatically after commmit, close, or explicit clear.

Level 2 (Global-Level)

• Requires explicit configuration.
• Shared across multiple SqlSessions.
• Must implement Serializable for cached objects.

Enable it in mybatis-config.xml:

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

Configure cache behavior per mapper:

<cache eviction="FIFO" flushInterval="60000" size="512" readOnly="true" />

Cache lookup order: Level 2 → Level 1 → Database. Closing a session moves data from Level 1 to Level 2.

Integrating EhCache for Advanced Caching

Replace MyBatis’ default cache with EhCache for enhanced performance and persistence.

Add dependencies:

<dependency>
    <groupId>net.sf.ehcache</groupId>
    <artifactId>ehcache-core</artifactId>
    <version>2.6.11</version>
</dependency>
<dependency>
    <groupId>org.mybatis.caches</groupId>
    <artifactId>mybatis-ehcache</artifactId>
    <version>1.2.1</version>
</dependency>

Create ehcache.xml:

<ehcache>
    <diskStore path="java.io.tmpdir/Tmp_EhCache" />
    <defaultCache eternal="false" maxElementsInMemory="10000" timeToLiveSeconds="259200" memoryStoreEvictionPolicy="LRU" />
</ehcache>

Use in mapper:

<cache type="org.mybatis.caches.ehcache.EhcacheCache" />

EhCache implements MyBatis’ Cache interface, allowing seamless integration. It supports disk persistence, TTL, and various eviction policies.