Hint

This article is generated by AI translation.

File Mapper

When SQL logic is complex or lengthy, the Programmatic API, Declarative API, or Fluent API all lead to reduced code readability. Placing SQL in a dedicated Mapper XML file is the best choice.

Highlights

• Separate SQL from code: manage SQL centrally via Mapper XML files — code only needs to call interface methods.
• Supports two dynamic SQL styles: Rule-based (recommended, more concise syntax) and XML tag-based (MyBatis-compatible style).
• Supports SQL fragment reuse via <sql> + <include> to avoid duplicate SQL.
• Supports multiple result mappings: resultType (Bean, Map, int, string, etc.) and resultMap (custom column-to-property mapping).
• Mapper files extend the Declarative API — you can mix annotation methods and XML methods.
• Execute via session.executeStatement / session.queryStatement / session.pageStatement.

Define Mapper File

classpath:/mapper/userMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//dbvisitor.net//DTD Mapper 1.0//EN"
        "https://www.dbvisitor.net/schema/dbvisitor-mapper.dtd">
<mapper namespace="com.example.UserMapper">
    <insert id="insertUser">
        insert into users (id, name, age) values (#{id}, #{name}, #{age})
    </insert>

    <select id="selectById" resultType="com.example.User">
        select * from users where id = #{id}
    </select>

    <select id="selectAll" resultType="com.example.User">
        select * from users order by id
    </select>

    <update id="updateEmail">
        update users set email = #{email} where id = #{id}
    </update>

    <delete id="deleteById">
        delete from users where id = #{id}
    </delete>
</mapper>

Dynamic SQL

Mapper files support dynamic SQL for conditional queries, dynamic updates, and more. dbVisitor provides two styles:

Conditional Query

Rules (Recommended)

Use @{and} rules — when a parameter is null, the condition is automatically skipped. No need to manually handle WHERE and AND connectors.

<select id="search" resultType="com.example.User">
    select * from users
        @{and, name = :name}
        @{and, age = :age}
        @{and, email = :email}
</select>

XML Tags

Use <where> + <if> tags. <where> automatically strips the leading AND/OR.

<select id="search" resultType="com.example.User">
    select * from users
    <where>
        <if test="name != null">AND name = #{name}</if>
        <if test="age != null">AND age = #{age}</if>
        <if test="email != null">AND email = #{email}</if>
    </where>
</select>

Collection IN Query

Rules (Recommended)

Use @{in} rules to automatically expand a collection parameter into IN (?, ?, ...).

<select id="selectByIds" resultType="com.example.User">
    select * from users
    where id in @{in, :ids}
</select>

XML Tags

Use <foreach> tag to iterate over a collection and generate an IN clause.

<select id="selectByIds" resultType="com.example.User">
    select * from users where id in
    <foreach collection="ids" item="id" open="(" separator="," close=")">
        #{id}
    </foreach>
</select>

Dynamic Update

Rules (Recommended)

Use @{set} rules — when a parameter is null, it is automatically skipped. Comma separators are managed automatically.

<update id="updateUser">
    update users
    set update_time = now()
        @{set, name = :name}
        @{set, email = :email}
    where id = :id
</update>

XML Tags

Use <set> + <if> tags. <set> automatically strips the trailing comma.

<update id="updateUser">
    update users
    <set>
        update_time = now(),
        <if test="name != null">name = #{name},</if>
        <if test="email != null">email = #{email},</if>
    </set>
    where id = #{id}
</update>

Learn more about dynamic SQL

• Statement Generation Rules — full list of rules including @{and}, @{or}, @{in}, @{set}, @{case}, @{macro}, etc.
• Dynamic SQL Tags — detailed usage of XML tags (<if>, <choose>, <where>, <set>, <foreach>, etc.).

Link to Interface

Map interface methods to XML statements via @RefMapper

@RefMapper("/mapper/userMapper.xml")
public interface UserMapper {
    int insertUser(User user);
    User selectById(@Param("id") int id);
    List<User> selectAll();
    int updateEmail(@Param("id") int id, @Param("email") String email);
    int deleteById(@Param("id") int id);
}

Create and Use

Load mapper and call via interface or Session

Configuration config = new Configuration();
Session session = config.newSession(dataSource);

// Option 1: Call via interface (recommended)
UserMapper mapper = session.createMapper(UserMapper.class);
User user = mapper.selectById(1);

// Option 2: Call via Session directly (using namespace.id)
List<User> list = session.queryStatement("com.example.UserMapper.selectAll", null);

For more details on File Mapper, see:

• Mapper File