Created postgres sequence, and GRPC service hit this sequencer to generate a new sequence. When ever it required new sequence, grpc service is hit and it will provide a new sequence, now this sequence can be used as some serial/ID etc.Long story short, Donot need to save this sequence in any table or column, Hit service get the sequence. service is working fine, but the problrm is after every 45-60 min service need to restart. Tried lot of solution mention is Stackoverflow Postgres connection has been closed error in Spring Boot
but it didnot help,
Getting this error
HikariPool-1 - Connection is not available, request timed out after 30000ms.",org.hibernate.engine.jdbc.spi.SqlExceptionHelper.logExceptions(SqlExceptionHelper.java:142)
response_description: Could not open JPA EntityManager for transaction; nested exception is org.hibernate.exception.JDBCConnectionException: Unable to acquire JDBC Connection
Could someone please look suggest, what doing wrong here?
Thanks in Advance !!
@Repository
public class SequenceNumberRepository {
@PersistenceContext
private EntityManager entityManager;
@Transactional
public BigInteger getNextSequenceNumber(String sequencer) {
BigInteger sequence = null;
try {
Query query = entityManager.createNativeQuery("SELECT nextval(:param)");
query.setParameter("param", ConstantUtil.SEQUENCER_NUMBER + ConstantUtil.PERIOD_CONSTANT +sequencer);
sequence = (BigInteger) query.getSingleResult();
}catch(Exception e){
e.printStackTrace();
} finally {
entityManager.close();
}
return sequence;
}
}
application.yml
spring:
config:
activate:
on-profile: dev
datasource:
url: "jdbc:postgresql://<server>:<port>/abc_dev?currentSchema=sequence_number"
username: abc
password: abc
test-on-borrow: true
validation-query: SELECT 1;
jpa:
properties:
hibernate:
dialect: org.hibernate.dialect.PostgreSQLDialect