时间戳

首页 > 解决方案 > 带有批处理 SQL 的 Java For 循环

java - 带有批处理 SQL 的 Java For 循环

问题描述

我有个问题。现在我正在使用 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();
}

除了我需要在 2for-loops之间create.batch()以编程方式创建插入。如何插入for loops，我是否使用了正确的方式插入来减少互联网流量和数据库压力？

标签： javadatabasejooq

解决方案

用作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 的大部分好处就会出现。避免代码生成的有效原因包括当您的模式是动态的并且在运行时未知时。这几乎是不使用代码生成的唯一原因。

推荐阅读