Skip to content
Tutorials Center
My learning

Topics

Lesson 6 of 119 ยท Databases with JDBC

JDBC Introduction

Learn how JDBC connects Java to databases - the API and driver architecture, connection URLs for H2, MySQL and PostgreSQL, try-with-resources and DatabaseMetaData.

JDBC (Java Database Connectivity) is the standard Java API for relational databases. It is part of the JDK (java.sql and javax.sql), it has been there since Java 1.1, and every way of reaching a relational database from Java goes through it: Hibernate, Spring's JdbcTemplate, Spring Data JPA, jOOQ, MyBatis and Flyway all call JDBC underneath. When a Spring Boot app logs a HikariPool warning or a PSQLException, it is talking about JDBC.

The idea is simple and powerful: you write code against a small set of interfaces (Connection, PreparedStatement, ResultSet), and a driver supplied by the database vendor implements them. Change the driver and the connection URL, and the same code talks to H2, PostgreSQL, MySQL, Oracle or SQL Server. This lesson covers the architecture, how drivers are found, connection URLs, opening and closing connections correctly, errors, and how to ask a database about itself with DatabaseMetaData. The next lesson, CRUD with JDBC, uses connections to read and write data.

The architecture

A JDBC call passes through three layers:

TEXT
 your code                      uses only java.sql / javax.sql interfaces
     |
     v
 JDBC API  (DriverManager or DataSource, Connection, PreparedStatement, ResultSet ...)
     |
     v
 JDBC driver  (a JAR from the vendor: org.postgresql:postgresql, com.mysql:mysql-connector-j, com.h2database:h2)
     |
     v  network protocol (or in-process, for embedded databases like H2)
 database server

The important types:

Type Package Role
DriverManager java.sql A class that finds a driver for a URL and opens connections. Fine for small programs and tests.
DataSource javax.sql An interface for a connection factory, usually backed by a pool. What real applications use (Connection Pooling and DataSource).
Driver java.sql Implemented by each vendor; turns a URL into a Connection. You rarely touch it.
Connection java.sql One session with the database. Creates statements and controls transactions.
Statement, PreparedStatement, CallableStatement java.sql Send SQL: plain text, precompiled with parameters, or stored procedure calls.
ResultSet java.sql A cursor over the rows a query returned.
SQLException java.sql The checked exception for everything that goes wrong.

Historically there were four "types" of drivers, some of which bridged through native libraries or ODBC. Today virtually every driver is type 4: pure Java, speaking the database's network protocol directly. You add one dependency (with runtime scope, as you saw in Maven in Depth) and you are done.

Modules. JDBC lives in the java.sql module. In a modular application, add requires java.sql; to module-info.java (The Java Module System). Java 25's compact source files only import java.base automatically, which is why every example here starts with import java.sql.*;.

Two-tier and three-tier architectures

Where the JDBC code runs decides the shape of your system:

  • Two-tier. The Java application (the client) talks to the database server directly through JDBC. Two machines, two layers. This is simple and fast to build, and it is how desktop tools, scripts, batch jobs and small apps work. The weakness is that every client needs the database address, a driver and credentials, so the database must be reachable from every user's machine, and changing the schema means updating every client.
  • Three-tier. A middle tier (an application server, for example a Spring Boot service) sits between the user interface and the database. Browsers and mobile apps call the middle tier over HTTP; only the middle tier holds the JDBC driver, the connection pool and the credentials. The database is hidden behind the firewall, business rules live in one place, and the middle tier can serve thousands of users with a small pool of connections. Nearly all web applications, including everything in the Spring lessons, are three-tier.
flowchart LR
    subgraph Two-tier
      A["Java application"] -->|JDBC| B[("Database")]
    end
    subgraph Three-tier
      C["Browser or mobile app"] -->|HTTP| D["Application server (JDBC + pool)"]
      D -->|JDBC| E[("Database")]
    end

In both designs JDBC is the link to the database; what differs is which program owns it.

The four driver types

A JDBC driver translates JDBC calls into something the database understands. There are four historical designs, numbered by how they reach the database:

Type Name How it works Advantages Disadvantages
1 JDBC-ODBC bridge Forwards every call to an ODBC driver installed on the machine Reaches any database that has an ODBC driver Needs native code on every client; slow (two translations); removed from the JDK in Java 8
2 Native-API driver Java code calling the database vendor's native client library (C code) through JNI Faster than type 1, because it talks to the vendor library directly The vendor library must be installed on every machine; not portable; a native crash can take the JVM down
3 Network-protocol (middleware) driver Sends JDBC calls over the network to a middleware server in an independent protocol; the middleware talks to the database Pure Java client; the server can pool connections, cache and support many databases; clients need no vendor software Needs and must maintain a middleware server; an extra network hop; vendors rarely ship these today
4 Native-protocol (thin) driver Pure Java that speaks the database's own network protocol directly No native code; portable; fast; one JAR is all you deploy Specific to one database (one driver per vendor)

Type 4 won. PostgreSQL, MySQL, MariaDB, SQL Server, Oracle (the "thin" driver) and H2 all ship type 4 drivers, and so does everything you use in this course. You need to know the other three mainly to read older documentation and to answer interview questions. When you do see a type 2 driver (Oracle's OCI driver is an example), the reason is a feature that only the vendor's native client offers.

Loading a driver the old way: registerDriver

Before JDBC 4 you had to register drivers yourself. A driver's static initializer calls DriverManager.registerDriver(new Driver()) when its class is loaded, which is why old code does Class.forName("com.mysql.jdbc.Driver"): loading the class registers it. You can call registerDriver directly, and its counterpart deregisterDriver removes a driver (web servers do this when an application is undeployed, to avoid leaking the class loader). Driver.jdbcCompliant() tells you whether the driver claims to pass the JDBC compliance tests.

Java Registering and removing a driver by hand

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.

In new code you will never call these methods. Add the driver JAR, and DriverManager or your connection pool finds it.

Your first connection

DriverManager.getConnection(url, user, password) returns an open Connection. Opening one is expensive: for a network database it means a TCP connection, often TLS, authentication and session setup, typically several milliseconds or more. Closing it releases resources on both sides. Always open it in a try-with-resources block, so it is closed even if an exception is thrown (try-with-resources).

Java Opening and closing a connection

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 output shows H2 2.5 and its driver, JDBC version 4.3 (the current version of the API since Java 9), the user APP (upper-cased, as H2 does with names) and the schema PUBLIC. Autocommit is true: by default every statement is its own transaction and is committed immediately. That is convenient but dangerous for multi-step changes, as Transactions with JDBC shows. The variable connection is declared as the java.sql.Connection interface, while the object is H2's org.h2.jdbc.JdbcConnection. Your code never needs to know that class.

How DriverManager finds a driver

Old tutorials start with Class.forName("com.mysql.jdbc.Driver"). That line loads the driver class so it can register itself. Since JDBC 4.0 (Java 6) it is unnecessary: each driver JAR contains the file META-INF/services/java.sql.Driver naming its driver class (you read H2's in the development environment lesson), and DriverManager loads every listed driver automatically through Java's ServiceLoader.

For each connection request, DriverManager asks the registered drivers in turn whether they accept the URL. The first one that does opens the connection. If none does, you get the most famous JDBC error of all:

Java Which driver handles which URL?

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.

Only org.h2.Driver is on this classpath, so both H2 URLs are accepted (finding a driver does not open anything, so the file path is never touched), and the PostgreSQL and MySQL URLs fail with "No suitable driver", SQLState 08001. When you see this error in a real project, the URL is not wrong in some subtle way. The driver JAR is simply missing from the runtime classpath, or the URL has a typo in its prefix such as jdbc:postgres: instead of jdbc:postgresql:.

Connection URLs

A JDBC URL always starts with jdbc:, then a subprotocol that selects the driver, then a driver-specific part with host, port, database and options. You will meet these constantly:

Database URL example Notes
H2, in memory jdbc:h2:mem:shop Private to this JVM; deleted when the last connection closes
H2, in memory, kept jdbc:h2:mem:shop;DB_CLOSE_DELAY=-1 Lives until the JVM exits; typical in tests
H2, file jdbc:h2:file:./data/shop Stored in ./data/shop.mv.db
H2, compatibility jdbc:h2:mem:test;MODE=PostgreSQL Imitates another dialect
PostgreSQL jdbc:postgresql://localhost:5432/shop Options: ?sslmode=require&ApplicationName=shop-api
MySQL jdbc:mysql://localhost:3306/shop Options: ?serverTimezone=UTC&useSSL=true (MySQL Connector/J)
MariaDB jdbc:mariadb://localhost:3306/shop
SQL Server jdbc:sqlserver://localhost:1433;databaseName=shop;encrypt=true Options separated by ;
Oracle jdbc:oracle:thin:@//localhost:1521/FREEPDB1 Service name after the slash
SQLite jdbc:sqlite:shop.db A file; good for desktop apps

Pass credentials separately as the user and password arguments (or in a Properties object together with other driver options), never inside a URL you log. And read all three from configuration, not from code, as you saw in the development environment lesson:

Java
var props = new Properties();
props.setProperty("user", System.getenv("DB_USER"));
props.setProperty("password", System.getenv("DB_PASSWORD"));
props.setProperty("ApplicationName", "shop-api");       // a PostgreSQL driver option

try (Connection connection = DriverManager.getConnection(System.getenv("DB_URL"), props)) {
    // ...
}

The life of an in-memory database

H2's in-memory databases are perfect for examples and tests, but their lifetime surprises everyone once. The name in the URL identifies the database inside the JVM: connections with the same name share it, different names are separate. And the database disappears as soon as its last connection closes, unless you add DB_CLOSE_DELAY=-1:

Java When does an in-memory database disappear?

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.

In step 1, the second connection sees the note, and other has no note table at all. In step 2 the table is gone: closing the only connection destroyed the database. In step 3 it survives. Code that opens a fresh connection per operation (which is normal with a pool) needs DB_CLOSE_DELAY=-1 when it runs against in-memory H2.

Closing resources properly

Each Connection, Statement and ResultSet holds resources: a server-side session, cursors, memory. Forgetting to close them causes leaks. In a long-running server, leaked connections pile up until the database (or the pool) refuses new ones, and the application stops working, usually at the busiest hour.

The rules:

  • Open every JDBC resource in try-with-resources. Several resources in one try (...) are closed in reverse order: result set, then statement, then connection.
  • Don't rely on cascades. The specification says closing a statement closes its current result set, but drivers differ in how eagerly they clean up after a closed connection. Close each resource explicitly; try-with-resources makes that free.
  • Keep connections short. Open (or borrow from a pool), do one unit of work, close. Never hold a connection open while waiting for user input or a slow HTTP call.
  • Don't share a connection between threads doing concurrent work. A connection is one session with one current transaction. Give each thread or request its own, which is exactly what a pool does.
Java
String sql = "SELECT id, name FROM customer WHERE country = ?";
try (Connection connection = dataSource.getConnection();
     PreparedStatement statement = connection.prepareStatement(sql)) {
    statement.setString(1, "DE");
    try (ResultSet rows = statement.executeQuery()) {
        while (rows.next()) {
            // ...
        }
    }
}   // rows, then statement, then connection: all closed, even on exceptions

The classic leak. Connection c = DriverManager.getConnection(url); ... c.close(); without try leaks the connection whenever anything between the two lines throws. With a pool, the symptom appears hours later as "Connection is not available, request timed out", far away from the bug.

Understanding SQLException

Almost every JDBC method throws the checked SQLException. Besides the message, it carries:

  • getSQLState(): a five-character code standardized by SQL and X/Open. The first two characters are the class: 08 connection problems, 23 constraint violations, 28 authentication, 42 syntax or access rule violations, 40 transaction rollbacks (such as deadlocks).
  • getErrorCode(): the vendor's own number, useful for vendor-specific handling.
  • getNextException() and causes: some drivers chain several exceptions (common with batches). SQLException is Iterable<Throwable>, so you can loop over the chain.

JDBC also defines subclasses that drivers throw, so you can catch by category: SQLSyntaxErrorException, SQLIntegrityConstraintViolationException, SQLInvalidAuthorizationSpecException, and the families SQLTransientException (retrying may succeed, such as a timeout) and SQLNonTransientException (it will fail again).

Java Reading an SQLException

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.

Each failure lands in a different category: the wrong password is SQLState 28000, the typo 42001, the unknown table 42S02 (H2's code for "table not found", both in class 42), and the duplicate key 23505. The switch uses pattern matching on the exception type, a clean way to classify errors. Real code uses this to decide what to do: retry a transient failure, turn a 23505 into a "409 Conflict" for the API client, and log everything else as a bug.

DatabaseMetaData: asking the database about itself

connection.getMetaData() returns a DatabaseMetaData with hundreds of methods describing the database: its product and version, which SQL features it supports, and the schema itself: tables, columns, keys and indexes. Tools such as IDE database browsers, migration tools (Flyway, Liquibase) and Hibernate's schema validation are built on it. Its lookup methods return ordinary ResultSets with documented column names:

Java Exploring a schema with DatabaseMetaData

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 program discovers both tables, every column with its type and nullability, the primary keys and the foreign key from orders.customer_id to customer.id, without knowing anything about the schema in advance. Note the names passed to getTables and getColumns are patterns (% matches anything) and must match the stored case: "customer" would find nothing in H2, while PostgreSQL would need exactly that lowercase spelling. storesUpperCaseIdentifiers() tells generic code which case to use. Column metadata for a single query result comes from a different object, ResultSetMetaData, covered in Batch Updates, Metadata and Large Objects.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. A service fails at startup with SQLException: No suitable driver found for jdbc:postgresql://db:5432/shop. What is the most likely fix?

    No registered driver accepted the URL. Since JDBC 4.0, drivers on the classpath register themselves, so the missing piece is the driver JAR (or a typo in the jdbc:postgresql: prefix).
  2. 2. A test opens jdbc:h2:mem:test, creates a table and closes the connection. The next method opens the same URL and queries the table. What happens?

    Without DB_CLOSE_DELAY=-1, an in-memory H2 database lives only as long as at least one connection is open.
  3. 3. In try (Connection c = ...; PreparedStatement ps = ...; ResultSet rs = ...) { }, in which order are the resources closed?

    try-with-resources closes resources in reverse order of declaration, which is exactly the order JDBC needs.
  4. 4. Which SQLException property should code use to recognize a duplicate-key error across different databases?

    SQLState codes are standardized. Vendor error codes and message texts differ between databases and versions, and driver class names are vendor-specific.
  5. 5. Why should a web application not share one Connection object between all request threads?

    Transactions, autocommit settings and open cursors belong to the connection. Pools hand out a separate connection per unit of work and reuse them afterwards.

Summary

  • JDBC is the JDK's standard API for relational databases; every Java persistence library (JPA/Hibernate, Spring Data, jOOQ) uses it underneath.
  • You code against interfaces (Connection, Statement/PreparedStatement, ResultSet); a vendor driver JAR implements them. Use DriverManager for simple programs and tests, a pooled DataSource in applications.
  • Drivers register themselves through META-INF/services/java.sql.Driver; Class.forName is obsolete. "No suitable driver" (08001) means the driver JAR is missing or the URL prefix is wrong.
  • Connection URLs are jdbc:<subprotocol>:<details>. In-memory H2 databases are shared by name and vanish with their last connection unless you add DB_CLOSE_DELAY=-1.
  • Open every JDBC resource in try-with-resources (closed in reverse order), keep connections short, and don't share them across threads.
  • SQLException carries a standard SQLState (classes 08, 23, 28, 42, ...), a vendor code and typed subclasses. DatabaseMetaData describes the database and its schema.

Next: CRUD with JDBC, where you insert, query, update and delete rows with PreparedStatement, read every column type from a ResultSet and map rows to records.