@Transactional on Spring shutdown to properly shutdown Hsqldb

Question

The heart of this question is: Is it possible to execute a Transaction from a method triggered by a Spring shutdown hook?

At the moment I have a HyperSqlDbServer class that implements SmartLifeCycle as found in this question: In a spring bean is it possible to have a shutdown method which can use transactions?

I have a method in that class that is marked transactional that gets invoked as part of the stop method:

@Transactional
public void executeShutdown() {
    hsqlDBShutdownService.executeShutdownQuery();
    hsqlDBShutdownService.closeEntityManager();
}

The service used in that method is a bit of a hack that I had to do because I could not autowire in the EntityManager to this class:

@Service
public class HsqlDBShutdownService {

    @PersistenceContext
    private EntityManager entityManager;

    @Autowired
    private HyperSqlDbServer hyperSqlDbServer;

    @Transactional
    public void executeShutdownQuery() {
        entityManager.createNativeQuery("SHUTDOWN").executeUpdate();
    }

    @Transactional
    public void closeEntityManager() {
        entityManager.close();
    }

    @PostConstruct
    public void setHsqlDBShutdownService() {
        hyperSqlDbServer.setShutdownService(this);
    }
}

You may notice that all I'm really trying to accomplish is invoking the query "SHUTDOWN" before stopping the server. Without this, the hsqldb lock file sticks around on server restart, and the server throws an exception.

The code above produces the following exception:

javax.persistence.TransactionRequiredException: Executing an update/delete query
    at org.hibernate.ejb.AbstractQueryImpl.executeUpdate(AbstractQueryImpl.java:96)
        ...

So my original question stands, but if anyone has a thought on how I could execute this query another way I'll try that as well.

FYI, I've also tried the @PreDestroy annotation, but get the same TransactionRequiredException.

Edit: For completeness, I am using the JpaTransactionManager and the @Transactional annotations work throughout my project, except on shutdown...

Edit 2: Datasource and transaction manager configuration:

@Configuration
@EnableTransactionManagement
@PropertySource("classpath:persistence.properties")
public class PersistenceConfig implements TransactionManagementConfigurer {

    private static final String PASSWORD_PROPERTY = "dataSource.password";
    private static final String USERNAME_PROPERTY = "dataSource.username";
    private static final String URL_PROPERTY = "dataSource.url";
    private static final String DRIVER_CLASS_NAME_PROPERTY = "dataSource.driverClassName";

    @Autowired
    private Environment env;

    @Bean
    @DependsOn("hsqlDb")
    public DataSource configureDataSource() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName(env.getProperty(DRIVER_CLASS_NAME_PROPERTY));
        dataSource.setUrl(env.getProperty(URL_PROPERTY));
        dataSource.setUsername(env.getProperty(USERNAME_PROPERTY));
        dataSource.setPassword(env.getProperty(PASSWORD_PROPERTY));
        return dataSource;
    }

    @Bean
    @DependsOn("hsqlDb")
    public LocalContainerEntityManagerFactoryBean configureEntityManagerFactory() {
        LocalContainerEntityManagerFactoryBean entityManagerFactoryBean = new LocalContainerEntityManagerFactoryBean();
        entityManagerFactoryBean.setDataSource(configureDataSource());
        entityManagerFactoryBean.setPackagesToScan("com.mycompany.model.db");
        entityManagerFactoryBean.setJpaVendorAdapter(new HibernateJpaVendorAdapter());

        Properties jpaProperties = new Properties();
        jpaProperties.put(org.hibernate.cfg.Environment.DIALECT, env.getProperty(org.hibernate.cfg.Environment.DIALECT));
        jpaProperties.put(org.hibernate.cfg.Environment.HBM2DDL_AUTO, env.getProperty(org.hibernate.cfg.Environment.HBM2DDL_AUTO));
        jpaProperties.put(org.hibernate.cfg.Environment.SHOW_SQL, env.getProperty(org.hibernate.cfg.Environment.SHOW_SQL));
        jpaProperties.put(org.hibernate.cfg.Environment.HBM2DDL_IMPORT_FILES_SQL_EXTRACTOR, env.getProperty(org.hibernate.cfg.Environment.HBM2DDL_IMPORT_FILES_SQL_EXTRACTOR));
        jpaProperties.put(org.hibernate.cfg.Environment.HBM2DDL_IMPORT_FILES, env.getProperty(org.hibernate.cfg.Environment.HBM2DDL_IMPORT_FILES));
        entityManagerFactoryBean.setJpaProperties(jpaProperties);

        return entityManagerFactoryBean;
    }

    @Override
    @Bean()
    @DependsOn("hsqlDb")
    public PlatformTransactionManager annotationDrivenTransactionManager() {
        return new JpaTransactionManager();
    }

}

Answer 1

I found a workaround for shutting down the HsqlDB database, but it involves avoiding the use of Spring's EntityManager and @Transactional as they apparently do not work during server shutdown. My modified HsqlDBShutdownService is below. The key change is that instead of using the EntityManager to invoke the query, I create a new jdbc connection manually, and invoke the query that way. This avoids the requirement for @Transactional:

@Service
public class HsqlDBShutdownService  {

    @Autowired
    private ApplicationContext applicationContext;

    @PersistenceContext
    private EntityManager entityManager;

    @Autowired
    private HyperSqlDbServer hyperSqlDbServer; 

    public void executeShutdownQuery() {

        Connection conn = null;
        try {
            JdbcTemplate jdbcTemplate = new JdbcTemplate(this.applicationContext.getBean(DataSource.class));
            conn = DataSourceUtils.getConnection(jdbcTemplate.getDataSource());
            conn.setAutoCommit(true);
            jdbcTemplate.execute("SHUTDOWN"); 
        } catch(Exception ex) {
            ex.printStackTrace();
        } finally {
            try {
                if(conn != null)
                    conn.close();
            } catch(Exception ex) {
                ex.printStackTrace();
            }
        }
    }

    @Transactional
    public void closeEntityManager() {
        entityManager.close();
    }

    @PostConstruct
    public void setHsqlDBShutdownService() {
        hyperSqlDbServer.setShutdownService(this);
    }

}

The server can now restart successfully without leaving Hsqldb lock files around.