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 
How many records are you dealing with in the count query?

I am not seeing any performance issues with the ReportQuery count but I am
only testing with a small set of data ( less than 200 records ).

-sonavor


tch wrote:
> 
> I have no issues with any other queries, even a select * from the same
> table returns more quickly. I'll probably have to attach the source
> and step through to see where we're having issues.
> 
> ./tch
> 
> 
> 
> On Tue, Aug 11, 2009 at 2:01 PM, sonavor<jw@xxxxxxxxxxxxxx> wrote:
>>
>> My performance seems fine in places where I am using it.
>>
>> A couple of things to try -
>> Turn off the JPA EclipseLink logging ( eclipselink.logging.level to OFF )
>> and see if the query is still slow.
>> The logs are nice for debugging but they really slow down the
>> performance.
>>
>> If that doesn't help then it could be the JDBC driver.  I haven't run
>> into
>> slow queries with the Oracle JDBC drivers in cases where I have used
>> Oracle
>> but I have seen similar performance problems when using MS SqlServer JDBC
>> drivers with SqlServer databases.  The fact that you can run the query
>> directly in sqlplus without any speed issues makes me suspect the JDBC
>> driver.  Are your other JPA queries performing okay?
>> Are you using a connection pool or are you creating a new connection each
>> time you run your query?
>> Can you create a similar query by just using EntityManager
>> em.createQuery("your select count query") and seeing if it is also slow?
>>
>> In your report query are you requesting just the single result -
>> something
>> like -
>> javax.persistence.Query jpaquery = ((JpaEntityManager)
>> em.getDelegate()).createQuery(reportQuery);
>> Object rObj = jpaquery.getSingleResult();
>>
>>
>> -sonavor
>>
>>
>>
>> tch wrote:
>>>
>>> ReportQuery with the count seems super slow. I have
>>> setShouldReturnWithoutReportQueryResult(true) called on the
>>> ReportQuery, but it still seems to take a long time -- especially the
>>> first time. It's instant from sqlplus, but takes about 20 seconds this
>>> way, so I don't think it's normal database query caching that i'm
>>> noticing. Anyone else experiencing this?
>>>
>>> I don't see in the logs it producing a query that would return all the
>>> objects, just the count(*) query. But it sure seems like it is pulling
>>> all the objects back -- why else would it take so long?
>>>
>>> ./tch
>>>
>>>
>>>
>>> On Thu, Aug 6, 2009 at 12:40 AM, sonavor<jw@xxxxxxxxxxxxxx> wrote:
>>>>
>>>> tch,
>>>>
>>>> Well, the ReportQuery extends the ReadAllQuery -
>>>> http://www.eclipse.org/eclipselink/api/1.0/org/eclipse/persistence/queries/ReportQuery.html
>>>> http://www.eclipse.org/eclipselink/api/1.0/org/eclipse/persistence/queries/ReportQuery.html
>>>>
>>>> The way to share the defined query that a ReadAllQuery is configured
>>>> for
>>>> is
>>>> to construct a ReportQuery using the ExpressionBuilder instance that
>>>> the
>>>> ReadAllQuery is using plus, optionally, the JPA entity class.  Or the
>>>> ReportQuery can be constructed from the JPA entity class and the
>>>> Expression
>>>> that the ReadAllQuery is using.
>>>>
>>>> In my case, I used the same build up of an ExpressionBuilder,
>>>> Expression
>>>> and
>>>> the JPA entity class to create the ReportQuery I am using for the count
>>>> -
>>>> ReportQuery reportQuery = new ReportQuery(MediaListing.class, eb);
>>>> ....note that I had previously in my code created the "eb"
>>>> EntityBuilder
>>>> variable.
>>>>
>>>> The Expression instance - "exp" that I defined for the ReadAllQuery is
>>>> the
>>>> selection criteria in my ReportQuery -
>>>> reportQuery.setSelectionCriteria(exp);
>>>>
>>>> As I mentioned before, I am using two separate methods.  The method for
>>>> count returns an int value (for the number of records).  The method for
>>>> the
>>>> paged search results returns a Collection<MediaListing> (a typed
>>>> Collection)
>>>> of the JPA entities being returned by the query.
>>>>
>>>> I am not familiar with using @NamedNativeQuery or @NamedQuery or
>>>> dynamic
>>>> JPQL queries in conjunction with the
>>>> org.eclipse.persistence.queries.ReadAllQuery class.  I always use
>>>> ExpressionBuilder and Expression classes to create the ReadAllQuery
>>>> (and
>>>> now
>>>> the ReportQuery since I have learned to use it).
>>>> When I use the NamedQuery or dynamic JPQL queries and want a count
>>>> value
>>>> I
>>>> just create a dynamic JPQL query for the count.  In both scenarios
>>>> (ReadAllQuery/ReportQuery or JPQL Query/JPQL Count Query) I want to
>>>> keep
>>>> the
>>>> same query types paired together just to make sure the generated SQL is
>>>> the
>>>> same for the body of the query (the joins and where clauses).
>>>>
>>>> Sonavor
>>>>
>>>>
>>>>
>>>> tch wrote:
>>>>>
>>>>> 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
>>>>>>
>>>>> _______________________________________________
>>>>> 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-tp24820917p24840239.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-tp24820917p24922691.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-tp24820917p24923004.html
Sent from the EclipseLink - Users mailing list archive at Nabble.com.

Back to the top