MyBatis 批量插入 Oracle 實戰：union all 寫法完整 Demo

MyBatis 批量插入 Oracle 實戰

背景與挑戰

專案需一次性導入上萬筆資料至 Oracle，採用批量插入提升效能。Oracle 不支援 MySQL 的 VALUES (...), (...), ... 語法，需使用 INSERT INTO ... SELECT ... FROM DUAL UNION ALL SELECT ... FROM DUAL 形式。

完整實作程式碼

1. 實體類 AccountInfo.java

public class AccountInfo {
    private Long id;
    private String userName;
    private String password;
    private String gender;
    private String email;
    private Date createDate;
    // Getter/Setter/toString 省略
}

2. Mapper 介面 AccountInfoMapper.java

public interface AccountInfoMapper {
    List<AccountInfo> queryAllAccountInfo();
    int batchInsertAccountInfo(List<AccountInfo> accountInfoList);
}

3. MyBatis 總配置 mybatis-configuration.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:@localhost:1521:orcl"/>
        <property name="username" value="xxx"/>
        <property name="password" value="xxx"/>
      </dataSource>
    </environment>
  </environments>
  <mappers>
    <mapper resource="config/AccountInfoMapper.xml"/>
  </mappers>
</configuration>

4. 映射檔 AccountInfoMapper.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.oracle.mapper.AccountInfoMapper">

  <resultMap id="BaseResultMap" type="com.oracle.entity.AccountInfo">
    <id column="ID" property="id" jdbcType="DECIMAL"/>
    <result column="USERNAME" property="userName" jdbcType="VARCHAR"/>
    <result column="PASSWORD" property="password" jdbcType="VARCHAR"/>
    <result column="GENDER" property="gender" jdbcType="CHAR"/>
    <result column="EMAIL" property="email" jdbcType="VARCHAR"/>
    <result column="CREATE_DATE" property="createDate" jdbcType="DATE"/>
  </resultMap>

  <select id="queryAllAccountInfo" resultMap="BaseResultMap">
    SELECT ID, USERNAME, PASSWORD, GENDER, EMAIL, CREATE_DATE FROM ACCOUNT_INFO
  </select>

  <!-- 批量插入：Oracle 專用 union all 寫法 -->
  <insert id="batchInsertAccountInfo" parameterType="java.util.List">
    INSERT INTO ACCOUNT_INFO (ID, USERNAME, PASSWORD, GENDER, EMAIL, CREATE_DATE)
    (
      <foreach collection="list" item="accountInfo" separator="UNION ALL">
        SELECT
          #{accountInfo.id},
          #{accountInfo.userName},
          #{accountInfo.password},
          #{accountInfo.gender},
          #{accountInfo.email},
          #{accountInfo.createDate}
        FROM DUAL
      </foreach>
    )
  </insert>
</mapper>

關鍵點：

• parameterType="java.util.List" 對應介面方法的 List 參數。
• collection="list"：單參數為 List 時固定值；若為陣列用 array；多參數需封裝 Map。
• separator="UNION ALL"：各 SELECT 之間以 UNION ALL 連接。
• 每個 SELECT 必須 FROM DUAL (Oracle 虛擬表)。

5. 測試類 MybatisTest.java

public class MybatisTest {
    public static void main(String[] args) throws Exception {
        String resource = "config/mybatis-configuration.xml";
        InputStream is = Resources.getResourceAsStream(resource);
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
        SqlSession session = factory.openSession();
        AccountInfoMapper mapper = session.getMapper(AccountInfoMapper.class);

        // 查詢驗證
        List<AccountInfo> list = mapper.queryAllAccountInfo();
        list.forEach(System.out::println);

        // 批量插入
        mapper.batchInsertAccountInfo(generateData());
        session.commit(); // 別忘了提交事務！
    }

    static List<AccountInfo> generateData() {
        List<AccountInfo> result = new ArrayList<>();
        // 構造 3 筆測試資料...
        return result;
    }
}

效能與注意事項

• 單次批量建議控制在 500~1000 筆，避免 SQL 過長超過 Oracle 解析限制或佔用過多 PGA 記憶體。
• 大量資料分批提交，每批提交事務。
• 確保 ID 唯一 (序列或應用層生成)，避免主鍵衝突。
• 完整 Eclipse 專案下載：CSDN 下載。