Exception Handling and Best Practices

Writing code that works on a sunny day is easy. Writing code that survives bad network segments, locked tables, and mistyped passwords is what separates production software from classroom exercises. This chapter covers how JDBC reports failures, how to log them without leaking secrets, and the everyday habits that keep your database layer maintainable and safe.

Prerequisites

  • MySQL running with the jdbc_demo database
  • The users and accounts tables from previous chapters
  • A Maven project with mysql-connector-j in pom.xml

Understanding SQLException

Every JDBC method that touches the network or the database declares throws SQLException. It is not a single exception class but the root of a hierarchy that includes driver-specific subclasses.

Error Code vs. SQL State

SQLException carries two distinct identifiers:

MethodExampleMeaning
getErrorCode()1062A vendor-specific number. MySQL uses this for duplicate keys, syntax errors, lock waits, and more.
getSQLState()23000A five-character code defined by the SQL standard. It is more portable but less granular.

Here are the MySQL error codes you will see most often:

Error CodeSQL StateTypical Cause
104528000Access denied — wrong credentials or host restriction.
104942000Unknown database — the database name in the URL does not exist.
106223000Duplicate entry — a UNIQUE constraint or primary key was violated.
114642S02Table does not exist.
120540001Lock wait timeout — another transaction held a row lock too long.
2003HY000Cannot connect — MySQL is not running or the port is wrong.

Tip

Log Both Numbers

When you log an exception, always capture getSQLState() and getErrorCode(). The SQL state helps you write portable retry logic; the error code helps you Google the exact vendor message.

Recoverable vs. Fatal Errors

Not every SQLException means your application is broken.

  • Recoverable: Lock wait timeout, deadlock (retry the transaction), transient network failure.
  • Fatal: Authentication failure, missing table, syntax error, constraint violation caused by a bug in your code.

Treat recoverable errors with a limited retry loop and an exponential backoff. Treat fatal errors as bugs that need human attention.

Logging Strategy

Calling e.printStackTrace() sends output to the console and offers no way to control verbosity, formatting, or destination. In real applications, use a logging facade such as SLF4J.

Adding SLF4J

Add a simple binding to pom.xml:

xml
<dependency>
    <groupId>org.slf4j</groupId>
    <artifactId>slf4j-simple</artifactId>
    <version>2.0.9</version>
</dependency>

Logging an Exception Properly

Warning

Never Log Raw Passwords

If you log the JDBC URL, strip out the password first. If you log parameters, redact anything that looks like a credit card number, token, or password.

Externalizing Configuration

Hard-coding the database URL, username, and password in Java source files makes your application impossible to deploy across environments without recompiling. Move these values to an external file.

A Simple Properties File

Create src/main/resources/db.properties:

properties
# Database connection settings
db.url=jdbc:mysql://127.0.0.1:3306/jdbc_demo?useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
db.user=jdbc_user
db.password=jdbcpass

Load it at runtime:

For production secrets, prefer environment variables or a secrets manager over plain files:

java
String password = System.getenv("DB_PASSWORD");

Query Timeouts

A runaway query can hang a thread forever. JDBC lets you set a per-statement timeout:

java
PreparedStatement ps = conn.prepareStatement(sql);
// Abort the statement if it runs longer than 10 seconds
ps.setQueryTimeout(10);
ps.executeQuery();

The timeout is enforced by the database driver, not the JVM. If the limit is exceeded, the driver throws an SQLTimeoutException, a subclass of SQLException.

Tip

Set Timeouts on All Long-Running Statements

Interactive queries and report generation should have a timeout. Short OLTP queries can live with the default. Never let a query sit indefinitely.

Best Practices Checklist

Here is a condensed list of rules that should guide every JDBC program you write.

RuleWhy
Always use PreparedStatementPrevents SQL injection and improves performance.
Always use try-with-resourcesPrevents connection leaks, statement leaks, and result set leaks.
Never create a connection inside a loopCreating connections is expensive; use a pool and reuse connections.
Always disable auto-commit for multi-step operationsGuarantees atomicity. Commit explicitly when the work is done.
Batch writes and wrap them in a transactionReduces network round-trips and keeps data consistent.
Externalize configurationKeeps secrets and environment-specific data out of source control.
Log exceptions with SQL state and error codeMakes production debugging possible without attaching a debugger.
Set query timeoutsPrevents runaway queries from consuming threads forever.
Validate connections on borrow (in a pool)Catches stale connections before your business logic sees them.
Keep transactions shortLong transactions hold locks and exhaust pool capacity.

FAQ

What is the difference between getErrorCode() and getSQLState()?

getErrorCode() is a vendor-specific integer. MySQL, PostgreSQL, and Oracle all use different numbers for the same conceptual error. getSQLState() is a five-character standard code that is more portable but less detailed. Use getErrorCode() for precise handling and getSQLState() for portable logging.

Should I catch SQLException inside my DAO or throw it?

Throw it. DAOs are data-access utilities; they should not make policy decisions about retries, user messages, or shutdowns. Let the calling service layer catch the exception and decide what to do. The only exception is cleanup logic such as rolling back a transaction, which belongs in the DAO or a transaction manager.

How do I know whether an error is recoverable?

There is no universal rule, but a good heuristic is:

  • Retryable: SQLTransientConnectionException, lock timeouts, deadlocks (MySQL error 1213), and network blips.
  • Not retryable: syntax errors, missing tables, primary-key violations, and authentication failures.

When in doubt, let the exception propagate rather than retrying indefinitely.

Is e.printStackTrace() ever acceptable?

Only in throwaway scripts or student homework. In any codebase that will be deployed, route errors through a logging framework so operations teams can aggregate, filter, and alert on them.