Last active
September 28, 2017 10:54
-
-
Save matriv/0ca76afceb35c419ac8c57d741539e88 to your computer and use it in GitHub Desktop.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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(); | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
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 :)