INSERT ... RETURNING returns null if I am not using Record objects

[petri.ka...@gmail.com]

I am implementing a simple repository for my jOOQ example and I ran into the following problem:

When I use INSERT... RETURNING the returned object is always null if I insert individual field values instead of inserting a Record object.

My code looks as follows:

import net.petrikainulainen.spring.jooq.todo.db.tables.records.TodosRecord;
import org.jooq.impl.DefaultDSLContext;
import java.sql.Timestamp;

import static net.petrikainulainen.spring.jooq.todo.db.Sequences.TODO_SEQUENCE;
import static net.petrikainulainen.spring.jooq.todo.db.tables.Todos.TODOS;

@Repository
public class JOOQTodoRepository implements TodoRepository {

    private DateTimeService dateTimeService;

    private DefaultDSLContext jooq;

    @Autowired
    public JOOQTodoRepository(DateTimeService dateTimeService, DefaultDSLContext jooq) {
        this.dateTimeService = dateTimeService;
        this.jooq = jooq;
    }

    @Override
    public Todo add(Todo todo) {
        Long id = jooq.nextval(TODO_SEQUENCE);
        Timestamp currentTime = dateTimeService.getCurrentTime();
        TodosRecord persisted = jooq.insertInto(TODOS)
                .set(TODOS.CREATION_TIME, currentTime)
                .set(TODOS.DESCRIPTION, todo.getDescription())
                .set(TODOS.ID, id)
                .set(TODOS.MODIFICATION_TIME, currentTime)
                .set(TODOS.TITLE, todo.getTitle())
                .returning()
                .fetchOne();

        //persisted is always null
    }
}

I was pointed to this issue: https://github.com/jOOQ/jOOQ/issues/2374. After I modified my code to use the TodosRecord class,
it looks as follows:

import net.petrikainulainen.spring.jooq.todo.db.tables.records.TodosRecord;
import org.jooq.impl.DefaultDSLContext;
import java.sql.Timestamp;

import static net.petrikainulainen.spring.jooq.todo.db.Sequences.TODO_SEQUENCE;
import static net.petrikainulainen.spring.jooq.todo.db.tables.Todos.TODOS;

@Repository
public class JOOQTodoRepository implements TodoRepository {

    private DateTimeService dateTimeService;

    private DefaultDSLContext jooq;

    @Autowired
    public JOOQTodoRepository(DateTimeService dateTimeService, DefaultDSLContext jooq) {
        this.dateTimeService = dateTimeService;
        this.jooq = jooq;
    }

    @Override
    public Todo add(Todo todo) {
        TodosRecord persisted = jooq.insertInto(TODOS)
                .set(createRecord(todo))
                .returning()
                .fetchOne();

        //Persisted is found
    }

    private TodosRecord createRecord(Todo todo) {
        Long id = jooq.nextval(TODO_SEQUENCE);
        Timestamp currentTime = dateTimeService.getCurrentTime();

        TodosRecord record = new TodosRecord();
        record.setCreationTime(currentTime);
        record.setDescription(todo.getDescription());
        record.setId(id);
        record.setModificationTime(currentTime);
        record.setTitle(todo.getTitle());
        return record;
    }
}

This works smoothly but I decided to post my code here so that I could ensure that I didn't miss something, and you could verify that this is the same issue which is discussed in issue 2374.

Any comments?

[Lukas Eder]

Hi Petri,

Thanks for reporting this and for the details about your issue. From a quick glance at your code, I think that you're using the API correctly, so this is likely a bug. I have registered #3140 for this:

https://github.com/jOOQ/jOOQ/issues/3140

I'll keep you posted about my findings. Can you also reproduce this bug when you use the INSERT .. VALUES .. RETURNING syntax, instead of the INSERT .. SET .. RETURNING syntax?

Cheers

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[petri.ka...@gmail.com]

Hi Lukas,

I'll keep you posted about my findings. Can you also reproduce this bug when you use the INSERT .. VALUES .. RETURNING syntax, instead of the INSERT .. SET .. RETURNING syntax?

I will commit my code to Github during this weekend. This way it is maybe a bit easier to debug the problem / ensure that I haven't made a mistake. By the way, I noticed that this problem occurs sometimes even if I use Record objects. It doesn't happen every time though. I will let you know where you can get the code after I have committed it.

- Petri

[Lukas Eder]

Hi Petri,

Thanks for reporting these things. I will have a closer look at them right away

Lukas