🛠️ Testing, Tools & Ecosystem · Advanced

JDBC in Java

Connection, PreparedStatement (prevents SQL injection), ResultSet, transactions, connection pools.

🧩 The mysteryA user types their name into a login form, and your query suddenly returns every account in the database. Their "name" contained a single quote.

The JDBC pieces

JDBC is Java's low-level database API. A **DataSource (usually a pool) gives you a Connection; you run SQL through a PreparedStatement and read rows from a ResultSet**. try-with-resources closes them all.

var sql = "SELECT name FROM users WHERE id = ?";
try (var ps = conn.prepareStatement(sql)) {
    ps.setLong(1, userId);
    try (var rs = ps.executeQuery()) {
        while (rs.next())
            names.add(rs.getString("name"));
    }
}

🔮 Predict it

Pasting input into SQL

A login form receives this name. What SQL does the code build?

String input = "' OR 1=1 --";
String sql =
    "SELECT * FROM users WHERE name = '"
    + input + "'";
System.out.println(sql);

SELECT * FROM users WHERE name = '' OR 1=1 --'
SELECT * FROM users WHERE name = 'OR 1=1'
Throws IllegalArgumentException

Show the answer

Concatenation pastes the input straight into the SQL, quotes included. The condition OR 1=1 is always true, and -- comments out the rest. SQL injection: the input changed the query's structure.

Querying by name

✗ Injectable

String sql = "... WHERE name = '"
    + name + "'";
stmt.executeQuery(sql);

Input becomes part of the SQL text.

✓ Parameterized

var ps = conn.prepareStatement(
    "... WHERE name = ?");
ps.setString(1, name);

SQL and values travel separately: input is always data.

⚠️ The trap

Counting from zero

JDBC parameter indexes start at 1, and so do column indexes. setLong(0, ...) fails with an SQLException about the parameter index.

var ps = conn.prepareStatement(
    "SELECT total FROM orders WHERE id = ?");
ps.setLong(0, orderId); // wrong: use 1

The cursor starts before row 1

A new ResultSet is positioned before the first row. Each **next()** moves forward and returns false when there are no more rows. That's why reading always starts with while (rs.next()) or if (rs.next()).

Transactions

With auto-commit on (the default), every statement commits on its own. For a transfer that debits A and credits B, turn it off and commit both together, or roll back on failure.

conn.setAutoCommit(false);
try {
    debit(conn, a, amount);
    credit(conn, b, amount);
    conn.commit();
} catch (SQLException e) {
    conn.rollback();
    throw e;
}

💼 In the real world

Why every app uses a pool

Opening a database connection costs network round trips and authentication. A connection pool such as HikariCP keeps a bounded set of open connections and hands them out ready to use. The bound also protects the database from being flooded with connections.

Key takeaways

PreparedStatement + ? parameters prevent SQL injection
Parameter and column indexes start at 1
rs.next() moves to the first row
Pools (e.g. HikariCP) reuse expensive connections

🤯 Did you know?

SQL injection was described publicly in Phrack magazine in 1998, and injection is still in the OWASP Top 10 decades later.

Practice questions

A login form receives this name. What SQL does the code build?

String input = "x' OR '1'='1";
String sql =
    "SELECT * FROM users WHERE name = '"
    + input + "'";
System.out.println(sql);

SELECT * FROM users WHERE name = ''
Throws IllegalArgumentException
SELECT * FROM users WHERE name = 'x OR 1=1'
SELECT * FROM users WHERE name = 'x' OR '1'='1'

Check your answer

SELECT * FROM users WHERE name = 'x' OR '1'='1'. String concatenation pastes the input straight into the SQL, quotes included. The WHERE clause now matches every row.

What's the right fix for this query?

String sql =
    "SELECT * FROM users WHERE name = '"
    + name + "'";
ResultSet rs = stmt.executeQuery(sql);

Use a PreparedStatement with `name = ?` and ps.setString(1, name)
Replace ' with '' by hand before concatenating
Convert the name to upper case
Wrap the call in try/catch SQLException

Check your answer

Use a PreparedStatement with `name = ?` and ps.setString(1, name). With parameters, the driver sends the SQL and the values separately, so input is always treated as data, never as SQL.

JDBC means writing SQL and copying columns into objects by hand. Next: JPA and Hibernate do it for you, with one famous trap.