MyBatis 3.x 手動配置 CRUD 完整實戰指南

MyEclipse 不提供 MyBatis 代碼自動生成，本文提供完整的手動配置流程，涵蓋核心配置檔、SqlSessionFactory 封裝、Mapper XML 編寫、Service 層整合與分頁查詢實現。

1. 核心配置檔 mybatis-conf.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>
  <environments default="development">
    <environment id="development">
      <transactionManager type="JDBC"/>
      <dataSource type="POOLED">
        <property name="driver" value="oracle.jdbc.driver.OracleDriver"/>
        <property name="url" value="jdbc:oracle:thin:@192.168.2.55:ORCL"/>
        <property name="username" value="ysk"/>
        <property name="password" value="123"/>
      </dataSource>
    </environment>
  </environments>
  <mappers>
    <!-- 映射 DAO 介面而非 POJO -->
    <mapper resource="com/kane/dao/NewsDAOImpl.xml"/>
  </mappers>
</configuration>

2. SqlSessionFactory 單例與 ThreadLocal 連線管理

package com.kane.dbc;

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

public class MyBATISSqlSessionFactory {
    private static final String CONFIG_FILE = "mybatis-conf.xml";
    private static final ThreadLocal<SqlSession> threadLocal = new ThreadLocal<>();
    private static SqlSessionFactory sqlSessionFactory;

    static {
        try (InputStream is = Resources.getResourceAsStream(CONFIG_FILE)) {
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
        } catch (Exception e) {
            throw new RuntimeException("MyBatis 初始化失敗", e);
        }
    }

    public static SqlSession getSession() {
        SqlSession session = threadLocal.get();
        if (session == null) {
            session = sqlSessionFactory.openSession();
            threadLocal.set(session);
        }
        return session;
    }

    public static void closeSession() {
        SqlSession session = threadLocal.get();
        threadLocal.remove();
        if (session != null) session.close();
    }
}

3. Mapper XML：NewsDAOImpl.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="com.kane.dao.INewsDAO">
  <insert id="doCreate" parameterType="News">
    INSERT INTO NEWS(news_id, news_title, image_id, news_content, news_time)
    VALUES (sys_guid(), #{news_title}, #{image_id}, #{news_content, jdbcType=VARCHAR}, #{news_time, jdbcType=DATE})
  </insert>

  <delete id="doRemove" parameterType="java.lang.Integer">
    DELETE FROM News WHERE news_id = #{id}
  </delete>

  <update id="doUpdate" parameterType="News">
    UPDATE News SET news_title=#{news_title}, image_id=#{image_id},
      news_content=#{news_content}, news_time=#{news_time}
    WHERE news_id=#{news_id}
  </update>

  <select id="findAll" resultType="News">
    SELECT news_id, news_title, image_id, news_content, news_time FROM News
  </select>

  <select id="findById" resultType="News">
    SELECT news_id, news_title, image_id, news_content, news_time FROM News WHERE news_id = #{id}
  </select>

  <!-- Oracle 分頁查詢 -->
  <select id="findAllSplit" resultType="News" parameterType="java.util.Map">
    SELECT * FROM (
      SELECT news_id, news_title, image_id, news_content, news_time, ROWNUM rn
      FROM News WHERE ${column} LIKE #{keyword} AND ROWNUM <= #{endNum}
    ) temp WHERE temp.rn > #{startNum}
  </select>

  <select id="getAllCount" resultType="java.lang.Integer" parameterType="java.util.Map">
    SELECT COUNT(*) FROM News WHERE ${column} LIKE #{keyword}
  </select>
</mapper>

關鍵點：jdbcType 解決 Oracle 空值插入問題；${column} 直接拼接 SQL（防注入需自行驗證）；分頁採用 ROWNUM 雙層嵌套。

4. Service 實現：NewsServiceImpl

package com.kane.service.impl;

import java.util.*;
import com.kane.dao.INewsDAO;
import com.kane.dbc.MyBATISSqlSessionFactory;
import com.kane.service.INewsService;
import com.kane.vo.News;

public class NewsServiceImpl implements INewsService {
    public List<News> findAll() {
        try {
            return getSession().getMapper(INewsDAO.class).findAll();
        } finally { closeSession(); }
    }

    public void insert(News news) throws Exception {
        SqlSession session = getSession();
        try {
            session.getMapper(INewsDAO.class).doCreate(news);
            session.commit();
        } catch (Exception e) {
            session.rollback();
            throw e;
        } finally { closeSession(); }
    }

    public Map<String, Object> list(int pageNo, int pageSize, String column, String keyword) {
        Map<String, Object> result = new HashMap<>();
        Map<String, Object> params = new HashMap<>();
        params.put("column", column);
        params.put("keyword", "%" + keyword + "%");
        params.put("endNum", pageNo * pageSize);
        params.put("startNum", (pageNo - 1) * pageSize);
        try {
            SqlSession session = getSession();
            result.put("allNews", session.getMapper(INewsDAO.class).findAllSplit(params));
            result.put("count", session.getMapper(INewsDAO.class).getAllCount(params));
        } finally { closeSession(); }
        return result;
    }
    // remove、update、findById 同理...
}

5. JUnit 測試

@Test
public void testList() throws Exception {
    Map<String, Object> map = ServiceFactory.getNewsServiceInstance().list(1, 5, "news_title", "12");
    System.out.println(map);
}

最佳實踐提醒：生產環境建議使用連線池（如 HikariCP）替換 POOLED，並啟用 MyBatis 二級快取；DAO 介面採用 Mapper 動態代理而非手寫 Impl 類別。