Java JDBC Connection

A Connection is a live session with the database. It is the object you get back from DriverManager (or a DataSource), and it is the factory for everything else — statements, transactions, savepoints, metadata. A connection is also a scarce, expensive resource: every open one ties up a socket and a server-side session, so the cardinal rule is open late, close promptly.

This chapter covers how to build a connection URL, the three ways to open a Connection, why try-with-resources is non-negotiable, the session settings you can tune, and the errors you will hit when something is misconfigured. It assumes you have already loaded a driver — see JDBC Drivers and the JDBC Introduction for the bigger picture.

The connection URL

Everything DriverManager needs to find and reach a database is encoded in the URL:

jdbc:<subprotocol>://<host>:<port>/<database>?<key=value&...>

For example jdbc:postgresql://db.internal:5432/shop?ssl=true. The jdbc: prefix is mandatory; the subprotocol selects the driver; the rest is vendor-specific but conventionally host, port, database, and a query string of tuning options. A few real URLs to recognize:

The subprotocol (postgresql, mysql, h2, sqlite) is how DriverManager decides which registered driver should handle the URL, so getting it right is what tells JDBC which database you mean.

Three ways to open one

// 1. URL with credentials as arguments
Connection a = DriverManager.getConnection(url, "app", "secret");

// 2. URL with a Properties bag (user, password, plus driver-specific keys)
Properties props = new Properties();
props.setProperty("user", "app");
props.setProperty("password", "secret");
props.setProperty("connectTimeout", "10");
Connection b = DriverManager.getConnection(url, props);

// 3. From a pooled DataSource (preferred for applications)
Connection c = dataSource.getConnection();

DriverManager vs. DataSource

DriverManager.getConnection(...) opens a brand-new physical connection every time, then tears it down when you close it. That handshake — DNS lookup, TCP, TLS, authentication — costs tens of milliseconds, which is fine for a script but ruinous for a server handling many requests.

A DataSource backed by a connection pool (HikariCP, Apache DBCP, or one your app server provides) keeps a set of physical connections open and hands them out. Calling getConnection() borrows one; calling close() returns it to the pool instead of really closing it. For any long-running application, prefer a pooled DataSource; reach for DriverManager only in small tools, tests, and examples.

Always close it — use try-with-resources

Connection, Statement, and ResultSet all implement AutoCloseable. Declaring them in a try-with-resources header guarantees they close in reverse order even if an exception is thrown — the single most important habit in JDBC:

try (Connection conn = DriverManager.getConnection(url, "app", "secret")) {
  // use conn...
} // conn.close() runs here automatically, even on exception

Leaking connections (forgetting to close) exhausts the pool and eventually hangs the whole application — a classic production outage. Note that opening a Connection throws a checked SQLException, so the call always lives inside a try (or a method that declares throws SQLException).

Once you have a connection, you use it to create Statements and PreparedStatements — and those should sit inside the same try-with-resources header so they close before the connection does:

try (Connection conn = dataSource.getConnection();
     PreparedStatement ps = conn.prepareStatement("SELECT name FROM users WHERE id = ?")) {
  ps.setInt(1, 42);
  // ...read the ResultSet...
} // ps closes first, then conn — reverse declaration order

Configuring a connection

Once open, a connection carries session-level settings:

These settings are per-session, so they reset when a real connection closes — but with a pool, a borrowed connection may still carry settings a previous user left behind. That is why pools reset autoCommit and isolation on return, and why you should set what you need rather than assume defaults.

Common connection errors

When a connection fails you almost always get a SQLException; the message tells you which layer broke:

• No suitable driver found for ... — the driver class was never loaded, or the URL's subprotocol is misspelled so no registered driver claims it. Fix the dependency or the URL (see JDBC Drivers).
• Connection refused — nothing is listening on that host/port: the database is down, or the host/port in the URL is wrong.
• Authentication / password authentication failed — wrong user or password, or the user lacks rights to that database.
• Connection timeout — the host is unreachable (firewall, wrong network). Set connectTimeout so the call fails fast instead of hanging.

A worked example: the anatomy of a connection URL

This program takes a realistic JDBC URL apart into its components and builds the Properties bag you would pass alongside it — the two inputs getConnection needs — without requiring a live database.

What to take from the run:

• The URL is not opaque — it is structured data. getConnection parses exactly these pieces: the subprotocol picks the driver, and the host/port/database tell that driver where to connect. Reading a URL out loud is the fastest way to debug "wrong database" mistakes.
• The query string (?ssl=true&applicationName=reports) carries driver-specific options. The same settings can travel in the URL or in the Properties bag — both reach the driver, and you mix them to taste.
• Credentials belong in Properties (or the DataSource config), not hard-coded in the URL string you log. The example masks the password on output for exactly that reason — never log credentials.
• connectTimeout is a real PostgreSQL driver property. Tuning lives in these key/value pairs, which is why you rarely subclass anything: configuration, not code, shapes a connection.
• This ran with no database because building the inputs to getConnection is pure string work. The expensive part — the socket and server session — only happens at the getConnection call itself, which is why you defer it and close it fast.

Practice