Skip to content
Tutorials Center
My learning

Topics

Lesson 7 of 119 ยท Databases with JDBC

CRUD with JDBC

Insert, query, update and delete rows with JDBC - Statement vs PreparedStatement, executeUpdate and executeQuery, ResultSet getters, NULLs, generated keys and records.

CRUD stands for the four things applications do with stored data: Create, Read, Update and Delete. In SQL they are INSERT, SELECT, UPDATE and DELETE, and in JDBC they come down to a handful of methods on PreparedStatement and ResultSet. You already know how to open a connection from JDBC Introduction. This lesson is about what you do with it.

By the end you will write the kind of data-access code found in real projects: parameterized statements, generated keys, correct handling of NULL, java.time and BigDecimal values, row counts that detect missing records, and rows mapped to Java records with a small reusable helper. It is also exactly the code that JPA and Spring Data generate for you later, so understanding it makes those frameworks transparent.

Statement vs PreparedStatement

JDBC has two main ways to send SQL:

  • A Statement sends a complete SQL string. Every value must be written into the string itself.
  • A PreparedStatement is created from SQL with ? placeholders. You then bind values with typed setters such as setString(1, name), and the driver sends the values separately from the SQL text.
Java
// Statement: values glued into the SQL text. Don't do this with input.
statement.executeQuery("SELECT * FROM product WHERE name = '" + name + "'");

// PreparedStatement: the SQL is fixed, the value is a parameter.
PreparedStatement ps = connection.prepareStatement("SELECT * FROM product WHERE name = ?");
ps.setString(1, name);
ResultSet rows = ps.executeQuery();
Statement PreparedStatement
Values Concatenated into the SQL string Bound to ? parameters with typed setters
SQL injection Vulnerable whenever input is concatenated Values can never change the SQL's structure
Types You format dates, decimals and quotes yourself The driver converts LocalDate, BigDecimal, null correctly
Performance Parsed every time Can be parsed and planned once, then reused (drivers and pools cache it)
Use for Fixed DDL and scripts without any variable parts Everything else

The rule professionals follow is simple: if any part of the SQL comes from a variable, use a PreparedStatement. The next lesson, PreparedStatement and SQL Injection, shows how an attacker exploits concatenated SQL. Parameter indexes start at 1, not 0, like everything else in JDBC.

The three execute methods

A statement is executed with one of three methods, depending on what the SQL returns:

Method Use for Returns
executeUpdate() INSERT, UPDATE, DELETE (and DDL) int: number of rows affected (0 for DDL)
executeQuery() SELECT a ResultSet
execute() SQL whose kind you don't know in advance true if a ResultSet is available (getResultSet()), false if an update count is (getUpdateCount())

Using the wrong one throws an SQLException. H2, for example, answers executeQuery with a DELETE by "Method is only allowed for a query". For huge tables there is also executeLargeUpdate(), which returns a long.

Create: INSERT and generated keys

When the database generates the primary key (an identity or auto-increment column), you usually need that key right after inserting, to return it to an API client or to insert child rows. Ask for it when you prepare the statement, then read it from getGeneratedKeys():

Java INSERT with generated keys

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

Each insert reports one affected row and the new IDs 1, 2 and 3. Notice:

  • One PreparedStatement, reused for all three rows. Setting new parameters overwrites the old ones, so there is no need to prepare the SQL again. For many rows, use batching instead (Batch Updates, Metadata and Large Objects).
  • new String[] {"id"} names the generated column to return. You can also pass Statement.RETURN_GENERATED_KEYS, but naming the column is more portable: PostgreSQL would otherwise return every column of the new row, and some drivers need the name to know what to return.
  • setNull(3, Types.INTEGER) writes SQL NULL. You cannot pass a null to setInt, which takes a primitive. setObject(3, null) also works with most drivers, but setNull with the SQL type is the most portable.
  • setObject(4, LocalDate) binds a java.time value directly. Since JDBC 4.2, drivers support LocalDate, LocalDateTime and OffsetDateTime through setObject and getObject. Avoid the old java.sql.Date and java.sql.Timestamp classes in new code.
  • setBigDecimal for money. Constructing it from a String keeps the exact value; new BigDecimal(19.95) would carry the double's binary error.

Read: navigating a ResultSet

executeQuery() returns a ResultSet, a cursor over the result rows. The cursor starts before the first row. Each call to next() moves it forward one row and returns false when there are no more rows, so the standard loop is while (rows.next()). For a query that returns at most one row, use if (rows.next()).

On the current row, read columns with typed getters, by column label or by 1-based index:

Getter Returns For SQL types
getString String VARCHAR, CHAR, TEXT (also works for most other types, as text)
getInt, getLong int, long (0 for NULL!) INT, BIGINT
getBigDecimal BigDecimal DECIMAL, NUMERIC
getBoolean boolean (false for NULL) BOOLEAN
getDouble double DOUBLE PRECISION, REAL
getObject(label, LocalDate.class) LocalDate DATE (likewise LocalDateTime, OffsetDateTime)
getObject(label, Integer.class) Integer or null Nullable numbers
getBytes byte[] VARBINARY, small BLOBs

Prefer labels (rows.getString("name")) over indexes: they survive changes to the column list and make code readable. The label is the column's alias if the query has one (SELECT price * 2 AS doubled), otherwise its name. Labels are case-insensitive.

Mapping each row to a record keeps JDBC types out of the rest of your program:

Java Mapping rows to records

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

The three products under 100 print as records, cheapest first, with exact prices such as 19.95 and real LocalDate values. findById(db, 2) finds the standing desk with stock=null, and findById(db, 99) returns Optional.empty instead of throwing or returning null. Two methods, one shared mapper: that toProduct function is the seed of the DAO pattern in The DAO and Repository Patterns.

Name the columns you select. SELECT * breaks mappers when someone adds, renames or reorders columns, and it transfers data you don't need. List exactly the columns your record needs.

The NULL trap

SQL NULL and Java primitives don't mix. getInt, getLong, getDouble and getBoolean return primitives, so for a NULL column they return 0 (or false). Your code cannot tell "zero in stock" from "stock unknown" unless you check. There are two correct ways:

Java Zero or NULL?

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

Both rows return getInt=0. Only wasNull() (which reports on the last column read, so call it immediately after the getter) or getObject(..., Integer.class) reveals that the standing desk's stock is NULL. Use boxed types (Integer, Long, BigDecimal) in your records for nullable columns, and primitives only for NOT NULL columns.

Update and delete: check the row count

executeUpdate() returns how many rows changed. Use that number: 0 means the WHERE matched nothing, which in a "update product 42" operation usually means product 42 does not exist (or the condition was false). Turning that into a clear result is better than silently succeeding:

Java UPDATE and DELETE with row counts

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

Changing product 1 succeeds; product 42 does not exist, so the update reports false instead of pretending it worked. The first reservation of two lamps succeeds (3 left, now 1), the second fails because only one is left, and no negative stock is ever written. Because the stock check sits inside the UPDATE, two simultaneous orders cannot both take the last lamp. The DELETE removes the two products with no stock, leaving only 1 Desk lamp 29.90 stock=1.

In a REST API, these booleans become status codes: false from changePrice becomes 404 Not Found, false from reserve becomes 409 Conflict. You will wire that up in Building a REST API with the JDK.

A tiny reusable query helper

Every query method above repeats the same ceremony: prepare, bind, execute, loop, map, close. A small helper with a row mapper removes it. Spring's JdbcTemplate and JdbcClient are built on exactly this idea:

Java A mini JdbcTemplate

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

All four CRUD operations now take one line each, and every resource is still closed in the right order inside the helper. setObject lets the driver pick the SQL type from the Java type (Long, String, BigDecimal, LocalDate). The mapper is an ordinary lambda, so a quick projection such as COUNT(*) needs no record at all. Explicit typed setters remain useful when you must control the SQL type, such as setNull with a specific type.

Common mistakes

  • Concatenating values into SQL. Use ? parameters for every value, always. Next lesson.
  • Forgetting next(). Reading a column before the first next() throws ("No data is available" in H2). The cursor starts before the first row.
  • Using 0-based indexes. setString(0, ...) and getString(0) fail. JDBC indexes start at 1.
  • Primitive getters for nullable columns. getInt turns NULL into 0. Use getObject(col, Integer.class) or wasNull().
  • Ignoring the update count. 0 rows affected is not an exception; your code has to notice it.
  • double for money, java.sql.Date for dates. Use BigDecimal and java.time types.
  • Not closing ResultSets and statements. Put every one in try-with-resources; long-running servers leak cursors otherwise.
  • Running one query per row in a loop to load related data (the N+1 problem). Use a join, an IN list or a batch.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. rows.getInt("stock") returns 0. What do you know about the stock column?

    Primitive getters return 0 for SQL NULL. wasNull() (called right after the getter) or getObject("stock", Integer.class) distinguishes the two.
  2. 2. Which call runs UPDATE product SET price = ? WHERE id = ? and tells you whether the product existed?

    executeUpdate returns the affected-row count. execute() returns true only when the result is a ResultSet, which an UPDATE never is.
  3. 3. What does this print? ResultSet rs = ps.executeQuery(); System.out.println(rs.getString(1));

    A new ResultSet's cursor sits before the first row. Call next() (and check that it returned true) before reading columns.
  4. 4. Why pass new String[] {"id"} instead of Statement.RETURN_GENERATED_KEYS to prepareStatement?

    Some drivers need the column name, and PostgreSQL returns all columns with RETURN_GENERATED_KEYS. Naming the key column gives the same result everywhere.
  5. 5. A record has a field Integer stock for a nullable INT column. Which line maps it correctly?

    getObject with a target class returns null for SQL NULL. getInt and getLong return 0, and a String cannot be cast to Integer.

Summary

  • Use a PreparedStatement with ? parameters for every SQL that contains a value; parameter and column indexes start at 1. Plain Statement is for fixed DDL only.
  • executeUpdate() returns the affected-row count, executeQuery() a ResultSet, execute() a boolean telling which one you got.
  • Get generated keys by preparing with the key column names (new String[] {"id"}) and reading getGeneratedKeys().
  • A ResultSet cursor starts before the first row: loop with while (rows.next()), or if (rows.next()) for one row. Read columns by label with typed getters; use BigDecimal for money and getObject(col, LocalDate.class) and friends for dates and times.
  • Primitive getters turn NULL into 0: use getObject(col, Integer.class) or wasNull(), and boxed types for nullable fields.
  • Check update counts to detect missing rows, put conditions inside UPDATE to make check-and-change atomic, and map rows to records with a reusable row mapper.

Next: PreparedStatement and SQL Injection, where you break into a login form built with string concatenation and then make it impossible.