Java JDBC Guide: Database Connectivity, CRUD, and Connection Pools
The Java Database Connectivity (JDBC) API is the standard for connecting Java applications to relational databases. Despite the popularity of ORM frameworks like Hibernate, JDBC remains the foundation — every ORM uses JDBC underneath. Understanding JDBC gives you control over query performance, transaction boundaries, and connection management. This guide covers JDBC drivers, DataSource configuration, CRUD operations, prepared statements, connection pooling with HikariCP, transaction management, and integration with JPA.
JDBC Architecture
JDBC consists of two layers: the JDBC API (in java.sql and javax.sql) and JDBC drivers provided by database vendors. The DriverManager class manages a list of registered drivers. When you request a connection, DriverManager iterates through drivers until one accepts the URL.
The DataSource interface (javax.sql) replaces DriverManager in production. It supports connection pooling, distributed transactions, and JNDI lookups. The Oracle JDBC documentation recommends DataSource over DriverManager for all server-side applications.
Drivers and Connection Setup
JDBC drivers come in four types (Mimer JDBC Drivers overview):
- Type 1: JDBC-ODBC bridge (deprecated, removed in Java 8)
- Type 2: Native-API, partially Java (vendor-specific)
- Type 3: Network-protocol, middleware-based
- Type 4: Pure Java, direct socket connection (most common)
Type 4 drivers (PostgreSQL org.postgresql.Driver, MySQL com.mysql.cj.jdbc.Driver, H2 org.h2.Driver) are pure Java and require no native libraries.
// Register driver (not required with modern JDBC 4+ — auto-loaded via SPI)
Class.forName("org.postgresql.Driver");
String url = "jdbc:postgresql://localhost:5432/excellentwiki";
Properties props = new Properties();
props.setProperty("user", "app_user");
props.setProperty("password", "secret");
try (Connection conn = DriverManager.getConnection(url, props)) {
System.out.println("Connected to database");
---DataSource and Connection Pooling
Creating a new connection for every request is expensive. Connection pooling reuses connections, dramatically improving performance. HikariCP is the fastest and most commonly used pool.
HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:postgresql://localhost:5432/excellentwiki");
config.setUsername("app_user");
config.setPassword("secret");
config.setMaximumPoolSize(20);
config.setMinimumIdle(5);
config.setConnectionTimeout(3000);
config.setIdleTimeout(600000);
DataSource ds = new HikariDataSource(config);Brett Wooldridge (HikariCP author) benchmarks show HikariCP processes 2,700+ connections/sec with single-digit millisecond latency, outperforming C3P0, DBCP2, and Tomcat JDBC. The Baeldung HikariCP tutorial recommends setting maximumPoolSize equal to the number of concurrent application threads.
CRUD Operations with PreparedStatement
PreparedStatement prevents SQL injection by separating SQL structure from data. The pre-compiled query plan also improves performance for repeated executions.
// CREATE
public long createArticle(String title, String body) throws SQLException {
String sql = "INSERT INTO articles (title, body) VALUES (?, ?)";
try (Connection conn = dataSource.getConnection();
PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
ps.setString(1, title);
ps.setString(2, body);
ps.executeUpdate();
try (ResultSet rs = ps.getGeneratedKeys()) {
if (rs.next()) return rs.getLong(1);
throw new SQLException("No generated key");
}
}
---
// READ
public Article findById(long id) throws SQLException {
String sql = "SELECT * FROM articles WHERE id = ?";
try (Connection conn = dataSource.getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setLong(1, id);
try (ResultSet rs = ps.executeQuery()) {
if (rs.next()) {
return new Article(rs.getLong("id"), rs.getString("title"), rs.getString("body"));
}
return null;
}
}
---The try-with-resources block ensures Connection, PreparedStatement, and ResultSet are closed automatically. Never close a connection manually while using a pool — the pool manages lifecycle.
Batch Operations
Batching reduces round-trips for bulk operations:
String sql = "INSERT INTO articles (title, body) VALUES (?, ?)";
try (Connection conn = dataSource.getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
for (Article article : articles) {
ps.setString(1, article.title());
ps.setString(2, article.body());
ps.addBatch();
}
int[] results = ps.executeBatch(); // single round-trip
---Transaction Management
JDBC transactions default to auto-commit mode — each statement is an independent transaction. Disable auto-commit for multi-statement transactions:
try (Connection conn = dataSource.getConnection()) {
conn.setAutoCommit(false);
try {
// Deduct from sender
String debitSql = "UPDATE accounts SET balance = balance - ? WHERE id = ?";
try (PreparedStatement ps = conn.prepareStatement(debitSql)) {
ps.setBigDecimal(1, amount);
ps.setLong(2, fromAccount);
ps.executeUpdate();
}
// Credit recipient
String creditSql = "UPDATE accounts SET balance = balance + ? WHERE id = ?";
try (PreparedStatement ps = conn.prepareStatement(creditSql)) {
ps.setBigDecimal(1, amount);
ps.setLong(2, toAccount);
ps.executeUpdate();
}
conn.commit(); // both succeed or both fail
} catch (SQLException e) {
conn.rollback();
throw e;
}
---Use TransactionIsolation levels appropriately: READ_COMMITTED is the default in most databases and pools. SERIALIZABLE prevents phantom reads but reduces concurrency.
ResultSet Mapping
Mapping ResultSet rows to Java objects manually is tedious. Utility libraries like Apache Commons DbUtils reduce boilerplate. Most projects eventually adopt JPA (Hibernate) for production use.
// Manual mapping
public List<Article> findAll() throws SQLException {
List<Article> articles = new ArrayList<>();
String sql = "SELECT * FROM articles ORDER BY id";
try (Connection conn = dataSource.getConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql)) {
while (rs.next()) {
articles.add(new Article(
rs.getLong("id"),
rs.getString("title"),
rs.getString("body"),
rs.getTimestamp("created_at").toLocalDateTime()
));
}
}
return articles;
---Integration with JPA/Hibernate
JDBC is the foundation of JPA. Hibernate, the reference implementation, uses JDBC for all database operations. Configuring HikariCP as Hibernate’s connection pool provider:
<property name="hibernate.hikari.connectionTimeout">3000</property>
<property name="hibernate.hikari.maximumPoolSize">20</property>
<property name="hibernate.hikari.minimumIdle">5</property>JPA sits above JDBC and manages the object-relational mapping automatically. Use JDBC directly when you need fine-grained control over SQL, batch operations, or stored procedure execution. Our Java Enterprise Guide covers JPA in detail.
FAQ
Q: What is the difference between Statement and PreparedStatement?
A: PreparedStatement pre-compiles the SQL, prevents SQL injection, and is faster for repeated executions. Always prefer it over Statement for parameterized queries.
Q: How do I handle BLOBs/CLOBs in JDBC?
A: Use PreparedStatement.setBinaryStream() for BLOBs and setCharacterStream() for CLOBs. Retrieve with ResultSet.getBinaryStream() and getCharacterStream().
Q: Why is connection pooling important? A: Opening a TCP connection to a database takes 10–100ms. Pooling reuses connections, reducing latency and preventing database overload from too many concurrent connections.
Q: Can JDBC work with NoSQL databases? A: Not directly. NoSQL databases (MongoDB, Cassandra) have proprietary APIs. JDBC is designed for relational databases with SQL support.
Q: How do I migrate from JDBC to Spring Data JPA?
A: Create JPA entities matching your tables, define Repository interfaces extending JpaRepository, and configure application.properties with the DataSource. Spring Data JPA generates CRUD operations automatically.
Stored Procedures and Functions
JDBC supports calling stored procedures via CallableStatement:
String sql = "{call get_article_count_by_category(?, ?)}";
try (Connection conn = dataSource.getConnection();
CallableStatement cs = conn.prepareCall(sql)) {
cs.setString(1, categoryName);
cs.registerOutParameter(2, Types.INTEGER);
cs.execute();
int count = cs.getInt(2);
return count;
---For functions returning result sets, use executeQuery() and process the ResultSet. Database-specific features (array types, cursor support, batch error codes) are accessed through vendor extension methods on the Connection or Statement objects.
Metadata and Schema Inspection
JDBC metadata APIs allow runtime discovery of database structure:
DatabaseMetaData meta = conn.getMetaData();
System.out.println("DB: " + meta.getDatabaseProductName());
System.out.println("Version: " + meta.getDatabaseProductVersion());
ResultSet tables = meta.getTables(null, "public", null, new String[]{"TABLE"});
while (tables.next()) {
System.out.println("Table: " + tables.getString("TABLE_NAME"));
---Use metadata for dynamic schema migrations, generic import/export tools, and database comparison utilities.
N+1 Query Prevention
The N+1 query problem occurs when code executes one query to fetch parent records and N additional queries for each child. In JDBC, this happens with manual eager loading. Solutions include JOIN FETCH, batch fetching with IN clauses, and subselect strategies.
Fetch Size and Cursor Control
Setting setFetchSize() on statements controls how many rows are retrieved per database round-trip, significantly reducing network overhead for large result sets. The default fetch size varies by driver — PostgreSQL defaults to 0 (stream all rows), Oracle to 10.
JDBC driver best practices include always validating connections before use (SELECT 1), setting socket timeouts to prevent thread leaks on network partitions, and using setQueryTimeout() on statements to prevent runaway queries. These practices, combined with HikariCP’s built-in connection validation and leak detection, eliminate most database connectivity incidents in production.
Output parameter registration is database-specific: PostgreSQL supports REF_CURSOR via the PG driver, Oracle uses OracleTypes.CURSOR, and MySQL aggregates result sets differently. Always consult the database vendor’s JDBC driver documentation for stored procedure compatibility.
Spring’s JdbcTemplate and NamedParameterJdbcTemplate wrap JDBC’s boilerplate (connection acquisition, statement creation, result set traversal) into concise, exception-safe operations. Under the hood, they use the same DataSource and PreparedStatement APIs — JdbcTemplate is not a replacement for JDBC but a productivity layer that reduces CRUD code by approximately 60% while maintaining full SQL control.
Spring Data JDBC (distinct from Spring Data JPA) provides repository-style data access without JPA’s lazy loading, caching, or dirty checking. It maps aggregate roots to tables directly using the repository pattern — define an interface, get CRUD operations automatically. This simplicity makes it an ideal middle ground between raw JDBC and full JPA. It maps aggregate roots to tables directly and executes SQL for each repository method invocation. This simpler model avoids many JPA pitfalls (N+1 queries, session management) while still eliminating JDBC boilerplate.
RowSet implementations (JdbcRowSet, CachedRowSet, WebRowSet) extend the ResultSet interface with JavaBeans-style properties and event notification. CachedRowSet in particular allows disconnected data access — fetching rows into memory, closing the connection, and performing updates offline before reconnecting to push changes. This pattern is useful for desktop applications and offline-capable mobile backends.
JDBC remains the lowest-level and most performant Java database access API. When query latency is critical (sub-millisecond) or the SQL is highly dynamic, JDBC’s direct control over statement construction, fetch sizes, and transaction isolation provides advantages that ORM abstractions cannot match. Use JDBC for read-only reporting queries, batch ETL pipelines, and database administration tools where ORM overhead is unacceptable.
For authoritative reference, consult the Oracle JDBC Tutorial and High-Performance Java Persistence by Vlad Mihalcea. See also our Java Exception Handling guide for JDBC error management patterns.