Every JDBC program follows the same first step: open a Connection to the database. The two previous lessons introduced JDBC and the driver layer; this lesson is about the handshake itself. It covers the three DriverManager.getConnection overloads, the methods the resulting Connection exposes, why try-with-resources is the only sane way to manage it, and how DataSource fits in for later.

A JDBC Connection is the handle your program holds onto an open session with a database. Internally, the driver has opened a TCP socket to the database server (or attached to an in-process engine, in the case of H2 in memory), gone through an authentication exchange, negotiated a protocol version, and reserved server-side resources to track your session: a transaction, a default schema, a set of prepared statements, locks you hold, and so on. None of that is free.

The Connection object is your way of using that session. Through it you create statements, run queries, control transactions, and ask the database about itself. When you close the connection, the driver tears down the session, and the server releases everything it was tracking on your behalf.

The diagram shows what happens when you call DriverManager.getConnection. DriverManager is the broker. It scans the URL, picks the registered driver that recognizes the prefix (jdbc:h2:, jdbc:postgresql:, etc.), and asks that driver to open the actual session. Once the session is ready, the driver returns a Connection object you can use.

Opening a JDBC connection is expensive. For a remote database, you pay for a TCP handshake, a TLS handshake if the URL uses SSL, authentication, and any per-session setup the server does. On a typical cloud database this is often 30 to 200 ms before your first query. Reuse connections aggressively, or use a pool.

DriverManager has three overloads of getConnection. They differ in how credentials and connection options are passed.

Overload 1: URL Only

The shortest form takes just the JDBC URL. Use this when the URL itself encodes everything the driver needs, or when no authentication is required. H2 in memory falls into that bucket: there's no user account to verify.

The URL jdbc:h2:mem:shop;DB_CLOSE_DELAY=-1 says three things. jdbc:h2: picks the H2 driver. mem:shop means "an in-memory database named shop," which lives only as long as the JVM (and only as long as someone holds a connection, normally). The DB_CLOSE_DELAY=-1 setting tells H2 to keep the database alive even when every connection is closed, so multiple main runs in the same JVM see the same data. We use that URL throughout the lesson so you can run any snippet on its own.

Overload 2: URL + User + Password

The second overload separates the username and password from the URL. This is the form to use against a real database, because embedding credentials in the URL leaks them into logs, exception messages, and stack traces.

H2's default account is sa with no password, which is why the snippet works. Against PostgreSQL or MySQL you'd pass real credentials here. The driver sends the username and password to the server during the authentication step of the handshake, and the server decides whether to accept the session.

Sending the password on every connection is fine for one session but adds up if you reopen connections for each request. A pool keeps the authenticated session open and hands it out to whichever request needs it, which avoids paying for authentication every time.

Overload 3: URL + Properties

The third overload is the one to use when you need more than user and password. You build a java.util.Properties object with named options and pass it in. Each driver documents the keys it recognizes; user and password are standard across all drivers.

Why bother with Properties when overload 2 already handles user and password? Three reasons. First, real drivers expose dozens of options that have no place in a URL: SSL settings, character encoding, connection timeout, socket buffer sizes, application name, statement caching limits. Second, building a Properties map composes well with configuration files; you load application.properties and pass it straight in. Third, the URL stays short and readable when complex options live in a separate object.

The commented-out lines show the most common option keys against PostgreSQL or MySQL. useSSL=true forces a TLS handshake. characterEncoding=UTF-8 makes sure non-ASCII strings round-trip cleanly. connectTimeout=2000 bounds the TCP handshake at two seconds so a dead server fails fast instead of hanging. The exact keys vary by driver, so check the driver docs for the database you're using.

java.sql.Connection is the type you get back from getConnection. It's an interface, so the actual object is a driver-specific class that implements it. What matters is what the interface promises, because your code only ever talks to the interface.

The first three methods are how you actually run SQL. The remaining four are housekeeping methods you'll use right next to getConnection, so we'll cover them here.

close(), isClosed(), and isValid()

close() is the most important of the three. It tells the driver "I'm done with this session," and the driver tears down the network connection, releases server-side memory, and clears any uncommitted transaction (most drivers roll it back, though the spec is fuzzy about this). After close(), almost every other method on the connection throws SQLException if you call it.

isClosed() tells you whether close() has been called on this connection object. It does not check whether the underlying network connection is still healthy. A connection can be open from your side, while the database has long since killed it because of an idle timeout or a server restart. isClosed() will happily return false for that dead connection.

isValid(int timeoutSeconds) is the method that actually pings the database. The driver sends a small no-op (often SELECT 1) and waits up to the given number of seconds for a reply. If the round trip succeeds within the timeout, isValid returns true; otherwise false. Pools use this to weed out dead connections before handing them to your code.

The contract for isValid after close is that it must return false, which the output confirms. The two methods answer different questions: isClosed is "did I (the caller) close this?", and isValid is "is this connection actually usable right now?"

Calling isValid is a round trip to the database. Don't call it before every single query; that doubles your network traffic. Pools call it once when checking a connection out and rely on it being healthy through the rest of the borrow.

getMetaData()

getMetaData() returns a DatabaseMetaData object that describes the database and the driver. You'll use it most often to log what you're connected to, to write code that adapts to the database vendor, or to introspect schemas for tooling.

Output (versions will vary by H2 release):

The methods on DatabaseMetaData cover a lot of ground. Beyond product name and driver name, you can list tables, list columns, ask whether the database supports specific SQL features, get the maximum identifier length, and so on. Most application code doesn't need any of that, but logging the database product and version at startup is a habit worth building. When something breaks in production, the first question is usually "what database are we even talking to?"

A Connection is an AutoCloseable. That means you can declare it as the resource of a try-with-resources block, and the JVM will call close() for you whether the body finishes normally or throws. Every JDBC connection in real production code should be wrapped this way; the cost of forgetting is a leaked session on the database that can take a real server out.

What makes try-with-resources matter is exception safety. Without it, you'd write something like the version below, and almost everyone gets it slightly wrong.

The bug isn't always visible in a one-off main. It bites in a server that runs the same code thousands of times a day. Every exception path that skips close() leaves a session behind, and the database eventually hits its max_connections limit. Then every new connection attempt fails until someone restarts the server.

The correct version is one line longer and bulletproof.

The mechanics are worth a quick look. When the JVM enters the try block, it remembers the resource. When the block exits, whether normally or because an exception is propagating, it calls close() on the resource. If both the body and close() throw, the close() exception is attached to the original as a "suppressed" exception so neither one is silently lost. That last detail matters because pre-Java-7 patterns using finally had a habit of swallowing the real error and surfacing the close() error instead.

Forgetting close() doesn't just leak a Java object. On the database side it holds a slot in max_connections (default 100 on PostgreSQL, 151 on MySQL), plus per-session memory, plus any locks the transaction is holding. One forgotten connection is invisible; a thousand take down the database.

We saw the Properties overload earlier. Let's build out a more realistic example that loads settings from somewhere that isn't hard-coded, since that's how it works in real apps. The pattern is: read from a config file, environment variables, or a vault, build a Properties, hand it to DriverManager.

Two notes on the pattern. First, building the Properties map is plain Java; you can populate it from anywhere. A common pattern is props.load(new FileInputStream("db.properties")), which reads a properties file from disk in one call. Second, drivers silently ignore keys they don't recognize, which is both convenient (the same config works on multiple drivers) and dangerous (a typo'd key like usSSL won't enable SSL and won't tell you it didn't). Always read the driver docs when you set a non-standard key, and verify in getMetaData or a log line that you got the connection you expected.

The same pattern with the file-loading idiom:

Output (when the file is missing):

A real production file might look like this:

The socketTimeout key (driver-specific, present on PostgreSQL and MySQL drivers) bounds how long any single query can sit waiting for a response from the server. It's the JDBC equivalent of setSoTimeout on a raw socket; without it, a hung query can block your thread forever. We won't go deeper into per-query timeouts here, but the option exists.

We touched on DatabaseMetaData earlier. Because it's reachable from any connection and it's the answer to a lot of "what database am I talking to?" questions, it deserves a focused look.

The diagram groups the methods into four bands. Product methods identify the engine: name and version. Driver methods identify the JDBC driver that wraps it. JDBC version methods say which spec the driver implements (4.0, 4.2, 4.3). Connection methods echo back the URL and username you used. The "feature flags" band (last node) lets you ask the driver whether it supports things like batched updates or specific transaction isolation levels, which is useful for code that has to run against more than one database.

A larger demo that includes a feature flag:

Logging the first four bands at application startup is useful. When something breaks at 3 a.m. and the on-call engineer is staring at the logs, "we're on H2 2.2.224 with driver 2.2.224" is exactly the answer they need to skip ten minutes of debugging.

DriverManager is the textbook way to open a connection, and it's what most tutorials show first because it works with no setup. In production code you almost always use javax.sql.DataSource instead. The reasons are worth understanding even before you reach the pooling lesson.

A DataSource is an interface whose getConnection() method returns a Connection. That's it. The interface hides where the connection comes from: it might be freshly opened, it might be borrowed from a pool, it might be looked up through JNDI in an application server. Your code doesn't know and doesn't care; it just calls getConnection().

The diagram shows the indirection: DataSource is the contract, and many classes can fulfill it. Your business code asks the DataSource for a connection; the implementation decides whether to open a fresh one, hand out a pooled one, or look one up in a directory.

Why prefer it over DriverManager? Three reasons that mostly come up later, but they're worth flagging now.

First, pooling. A DataSource backed by a pool reuses connections across many requests. The application asks for a connection, runs a query, and "closes" it. The close call returns the connection to the pool instead of tearing down the session. The next request gets the same warm connection and skips the handshake. That's the difference between a 30 ms query and a 200 ms one for most cloud databases.

Second, configuration. A DataSource holds its configuration (URL, credentials, pool sizing, timeouts) as object state. You configure it once at startup and inject the same instance everywhere it's needed. With DriverManager, every caller has to know the URL and the credentials, which spreads configuration across the codebase.

Third, testing. Because DataSource is an interface, you can substitute it with a test implementation that returns connections to an in-memory database. Your production code doesn't change; it still calls dataSource.getConnection(). Test code points it at H2, production code points it at PostgreSQL.

The simplest possible DataSource (not for production, but useful for seeing the shape of the interface):

This TrivialDataSource adds nothing real; it's a thin wrapper over DriverManager. What matters is the shape. The application code that calls ds.getConnection() doesn't know or care whether it's getting a fresh session or one from a pool. Swap the implementation for HikariCP later and the calling code doesn't change. That substitutability is what makes DataSource a useful abstraction for production code.

A pooled DataSource typically gets a connection in under a millisecond because no handshake is needed. DriverManager.getConnection against a remote database is two or three orders of magnitude slower per call. If you find yourself opening a connection per request, the pool is the fix.

A complete runnable program that pulls everything together. It connects to the in-memory shop, prints connection metadata, runs through isClosed and isValid, and closes cleanly through try-with-resources.

The structure is the template you'll reuse in every JDBC program: build options, open the connection inside try-with-resources, do work, let the block close the connection for you.