java - 更新 t1 中的一行，同时在 t2 中插入许多行

Question

我不知道我是否应该使用 2 个单独的查询，或者是否只有 1 个是可能的。

我需要在 table 中添加UPDATE一行，records然后在 table 中INSERT添加 50,000 行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

我可以编写一个如下所示的查询，但它会UPDATE在每批中与INSERT我的代码一起执行。

给定我的 Java 代码，我如何调整此查询以便UPDATE执行一次和INSERT50,000 次。如果 2 个查询是标准方法，请告诉我。

工作 SQLFiddle

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 代码：

   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);

   }

如果您想知道我为什么要使用 a HashMap，请参阅此处。

标签： javapostgresql