Planned lessonJNR-025-L02

Generated keys and batching

Lesson 2 of 5
Junior

On This Lesson 4 topics

In this lesson
Planned topics
Draft outline details
Practice

This lesson is planned. The outline below lists the topics it will cover. The full lesson has not been published yet.

In this lesson

insert many rows quickly and read the ids the database created.

Planned topics

The database often generates the id (identity column). Ask for it with RETURN_GENERATED_KEYS.
Each executeUpdate is one network round trip.
A batch collects many statements (addBatch) and sends them together (executeBatch).
Group batches (for example every 500 rows) so memory stays small.
Turn off auto-commit and commit once per chunk for more speed.

Draft outline details

Demonstration

java

c.setAutoCommit(false);
try (PreparedStatement ps = c.prepareStatement(
        "insert into product(name, price) values (?, ?)", Statement.RETURN_GENERATED_KEYS)) {
    for (Product p : products) {
        ps.setString(1, p.name());
        ps.setBigDecimal(2, p.price());
        ps.addBatch();
    }
    ps.executeBatch();
    try (ResultSet keys = ps.getGeneratedKeys()) { while (keys.next()) { /* keys.getLong(1) */ } }
}
c.commit();

Check your understanding

Why is a batch faster than a loop of single inserts?

Show answer

Fewer network round trips.

Practice

Apply this lesson with the module's lab, exercises and quiz.

Lab and exercises Quiz

Marking a lesson complete is optional and you can undo it any time. Your progress stays in this browser.

Continue to next lesson