Java JDBC Connection Interface
The Java JDBC Connection interface (java.sql.Connection) represents one open session with a database. Besides being the object you get from DriverManager.getConnection(), it is the factory for statements, the place where transactions are committed or rolled back, and the doorway to information about the database itself.
In this lesson you will explore the most important Connection methods: creating statements, controlling auto-commit, committing and rolling back transactions, using savepoints, reading isolation levels, inspecting the database with DatabaseMetaData, and closing connections safely. The lesson ends with an order service that keeps stock and orders consistent.
Connection Is an Interface, Not a Class
You never write new Connection(). Connection is an interface, and the JDBC driver provides the class that implements it. Your code stays the same whichever driver is behind it.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class WhatIsAConnection {
public static void main(String[] args) throws SQLException {
try (Connection connection = DriverManager.getConnection("jdbc:h2:mem:shop", "sa", "")) {
System.out.println("Declared type: java.sql.Connection");
System.out.println("Actual class: " + connection.getClass().getName());
System.out.println("Is a Connection? " + (connection instanceof Connection));
}
}
}
Output:
Declared type: java.sql.Connection
Actual class: org.h2.jdbc.JdbcConnection
Is a Connection? true
How it works:
DriverManagerreturned an object of the H2 classorg.h2.jdbc.JdbcConnection.- Because your variable has the type
Connection, you only use the methods defined by the interface. With MySQL the actual class would becom.mysql.cj.jdbc.ConnectionImpl, and the code would not change.
Java JDBC Connection Methods Overview
| Group | Method | What it does |
|---|---|---|
| Statements | createStatement() | Creates a Statement for fixed SQL |
| Statements | prepareStatement(sql) | Creates a PreparedStatement with ? parameters |
| Statements | prepareCall(sql) | Creates a CallableStatement for stored procedures |
| Transactions | setAutoCommit(boolean) / getAutoCommit() | Turns automatic commits on or off |
| Transactions | commit() / rollback() | Saves or undoes the current transaction |
| Transactions | setSavepoint(name) / rollback(savepoint) | Partial rollback inside a transaction |
| Transactions | setTransactionIsolation(level) | Controls what concurrent transactions can see |
| Information | getMetaData() | Returns DatabaseMetaData about the database and driver |
| Information | getSchema() / getCatalog() | Current schema and catalog (database) |
| Settings | setReadOnly(boolean) | Hint that the connection will only read |
| State | isValid(seconds) | Checks that the connection still works |
| State | close() / isClosed() | Closes the connection / checks if it was closed |
Creating Statements from a Connection
A connection creates the objects that run SQL. Use createStatement() for fixed SQL and prepareStatement() whenever values come from variables or users.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class CreatingStatements {
public static void main(String[] args) throws SQLException {
try (Connection connection = DriverManager.getConnection("jdbc:h2:mem:shop", "sa", "")) {
// Statement: fixed SQL with no variable parts
try (Statement statement = connection.createStatement()) {
statement.executeUpdate("CREATE TABLE customers (id INT PRIMARY KEY, name VARCHAR(50), city VARCHAR(50))");
statement.executeUpdate("INSERT INTO customers VALUES (1, 'Asha', 'Chennai'), (2, 'Ravi', 'Madurai'), (3, 'Meena', 'Chennai')");
}
// PreparedStatement: the city is a parameter, not glued into the SQL
String city = "Chennai";
try (PreparedStatement query = connection.prepareStatement("SELECT name FROM customers WHERE city = ? ORDER BY name")) {
query.setString(1, city);
try (ResultSet rows = query.executeQuery()) {
while (rows.next()) {
System.out.println(city + ": " + rows.getString("name"));
}
}
}
}
}
}
Output:
Chennai: Asha
Chennai: Meena
How it works:
- One connection created two different statement objects. Each is closed by its own try-with-resources block, while the connection stays open.
prepareStatement()sends the SQL with a?placeholder, andsetString(1, city)fills in the first placeholder. This prevents SQL injection, which the Java JDBC Statement lesson explains.
Auto-Commit, commit() and rollback()
By default a new connection is in auto-commit mode: every statement is saved permanently as soon as it finishes. When several changes must succeed or fail together, turn auto-commit off, run the statements, then call commit() to save them all or rollback() to undo them all.
This example moves money between two accounts. A CHECK constraint forbids negative balances, so a transfer that is too large fails halfway.
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class CommitAndRollback {
static void transfer(Connection connection, int fromId, int toId, BigDecimal amount) throws SQLException {
connection.setAutoCommit(false); // start a transaction
try (PreparedStatement deposit = connection.prepareStatement("UPDATE accounts SET balance = balance + ? WHERE id = ?");
PreparedStatement withdraw = connection.prepareStatement("UPDATE accounts SET balance = balance - ? WHERE id = ?")) {
deposit.setBigDecimal(1, amount);
deposit.setInt(2, toId);
deposit.executeUpdate(); // step 1 succeeds
withdraw.setBigDecimal(1, amount);
withdraw.setInt(2, fromId);
withdraw.executeUpdate(); // step 2 may break the CHECK constraint
connection.commit(); // both steps saved together
System.out.println("Transfer of " + amount + ": committed");
} catch (SQLException e) {
connection.rollback(); // undo step 1 as well
System.out.println("Transfer of " + amount + ": rolled back (SQLState class " + e.getSQLState().substring(0, 2) + ")");
} finally {
connection.setAutoCommit(true); // back to the default mode
}
}
static void printBalances(Connection connection) throws SQLException {
try (Statement statement = connection.createStatement();
ResultSet rows = statement.executeQuery("SELECT owner, balance FROM accounts ORDER BY id")) {
while (rows.next()) {
System.out.println(" " + rows.getString("owner") + ": " + rows.getBigDecimal("balance"));
}
}
}
public static void main(String[] args) throws SQLException {
try (Connection connection = DriverManager.getConnection("jdbc:h2:mem:bank", "sa", "");
Statement statement = connection.createStatement()) {
System.out.println("Auto-commit by default? " + connection.getAutoCommit());
statement.executeUpdate("CREATE TABLE accounts (id INT PRIMARY KEY, owner VARCHAR(30), "
+ "balance DECIMAL(10, 2) NOT NULL CHECK (balance >= 0))");
statement.executeUpdate("INSERT INTO accounts VALUES (1, 'Asha', 1000.00), (2, 'Ravi', 200.00)");
transfer(connection, 1, 2, new BigDecimal("300.00"));
printBalances(connection);
transfer(connection, 1, 2, new BigDecimal("5000.00"));
printBalances(connection);
}
}
}
Output:
Auto-commit by default? true
Transfer of 300.00: committed
Asha: 700.00
Ravi: 500.00
Transfer of 5000.00: rolled back (SQLState class 23)
Asha: 700.00
Ravi: 500.00
How it works:
setAutoCommit(false)starts a transaction. Nothing is permanent untilcommit()is called.- The first transfer runs both updates and commits them, so the balances change.
- In the second transfer, the deposit succeeds, but the withdrawal would make Asha's balance negative. The database rejects it with SQLState class
23(integrity constraint violation). rollback()undoes the deposit that already ran, so Ravi does not receive money that never left Asha's account.- The
finallyblock restores auto-commit, leaving the connection in its normal state for the next caller.
Note: If you close a connection while a transaction is open, the result depends on the driver: some commit, most roll back. Always call
commit()orrollback()yourself.
Savepoints: Rolling Back Part of a Transaction
A savepoint marks a point inside a transaction. rollback(savepoint) undoes only the work done after that mark and keeps everything before it.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Savepoint;
import java.sql.Statement;
public class SavepointExample {
public static void main(String[] args) throws SQLException {
try (Connection connection = DriverManager.getConnection("jdbc:h2:mem:events", "sa", "");
Statement statement = connection.createStatement()) {
statement.executeUpdate("CREATE TABLE log (message VARCHAR(50))");
connection.setAutoCommit(false);
statement.executeUpdate("INSERT INTO log VALUES ('order received')");
Savepoint afterOrder = connection.setSavepoint("afterOrder");
statement.executeUpdate("INSERT INTO log VALUES ('coupon applied')");
statement.executeUpdate("INSERT INTO log VALUES ('coupon expired!')");
connection.rollback(afterOrder); // undo only the coupon rows
statement.executeUpdate("INSERT INTO log VALUES ('paid full price')");
connection.commit();
try (ResultSet rows = statement.executeQuery("SELECT message FROM log")) {
while (rows.next()) {
System.out.println(rows.getString("message"));
}
}
System.out.println("Savepoint name: " + afterOrder.getSavepointName());
}
}
}
Output:
order received
paid full price
Savepoint name: afterOrder
How it works:
setSavepoint("afterOrder")marks the position after the first insert.- The two coupon rows are inserted after the savepoint, so
rollback(afterOrder)removes them while keeping "order received". - The transaction continues normally: another insert, then
commit()saves the final result.
Transaction Isolation Levels
When many connections work at the same time, the isolation level decides how much one transaction can see of another's unfinished work. Connection defines a constant for each level.
| Constant | Value | Dirty reads | Non-repeatable reads | Phantom reads |
|---|---|---|---|---|
TRANSACTION_READ_UNCOMMITTED | 1 | Possible | Possible | Possible |
TRANSACTION_READ_COMMITTED | 2 | Prevented | Possible | Possible |
TRANSACTION_REPEATABLE_READ | 4 | Prevented | Prevented | Possible |
TRANSACTION_SERIALIZABLE | 8 | Prevented | Prevented | Prevented |
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Map;
public class IsolationLevels {
static final Map<Integer, String> NAMES = Map.of(
Connection.TRANSACTION_READ_UNCOMMITTED, "READ_UNCOMMITTED",
Connection.TRANSACTION_READ_COMMITTED, "READ_COMMITTED",
Connection.TRANSACTION_REPEATABLE_READ, "REPEATABLE_READ",
Connection.TRANSACTION_SERIALIZABLE, "SERIALIZABLE");
public static void main(String[] args) throws SQLException {
try (Connection connection = DriverManager.getConnection("jdbc:h2:mem:iso", "sa", "")) {
System.out.println("Default level: " + NAMES.get(connection.getTransactionIsolation()));
connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
System.out.println("New level: " + NAMES.get(connection.getTransactionIsolation()));
}
}
}
Output:
Default level: READ_COMMITTED
New level: SERIALIZABLE
How it works:
getTransactionIsolation()returns anint, so the map turns it into a readable name.- H2, PostgreSQL, Oracle and SQL Server default to
READ_COMMITTED; MySQL's InnoDB defaults toREPEATABLE_READ. - Stricter levels give safer results but can make transactions wait for or block each other. Change the level before starting a transaction, not in the middle of one.
Reading Database Information with getMetaData()
getMetaData() returns a DatabaseMetaData object. It describes the database product, the driver and the objects stored in the database, such as tables and columns. Tools like database browsers and migration libraries are built on it.
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class ConnectionMetadata {
public static void main(String[] args) throws SQLException {
try (Connection connection = DriverManager.getConnection("jdbc:h2:mem:store", "sa", "");
Statement statement = connection.createStatement()) {
statement.executeUpdate("CREATE TABLE customers (id INT PRIMARY KEY, name VARCHAR(50))");
statement.executeUpdate("CREATE TABLE orders (id INT PRIMARY KEY, customer_id INT, total DECIMAL(10, 2))");
DatabaseMetaData meta = connection.getMetaData();
System.out.println("Product: " + meta.getDatabaseProductName() + " " + meta.getDatabaseMajorVersion());
System.out.println("User: " + meta.getUserName());
System.out.println("Schema: " + connection.getSchema());
System.out.println("Supports transactions? " + meta.supportsTransactions());
System.out.println("Tables:");
try (ResultSet tables = meta.getTables(null, connection.getSchema(), "%", null)) {
while (tables.next()) {
System.out.println(" - " + tables.getString("TABLE_NAME"));
}
}
}
}
}
Output:
Product: H2 2
User: SA
Schema: PUBLIC
Supports transactions? true
Tables:
- CUSTOMERS
- ORDERS
How it works:
getMetaData()reads information without running your own SQL.getTables(catalog, schema, namePattern, types)returns aResultSetwith one row per table."%"matches every name, andnullfor the types means "any type". Limiting the search to the current schema (PUBLIC) leaves out the database's own system tables.- H2 stores unquoted names in upper case, so the tables appear as
CUSTOMERSandORDERS.
Closing a Connection
close() releases the connection and any resources it still holds, including statements you forgot to close. After that, every method except isClosed(), isValid() and close() throws SQLException.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class ClosingConnection {
public static void main(String[] args) throws SQLException {
Connection connection = DriverManager.getConnection("jdbc:h2:mem:temp", "sa", "");
connection.close();
connection.close(); // closing twice is allowed and does nothing
System.out.println("isClosed(): " + connection.isClosed());
try {
connection.createStatement();
} catch (SQLException e) {
System.out.println("createStatement() failed: the connection is closed");
}
}
}
Output:
isClosed(): true
createStatement() failed: the connection is closed
How it works:
- Calling
close()on an already closed connection is harmless, as the JDBC specification requires. createStatement()on a closed connection throwsSQLException. Each driver uses its own subclass and message, so catchSQLExceptionrather than a driver-specific type.
Note: A
Connectionis not meant to be shared by several threads at once. Give each thread or each request its own connection, usually from a connection pool.
Real-World Example: An Order Service with Transactions
An online shop must never create an order without reducing stock, or reduce stock without an order. This service does both inside one transaction and rolls back when there is not enough stock.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class OrderService {
private final Connection connection;
OrderService(Connection connection) {
this.connection = connection;
}
/** Places an order and reduces stock; returns true if the order was saved. */
boolean placeOrder(String customer, int productId, int quantity) throws SQLException {
boolean previousAutoCommit = connection.getAutoCommit();
connection.setAutoCommit(false);
try (PreparedStatement insertOrder = connection.prepareStatement(
"INSERT INTO orders (customer, product_id, quantity) VALUES (?, ?, ?)");
PreparedStatement reduceStock = connection.prepareStatement(
"UPDATE products SET stock = stock - ? WHERE id = ? AND stock >= ?")) {
insertOrder.setString(1, customer);
insertOrder.setInt(2, productId);
insertOrder.setInt(3, quantity);
insertOrder.executeUpdate();
reduceStock.setInt(1, quantity);
reduceStock.setInt(2, productId);
reduceStock.setInt(3, quantity);
int updated = reduceStock.executeUpdate(); // 0 rows = not enough stock
if (updated == 0) {
connection.rollback();
System.out.printf("%-6s x%-2d -> REJECTED (not enough stock)%n", customer, quantity);
return false;
}
connection.commit();
System.out.printf("%-6s x%-2d -> PLACED%n", customer, quantity);
return true;
} catch (SQLException e) {
connection.rollback();
throw e;
} finally {
connection.setAutoCommit(previousAutoCommit);
}
}
void printStatus() throws SQLException {
try (Statement statement = connection.createStatement();
ResultSet stock = statement.executeQuery("SELECT stock FROM products WHERE id = 1")) {
stock.next();
System.out.print("Stock left: " + stock.getInt(1));
}
try (Statement statement = connection.createStatement();
ResultSet orders = statement.executeQuery("SELECT COUNT(*) FROM orders")) {
orders.next();
System.out.println(", orders saved: " + orders.getInt(1));
}
}
public static void main(String[] args) throws SQLException {
try (Connection connection = DriverManager.getConnection("jdbc:h2:mem:orders", "sa", "");
Statement setup = connection.createStatement()) {
setup.executeUpdate("CREATE TABLE products (id INT PRIMARY KEY, name VARCHAR(50), stock INT NOT NULL)");
setup.executeUpdate("CREATE TABLE orders (id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, "
+ "customer VARCHAR(50), product_id INT, quantity INT)");
setup.executeUpdate("INSERT INTO products VALUES (1, 'Headphones', 5)");
OrderService service = new OrderService(connection);
service.placeOrder("Asha", 1, 2);
service.placeOrder("Ravi", 1, 4);
service.placeOrder("Meena", 1, 3);
service.printStatus();
}
}
}
Output:
Asha x2 -> PLACED
Ravi x4 -> REJECTED (not enough stock)
Meena x3 -> PLACED
Stock left: 0, orders saved: 2
How it works:
- One transaction per order.
placeOrder()turns auto-commit off, runs the insert and the stock update, and then either commits or rolls back. Both changes happen together or not at all. - The database checks the stock.
WHERE id = ? AND stock >= ?only matches when enough stock exists. If it matches no row,executeUpdate()returns0, and the method rolls back the order insert. - Ravi's order is undone completely. His order row was inserted, but the rollback removes it, so only two orders are saved even though three were attempted.
- Unexpected errors also roll back. The
catchblock rolls back for anySQLExceptionand re-throws it so the caller knows something went wrong. - The connection is left as it was found. The
finallyblock restores the previous auto-commit setting, which matters when the same connection is reused. - Identity column.
GENERATED BY DEFAULT AS IDENTITYlets the database number the orders automatically. This standard SQL syntax works in H2 and PostgreSQL; MySQL usesAUTO_INCREMENTinstead.
To review how connections are opened and configured, see Java Database Connection. The next lesson, Java JDBC Statement, looks closely at the objects a connection creates.