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 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
>

Back to the top