JDBC for Beginners: PreparedStatement, Transactions and SQL Injection

Almost every Java back-end job involves a database, so freshers are asked how Java talks to one. JDBC (Java Database Connectivity) is the standard API in java.sql. You write against interfaces (Connection, PreparedStatement, ResultSet) and a vendor driver (a JAR for PostgreSQL, MySQL, Oracle...) supplies the implementation. Frameworks such as Hibernate and Spring Data JPA sit on top of JDBC, so understanding it explains what they do.

The examples that talk to a real database need a driver and a running server, so they are marked Illustrative (read them; they are not run here). The SQL injection demonstration at the end is a complete program you can run.

The core objects

Query with PreparedStatement and try-with-resources

// Illustrative: needs a JDBC driver (for example PostgreSQL) and a running database.
import java.sql.*;
import java.util.*;

public class EmployeeDao {
    private static final String URL = "jdbc:postgresql://localhost:5432/shop";

    public List<String> findNamesByDepartment(String department) throws SQLException {
        String sql = "SELECT name FROM employees WHERE department = ? ORDER BY name";
        List<String> names = new ArrayList<>();

        try (Connection connection = DriverManager.getConnection(URL, "app", System.getenv("DB_PASSWORD"));
             PreparedStatement statement = connection.prepareStatement(sql)) {

            statement.setString(1, department);                 // placeholders are numbered from 1
            try (ResultSet rows = statement.executeQuery()) {
                while (rows.next()) {
                    names.add(rows.getString("name"));
                }
            }
        }                                                       // all three resources are closed here, even on an exception
        return names;
    }
}

Three points interviewers listen for: the ? placeholder, 1-based parameter indexes (unlike arrays), and closing every resource. Closing the Connection also closes its statements and result sets, but doing it explicitly with try-with-resources is the safe habit, and a pooled connection is returned to the pool by close().

Never read a column with getInt and assume it is not NULL: it returns 0 for NULL. Call rows.wasNull() or use getObject("col", Integer.class).

Transactions

By default a connection is in auto-commit mode, so each statement commits at once. To make several statements succeed or fail together, switch it off:

// Illustrative: needs a JDBC driver and a running database.
public void transfer(Connection connection, int from, int to, long amount) throws SQLException {
    connection.setAutoCommit(false);
    try (PreparedStatement debit = connection.prepareStatement("UPDATE accounts SET balance = balance - ? WHERE id = ?");
         PreparedStatement credit = connection.prepareStatement("UPDATE accounts SET balance = balance + ? WHERE id = ?")) {

        debit.setLong(1, amount);
        debit.setInt(2, from);
        debit.executeUpdate();

        credit.setLong(1, amount);
        credit.setInt(2, to);
        credit.executeUpdate();

        connection.commit();                // both updates become permanent together
    } catch (SQLException e) {
        connection.rollback();              // neither update survives
        throw e;
    }
}

The transaction properties are ACID: Atomicity, Consistency, Isolation, Durability. Isolation levels (READ COMMITTED, REPEATABLE READ, SERIALIZABLE) trade safety against concurrency; the default in PostgreSQL and Oracle is READ COMMITTED.

Batching and pooling

For many inserts, add rows with and send them with . This makes far fewer network round trips than one per row.

Opening a connection is slow, so applications use a connection pool (HikariCP is the default in Spring Boot). A pool keeps a set of open connections and lends them out. Size it modestly: a handful of connections per CPU core is usually plenty, and a huge pool usually just moves the bottleneck into the database.

SQL injection

SQL injection happens when user input is concatenated into SQL text, so the input can change the meaning of the query. This complete program only builds the strings so you can see the problem:

The second query's clause is true for every row, so the attacker reads the whole table. With a login query, the same trick bypasses the password check. A sends the SQL text and the values separately; the database treats the value as data, so is just a strange name that matches nothing.

Prevention checklist:

• Always use parameters (, JPA named parameters, Spring Data derived queries).
• Table and column names cannot be parameters. Check them against an allow-list.
• Give the application's database user only the permissions it needs.
• Never show raw database error messages to users.

Interview answers in one line each

• Statement versus PreparedStatement: the prepared form is precompiled, parameterized, safer against injection and often faster when reused.
• What does do? It loads the driver. Since JDBC 4 (Java 6) drivers register themselves automatically, so you rarely need it.
• How do you handle a column? Use or , and when writing.
• What is the N+1 problem? One query for a list, then one more query per row. With JPA, fix it with a join fetch or batch fetching.

Practise

1. Run and change the input to to see how the text changes.
2. Add a for an and read the generated key with .
3. Read the SQL interview collection to practise the queries behind the code.