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.
