【Jdbc】给已存在记录的表使用BatchUpdate插入重复记录会发生什么

【结果】

主键冲突，程序爆：

java.sql.BatchUpdateException: ORA-00001: 违反唯一约束条件 (LUNA.SYS_C0012021)

【潜在危险】

如果目的表有主键，插入重复记录会导致同批次的其它记录受异常影响。

如果目的表没有主键，则重复记录需要预处理。

【代码】

测试类：

package com.hy.lab.batchadd;

import java.sql.Connection;
import java.sql.PreparedStatement;

public class Adder {
    public static void main(String[] args){
        final String insertSql="insert into emp626(id,name) values(?,?)";

        try(Connection conn=DbUtil.getConn();
            PreparedStatement pstmt=conn.prepareStatement(insertSql)){
            conn.setAutoCommit(false);

            final String[] arr={"Andy","Bill","Cindy","Douglas","Eliot"};

            for(int i=0;i<arr.length;i++){
                pstmt.setInt(1,i);

                String name=arr[i];
                pstmt.setString(2,name);

                pstmt.addBatch();
            }

            pstmt.executeBatch();
            conn.commit();
            pstmt.clearBatch();
        }catch(Exception e){
            e.printStackTrace();
        }
    }
}

 

提供Conn的工具类：

package com.hy.lab.batchadd;

import java.sql.Connection;
import java.sql.DriverManager;

public class DbUtil {
    //-- 以下为连接Oracle数据库的四大参数
    private static final String DRIVER = "oracle.jdbc.OracleDriver";
    private static final String URL = "jdbc:oracle:thin:@127.0.0.1:1521/orcl";
    private static final String USER = "luna";
    private static final String PSWD = "1234";

    public static Connection getConn() throws Exception{
        Class.forName(DRIVER);
        Connection conn = DriverManager.getConnection(URL, USER, PSWD);
        return conn;
    }
}

 

【执行情况】

首次空表插记录没问题，次回再执行爆异常：

java.sql.BatchUpdateException: ORA-00001: 违反唯一约束条件 (LUNA.SYS_C0012021)

    at oracle.jdbc.driver.OraclePreparedStatement.generateBatchUpdateException(OraclePreparedStatement.java:10323)
    at oracle.jdbc.driver.OraclePreparedStatement.executeBatchWithoutQueue(OraclePreparedStatement.java:10090)
    at oracle.jdbc.driver.OraclePreparedStatement.executeLargeBatch(OraclePreparedStatement.java:9975)
    at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:9932)
    at oracle.jdbc.driver.OracleStatementWrapper.executeBatch(OracleStatementWrapper.java:262)
    at com.hy.lab.batchadd.Adder.main(Adder.java:25)

 

【建表语句】

create table emp626(
    id number(10),
    name nvarchar2(20),
    primary key(id)
);

 

END