[
Date Prev][
Date Next][
Thread Prev][
Thread Next][
Date Index][
Thread Index]
[
List Home]
|
Re: [eclipselink-users] MySql problem with ReadAllQuery and pagination
|
Just to fix a typo...
In my previous reply the line:
java.lang.Long vLong count = null;
should have read:
java.lang.Long count = null;
sonavor 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-tp24820917p24836221.html
Sent from the EclipseLink - Users mailing list archive at Nabble.com.
