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 
I attached the source, and stepped through what it takes to run a
query (props to the EL team for writing that, jeesh) and the issue is
just on the JDBC level, it doesn't hang till it executes the prepared
statement, so it must be an oracle driver issue. I'll see if I can
find out why now.

./tch



On Tue, Aug 11, 2009 at 2:17 PM, sonavor<jw@xxxxxxxxxxxxxx> wrote:
>
> 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.
>
> _______________________________________________
> eclipselink-users mailing list
> eclipselink-users@xxxxxxxxxxx
> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
>

Back to the top