Spring Batch - HibernateItemWriter : Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port

Question

I have a Spring Batch application that uses HibernateItemWriter to update the Azure SQL Server database.

The batch job gets executed for 20mins and failed with the following error

Could not open Hibernate Session for transaction; nested exception is org.hibernate.exception.JDBCConnectionException: Unable to acquire JDBC connection

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host sqlsrv-01.database.windows.net, port 1433 has failed. Error: "sqlsrv-01.database.windows.net: Temporary failure in name resolution. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.".

Below is my implementation

public class StoreWriter implements ItemWriter<List<Store>> {

Logger logger = Logger.getLogger(StoreWriter.class);

@Autowired
private SessionFactory sessionFactory;

@Override
public void write(List<? extends List<Store>> items) throws Exception {
    HibernateItemWriter<Store> hibernateItemWriter = new HibernateItemWriter<>();
    hibernateItemWriter.setSessionFactory(sessionFactory);

    for (List<Store> Store : items) {

        hibernateItemWriter.write(Store);

    }
    hibernateItemWriter.afterPropertiesSet();
    logger.info(String.format("Store Processing Completed %s", new LocalDateTime()));
}}

I guess it is a temporary issue however I want to know if I can implement a retry logic to handle this issue?

Answer 1

The StoreWriter does not add any value over the HibernateItemWriter. You could use the HibernateItemWriter directly in your step.

This StoreWriter creates a new HibernateItemWriter for each chunk, which is not efficient and could be the root cause of your issue (ie a new session is created for each chunk, and at some point, it is not possible to acquire a new session anymore, hence the error Could not open Hibernate Session for transaction). The delegate writer should be refactored as a field, something like:

public class StoreWriter implements ItemWriter<List<Store>> {

   Logger logger = Logger.getLogger(StoreWriter.class);

   private HibernateItemWriter<Store> hibernateItemWriter;

   public StoreWriter(HibernateItemWriter<Store> hibernateItemWriter) {
      this.hibernateItemWriter = hibernateItemWriter;
   }

   @Override
   public void write(List<? extends List<Store>> items) throws Exception {
      for (List<Store> Store : items) {
        hibernateItemWriter.write(Store);
      }
      logger.info(String.format("Store Processing Completed %s", new LocalDateTime()));
   }
}