[FIXED] A field value returned by a trigger after insert is not synchronized in JPA with a method annotated with @Transactional and saveAndFlush() invocation

Issue

The trigger is working perfectly and generates the value when executed in a script. However, in JPA, the trigger generated value is null after the method to save with @Transactional annotation is executed. When checked in the database this field has a value. I tried to use findById to refresh the record after the save method but the field is still null. The weird part is that when I run the API to just get the record which executes the same findById method, it displays the value.

I have a similar code for another table and it works perfectly. The difference is that the function invoked in the trigger is executed faster than this one.

Is @Transactional executes a commit? When the execution of the method with @Transaction annotation is complete, is the commit is also complete? So why is the database record not the same as the entity record when the findById is executed?

Below are sample code snippets for reference:

@Entity
@Table(name = "my_table")
@EntityListeners(AuditingEntityListener.class)
public class MyTableEntity implements Serializable {
...
@Id
@GeneratedValue(generator = "iDGenerator")
@Column(name="column1", nullable=false, updatable=false)
private Long column1;

@Column(name="column2", length=5, nullable=false, updatable=false)
private String column2;

@Column(name="column3", length=10)
private String column3;

@Column(name="column4", insertable=false, updatable=false)
private Long column4;

@Column(name="cre_userid", length=30, nullable=false, updatable=false)
@CreatedBy
private String createdBy;

@Column(name="cre_date", nullable=false, updatable=false)
@CreatedDate
private LocalDateTime dateCreated;
...

Its table has a trigger that sets the value of column4 as follows:

CREATE OR REPLACE TRIGGER MY_TABLE_BIUFER 
BEFORE INSERT ON MY_TABLE
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
BEGIN
  :new.column4 := workflow_pkg.create_workflow (:new.column1);
END;

The controller and the service code snippets are listed below:

@Service
public class MyServiceImpl implements MyService{

    @Autowired
    MyTableRepository myTableRepo;

    @Override
    public MyTableDto getRecord(Long pk) {
        MyTableDto returnValue;
        MyTableEntity myTableEntity = myTableRepo.findById(pk)
                                                 .orElseThrow(() -> new IIBSException("Transaction Id " + pk + " not found. Process Terminated."));
        myTableDto = convertToDto(myTableEntity); //use ModelMapper to convert entity to dto
        return returnValue;
    }

    @Override
    @Transactional
    public Long saveRecord(MyTableDto myTableDto) {
        MyTableEntity myTableEntity, storedDetails;
        Long returnValue;

        myTableEntity = convertToEntity(myTableDto); //use ModelMapper to convert dto to entity
        storedDetails = myTableRepo.saveAndFlush(myTableEntity);

        returnValue = storedDetails.getQuotApplTransNo();
        return returnValue;                 
    }
}



@RestController
@RequestMapping
public class MyController {

    @Autowired
    MyService myService;

    @GetMapping(path="/{pk}")
    public MyTableDto getRecord (@PathVariable Long pk) throws MyException {        
        if (pk == null) throw new MyException(ErrorMessage.MISSING_PARAMETER.getErrorMessage());
        
        MyTableDto returnValue = myService.getRecord(pk);   
        return returnValue;
    }

    @PostMapping
    public MyTableDto saveRecord (@RequestBody MyTableDto myTableDto) throws MyException
    {       
        //.. validations .. //

        Long pk = myService.saveRecord(myTableDto); 
        if (pk == null) throw new MyException("Error in saving Request for Quotation Package");

        MyTableDto returnValue = myService.getRecord(pk);   
        return returnValue;
    }
}

Solution

When saving, there is no synchronization of the values in the database table back to the entity.

You must call EntityManager.refresh() after the save if you need these values.

There is not refresh on the JpaRepository. So you must inject the EntityManager into your service.

Answered By - Simon Martinelli
Answer Checked By - Willingham (JavaFixing Volunteer)