I can't figure out whether I should use 2 separate queries or if just 1 is possible.
I need to UPDATE
a single row in table records
but then INSERT
50,000 rows into table runners
.
records
rc_id runner msg
1 bill 'hello'
runners
r_id runner km_run
1 mary 3.5
2 anna 1.5
3 john 6.5
4 bill 1.5
5 jess 6.2
6 jack 2.5
I could write a query like below, but it would perform the UPDATE
in every batch along with the INSERT
in my code.
Given my Java code, how do I adapt this query so that the UPDATE
is performed once, and the INSERT
50,000 times. If 2 queries is the standard approach, please let me know.
WITH r_updater AS
(UPDATE records
SET msg = 'goodbye'
WHERE runner = 'bill' )
INSERT INTO runners (runner, km_run)
VALUES ('george', 2.3) ON CONFLICT (runner, km_run) DO
UPDATE
SET runner=EXCLUDED.runner,
km_run=EXCLUDED.km_run;
Java Code:
Connection conn = null;
PreparedStatement stmt = null;
try {
conn = MyDBSource.getInstance().getConnection();
// This is the first of 2 queries I'm running. I want to combine them somehow
String updateQuery = "UPDATE records
SET msg = 'goodbye'
WHERE runner = 'bill';
stmt = conn.prepareStatement(updateQuery);
stmt.executeUpdate();
conn.setAutoCommit(false);
String insertQuery = "INSERT INTO runners (runner, km_run)
VALUES(?, ?)
ON CONFLICT(runner, km_run)
DO UPDATE
SET
runner = EXCLUDED.runner,
km_run = EXCLUDED.km_run;"
stmt = conn.prepareStatement(insertQuery);
int batchSize = 100;
int rows = 0;
for (RunnerModel runnerModel: runnerModelMap.entrySet()) {
int i = 0;
stmt.setString(++i, runnerModel.getRunner());
stmt.setString(++i, runnerModel.getKmRun());
stmt.addBatch();
if (++rows % batchSize == 0) {
stmt.executeBatch();
}
}
if (rows % batchSize != 0) {
stmt.executeBatch();
}
conn.commit();
} catch (SQLException e) {
e.getNextException().printStackTrace();
} finally {
closeResources(stmt, conn);
}
If you're wondering why I'm using a HashMap
at all, see here.