Spring Data Jpa - Repository Query to get MaxID + 1?

I went through Spring CRUD repository: is there findOneByMaxXYZColumn()? and Spring data jpa. Find max if no result return default value already, but I am looking more from the correct implementation perspective in concurrent environment.

In my application for Employee table, tools like ETL and NiFi will add Employee in to Postgres DB, but when inserting PK ID sequence will not be maintain it could be any unique value.

Ex: Any value of Employees like 399, 430, 439, 444 etc. If we look, IDs between 400 to 429 is not occupied, but when I add Employee though Admin UI, it should always 445 in this case. I am using Spring Boot JPA Repository queries

Long findFirstByEmployeeIdAsc(); 

This way I will get Max Id (Maximum Value present in the PK column of the Employee Table) and while saving record in to DB, I will do +1 to ID. Is this correct way to work with concurrent environment ?

Is there any way with the help of Jpa Repository query we can get MAXID+1 value? If we used this and what if any exception occures will that Id be consumed ?

Any pointers?

1 Answer

Try this, hope it will work:

@Query(value = "SELECT coalesce(max(id), 0) FROM Product") \ public Long getMaxId();