MyBatis 實戰：Mapper 接口實現 MySQL 增刪改查（含關聯查詢、動態 SQL）

領域模型

public class User {
    private Integer id;
    private String name;
    private Integer age;
    private String address;
    // getter/setter/toString
}

public class Article {
    private Integer id;
    private User author;        // 多對一
    private String title;
    private String content;
    // getter/setter/toString
}

Mapper 接口定義

public interface UserMapper {
    // 單條查詢
    User selectById(Integer id);

    // 條件查詢列表
    List<User> selectByNameLike(String name);

    // 關聯查詢：用戶 + 其文章列表 (一對多)
    List<Article> selectArticlesByUserId(Integer userId);

    // 插入（回填自增 ID）
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insert(User user);

    // 更新（僅非空字段）
    int update(User user);

    // 刪除
    int deleteById(Integer id);
}

Mapper XML 完整映射

<mapper namespace="com.example.mapper.UserMapper">

    <resultMap id="userMap" type="User">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="age" column="age"/>
        <result property="address" column="address"/>
    </resultMap>

    <resultMap id="articleMap" type="Article">
        <id property="id" column="a_id"/>
        <result property="title" column="title"/>
        <result property="content" column="content"/>
        <association property="author" resultMap="userMap"/> <!-- 復用 userMap -->
    </resultMap>

    <sql id="userCols">id, name, age, address</sql>
    <sql id="articleCols">a.id AS a_id, a.title, a.content, a.user_id</sql>

    <select id="selectById" parameterType="int" resultMap="userMap">
        SELECT <include refid="userCols"/> FROM `user` WHERE id = #{id}
    </select>

    <select id="selectByNameLike" parameterType="string" resultMap="userMap">
        SELECT <include refid="userCols"/> FROM `user` WHERE name LIKE #{name}
    </select>

    <select id="selectArticlesByUserId" parameterType="int" resultMap="articleMap">
        SELECT <include refid="articleCols"/>, <include refid="userCols"/>
        FROM article a
        INNER JOIN `user` u ON a.user_id = u.id
        WHERE u.id = #{userId}
    </select>

    <insert id="insert" parameterType="User" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO `user`(name, age, address) VALUES(#{name}, #{age}, #{address})
    </insert>

    <update id="update" parameterType="User">
        UPDATE `user`
        <set>
            <if test="name != null">name = #{name},</if>
            <if test="age != null">age = #{age},</if>
            <if test="address != null">address = #{address},</if>
        </set>
        WHERE id = #{id}
    </update>

    <delete id="deleteById" parameterType="int">
        DELETE FROM `user` WHERE id = #{id}
    </delete>

</mapper>

測試用例

@SpringBootTest
class UserMapperTest {
    @Autowired UserMapper userMapper;

    @Test
    void crud() {
        // Insert
        User u = new User();
        u.setName("Lucy");
        u.setAge(18);
        u.setAddress("Happy Valley");
        userMapper.insert(u);
        System.out.println("新增 ID: " + u.getId()); // 回填

        // Select
        User byId = userMapper.selectById(u.getId());
        assertEquals("Lucy", byId.getName());

        // Update
        byId.setAddress("Sunny District");
        userMapper.update(byId);
        assertEquals("Sunny District", userMapper.selectById(u.getId()).getAddress());

        // List
        List<User> list = userMapper.selectByNameLike("%u%");
        list.forEach(System.out::println);

        // 關聯查詢
        List<Article> articles = userMapper.selectArticlesByUserId(1);
        articles.forEach(a -> System.out.println(a.getTitle() + " by " + a.getAuthor().getName()));

        // Delete
        userMapper.deleteById(u.getId());
        assertNull(userMapper.selectById(u.getId()));
    }
}

關鍵點總結

• resultMap 支援 association（多對一）、collection（一對多）、嵌套復用。
• <set> + <if> 實現動態更新，避免全字段覆蓋。
• useGeneratedKeys/keyProperty 獲取自增主鍵。
• <sql> 片段復用減少重複。
• 事務控制：服務層 @Transactional，單元測試可用 SqlSessionFactory.openSession(false) 手動 commit/rollback。

結語

掌握 Mapper 接口 + XML 映射 + 動態 SQL + 關聯查詢，即可高效完成 MyBatis 核心 CRUD 開發。實際專案中建議結合 PageHelper 分頁、通用 Mapper 抽象進一步提效。