JPA Native Query with call to Oracle function returns the same object in different calls in same Transaction context

Question

I'm having a problem regarding the execution of a query in a managed transaction context (Using JTA Transaction Factory)

During the entire execution of the request the query should be executed twice: the first time fetching default values from database; the second time, run with different parameters, it should return a different object with different values.

The query itself makes a call to a function in an Oracle database like this:

SELECT attr1, attr2, attr3
FROM TABLE(package.function (
    param1 => :param1,
    param2 => :param2,
    param3 => :param3))

The method which does the query (and which must be executed twice with different parameters) is something like:

public MyEntity getMyEntity(Map<String,String> params) {
    String sql = getQuery(); // gets the string of the aforementioned query
    Query query = getEntityManager().createNativeQuery(sql, MyEntity.class);

    query.setParameter("param1", params.get("param1"));
    query.setParameter("param2", params.get("param2"));
    query.setParameter("param3", params.get("param3"));

    return (MyEntity) query.getSingleResult();
}

The problem is that during the execution of the request, the first time this method gets called, it returns a certain MyEntity object which is correct. However, the second time the function gets called, the function getMyEntity wrongly returns the very same object (his Java Object reference is the same of the first object's), although the parameters with which it gets called are different.

It seems like a caching problem; so I explicitly added to my persistence.xml file the following properties

<property name="hibernate.cache.provider_class" value="org.hibernate.cache.SingletonEhCacheProvider"/>
<property name="hibernate.cache.use_second_level_cache" value="false"/>
<property name="hibernate.cache.use_query_cache" value="false"/>

and set a query hint

query.setHint(QueryHints.CACHEABLE, false);

but the problem still remains.

I would like to ask if I am missing something and if there is a way of solving this problem.

Note: the code is part of a project which is a porting of an old application to a new version based on RESTful Apis, so changing the logic structure of the code is not an option.

Answer 1

The JPA specification forces

getEntityManager().createNativeQuery(sql, MyEntity.class);

To return managed instances of MyEntity. You are causing the MyEntity instance to be built by the first query to be managed and tracked for changes, and of course, its identity will be managed. Once an entity is loaded, that same instance is returned every time you query for that instance. So when your next query returns a row with similar ID values, the previous entity is returned instead.

Some options, depending on your provider are:

1. clear the cache. Calling em.clear() might help if you are in a transaction, otherwise you may need to evict the entity from the shared cache.
2. force a refresh. EclipseLink has a refresh query hint that if used on the second query, will cause the entity to be reloaded with the second set of data
3. Do not use managed entities for functions that are better suited for raw data. If all you want is a DTO for the data, use the constructor option as outlined here

Option 3 makes much more sense to me, as there is less risk of corrupting the cache and objects returned from other queries.