CODEWITHSUNDEEP
javaspringhibernatepostgresqljpa
Babak Behzadi
Babak Behzadi

Reputation: 1256

Auto reconnect JPA EntityManager after connection loss

I have an EntityManager bean object called in service classes as autowired object.

Spring Config class:

@EnableWebMvc
@Configuration("myWebConfiguration")
@ComponentScan(basePackages = {"org.my.app"})
@EnableScheduling
public class MyWebConfiguration extends WebMvcConfigurerAdapter {

....

    private static EntityManager entityManager; 

    @Bean
    public EntityManager entityManager() {
        if (entityManager == null) {
            entityManager = managerFactoryBean().getObject().createEntityManager();
        }
        return entityManager;
    }

....

    private Properties hibernateProperties() {
        Properties properties = new Properties();
        properties.setProperty("hibernate.hbm2ddl.auto", "update");
        properties.setProperty("hibernate.show_sql", "true");
        properties.setProperty("hibernate.format_sql", "true");
        properties.setProperty("hibernate.dialect", "org.hibernate.dialect.PostgreSQL9Dialect");
        return properties;
    }

....

}

Sample Service class:

@Service("sampleService")
public class SampleService {

    @Autowired
    protected EntityManager entityManager;

    public Object find(Class entityClass, Object id) {
        return entityManager.find(entityClass, id);
    }

    ....

}

And the problem:

If connection between WebApp server and DB server get lost, JPA and spring cannot reconnect to the DB Server and calling entityManager methods causes exceptions such as org.postgresql.util.PSQLException: This connection has been closed. and org.hibernate.exception.JDBCConnectionException: could not inspect JDBC autocommit mode.

Is it possible to detect connection loss automatically and re-establishing the connection in case of connection loss?

Upvotes: 3

Views: 3705

Answers (1)

Madhusudana Reddy Sunnapu
Madhusudana Reddy Sunnapu

Reputation: 8247

That is possible and that is a responsibility fulfilled by connection pooling (or datasource that supports connection pooling) libraries.

For example, If you use DBCP, you can set parameters like validationQuery="SELECT 1" and testOnBorrow="true" that allows detect the connection status and reopen if needed.

Similarly, c3p0 allows to configure such parameters using c3p0.testConnectionOnCheckout=true etc.

Other connection pooling libraries would expose similar configuration.

If you are already using a connection pooling library (or datasource that uses connection pool) you may want to check its documentation to get the relevant configuration properties to be set. If not, you may want to consider using a connection pooling library or a data source that uses connection pooling and exposes those parameters.

UPDATE: For c3p0 can you add the following properties and test:

properties.setProperty("hibernate.c3p0.preferredTestQuery","SELECT 1");
properties.setProperty("hibernate.c3p0.testConnectionOnCheckout","true");

Upvotes: 4

Related Questions