Java For loop with batch SQL

Question

I have a problem. Right now I am using JOOQ to insert arround 100.000 records in my database using the following code:

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

With the function 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() + "';";

}

But this gives a lot of load on my internet/database, so I found out you can do batch inserts for big data. I found the following page of JOOQ about batch inserts: https://www.jooq.org/doc/3.14/manual/sql-execution/batch-execution/. Now I think this is what I need, but I have a problem. The example looks in my case like this:

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

Except that I need to put the 2 for-loops between the create.batch() to programmatically create the inserts. How can I insert the for loops and am I using the batch insert the right way to reduce internet traffic and database pressure?

Answer 1

Using BatchedConnection as a quick fix

The simplest solution to turning existing jOOQ code (or any JDBC based code, for that matter) into a batched JDBC interaction is to use jOOQ's 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();
});

Using the batch API that you've tried using

You just have to assign the BatchBindStep to a local variable in your loop and you're set:

BatchBindStep step = create.batch(query);

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

step.execute();

Using the import API

Use the import API. Assuming you're using the code generator and you have the usual static imports

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

Write this:

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

See also the sections about:

• Throttling (you may want to play with these values to find what's optimal for your system)
• Error handling

Which may be of interest. If the input Object[][] gets too large, you can chunk your input trendlines.values() collection manually on your side. If sorting your map by key is really essential (it shouldn't be from what I can tell from your question), then write this instead:

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

A few remarks on your own attempts

• You're calling create.fetch(sql), when in fact your statement is a query with an update count, so in that case, you would have wanted to use create.execute(sql) instead.
• Please never concatenate SQL strings when using jOOQ! Even when using plain SQL templating, there is never a need for concatenating SQL strings. You'll run into syntax errors and SQL injection. Please always use bind variables.
• I really recommend you use jOOQ's code generator. Most benefits of using jOOQ arise when you use the code generator. Valid reasons to avoid code generation include when your schema is dynamic and not known at runtime. That's almost the only reason not to use code generation.