MyBatis CRUD 完整實戰：從建表到 Mapper 封裝與事務控制

1. 數據庫建表

CREATE TABLE students (
    stud_id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    dob DATE
);

2. 實體類 Student（POJO）

@Data @NoArgsConstructor @AllArgsConstructor
public class Student {
    private Integer studId;
    private String name;
    private String email;
    private Date dob;
}

3. 核心配置文件

mybatis-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
  "https://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
  <typeAliases>
    <typeAlias type="com.mybatis.pojo.Student" alias="Student"/>
  </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/test?useSSL=false&serverTimezone=UTC"/>
        <property name="username" value="root"/>
        <property name="password" value="123456"/>
      </dataSource>
    </environment>
  </environments>
  <mappers>
    <mapper resource="com/mybatis/mappers/StudentMapper.xml"/>
  </mappers>
</configuration>

StudentMapper.xml（含 resultMap 與動態 SQL）

<?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="com.mybatis.mappers.StudentMapper">
  <resultMap id="StudentResult" type="Student">
    <id property="studId" column="stud_id"/>
    <result property="name" column="name"/>
    <result property="email" column="email"/>
    <result property="dob" column="dob"/>
  </resultMap>

  <select id="findAll" resultMap="StudentResult">
    SELECT * FROM students
  </select>

  <select id="findById" parameterType="int" resultMap="StudentResult">
    SELECT * FROM students WHERE stud_id = #{id}
  </select>

  <insert id="insert" parameterType="Student" useGeneratedKeys="true" keyProperty="studId">
    INSERT INTO students (name, email, dob) VALUES (#{name}, #{email}, #{dob})
  </insert>

  <update id="update" parameterType="Student">
    UPDATE students SET name=#{name}, email=#{email}, dob=#{dob} WHERE stud_id=#{studId}
  </update>

  <delete id="deleteById" parameterType="int">
    DELETE FROM students WHERE stud_id = #{id}
  </delete>
</mapper>

4. Mapper 接口

public interface StudentMapper {
    List<Student> findAll();
    Student findById(Integer id);
    void insert(Student student);
    void update(Student student);
    void deleteById(Integer id);
}

5. SqlSessionFactory 封裝（線程安全單例）

public class MyBatisUtil {
    private static final SqlSessionFactory FACTORY;
    static {
        try (InputStream is = Resources.getResourceAsStream("mybatis-config.xml")) {
            FACTORY = new SqlSessionFactoryBuilder().build(is);
        } catch (IOException e) {
            throw new ExceptionInInitializerError(e);
        }
    }
    public static SqlSession openSession() { return FACTORY.openSession(); }
    public static SqlSession openSession(boolean autoCommit) { return FACTORY.openSession(autoCommit); }
}

6. 測試類（JUnit 5 + 事務手動控制）

@TestMethodOrder(OrderAnnotation.class)
class StudentMapperTest {

    private static SqlSession session;
    private static StudentMapper mapper;

    @BeforeAll
    static void init() {
        session = MyBatisUtil.openSession(); // 默認不自動提交
        mapper = session.getMapper(StudentMapper.class);
    }

    @AfterAll
    static void close() {
        if (session != null) session.close();
    }

    @Test @Order(1)
    void testInsert() {
        Student s = new Student(null, "張三", "zhang@test.com", Date.valueOf("2000-01-01"));
        mapper.insert(s);
        session.commit(); // 手動提交
        Assertions.assertNotNull(s.getStudId(), "主鍵回填失敗");
    }

    @Test @Order(2)
    void testFindById() {
        Student s = mapper.findById(1);
        Assertions.assertEquals("張三", s.getName());
    }

    @Test @Order(3)
    void testUpdate() {
        Student s = mapper.findById(1);
        s.setEmail("zhang_new@test.com");
        mapper.update(s);
        session.commit();
        Assertions.assertEquals("zhang_new@test.com", mapper.findById(1).getEmail());
    }

    @Test @Order(4)
    void testFindAll() {
        List<Student> list = mapper.findAll();
        Assertions.assertFalse(list.isEmpty());
        list.forEach(System.out::println);
    }

    @Test @Order(5)
    void testDelete() {
        mapper.deleteById(1);
        session.commit();
        Assertions.assertNull(mapper.findById(1));
    }
}

7. 進階提示

• 分頁查詢：引入 PageHelper 插件，Service 層 PageHelper.startPage(pageNum, pageSize) 即可自動拼接 LIMIT。
• 動態 SQL：<where>、<if>、<choose>、<trim> 解決條件組合。
• 批量插入：<foreach collection="list" item="s" separator=",">(#{s.name},#{s.email},#{s.dob})</foreach>。
• 結果映射：複雜關聯查詢用 <association>（一對一）、<collection>（一對多）。
• 事務管理：Spring 整合時交給 @Transactional，手動模式務必 try { commit } catch { rollback } finally { close }。