JDBC and Database Connectivity — Complete Guide

DodaTech Updated 2026-06-28 6 min read

In this tutorial, you will learn about JDBC and Database Connectivity. We cover key concepts, practical examples, and best practices to help you master this topic.

The Bridge Between Java and Databases

Almost every real-world application needs to store and retrieve data from a database. Java Database Connectivity (JDBC) is the standard Java API that provides this bridge. It defines how Java applications connect to relational databases, execute SQL statements, and process results. Whether you use an ORM like Hibernate or a framework like Spring Data JPA, those libraries are built on top of JDBC. Understanding JDBC at the foundation level gives you the ability to troubleshoot database issues, optimize queries, and work effectively when an ORM is overkill or inappropriate.

JDBC follows a driver-based architecture. Database vendors provide JDBC drivers that implement the standard interfaces. Your application talks to the JDBC API, and the driver translates those calls into the database-specific protocol. This abstraction lets you switch databases (from PostgreSQL to MySQL, for example) by simply swapping the driver JAR and connection URL.

flowchart LR
    App[Java Application] --> JDBC[JDBC API]
    JDBC --> Driver[JDBC Driver]
    Driver --> DB1[(PostgreSQL)]
    Driver --> DB2[(MySQL)]
    Driver --> DB3[(Oracle)]
    JDBC --> CP[Connection Pool
HikariCP]
    CP --> Driver

Setting Up JDBC

Step 1: Add the Driver Dependency

For Maven, add the driver to pom.xml:

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.7.0</version>
</dependency>

For Gradle:

implementation 'org.postgresql:postgresql:42.7.0'

Step 2: Establish a Connection

import java.sql.*;

public class JdbcConnect {
    public static void main(String[] args) {
        String url = "jdbc:postgresql://localhost:5432/mydb";
        String user = "appuser";
        String password = "secret";
        
        try (Connection conn = DriverManager.getConnection(url, user, password)) {
            System.out.println("Connected to database!");
            System.out.println("Catalog: " + conn.getCatalog());
            System.out.println("Driver: " + conn.getMetaData().getDriverName());
        } catch (SQLException e) {
            System.err.println("Connection failed: " + e.getMessage());
        }
    }
}

Output:

Connected to database!
Catalog: mydb
Driver: PostgreSQL JDBC Driver

CRUD Operations with JDBC

Creating a Table

String createTable = """
    CREATE TABLE IF NOT EXISTS users (
        id SERIAL PRIMARY KEY,
        name VARCHAR(100) NOT NULL,
        email VARCHAR(255) UNIQUE NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
    """;

try (Statement stmt = conn.createStatement()) {
    stmt.execute(createTable);
    System.out.println("Table created");
}

Inserting Data (PreparedStatement)

Always use PreparedStatement instead of Statement for parameterized queries. It prevents SQL Injection and handles escaping automatically.

String insert = "INSERT INTO users (name, email) VALUES (?, ?)";

try (PreparedStatement pstmt = conn.prepareStatement(insert,
        Statement.RETURN_GENERATED_KEYS)) {
    pstmt.setString(1, "Alice Johnson");
    pstmt.setString(2, "alice@example.com");
    int affected = pstmt.executeUpdate();
    
    ResultSet keys = pstmt.getGeneratedKeys();
    if (keys.next()) {
        System.out.println("Inserted ID: " + keys.getInt(1));
    }
    System.out.println("Rows affected: " + affected);
}

Output:

Inserted ID: 1
Rows affected: 1

Querying Data

String query = "SELECT id, name, email, created_at FROM users WHERE id = ?";

try (PreparedStatement pstmt = conn.prepareStatement(query)) {
    pstmt.setInt(1, 1);
    ResultSet rs = pstmt.executeQuery();
    
    while (rs.next()) {
        int id = rs.getInt("id");
        String name = rs.getString("name");
        String email = rs.getString("email");
        Timestamp createdAt = rs.getTimestamp("created_at");
        
        System.out.printf("%d: %s (%s) - %s%n", id, name, email, createdAt);
    }
}

Output:

1: Alice Johnson (alice@example.com) - 2026-06-28 10:30:00.0

Updating Data

String update = "UPDATE users SET email = ? WHERE id = ?";

try (PreparedStatement pstmt = conn.prepareStatement(update)) {
    pstmt.setString(1, "alice.johnson@newdomain.com");
    pstmt.setInt(2, 1);
    int affected = pstmt.executeUpdate();
    System.out.println("Updated " + affected + " row(s)");
}

Deleting Data

String delete = "DELETE FROM users WHERE id = ?";

try (PreparedStatement pstmt = conn.prepareStatement(delete)) {
    pstmt.setInt(1, 1);
    int affected = pstmt.executeUpdate();
    System.out.println("Deleted " + affected + " row(s)");
}

Transaction Management

Transactions ensure that a group of operations either all succeed or all fail. JDBC transactions are managed via Connection methods.

public void transferFunds(Connection conn, int fromId, int toId,
        BigDecimal amount) throws SQLException {
    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.setInt(2, fromId);
        credit.setBigDecimal(1, amount);
        credit.setInt(2, toId);
        
        debit.executeUpdate();
        credit.executeUpdate();
        
        conn.commit();
        System.out.println("Transfer successful");
    } catch (SQLException e) {
        conn.rollback();
        System.err.println("Transfer failed, rolled back: " + e.getMessage());
        throw e;
    } finally {
        conn.setAutoCommit(true);
    }
}

Connection Pooling with HikariCP

Creating a new database connection for every request is expensive. Connection pools maintain a pool of reusable connections.

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

public class DataSourceConfig {
    private static HikariDataSource dataSource;
    
    static {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:postgresql://localhost:5432/mydb");
        config.setUsername("appuser");
        config.setPassword("secret");
        config.setMaximumPoolSize(20);
        config.setMinimumIdle(5);
        config.setIdleTimeout(300000);
        config.setConnectionTimeout(10000);
        config.addDataSourceProperty("cachePrepStmts", "true");
        config.addDataSourceProperty("prepStmtCacheSize", "250");
        config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
        
        dataSource = new HikariDataSource(config);
    }
    
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }
}

Common Mistakes

1. Not Closing Resources

Failing to close Connection, Statement, and ResultSet objects causes resource leaks. Always use try-with-resources.

// Wrong: resource leak
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM users");
// stmt and rs are never closed

// Correct: try-with-resources
try (Statement stmt = conn.createStatement();
     ResultSet rs = stmt.executeQuery("SELECT * FROM users")) {
    while (rs.next()) { /* process */ }
}

2. SQL Injection via String Concatenation

// Dangerous: SQL injection vulnerability
String sql = "SELECT * FROM users WHERE name = '" + userName + "'";
Statement stmt = conn.createStatement(sql); // user can inject ' OR '1'='1

// Safe: use PreparedStatement
PreparedStatement pstmt = conn.prepareStatement(
    "SELECT * FROM users WHERE name = ?");
pstmt.setString(1, userName);

3. Ignoring Transaction Boundaries

Every SQL statement is auto-committed by default. Multiple operations that should be atomic need explicit transaction management.

4. Fetching Too Much Data

ResultSet loads all rows into memory by default. For large datasets, set fetch size or use streaming.

stmt.setFetchSize(100); // Fetch 100 rows at a time

5. Hardcoding Credentials

Never hardcode database credentials in source code. Use environment variables, configuration files (.env), or secret management services.

6. Using SELECT *

Explicitly listing columns is faster, more maintainable, and avoids issues when the table schema changes.

Practice Questions

Why is PreparedStatement preferred over Statement for SQL queries?
What happens if you do not call commit() after setting autoCommit to false?
How does connection pooling improve application performance?
What is the difference between executeQuery() and executeUpdate()?
How do you handle SQLException properly in a layered application?

Challenge: Build a batch insert utility that reads a CSV file and inserts 10,000 records into a database table using JDBC batch processing (addBatch() and executeBatch()). Measure and optimize the insertion time.

FAQ

What is the difference between JDBC and an ORM like Hibernate?

JDBC is a low-level API for direct SQL execution. ORMs like Hibernate map Java objects to database tables, generating SQL automatically and providing caching, lazy loading, and change tracking.

How do I handle BLOB and CLOB types with JDBC?

Use PreparedStatement.setBinaryStream() for BLOBs and setCharacterStream() for CLOBs. Read them with ResultSet.getBinaryStream() and getCharacterStream().

Can JDBC work with NoSQL databases?

JDBC is designed for relational databases with SQL support. NoSQL databases typically provide their own Java drivers or use alternative APIs like Spring Data MongoDB.

What is the difference between Type 4 and Type 2 JDBC drivers?

Type 4 (thin) drivers are pure Java and connect directly to the database over the network. Type 2 drivers use native code on the client side and require platform-specific libraries.

How do I debug slow queries in JDBC?

Enable driver logging, set statement timeout, use pg_stat_activity (PostgreSQL) or equivalent monitoring, and analyze execution plans with EXPLAIN ANALYZE.

Mini Project: Database Migration Tool

Build a simple database migration tool in the style of Flyway. The tool should:

Scan a directory for SQL migration files named V<version>__<description>.sql
Track applied migrations in a schema_version table
Apply new migrations in order within a transaction
Report which migrations were applied and skipped

Use JDBC with HikariCP for connection pooling. Support PostgreSQL as the target database.

What's Next

You now have solid database skills. In the next lesson, we will build on this knowledge to create web applications with Servlets and JSP, the traditional Java web stack.

← Previous Java Version Features (8 through 21) Next → Servlets and JSP — Complete Guide

Built by the developers of DodaTech

Doda Browser, DodaZIP & Durga Antivirus Pro

Home Browse Java