MyBatis CRUD 完整實戰教學

MyBatis 增刪改查 (CRUD) 完整實戰指南

環境準備

• Maven 依賴：mybatis、mysql-connector-java、junit
• 數據庫表 users(id, name, age)

實體類

public class User {
    private int id;
    private String name;
    private int age;
    // getter/setter/toString 省略
}

工具類：SqlSession 管理

public class MyBatisUtil {
    private static SqlSessionFactory factory;

    static {
        String resource = "mybatis-config.xml";
        InputStream is = MyBatisUtil.class.getClassLoader().getResourceAsStream(resource);
        factory = new SqlSessionFactoryBuilder().build(is);
    }

    public static SqlSession getSqlSession() {
        return factory.openSession(); // 手動提交事務
    }

    public static SqlSession getSqlSession(boolean autoCommit) {
        return factory.openSession(autoCommit);
    }
}

方式一：XML 映射文件配置

1. 核心配置文件

<configuration>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/test"/>
                <property name="username" value="root"/>
                <property name="password" value="admin"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="com/example/mapper/UserMapper.xml"/>
    </mappers>
</configuration>

2. Mapper XML

<mapper namespace="com.example.mapper.UserMapper">
    <select id="getUser" parameterType="int" resultType="com.example.domain.User">
        SELECT * FROM users WHERE id = #{id}
    </select>

    <insert id="addUser" parameterType="com.example.domain.User">
        INSERT INTO users(name, age) VALUES(#{name}, #{age})
    </insert>

    <delete id="deleteUser" parameterType="int">
        DELETE FROM users WHERE id = #{id}
    </delete>

    <update id="updateUser" parameterType="com.example.domain.User">
        UPDATE users SET name=#{name}, age=#{age} WHERE id=#{id}
    </update>

    <select id="getAllUsers" resultType="com.example.domain.User">
        SELECT * FROM users
    </select>
</mapper>

3. 測試代碼

public class UserMapperTest {
    @Test
    public void testCRUD() {
        try (SqlSession session = MyBatisUtil.getSqlSession(true)) {
            String ns = "com.example.mapper.UserMapper.";
            
            // 新增
            User u = new User(); u.setName("張三"); u.setAge(25);
            session.insert(ns + "addUser", u);
            
            // 查詢單個
            User user = session.selectOne(ns + "getUser", u.getId());
            
            // 修改
            user.setName("李四"); user.setAge(26);
            session.update(ns + "updateUser", user);
            
            // 查詢所有
            List<User> list = session.selectList(ns + "getAllUsers");
            
            // 刪除
            session.delete(ns + "deleteUser", user.getId());
        }
    }
}

方式二：註解式開發

1. Mapper 介面

public interface UserMapper {
    @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. 註冊 Mapper

<mappers>
    <mapper class="com.example.mapper.UserMapper"/>
</mappers>

3. 測試代碼

@Test
public void testAnnotationCRUD() {
    try (SqlSession session = MyBatisUtil.getSqlSession(true)) {
        UserMapper mapper = session.getMapper(UserMapper.class);
        
        User u = new User(); u.setName("王五"); u.setAge(28);
        mapper.add(u);
        
        User user = mapper.getById(u.getId());
        user.setName("趙六"); user.setAge(29);
        mapper.update(user);
        
        List<User> list = mapper.getAll();
        mapper.deleteById(u.getId());
    }
}

兩種方式對比

最佳實踐建議

• 複雜查詢、動態 SQL 使用 XML
• 簡單單表 CRUD 使用註解
• 統一使用 try-with-resources 管理 SqlSession
• 生產環境配置連接池（HikariCP）替換內建 POOLED