JDBC & Databases

Connecting to a Database

15 min Lesson 2 of 13

Connecting to a Database

Before you can execute a single SQL statement you need a live Connection object. JDBC gives you two distinct mechanisms to obtain one: the low-level DriverManager class and the higher-level DataSource interface. Understanding both — and knowing when to reach for each one — is the foundation of everything that follows in this tutorial.

How JDBC Loads a Driver

A JDBC driver is just a JAR on your classpath that contains a class implementing java.sql.Driver. Since JDBC 4.0 (Java 6), the driver registers itself automatically via the Service Provider Interface (SPI): the JAR includes a file META-INF/services/java.sql.Driver listing its driver class, and DriverManager reads it at startup. You no longer need to write Class.forName("com.mysql.cj.jdbc.Driver") — though you may still see it in legacy code.

What SPI means in practice: Add the driver JAR (e.g. mysql-connector-j via Maven/Gradle) to your project and it is discovered automatically. No manual registration code needed.

DriverManager — The Simple Approach

DriverManager.getConnection(url, user, password) is the most direct way to get a connection. It iterates the registered drivers and asks each one whether it understands the URL you supplied.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class SimpleConnect {
    public static void main(String[] args) throws SQLException {
        String url  = "jdbc:mysql://localhost:3306/shop?useSSL=false&serverTimezone=UTC";
        String user = "appuser";
        String pass = "secret";

        try (Connection conn = DriverManager.getConnection(url, user, pass)) {
            System.out.println("Connected: " + conn.getMetaData().getDatabaseProductName());
        }
        // conn.close() is called automatically by try-with-resources
    }
}

The try-with-resources block ensures the connection is closed even if an exception is thrown. Never skip this — an unclosed connection leaks a socket and a server-side session.

Anatomy of a JDBC URL

Every JDBC URL follows the pattern jdbc:<subprotocol>:<subname>. The subprotocol identifies the driver; the subname is driver-specific. Common examples:

MySQL / MariaDB: jdbc:mysql://host:3306/dbname?param=value
PostgreSQL: jdbc:postgresql://host:5432/dbname
H2 (in-memory, great for tests): jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1
SQLite: jdbc:sqlite:/path/to/file.db

Query-string parameters vary by driver but frequently include:

useSSL=true / sslmode=require — encrypt the connection (always use in production).
serverTimezone=UTC (MySQL) — prevents timezone negotiation errors.
connectTimeout=5000 — maximum milliseconds to wait for the TCP handshake.

Never hardcode credentials in source code. Load them from environment variables or a secrets manager. A URL string with a password that ends up in version control is a security incident waiting to happen.

Why DriverManager Is Not Enough for Real Applications

Opening a fresh TCP connection for every database call is expensive — typically 20–100 ms on a local network. Under any meaningful load, a web application that uses DriverManager directly will either exhaust the database's connection limit or create unacceptable latency. This is the problem connection pooling solves, and pooling is exposed through the DataSource interface.

DataSource — The Production Approach

javax.sql.DataSource (part of the JDBC API) is a factory that hands out pre-established connections from a pool. From the calling code's point of view the API is almost identical — you call dataSource.getConnection() instead of DriverManager.getConnection(...) — but behind the scenes the connection comes from a reusable pool rather than a fresh TCP socket.

The most widely used DataSource implementation is HikariCP, known for its low overhead and aggressive validation. Add it to your build:

<!-- Maven -->
<dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP</artifactId>
    <version>5.1.0</version>
</dependency>

Configure and use it:

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;

public class DataSourceFactory {

    private static final HikariDataSource DATA_SOURCE;

    static {
        HikariConfig cfg = new HikariConfig();
        cfg.setJdbcUrl("jdbc:postgresql://localhost:5432/shop");
        cfg.setUsername(System.getenv("DB_USER"));
        cfg.setPassword(System.getenv("DB_PASS"));

        // pool tuning
        cfg.setMaximumPoolSize(10);          // max concurrent connections
        cfg.setMinimumIdle(2);               // keep 2 alive when quiet
        cfg.setConnectionTimeout(30_000);    // 30 s to acquire from pool
        cfg.setIdleTimeout(600_000);         // 10 min before idle conn is closed
        cfg.setMaxLifetime(1_800_000);       // 30 min hard cap (must be < DB wait_timeout)
        cfg.setConnectionTestQuery("SELECT 1"); // validation query

        DATA_SOURCE = new HikariDataSource(cfg);
    }

    public static DataSource get() { return DATA_SOURCE; }
}

Every caller then borrows and returns connections through the pool:

try (Connection conn = DataSourceFactory.get().getConnection()) {
    // execute queries
}   // conn.close() RETURNS the connection to the pool, not to the OS

Always call close() (or use try-with-resources). With a pool, close() does not destroy the TCP socket — it marks the connection as available again. Forgetting it starves the pool just as badly as a real leak.

DriverManager vs DataSource — When to Use Which

DriverManager: scripts, one-off tools, unit tests that spin up a single connection. Simple to set up, no dependencies beyond the driver JAR.
DataSource (pooled): every server-side application, every service that handles more than one request at a time. Lower latency, configurable limits, health checking, graceful shutdown.

In practice, frameworks like Spring Boot configure a pooled DataSource automatically from application.properties. Understanding what they set up — HikariCP by default — is what lets you tune and troubleshoot it.

Summary

DriverManager is your quick-start tool: supply a URL, username, and password and get a connection. For any real workload, replace it with a pooled DataSource (HikariCP is the standard choice). Both expose a Connection object that you use identically from this point forward — always inside a try-with-resources block. In the next lesson you will use that connection to execute SQL statements.