JDBC and prepared statements

JDBC is Java's low-level database API.

🔎 Unravel this example Prerequisites (1)

Learn these first

try-with-resources

At a glance

Mental model

A prepared statement is a form letter with blanks.

What happens, in order

1A DataSource hands out Connections (usually from a pool)You create a PreparedStatement from SQL such as select * from users where id = ?, set parameters with setLong(1, id), and run executeQuery or executeUpdate.
2A ResultSet is a cursor: while (rs.next()) moves to the next row, and rs.getString("name") reads a column
3Connections, statements and result sets hold database resources and must be closedTry-with-resources closes them in reverse order even on errors.

Remember

Never build SQL by joining strings with user input: "..
Connections in a pool are borrowed and returned
With a pool such as HikariCP, close() on the connection returns it to the pool instead of closing the socket

Watch out

String-concatenated SQLNot closing connections and statementsLoading a huge ResultSet entirely into a listUsing Statement instead of PreparedStatement for anything with parameters

Example (highlighted lines explain it)

String sql = "select name from users where id = ?";try (Connection con = dataSource.getConnection();     PreparedStatement ps = con.prepareStatement(sql)) {    ps.setLong(1, userId);    try (ResultSet rs = ps.executeQuery()) {        if (rs.next()) {            return rs.getString("name");        }    }}

line 2 uses Connection, the core of JDBC and prepared statements
line 3 uses PreparedStatement, the core of JDBC and prepared statements
line 5 uses ResultSet, the core of JDBC and prepared statements

Read the full explanation (about 1 min)

JDBC is Java's low-level database API. A PreparedStatement sends SQL with placeholders (?) and the values separately, which prevents SQL injection and lets the database reuse the query plan.

A mental model

A prepared statement is a form letter with blanks. You fill in the blanks with values, never rewrite the letter itself, so a customer cannot sneak new sentences into it.

How it works

A DataSource hands out Connections (usually from a pool). You create a PreparedStatement from SQL such as select * from users where id = ?, set parameters with setLong(1, id), and run executeQuery or executeUpdate.

A ResultSet is a cursor: while (rs.next()) moves to the next row, and rs.getString("name") reads a column.

Connections, statements and result sets hold database resources and must be closed. Try-with-resources closes them in reverse order even on errors.

Going deeper

Never build SQL by joining strings with user input: "... where name = '" + name + "'" is SQL injection. Placeholders send values as data, so they can never become SQL syntax.

Connections in a pool are borrowed and returned. A connection leaked by a missing close eventually exhausts the pool and the whole application hangs waiting for one.

JdbcTemplate removes the boilerplate (opening, closing, exception translation) and is the practical choice when you want SQL without JPA.

Under the hood

With a pool such as HikariCP, close() on the connection returns it to the pool instead of closing the socket. Drivers can cache prepared statements per connection, and the database caches the parsed plan.

Common mistakes

String-concatenated SQL.
Not closing connections and statements.
Loading a huge ResultSet entirely into a list.
Using Statement instead of PreparedStatement for anything with parameters.

How Unravel recognises it

When you paste code, Unravel looks for these signs of jdbc and prepared statements. Spotting them yourself is a good habit when you read unfamiliar code:

Types: PreparedStatement ResultSet Connection DataSource JdbcTemplate NamedParameterJdbcTemplate Statement
Method calls: prepareStatement() executeQuery() executeUpdate() getConnection()

Related concepts

try-with-resourcesErrors and logging JPA entitiesPersistence

Builds on this

Connection pools (HikariCP)Persistence JDBC transactions, batching and JdbcTemplate mappingPersistence SQL mappers and builders (MyBatis, jOOQ)Persistence JDBC connections, DataSource and poolsPersistence ResultSet and mapping rows to objectsPersistence

Frequently asked questions

What is JDBC and prepared statements?

JDBC is Java's low-level database API. A PreparedStatement sends SQL with placeholders (?) and the values separately, which prevents SQL injection and lets the database reuse the query plan.

What are the most common mistakes with JDBC and prepared statements?

String-concatenated SQL. Not closing connections and statements. Loading a huge ResultSet entirely into a list. Using Statement instead of PreparedStatement for anything with parameters.

How can I see JDBC and prepared statements in real code?

Paste your own Java into Unravel and it will point out where jdbc and prepared statements appears, explain how it works at the detail level you choose, and draw the flow of the code around it.