Stage 6·Professional Java

JDBC & Databases

Drivers and connections, PreparedStatement and SQL injection, transactions, and pooling.

18 min readIntermediate

On this page

Real applications remember data in databases. JDBC (Java Database Connectivity) is Java's standard API for talking to relational databases like PostgreSQL, MySQL, and others. It's the foundation every higher-level tool (including Hibernate and Spring Data) builds on.

The pieces

A database stores data in tables (rows and columns).
SQL is the language you use to query and modify it.
A JDBC driver is a library that lets Java speak to a specific database.
JDBC is the common API so your Java code looks the same regardless of database.

JDBC is like a universal remote

Different TVs (databases) speak different internal signals. A universal remote (JDBC) gives you one set of buttons; a small code chip for each brand (the driver) translates. You learn one interface and control any database.

Connecting and querying

String url = "jdbc:postgresql://localhost:5432/shop";

try (Connection conn = DriverManager.getConnection(url, user, password);
     Statement stmt = conn.createStatement();
     ResultSet rs = stmt.executeQuery("SELECT id, name FROM products")) {

    while (rs.next()) {
        int id = rs.getInt("id");
        String name = rs.getString("name");
        System.out.println(id + ": " + name);
    }
}   // try-with-resources closes everything automatically

ResultSet is a cursor over the returned rows; rs.next() advances to each row.

PreparedStatement - always use it for input

Never build SQL by gluing strings together with user input. That opens the door to SQL injection, one of the most dangerous security flaws. Use a PreparedStatement with ? placeholders instead:

// DANGEROUS - never do this
String sql = "SELECT * FROM users WHERE name = '" + input + "'";  // 💥 injectable

// SAFE - parameters are sent separately, never mixed into the SQL text
String sql = "SELECT * FROM users WHERE name = ?";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
    ps.setString(1, input);       // safely bound
    ResultSet rs = ps.executeQuery();
}

SQL injection is a top security risk

If input like ' OR '1'='1 is concatenated into SQL, an attacker can read or destroy your whole database. A PreparedStatement keeps data separate from the query structure, making injection impossible. Always use it for any value that isn't a hard-coded constant.

Inserting and updating

String sql = "INSERT INTO products (name, price) VALUES (?, ?)";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
    ps.setString(1, "Keyboard");
    ps.setDouble(2, 49.99);
    int rows = ps.executeUpdate();     // returns number of rows affected
}

Transactions - all or nothing

A transaction groups operations so they all succeed or all fail together - essential for correctness (imagine transferring money between accounts).

A transaction is like a bank transfer

Moving $100 from A to B is two steps: subtract from A, add to B. If the power fails between them, the money must not vanish. A transaction guarantees both happen or neither does - the account is never left inconsistent.

conn.setAutoCommit(false);            // start a transaction
try {
    debit(conn, accountA, 100);
    credit(conn, accountB, 100);
    conn.commit();                    // both succeeded - make it permanent
} catch (SQLException e) {
    conn.rollback();                  // something failed - undo everything
}

Connection pooling

Opening a database connection is expensive. Production apps use a connection pool (like HikariCP) that keeps a set of open connections ready to reuse - just like the thread pool idea from the concurrency stage.

You'll rarely write raw JDBC

JDBC is the foundation, but in real projects you'll usually use higher-level tools - Spring's JdbcTemplate, or an ORM like Hibernate (next lessons) - that remove the boilerplate. Understanding JDBC makes those tools far less mysterious.

Quick check

Why should you use a PreparedStatement instead of concatenating user input into SQL?

Key takeaways

JDBC is Java's standard API for relational databases; a driver adapts it to a specific database.
Use Connection, Statement/PreparedStatement, and ResultSet; try-with-resources closes them safely.
ALWAYS use PreparedStatement with ? placeholders for input to prevent SQL injection.
executeQuery reads (ResultSet); executeUpdate writes (returns affected row count).
Transactions (setAutoCommit(false), commit, rollback) make grouped operations all-or-nothing.
Production apps use a connection pool (e.g. HikariCP) to reuse expensive connections.

Before building bigger apps, you need to know they work. Next: testing.