JDBI bind value or null in query?

user_1335245 0
Viewed 1636

JDBI query needs to support setting a value, or null, for multiple columns in the query.

But, the following is inserting empty strings and zeroes, not nulls:

handle
  .createUpdate("REPLACE INTO products(id, name, price) VALUES (:id, :name, :price)")
  .bind("id", product.getId())
  .bind("name", product.getName())
  .bind("price", product.getPrice())
  .execute();

Further, when I SELECT records from the same database and use the following row mapper, this results in zeroes (for Double, though String seems to map null OK):

return Product.newBuilder()
  .setId(rs.getString("id"))
  .setName(rs.getString("name"))
  .setPrice(rs.getDouble("price"))
  .build();
jdbi jdbc mysql sql java
1 Answers

For writing, it's necessary to directly access the JDBC prepared statement's setNull method:

var stmt = handle
  .createUpdate("REPLACE INTO products(id, name, price) VALUES (:id, :name, :price)")
  .bind("id", deal.getId());
bindValueOrNull(stmt, "name", product.getName());
bindValueOrNull(stmt, "price", product.getPrice());
stmt.execute();

/**
 Bind a non-null value to the named query parameter, or else SQL Types.NULL value

 @param stmt  being prepared
 @param key   to bind
 @param value or null
 */
private void bindValueOrNull(Update stmt, String key, @Nullable Object value) {
  if (Objects.nonNull(value))
    stmt.bind(key, value);
  else
    stmt.bindNull(key, Types.NULL);
}

For reading, it's necessary to do a null check using the getObject method:

return Product.newBuilder()
  .setId(rs.getString("id"))
  .setName(rs.getString("name"))
  .setPrice(getDoubleOrNull(rs, "price"))
  .build();

/**
 Get Double value, or null, from a result set

 @param rs  result set
 @param key of column
 @return Double or null
 */
@Nullable
private static Double getDoubleOrNull(ResultSet rs, String key) throws SQLException {
  return Objects.nonNull(rs.getObject(key)) ? rs.getDouble(key) : null;
}
user_13352450
Related