Eclipse Community Forums: EclipseLink » Native Queries and ResultSetMapping

Home » Eclipse Projects » EclipseLink » Native Queries and ResultSetMapping - caching problem(We want to perform two native queries on the same ResultSetMapping. Between the two queries, we manually update the object in OracleDb: we would like to see in the second query result the new manually)

Show: Today's Messages :: Show Polls :: Message Navigator

Native Queries and ResultSetMapping - caching problem [message #1745110]

Wed, 05 October 2016 09:47

Matteo Ferrari

Messages: 1
Registered: October 2016

Junior Member

Hi all,
we are experiencing a strange behaviour while executing NativeQueries with ResultSetMapping.

In short: we want to perform two native queries on the same ResultSet (LxObjAndLxSymb); between the two queries, we manually update the object in OracleDb: we would like to see in the second query result the new manually updated value, but we don't.

I'll try to explain with some code:


LxSymb symbBeforeManualUpdate = null, symbAfterManualUpdate = null;

//The first query

Query query = entityManager.createNativeQuery("SELECT obj.*, symb.* FROM lx_symb symb, lx_obj obj WHERE symb.obj_id = obj.obj_id and symb.symb_id = 405430", "LxObjAndLxSymb");
List<Object[]> retList = (List<Object[]>) query.getResultList();

symbBeforeManualUpdate = (LxSymb)retList.get(0)[1];

// **************************************************************
//
// Let's suppose symbBeforeManualUpdate .prop1 = 10;
// Then we manually update the LxSymb.prop1 value to 33 in ORACLE DB (e.g. via sqlplus)
//
// **************************************************************

//The second query

Query query2 = entityManager.createNativeQuery("SELECT obj.*, symb.* FROM lx_symb symb, lx_obj obj WHERE symb.obj_id = obj.obj_id and symb.symb_id = 405430", "LxObjAndLxSymb");
List<Object[]> retList2 = (List<Object[]>) query2.getResultList();

symbAfterManualUpdate = (LxSymb)retList2.get(0)[1];

// **************************************************************
//
//  We would expect to see symbAfterManualUpdate .prop1=33, instead we have symbAfterManualUpdate .prop1=10
//
// **************************************************************

Please note that we are not interested at all to cache, since in our environment we have many different actors writing and reading from/to database: we just need to always have fresh data at each query:

with NamedQueries, we are using proper Hints (QueryHints.CACHE_RETRIEVE_MODE=CacheRetrieveMode.BYPASS and QueryHints.CACHE_STORE_MODE=CacheStoreMode.REFRESH) and seems to be ok
with NativeQueries WITHOUT ResultSetMapping we are using Hint (QueryHints.CACHE_STORE_MODE=CacheStoreMode.REFRESH) and seems to be ok
NativeQueries WITH does not support CACHE_STORE_MODE hint, so the problem.

How can I force ResultSetMapping Native queries always have aligned data with Oracle DB?
And in general, how can I make eclipselink always ignore all kind of caches?

Thanks in advance for any help,
Matteo

Report message to a moderator