MyBatis 入門實戰（二）：XML 與註解映射、Mapper 接口、動態 SQL 進階

快速回顧：核心組件

• SqlSessionFactoryBuilder → 讀取設定 → 建立 SqlSessionFactory（單例，線程安全）
• SqlSessionFactory.openSession() → 取得 SqlSession（非線程安全，請求級）
• SqlSession.getMapper(Mapper.class) → 取得 Mapper 代理物件，執行型別安全的資料庫操作

1. 專案結構與依賴

src/
├── main/
│   ├── java/
│   │   └── com/example/
│   │       ├── entity/User.java
│   │       ├── mapper/UserMapper.java
│   │       └── MyBatisTest.java
│   └── resources/
│       ├── mybatis-config.xml
│       └── mapper/UserMapper.xml
pom.xml: mybatis-3.5.x, mysql-connector-java, junit-jupiter

2. mybatis-config.xml 完整配置

<?xml version="1.0" encoding="UTF-8"?>
<configuration>
    <settings>
        <setting name="mapUnderscoreToCamelCase" value="true"/> <!-- 自動駝峰映射 -->
        <setting name="logImpl" value="SLF4J"/>
    </settings>

    <typeAliases>
        <package name="com.example.entity"/> <!-- 別名掃描 -->
    </typeAliases>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis_demo?useSSL=false&serverTimezone=UTC"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <package name="com.example.mapper"/> <!-- 掃描 Mapper 接口，自動關聯同名 XML -->
    </mappers>
</configuration>

3. 實體類

@Data @NoArgsConstructor @AllArgsConstructor
public class User {
    private Integer id;
    private String username;
    private Integer age;
    private LocalDateTime createTime;
}

4. Mapper 接口 + XML 映射（推薦模式）

UserMapper.java

public interface UserMapper {
    User selectById(@Param("id") int id);
    List<User> selectAll();
    int insert(User user);
    int update(User user);
    int deleteById(int id);
}

UserMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<mapper namespace="com.example.mapper.UserMapper">
    <resultMap id="userMap" type="User">
        <id property="id" column="f_id"/>
        <result property="username" column="f_username"/>
        <result property="age" column="f_age"/>
        <result property="createTime" column="f_create_time"/>
    </resultMap>

    <sql id="cols">f_id, f_username, f_age, f_create_time</sql>

    <select id="selectById" resultMap="userMap">
        SELECT <include refid="cols"/> FROM t_user_main WHERE f_id = #{id}
    </select>

    <select id="selectAll" resultMap="userMap">
        SELECT <include refid="cols"/> FROM t_user_main
    </select>

    <insert id="insert" useGeneratedKeys="true" keyProperty="id" parameterType="User">
        INSERT INTO t_user_main (f_username, f_age, f_create_time)
        VALUES (#{username}, #{age}, NOW())
    </insert>

    <update id="update" parameterType="User">
        UPDATE t_user_main SET
        f_username = #{username},
        f_age = #{age}
        WHERE f_id = #{id}
    </update>

    <delete id="deleteById">
        DELETE FROM t_user_main WHERE f_id = #{id}
    </delete>
</mapper>

5. 純註解映射（無 XML）

public interface UserMapper {
    @Select("SELECT f_id id, f_username username, f_age age, f_create_time createTime FROM t_user_main WHERE f_id = #{id}")
    @Results(id = "userMap", value = {
        @Result(property = "id", column = "id", id = true),
        @Result(property = "username", column = "username"),
        @Result(property = "age", column = "age"),
        @Result(property = "createTime", column = "createTime")
    })
    User selectById(int id);

    @Insert("INSERT INTO t_user_main (f_username, f_age, f_create_time) VALUES (#{username}, #{age}, NOW())")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insert(User user);
    // 其他方法類似...
}

選型建議：簡單查詢用註解；複雜動態 SQL、結果映射、多表關聯請用 XML，可維護性更高。

6. 動態 SQL 精要

<select id="selectByCondition" resultMap="userMap">
    SELECT <include refid="cols"/> FROM t_user_main
    <where>
        <if test="username != null and username != ''">
            AND f_username LIKE CONCAT('%', #{username}, '%')
        </if>
        <if test="age != null">
            AND f_age = #{age}
        </if>
    </where>
    <if test="orderBy != null">
        ORDER BY ${orderBy} <!-- ${} 直接拼接，需防注入 -->
    </if>
</select>

<!-- 批次插入 -->
<insert id="batchInsert" parameterType="java.util.List">
    INSERT INTO t_user_main (f_username, f_age, f_create_time) VALUES
    <foreach collection="list" item="user" separator=",">
        (#{user.username}, #{user.age}, NOW())
    </foreach>
</insert>

7. 測試驗證

@Test
void testCRUD() throws IOException {
    try (InputStream is = Resources.getResourceAsStream("mybatis-config.xml")) {
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
        try (SqlSession session = factory.openSession()) {
            UserMapper mapper = session.getMapper(UserMapper.class);

            // Insert
            User u = new User(null, "測試用戶", 25, null);
            assertEquals(1, mapper.insert(u));
            assertNotNull(u.getId()); // 回寫自增 ID

            // Select
            User found = mapper.selectById(u.getId());
            assertEquals("測試用戶", found.getUsername());

            // Update
            found.setAge(26);
            assertEquals(1, mapper.update(found));

            // Delete
            assertEquals(1, mapper.deleteById(u.getId()));
            assertNull(mapper.selectById(u.getId()));

            session.commit(); // 若非自動提交
        }
    }
}

8. 進階主題預告

• 關聯映射：<association>、<collection>、嵌套查詢 vs 嵌套結果。
• 緩存：一級緩存、二級緩存、<cache>、@CacheNamespace。
• 插件/攔截器：分頁、租戶隔離、SQL 審計。
• Spring 整合：MapperScannerConfigurer、事務管理、@MapperScan。