Sample Scenario

I have a limit that controls the total value of a column. If I make a save that exceeds this limit, I want it to throw an exception. For example;

Suppose I have already added the following data: LIMIT = 20

• If I insert (A,2) it exceeds the limit and I want to get exception
• If I insert (B,4) the transaction should be successful since it didn't exceed the limit
• code and value are interrelated

What can I do

I can check this scenario with required queries. For example, I write a method for it and I can check it in the save method. That's it.

However, I'm looking for a more useful solution than this

• For example, is there any annotation when designing Entity ?
• Can I do this without calling the method that provides this control every time ?

What examples can I give ?

• @UniqueConstraint checking if it adds the same values

CodePudding user response：

Neither Hibernate nor Spring Data JPA have anything built-in for this scenario. You have to program the transaction logic in your repository yourself:

@PersistenceContext
EntityManager em;

public addValue(String code, int value) {
    var checkQuery = em.createQuery("SELECT SUM(value) FROM Entity WHERE code = :code", Integer.class);
    checkQuery.setParameter("code", code);

    if (checkQuery.getSingleResult()   value > 20) {
        throw new LimitExceededException("attempted to exceed limit for "   code);
    }

    var newEntity = new Entity();
    newEntity.setCode(code);
    newEntity.setValue(value);
    em.persist(newEntity);
}

Then (it's important!) you have to define SERIALIZABLE isolation level on the @Transactional annotations for the methods that work with this table.

Read more about serializable isolation level here, they have an oddly similar example.

Note that you have to consider retrying the failed transaction. No idea how to do this with Spring though.

CodePudding user response：

Using transaction

The most common and long-accepted way is to simply abstract in a suitable form (in a class, a library, a service, ...) the business rules that govern the behavior you describe, within a transaction:

@Transactional(propagation = Propagation.REQUIRED)
public RetType operation(ReqType args) {
    ...
    perform operations;
    ...
    if(fail post conditions)
        throw ...;
    ...
}

In this case, if when calling a method there is already an open transaction, that transaction will be used (and there will be no interlocks), if there is no transaction created, it will create a new one so that both the operations and the postconditions check are performed within the same transaction.

Note that with this strategy both operation and invariant check transactions can combine multiple transactional states managed by the TransactionManager (e.g. Redis, MySQL, MQS, ... simultaneously and in a coordinated manner).

Using only the database

It has not been used for a long time (in favor of the first way) but using TRIGGERS was the canonical option used some decades ago to check postconditions, but this solution is usually coupled to the specific database engine (e.g. in PostgreSQL or MySQL).

It could be useful in the case where the client making the modifications is unable or unwilling (not safe) to check postconditions (e.g. bash processes) within a transaction. But nowadays it is infrequent.

The use of TRIGGERS may also be preferable in certain scenarios where efficiency is required, as there are certain optimization options within the database scripts.