Java Database (JDBC)

Most real Java applications talk to a relational database. This topic covers JDBC, the standard Java API for connecting to databases and executing SQL. JDBC has been part of Java since 1997 and is the foundation that every higher-level database framework — JPA, Hibernate, MyBatis, Spring Data JPA — builds on. Understanding JDBC is essential to using those frameworks effectively.

Why this topic matters

Even if your day-to-day work uses Spring Data JPA or Hibernate, you are using JDBC under the hood. Knowing how JDBC works helps you understand what the higher-level abstractions do, when they are efficient, and when they are not. The classic performance mistake in JPA is the “N+1 query” problem — loading 1000 entities and triggering 1000 lazy SQL queries instead of one join. Recognising this requires knowing what SQL JPA is generating, which requires understanding JDBC.

JDBC is also the right tool for many jobs that JPA does not handle well: stored procedures, complex analytical queries, bulk inserts, database-specific features. A codebase that uses JPA for 80 percent of its queries and raw JDBC for the 20 percent that need it is a healthy codebase. A codebase that uses JPA for everything is usually slow and complicated.

Finally, JDBC is where Java meets SQL — and SQL is one of the most valuable skills in software engineering. Time spent on JDBC is time spent learning SQL, and SQL is a far more durable skill than any one ORM.

What you will learn

You will learn how to obtain a Connection from a DriverManager or a DataSource, how to safely execute queries with PreparedStatement to avoid SQL injection (the single most important security practice in database code), how to read results with ResultSet, how to wrap multiple operations in a transaction, how to retrieve auto-generated keys, and how to use connection pooling to make a production application fast.

You will also learn the limits of raw JDBC: no object-relational mapping, no schema migration tooling, no declarative query language. We touch on the trade-offs between hand-written JDBC and higher-level frameworks such as Spring Data JPA — when to use which, and how to mix them in the same codebase.

Core concepts

  • DriverManager — the classic factory for Connections; uses the JDBC URL scheme.
  • DataSource — the modern factory; preferred for pooled connections and JNDI deployment.
  • Connection — a session with the database; close it after use (try-with-resources).
  • Statement — one-shot SQL with no parameters; rarely the right choice.
  • PreparedStatement — pre-parsed SQL with ? placeholders; the safe default.
  • CallableStatement — for stored procedures.
  • ResultSet — a cursor over the rows returned by a query.
  • Transaction — a unit of work that commits atomically. JDBC auto-commits by default; turn it off with setAutoCommit(false).
  • Connection pool — a managed set of warm Connections; HikariCP is the de-facto standard.

Common pitfalls

  • SQL injection. Concatenating user input into a SQL string is the single most common cause of database-driven security incidents. Always use PreparedStatement with ? placeholders.
  • Leaking connections. Forgetting to close a Connection after use exhausts the pool and eventually the database. Always use try-with-resources.
  • Auto-commit surprises. By default, every statement commits immediately. If you want two writes to be atomic, you must turn off auto-commit and explicitly commit or rollback.
  • Reading columns by index when schema changes. rs.getString(3) breaks if a column is added. Read by column name: rs.getString("email").
  • Forgetting wasNull(). rs.getInt("age") returns 0 for both “age is 0” and “age is NULL”. Check rs.wasNull() if NULL is meaningful.

Best practices

  • Always use PreparedStatement, even for queries with no parameters. The pre-parse is faster on the second call.
  • Always wrap Connection, Statement, and ResultSet in try-with-resources so they are closed on any path, including exceptions.
  • In production, always use a connection pool (HikariCP). Never call DriverManager.getConnection per request.
  • Group related writes into transactions. Roll back on any failure; never commit a partial transaction.
  • For batch inserts, use addBatch + executeBatch. They are 10x-100x faster than individual inserts.
  • Profile slow queries at the database, not the application. The database's EXPLAIN ANALYZE is the source of truth.

Frequently asked questions

Why is SQLException checked?

Because JDBC was designed in 1997 when checked exceptions were in fashion and the designers wanted to force callers to handle database errors. In modern Java style, this is considered a mistake — database failures are usually unrecoverable at the call site, and the proliferation of throws SQLException clutters method signatures. Many modern wrappers (Spring's DataAccessException) convert checked SQLException to an unchecked exception.

Should I use JDBC or JPA?

Use JPA (Spring Data JPA) for the 80 percent of queries that map cleanly to entities — CRUD operations, simple joins, simple search. Use raw JDBC for the 20 percent that do not — complex reports, stored procedures, bulk operations, database-specific features. A healthy codebase uses both. The worst outcome is using JPA for everything and writing tortured JPQL to express queries that would be one line of SQL.

How do I prevent SQL injection?

Always use PreparedStatement with ? placeholders, never string concatenation. The PreparedStatement pre-parses the SQL and binds parameter values safely, so user input is never interpreted as SQL. This is sufficient for all standard SQL. For dynamic table or column names (which cannot be parameters), validate against a whitelist.

What is the N+1 query problem?

When you load a list of entities and then access a lazy-loaded association on each, JPA issues one SQL per entity — 1000 entities becomes 1001 queries. The fix is usually to fetch-join the association in the original query, or to use @EntityGraph to declaratively eager-fetch. Recognising the pattern requires understanding what SQL JPA is generating, which requires understanding JDBC.

Tutorials in this topic

  • JDBC — connecting, querying, transactions, pooling.
  • Exception Handling — required reading: SQLException is checked, and try-with-resources is essential.