Lindorm:Write data in an efficient manner

Prerequisites

Before you begin, ensure that you have:

• A JDBC connection to LindormTSDB. For setup instructions, see Use Lindorm JDBC Driver to connect to and use Lindorm TSDB (recommended).

• A time series table to write to. For table design guidance, see Design a time series table.

How it works

PreparedStatement lets you define an INSERT statement once and execute it repeatedly with different parameter values. Calling addBatch() for each row and then executeBatch() sends all rows in a single database round trip, which is significantly more efficient than executing individual INSERT statements.

All column parameters are bound by position using the question mark (?) placeholder. The index passed to each setXXX() call must match the column order in the INSERT statement.

Write data in batches

The following steps use the aqm air quality table from Design a time series table as an example.

1. Create a connection object. See Use Lindorm JDBC Driver to connect to and use Lindorm TSDB (recommended) for connection setup.

2. Create a PreparedStatement object with the INSERT statement.

   StringBuilder builder = new StringBuilder();
   builder.append("INSERT INTO aqm (city, district, id, time, pm2_5, pm10, so2, no2) ");
   builder.append("VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
   
   PreparedStatement preparedStmt = connection.prepareStatement(builder.toString());

3. Bind parameters for each row and execute the batch.

   for (int i = 0; i < batchSize; i++) {
       // Bind values in the same order as the columns in the INSERT statement.
       // city, district, id, ts, pm25, pm10, so2, no2 are local variables.
       preparedStmt.setString(1, city);
       preparedStmt.setString(2, district);
       preparedStmt.setString(3, id);
       preparedStmt.setTimestamp(4, ts);
       preparedStmt.setDouble(5, pm25);
       preparedStmt.setDouble(6, pm10);
       preparedStmt.setDouble(7, so2);
       preparedStmt.setDouble(8, no2);
   
       // Queue the row for batch execution.
       preparedStmt.addBatch();
   }
   
   // Write all queued rows to the database.
   int[] results = preparedStmt.executeBatch();
   
   // results[i] contains the update count for each row in the batch.

Usage notes

Parameter binding: In LindormTSDB, PreparedStatement only supports the question mark (?) placeholder. The index in each setXXX() call must correspond exactly to the column position in the INSERT statement.

Batch size: Batch size controls the tradeoff between throughput and latency — larger batches reduce round trips but increase memory usage and per-request latency. A larger batch does not always produce better write performance; the optimal value depends on your table structure.

What's next

• Use Lindorm JDBC Driver to connect to and use Lindorm TSDB (recommended)

• Design a time series table