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

Question

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;
    }
}

Simon Martinelli · Accepted Answer · 2022-08-30T12:13:29.453

0

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.

edited Aug 30 '22 at 12:13

answered Aug 30 '22 at 11:46

Simon Martinelli

34,053
5
48
82

Hi Simon, thanks for the immediately response. I'm new to JPA and my repository basically extends JpaRepository. The options are only save(), saveAndFlush(). flush().. there is no persist(), merge(), update(), refresh(). I appreciate if you can give me a link with sample code so I can try it out. Thanks a lot. – A Aragon Aug 30 '22 at 12:08
1

Thank you for your answer it gives me an idea. Since I don't want to inject the EntityManager in all my repositories, I created a custom repository to implement all hibernate methods such as refresh, persist, merge, remove, etc. and use this custom repositiory as my base repository class using @EnableJpaRepositories(repositoryBaseClass = MyCustomRepositoryImpl.class The custom repository is an interface that extends JpaRepository. All my repositories now extends from MyCustomRepository instead of JpaRepository. – A Aragon Aug 30 '22 at 17:42
Great ideas! If you don't mind, please accept my answer otherwise the question will stay open but you've solved the problem – Simon Martinelli Aug 31 '22 at 06:21
1

Thanks @Simon, will do. I will also include this [link](https://stackoverflow.com/questions/45491551/refresh-and-fetch-an-entity-after-save-jpa-spring-data-hibernate) as reference how to create a custom repository. – A Aragon Sep 01 '22 at 12:34

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

1 Answers1

Linked