JDBC is the standard way Java talks to a relational database. Even if you use JPA or Spring Data, JDBC is the layer underneath, and understanding it explains a great deal of what those tools do for you. This guide covers connecting, querying safely, transactions, and the resource handling that prevents connection leaks.
Adding a driver#
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>42.7.3</version>
</dependency>
Older tutorials call Class.forName("org.postgresql.Driver") first. That has not been necessary since JDBC 4 — drivers register themselves when they are on the classpath.
Connecting#
String url = "jdbc:postgresql://localhost:5432/shop";
String user = System.getenv("DB_USER");
String password = System.getenv("DB_PASSWORD");
try (Connection conn = DriverManager.getConnection(url, user, password)) {
System.out.println("Connected: " + conn.getCatalog());
} catch (SQLException e) {
System.err.println("Could not connect: " + e.getMessage());
}
Credentials come from the environment, never from source. The connection URL format varies by database:
jdbc:postgresql://host:5432/database
jdbc:mysql://host:3306/database
jdbc:sqlserver://host:1433;databaseName=db
jdbc:sqlite:/path/to/file.db
jdbc:h2:mem:testdb
Querying safely#
String sql = "SELECT id, name, price FROM products WHERE price < ? AND active = ?";
try (Connection conn = DriverManager.getConnection(url, user, password);
PreparedStatement stmt = conn.prepareStatement(sql)) {
stmt.setBigDecimal(1, new BigDecimal("20.00"));
stmt.setBoolean(2, true);
try (ResultSet rs = stmt.executeQuery()) {
while (rs.next()) {
System.out.printf("%d %s %s%n",
rs.getInt("id"), rs.getString("name"), rs.getBigDecimal("price"));
}
}
}
Parameters are numbered from 1, not 0 — a small detail that catches everyone once.
try-with-resources#
Connections, statements and result sets all hold operating system resources. Leaking them exhausts the database’s connection limit, and the symptom — an application that works for an hour and then stops — is confusing.
// Everything in the brackets is closed automatically,
// in reverse order, whether or not an exception is thrown
try (Connection conn = dataSource.getConnection();
PreparedStatement stmt = conn.prepareStatement(sql);
ResultSet rs = stmt.executeQuery()) {
while (rs.next()) { }
}
Inserting and getting the generated key#
String sql = "INSERT INTO products (name, price) VALUES (?, ?)";
try (Connection conn = dataSource.getConnection();
PreparedStatement stmt =
conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
stmt.setString(1, "Notebook");
stmt.setBigDecimal(2, new BigDecimal("4.99"));
stmt.executeUpdate();
try (ResultSet keys = stmt.getGeneratedKeys()) {
if (keys.next()) {
System.out.println("New id: " + keys.getLong(1));
}
}
}
Transactions#
try (Connection conn = dataSource.getConnection()) {
conn.setAutoCommit(false);
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, amount);
debit.setLong(2, fromId);
debit.executeUpdate();
credit.setBigDecimal(1, amount);
credit.setLong(2, toId);
credit.executeUpdate();
conn.commit();
} catch (SQLException e) {
conn.rollback();
throw e;
}
}
By default each statement commits on its own. Turning autocommit off is what makes the two updates one atomic operation — either both happen or neither does.
Batch inserts#
try (Connection conn = dataSource.getConnection();
PreparedStatement stmt = conn.prepareStatement(
"INSERT INTO products (name, price) VALUES (?, ?)")) {
conn.setAutoCommit(false);
int count = 0;
for (Product p : products) {
stmt.setString(1, p.name());
stmt.setBigDecimal(2, p.price());
stmt.addBatch();
if (++count % 1000 == 0) {
stmt.executeBatch();
}
}
stmt.executeBatch();
conn.commit();
}
Inserting ten thousand rows one at a time means ten thousand round trips. Batching in groups of a thousand typically turns minutes into seconds.
Connection pooling#
Opening a connection is expensive — a network handshake and authentication. A pool keeps a set of them open and hands them out:
HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:postgresql://localhost:5432/shop");
config.setUsername(System.getenv("DB_USER"));
config.setPassword(System.getenv("DB_PASSWORD"));
config.setMaximumPoolSize(10);
config.setConnectionTimeout(5000);
DataSource dataSource = new HikariDataSource(config);
Calling close() on a pooled connection returns it to the pool rather than closing it, so try-with-resources is still exactly right. Never use DriverManager directly in a server application.
Handling nulls#
int quantity = rs.getInt("quantity");
if (rs.wasNull()) {
// the column was actually NULL, not zero
}
// Cleaner for nullable numbers
Integer quantity = rs.getObject("quantity", Integer.class);
getInt returns 0 for a NULL column, which is indistinguishable from a genuine zero. getObject with a wrapper type returns null properly.
When to move up a layer#
| Use | When |
|---|---|
| Plain JDBC | a handful of queries, or full control over SQL |
| Spring JdbcTemplate | you want the boilerplate gone but keep the SQL |
| JPA / Hibernate | object mapping, relationships, change tracking |
| jOOQ | type-safe SQL checked at compile time |
JPA is the default in Spring applications, but knowing JDBC underneath is what lets you diagnose a slow query or an N+1 problem when the abstraction misbehaves.
Questions people ask#
Do I still need Class.forName?
No. Drivers register automatically since JDBC 4. You will see it in older code and can safely delete it.
Why does my application run out of connections?
Somewhere a connection is not being closed. Use try-with-resources everywhere, and set a leak-detection threshold in your pool configuration to find the offending code.
Should I use Statement or PreparedStatement?
PreparedStatement, always — even without parameters. It prevents injection and the database can cache the execution plan.
How do I see the SQL that actually ran?
Turn on statement logging in the driver or the database. With Spring, set the JPA show-sql property. Reading the real SQL is the fastest way to diagnose a slow page.
Where to go next#
- Working with databases — SQL fundamentals.
- Java security — injection and secrets in depth.
- Java persistence with Hibernate — the layer above.