Skip to content
Tutorials Center
My learning

Topics

Lesson 11 of 119 ยท Databases with JDBC

Batch Updates, Metadata and Large Objects

Speed up JDBC inserts with addBatch and executeBatch, print any query with ResultSetMetaData, store text and files as CLOB and BLOB, and scroll result sets.

The JDBC you have used so far sends one statement, waits for the answer, then sends the next. That is fine for a handful of rows, but importing 50,000 products that way means 50,000 network round trips. Batch updates bundle many statements into one trip. This lesson covers batches first, then three tools you reach for less often but need to know: ResultSetMetaData for working with queries whose columns you don't know in advance, large objects (CLOB and BLOB) for long text and binary files, and scrollable result sets that can move backwards.

All examples use H2 in memory. Basic PreparedStatement usage is covered in CRUD with JDBC, and commit/rollback in Transactions with JDBC.

Why batches are faster

Each executeUpdate() call travels to the database server, gets parsed or looked up, runs, and sends a result back. With a database on another machine, the waiting on the network often costs far more than the work itself: at 0.5 ms per round trip, 50,000 single inserts spend 25 seconds just traveling.

A batch collects the parameter sets on the client and sends them together. The database runs the same prepared statement once per set and returns all the update counts in one reply. The API is three methods:

Method What it does
addBatch() On a PreparedStatement: saves the current parameter values as one entry.
addBatch(String sql) On a plain Statement: adds a complete SQL statement.
executeBatch() Sends every entry, returns an int[] with one update count per entry, and empties the batch.
clearBatch() Throws away entries that were not executed yet.

Your first batch

The pattern is: set parameters, addBatch(), repeat, then executeBatch(). Two habits make it production-ready. First, run the batch inside a transaction, so a failure doesn't leave half the rows behind and the database doesn't commit after every row. Second, flush the batch every few hundred or thousand rows, so the client doesn't hold 50,000 parameter sets in memory at once.

Java Inserting 10,000 rows in chunks

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

The last executeBatch() after the loop is the line people forget. Without it, any rows after the last full chunk would silently never be sent. Here 10,000 divides evenly by 1,000, so it sends nothing, but with 10,001 rows it would send one.

MAX(name) returns Product 9999, not Product 10000, because strings compare character by character and '9' sorts after '1'. A small reminder that names are not numbers.

Reading the update counts

Each element of the returned array describes one entry:

  • a number >= 0: rows affected by that entry;
  • Statement.SUCCESS_NO_INFO (-2): it worked, but the driver doesn't know the count. Some drivers return this when they rewrite the batch into one multi-row statement;
  • Statement.EXECUTE_FAILED (-3): that entry failed (only seen when the driver keeps going after an error, see below).

For very large counts there is executeLargeBatch(), which returns long[].

Driver settings that matter

Batches are only as fast as the driver makes them. Two popular drivers need a URL flag to rewrite inserts into a single multi-row INSERT:

PROPERTIES
# MySQL Connector/J
jdbc:mysql://localhost:3306/shop?rewriteBatchedStatements=true
# PostgreSQL JDBC
jdbc:postgresql://localhost:5432/shop?reWriteBatchedInserts=true

With the flag, imports commonly get several times faster. Without it, the driver may still send entries one by one. H2 in memory has no network at all, so batching makes little difference in these TryIts; the API and the error handling are what you are practicing here.

When one entry fails: BatchUpdateException

If an entry fails, executeBatch() throws a BatchUpdateException, a subclass of SQLException. Its getUpdateCounts() tells you what happened to the entries, but drivers differ in a way that matters:

  • Some stop at the first failure: the array is shorter than the batch and holds counts only for the entries before the failing one (PostgreSQL behaves like this).
  • Others keep going: the array has one element per entry, with EXECUTE_FAILED at each failure. H2 does this.

Because of that difference, the reliable approach is to treat the batch as all-or-nothing: run it in a transaction and roll back on BatchUpdateException. Use the counts for reporting, not for deciding what to keep.

Java A duplicate key inside a batch

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

H2 ran all three entries, reported [1, -3, 1], and the rollback removed ana and lee again, so only the original user is left. SQLState 23505 is the standard code for a unique constraint violation, which is handy for telling "duplicate" apart from other errors without parsing messages.

Generated keys and batches: prepareStatement(sql, Statement.RETURN_GENERATED_KEYS) combined with executeBatch() works in H2, PostgreSQL and MySQL, and getGeneratedKeys() then returns one row per inserted entry. The JDBC specification leaves this optional, so check your driver before relying on it.

ResultSetMetaData: describing any query

Usually you know exactly which columns a query returns, and you map them to a record by name. But some code has to handle any query: an admin console, a CSV exporter, a test helper that dumps a table, a report builder. ResultSet.getMetaData() returns a ResultSetMetaData that describes the columns:

Method Returns
getColumnCount() number of columns (they are numbered from 1)
getColumnLabel(i) the name to display, including AS aliases; prefer this over getColumnName(i)
getColumnType(i) a java.sql.Types constant; JDBCType.valueOf(...) turns it into a readable enum
getColumnTypeName(i) the database's own type name, such as CHARACTER VARYING
getPrecision(i), getScale(i) size and decimal places, e.g. 8 and 2 for DECIMAL(8,2)
isNullable(i) columnNoNulls, columnNullable or columnNullableUnknown
getColumnClassName(i) the Java class getObject(i) will return

With those, a generic table printer takes a dozen lines. It measures each column, then pads the values. It works for any SELECT, including joins and computed columns.

Java A table printer for any query

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

Notice two things in the output. The NULL price prints as null, because String.valueOf(null) does that; a real exporter would print an empty cell. (It also explains why Mara Quill's total is only 18.50: SUM skips NULL.) And the computed column doubled still has a full type description: H2 worked out that a DECIMAL times an integer is a NUMERIC, which Java receives as a BigDecimal.

DatabaseMetaData, which describes the database itself (tables, columns, keys, supported features), was introduced in JDBC Introduction. The two work well together: getTables() lists the tables, and ResultSetMetaData describes any query against them.

Large objects: CLOB and BLOB

Most columns hold small values. For long text (articles, JSON documents, logs) and binary data (images, PDFs), SQL has large object types:

  • CLOB (character large object) for text. MySQL calls it TEXT/LONGTEXT, PostgreSQL uses TEXT.
  • BLOB (binary large object) for bytes. MySQL has BLOB/LONGBLOB, PostgreSQL uses BYTEA.

For values that fit comfortably in memory, you don't need any special API: setString/getString work for CLOBs and setBytes/getBytes for BLOBs. For big values, use streams so the whole file never has to sit in a Java array: setCharacterStream/getCharacterStream and setBinaryStream/getBinaryStream. You can also ask for a Clob or Blob object, which lets you read just a part (getSubString, getBytes(pos, len); positions start at 1) and should be released with free() when you are done.

Java Storing text and bytes as CLOB and BLOB

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

The checksums match, so the bytes made the round trip unchanged. In a real program the streams would come from Files.newInputStream(path) or an upload, and the reading side would copy into an output stream with in.transferTo(out) instead of readAllBytes().

Should files live in the database at all? Small, tightly linked data (an avatar, a signed contract that must be backed up with its record) is fine as a BLOB. For large or numerous files, most teams store them in a file system or object storage (such as S3) and keep only the path, size and checksum in a normal column. Databases full of BLOBs make backups slow and caches less effective.

Scrollable and updatable result sets

By default a ResultSet is forward-only: you call next() until it returns false, and that's it. You can request more when creating the statement:

Java
Statement st = db.createStatement(
    ResultSet.TYPE_SCROLL_INSENSITIVE,   // can move in any direction; doesn't see later changes
    ResultSet.CONCUR_READ_ONLY);         // or CONCUR_UPDATABLE to change rows through it
Type Meaning
TYPE_FORWARD_ONLY the default; only next() is guaranteed to work
TYPE_SCROLL_INSENSITIVE move freely; shows the data as it was when the query ran
TYPE_SCROLL_SENSITIVE move freely and reflect changes made by others (rarely supported; H2 doesn't)

A scrollable result set adds previous(), first(), last(), absolute(n) (row n, from 1; negative counts from the end), relative(n), beforeFirst() and getRow(). With CONCUR_UPDATABLE you can also call updateXxx(column, value) followed by updateRow(), or deleteRow(), on the current row.

Java Moving around a scrollable result set

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

Updatable result sets need a query the driver can map back to a single table with its primary key; joins and aggregates won't work. And although scrolling looks convenient, many drivers implement TYPE_SCROLL_INSENSITIVE by loading every row into client memory. For paging through large tables, use SQL instead: ORDER BY ... LIMIT ? OFFSET ?, or better, keyset pagination (WHERE id > ? ORDER BY id LIMIT ?), which stays fast on page 5,000. Some drivers are lenient and let a forward-only result set call last() anyway (H2 does), but code that relies on that breaks on the next database.

Fetch size for big reads

The opposite problem, reading millions of rows forward-only, is controlled by setFetchSize(n): a hint for how many rows the driver should pull from the server per trip. Defaults vary wildly. The PostgreSQL driver loads the entire result into memory unless autocommit is off and a fetch size is set; MySQL Connector/J also buffers everything by default. If an export job runs out of memory on a big table, the fetch size is the first thing to check.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. A loop adds 2,500 entries and calls executeBatch() whenever count % 1000 == 0. There is no call after the loop. How many rows are inserted?

    The batch is flushed at 1,000 and 2,000. The last 500 entries are added but never sent, which is why you always call executeBatch() once more after the loop.
  2. 2. What does Statement.EXECUTE_FAILED (-3) in the update counts of a BatchUpdateException tell you?

    Drivers that keep going after a failure mark each failed entry with EXECUTE_FAILED. Drivers that stop instead return a shorter array.
  3. 3. For SELECT name AS author FROM writer, which call returns AUTHOR (the alias)?

    The label includes the alias, so use getColumnLabel for display and for mapping. Columns are numbered from 1, so index 0 is invalid.
  4. 4. You need to save a 2 GB video in a BLOB column. Which approach avoids loading the whole file into memory?

    A stream lets the driver read the file piece by piece. readAllBytes needs a 2 GB array, and converting bytes to a String corrupts binary data.
  5. 5. Why is ORDER BY id LIMIT ? OFFSET ? usually better than a scrollable result set for showing page 40 of a large table?

    Scroll-insensitive result sets are often fully buffered on the client. Letting the database return just one page keeps memory and transfer small.

Summary

  • Batches (addBatch() + executeBatch()) send many statements in one round trip. Run them in a transaction, flush in chunks, and call executeBatch() once more after the loop.
  • Update counts can be a row count, SUCCESS_NO_INFO (-2) or EXECUTE_FAILED (-3). On a BatchUpdateException, drivers either stop or continue, so roll back and treat the batch as all-or-nothing.
  • Enable batch rewriting in MySQL (rewriteBatchedStatements=true) and PostgreSQL (reWriteBatchedInserts=true) for big speedups.
  • ResultSetMetaData describes a query's columns (label, type, precision, nullability), which makes generic printers and exporters possible. Columns start at 1.
  • CLOB holds long text and BLOB holds bytes. Use setString/setBytes for small values and the stream methods for large ones; consider object storage for big files.
  • Scrollable result sets can move in any direction and updatable ones can change rows, but SQL paging and a sensible fetch size scale better for large data.

Next: Connection Pooling and DataSource explains why real applications never call DriverManager.getConnection for every request.