Re: [eclipselink-users] MySql problem with ReadAllQuery and pagination

[Tim Hollosy <hollosyt@xxxxxxxxx>]

Figured out it was just oracle being goofy after I ran the query the
next day in my query tool.

Had to use a nested loop query hint in the oracle query -- first time
I've had to do something like that :/

./tch



On Tue, Aug 11, 2009 at 4:13 PM, Tim Hollosy<hollosyt@xxxxxxxxx> wrote:
> 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
>>
>