JoeS February 2016

Pass null primary key with jOOQ to have it set by the database

For a REST service, I receive an object to persist in the database. I want to prevent the user from setting the id. Instead, the database should assign the value automatically from a sequence, like Postgresql's SERIAL.

If I try to null out the id in the jOOQ generated record, the database will error out when I try to store it.

Here's the relevant DDL.

CREATE TABLE todo (
  id SERIAL PRIMARY KEY,
  title TEXT,
  url TEXT
);

Here's the relevant code. Full code at todo-backend.

@POST
public Todo addTodo(Todo todo, @Context DSLContext db) {
    final TodoRecord todoRecord = db.newRecord(TODO, todo);

    // This errors out, violates not-null constraint on DB
    todoRecord.setId(null);

    todoRecord.store();

    todoRecord.setUrl("http://localhost:8080/" + todoRecord.getId());
    todoRecord.store();

    return db.selectFrom(TODO)
            .where(TODO.ID.eq(todoRecord.getId()))
            .fetchOneInto(Todo.class);
}

Versions

  • dropwizard 0.8.5
  • jOOQ 3.7.2
  • Postgres 9.5
  • Java 1.8

Answers


Lukas Eder February 2016

Some databases allow NULL values in keys. Or perhaps, you might have a trigger that listens to NULL values being set on primary keys.

This is why jOOQ cannot assume that your intention for this call is to set ID to DEFAULT. jOOQ will assume that you really want NULL to be set in INSERT or UPDATE statements:

todoRecord.setId(null);

Instead, write

todoRecord.changed(TODO.ID, false);

See Record.changed(Field, boolean)

Post Status

Asked in February 2016
Viewed 3,034 times
Voted 11
Answered 1 times

Search




Leave an answer