JDBC Tutorial: Learn Database Connectivity from Scratch (2026)
JDBC is the lowest-level Java API for database access, providing direct SQL execution and result set processing. While frameworks like JPA and Spring Data abstract away JDBC, understanding the underlying API is crucial for debugging, performance tuning, and scenarios where ORM overhead is unacceptable. I have used JDBC for high-throughput batch processing systems where every millisecond of object-materialization overhead mattered.
This tutorial covers connection management, statement types, result set processing, transactions, batch operations, and connection pooling. You will gain a deep understanding of what frameworks do under the hood.
Connections and Drivers
Database connectivity starts with loading a driver and opening a connection. DriverManager provides a basic connection factory, but production applications should use a connection pool like HikariCP. The Driver class auto-registers via SPI in JDBC 4+, so Class.forName() is no longer necessary.
Connection URL format varies by database: jdbc:postgresql://localhost:5432/mydb for PostgreSQL, jdbc:mysql://localhost:3306/mydb for MySQL. Include connection timeout, SSL mode, and schema parameters in the URL. Always close connections in finally blocks or use try-with-resources.
String url = "jdbc:postgresql://localhost:5432/orders";
String user = "app_user";
String password = System.getenv("DB_PASSWORD");
Properties props = new Properties();
props.setProperty("user", user);
props.setProperty("password", password);
props.setProperty("ssl", "require");
props.setProperty("connectTimeout", "5");
try (Connection conn = DriverManager.getConnection(url, props)) {
System.out.println("Connected: " + conn.getSchema());
}Statement and PreparedStatement
Statement executes static SQL strings. PreparedStatement precompiles SQL with parameter placeholders (?), preventing SQL injection and improving performance for repeated executions. Always use PreparedStatement for queries involving user input — never concatenate values into SQL strings.
JDBC parameter indexes start at 1. Use setString, setInt, setObject for parameters. For streaming large data, setFetchSize to control how many rows the driver fetches per database round trip. A fetch size of 100 to 1000 is reasonable for most workloads.
String sql = "SELECT id, name, email FROM customers WHERE email = ? AND active = ?";
try (PreparedStatement stmt = conn.prepareStatement(sql)) {
stmt.setString(1, "user@example.com");
stmt.setBoolean(2, true);
try (ResultSet rs = stmt.executeQuery()) {
while (rs.next()) {
long id = rs.getLong("id");
String name = rs.getString("name");
// process row
}
}
}
// Batch insert
String insertSql = "INSERT INTO orders (customer_id, total) VALUES (?, ?)";
try (PreparedStatement stmt = conn.prepareStatement(insertSql)) {
for (Order order : orders) {
stmt.setLong(1, order.customerId());
stmt.setBigDecimal(2, order.total());
stmt.addBatch();
}
int[] results = stmt.executeBatch();
}ResultSet Processing
ResultSet represents the query result as a cursor. next() advances to the next row and returns false when exhausted. Access columns by name (rs.getString("email")) or index (rs.getString(3)). Column indexes are 1-based and less readable but slightly faster.
Scrollable/updatable result sets are created with additional parameters in createStatement or prepareStatement, but they consume more database resources. For most applications, forward-only read-only result sets with streaming are sufficient.
String sql = "SELECT id, name, email, created_at FROM customers WHERE active = true";
try (Statement stmt = conn.createStatement()) {
stmt.setFetchSize(500); // Stream results
try (ResultSet rs = stmt.executeQuery(sql)) {
while (rs.next()) {
Customer customer = new Customer(
rs.getLong("id"),
rs.getString("name"),
rs.getString("email"),
rs.getTimestamp("created_at").toLocalDateTime()
);
customers.add(customer);
}
}
}Transaction Management
JDBC transactions begin implicitly when auto-commit is disabled (conn.setAutoCommit(false)). Call conn.commit() to make changes permanent or conn.rollback() to revert. Always wrap transactional work in try-catch-finally to ensure rollback on failure and restore auto-commit in finally.
Transaction isolation levels (READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE) control visibility of concurrent changes. PostgreSQL defaults to READ_COMMITTED, which balances consistency and performance. Savepoints allow partial rollback within a transaction without aborting the entire unit of work.
try (Connection conn = dataSource.getConnection()) {
conn.setAutoCommit(false);
conn.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
try {
updateInventory(conn, productId, -quantity);
insertOrder(conn, order);
conn.commit();
} catch (SQLException e) {
conn.rollback();
throw new DataAccessException("Order creation failed", e);
} finally {
conn.setAutoCommit(true);
}
}Connection Pooling with HikariCP
Opening a new database connection for every request is expensive. Connection pools maintain a set of ready-to-use connections, dramatically reducing latency. HikariCP is the fastest and most reliable pooling library for JDBC. Configure minimumIdle, maximumPoolSize, connectionTimeout, and idleTimeout based on expected concurrency.
Always close connections (they return to the pool rather than being destroyed). A pool size of 10 to 30 handles hundreds of concurrent requests on modern databases. Monitor pool metrics — wait time growth signals insufficient pool size or slow queries.
HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:postgresql://localhost:5432/orders");
config.setUsername("app");
config.setPassword(System.getenv("DB_PASSWORD"));
config.setMaximumPoolSize(20);
config.setMinimumIdle(5);
config.setConnectionTimeout(3_000);
config.setIdleTimeout(600_000);
config.setMaxLifetime(1_800_000);
config.addDataSourceProperty("cachePrepStmts", "true");
config.addDataSourceProperty("prepStmtCacheSize", "250");
DataSource dataSource = new HikariDataSource(config);
try (Connection conn = dataSource.getConnection()) {
// use connection
}Metadata and Database Introspection
DatabaseMetaData provides information about the connected database — tables, columns, primary keys, foreign keys, and supported SQL features. Use it to build generic database tools, schema migration validators, or ORM-free data browsers. Column metadata includes name, type, nullable status, and column size.
ResultSetMetaData describes the columns in a query result. This is useful for dynamic report generators or tools that display query results without knowing the schema in advance. Get column count, names, and types at runtime.
DatabaseMetaData dbMeta = conn.getMetaData();
System.out.println("DB: " + dbMeta.getDatabaseProductName());
System.out.println("Version: " + dbMeta.getDatabaseProductVersion());
// List tables
ResultSet tables = dbMeta.getTables(null, "public", "%", new String[]{"TABLE"});
while (tables.next()) {
System.out.println("Table: " + tables.getString("TABLE_NAME"));
}
// Query metadata
PreparedStatement stmt = conn.prepareStatement("SELECT * FROM orders WHERE 1=0");
ResultSetMetaData rsMeta = stmt.getMetaData();
for (int i = 1; i <= rsMeta.getColumnCount(); i++) {
System.out.println(rsMeta.getColumnName(i) + " (" +
rsMeta.getColumnTypeName(i) + ")");
}Frequently Asked Questions
Why should I use PreparedStatement instead of Statement?
PreparedStatement prevents SQL injection by escaping parameters automatically, precompiles SQL for better performance on repeated execution, and handles binary data and date/time types correctly. Plain Statement should only be used for DDL or truly dynamic SQL without user input.
What is the difference between executeQuery, executeUpdate, and execute?
executeQuery returns a ResultSet (SELECT). executeUpdate returns the number of affected rows (INSERT, UPDATE, DELETE, DDL). execute returns boolean and handles any SQL type — use when you do not know the statement type at compile time.
How do I handle SQLException properly?
SQLException is a checked exception. It chains multiple exceptions via getNextException. In production, catch it at the DAO boundary and wrap it in an unchecked DataAccessException. Always log the SQLState and vendor-specific error code for diagnostics.
What is the difference between JDBC and JPA?
JDBC is a low-level API where you write SQL and process ResultSets manually. JPA is an ORM specification that maps objects to tables and generates SQL. JDBC offers full control and better performance for bulk operations; JPA increases productivity for standard CRUD.
Originally published on Ayodhyyya. Last updated June 1, 2026.