Skip to content

Instantly share code, notes, and snippets.

@matriv
Last active September 28, 2017 10:54
Show Gist options
  • Save matriv/0ca76afceb35c419ac8c57d741539e88 to your computer and use it in GitHub Desktop.
Save matriv/0ca76afceb35c419ac8c57d741539e88 to your computer and use it in GitHub Desktop.
import java.sql.BatchUpdateException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.Properties;
public class JdbcTest {
private static Connection conn;
public static void main(String[] args) throws SQLException {
String url = "crate://localhost:5434/?user=crate";
try {
conn = DriverManager.getConnection(url, new Properties());
} catch (SQLException e) {
throw new SQLException("cannot connect to the database", e);
}
conn.createStatement().executeUpdate("drop table if exists doc.t");
conn.createStatement().executeUpdate("create table doc.t(a integer primary key)");
// Duplicate value for primary key
PreparedStatement preparedStatement = conn.prepareStatement("insert into doc.t(a) values(?)");
for (int i = 1; i <= 10; i++) {
if (i <= 5) { // insert 5 valid values
preparedStatement.setInt(1, i);
} else { // try to insert duplicate primary key
preparedStatement.setInt(1, 1);
}
preparedStatement.addBatch();
}
int[] result = preparedStatement.executeBatch();
System.out.println(Arrays.toString(result));
/* Prints: [1, 1, 1, 1, 1, -3, -3, -3, -3, -3]
* which means the first 5 inserts inserted 1 row each and the last 5 failed
*/
// Exceptions thrown
preparedStatement = conn.prepareStatement("insert into doc.t(a) values(?)");
for (int i = 11; i <= 20; i++) {
if (i <= 5) { // insert 5 valid values
preparedStatement.setInt(1, i);
} else { // produceException
preparedStatement.setString(1, "wrongDataType");
}
preparedStatement.addBatch();
}
try {
preparedStatement.executeBatch();
} catch (BatchUpdateException e) {
System.out.println(Arrays.toString(e.getUpdateCounts()));
/* Prints: [-3, -3, -3, -3, -3, -3, -3, -3, -3, -3]
* which means no row was inserted
*/
}
conn.close();
}
}
@asicoe
Copy link

asicoe commented Sep 28, 2017

Thanks for the example @matriv.

Correct me if I am wrong but this would not really fully fix the problem when doing upserts from a parallel app with end to end at least once semantics, since you cannot get the fail reason for each individual insert of the batch. Instead you only get whether it failed or not. What if some inserts failed because of the duplicate primary key (like in your example) but some others, from the same batch, fail for another reason. In case you want to do upserts and not loose data we need to be able to distinguish between the two cases and retry only the failures for other reasons than primary key duplication.

A great fix for this issue for people wanting to do upserts with CrateDB would be to support ON CONFLICT DO NOTHING. There is an ongoing issue recently submitted along these lines.

Would be fantastic to get some traction on that feature :)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment