[
Date Prev][
Date Next][
Thread Prev][
Thread Next][
Date Index][
Thread Index]
[
List Home]
|
Re: [eclipselink-users] MySql problem with ReadAllQuery and pagination
|
I did some addition experimenting with the ReadAllQuery setFirstResult and
setMaxRows and believe that I have found a solution to my problem.
In looking at the second page request, where I set the starting row
(setFirstResult) to row 5 and set the max rows to return to 4, the -1 looked
odd. So I tried just hard-coding the starting row in the beginning to 5 and
got the same error on the initial execution of the query. Next I tried
hard-coding the starting row to 1. That worked and the bind value for the
starting row and max rows was: [1, 3].
That's when it hit me that the ReadAllQuery pagination was subtracts the
setFirstResult value from the setMaxRows value. Note that the normal JPA
Query does not do it this way!!!!
So I changed my code to this -
ReadAllQuery readAllQuery = new ReadAllQuery(MediaListing.class);
readAllQuery.setSelectionCriteria(exp);
readAllQuery.setFirstResult(startRow);
readAllQuery.setMaxRows(rowCount + startRow);
readAllQuery.prepareForExecution();
Session session = ((org.eclipse.persistence.jpa.JpaEntityManager)
em.getDelegate()).getActiveSession();
List<MediaListing> results = (List) session.executeQuery(readAllQuery);
...and like that, no more error.
The standard JPA Query class does have a different name for the maximum row
setting: setMaxResults
while the ReadAllQuery provides: setMaxRows
However, in the documentation (TopLink), I only found where setMaxRows was
defined as the maximum number of rows that the result set will return from
the database. I didn't find any mention of how the setMaxRows attribute was
tied to the setFirstResult attribute.
sonavor wrote:
>
> 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-tp24820917p24821291.html
Sent from the EclipseLink - Users mailing list archive at Nabble.com.
