Skip to main content
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index] [List Home]
Re: [eclipselink-users] MySql problem with ReadAllQuery and pagination 
Thanks Sonavar,
I was specifically talking about effecient ways to execute a count
query for use in pagination.

I always have a ReadAllQuery, either gotten from a @NamedNativeQuery
for a @NamedQuery or some dynamic JPQL.

What I was wondering was, could you simply cast any ReadAllQuery to a
ReportQuery (or pass in a ReadAllQuery to a ReportQuery's constructor)
and have ReportQuery do the legwork to create a count query.

Currently for my generic pagination I do some substring nastiness to
extract the actual query being run (minus the rownum business for my
oracle queries) and surround it with a select count(*) from...

./tch



On Wed, Aug 5, 2009 at 10:32 PM, sonavor<jw@xxxxxxxxxxxxxx> wrote:
>
> tch,
>
> I am not sure which specific part you are referring to but for most JPA
> queries I use the basic -
>
> javax.persistence.Query
>
> and then use the EnitiyManager to create a named query  -
> Query q = em.createNamedQuery("MediaCategory.findAll");
>
> or build a query in a StringBuffer -
>
> StringBuffer qSB = new StringBuffer();
> qSB.append("SELECT count(m) FROM MediaListing as m ");
> ...with additional logic to build the WHERE clause...
>
> Then create it -
> Query q = em.createQuery(qSB.toString());
> ...add parameters for the where clause -
> q.setParameter("authIdArg", searchMedia.getAuthor().getUserId());
> and run it -
> count = ((Long)q.getSingleResult()).intValue();
>
> With the EclipseLink org.eclipse.persistence.queries.ReadAllQuery, I haven't
> used it all that much and until now haven't ever used the
> org.eclipse.persistence.queries.ReportQuery
>
> My understanding is that both of those query classes are designed to work
> with the EclipseLink org.eclipse.persistence.expressions.ExpressionBuilder
> and org.eclipse.persistence.expressions.Expression classes to build the
> query.
>
> To execute the ReadAllQuery I thought I had to always get the
> org.eclipse.persistence.sessions.Session object and use -
> Session session = ((org.eclipse.persistence.jpa.JpaEntityManager)
> em.getDelegate()).getActiveSession();
> following by -
> List<MediaListing> results = (List) session.executeQuery(readAllQuery);
>
> Chris Delahunt enlightened me that you can take the ReadAllQuery and use it
> with a javax.persistence.Query by doing -
> javax.persistence.Query jpaquery =
> ((JpaEntityManager)em.getDelegate()).createQuery(readAllQuery);
> ...setting whatever other Query attributes needed.....
> then executing -
> List<MediaListing> results = jpaquery.getResultList();
>
> Chris also pointed out that in the case of using ExpressionBuilder and a
> ReadAllQuery for data in a pagination scenario that retrieving all records
> with the ReadAllQuery to read the result set size was not efficient and
> costs memory space.  For the task of getting the total row count of the
> query for pagination purposes you can use the same ExpressionBuilder and
> Expression as the ReadAllQuery with a ReportQuery to just return the count
> as shown in examples of this thread.  Like the ReadAllQuery, the ReportQuery
> can be used with the org.eclipse.persistence.sessions.Session or with a
> javax.persistence.Query (see the examples of this in this thread).
>
> As for the pagination, both the javax.persistence.Query and the
> org.eclipse.persistence.queries.ReadAllQuery allow for retrieval of result
> rows by a specified start point and number of records to return.
>
> I hope that helps.
> Sonavor
>
>
>
>
>
>
> tch wrote:
>>
>> I just read the end of this thread, but this looks quite interesting.
>> Looking at the java docs real quick, it looks like this only works for
>> expressions not any ol' ReadAllQuery though, correct?
>>
>> ./tch
>>
>>
>>
>> On Wed, Aug 5, 2009 at 5:07 PM, sonavor<jw@xxxxxxxxxxxxxx> wrote:
>>>
>>> Chris,
>>>
>>> I got your ReportQuery method of getting the count of an
>>> ExpressionBuilder
>>> created query to work for me.
>>>
>>> I implemented it this way -
>>>
>>> ExpressionBuilder eb = new ExpressionBuilder(MediaListing.class);
>>> Expression exp = ...my constructed query expressions based on what
>>> user-selected search criteria was submitted...
>>>
>>> ReportQuery reportQuery = new ReportQuery(MediaListing.class, eb);
>>> reportQuery.addCount();
>>> reportQuery.setSelectionCriteria(exp);
>>> reportQuery.setShouldReturnWithoutReportQueryResult(true);
>>>
>>> Session session = ((org.eclipse.persistence.jpa.JpaEntityManager)
>>> em.getDelegate()).getActiveSession();
>>> Vector reportRows = (Vector) session.executeQuery(reportQuery);
>>>
>>> java.lang.Long vLong count = null;
>>> if ( reportRows != null ) {
>>>   count = (java.lang.Long)reportRows.get(0);
>>> }
>>>
>>> The reportQuery.addCount(), reportQuery.setSelectionCriteria(exp)  and
>>> reportQuery.setShouldReturnWithoutReportQueryResult(true)
>>>
>>> results in a single result row that is of type java.lang.Long.  That
>>> value
>>> contains the count value I am looking for.
>>>
>>> That is really nice.  As you said I can copy my query method that returns
>>> the result data and change from the ReadAllQuery to this ReportQuery in
>>> order to create a duplicate query method that returns the record count
>>> for
>>> my pagination scheme. Plus, this query is fast and doesn't take up the
>>> memory that getting the size from the ReadAllQuery does (as you mentioned
>>> in
>>> your previous reply).
>>>
>>> In the most simple form the SQL that the ReportQuery produces is this -
>>>
>>> SELECT COUNT(MEDIA_ID) FROM MEDIA_LISTING
>>>
>>> That is when no search criteria has been selected.
>>>
>>> When choosing some search criteria that involve foreign keys to
>>> relationship
>>> tables the ReportQuery produces SQL like this -
>>>
>>> SELECT COUNT(t0.MEDIA_ID) FROM KWMAP t4, KWMAP t3, KWLIST t2, KWLIST t1,
>>> MEDIA_LISTING t0 WHERE (((((t0.MEDIA_TYPE_ID = ?) AND (t0.MEDIA_FORMAT_ID
>>> =
>>> ?)) AND (t1.KW_ID = ?)) AND (t2.KW_ID = ?)) AND (((t3.MEDIA_ID =
>>> t0.MEDIA_ID) AND (t1.KW_ID = t3.KW_ID)) AND ((t4.MEDIA_ID = t0.MEDIA_ID)
>>> AND
>>> (t2.KW_ID = t4.KW_ID))))
>>> bind => [140, 310, 351, 104]
>>>
>>> Which is correct and produces a successful result.
>>>
>>> Thanks for your help.
>>> sonavor
>>>
>>>
>>>
>>> christopher delahunt wrote:
>>>>
>>>> Hello Sonavor,
>>>>
>>>> If you use a readAllQuery and do not specify the firstResult/maxRows,
>>>> then all the results are returned.  This means that the query is
>>>> executed, all results are sent over the connection, and all the objects
>>>> are built and cached, so there is no reason to then use pagination on
>>>> multiple queries - you can just use the result list already returned and
>>>> iterate over it instead.
>>>>
>>>> I would recommend using a ReportQuery instead of ReadAllQuery to count
>>>> the number of rows.  This would allow you to reuse the expression, and
>>>> just get a different result instead;
>>>>
>>>> ReportQuery reportQuery = new ReportQuery(MediaListing.class);
>>>> reportQuery.setSelectionCriteria(exp);
>>>> reportQuery.count(reportQuery.getExpressionBuilder());
>>>> result = em.getDelegate().getActiveSession().executeQuery(reportQuery);
>>>>
>>>> This is essentially the same as executing a JPQL count query.  I haven't
>>>> looked into executing it through the JPA interface, but you may need to
>>>> use setShouldReturnWithoutReportQueryResult(true) to get the correct
>>>> type.
>>>>
>>>> Best Regards,
>>>> Chris
>>>>
>>>>
>>>>
>>>> sonavor wrote:
>>>>> 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
>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>> _______________________________________________
>>>> 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-tp24820917p24836177.html
>>> Sent from the EclipseLink - Users mailing list archive at Nabble.com.
>>>
>>> _______________________________________________
>>> eclipselink-users mailing list
>>> eclipselink-users@xxxxxxxxxxx
>>> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
>>>
>> _______________________________________________
>> 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-tp24820917p24839418.html
> Sent from the EclipseLink - Users mailing list archive at Nabble.com.
>
> _______________________________________________
> eclipselink-users mailing list
> eclipselink-users@xxxxxxxxxxx
> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
>

Back to the top