CODEWITHSUNDEEP
jooq
pennstatephil
pennstatephil

Reputation: 1653

Set fields null from POJO using JOOQ

According to this entry from the JOOQ blog, the section titled "SQL DEFAULT expressions and POJOs" indicates that any null fields from a POJO will set the column to null, since every Record.changed() flag will be set to true.

In practice, I have not found this to be the case-- if a POJO field is null and I follow the example in the blog, existing values are retained. I want to set these fields to null, but how can I do it using POJOs?

// Load the customer record from a POJO
CustomerRecord customer = ctx.newRecord(CUSTOMER, customerPojo);
customer.update();

// Expected behavior: null fields in customerPojo will set db values to null
// Actual behavior: null fields in customerPojo do not change values in db

Edit: This is using Jooq 3.11.10, Postgres 10.6.

Create customer script:

drop table if exists customers;
create table customers
(
    id         serial not null primary key,
    first_name text,
    last_name  text
);

Customer Class:

@Data
@Builder(toBuilder = true)
@NoArgsConstructor
@AllArgsConstructor
public class Customer {

    private Integer id;
    private String firstName;
    private String lastName;

    public static Customers TAB = Customers.CUSTOMERS;
    static DSLContext ctx = PostgresSingleton.getMainCtx();

    public Customer store(){
        CustomersRecord rec = ctx.newRecord(TAB, this);
        if(getId() == null) {
            rec.store();
        }
        else {
            rec.update();
        }
        rec.refresh();
        return rec.into(Customer.class);
    }
}

Test (Spock/Groovy):

    def "Customer test"() {
        when: "save a new customer"
        Customer cust = Customer.builder().firstName("Phil").lastName("H").build().store()

        then: "ID populated"
        def custId = cust.getId()
        custId != null

        when: "null out last name"
        cust.toBuilder().lastName(null).build().store()

        then: "last name actually set to null"
        cust.getId() == custId
        cust.getFirstName() == "Phil"
        cust.getLastName() == null //fails, still is "H"
    }

Edit 2:

It appears the .update() call is in fact nulling the field in the DB! However, the .into() call afterwards does not refresh the POJO with the null field as expected. I verified the value for lastName is null after .refresh(), but the into() call doesn't null the value.

Upvotes: 1

Views: 642

Answers (1)

pennstatephil
pennstatephil

Reputation: 1653

Well, it was a PEBKAC error-- I wasn't storing the result of my update back into the same object in my test:

        when: "null out last name"
        cust.toBuilder().lastName(null).build().store() //not storing the result!

should be:

        when: "null out last name"
        cust = cust.toBuilder().lastName(null).build().store()

Easy to miss.

Upvotes: 0

Related Questions