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)?

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 :)