JPA Second level query cache configuration with hibernate

I tried to enable second level cache for queries in a standalone sample I made myself.
What I noticed at first is that even when I give queries to be cacheable the hibernate still executed SQL-s.

here is the source of the persistence.xml:

<?xml version="1.0" encoding="UTF-8"?>
<persistence xmlns="http://java.sun.com/xml/ns/persistence" version="1.0">

    <persistence-unit name="defaultPeristenceUnit">
        <jta-data-source>java:/DefaultDS</jta-data-source>
        <properties>
            <property name="hibernate.hbm2ddl.auto" value="create-drop"/>
            <property name="hibernate.show_sql" value="true"/>
            <property name="hibernate.format_sql" value="true"/>
            
            <property name="hibernate.cache.use_second_level_cache" value="true"/>
            <property name="hibernate.cache.use_query_cache" value="true"/>
            <property name="hibernate.ejb.classcache.com.ejbtest.Address" value="transactional"/>
        </properties>
    </persistence-unit>

</persistence>

Here is my TestNG test AddressTest.java:

package com.ejbtest;

import org.testng.annotations.Test;

@Test(groups = {"default"})
public class AddressTest {

    @Test
    public void testAddress() {
        AddressFacade facade = (AddressFacade) EJB3Container.lookup("xxx/AddressFacadeEJB");
        for (int i=0; i < 10; i++) {
            facade.createAddress("Address "+i, "town"+i);
        }
        System.out.println("---------------------------H1-----------------------------");
        facade.getAll();
        System.out.println("---------------------------H2-----------------------------");
        facade.getAll();
    }
}

Here is my stateless ejb :

package com.ejbtest;

import javax.ejb.*;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import java.util.List;

@Stateless(name = "AddressFacadeEJB")
@Local(AddressFacade.class)
public class AddressFacadeBean implements AddressFacade {
    @PersistenceContext(name = "defaultPeristenceUnit")
    private EntityManager em;

	...
    public List<Address> getAll() {
        return (List<Address>) em.createNamedQuery("Address.findAll").getResultList();
    }

}

My named query in the entity bean is like that:

        @NamedQuery(
                name = "Address.findAll",
                query = "select OBJECT(a) from AddressEntityEJB a"
        )

And the result from system out:

---------------------------H1-----------------------------
Hibernate: 
    select
        address0_.id as id1_,
        address0_.address as address1_,
        address0_.town as town1_,
        address0_.crDate as crDate1_,
        address0_.chDate as chDate1_ 
    from
        AddressEntityEJB address0_
---------------------------H2-----------------------------
Hibernate: 
    select
        address0_.id as id1_,
        address0_.address as address1_,
        address0_.town as town1_,
        address0_.crDate as crDate1_,
        address0_.chDate as chDate1_ 
    from
        AddressEntityEJB address0_

Here I noticed that SQL is executed twice, and at first glance I thought that cache needs to be configured with some provider. Then I did a little bit research. I found that the query itself should also be told to be cacheable (except in the persistence configuration). This can be done in two ways - via hibernate annotation, and via Query Hint. Well, if we use hibernate annotation we have to include hibernate in the build class path, and I don't want that. So I choosed to include it as a hint (sounds more portable solution).

And here is the new look of the named query:

@NamedQueries({
        @NamedQuery(
                name = "Address.findAll",
                query = "select OBJECT(a) from AddressEntityEJB a",
                hints = {@QueryHint(name = "org.hibernate.cacheable", value = "true")}
        )
})

With this query hint we tell the query to use property "org.hibernate.cacheable=true" and thus telling hibernate to cache the results from this query if we use it with the same parameters more times (in our case we do not have parameters).

Let's start the example again to see the the system out:

---------------------------H1-----------------------------
Hibernate: 
    select
        address0_.id as id1_,
        address0_.address as address1_,
        address0_.town as town1_,
        address0_.crDate as crDate1_,
        address0_.chDate as chDate1_ 
    from
        AddressEntityEJB address0_
---------------------------H2-----------------------------

Now it is obvious that query cache works, no SQL is required from the database.

I hope this will help you.

PS: my environment is: jboss EJB3 standalone container (jboss-EJB-3.0_Embeddable_ALPHA_9.zip), JNDI names are given via jboss.xml specific deployment descriptor, test is run with TestNG.