I ran into some trouble using a named native query with setCacheable(true). I found a solution, but I'd like feedback on what I did; and maybe I have some suggested improvements to Hibernate and Hibernate Annotations.

The setup: The idea of the named query is to get the list of users who are managers (RIs) for a specified project. On the project page, the user can update the list of RIs (the RIs are a collection on the project). In other parts of the application, I need to know who the RIs are for the project (and it's child objects) in order to determine permissions.

The problem: After the user updates the RI list on our project page, the old (cached) RI list is still returned by the named native query.

My solution: Cast the Query to a SQLQuery, and associated it with the relevant tables using the addSynchronizedQuerySpace(String) method.



The details:

This is a much-simplified version of my named SQL query, for clarity:

@NamedNativeQuery(name = "user.managers", query = "select u.* from users_web u where u.unique_id IN ( select ri_id from ri_id_project_id where project_id = :projectId)", resultClass = User.class)

The ri_id_project_id table maps the project entity to a collection of User entities. The mapped property on the project entity looks like this:

@OneToMany(cascade = { CascadeType.MERGE, CascadeType.PERSIST, CascadeType.REMOVE }, fetch = FetchType.LAZY)
@JoinTable(name = "ri_id_project_id", joinColumns = @JoinColumn(name = "project_id"), inverseJoinColumns = @JoinColumn(name = "ri_id"))
@Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE)
...
public SortedSet<User> getManagers() { ... }

Here is the build(Session) method of my IQueryBuilder, which uses the "user.managers" named native query:

/**
* @see net.databinder.models.IQueryBuilder#build(org.hibernate.Session)
*/
public Query build(final Session hibernateSession) {
Query query = hibernateSession.getNamedQuery("user.managers");
query.setCacheable(true);

/*
* When using a cacheable SQL query, we have to explicitly set the query spaces (tables) that the query relies on.
* This ensures that the query cache doesn't return stale results when the relevant tables are updated.
*/
SQLQuery sqlQuery = (SQLQuery) query;
//get the database catalog name
String catalogName = null;
try {
catalogName = hibernateSession.connection().getCatalog();
}
catch (SQLException e) {
throw new RuntimeException("Could not retrieve the DB catalog name", e);
}
//add query spaces <-- here is my fix
sqlQuery.addSynchronizedQuerySpace(catalogName + ".dbo.users_web");
sqlQuery.addSynchronizedQuerySpace(catalogName + ".dbo.ri_id_project_id");

/*
* Set the query parameters
*/
...
return query;
}

One of my co-workers and I spent an hour or so stepping through Hibernate code in the debugger, and we determined that the "querySpaces" property was the source of the problem. When the named native query is constructed, the querySpaces property is null. querySpaces are the tables involved in the query; each query space is associated with a timestamp in the cache, and when an update is performed through hibernate, the timestamp changes. If a query cached result has an older timestamp than of any of its query spaces, the cached result is expired, and a fresh query is run against the DB.

In our case, the problem was that the named native query had no query spaces; i.e., no tables or entities were associated with the query which had to be checked for updates. I was able to add the query spaces; but I think that Hibernate should:
A. Try to determine the appropriate query spaces from a SQL query automatically
or:
B. Provide an easier/more obvious/well-documented mechanism for specifying the query spaces of a native query.

I would have liked to see a Hibernate annotation to accomplish this; ex:
@NamedNativeQuery(name = "user.managers", query = "select u.* from users_web u where u.unique_id IN ( select ri_id from ri_id_project_id where project_id = :projectId)", resultClass = User.class, querySpaces=@QuerySpaces({@QuerySpace(name="dbo.users_web"), @QuerySpace(name="dbo.ri_id_project_id")}))
I think additional documentation is also warranted.

What do you guys think? Is my solution the correct one? If not, what should I be doing differently? If so, can I add the suggestions to the JIRA?