Spring 4 + HIbernate 4 + Junit @Transactional nativeSQL query

Question

Hi I've a problem with Spring Hibernate session management in some JUnit tests.

I have a test class signed as @Transactional in order to have the autorollback feature for each test method; during a test i insert three rows into the database and then run a native query (using session.createSQLQuery()) which should return a list containing all three elements i've just saved, but the result of the native query is always empty.

I've tryied to remove @Transactional from test class so the insert process will end with a commit, in this scenario my native query works perfectly and the test is green but no rollback is perfomed (due to the lack of @Transactional).

Personally I would prefer to not sign test with @Rollback(false) and remove the data manually so I was wondering if this strange behavior was caused by incorrect configuration.

Thanks in advance.

Below my Spring session and transaction manager configuration, incriminated test and native query under test.

Spring Config:

@Bean(name = "sessionFactory")
@Autowired
public SessionFactory getSessionFactory(final DataSource dataSource) throws Exception {
        final LocalSessionFactoryBuilder springSessionFactoryBean = new LocalSessionFactoryBuilder(dataSource);
        springSessionFactoryBean.addAnnotatedClasses(CvSentEvent.class);

        final Properties hibernateProperties = new Properties();
        hibernateProperties.put("hibernate.connection.driver_class", "org.postgresql.Driver");
        hibernateProperties.put("hibernate.cache.use_second_level_cache", true);
        hibernateProperties.put("hibernate.hbm2ddl.auto", "validate");
        hibernateProperties.put("hibernate.dialect", "org.hibernate.dialect.PostgreSQLDialect");
        hibernateProperties.put("hibernate.format_sql", false);
        hibernateProperties.put("hibernate.jdbc.use_scrollable_resultsets", true);
        hibernateProperties.put("hibernate.cache.region.factory_class", "org.hibernate.cache.ehcache.EhCacheRegionFactory");
        hibernateProperties.put("org.hibernate.cache.ehcache.configurationResourceName", "ehcache.xml");

        springSessionFactoryBean.addProperties(hibernateProperties);

        return springSessionFactoryBean.buildSessionFactory();
    }

Method under Test

@Override
@Transactional(readOnly = true)
public List<CvSentReport> searchByWebsiteAccount(final String website, final String account, final String cvs, final String budgetFIlter) {
        final String sql = ""
                + "select sent, website, account from (select "
                + "    sum(c.count) as sent, "
                + "    c.website, "
                + "    c.sponsoraccountname as account "
                + " from "
                + "    cvsentevents c "
                + " where "
                + "    c.website like :website "
                + "    and (c.sponsoraccountname like :account or (c.sponsoraccountname IS NULL and :account = '%%')) "
                + " group by "
                + "    c.website, "
                + "    c.sponsoraccountname ) z " + filterForCVs(cvs) + ";";

        final List<Object> list = new ArrayList<Object>();

        @SuppressWarnings("unchecked")
        final List<Object> itemList = getSession()
                .createSQLQuery(sql)
                .setParameter("website", "%" + website + "%")
                .setParameter("account", "%" + account + "%")
                .list();

        list.addAll(itemList);

        return createAListOfCvSentReports(budgetFIlter, list);

    }

Test code:

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(classes = { SpringJunitContext.class })
@Transactional
public class CvSentEventDaoTest {

    @Autowired
    private CvSentEventDao sut;

   @Test
    public void findAllTheItems() {
        newCvSentEvent(0, "website1", "account1", "1");
        newCvSentEvent(1, "website2", "account2", "1");
        newCvSentEvent(1, "website3", "account3", "0");

        final List<CvSentReport> actual = sut.searchByWebsiteAccount("website", "account", "all", "all");

        assertEquals(3, actual.size());
    }
}

Answer 1

Have had similar issue. Entities could not have been removed from H2 in mem during transactional test method invocation. Fixed it flushing current session within removal DAO method.