After wasting few hours finally got it working through jdbc, for some reason if you give the parameters to insert directly it won’t work. This seems to be the standard way and it works.
|
1 2 3 4 5 6 7 |
private final String query= "MERGE INTO TABLE1 AS t1 "+ "USING (VALUES(?,?,?,?)) AS t2(ID, COL1, COL2, COL3) ON (t1.ID =t2.ID) "+ "WHEN MATCHED THEN UPDATE SET t1.COL1 = t2.COL1, t1.COL2 = t2.COL2, t1.COL3 = t2.COL3 "+ "WHEN NOT MATCHED THEN INSERT (ID, COL1, COL2, COL3) VALUES (DEFAULT, t2.COL1, t2.COL2, t2.COL13)"; |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
PreparedStatement preparedStatement; try { preparedStatement = connection.prepareStatement(query); for(Object object: list){ preparedStatement.setInt(1, object.getId()); preparedStatement.setDouble(2, object.getCol1()); preparedStatement.setInt(3, object.getCol2()); preparedStatement.setInt(4, object.getCol3()); preparedStatement.addBatch(); if(list.indexOf(object) % 500 == 0){ // BATCH SIZE preparedStatement.executeBatch(); } } preparedStatement.executeBatch(); connection.commit(); } catch (SQLException e) { e.printStackTrace(); } |