hibernate存储过程查询缓存(hibernate stored procedure query cache)

 我们使用hibernate将存储过程结果映射到java对象。 它使用hibernate 4.1.x在SQL Server 2008上执行 
 
@XmlRootElement(name = "hotel")
@Entity
@NamedNativeQuery(name = "fetchHotel",
query = "{ call usp_iconnect_dashboard_gethotels(:screenname) }",
resultSetMapping = "hotel-data",
hints = {@QueryHint(name = "org.hibernate.callable", value = "true") }
)
@SqlResultSetMapping(name = "hotel-data",
entities = @EntityResult(entityClass = Hotel.class))
@Cache(usage = CacheConcurrencyStrategy.READ_ONLY)
public class Hotel {

    /**
     * Unique hotel code.
     */
    @Id
    @Column(name = "hotelcode")
    private String code;

    /**
     * Hotel name.
     */
    @Column(name = "hotel")
    private String name;

    /**
     * Indicates if user belongs to hotel.
     * Y - if it is default hotel.
     * N - if it is not default hotel.
     */
    @Column(name = "is_default")
    private String isDefault;

    /**
     * @return the code
     */
    public final String getCode() {
        return code;
    }

    /**
     * @param code the code to set
     */
    public final void setCode(final String code) {
        this.code = code;
    }

    /**
     * @return the name
     */
    public final String getName() {
        return name;
    }

    /**
     * @param name the name to set
     */
    public final void setName(final String name) {
        this.name = name;
    }

    /**
     * @return the isDefault
     */
    public final String getIsDefault() {
        return isDefault;
    }

    /**
     * @param isDefault the isDefault to set
     */
    public final void setIsDefault(final String isDefault) {
        this.isDefault = isDefault;
    }

}
 
 它可以很好地获取结果。 
 
 但是当启用二级缓存（ehcache）和查询缓存时。 
 
<property name="hibernateProperties">
    <props>
    <prop key="hibernate.dialect">${dialect}</prop>
            <prop key="hibernate.show_sql">${showSQL}</prop>
            <prop key="format_sql">true</prop>
            <prop key="hibernate.current_session_context_class">thread</prop>
            <prop key="hibernate.connection.release_mode">on_close</prop>
            <prop key="hibernate.transaction.factory_class">org.hibernate.transaction.JDBCTransactionFactory</prop>
            <prop key="hibernate.cache.provider_class">org.hibernate.cache.EhCacheProvider</prop>
            <prop key="hibernate.cache.region.factory_class">org.hibernate.cache.ehcache.EhCacheRegionFactory</prop>
            <prop key="hibernate.cache.use_second_level_cache">true</prop>
            <prop key="hibernate.cache.use_query_cache">true</prop>
        </props>
    </property>
 
 它的工作正常，可以添加查询缓存。 但是在几次通话之后，它给出的错误如下。 
 
DEBUG StandardQueryCache - Checking cached query results in region: DailyExpire
DEBUG EhcacheGeneralDataRegion - key: sql: { call usp_iconnect_dashboard_gethotels(?) }; parameters: ; named parameters: {screenname=dhulipah}; transformer: org.hibernate.transform.CacheableResultTransformer@110f2
DEBUG StandardQueryCache - Checking query spaces are up-to-date: [Hotel]
DEBUG EhcacheGeneralDataRegion - key: Hotel
DEBUG EhcacheGeneralDataRegion - Element for key Hotel is null
DEBUG StandardQueryCache - Returning cached query results
Hibernate: select hotel0_.hotelcode as hotelcod1_1_0_, hotel0_.is_default as is2_1_0_, hotel0_.hotel as hotel3_1_0_ from Hotel hotel0_ where hotel0_.hotelcode=?
WARN  SqlExceptionHelper - SQL Error: 208, SQLState: S0002
ERROR SqlExceptionHelper - Invalid object name 'Hotel'.
INFO  DefaultLoadEventListener - HHH000327: Error performing load command : org.hibernate.exception.SQLGrammarException: Invalid object name 'Hotel'.
Jul 22, 2013 3:25:39 PM com.sun.jersey.spi.container.ContainerResponse mapMappableContainerException
SEVERE: The RuntimeException could not be mapped to a response, re-throwing to the HTTP container
org.hibernate.exception.SQLGrammarException: Invalid object name 'Hotel'.
 
 与上面的日志日志一样，它在一段时间后尝试获取SELECT查询而不是存储过程调用。 
 
 知道为什么会这样发生吗？ 因此，查询缓存不能用于在hibernate中缓存存储过程。 是否有任何其他方法可以将缓存用于存储过程结果？ 
 
 提前感谢您的帮助。 

We are using hibernate to map stored procedure result to java object. Its using hibernate 4.1.x for executing on SQL server 2008
 
@XmlRootElement(name = "hotel")
@Entity
@NamedNativeQuery(name = "fetchHotel",
query = "{ call usp_iconnect_dashboard_gethotels(:screenname) }",
resultSetMapping = "hotel-data",
hints = {@QueryHint(name = "org.hibernate.callable", value = "true") }
)
@SqlResultSetMapping(name = "hotel-data",
entities = @EntityResult(entityClass = Hotel.class))
@Cache(usage = CacheConcurrencyStrategy.READ_ONLY)
public class Hotel {

    /**
     * Unique hotel code.
     */
    @Id
    @Column(name = "hotelcode")
    private String code;

    /**
     * Hotel name.
     */
    @Column(name = "hotel")
    private String name;

    /**
     * Indicates if user belongs to hotel.
     * Y - if it is default hotel.
     * N - if it is not default hotel.
     */
    @Column(name = "is_default")
    private String isDefault;

    /**
     * @return the code
     */
    public final String getCode() {
        return code;
    }

    /**
     * @param code the code to set
     */
    public final void setCode(final String code) {
        this.code = code;
    }

    /**
     * @return the name
     */
    public final String getName() {
        return name;
    }

    /**
     * @param name the name to set
     */
    public final void setName(final String name) {
        this.name = name;
    }

    /**
     * @return the isDefault
     */
    public final String getIsDefault() {
        return isDefault;
    }

    /**
     * @param isDefault the isDefault to set
     */
    public final void setIsDefault(final String isDefault) {
        this.isDefault = isDefault;
    }

}
 
Its working fine to fetch results.
 
However when enabled second level cache (ehcache) and query cache.
 
<property name="hibernateProperties">
    <props>
    <prop key="hibernate.dialect">${dialect}</prop>
            <prop key="hibernate.show_sql">${showSQL}</prop>
            <prop key="format_sql">true</prop>
            <prop key="hibernate.current_session_context_class">thread</prop>
            <prop key="hibernate.connection.release_mode">on_close</prop>
            <prop key="hibernate.transaction.factory_class">org.hibernate.transaction.JDBCTransactionFactory</prop>
            <prop key="hibernate.cache.provider_class">org.hibernate.cache.EhCacheProvider</prop>
            <prop key="hibernate.cache.region.factory_class">org.hibernate.cache.ehcache.EhCacheRegionFactory</prop>
            <prop key="hibernate.cache.use_second_level_cache">true</prop>
            <prop key="hibernate.cache.use_query_cache">true</prop>
        </props>
    </property>
 
Its working fine to add query cache. However after couple of calls, its giving error as below.
 
DEBUG StandardQueryCache - Checking cached query results in region: DailyExpire
DEBUG EhcacheGeneralDataRegion - key: sql: { call usp_iconnect_dashboard_gethotels(?) }; parameters: ; named parameters: {screenname=dhulipah}; transformer: org.hibernate.transform.CacheableResultTransformer@110f2
DEBUG StandardQueryCache - Checking query spaces are up-to-date: [Hotel]
DEBUG EhcacheGeneralDataRegion - key: Hotel
DEBUG EhcacheGeneralDataRegion - Element for key Hotel is null
DEBUG StandardQueryCache - Returning cached query results
Hibernate: select hotel0_.hotelcode as hotelcod1_1_0_, hotel0_.is_default as is2_1_0_, hotel0_.hotel as hotel3_1_0_ from Hotel hotel0_ where hotel0_.hotelcode=?
WARN  SqlExceptionHelper - SQL Error: 208, SQLState: S0002
ERROR SqlExceptionHelper - Invalid object name 'Hotel'.
INFO  DefaultLoadEventListener - HHH000327: Error performing load command : org.hibernate.exception.SQLGrammarException: Invalid object name 'Hotel'.
Jul 22, 2013 3:25:39 PM com.sun.jersey.spi.container.ContainerResponse mapMappableContainerException
SEVERE: The RuntimeException could not be mapped to a response, re-throwing to the HTTP container
org.hibernate.exception.SQLGrammarException: Invalid object name 'Hotel'.
 
As in log log above, its trying to fetch as SELECT query instead of stored procedure call after some time.
 
Any idea why is it happening like this ? Due to this, query cache can't be used for caching stored procedure in hibernate. Is there is any other way to use caching for stored procedure results ?
 
Thank you in advance for help.

原文：https://stackoverflow.com/questions/17792067