[
Date Prev][
Date Next][
Thread Prev][
Thread Next][
Date Index][
Thread Index]
[
List Home]
|
Re: [eclipselink-users] MySql problem with ReadAllQuery and pagination
|
As another option to run the ReportQuery. Chris mentioned that it could
probably also run as a JPA Query in the way that the ReadAllQuery was
executed.
Instead of doing -
Session session = ((org.eclipse.persistence.jpa.JpaEntityManager)
em.getDelegate()).getActiveSession();
Vector reportRows = (Vector) session.executeQuery(reportQuery);
I am also successful with the execution doing -
javax.persistence.Query jpaquery =
((JpaEntityManager)em.getDelegate()).createQuery(reportQuery);
Object rObj = jpaquery.getSingleResult();
The resulting Object from the single result call is a java.lang.Long type
and is the requested count.
sonavor wrote:
>
> 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-tp24820917p24836827.html
Sent from the EclipseLink - Users mailing list archive at Nabble.com.
