MyBatis 入門教程(二)：CRUD 操作完整實現指南

MyBatis 入門教程(二)：Users 表 CRUD 操作完整實現

承接上篇 MyBatis 快速入門，本文詳細講解如何使用 MyBatis 對 users 表執行 Create、Retrieve、Update、Delete 完整操作，涵蓋 XML 映射與註解兩種實現方式。

環境準備

• 資料表：users(id, name, age)
• 實體類：me.gacl.domain.User (id, name, age + getter/setter)
• 工具類：MyBatisUtil 提供 SqlSessionFactory 與 SqlSession
• MyBatis 核心配置：conf.xml

方式一：基於 XML 映射實現 CRUD

1. SQL 映射文件 - userMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" 
  "https://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="me.gacl.mapping.userMapper">
    <!-- 查詢單個用戶 -->
    <select id="getUser" parameterType="int" resultType="me.gacl.domain.User">
        SELECT * FROM users WHERE id = #{id}
    </select>

    <!-- 新增用戶 (Create) -->
    <insert id="addUser" parameterType="me.gacl.domain.User">
        INSERT INTO users(name, age) VALUES(#{name}, #{age})
    </insert>

    <!-- 刪除用戶 (Delete) -->
    <delete id="deleteUser" parameterType="int">
        DELETE FROM users WHERE id = #{id}
    </delete>

    <!-- 更新用戶 (Update) -->
    <update id="updateUser" parameterType="me.gacl.domain.User">
        UPDATE users SET name = #{name}, age = #{age} WHERE id = #{id}
    </update>

    <!-- 查詢所有用戶 -->
    <select id="getAllUsers" resultType="me.gacl.domain.User">
        SELECT * FROM users
    </select>
</mapper>

2. 單元測試類 - TestCRUDByXmlMapper.java

package me.gacl.test;

import me.gacl.domain.User;
import me.gacl.util.MyBatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.List;

public class TestCRUDByXmlMapper {
    
    @Test
    public void testAdd() {
        // true = 自動提交事務
        SqlSession sqlSession = MyBatisUtil.getSqlSession(true);
        String statement = "me.gacl.mapping.userMapper.addUser";
        
        User user = new User();
        user.setName("用戶孤獨蒼狼");
        user.setAge(25);
        
        int result = sqlSession.insert(statement, user);
        sqlSession.close();
        System.out.println("新增結果: " + result); // 返回影響行數
    }

    @Test
    public void testUpdate() {
        SqlSession sqlSession = MyBatisUtil.getSqlSession(true);
        String statement = "me.gacl.mapping.userMapper.updateUser";
        
        User user = new User();
        user.setId(1);
        user.setName("孤獨蒼狼");
        user.setAge(26);
        
        int result = sqlSession.update(statement, user);
        sqlSession.close();
        System.out.println("更新結果: " + result);
    }

    @Test
    public void testDelete() {
        SqlSession sqlSession = MyBatisUtil.getSqlSession(true);
        String statement = "me.gacl.mapping.userMapper.deleteUser";
        
        int result = sqlSession.delete(statement, 1);
        sqlSession.close();
        System.out.println("刪除結果: " + result);
    }

    @Test
    public void testGetAll() {
        SqlSession sqlSession = MyBatisUtil.getSqlSession();
        String statement = "me.gacl.mapping.userMapper.getAllUsers";
        
        List<User> users = sqlSession.selectList(statement);
        sqlSession.close();
        System.out.println("查詢全部: " + users);
    }
}

方式二：基於註解實現 CRUD

1. 映射介面 - UserMapperI.java

package me.gacl.mapping;

import me.gacl.domain.User;
import org.apache.ibatis.annotations.*;
import java.util.List;

public interface UserMapperI {
    @Insert("INSERT INTO users(name, age) VALUES(#{name}, #{age})")
    int add(User user);

    @Delete("DELETE FROM users WHERE id = #{id}")
    int deleteById(int id);

    @Update("UPDATE users SET name = #{name}, age = #{age} WHERE id = #{id}")
    int update(User user);

    @Select("SELECT * FROM users WHERE id = #{id}")
    User getById(int id);

    @Select("SELECT * FROM users")
    List<User> getAll();
}

2. 註冊映射介面 - conf.xml

<mappers>
    <mapper resource="me/gacl/mapping/userMapper.xml"/>
    <mapper class="me.gacl.mapping.UserMapperI"/>
</mappers>

3. 單元測試類 - TestCRUDByAnnotationMapper.java

package me.gacl.test;

import me.gacl.domain.User;
import me.gacl.mapping.UserMapperI;
import me.gacl.util.MyBatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.List;

public class TestCRUDByAnnotationMapper {
    
    @Test
    public void testAdd() {
        SqlSession sqlSession = MyBatisUtil.getSqlSession(true);
        UserMapperI mapper = sqlSession.getMapper(UserMapperI.class);
        
        User user = new User();
        user.setName("用戶xdp");
        user.setAge(20);
        
        int result = mapper.add(user);
        sqlSession.close();
        System.out.println("註解新增: " + result);
    }

    @Test
    public void testUpdate() {
        SqlSession sqlSession = MyBatisUtil.getSqlSession(true);
        UserMapperI mapper = sqlSession.getMapper(UserMapperI.class);
        
        User user = new User();
        user.setId(1);
        user.setName("孤獨蒼狼_xdp");
        user.setAge(30);
        
        int result = mapper.update(user);
        sqlSession.close();
        System.out.println("註解更新: " + result);
    }

    @Test
    public void testGetAll() {
        SqlSession sqlSession = MyBatisUtil.getSqlSession();
        UserMapperI mapper = sqlSession.getMapper(UserMapperI.class);
        
        List<User> users = mapper.getAll();
        sqlSession.close();
        System.out.println("註解查詢全部: " + users);
    }
}

核心工具類 - MyBatisUtil.java

package me.gacl.util;

import org.apache.ibatis.session.*;
import java.io.InputStream;

public class MyBatisUtil {
    public static SqlSessionFactory getSqlSessionFactory() {
        String resource = "conf.xml";
        InputStream is = MyBatisUtil.class.getClassLoader().getResourceAsStream(resource);
        return new SqlSessionFactoryBuilder().build(is);
    }
    
    public static SqlSession getSqlSession() {
        return getSqlSessionFactory().openSession();
    }
    
    public static SqlSession getSqlSession(boolean autoCommit) {
        return getSqlSessionFactory().openSession(autoCommit);
    }
}

關鍵概念解析

1. namespace 與 id 組合定位 SQL

me.gacl.mapping.userMapper.addUser = namespace + "." + insert id，唯一定位一條 SQL。

2. 參數綁定 #{ } vs ${ }

• #{id}：預編譯參數 (PreparedStatement)，防 SQL 注入，推薦
• ${id}：字串直接替換，僅用於動態表名/欄位名等無法預編譯場景

3. 結果映射 resultType

• 簡單映射：resultType="User" 自動匹配屬性名與欄位名
• 複雜映射：使用 <resultMap> 處理關聯查詢、嵌套結果

4. 事務控制

• openSession(true)：自動提交，適合單條增刪改
• openSession(false)：手動提交，sqlSession.commit()，適合多步事務

XML vs 註解選擇建議

常見錯誤與排查

• BindingException：namespace 拼寫錯誤、mapper 未註冊、方法名與 id 不匹配
• TypeException：parameterType/resultType 類型不匹配
• 資料未提交：忘記 autoCommit=true 或手動 commit()
• 連線洩漏：未在 finally 中 sqlSession.close()

總結

本文展示了 MyBatis 實現 CRUD 的兩種主流方式：XML 映射適合複雜 SQL 與團隊協作，註解映射適合簡單 CRUD 與開發效率。掌握 SqlSession 的獲取、SQL 定位、參數綁定、結果映射、事務控制五大核心環節，即可熟練使用 MyBatis 進行資料庫操作。