2

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.

Community
  • 1
  • 1
Sam
  • 2,352
  • 4
  • 32
  • 45
  • 1
    seems like a dialect issue to me. `TOP 10` is something i'd expect to see in SQL-Server. Can you share the datasource setup? – Jan Dec 20 '15 at 08:08

0 Answers0