JDBC Best Practices

This page is a capstone checklist for the whole JDBC track — the habits worth carrying into every real project. Each item links back to the page that covers it in full, so use this as a quick-reference summary rather than a first read.

1) Prefer PreparedStatement Over Statement

Any query built from external input — user input, form data, request parameters — should use PreparedStatement, never string-concatenated SQL with plain Statement.

// Good: placeholders, not string concatenation
PreparedStatement ps = conn.prepareStatement("SELECT * FROM users WHERE email = ?");
ps.setString(1, userEmail);

• Never build SQL by concatenating raw strings with user input.
• Use PreparedStatement even for values you "trust" — it costs nothing and closes the door on SQL injection.

2) Always Close Resources with try-with-resources

Never rely on manual finally blocks to close a Connection, Statement, or ResultSet. See Exception Handling for the full comparison.

try (
  Connection conn = DriverManager.getConnection(url, user, password);
  PreparedStatement ps = conn.prepareStatement("SELECT id FROM users")
) {
  // conn and ps close automatically, in reverse order
}

• Declare every AutoCloseable JDBC resource inside the try-with-resources header.
• Don't mix in a manual finally block "just in case" — it's the pattern try-with-resources replaces.

3) Use Transactions for Multi-Step Operations

If two or more statements must succeed or fail together, wrap them in a transaction. See Transactions.

conn.setAutoCommit(false);
try {
  // ... run multiple statements ...
  conn.commit();
} catch (SQLException e) {
  conn.rollback();
}

• Turn off auto-commit before a multi-step operation, and commit explicitly once every step succeeds.
• Always roll back on failure so you never leave data in a half-updated state.

4) Batch Bulk Inserts and Updates

Looping a single INSERT or UPDATE statement one row at a time means one network round trip per row. Batch it instead. See Batch Processing.

for (User u : users) {
  ps.setString(1, u.getName());
  ps.addBatch();
}
ps.executeBatch();

• Use addBatch() / executeBatch() for bulk inserts and updates.
• Check the returned update counts (or catch chained exceptions) to see which rows in the batch actually succeeded.

5) Use a Connection Pool in Production

Never open a raw DriverManager connection per request in a real application — pool them instead. See Connection Pooling.

HikariDataSource ds = new HikariDataSource(config);
try (Connection conn = ds.getConnection()) {
  // borrowed from the pool, returned to it on close()
}

• Use a pool such as HikariCP for anything beyond a learning script.
• Let the pool manage connection lifetime — don't hold a borrowed connection longer than one unit of work needs.

6) Never Expose Raw SQLException Messages to Users

A raw SQLException message can leak table names, column names, or even fragments of your SQL — details an end user should never see.

catch (SQLException e) {
  logger.error("Database error", e);
  throw new RuntimeException("Something went wrong. Please try again.");
}

• Log the full exception (message, SQL state, error code) server-side for debugging.
• Show the end user a short, generic message — never the raw exception text.

7) Keep SQL Readable and Explicit

SQL embedded in Java strings is easy to let rot. Format it clearly, and avoid SELECT * in production code.

// Avoid
PreparedStatement ps = conn.prepareStatement("SELECT * FROM users WHERE active = ?");

// Prefer: explicit columns, clear formatting
PreparedStatement ps = conn.prepareStatement(
  "SELECT id, name, email " +
  "FROM users " +
  "WHERE active = ?");

• Name the columns you actually need instead of SELECT * — it's faster and won't silently break when the table changes.
• Break long queries across multiple lines with consistent indentation so they stay reviewable.