Quiz: Statement, PreparedStatement and ResultSet in JDBC

Total: 6 questions

1. 

What is the difference between Statement, PreparedStatement and CallableStatement in JDBC? What do execute, executeUpdate and executeQuery return?

All three are created by a Connection. Statement (createStatement()) runs a ready-made SQL string without parameters. PreparedStatement (prepareStatement(sql)) accepts ? parameters and is used for any query with values from outside. CallableStatement (prepareCall("{call proc(?)}")) calls a stored procedure; OUT parameters are registered with registerOutParameter. PreparedStatement extends Statement, and CallableStatement extends PreparedStatement.

  • executeQuery is for SELECT and returns a ResultSet.
  • executeUpdate is for INSERT, UPDATE, DELETE and DDL and returns the number of affected rows (0 for DDL).
  • execute is for when the type is unknown: true if the first result is a ResultSet, false if it is an update count (getUpdateCount()).

If you pick the wrong method (INSERT in executeQuery, SELECT in executeUpdate), the driver throws SQLException.

2. 

How do ResultSet and the next() cursor work? How do you read a column that may contain NULL?

A ResultSet holds the rows returned by a SELECT and is read row by row. The cursor of a new set stands before the first row; next() moves to the next row and returns false when the rows run out. So you write while (rs.next()), and if (rs.next()) for a single row. Reading without next() throws SQLException (MySQL: Before start of result set).

Values are read with getXxx by name (getInt("id")) or by column index, counting from 1. Reading by name does not break if the column order in the SELECT changes.

For NULL, getInt returns 0 and getBoolean returns false. To detect NULL, call wasNull() right after reading, or use getObject(column, Integer.class), which returns null.

By default a set is TYPE_FORWARD_ONLY and CONCUR_READ_ONLY. For previous(), absolute() or updateRow() request it like this: createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE).

3. 

How do you run a batch of queries in JDBC, and what does executeBatch() return?

A batch sends several data-changing queries to the database in one round trip and reduces traffic between the client and the DBMS. With Statement you add queries with addBatch(String sql); with PreparedStatement, with addBatch() without arguments, which adds the current set of parameters.

executeBatch() returns an int[], one element per query in the order of addBatch: the number of affected rows, SUCCESS_NO_INFO (-2) if the driver does not know the row count, or EXECUTE_FAILED (-3) if the query failed and the driver went on with the rest.

If a query fails, BatchUpdateException is thrown; getUpdateCounts() shows what managed to run. The batch is not rolled back on its own: without manual transaction control the rows of successful queries stay.

MySQL Connector/J sends batch queries one by one by default. The URL parameter rewriteBatchedStatements=true merges INSERTs of a PreparedStatement into one multi-row query, but the array may then contain -2.

4. 

What is SQL injection, and how does PreparedStatement prevent it in Java?

SQL injection is an attack in which the user enters not data but a fragment of SQL, and it becomes part of the query. It happens with string concatenation: the input ' OR '1'='1 turns WHERE username = '...' into a condition that is true for every row, and in a login check it lets you in without a password.

The protection is a PreparedStatement with ? parameters: the query structure is fixed in prepareStatement before any data gets into it, and setString passes the input as an ordinary value. Parameters are numbered from 1.

Nuances:

  • Concatenation inside prepareStatement("... = '" + name + "'") is just as vulnerable.
  • ? substitutes only a value: a table or column name cannot be passed this way; check it against a whitelist of allowed names.
  • The main reason to use PreparedStatement is security, not speed: MySQL Connector/J has server-side preparation off by default (useServerPrepStmts=false).
5. 

How do you get the id of a new row after an INSERT in JDBC (getGeneratedKeys)?

Pass the Statement.RETURN_GENERATED_KEYS flag to prepareStatement (or to executeUpdate of a Statement), run the INSERT and read the key from the ResultSet returned by getGeneratedKeys():

PreparedStatement ps = connection.prepareStatement(
        "INSERT INTO users (username) VALUES (?)", Statement.RETURN_GENERATED_KEYS);
ps.setString(1, "ivanov");
ps.executeUpdate();
try (ResultSet keys = ps.getGeneratedKeys()) {
    if (keys.next()) {
        int id = keys.getInt(1);
    }
}

The key set has one row per inserted record, so after a batch insert you read it in while (keys.next()). Without the flag MySQL Connector/J throws SQLException: Generated keys not requested.

SELECT MAX(id) after the insert is not acceptable: between the two queries another thread can insert its own row, and you get someone else's id.

6. 

Which JDBC methods read and write INT, VARCHAR, DECIMAL, DATE and DATETIME columns? How do you write NULL?

Every type has a pair: getXxx of ResultSet for reading and setXxx of PreparedStatement for writing.

  • INT: getInt/setInt; BIGINT: getLong/setLong; VARCHAR and TEXT: getString/setString.
  • DECIMAL: BigDecimal with getBigDecimal/setBigDecimal. Money is not read with getDouble: double stores decimal fractions approximately, and 0.1 + 0.2 gives 0.30000000000000004.
  • DATE: getObject(col, LocalDate.class); DATETIME and TIMESTAMP: getObject(col, LocalDateTime.class); writing with setObject. This works since JDBC 4.2 (Java 8). getDate and getTimestamp return the legacy java.sql.Date and java.sql.Timestamp.
  • BOOLEAN (in MySQL it is TINYINT(1)): getBoolean/setBoolean.

To write NULL through a parameter, call setNull(1, Types.INTEGER). When reading, getInt returns 0 for NULL, so nullable columns are read with getObject(col, Integer.class).

Page 1 of 1