带有批处理SQL的Java循环

Question

我有个问题。现在，我正在使用JOOQ使用以下代码在我的数据库中插入大约100.000条记录：

try (Connection conn = DriverManager.getConnection(SqlConn.getURL(), SqlConn.getUSERNAME(), SqlConn.getPASSWORD())) {
    DSLContext create = DSL.using(conn, SQLDialect.MYSQL);

    for (String key : trendlines.keySet()) {
        for (Trendline trendline : trendlines.get(key)) {
            String sql = createTrendlineQuery(trendline);
            create.fetch(sql);
        }
    }

}
catch (Exception e) {
    e.printStackTrace();
}

函数createTrendlineQuery()

private String createTrendlineQuery(Trendline trendline) {

    return "INSERT INTO Trendline (openTime, market, coin, period, metric, number, slope, interceptY, percentage, formula, data) VALUES (" + 
    trendline.getOpenTime() + ", '" +
    trendline.getMarket() + "', '" +
    trendline.getCoin() + "', '" +
    trendline.getPeriod() + "', '" +
    trendline.getFormula() + "') " +
    "ON DUPLICATE KEY UPDATE " + 
    "openTime = " + trendline.getOpenTime() + ", " +
    "market = '" + trendline.getMarket()+ "', " +
    "coin = '" + trendline.getCoin() + "', " +
    "period = '" + trendline.getPeriod() + "', " +
    "formula = '" + trendline.getFormula() + "';";

}

但是这给我的互联网/数据库带来了很大的负担，所以我发现你可以对大数据进行批量插入。我找到了关于批处理插入的JOOQ的以下页面：https://www.jooq.org/doc/3.14/manual/sql-execution/batch-execution/。我想这是我需要的，但我有个问题。在我的例子中，示例如下所示：

try (Connection conn = DriverManager.getConnection(SqlConn.getURL(), SqlConn.getUSERNAME(), SqlConn.getPASSWORD())) {
    DSLContext create = DSL.using(conn, SQLDialect.MYSQL);

    create.batch(create.insertInto(DSL.table("Trendline"), DSL.field("openTime"), DSL.field("market"), DSL.field("coin")  ).values((Integer) null, null, null))
        .bind(                           trendline.getOpenTime() , trendline.getMarket() , trendline.getCoin()  )
        .bind(                           trendline.getOpenTime() , trendline.getMarket() , trendline.getCoin()  )
        .bind(                           trendline.getOpenTime() , trendline.getMarket() , trendline.getCoin()  )
        .bind(                           trendline.getOpenTime() , trendline.getMarket() , trendline.getCoin()  )
        .execute();

}
catch (Exception e) {
    e.printStackTrace();
}

不过，我需要将2 for-loops放在create.batch()之间，以便以编程方式创建插入。如何插入for loops并使用批处理插入正确的方式来减少互联网流量和数据库压力？

Answer 1

使用BatchedConnection作为快速修复

将现有的jOOQ代码(或任何基于JDBC的代码)转换为批处理JDBC交互的最简单的解决方案是使用jOOQ的BatchedConnection

create.batched((Connection c) -> {
    // Now work with this Connection c, instead of your own Connection and all the statements
    // will be buffered and batched, e.g.
    DSL.using(c).insertInto(...).values(...).execute();
});

使用您尝试使用的批处理API

只需将BatchBindStep赋值给循环中的局部变量，就可以设置：

BatchBindStep step = create.batch(query);

for (...) 
    step = step.bind(...);

step.execute();

使用导入API

使用进口API。假设您使用的是代码生成器，并且具有通常的静态导入

import static org.jooq.impl.DSL.*;
import static com.example.generated.Tables.*;

写这个：

create.loadInto(TRENDLINE)
      .onDuplicateKeyUpdate()
      .loadArrays(trendlines
          .values()
          .stream()
          .map(t -> new Object[] { 
              t.getOpenTime(),
              t.getMarket(),
              t.getCoin(),
              t.getPeriod(),
              t.getFormula()
              /* And the other fields which you partially omitted */
          })
          .toArray(Object[][]::new)
      )
      .fields(
          TRENDLINE.OPENTIME,
          TRENDLINE.MARKET,
          TRENDLINE.COIN,
          TRENDLINE.PERIOD,
          TRENDLINE.FORMULA
          /* And the other fields which you partially omitted */
      )
      .execute();

另见关于下列各节：

• 节流 (您可能想使用这些值来找到系统的最佳值)
• 错误处理

这可能是有意义的。如果输入Object[][]太大，则可以手动将输入trendlines.values()集合块块放在您的一侧。如果按键对地图进行排序是非常必要的(这不应该是我从你的问题中看出的)，那就写如下：

trendlines
    .keySet()
    .stream()
    .flatMap(k -> trendlines.get(k).stream())
    .map(t -> new Object[] { ... })
    ...

关于你自己尝试的几句话

• 您正在调用create.fetch(sql)，而实际上您的语句是一个具有更新计数的查询，因此在这种情况下，您可能希望使用create.execute(sql)。
• 请不要在使用jOOQ时连接SQL字符串！即使使用普通SQL模板，从来不需要连接SQL字符串。。您将遇到语法错误和SQL注入。请始终使用绑定变量。
• 我建议您使用jOOQ的代码生成器。使用jOOQ的大部分好处是在使用代码生成器时产生的。避免代码生成的有效原因包括:模式是动态的，运行时不知道。这几乎是不使用代码生成的唯一原因。