[
Date Prev][
Date Next][
Thread Prev][
Thread Next][
Date Index][
Thread Index]
[
List Home]
|
Re: [eclipselink-users] MySql problem with ReadAllQuery and pagination
|
Cool, Chris. Thanks.
I like your solution. I wasn't aware of the code -
javax.persistence.Query jpaquery =
((JpaEntityManager)em.getDelegate()).createQuery(readAllQuery);
I tried it out and it works great.
One other question I have regarding pagination involving the ReadAllQuery.
I usually try to just use the javax.persistence.Query with JPA QL. I only
use ExpressionBuilder with ReadAllQuery when I have a query that is joining
a many-to-many relationship along with additional relationship tables
(usually an advanced search screen type situation). That is the case here.
My question is about getting the total result count for the query
pagination. The way I am doing it now is I run the same ReadAllQuery
without any max row setting and capture the size of the result set. I only
do that once per query submit. After that the pagination mechanism just
calls the query with the starting row and max row value.
Is that the only way of solving the get count when a ReadAllQuery is
involved? In cases where I use a javax.persistence.Query I just create a
"count" query ( SELECT count(o) FROM MyJpaClass as o WHERE .... whatever
other query attributes are involved ).
Thanks again,
sonavor
christopher.delahunt wrote:
>
> Hello,
>
> While this is counter intuitive when using pagination, EclipseLink's
> maxRows and firstResult is based on using the statement to set the
> maxRows to return and then the resultset after the query executes to
> jump to the firstResult indicated. The MaxRows is applied before the
> firstResult. This means that values of 4 for maxrows and then 5 for the
> first result that it would skip to the 5th record when there are only
> 4. The maxRow value needs to be 9, with firstResult set to 5 to get 4
> rows back.
>
> When pagination was implemented, it used the same maxRows but had to
> correct for this when the value is passed to the limit function in
> MySQL, which is why you see a -1. To use the native maxRows with
> firstResult, you will need to add the first rows value to the maxResults.
>
> You will see different behaviour when using the JPA Query setMaxResults
> than when using the native setMaxRows. You can use
>
> ReadAllQuery readAllQuery = new ReadAllQuery(MediaListing.class);
> readAllQuery.setSelectionCriteria(exp);
> readAllQuery.prepareForExecution();
> javax.persistence.Query jpaquery =
> ((JpaEntityManager)em.getDelegate()).createQuery(readAllQuery);
> query.setMaxResults(rowCount);
> query.setFirstResult(startRow);
> List<MediaListing> results = query.getResultList();
>
>
> Or you can use the createQuery(Expression, Class) method and avoid
> creating the ReadAllQuery.
>
> Best Regards,
> Chris
>
> 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.
>>
>>
>>
>>
>>
> _______________________________________________
> eclipselink-users mailing list
> eclipselink-users@xxxxxxxxxxx
> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
>
>
--
View this message in context: http://www.nabble.com/MySql-problem-with-ReadAllQuery-and-pagination-tp24820917p24832502.html
Sent from the EclipseLink - Users mailing list archive at Nabble.com.
