Hibernate Query Cache issue

Question

I've found a strange bug in my app. I've simplified it, and that is how it can be reproduced:

(I used DbUnit to create the tables and HSQLDB as a database, but that doesn't actually matter)

package test;

import java.io.IOException;
import java.io.Serializable;
import java.io.StringReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Set;

import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.JoinTable;
import javax.persistence.ManyToMany;
import javax.persistence.Table;

import org.dbunit.DatabaseUnitException;
import org.dbunit.database.DatabaseConnection;
import org.dbunit.database.IDatabaseConnection;
import org.dbunit.dataset.IDataSet;
import org.dbunit.dataset.xml.FlatXmlDataSetBuilder;
import org.dbunit.operation.DatabaseOperation;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
import org.springframework.orm.hibernate3.HibernateTemplate;

public class DatabaseBugReproduction {

    @Entity(name = "A")
    @Table(name = "a")
    public static class A {

        private int     id;
        private Set <B> bs;

        @Id
        public int getId() {
            return id;
        }

        @ManyToMany
        @JoinTable(
            name = "ab",
            joinColumns = @JoinColumn(name = "a_id"),
            inverseJoinColumns = @JoinColumn(name = "b_id")
        )
        public Set <B> getBs() {
            return bs;
        }

        void setId(int id) {
            this.id = id;
        }

        void setBs(Set <B> engines) {
            this.bs = engines;
        }
    }

    @Entity(name = "B")
    @Table(name = "b")
    public static class B {

        private int id;

        @Id
        public int getId() {
            return id;
        }

        void setId(int id) {
            this.id = id;
        }
    }

    private static SessionFactory getSessionFactory() throws SQLException, IOException, DatabaseUnitException {
        String driverClass = "org.hsqldb.jdbc.JDBCDriver";
        String jdbcUrl = "jdbc:hsqldb:mem:seoservertooltest";
        String dbUsername = "test";
        String dbPassword = "test";
        String dbDialect = "org.hibernate.dialect.HSQLDialect";
        Configuration config = new Configuration()//
        .setProperty("hibernate.connection.driver_class", driverClass)//
        .setProperty("hibernate.connection.url", jdbcUrl)//
        .setProperty("hibernate.connection.username", dbUsername)//
        .setProperty("hibernate.connection.password", dbPassword)//
        .setProperty("hibernate.dialect", dbDialect)//
        .setProperty("hibernate.hbm2ddl.auto", "create-drop")//
        .setProperty("hibernate.current_session_context_class", "thread")//
        .setProperty("hibernate.cache.use_query_cache", "true")//
        .setProperty("hibernate.cache.use_second_level_cache", "true")//
        .setProperty("hibernate.cache.region.factory_class", "net.sf.ehcache.hibernate.EhCacheRegionFactory")//
        .setProperty("hibernate.cache.region_prefix", "")//
        // .setProperty("hibernate.show_sql", "true")//
        // .setProperty("hibernate.format_sql", "true")//
        .addAnnotatedClass(A.class) //
        .addAnnotatedClass(B.class) //
        ;
        SessionFactory result = config.buildSessionFactory();
        try (Connection con = DriverManager.getConnection(jdbcUrl, dbUsername, dbPassword)) {
            con.createStatement().executeUpdate("SET DATABASE REFERENTIAL INTEGRITY FALSE;");
            String xml = "<?xml version='1.0' encoding='UTF-8'?>"//
                    + "<dataset>"//
                    + "<a id='1'/>"//
                    + "<b id='1'/>"//
                    + "<ab a_id='1' b_id='1' />"//
                    + "</dataset>";
            final IDatabaseConnection dbCon = new DatabaseConnection(con);
            try {
                final FlatXmlDataSetBuilder builder = new FlatXmlDataSetBuilder();
                final IDataSet dataSet = builder.build(new StringReader(xml));
                DatabaseOperation.CLEAN_INSERT.execute(dbCon, dataSet);
            } finally {
                dbCon.close();
            }
        }
        return result;
    }

    public static void main(String[] args) throws Exception {
        HibernateTemplate hibTemplate = new HibernateTemplate(getSessionFactory());
        hibTemplate.setCacheQueries(true);
        System.out.println(hibTemplate.find("select a.bs from A a"));
        System.out.println(hibTemplate.find("select a.bs from A a"));
    }
}

Output is:

[test.DatabaseBugReproduction$B@2942ce]
[null]

It looks like the cache is somehow misconfigured. Where is a mistake and how can I fix it?

Used:

• JDK 1.7.0_01 both x32 and x64
• Hibernate 3.6.7
• Ehcache 2.5.0
• Spring 3.1.0
• Database: works at least with HSQLDB, H2 and MySQL

Answer 1

After some debugging, I've found that there seems to be a problem with the Query Cache and collection queries. The method that dissembles collections to store in the cache always returns null.

In fact, after googling it up, it turns out that this problems is due to a bug in Hibernate. See the issue description for more information.

While this problem isn't fixed (it seems like it won't) you could re-write your query so you don't need a collection query:

public static void main(String[] args) throws Exception {
    HibernateTemplate hibTemplate = new HibernateTemplate(getSessionFactory());
    hibTemplate.setCacheQueries(true);
    //System.out.println(hibTemplate.find("select a.bs from A a"));
    //System.out.println(hibTemplate.find("select a.bs from A a"));
    System.out.println(hibTemplate.find("select bs from A a inner join a.bs as bs"));
    System.out.println(hibTemplate.find("select bs from A a inner join a.bs as bs"));
}

I've tested it and it works fine.