I am trying to select TOP 10 records from DB2 database using OpenJPA. I am trying to get results as specified in query:
SELECT * FROM CACHE_REFRESH_TABLE FETCH FIRST 10 ROWS ONLY
For that I have enabled below property in my persistence.xml
.
<property name="openjpa.jdbc.DBDictionary" value="db2(SupportsSelectStartIndex=true,SupportsSelectEndIndex=true)"/>
But after enabling the property it throws an exception for other simple SELECT queries as I had mentioned here.
The Java code I am using to retrieve the TOP 10 records is as follows. But it does not work as it forms the SELECT as SELECT TOP 10 FROM CACHE_REFRESH_TABLE
and not as
SELECT * FROM CACHE_REFRESH_TABLE FETCH FIRST 10 ROWS ONLY
Below is the Java code.
public List<CacheHistoryOTO> fetchRefreshHistory(
int someInput1, String someInput2) throws SomeException {
LOGGER.info(EventMessages.METH_START, "fetchRefreshHistory");
List<CacheHistoryOTO> cacheHistoryOTOList = null;
try {
EntityManager entityManager = entityManagerProvider
.getEntityManager();
final Query query = entityManager
.createNamedQuery("topHistoryRecords");
query.setMaxResults(someInput1);//Depending on parameter someInput1 number of records to be fetched will be provided.
cacheHistoryOTOList = (List<CacheHistoryOTO>) query
.getResultList();
} catch (Exception e) {
LOGGER.error(EventMessages.ERROR, e);
throw new SomeException(Constant.ERROR_PERSISTENCE, e);
}
LOGGER.info(EventMessages.METH_END, "fetchRefreshHistory");
return cacheHistoryOTOList;
}
Named query is declared as follows.
@NamedQueries({
@NamedQuery(name = "topHistoryRecords", query = "SELECT cacheRefreshHistoryOTO FROM CacheHistoryOTO cacheRefreshHistoryOTO ORDER BY cacheRefreshHistoryOTO.refreshTimeStamp DESC")
})
Can someone please suggest to use the right way to do this?
I have looked at JPA 2 CriteriaQuery, using a limit but did not help.