Description:
When I use executeBatch() to insert data, I found a strange phenomenon: if I add some SQL statements to the batch statement that may generate 'Duplicate entry' errors, it causes the insert to fail.
For example, in the test case I provided, I expected to see three records: '2006-04-01', '2010-10-02' and '2019-04-11', but instead I only got '2010-10-02'.
How to repeat:
@Test
public void test() throws SQLException {
String url = "jdbc:mysql://localhost:3306?user=user&password=password&rewriteBatchedStatements=true";
Connection con = DriverManager.getConnection(url);
execute(con, "DROP DATABASE IF EXISTS test");
execute(con, "CREATE DATABASE test");
execute(con, "USE test");
execute(con, "CREATE TABLE t0(c0 DATE UNIQUE PRIMARY KEY NOT NULL) engine=InnoDB");
batchExecute(con, new String[]{"INSERT INTO t0 VALUES ('2010-10-02')"});
batchExecute(con, new String[]{"INSERT INTO t0 VALUES ('2010-10-02')", "INSERT INTO t0 VALUES ('2006-04-01')",
"INSERT INTO t0 VALUES ('2019-04-11')", "INSERT INTO t0 VALUES ('2006-04-01')", "INSERT INTO t0 VALUES ('2019-04-11')"});
executeAndPrint(con, "SELECT * FROM t0");
}
public void execute(Connection con, String sql) {
try {
Statement statement = con.createStatement();
statement.execute(sql);
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
public void executeAndPrint(Connection con, String sql) {
try {
Statement statement = con.createStatement();
if (statement.execute(sql)) {
ResultSet rs = statement.getResultSet();
ResultSetMetaData rsMetaData = rs.getMetaData();
int count = rsMetaData.getColumnCount();
StringBuffer sb = new StringBuffer();
while (rs.next()) {
sb.setLength(0);
for (int i = 1; i <= count; i++) {
sb.append("* " + rs.getString(i) + " *");
}
System.out.println(sb);
}
}
else {
System.out.println("count: " + statement.getUpdateCount());
}
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
public void batchExecute(Connection con, String[] sql) {
try {
Statement statement = con.createStatement();
for (String s : sql) {
statement.addBatch(s);
}
statement.executeBatch();
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
Description: When I use executeBatch() to insert data, I found a strange phenomenon: if I add some SQL statements to the batch statement that may generate 'Duplicate entry' errors, it causes the insert to fail. For example, in the test case I provided, I expected to see three records: '2006-04-01', '2010-10-02' and '2019-04-11', but instead I only got '2010-10-02'. How to repeat: @Test public void test() throws SQLException { String url = "jdbc:mysql://localhost:3306?user=user&password=password&rewriteBatchedStatements=true"; Connection con = DriverManager.getConnection(url); execute(con, "DROP DATABASE IF EXISTS test"); execute(con, "CREATE DATABASE test"); execute(con, "USE test"); execute(con, "CREATE TABLE t0(c0 DATE UNIQUE PRIMARY KEY NOT NULL) engine=InnoDB"); batchExecute(con, new String[]{"INSERT INTO t0 VALUES ('2010-10-02')"}); batchExecute(con, new String[]{"INSERT INTO t0 VALUES ('2010-10-02')", "INSERT INTO t0 VALUES ('2006-04-01')", "INSERT INTO t0 VALUES ('2019-04-11')", "INSERT INTO t0 VALUES ('2006-04-01')", "INSERT INTO t0 VALUES ('2019-04-11')"}); executeAndPrint(con, "SELECT * FROM t0"); } public void execute(Connection con, String sql) { try { Statement statement = con.createStatement(); statement.execute(sql); statement.close(); } catch (SQLException e) { e.printStackTrace(); } } public void executeAndPrint(Connection con, String sql) { try { Statement statement = con.createStatement(); if (statement.execute(sql)) { ResultSet rs = statement.getResultSet(); ResultSetMetaData rsMetaData = rs.getMetaData(); int count = rsMetaData.getColumnCount(); StringBuffer sb = new StringBuffer(); while (rs.next()) { sb.setLength(0); for (int i = 1; i <= count; i++) { sb.append("* " + rs.getString(i) + " *"); } System.out.println(sb); } } else { System.out.println("count: " + statement.getUpdateCount()); } statement.close(); } catch (SQLException e) { e.printStackTrace(); } } public void batchExecute(Connection con, String[] sql) { try { Statement statement = con.createStatement(); for (String s : sql) { statement.addBatch(s); } statement.executeBatch(); statement.close(); } catch (SQLException e) { e.printStackTrace(); } }