Java JDBC: A Complete Guide to Database Connectivity

Learn how to connect Java applications to relational databases using JDBC. This tutorial covers connections, queries, PreparedStatement, SQL injection prevention, transactions, and building a real data access layer — all with working code.

What Is JDBC?

JDBC (Java Database Connectivity) is the standard Java API for connecting to relational databases and executing SQL statements. It is part of the Java SE platform, so you don't need to install anything extra to use it — the core classes live in the java.sql package.

It's important to understand what JDBC is not :

  • JDBC is not a database. It's an API — a set of interfaces and classes that define how to talk to a database.
  • JDBC is not tied to any specific database. The same JDBC code can work with MySQL, PostgreSQL, Oracle, SQLite, or any other relational database — you just change the driver (a small library that translates JDBC calls into the database's native protocol).
  • JDBC is not an ORM (Object-Relational Mapper). It works at the SQL level — you write SQL statements, and JDBC sends them to the database and returns the results. Tools like Hibernate and JPA build on top of JDBC.

Prerequisites

This tutorial assumes you understand basic SQL (SELECT, INSERT, UPDATE, DELETE) and Java fundamentals (classes, exceptions, try-catch). If you haven't covered exception handling yet, you may want to review that first — JDBC relies heavily on it.

Setting Up: Driver and Database

Before writing any Java code, you need two things:

1. A Running Database

For this tutorial, we'll use MySQL because it's the most common database for Java learners. If you're using PostgreSQL or another database, the Java code is almost identical — only the connection URL and driver class name change (we note the differences where relevant).

Create a database and a table to work with. Run this SQL in your MySQL client (mysql command line, MySQL Workbench, DBeaver, etc.):

SQL — Database Setup 
-- Create the database
CREATE DATABASE company_db;

-- Switch to it
USE company_db;

-- Create the employees table
CREATE TABLE employees (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name  VARCHAR(50) NOT NULL,
    email      VARCHAR(100) UNIQUE,
    salary     DECIMAL(10,2),
    department VARCHAR(50)
);

-- Insert some sample data
INSERT INTO employees (first_name, last_name, email, salary, department)
VALUES
    ('Alice',   'Johnson',  'alice@example.com',   75000.00, 'Engineering'),
    ('Bob',     'Smith',    'bob@example.com',     68000.00, 'Marketing'),
    ('Carol',   'Williams', 'carol@example.com',   82000.00, 'Engineering'),
    ('David',   'Brown',    'david@example.com',   71000.00, 'Sales'),
    ('Eve',     'Davis',    'eve@example.com',     95000.00, 'Engineering');

2. The JDBC Driver

The JDBC driver is a JAR file that implements the JDBC interfaces for a specific database. For MySQL, you need the MySQL Connector/J driver. Add it to your project:

Maven — pom.xml 
<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>8.4.0</version>
</dependency>

Other Database Drivers

If you're using a different database, add the corresponding driver and adjust the connection URL (shown in the next section):

  • PostgreSQL: org.postgresql:postgresql:42.7.3
  • SQLite: org.xerial:sqlite-jdbc:3.45.2.0
  • Oracle: com.oracle.database.jdbc:ojdbc11:23.3.0.23.09
  • H2 (embedded, great for testing): com.h2database:h2:2.2.224

Establishing a Connection

The first thing every JDBC program does is open a connection to the database. A Connection object represents a single session with the database — all SQL statements you execute go through it.

BasicConnection.java 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class BasicConnection {

    public static void main(String[] args) {

        // JDBC URL format: jdbc:mysql://host:port/databaseName
        String url = "jdbc:mysql://localhost:3306/company_db";
        String username = "root";
        String password = "your_password";

        try (
            // DriverManager.getConnection() returns a Connection
            Connection conn = DriverManager.getConnection(url, username, password)
        ) {
            if (conn != null) {
                System.out.println("Connected to the database successfully.");
                System.out.println("Database: " + conn.getCatalog());
                System.out.println("Driver: " + conn.getMetaData().getDriverName());
            }
        } catch (SQLException e) {
            System.err.println("Connection failed: " + e.getMessage());
        }
    }
}
Console Output
Connected to the database successfully. Database: company_db Driver: MySQL Connector/J

Connection URL Reference

The JDBC URL format varies by database:

  • MySQL: jdbc:mysql://localhost:3306/company_db
  • PostgreSQL: jdbc:postgresql://localhost:5432/company_db
  • SQLite: jdbc:sqlite:/path/to/company.db (file path) or jdbc:sqlite::memory: (in-memory)
  • H2: jdbc:h2:./company_db (file) or jdbc:h2:mem:company_db (in-memory)

You can append parameters to the URL: jdbc:mysql://localhost:3306/company_db?useSSL=false&serverTimezone=UTC

About try-with-Resources

The try (Connection conn = ...) syntax is called try-with-resources . It automatically calls conn.close() when the try block ends, even if an exception occurs. We use it throughout this tutorial because every JDBC resource ( Connection , Statement , ResultSet ) must be closed when you're done with it. Failing to close connections causes connection leaks — the database runs out of available connections and your application stops working. If you're unfamiliar with this syntax, review exception handling first.

The Core JDBC Workflow

Every database operation in JDBC follows the same pattern. Understanding this pattern is more important than memorizing individual method names:

  1. Open a connection — DriverManager.getConnection(url, user, pass)
  2. Create a statement — conn.createStatement() or conn.prepareStatement(sql)
  3. Execute the SQL — statement.executeQuery() (for SELECT) or statement.executeUpdate() (for INSERT/UPDATE/DELETE)
  4. Process the results — Loop through the ResultSet (for queries) or check the row count (for updates)
  5. Close everything — Handled automatically by try-with-resources

Steps 2–4 are where the actual work happens, and they differ depending on what you're doing. The rest of this tutorial covers each variation in detail.

Executing a Simple Query

Let's retrieve all employees from the database. We'll start with the basic Statement interface (we'll switch to PreparedStatement shortly, which is what you should use in practice).

SimpleQuery.java 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class SimpleQuery {

    public static void main(String[] args) {

        String url = "jdbc:mysql://localhost:3306/company_db";
        String user = "root";
        String pass = "your_password";

        String sql = "SELECT id, first_name, last_name, email, salary, department "
                  + "FROM employees ORDER BY salary DESC";

        // try-with-resources closes Connection, Statement, and ResultSet automatically
        try (Connection conn = DriverManager.getConnection(url, user, pass);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            System.out.println("ID | Name              | Email             | Salary    | Department");
            System.out.println("---+-------------------+-------------------+-----------+------------");

            // ResultSet starts BEFORE the first row.
            // Call next() to advance to each row. It returns false when there are no more rows.
            while (rs.next()) {
                int id = rs.getInt("id");
                String firstName = rs.getString("first_name");
                String lastName = rs.getString("last_name");
                String email = rs.getString("email");
                double salary = rs.getDouble("salary");
                String department = rs.getString("department");

                System.out.printf("%-2d | %-17s | %-17s | $%-8.2f | %s%n",
                        id, firstName + " " + lastName, email, salary, department);
            }

        } catch (SQLException e) {
            System.err.println("Database error: " + e.getMessage());
        }
    }
}
Console Output
ID | Name | Email | Salary | Department ---+-------------------+-------------------+-----------+------------ 5 | Eve Davis | eve@example.com | $95000.00 | Engineering 3 | Carol Williams | carol@example.com | $82000.00 | Engineering 1 | Alice Johnson | alice@example.com | $75000.00 | Engineering 4 | David Brown | david@example.com | $71000.00 | Sales 2 | Bob Smith | bob@example.com | $68000.00 | Marketing

Key things to understand about ResultSet :

  • The cursor starts before the first row. You must call next() before reading any data.
  • next() returns true if it advanced to a valid row, false when there are no more rows. This is why while (rs.next()) works as a loop condition.
  • You can retrieve columns by name ( rs.getInt("id") ) or by index ( rs.getInt(1) ). Column indexes start at 1, not 0. Using names is safer and more readable.
  • The getXxx() methods ( getInt , getString , getDouble , getDate , etc.) perform type conversion automatically. If you call getString("salary") on a DECIMAL column, JDBC converts it to a String for you.

Two Execution Methods — Don't Mix Them Up

  • executeQuery(sql) — for SELECT statements. Returns a ResultSet .
  • executeUpdate(sql) — for INSERT , UPDATE , DELETE , and CREATE TABLE . Returns an int (the number of rows affected).

If you call executeQuery() with an INSERT statement, most drivers will throw an exception. If you call executeUpdate() with a SELECT , the return value will be -1 and you won't get a ResultSet .

Using PreparedStatement

A PreparedStatement is a pre-compiled SQL statement with placeholder parameters. Instead of building SQL strings by concatenation, you use ? placeholders and set each parameter separately.

PreparedStatementQuery.java 
import java.sql.*;

public class PreparedStatementQuery {

    public static void main(String[] args) {

        String url = "jdbc:mysql://localhost:3306/company_db";
        String user = "root";
        String pass = "your_password";

        // The ? is a parameter placeholder — NOT a string literal
        String sql = "SELECT id, first_name, last_name, salary, department "
                  + "FROM employees WHERE department = ? AND salary > ? "
                  + "ORDER BY salary DESC";

        try (Connection conn = DriverManager.getConnection(url, user, pass);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            // Set parameters by position (1-based, NOT 0-based)
            pstmt.setString(1, "Engineering");  // first ?
            pstmt.setDouble(2, 70000.0);        // second ?

            try (ResultSet rs = pstmt.executeQuery()) {
                System.out.println("Engineering employees earning over $70,000:");
                while (rs.next()) {
                    System.out.printf("  %s %s — $%.2f (%s)%n",
                            rs.getString("first_name"),
                            rs.getString("last_name"),
                            rs.getDouble("salary"),
                            rs.getString("department"));
                }
            }

        } catch (SQLException e) {
            System.err.println("Database error: " + e.getMessage());
        }
    }
}
Console Output
Engineering employees earning over $70,000: Eve Davis — $95000.00 (Engineering) Carol Williams — $82000.00 (Engineering) Alice Johnson — $75000.00 (Engineering)

Why use PreparedStatement instead of Statement ?

  1. Security — It prevents SQL injection (explained in detail in the next section).
  2. Performance — The database can compile the SQL once and reuse the execution plan when you change only the parameters.
  3. Readability — No messy string concatenation with quotes and escaping.
  4. Type safety — setInt() , setString() , setDouble() make it clear what type each parameter is.

Parameter Indexing Starts at 1

JDBC parameter indexes are 1-based , not 0-based like Java arrays. pstmt.setString(1, value) sets the first ? , pstmt.setString(2, value) sets the second. Getting this wrong is one of the most common JDBC bugs — you'll either get a wrong-data bug or an SQLException saying "parameter index out of range".

SQL Injection: The Danger and the Fix

SQL injection is one of the most serious security vulnerabilities in web applications. It occurs when user input is inserted directly into a SQL string, allowing an attacker to manipulate the query's logic.

The Vulnerable Version

VULNERABLE — Do Not Use This Code 
// The user enters their email in a login form.
String userInput = "alice@example.com";

// BAD — building SQL by concatenating user input
String sql = "SELECT * FROM employees WHERE email = '"
            + userInput + "'";

// This produces a valid query:
// SELECT * FROM employees WHERE email = 'alice@example.com'

Now consider what happens when a malicious user enters this as their email:

Malicious Input 
String userInput = "' OR '1'='1";

// The concatenated SQL becomes:
// SELECT * FROM employees WHERE email = '' OR '1'='1'

// This is ALWAYS true — it returns EVERY row in the table!
// The attacker just bypassed the login check entirely.

Worse attacks can modify or delete data:

More Dangerous Injection 
String userInput = "'; DELETE FROM employees; --";

// The concatenated SQL becomes:
// SELECT * FROM employees WHERE email = ''; DELETE FROM employees; --'
//                                                          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
//                                                          This deletes ALL employees!

The Safe Version

SAFE — PreparedStatement Prevents Injection 
String userInput = "' OR '1'='1";

// GOOD — the ? is a parameter placeholder, not part of the SQL syntax
String sql = "SELECT * FROM employees WHERE email = ?";

try (PreparedStatement pstmt = conn.prepareStatement(sql)) {

    // The driver sends the parameter as DATA, not as SQL code.
    // The database treats it as a literal string value.
    pstmt.setString(1, userInput);

    // The database executes this as if you wrote:
    // SELECT * FROM employees WHERE email = ''' OR ''1''=''1'
    // Which looks for a row where email literally equals that entire string.
    // No rows match — the attack fails.
    ResultSet rs = pstmt.executeQuery();
    // ...
}

Rule: Never Concatenate User Input Into SQL

This is not a suggestion — it is a hard rule. Always use PreparedStatement with parameter placeholders for any value that comes from user input, configuration files, or any external source. The only things that should appear directly in the SQL string are structural elements like table names, column names, and SQL keywords.

Insert, Update, and Delete

Data modification statements use executeUpdate() , which returns the number of rows affected. The pattern is the same as queries — you just call a different method and get a different result.

InsertUpdateDelete.java 
import java.sql.*;

public class InsertUpdateDelete {

    static final String URL = "jdbc:mysql://localhost:3306/company_db";
    static final String USER = "root";
    static final String PASS = "your_password";

    public static void main(String[] args) {

        // --- INSERT ---
        String insertSql = "INSERT INTO employees (first_name, last_name, email, salary, department) "
                         + "VALUES (?, ?, ?, ?, ?)";

        try (Connection conn = DriverManager.getConnection(URL, USER, PASS);
             PreparedStatement pstmt = conn.prepareStatement(insertSql)) {

            pstmt.setString(1, "Frank");
            pstmt.setString(2, "Miller");
            pstmt.setString(3, "frank@example.com");
            pstmt.setDouble(4, 62000.00);
            pstmt.setString(5, "Sales");

            int rowsInserted = pstmt.executeUpdate();
            System.out.println("Inserted " + rowsInserted + " row(s).");

        } catch (SQLException e) {
            System.err.println("Insert failed: " + e.getMessage());
        }

        // --- UPDATE ---
        String updateSql = "UPDATE employees SET salary = ? WHERE department = ?";

        try (Connection conn = DriverManager.getConnection(URL, USER, PASS);
             PreparedStatement pstmt = conn.prepareStatement(updateSql)) {

            pstmt.setDouble(1, 80000.00);
            pstmt.setString(2, "Engineering");

            int rowsUpdated = pstmt.executeUpdate();
            System.out.println("Updated " + rowsUpdated + " row(s).");

        } catch (SQLException e) {
            System.err.println("Update failed: " + e.getMessage());
        }

        // --- DELETE ---
        String deleteSql = "DELETE FROM employees WHERE salary < ?";

        try (Connection conn = DriverManager.getConnection(URL, USER, PASS);
             PreparedStatement pstmt = conn.prepareStatement(deleteSql)) {

            pstmt.setDouble(1, 65000.00);

            int rowsDeleted = pstmt.executeUpdate();
            System.out.println("Deleted " + rowsDeleted + " row(s).");

        } catch (SQLException e) {
            System.err.println("Delete failed: " + e.getMessage());
        }
    }
}
Console Output
Inserted 1 row(s). Updated 3 row(s). Deleted 1 row(s).

Getting Auto-Generated Keys

When you insert a row with an AUTO_INCREMENT column, you often need to know the generated ID (for example, to use it as a foreign key in another table). You can retrieve it by passing Statement.RETURN_GENERATED_KEYS when creating the PreparedStatement :

GetGeneratedKey.java 
String sql = "INSERT INTO employees (first_name, last_name, email, salary, department) "
           + "VALUES (?, ?, ?, ?, ?)";

try (Connection conn = DriverManager.getConnection(URL, USER, PASS);

     // The second argument tells the driver to return generated keys
     PreparedStatement pstmt = conn.prepareStatement(
             sql, Statement.RETURN_GENERATED_KEYS)) {

    pstmt.setString(1, "Grace");
    pstmt.setString(2, "Lee");
    pstmt.setString(3, "grace@example.com");
    pstmt.setDouble(4, 72000.00);
    pstmt.setString(5, "Marketing");

    pstmt.executeUpdate();

    // Retrieve the generated key(s)
    try (ResultSet keys = pstmt.getGeneratedKeys()) {
        if (keys.next()) {
            int generatedId = keys.getInt(1);
            System.out.println("New employee ID: " + generatedId);
        }
    }
}
Console Output
New employee ID: 6
Ad placeholder
Advertisement

Transaction Management

By default, JDBC operates in auto-commit mode : every SQL statement is committed to the database immediately when it executes. This is fine for simple operations, but many real-world scenarios require transactions — a group of statements that must all succeed or all fail as a unit.

Consider a bank transfer: you need to debit one account and credit another. If the debit succeeds but the credit fails, the money disappears. Transactions prevent this.

TransactionDemo.java 
import java.sql.*;

public class TransactionDemo {

    static final String URL = "jdbc:mysql://localhost:3306/company_db";
    static final String USER = "root";
    static final String PASS = "your_password";

    public static void main(String[] args) {

        // For this example, assume we have an accounts table:
        // CREATE TABLE accounts (id INT PRIMARY KEY, balance DECIMAL(10,2));
        // With data: (1, 1000.00) and (2, 500.00)

        int fromAccount = 1;
        int toAccount = 2;
        double amount = 300.00;

        String debitSql = "UPDATE accounts SET balance = balance - ? WHERE id = ?";
        String creditSql = "UPDATE accounts SET balance = balance + ? WHERE id = ?";

        try (Connection conn = DriverManager.getConnection(URL, USER, PASS)) {

            // Step 1: Turn off auto-commit — we control when changes are saved
            conn.setAutoCommit(false);

            try {
                // Step 2: Debit the source account
                try (PreparedStatement debit = conn.prepareStatement(debitSql)) {
                    debit.setDouble(1, amount);
                    debit.setInt(2, fromAccount);
                    int rows = debit.executeUpdate();
                    if (rows == 0) {
                        throw new SQLException("Source account not found.");
                    }
                }

                // Step 3: Credit the destination account
                try (PreparedStatement credit = conn.prepareStatement(creditSql)) {
                    credit.setDouble(1, amount);
                    credit.setInt(2, toAccount);
                    int rows = credit.executeUpdate();
                    if (rows == 0) {
                        throw new SQLException("Destination account not found.");
                    }
                }

                // Step 4: Both statements succeeded — commit the transaction
                conn.commit();
                System.out.println("Transfer of $" + amount + " completed successfully.");

            } catch (SQLException e) {
                // Step 5: Something went wrong — roll back ALL changes
                conn.rollback();
                System.err.println("Transfer failed: " + e.getMessage());
                System.err.println("Transaction rolled back. No money was moved.");
            }

        } catch (SQLException e) {
            System.err.println("Connection error: " + e.getMessage());
        }
    }
}

The transaction pattern has three critical parts:

  1. conn.setAutoCommit(false) — Disables automatic commit so statements are grouped.
  2. conn.commit() — Saves all changes made since auto-commit was disabled.
  3. conn.rollback() — Discards all changes made since the last commit.

Always Roll Back on Exception

If an exception occurs in the middle of a transaction and you don't call rollback() , the transaction remains open. When the connection is eventually closed (by try-with-resources), some databases will roll back automatically, but others may leave partial changes in an indeterminate state. Always explicitly call rollback() in your catch block.

Handling SQLException Properly

SQLException carries more information than a typical exception. Learning to read it properly will save you hours of debugging.

ErrorHandling.java 
import java.sql.*;

public class ErrorHandling {

    static final String URL = "jdbc:mysql://localhost:3306/company_db";
    static final String USER = "root";
    static final String PASS = "your_password";

    public static void main(String[] args) {

        // Deliberately invalid SQL to trigger an error
        String sql = "SELECT * FROM nonexistent_table";

        try (Connection conn = DriverManager.getConnection(URL, USER, PASS);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            // This line is never reached
        } catch (SQLException e) {

            // The error message — human-readable description
            System.err.println("Message: " + e.getMessage());

            // The SQL state — a 5-character code defined by the SQL standard
            // "42S02" means "base table or view not found"
            System.err.println("SQL State: " + e.getSQLState());

            // The vendor-specific error code (MySQL error 1146 = table doesn't exist)
            System.err.println("Error Code: " + e.getErrorCode());

            // SQLException can be chained — the next exception provides more detail
            SQLException next = e.getNextException();
            if (next != null) {
                System.err.println("Caused by: " + next.getMessage());
            }
        }
    }
}
Console Output
Message: Table 'company_db.nonexistent_table' doesn't exist SQL State: 42S02 Error Code: 1146

Common SQL State Codes Worth Knowing

  • 42S02 — Table or view not found
  • 42S21 — Column already exists
  • 23000 — Integrity constraint violation (duplicate key, foreign key violation)
  • 08001 — Connection failed (wrong URL, database down, wrong credentials)
  • 08004 — Connection rejected (authentication failed)
  • 22001 — String data too long for column
  • HY000 — General error (check the error code and message for specifics)

Batch Operations

When you need to execute many similar statements (inserting 1000 rows, for example), doing them one at a time is slow because each statement requires a separate round-trip to the database. JDBC's batch API lets you queue up multiple statements and send them all at once.

BatchInsert.java 
import java.sql.*;
import java.util.Arrays;
import java.util.List;

public class BatchInsert {

    static final String URL = "jdbc:mysql://localhost:3306/company_db";
    static final String USER = "root";
    static final String PASS = "your_password";

    public static void main(String[] args) {

        // Sample data — in a real app this might come from a file or API
        List<String[]> employees = Arrays.asList(
            new String[]{"Hank", "Wilson", "hank@example.com", "55000", "Sales"},
            new String[]{"Ivy", "Moore", "ivy@example.com", "67000", "Marketing"},
            new String[]{"Jack", "Taylor", "jack@example.com", "73000", "Engineering"},
            new String[]{"Karen", "Anderson", "karen@example.com", "81000", "Engineering"},
            new String[]{"Leo", "Thomas", "leo@example.com", "59000", "Sales"}
        );

        String sql = "INSERT INTO employees (first_name, last_name, email, salary, department) "
                   + "VALUES (?, ?, ?, ?, ?)";

        try (Connection conn = DriverManager.getConnection(URL, USER, PASS);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            // Disable auto-commit for better batch performance
            conn.setAutoCommit(false);

            for (String[] emp : employees) {
                pstmt.setString(1, emp[0]);
                pstmt.setString(2, emp[1]);
                pstmt.setString(3, emp[2]);
                pstmt.setDouble(4, Double.parseDouble(emp[3]));
                pstmt.setString(5, emp[4]);

                // addBatch() queues the statement — it does NOT execute yet
                pstmt.addBatch();
            }

            // executeBatch() sends all queued statements to the database at once
            // Returns an int[] where each element is the row count for that batch item
            int[] results = pstmt.executeBatch();

            // Commit all the inserts as a single transaction
            conn.commit();

            System.out.println("Batch insert complete: " + results.length + " rows added.");

        } catch (SQLException e) {
            System.err.println("Batch insert failed: " + e.getMessage());
        }
    }
}
Console Output
Batch insert complete: 5 rows added.

Why Disable Auto-Commit for Batches

With auto-commit enabled, each statement in the batch would be committed individually — five separate transactions, each with its own disk write. With auto-commit disabled, all five inserts are committed in a single transaction, which is significantly faster. This is one of the simplest and most effective JDBC performance optimizations.

ResultSet Metadata

Sometimes you don't know the column names or types at compile time — for example, when building a generic query tool or processing dynamic queries. ResultSetMetaData provides information about the columns in a ResultSet .

MetadataDemo.java 
import java.sql.*;

public class MetadataDemo {

    static final String URL = "jdbc:mysql://localhost:3306/company_db";
    static final String USER = "root";
    static final String PASS = "your_password";

    public static void main(String[] args) {

        String sql = "SELECT * FROM employees LIMIT 3";

        try (Connection conn = DriverManager.getConnection(URL, USER, PASS);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            // Get metadata about the result set
            ResultSetMetaData meta = rs.getMetaData();
            int columnCount = meta.getColumnCount();

            // Print column headers
            for (int i = 1; i <= columnCount; i++) {
                String label = meta.getColumnLabel(i);
                String type = meta.getColumnTypeName(i);
                System.out.printf("%-15s (%-10s) ", label, type);
            }
            System.out.println();
            System.out.println("-".repeat(columnCount * 20));

            // Print rows
            while (rs.next()) {
                for (int i = 1; i <= columnCount; i++) {
                    String value = rs.getString(i);
                    System.out.printf("%-15s            ", value);
                }
                System.out.println();
            }

        } catch (SQLException e) {
            System.err.println("Error: " + e.getMessage());
        }
    }
}
Console Output
id (INT ) first_name (VARCHAR ) last_name (VARCHAR ) email (VARCHAR ) salary (DECIMAL ) department (VARCHAR ) -------------------------------------------------------------------------------------------------------- 1 Alice Johnson alice@example.com 75000.00 Engineering 2 Bob Smith bob@example.com 68000.00 Marketing 3 Carol Williams carol@example.com 82000.00 Engineering

Metadata is useful for building generic tools, but for regular application code, you should always access columns by name ( rs.getString("first_name") ) rather than by index — it's more readable and less fragile when the query changes.

Real-World Example: EmployeeDAO

In a real application, you don't scatter JDBC code throughout your business logic. Instead, you create a Data Access Object (DAO) — a class that encapsulates all database operations for a specific entity. Here's a complete, production-quality example:

Employee.java 
/**
 * A simple domain object representing an employee.
 * This class has nothing to do with JDBC — it's just data.
 */
public class Employee {

    private Integer id;          // null for new employees (not yet saved)
    private String firstName;
    private String lastName;
    private String email;
    private double salary;
    private String department;

    public Employee(String firstName, String lastName, String email,
                   double salary, String department) {
        this.firstName = firstName;
        this.lastName = lastName;
        this.email = email;
        this.salary = salary;
        this.department = department;
    }

    // Getters and setters
    public Integer getId() { return id; }
    public void setId(Integer id) { this.id = id; }
    public String getFirstName() { return firstName; }
    public void setFirstName(String firstName) { this.firstName = firstName; }
    public String getLastName() { return lastName; }
    public void setLastName(String lastName) { this.lastName = lastName; }
    public String getEmail() { return email; }
    public void setEmail(String email) { this.email = email; }
    public double getSalary() { return salary; }
    public void setSalary(double salary) { this.salary = salary; }
    public String getDepartment() { return department; }
    public void setDepartment(String department) { this.department = department; }

    @Override
    public String toString() {
        return String.format("Employee{id=%d, name='%s %s', email='%s', salary=$%.2f, dept='%s'}",
                id, firstName, lastName, email, salary, department);
    }
}
EmployeeDAO.java 
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Data Access Object for Employee entities.
 * All database operations for employees go through this class.
 */
public class EmployeeDAO {

    private final String url;
    private final String username;
    private final String password;

    public EmployeeDAO(String url, String username, String password) {
        this.url = url;
        this.username = username;
        this.password = password;
    }

    /**
     * Inserts a new employee and returns the employee with the generated ID.
     */
    public Employee insert(Employee employee) throws SQLException {
        String sql = "INSERT INTO employees (first_name, last_name, email, salary, department) "
                   + "VALUES (?, ?, ?, ?, ?)";

        try (Connection conn = DriverManager.getConnection(url, username, password);
             PreparedStatement pstmt = conn.prepareStatement(
                     sql, Statement.RETURN_GENERATED_KEYS)) {

            pstmt.setString(1, employee.getFirstName());
            pstmt.setString(2, employee.getLastName());
            pstmt.setString(3, employee.getEmail());
            pstmt.setDouble(4, employee.getSalary());
            pstmt.setString(5, employee.getDepartment());

            pstmt.executeUpdate();

            try (ResultSet keys = pstmt.getGeneratedKeys()) {
                if (keys.next()) {
                    employee.setId(keys.getInt(1));
                }
            }
            return employee;
        }
    }

    /**
     * Finds an employee by ID. Returns null if not found.
     */
    public Employee findById(int id) throws SQLException {
        String sql = "SELECT id, first_name, last_name, email, salary, department "
                   + "FROM employees WHERE id = ?";

        try (Connection conn = DriverManager.getConnection(url, username, password);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, id);

            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    return mapRowToEmployee(rs);
                }
                return null;  // No employee found with this ID
            }
        }
    }

    /**
     * Returns all employees, optionally filtered by department.
     */
    public List<Employee> findAll(String department) throws SQLException {
        String sql;
        if (department == null || department.isEmpty()) {
            sql = "SELECT id, first_name, last_name, email, salary, department "
                + "FROM employees ORDER BY id";
        } else {
            sql = "SELECT id, first_name, last_name, email, salary, department "
                + "FROM employees WHERE department = ? ORDER BY id";
        }

        List<Employee> employees = new ArrayList<>();

        try (Connection conn = DriverManager.getConnection(url, username, password);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            if (department != null && !department.isEmpty()) {
                pstmt.setString(1, department);
            }

            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    employees.add(mapRowToEmployee(rs));
                }
            }
        }
        return employees;
    }

    /**
     * Updates an existing employee's information.
     * Returns true if a row was updated, false if the employee ID doesn't exist.
     */
    public boolean update(Employee employee) throws SQLException {
        String sql = "UPDATE employees SET first_name = ?, last_name = ?, "
                   + "email = ?, salary = ?, department = ? WHERE id = ?";

        try (Connection conn = DriverManager.getConnection(url, username, password);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, employee.getFirstName());
            pstmt.setString(2, employee.getLastName());
            pstmt.setString(3, employee.getEmail());
            pstmt.setDouble(4, employee.getSalary());
            pstmt.setString(5, employee.getDepartment());
            pstmt.setInt(6, employee.getId());

            return pstmt.executeUpdate() > 0;
        }
    }

    /**
     * Deletes an employee by ID.
     * Returns true if a row was deleted, false if the ID doesn't exist.
     */
    public boolean deleteById(int id) throws SQLException {
        String sql = "DELETE FROM employees WHERE id = ?";

        try (Connection conn = DriverManager.getConnection(url, username, password);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, id);
            return pstmt.executeUpdate() > 0;
        }
    }

    /**
     * Maps a row from the ResultSet to an Employee object.
     * This private helper avoids repeating the mapping logic in every method.
     */
    private Employee mapRowToEmployee(ResultSet rs) throws SQLException {
        Employee emp = new Employee(
                rs.getString("first_name"),
                rs.getString("last_name"),
                rs.getString("email"),
                rs.getDouble("salary"),
                rs.getString("department")
        );
        emp.setId(rs.getInt("id"));
        return emp;
    }
}
DAODemo.java 
import java.sql.SQLException;
import java.util.List;

public class DAODemo {

    public static void main(String[] args) {
        EmployeeDAO dao = new EmployeeDAO(
                "jdbc:mysql://localhost:3306/company_db",
                "root",
                "your_password"
        );

        try {
            // CREATE
            Employee newEmp = new Employee(
                    "Mia", "Garcia", "mia@example.com", 77000.00, "Engineering");
            dao.insert(newEmp);
            System.out.println("Created: " + newEmp);

            // READ (by ID)
            Employee found = dao.findById(newEmp.getId());
            System.out.println("Found: " + found);

            // READ (all in Engineering)
            List<Employee> engineers = dao.findAll("Engineering");
            System.out.println("\nEngineering employees (" + engineers.size() + "):");
            engineers.forEach(System.out::println);

            // UPDATE
            newEmp.setSalary(85000.00);
            newEmp.setDepartment("Management");
            boolean updated = dao.update(newEmp);
            System.out.println("\nUpdated: " + (updated ? "yes" : "no"));
            System.out.println("After update: " + dao.findById(newEmp.getId()));

            // DELETE
            boolean deleted = dao.deleteById(newEmp.getId());
            System.out.println("\nDeleted: " + (deleted ? "yes" : "no"));
            System.out.println("After delete: " + dao.findById(newEmp.getId()));

        } catch (SQLException e) {
            System.err.println("Database error: " + e.getMessage());
            e.printStackTrace();
        }
    }
}
Console Output
Created: Employee{id=11, name='Mia Garcia', email='mia@example.com', salary=$77000.00, dept='Engineering'} Found: Employee{id=11, name='Mia Garcia', email='mia@example.com', salary=$77000.00, dept='Engineering'} Engineering employees (4): Employee{id=1, name='Alice Johnson', email='alice@example.com', salary=$80000.00, dept='Engineering'} Employee{id=3, name='Carol Williams', email='carol@example.com', salary=$80000.00, dept='Engineering'} Employee{id=5, name='Eve Davis', email='eve@example.com', salary=$80000.00, dept='Engineering'} Employee{id=11, name='Mia Garcia', email='mia@example.com', salary=$77000.00, dept='Engineering'} Updated: yes After update: Employee{id=11, name='Mia Garcia', email='mia@example.com', salary=$85000.00, dept='Management'} Deleted: yes After delete: null

Notice the design decisions in this DAO:

  • Each method throws SQLException — the caller decides how to handle it (log it, show an error message, retry, etc.). The DAO doesn't swallow exceptions or print stack traces.
  • The mapRowToEmployee helper eliminates duplicated mapping code.
  • findById returns null when no row is found rather than throwing an exception — this is a common convention (an "empty result" is not an error condition).
  • update and deleteById return boolean so the caller knows whether the operation actually affected a row.
  • The DAO receives the connection info through its constructor — it's not hardcoded inside the methods.
Ad placeholder
Advertisement

Connection Pooling (Overview)

Every example in this tutorial opens a new connection, does one operation, and closes it. In a real web application that handles hundreds of requests per second, this is extremely inefficient — establishing a TCP connection, authenticating, and negotiating protocol settings takes 10–50 milliseconds per connection.

Connection pooling solves this by maintaining a set of reusable connections. When your code needs a connection, it borrows one from the pool. When it's done, the connection is returned to the pool instead of being closed. The pool handles connection validation, timeout, and sizing.

Popular Connection Pool Libraries

  • HikariCP — The fastest and most widely used pool. It's the default pool in Spring Boot. Add it with: com.zaxxer:HikariCP:5.1.0
  • Apache DBCP — An older but reliable option from the Apache Commons project.
  • Tomcat JDBC Pool — Built into Apache Tomcat, also usable standalone.

Using HikariCP typically looks like this:

HikariCP Example (requires adding the dependency) 
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import java.sql.Connection;
import java.sql.SQLException;

public class ConnectionPoolExample {

    public static void main(String[] args) throws SQLException {

        // Configure the pool
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:mysql://localhost:3306/company_db");
        config.setUsername("root");
        config.setPassword("your_password");
        config.setMaximumPoolSize(10);   // max 10 connections at once
        config.setMinimumIdle(2);       // keep 2 connections ready

        // Create the pool (this establishes the initial connections)
        HikariDataSource dataSource = new HikariDataSource(config);

        // Borrow a connection from the pool
        try (Connection conn = dataSource.getConnection()) {
            // Use conn exactly like a regular JDBC connection
            // When the try block ends, the connection is returned to the pool
            // — it is NOT actually closed
            var stmt = conn.createStatement();
            var rs = stmt.executeQuery("SELECT COUNT(*) FROM employees");
            rs.next();
            System.out.println("Total employees: " + rs.getInt(1));
        }

        // When your application shuts down, close the pool
        dataSource.close();
    }
}

The beauty of connection pooling is that your JDBC code (the PreparedStatement , ResultSet , etc.) doesn't change at all. You just get the Connection from a different source. This is why learning raw JDBC first is valuable — even when you use a pool or an ORM, understanding what's happening underneath helps you debug performance problems and write better code.

Best Practices Summary

Security

  • Always use PreparedStatement for any value that comes from user input, configuration, or any external source. This prevents SQL injection — no exceptions.
  • Never put passwords in source code. Use environment variables, a configuration file outside version control, or a secrets manager. In the examples above we used a literal string for clarity, but in production, use something like System.getenv("DB_PASSWORD") .
  • Use the principle of least privilege. Your application's database user should only have the permissions it needs (SELECT, INSERT, UPDATE, DELETE on specific tables) — not DROP, ALTER, or access to other databases.

Resource Management

  • Always use try-with-resources for Connection , Statement / PreparedStatement , and ResultSet . This is non-negotiable. Connection leaks are one of the most common causes of production failures in Java applications.
  • Close resources in the right order — ResultSet first, then Statement , then Connection . Try-with-resources handles this automatically when you nest them in the correct order.
  • Don't hold connections open longer than necessary. Open a connection, do your work, close it. Don't open a connection at application startup and hold it for the lifetime of the application (unless you're using a connection pool, which manages this for you).

Performance

  • Use batch operations for multiple INSERT/UPDATE/DELETE statements — they're dramatically faster than individual executions.
  • Disable auto-commit during batches to group all changes into a single transaction.
  • Use connection pooling in any application that handles more than a handful of database operations.
  • Only select the columns you need. SELECT id, name FROM employees is faster than SELECT * FROM employees , especially on wide tables with large text or blob columns.
  • Consider using setFetchSize() on the Statement when reading large result sets. This controls how many rows the driver fetches from the database at a time, reducing memory usage.

Code Organization

  • Use the DAO pattern to separate database code from business logic.
  • Don't let SQLException leak outside your DAO layer. Either catch it and wrap it in a custom exception, or use a higher-level abstraction. Business logic shouldn't depend on java.sql types.
  • Extract SQL strings as constants at the top of your DAO class. This makes the code easier to read and maintain, and makes it simple to find all the SQL in your codebase.

A Note on e.printStackTrace()

We used e.printStackTrace() in a few examples above for brevity. In production code, never use this . It writes to System.err with no context, no timestamp, and no structured format. Instead, log the exception using a logging framework (as covered in the Java Logging tutorial): logger.log(Level.SEVERE, "Failed to insert employee", e) . This gives you a timestamp, log level, your message, and the full stack trace in a consistent format.

Common Errors and Fixes

Error Message Cause Fix
No suitable driver found for jdbc:mysql://... JDBC driver JAR not in classpath Add the driver dependency (Maven/Gradle) or put the JAR in lib/
Access denied for user 'root'@'localhost' Wrong username or password Verify credentials; reset password if needed
Communications link failure Database not running or wrong host/port Start the database; check the URL host and port
Unknown database 'company_db' Database doesn't exist Create it: CREATE DATABASE company_db
Parameter index out of range (1 > 0) Using 0-based index instead of 1-based Change setString(0, ...) to setString(1, ...)
Before start of result set Called rs.getXxx() before rs.next() Always call next() first; check the return value
Duplicate entry 'x@example.com' for key 'email' UNIQUE constraint violation Check for existing record before insert, or handle the exception
Connection is closed Using a connection after it was closed Check your try-with-resources scope; don't close a connection you still need
Exercise

Exercise: Build a DepartmentDAO with Aggregation Queries

Create a DepartmentDAO class that works with the same employees table and provides these methods:

  1. List<String> getAllDepartments() — Returns a list of distinct department names from the table.
  2. int getEmployeeCount(String department) — Returns the number of employees in the given department.
  3. double getAverageSalary(String department) — Returns the average salary for the given department. Return 0.0 if the department has no employees.
  4. Map<String, Integer> getEmployeeCountByDepartment() — Returns a map where each key is a department name and each value is the employee count for that department.
  5. boolean transferEmployee(int employeeId, String newDepartment) — Updates an employee's department. Returns false if the employee doesn't exist.

Requirements:

  • Use PreparedStatement for all queries with parameters.
  • Use try-with-resources for all JDBC resources.
  • Handle the case where a query returns no rows gracefully (don't throw exceptions for "not found" situations).
  • Write a main method that demonstrates all five methods.

Solution

DepartmentDAO.java 
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class DepartmentDAO {

    private final String url;
    private final String username;
    private final String password;

    public DepartmentDAO(String url, String username, String password) {
        this.url = url;
        this.username = username;
        this.password = password;
    }

    /**
     * 1. Returns all distinct department names.
     */
    public List<String> getAllDepartments() throws SQLException {
        String sql = "SELECT DISTINCT department FROM employees ORDER BY department";
        List<String> departments = new ArrayList<>();

        try (Connection conn = DriverManager.getConnection(url, username, password);
             PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {
            while (rs.next()) {
                departments.add(rs.getString("department"));
            }
        }
        return departments;
    }

    /**
     * 2. Returns the number of employees in a department.
     */
    public int getEmployeeCount(String department) throws SQLException {
        String sql = "SELECT COUNT(*) AS cnt FROM employees WHERE department = ?";

        try (Connection conn = DriverManager.getConnection(url, username, password);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, department);

            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    return rs.getInt("cnt");
                }
            }
        }
        return 0;
    }

    /**
     * 3. Returns the average salary for a department.
     *    Returns 0.0 if no employees in that department.
     */
    public double getAverageSalary(String department) throws SQLException {
        String sql = "SELECT AVG(salary) AS avg_sal FROM employees WHERE department = ?";

        try (Connection conn = DriverManager.getConnection(url, username, password);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, department);

            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    double avg = rs.getDouble("avg_sal");
                    // AVG() returns NULL when no rows match, which becomes 0.0 via getDouble()
                    return avg;
                }
            }
        }
        return 0.0;
    }

    /**
     * 4. Returns a map of department name -> employee count.
     */
    public Map<String, Integer> getEmployeeCountByDepartment() throws SQLException {
        String sql = "SELECT department, COUNT(*) AS cnt "
                   + "FROM employees GROUP BY department ORDER BY department";
        Map<String, Integer> counts = new HashMap<>();

        try (Connection conn = DriverManager.getConnection(url, username, password);
             PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {
            while (rs.next()) {
                counts.put(rs.getString("department"), rs.getInt("cnt"));
            }
        }
        return counts;
    }

    /**
     * 5. Transfers an employee to a new department.
     *    Returns false if the employee doesn't exist.
     */
    public boolean transferEmployee(int employeeId, String newDepartment)
            throws SQLException {
        String sql = "UPDATE employees SET department = ? WHERE id = ?";

        try (Connection conn = DriverManager.getConnection(url, username, password);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, newDepartment);
            pstmt.setInt(2, employeeId);

            return pstmt.executeUpdate() > 0;
        }
    }
}
DepartmentDAODemo.java 
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

public class DepartmentDAODemo {

    public static void main(String[] args) {
        DepartmentDAO dao = new DepartmentDAO(
                "jdbc:mysql://localhost:3306/company_db",
                "root",
                "your_password"
        );

        try {
            // 1. All departments
            List<String> depts = dao.getAllDepartments();
            System.out.println("Departments: " + depts);

            // 2. Employee count per department
            for (String dept : depts) {
                int count = dao.getEmployeeCount(dept);
                double avgSalary = dao.getAverageSalary(dept);
                System.out.printf("  %s: %d employees, avg salary $%.2f%n",
                        dept, count, avgSalary);
            }

            // 4. Count map
            Map<String, Integer> countMap = dao.getEmployeeCountByDepartment();
            System.out.println("\nEmployee counts: " + countMap);

            // 5. Transfer
            boolean transferred = dao.transferEmployee(2, "Engineering");
            System.out.println("\nTransfer employee 2 to Engineering: "
                    + (transferred ? "success" : "employee not found"));

            // Verify the transfer
            System.out.printf("Engineering count after transfer: %d%n",
                    dao.getEmployeeCount("Engineering"));

            // Transfer non-existent employee
            boolean notFound = dao.transferEmployee(999, "Sales");
            System.out.println("Transfer employee 999 to Sales: "
                    + (notFound ? "success" : "employee not found"));

        } catch (SQLException e) {
            System.err.println("Database error: " + e.getMessage());
        }
    }
}
Console Output
Departments: [Engineering, Marketing, Sales] Engineering: 3 employees, avg salary $84000.00 Marketing: 1 employees, avg salary $68000.00 Sales: 1 employees, avg salary $71000.00 Employee counts: {Engineering=3, Marketing=1, Sales=1} Transfer employee 2 to Engineering: success Engineering count after transfer: 4 Transfer employee 999 to Sales: employee not found
Ad placeholder
Advertisement

Summary

Here's a quick reference of everything covered in this tutorial:

  • JDBC is an API , not a database. It provides a standard interface that works with any relational database through driver JARs.
  • The core workflow : connect → create statement → execute SQL → process results → close resources.
  • executeQuery() for SELECT (returns ResultSet ), executeUpdate() for INSERT/UPDATE/DELETE (returns row count).
  • PreparedStatement is the standard way to execute SQL. It prevents SQL injection, improves performance, and makes code cleaner.
  • SQL injection happens when user input is concatenated into SQL strings. PreparedStatement sends parameters as data, not code, preventing this entirely.
  • ResultSet starts before the first row. Call next() to advance. Access columns by name (preferred) or 1-based index.
  • Transactions : setAutoCommit(false) → execute statements → commit() on success or rollback() on failure.
  • Batch operations with addBatch() + executeBatch() are dramatically faster for multiple statements.
  • Try-with-resources is essential for closing Connection , Statement , and ResultSet reliably.
  • The DAO pattern encapsulates all database operations for an entity in a single class, separating persistence from business logic.
  • Connection pooling (HikariCP, DBCP) reuses connections instead of creating new ones, critical for production applications.

Next Steps

  • Java Logging — Learn how to properly log database errors and query timing instead of using e.printStackTrace() .
  • Exception Handling — If you need a deeper understanding of try-with-resources and exception chaining before tackling more JDBC.
  • Spring Boot — See how Spring Boot eliminates most JDBC boilerplate with JdbcTemplate and handles connection pooling, transaction management, and exception translation automatically.