[eclipselink-users] MySql problem with ReadAllQuery and pagination

[sonavor <jw@xxxxxxxxxxxxxx>]

I have a MySql database (v5.1) where I created a small database consisting of
several tables that use a variety of relationships (one-one, one-many,
many-to-many).  In testing how to provide a user with multiple search
options I decided to use the EclipseLink ExpressionBuilder.  This was
successful and I tested a bunch of search criteria combinations.

My ExpressionBuilder solution is used with a ReadAllQuery as follows:

ReadAllQuery readAllQuery = new ReadAllQuery(MediaListing.class);
readAllQuery.setSelectionCriteria(exp);
readAllQuery.setFirstResult(0);
readAllQuery.prepareForExecution();
Session session = ((org.eclipse.persistence.jpa.JpaEntityManager)
em.getDelegate()).getActiveSession();
List<MediaListing> results = (List) session.executeQuery(readAllQuery);

Once I was satisfied with the search results I got with this query I moved
on to implement pagination of the search results.  That is where I have run
into problems.

My updated ReadQllQuery for pagination is this -

ReadAllQuery readAllQuery = new ReadAllQuery(MediaListing.class);
readAllQuery.setSelectionCriteria(exp);
readAllQuery.setFirstResult(startRow);
readAllQuery.setMaxRows(rowCount);
readAllQuery.prepareForExecution();
Session session = ((org.eclipse.persistence.jpa.JpaEntityManager)
em.getDelegate()).getActiveSession();
List<MediaListing> results = (List) session.executeQuery(readAllQuery);

When I run it the first time, startRow = 0 and rowCount = 4
The EclipseLink logging shows this (the first page of the result set) -

Fine]: 2009-08-04
23:34:30.018--ServerSession(30709456)--Connection(19936982)--Thread(Thread[TP-Processor3,5,main])--SELECT
MEDIA_ID AS MEDIA_ID1, LAST_UPDATE AS LAST_UPDATE2, MEDIA_CODE AS
MEDIA_CODE3, MEDIA_RATING AS MEDIA_RATING4, TOTAL_PURCH_COUNT AS
TOTAL_PURCH_COUNT5, MEDIA_TITLE AS MEDIA_TITLE6, MEDIA_DESCRIPTION AS
MEDIA_DESCRIPTION7, PREDOMINATE_COLOR AS PREDOMINATE_COLOR8, VIEW_COUNT AS
VIEW_COUNT9, AVAILABILITY AS AVAILABILITY10, MEDIA_CATEGORY_ID AS
MEDIA_CATEGORY_ID11, AUTHOR_ID AS AUTHOR_ID12, MEDIA_TYPE_ID AS
MEDIA_TYPE_ID13, MEDIA_SUBJECT_ID AS MEDIA_SUBJECT_ID14, MEDIA_FORMAT_ID AS
MEDIA_FORMAT_ID15 FROM MEDIA_LISTING LIMIT ?, ?
        bind => [0, 4]

Notice the bind has the starting value of 0 and the maximum number of rows
to return as 4

When I click on my pagination control to go to page 2 the submitted startRow
value = 5 and the rowCount = 4
However, EclipseLink bombs with -

Exception [EclipseLink-4002] (Eclipse Persistence Services -
1.1.2.v20090612-r4475): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception:
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error
in your SQL syntax; check the manual that corresponds to your MySQL server
version for the right syntax to use near '-1' at line 1
Error Code: 1064
Call: SELECT MEDIA_ID AS MEDIA_ID1, LAST_UPDATE AS LAST_UPDATE2, MEDIA_CODE
AS MEDIA_CODE3, MEDIA_RATING AS MEDIA_RATING4, TOTAL_PURCH_COUNT AS
TOTAL_PURCH_COUNT5, MEDIA_TITLE AS MEDIA_TITLE6, MEDIA_DESCRIPTION AS
MEDIA_DESCRIPTION7, PREDOMINATE_COLOR AS PREDOMINATE_COLOR8, VIEW_COUNT AS
VIEW_COUNT9, AVAILABILITY AS AVAILABILITY10, MEDIA_CATEGORY_ID AS
MEDIA_CATEGORY_ID11, AUTHOR_ID AS AUTHOR_ID12, MEDIA_TYPE_ID AS
MEDIA_TYPE_ID13, MEDIA_SUBJECT_ID AS MEDIA_SUBJECT_ID14, MEDIA_FORMAT_ID AS
MEDIA_FORMAT_ID15 FROM MEDIA_LISTING LIMIT ?, ?
        bind => [5, -1]

Notice the bind shows the correct start row of 5 but for some reason the
maximum row count is -1 !!!!

Does anyone know what could be causing that?  I put in a debug statement and
verified that I was setting the readAllQuery.setMaxRows(rowCount); with a
rowCount value of 4.  

As a sanity check I created a similar scenario with a test table called
TEST_LISTING and tried my pagination control with a JPA Query provided by
the EntityManager.  It is defined like this -

Query q = em.createNamedQuery("TestListing.findByGroupingCode");
q.setParameter("groupingCode", groupCode);
q.setFirstResult(startingRow);
q.setMaxResults(rowCount);
List<TestListing> tList = q.getResultList();

In the test with the Query object my pagination works without any problems.

Is there a bug with the ReadAllQuery and setMaxRows ?  Or is it something to
do just with MySql and the ReadAllQuery?  Maybe some problem with the MySql
JDBC driver?

As one last test I ran the EclipseLink generated SQL -
SELECT MEDIA_ID AS MEDIA_ID1, LAST_UPDATE AS LAST_UPDATE2, MEDIA_CODE AS
MEDIA_CODE3, MEDIA_RATING AS MEDIA_RATING4, TOTAL_PURCH_COUNT AS
TOTAL_PURCH_COUNT5, MEDIA_TITLE AS MEDIA_TITLE6, MEDIA_DESCRIPTION AS
MEDIA_DESCRIPTION7, PREDOMINATE_COLOR AS PREDOMINATE_COLOR8, VIEW_COUNT AS
VIEW_COUNT9, AVAILABILITY AS AVAILABILITY10, MEDIA_CATEGORY_ID AS
MEDIA_CATEGORY_ID11, AUTHOR_ID AS AUTHOR_ID12, MEDIA_TYPE_ID AS
MEDIA_TYPE_ID13, MEDIA_SUBJECT_ID AS MEDIA_SUBJECT_ID14, MEDIA_FORMAT_ID AS
MEDIA_FORMAT_ID15 FROM MEDIA_LISTING LIMIT ?, ?

In a MySql query window and tried the various startRow and rowCount values
manually.  The MySql query window executed the query and returned the
expected results.

Thanks for any help with this matter.




-- 
View this message in context: http://www.nabble.com/MySql-problem-with-ReadAllQuery-and-pagination-tp24820917p24820917.html
Sent from the EclipseLink - Users mailing list archive at Nabble.com.