How to use Hibernate Validator to validate date ranges across multiple rows in a table?

Question

I have a custom validator which I use to ensure that some business rules are applied on an entity before it is saved.

For example (a hypothetical one), when ProductPrice, defined below, is saved, I validate that the fromDate and endDate for a given product (identified by productId) does not have overlaps with existing ProductPrice rows for that product in the DB.

@MyCustomValidator
class ProductPrice {

  Long productId;

  Date fromDate;
  Date toDate; 
}

This works fine as long as the ProductPrice is saved as an individual entity. Once that entity is added as a One-To-Many relationship in another entity, as below,

class Product {
  List<ProductPrice> productPrices;
}

When the parent (Product) entity is updated (for save it works fine), along with some changes in the child entities (one or more ProductPrice in the List), then it fails as saving of one instance of the ProductPrice in the List does not know anything about the ProductPrice that are going to be saved next.

Detailed Explanation:

Suppose there are two ProducePrice rows in the DB for a given Product as below:

Id    Product Id        From Date        To Date        Price
1     PRD-001           01-01-2016       10-06-106      29.99
2     PRD-001           11-06-2016       10-12-106      32.99

Suppose I want to update both the rows (ProductPrice) as part of Product entity to below values:

Id    Product Id        From Date        To Date        Price
1     PRD-001           01-01-2016       30-06-106      29.99
2     PRD-001           01-07-2016       31-12-106      32.99

The above data is still valid, but the hibernate validator will fail. That is because when saving the first row if I query the database to check the fromDate and toDate of existing rows, then it will overlap with 2nd row existing in the database. But that is not correct since, the second row is also about to be updated with valid values. It could be also possible that, there are 3 rows in the table and I am updating only 2 of them in the current transaction, 3rd one is untouched. So it is important that I have to query the database in the validator to check the validity.

Too long a question, and I might not be clear in explaining the problem. But to conclude, is it possible to query the current persistence context and get the data that is dirty (about to be saved)?

Shovel

Answer 1

It can work but I am not sure how optimal it will be, it depends on your context.

But idea is when you query the database to check the fromDate and toDate of existing rows, you should also query your collection from which you will update database, and based on that to update fromDate and toDate, which will in that case be valid :)

Manuscript

The validator gets only one entry from the collection at a time, it doesn't know anything about the collection itself. I hope it was that easy, just to compare the combination of query results and the collection :-) – Shovel

Answer 2

Consider putting the validator on the Product entity instead of ProductPrice:

@ProductPricesCheck
class Product {
  List<ProductPrice> productPrices;
}

...with the validator looking something like this:

class ProductPricesValidator implements ConstraintValidator<ProductPricesCheck, Product> {
  @Override
  public boolean isValid(final Product product, final ConstraintValidatorContext context) {
    for (ProductPrice productPrice : product.getProductPrices()) {
      // Insert logic for checking the date ranges don't overlap here...
      // ...Return true or false as appropriate.
    }
  }
}

Starflower

That was one option that I thought of, but then if I update ProductPrice independently (not as part of Product), then the validator will not be fired. – Shovel

Then perhaps have both validators? It may help if you could please post the existing code for your ProductPrice validator as I'm not exactly sure how it works currently. – Starflower

If I have both the validators, then when the validator runs for ProductPrice that will run into the problem which I am precisely trying to avoid as mentioned in the question. The validator currently that I have is exactly same as you mentioned in the answer, except that it works on ProductPrice and not the Product. – Shovel

Not sure exactly how you are querying to get the other ProductPrices but this may be relevant: stackoverflow.com/questions/9908061 – Starflower

Querying the ProductPrice is not a problem, as I query that in a separate transaction that that of the validator. – Shovel

Querying within a JSR-303 validator doesn't seem a very "standard" thing to do. Usually, such validators are just looking at static data in the entity instance (i.e. row being changed) and its related entities. There might be a way to detect that the ProductPrice validator is running from a Product update and temporarily turn it off but this all seems a bit too hacky. A preferred suggestion would be not to use the JSR-303 validation for this type of thing - instead consider a higher level validator that verifies the changes after Hibernate, rolling back the transaction if validation fails. – Starflower

Can you give an example of such a higher level validator that can verify the changes done by Hibernate and rollback if necessary? – Shovel

An example would depend on context but would consist of calling a validation method after all the relevant persist() calls have been made that then runs queries within the same transaction. If Spring transaction manager is being used then throwing an unchecked exception would cause the transaction to roll back (by default). Also Method 2 in this answer might also help if Spring-MVC is being used. (Not quite what I suggested as this would need to check the data before attempting to persist anything.) – Starflower

Answer 3

You should use @Valid annotation

class Product {

  @Valid
  List<ProductPrice> productPrices;
}

See this link for more information

Coremaker