[
Date Prev][
Date Next][
Thread Prev][
Thread Next][
Date Index][
Thread Index]
[
List Home]
|
Re: [eclipselink-users] MySql problem with ReadAllQuery and pagination
|
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.
