Ignore null values of entity bean while updating the table using Hibernate

Question

Stuck on the same problem Is there any method to ignore null values when updating the database in Hibernate?

Whenever you call update(); of Session, it will also update the null values found in the object.

Example:

User user = new User();
user.setUserId(5);
user.setUserName("Maarten");
user.setUserFirstName(null); // but in database this value is not null

session.update( user);

OR

session.saveOrUpdate( user);

The DB will now update the user, but will set the user firstname to null (because it is null in the object).

Is there any way or method in Hibernate to avoid this (I don't want to fire a select/ update query to set the bean)? that it will ignore the null value?

Blotchy

possible duplicate of How can I prevent Hibernate from updating NULL values – Goffer

so you want to set FirstName value as the value it is there in the DB previously.You dont want to update that FirstName Right ? – Burdock

@Ganesh yup you are right. – Blotchy

Answer 1

hibernate-dynamic-update

The dynamic-update attribute tells Hibernate whether to include unmodified properties in the SQL UPDATE statement.

Burdock

dynamic-update is depreciated use instead @DynamicUpdate annotation baeldung.com/spring-data-jpa-dynamicupdate – Chemoprophylaxis

Answer 2

It depends on the abstraction level you wish to control, and the business rules of the object:

• Is the field really nullable, or are you simply using it as a DTO? perhaps you need an intermediate object to control null values
• What happens with new users (as in the example)? I should ignore the null value, then which value should be persisted, the default one?

If, anyway, you define your requisites in a lower level, e.g. "do not include null values in update queries", you can use the following:

• sql-update annotation here. You can use a custom sql query or procedure to control values
• dynamicUpdate is about changes, not values, so I think it's not really suitable
• Use a EntityListener, and an auxiliary transient field to revert changes if desired.
• Other options: use an interceptor or even create a custom dialect

Themis

Answer 3

One of the options is, load the bean using get(object) method from the session and then set/change the values. But this comes with an overhead of one extra call to the database. Or you can create a custom query if making an extra call is crucial.

Edit: if bean is cached then get(object) will not be a performance hit.

Blotchy

Other then overhead, in case that the field is being updated from another process you might end up overriding the value (read-write race-cond). Transaction is one possible solution but I prefer to split into 2 tables or update the specific fields carefully. – Curia

@Curia that would depend on the concurrency control you have set, databases protect from the use-case you described. – Blotchy

Answer 4

This works for me

@Data
public class ProductRequest {

    private Optional<String> name;

    private Optional<String> unitSale;

}

The most important thing here is to use Optional<> in the DTO class, because Jackson when deserializing distinguishes between an explicit null in the request body and a null due to the omission of the parameter in the request.

Example:

If we make the following PUT request with parameters

http put localhost:8080/products/200 name=banana unit_sale=KG

We obtain the following DTO

ProductRequest(name=Optional[banana], unitSale=Optional[KG])

And when we send an explicit null value:

http put :8080/products/200 name=banana unit_sale:=null
ProductRequest(name=Optional[banana], unitSale=Optional.empty)

And when we send some parameters omitting the others

http put :8080/products/200 name=banana
ProductRequest(name=Optional[banana], unitSale=null)

And that's the key, distinguishing between Optional.empty and null in our business logic to determine what value to set in our entity.

 @Transactional
    public void update(Long productId, @Valid ProductRequest productRequest) {

        ProductEntity productEntity = productRepository.findByIdOptional(productId)
                .orElseThrow(() -> new EntityNotFoundException("Product ID not found"));

        if (productRequest.getName() != null) {
            productEntity.name = unwrapOptional(productRequest.getName());
        }

        if (productRequest.getUnitSale() != null) {
            productEntity.unitSale = unwrapOptional(productRequest.getUnitSale());
        }

    }

    public <T> T unwrapOptional(final Optional<T> o) {
        return o.isPresent() ? o.get() : null;
    }

Tampon