PreparedStatement Updates and ResultSet Metadata: What to Take Away

Editorial note: The prior version mixed older PDF excerpts with references to custom XML utilities and general database design notes. It also emphasized Statement and string-built SQL. This rewrite streamlines to a clear outcome: use PreparedStatement for parameterized DML (and batching) and leverage ResultSetMetaData only when you truly need dynamic schema details. Any XML helper classes are omitted; standard JDBC/Java APIs and try-with-resources cover common needs.

Main Point

Modern JDBC (4.2+) favors PreparedStatement with explicit transactions for updates, and uses ResultSetMetaData to inspect result shapes when your code must adapt dynamically. Together, these features let you write secure, efficient data-access code that scales from fixed schemas to ad-hoc queries.

1) Updating Data the Right Way

• Use PreparedStatement with placeholders (?) — safer and cache-friendly.
• Control the transaction: setAutoCommit(false) → work → commit() (or rollback() on error).
• For many similar operations, use addBatch() + executeBatch() (or executeLargeBatch()).

Example: Two parameterized updates in one transaction

import javax.sql.DataSource;
import java.sql.*;

DataSource ds = MyDataSourceProvider.getDataSource(); // e.g., HikariCP pool
String sql = "UPDATE hospitals SET phoneno = ? WHERE id = ?";

try (Connection con = ds.getConnection()) {
    con.setAutoCommit(false);
    try (PreparedStatement ps = con.prepareStatement(sql)) {
        ps.setString(1, "(019) 788-7130");
        ps.setInt(2, 10101);
        ps.addBatch();

        ps.setString(1, "(019) 788-7034");
        ps.setInt(2, 10102);
        ps.addBatch();

        int[] counts = ps.executeBatch();
        con.commit();
        System.out.println("Rows affected: " + java.util.Arrays.toString(counts));
    } catch (BatchUpdateException bue) {
        con.rollback();
        System.err.println("Partial results: " + java.util.Arrays.toString(bue.getUpdateCounts()));
        throw bue;
    } catch (SQLException ex) {
        con.rollback();
        throw ex;
    }
}

Do not mix DDL (e.g., ALTER TABLE) into DML batches; behavior is driver-specific and not portable.

2) When and How to Use ResultSet Metadata

ResultSetMetaData describes the columns returned by a query—count, names, types, and properties. Most CRUD code already knows the schema and doesn’t need it. Use metadata when your code must render, transform, or validate results generically (e.g., admin consoles, report builders, CSV/JSON exporters).

Example: Inspect columns and types

import javax.sql.DataSource;
import java.sql.*;

DataSource ds = MyDataSourceProvider.getDataSource();

String sql = "SELECT id, name, created_at FROM hospitals ORDER BY id";
try (Connection con = ds.getConnection();
     PreparedStatement ps = con.prepareStatement(sql);
     ResultSet rs = ps.executeQuery()) {

    ResultSetMetaData md = rs.getMetaData();
    int cols = md.getColumnCount();

    System.out.println("Columns: " + cols);
    for (int i = 1; i <= cols; i++) {
        System.out.printf("#%d name=%s type=%s precision=%d nullable=%s%n",
                i,
                md.getColumnLabel(i),
                md.getColumnTypeName(i),
                md.getPrecision(i),
                (md.isNullable(i) == ResultSetMetaData.columnNullable ? "YES" : "NO"));
    }

    // Consume rows normally...
    while (rs.next()) {
        // ...
    }
}

Related metadata you may encounter:

• DatabaseMetaData — global database/driver capabilities.
• RowSetMetaData — metadata for rowset implementations; extends ResultSetMetaData.

3) Practical Guidance and Pitfalls

• Prefer DataSource over DriverManager for pooling and configuration.
• Batch thoughtfully: chunk large workloads (e.g., 500–2000 statements), clearBatch() between chunks.
• Handle exceptions: use BatchUpdateException.getUpdateCounts() to analyze partial success.
• Portability: keep batches to DML; check DatabaseMetaData.supportsBatchUpdates().
• Serialization: if you need to export results, prefer well-formed CSV/JSON libraries rather than rolling custom XML builders.

What You Should Now Be Able To Do

1. Explain why PreparedStatement is preferred over Statement for updates and batching.
2. Implement parameterized updates with transaction control and batch execution.
3. Use ResultSetMetaData to dynamically inspect column names, types, and attributes.
4. Recognize when metadata is useful vs. when it’s unnecessary overhead.

Next Module: Stored procedures and transactions — calling procedures, passing parameters, and managing ACID boundaries with care.