|
Hi,
i tried to use hibernate query cache to cache a long running SQL-Statement like this:
@NamedNativeQuery(name="Position.countDistinctUrls",
query="SELECT count(distinct a.url) as counturls FROM( "+
"SELECT distinct tera.url "+
"FROM seostar_teradata tera " +
"WHERE tera.datum<=:dateTo AND tera.datum>=:dateFrom "+
"AND tera.url like :url AND tera.keyword like :keyword "+
"UNION " +
"SELECT distinct pos.url "+
"FROM seostar_position pos " +
"WHERE pos.valid_from <= :dateTo AND pos.valid_to >= :dateFrom "+
"AND pos.url like :url AND pos.keyword like :keyword "+
")a",resultSetMapping="DistinctUrls")
In my DAO I added hints to this Query:
@Override
public Long countDistinctUrls(String urlFilter, String keywordFilter,
Date from, Date to) { urlFilter = StringUtils.escapeSQL(urlFilter).toLowerCase(); keywordFilter = StringUtils.escapeSQL(keywordFilter); Query query= getEntityManager().createNamedQuery("Position.countDistinctUrls"); query.setHint(QueryHints.HINT_CACHEABLE, true); query.setHint(QueryHints.HINT_READONLY, true); query.setParameter("url", urlFilter); query.setParameter("keyword", keywordFilter); query.setParameter("dateFrom", from); query.setParameter("dateTo", to); Object result = query.getSingleResult(); return ((BigInteger)result).longValue(); }
Without using a SqlResultSetMapping it's not even possible to write the Data into the Cache. Exception is:
java.lang.IllegalStateException: aliases expected length is 0; actual length is 3
Setting SqlResultSetMapping to
@SqlResultSetMapping(
name="TrendbyFilter",
columns= {@ColumnResult(name="pos",type=BigInteger.class), @ColumnResult(name="weight",type=BigInteger.class), @ColumnResult(name="urlkeywords",type=String.class)}
)
writing into Cache works, but reading from it doesn't! And thats the point where I'm not able to fix this any more.
If you look how the NamedQueryRepository gets filled you'll see that the type value of all NativeSQLQueryScalarReturn-Objects always is null. The Type of @ColumnResult never gets used here. And that's why the CacheableResultTransformer throws a NullPointerException when trying to untransform.
|
