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?

Mahmoud Ben Hassine · Accepted Answer · 2021-09-01 07:21:39Z

1

+50

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

answered Sep 1, 2021 at 7:21

Mahmoud Ben Hassine

32.1k5 gold badges38 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

One Developer Over a year ago

how do we pass the 'hibernateItemWriter' to 'StoreWriter' bean especially with 'sessionFactory' object?

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related