Navigation

Java jdbc 7 min read

Connection Interface

The java.sql.Connection interface is your live link to a database. Every SQL statement you execute, every transaction you manage, and every piece of database metadata you read all flows through a Connection object. Understanding what Connection can do — beyond just opening and closing it — will make you a significantly more effective JDBC developer.

What Is the Connection Interface?

Connection is part of the java.sql package. You never create a Connection directly; instead, you obtain one from DriverManager.getConnection() or from a DataSource (used by connection pools like HikariCP). The underlying driver returns a concrete implementation of the interface that wraps a real network socket to your database.

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

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

        try (Connection conn = DriverManager.getConnection(url, user, pass)) {
            System.out.println("Connected: " + conn.getCatalog());
            System.out.println("Read-only: " + conn.isReadOnly());
            System.out.println("Auto-commit: " + conn.getAutoCommit());
        }
    }
}

Output:

Connected: mydb
Read-only: false
Auto-commit: true

Note: Connection implements AutoCloseable, so wrapping it in try-with-resources guarantees it is closed even when an exception is thrown — preventing costly connection leaks.

Creating Statements

The most common job of a Connection is producing statement objects to carry your SQL to the database. There are three types:

Factory method	Returns	When to use
`conn.createStatement()`	`Statement`	One-off queries with no user input
`conn.prepareStatement(sql)`	`PreparedStatement`	Any query with parameters (safer, faster)
`conn.prepareCall(sql)`	`CallableStatement`	Stored procedures

Statement

import java.sql.*;

try (Connection conn = DriverManager.getConnection(url, user, pass);
     Statement stmt = conn.createStatement();
     ResultSet rs   = stmt.executeQuery("SELECT id, name FROM users")) {

    while (rs.next()) {
        System.out.println(rs.getInt("id") + " - " + rs.getString("name"));
    }
}

PreparedStatement

Use PreparedStatement whenever your SQL includes user-supplied values. The driver sends the SQL template and the parameters separately, eliminating SQL injection.

String sql = "SELECT id, name FROM users WHERE age > ?";

try (Connection conn = DriverManager.getConnection(url, user, pass);
     PreparedStatement ps = conn.prepareStatement(sql)) {

    ps.setInt(1, 25);
    try (ResultSet rs = ps.executeQuery()) {
        while (rs.next()) {
            System.out.println(rs.getInt("id") + " - " + rs.getString("name"));
        }
    }
}

CallableStatement

// Call a stored procedure: CALL get_user_by_id(?)
try (Connection conn = DriverManager.getConnection(url, user, pass);
     CallableStatement cs = conn.prepareCall("{call get_user_by_id(?)}")) {

    cs.setInt(1, 42);
    try (ResultSet rs = cs.executeQuery()) {
        if (rs.next()) {
            System.out.println(rs.getString("name"));
        }
    }
}

Managing Transactions

By default, every SQL statement is immediately and automatically committed — this is called auto-commit mode. For operations that must succeed or fail as a unit, you need to take control of transaction boundaries.

Disabling Auto-Commit

try (Connection conn = DriverManager.getConnection(url, user, pass)) {
    conn.setAutoCommit(false);   // start manual transaction mode

    try (PreparedStatement debit  = conn.prepareStatement(
                "UPDATE accounts SET balance = balance - ? WHERE id = ?");
         PreparedStatement credit = conn.prepareStatement(
                "UPDATE accounts SET balance = balance + ? WHERE id = ?")) {

        debit.setBigDecimal(1, new java.math.BigDecimal("500.00"));
        debit.setInt(2, 1);
        debit.executeUpdate();

        credit.setBigDecimal(1, new java.math.BigDecimal("500.00"));
        credit.setInt(2, 2);
        credit.executeUpdate();

        conn.commit();   // both updates OK — make permanent
        System.out.println("Transfer committed.");

    } catch (SQLException e) {
        conn.rollback(); // something failed — undo everything
        System.err.println("Transfer rolled back: " + e.getMessage());
        throw e;
    }
}

Warning: Once you call conn.setAutoCommit(false), never forget to call commit() or rollback() before closing the connection. Some drivers auto-rollback on close, but this is not guaranteed across all vendors.

Savepoints

Savepoints let you roll back to a specific point within a transaction without discarding all your work.

try (Connection conn = DriverManager.getConnection(url, user, pass)) {
    conn.setAutoCommit(false);

    try (Statement stmt = conn.createStatement()) {
        stmt.executeUpdate("INSERT INTO orders (product, qty) VALUES ('Widget', 10)");

        Savepoint sp = conn.setSavepoint("after_first_insert");

        stmt.executeUpdate("INSERT INTO orders (product, qty) VALUES ('Gadget', 5)");

        // Second insert caused a problem — roll back just that part
        conn.rollback(sp);

        // First insert is still alive — commit it
        conn.commit();
        System.out.println("Only first order saved.");
    }
}

Tip: Savepoints are especially useful in long batch operations where you want to retry or skip a single failed record without rolling back the whole batch.

Transaction Isolation Levels

Isolation levels control how much one transaction can “see” the uncommitted work of another. The Connection interface defines four constants for these levels:

Constant	Dirty Read	Non-Repeatable Read	Phantom Read
`TRANSACTION_READ_UNCOMMITTED`	possible	possible	possible
`TRANSACTION_READ_COMMITTED`	prevented	possible	possible
`TRANSACTION_REPEATABLE_READ`	prevented	prevented	possible
`TRANSACTION_SERIALIZABLE`	prevented	prevented	prevented

try (Connection conn = DriverManager.getConnection(url, user, pass)) {
    // Set REPEATABLE READ (MySQL's default is already REPEATABLE_READ)
    conn.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
    System.out.println("Isolation level: " + conn.getTransactionIsolation());
}

Output:

Isolation level: 4

Note: Higher isolation levels prevent more anomalies but reduce concurrency because they require more locking. Most applications do fine with READ_COMMITTED. Use SERIALIZABLE only for financial-critical operations where correctness outweighs throughput.

Connection Metadata

Connection.getMetaData() returns a DatabaseMetaData object packed with information about the database server, its capabilities, and its schema.

try (Connection conn = DriverManager.getConnection(url, user, pass)) {
    java.sql.DatabaseMetaData meta = conn.getMetaData();

    System.out.println("DB Product : " + meta.getDatabaseProductName());
    System.out.println("DB Version : " + meta.getDatabaseProductVersion());
    System.out.println("Driver     : " + meta.getDriverName());
    System.out.println("Driver ver : " + meta.getDriverVersion());
    System.out.println("JDBC ver   : " + meta.getJDBCMajorVersion()
                                     + "." + meta.getJDBCMinorVersion());
    System.out.println("Max conns  : " + meta.getMaxConnections());
}

Output:

DB Product : MySQL
DB Version : 8.0.36
Driver     : MySQL Connector/J
Driver ver : mysql-connector-j-8.3.0
JDBC ver   : 4.2
Max conns  : 0

Note: A getMaxConnections() result of 0 means the database reports no known limit (or the driver does not expose it), not that zero connections are allowed.

Other Useful Connection Methods

Here is a handy reference of the methods you will encounter day-to-day:

Method	What it does
`conn.getCatalog()`	Returns the current database/catalog name
`conn.setCatalog(name)`	Switches to a different database
`conn.getSchema()`	Returns the current schema (JDBC 4.1+, Java 7)
`conn.isValid(timeout)`	Pings the database; returns `false` if the connection is dead
`conn.isReadOnly()`	Returns `true` if the connection is in read-only mode
`conn.setReadOnly(true)`	Hints to the driver/DB to optimise for reads only
`conn.isClosed()`	Returns `true` after `close()` has been called
`conn.nativeSQL(sql)`	Converts JDBC escape syntax to native SQL for debugging
`conn.createArrayOf(type, elements)`	Creates a SQL `ARRAY` object
`conn.createStruct(type, attrs)`	Creates a SQL structured type

Checking Connection Liveness

try (Connection conn = DriverManager.getConnection(url, user, pass)) {
    if (conn.isValid(2)) {   // wait up to 2 seconds for a ping response
        System.out.println("Connection is alive.");
    } else {
        System.out.println("Connection is dead.");
    }
}

Output:

Connection is alive.

createStatement() Overloads

The full signature of createStatement accepts two parameters that control what kind of ResultSet it produces:

Statement stmt = conn.createStatement(
    ResultSet.TYPE_SCROLL_INSENSITIVE,   // can scroll forward and backward
    ResultSet.CONCUR_UPDATABLE           // can update rows in the result set
);

ResultSet type constants:

Constant	Behaviour
`TYPE_FORWARD_ONLY`	Default — cursor moves forward only
`TYPE_SCROLL_INSENSITIVE`	Can scroll; does not reflect live DB changes
`TYPE_SCROLL_SENSITIVE`	Can scroll; reflects live DB changes (driver-dependent)

Concurrency constants:

Constant	Behaviour
`CONCUR_READ_ONLY`	Default — result set is read-only
`CONCUR_UPDATABLE`	Allows `rs.updateString(...)` / `rs.updateRow()`

Under the Hood

When DriverManager.getConnection() hands you a Connection, the driver has already:

Opened a TCP socket to the database server.
Completed an authentication handshake (e.g. SHA-256 or caching_sha2_password for MySQL 8).
Negotiated session parameters: character set, timezone, and SQL mode.
Allocated a server-side session that holds the current transaction state, temporary tables, and user variables.

That session stays alive — consuming server memory and file descriptors — until you call conn.close(). This is why leaked connections are so harmful at scale.

Auto-commit under the hood: When auto-commit is true (the default), the driver wraps every statement in an implicit BEGIN / COMMIT pair. The database treats each statement as its own tiny transaction. Calling setAutoCommit(false) starts an explicit transaction; the driver sends BEGIN to the server and stops sending COMMIT after each statement.

Connection pools: Opening a TCP connection costs 5–50 ms depending on network conditions. In web applications that handle hundreds of requests per second, re-opening a connection on every request would be catastrophically slow. A connection pool (HikariCP, c3p0, DBCP) maintains a warm pool of pre-opened Connection objects. When you call pool.getConnection(), you receive a wrapper that forwards all real calls to the underlying socket but overrides close() to return the connection to the pool rather than closing the socket. From your code’s perspective, you use Connection exactly the same way.

Tip: Always call conn.close() (or rely on try-with-resources) even when using a connection pool. This is what returns the connection to the pool for the next caller.

DriverManager — how getConnection() finds the right driver and builds a Connection for you
PreparedStatement — the safest way to execute parameterised SQL through a Connection
Transaction Management — deep dive into commit, rollback, savepoints, and isolation levels
ResultSet — reading and navigating the rows returned by your queries
DatabaseMetaData — exploring database capabilities and schema structure via Connection.getMetaData()
Steps to Connect a Database — the full end-to-end JDBC flow from opening to closing a connection

Connection Interface

What Is the Connection Interface?

Creating Statements

Statement

PreparedStatement

CallableStatement

Managing Transactions

Disabling Auto-Commit

Savepoints

Transaction Isolation Levels

Connection Metadata

Other Useful Connection Methods

Checking Connection Liveness

createStatement() Overloads

Under the Hood

Related Topics