MySql problem with ReadAllQuery and pagination
|
View:
New views
18 Messages
—
Rating Filter:
Alert me
|
 |
MySql problem with ReadAllQuery and pagination
by sonavor
::
Rate this Message:
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. |
|
|
Re: MySql problem with ReadAllQuery and pagination
by sonavor
::
Rate this Message:
I did some addition experimenting with the ReadAllQuery setFirstResult and setMaxRows and believe that I have found a solution to my problem.
In looking at the second page request, where I set the starting row (setFirstResult) to row 5 and set the max rows to return to 4, the -1 looked odd. So I tried just hard-coding the starting row in the beginning to 5 and got the same error on the initial execution of the query. Next I tried hard-coding the starting row to 1. That worked and the bind value for the starting row and max rows was: [1, 3]. That's when it hit me that the ReadAllQuery pagination was subtracts the setFirstResult value from the setMaxRows value. Note that the normal JPA Query does not do it this way!!!! So I changed my code to this - ReadAllQuery readAllQuery = new ReadAllQuery(MediaListing.class); readAllQuery.setSelectionCriteria(exp); readAllQuery.setFirstResult(startRow); readAllQuery.setMaxRows(rowCount + startRow); readAllQuery.prepareForExecution(); Session session = ((org.eclipse.persistence.jpa.JpaEntityManager) em.getDelegate()).getActiveSession(); List<MediaListing> results = (List) session.executeQuery(readAllQuery); ...and like that, no more error. The standard JPA Query class does have a different name for the maximum row setting: setMaxResults while the ReadAllQuery provides: setMaxRows However, in the documentation (TopLink), I only found where setMaxRows was defined as the maximum number of rows that the result set will return from the database. I didn't find any mention of how the setMaxRows attribute was tied to the setFirstResult attribute.
|
|
|
Re: MySql problem with ReadAllQuery and pagination
by christopher delahunt
::
Rate this Message:
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@... https://dev.eclipse.org/mailman/listinfo/eclipselink-users |
|
|
Re: MySql problem with ReadAllQuery and pagination
by sonavor
::
Rate this Message:
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
|
|
|
Re: MySql problem with ReadAllQuery and pagination
by christopher delahunt
::
Rate this Message:
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@... >> https://dev.eclipse.org/mailman/listinfo/eclipselink-users >> >> >> > > eclipselink-users mailing list eclipselink-users@... https://dev.eclipse.org/mailman/listinfo/eclipselink-users |
|
|
Re: MySql problem with ReadAllQuery and pagination
by sonavor
::
Rate this Message:
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
|
|
|
Re: MySql problem with ReadAllQuery and pagination
by sonavor
::
Rate this Message:
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;
|
|
|
Re: MySql problem with ReadAllQuery and pagination
by sonavor
::
Rate this Message:
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.
|
|
|
Re: MySql problem with ReadAllQuery and pagination
by tch
::
Rate this Message:
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@...> 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@... >>>> https://dev.eclipse.org/mailman/listinfo/eclipselink-users >>>> >>>> >>>> >>> >>> >> _______________________________________________ >> eclipselink-users mailing list >> eclipselink-users@... >> 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@... > https://dev.eclipse.org/mailman/listinfo/eclipselink-users > eclipselink-users mailing list eclipselink-users@... https://dev.eclipse.org/mailman/listinfo/eclipselink-users |
|
|
Re: MySql problem with ReadAllQuery and pagination
by sonavor
::
Rate this Message:
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
|
|
|
Re: MySql problem with ReadAllQuery and pagination
by tch
::
Rate this Message:
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@...> 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@...> 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@... >>>>>> https://dev.eclipse.org/mailman/listinfo/eclipselink-users >>>>>> >>>>>> >>>>>> >>>>> >>>>> >>>> _______________________________________________ >>>> eclipselink-users mailing list >>>> eclipselink-users@... >>>> 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@... >>> https://dev.eclipse.org/mailman/listinfo/eclipselink-users >>> >> _______________________________________________ >> eclipselink-users mailing list >> eclipselink-users@... >> 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@... > https://dev.eclipse.org/mailman/listinfo/eclipselink-users > eclipselink-users mailing list eclipselink-users@... https://dev.eclipse.org/mailman/listinfo/eclipselink-users |
|
|
Re: MySql problem with ReadAllQuery and pagination
by sonavor
::
Rate this Message:
tch,
Well, the ReportQuery extends the ReadAllQuery - 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
|
|
|
Re: MySql problem with ReadAllQuery and pagination
by tch
::
Rate this Message:
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@...> 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@...> 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@...> 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@... >>>>>>>> https://dev.eclipse.org/mailman/listinfo/eclipselink-users >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>> >>>>>>> >>>>>> _______________________________________________ >>>>>> eclipselink-users mailing list >>>>>> eclipselink-users@... >>>>>> 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@... >>>>> https://dev.eclipse.org/mailman/listinfo/eclipselink-users >>>>> >>>> _______________________________________________ >>>> eclipselink-users mailing list >>>> eclipselink-users@... >>>> 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@... >>> https://dev.eclipse.org/mailman/listinfo/eclipselink-users >>> >> _______________________________________________ >> eclipselink-users mailing list >> eclipselink-users@... >> 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@... > https://dev.eclipse.org/mailman/listinfo/eclipselink-users > eclipselink-users mailing list eclipselink-users@... https://dev.eclipse.org/mailman/listinfo/eclipselink-users |
|
|
Re: MySql problem with ReadAllQuery and pagination
by sonavor
::
Rate this Message:
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
|
|
|
Re: MySql problem with ReadAllQuery and pagination
by tch
::
Rate this Message:
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@...> 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@...> 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@...> 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@...> 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@... >>>>>>>>>> https://dev.eclipse.org/mailman/listinfo/eclipselink-users >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>> _______________________________________________ >>>>>>>> eclipselink-users mailing list >>>>>>>> eclipselink-users@... >>>>>>>> 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@... >>>>>>> https://dev.eclipse.org/mailman/listinfo/eclipselink-users >>>>>>> >>>>>> _______________________________________________ >>>>>> eclipselink-users mailing list >>>>>> eclipselink-users@... >>>>>> 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@... >>>>> https://dev.eclipse.org/mailman/listinfo/eclipselink-users >>>>> >>>> _______________________________________________ >>>> eclipselink-users mailing list >>>> eclipselink-users@... >>>> 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@... >>> https://dev.eclipse.org/mailman/listinfo/eclipselink-users >>> >> _______________________________________________ >> eclipselink-users mailing list >> eclipselink-users@... >> 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@... > https://dev.eclipse.org/mailman/listinfo/eclipselink-users > eclipselink-users mailing list eclipselink-users@... https://dev.eclipse.org/mailman/listinfo/eclipselink-users |
|
|
Re: MySql problem with ReadAllQuery and pagination
by sonavor
::
Rate this Message:
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
|
|
|
Re: MySql problem with ReadAllQuery and pagination
by tch
::
Rate this Message:
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@...> 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@...> 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@...> 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@...> 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@...> 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@... >>>>>>>>>>>> https://dev.eclipse.org/mailman/listinfo/eclipselink-users >>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>> _______________________________________________ >>>>>>>>>> eclipselink-users mailing list >>>>>>>>>> eclipselink-users@... >>>>>>>>>> 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@... >>>>>>>>> https://dev.eclipse.org/mailman/listinfo/eclipselink-users >>>>>>>>> >>>>>>>> _______________________________________________ >>>>>>>> eclipselink-users mailing list >>>>>>>> eclipselink-users@... >>>>>>>> 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@... >>>>>>> https://dev.eclipse.org/mailman/listinfo/eclipselink-users >>>>>>> >>>>>> _______________________________________________ >>>>>> eclipselink-users mailing list >>>>>> eclipselink-users@... >>>>>> 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@... >>>>> https://dev.eclipse.org/mailman/listinfo/eclipselink-users >>>>> >>>> _______________________________________________ >>>> eclipselink-users mailing list >>>> eclipselink-users@... >>>> 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@... >>> https://dev.eclipse.org/mailman/listinfo/eclipselink-users >>> >> _______________________________________________ >> eclipselink-users mailing list >> eclipselink-users@... >> 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@... > https://dev.eclipse.org/mailman/listinfo/eclipselink-users > eclipselink-users mailing list eclipselink-users@... https://dev.eclipse.org/mailman/listinfo/eclipselink-users |
|
|
Re: MySql problem with ReadAllQuery and pagination
by tch
::
Rate this Message:
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@...> 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@...> 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@...> 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@...> 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@...> 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@...> 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@... >>>>>>>>>>>>> https://dev.eclipse.org/mailman/listinfo/eclipselink-users >>>>>>>>>>>>> >>>>>>>>>>>>> >>>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>> _______________________________________________ >>>>>>>>>>> eclipselink-users mailing list >>>>>>>>>>> eclipselink-users@... >>>>>>>>>>> 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@... >>>>>>>>>> https://dev.eclipse.org/mailman/listinfo/eclipselink-users >>>>>>>>>> >>>>>>>>> _______________________________________________ >>>>>>>>> eclipselink-users mailing list >>>>>>>>> eclipselink-users@... >>>>>>>>> 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@... >>>>>>>> https://dev.eclipse.org/mailman/listinfo/eclipselink-users >>>>>>>> >>>>>>> _______________________________________________ >>>>>>> eclipselink-users mailing list >>>>>>> eclipselink-users@... >>>>>>> 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@... >>>>>> https://dev.eclipse.org/mailman/listinfo/eclipselink-users >>>>>> >>>>> _______________________________________________ >>>>> eclipselink-users mailing list >>>>> eclipselink-users@... >>>>> 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@... >>>> https://dev.eclipse.org/mailman/listinfo/eclipselink-users >>>> >>> _______________________________________________ >>> eclipselink-users mailing list >>> eclipselink-users@... >>> 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@... >> https://dev.eclipse.org/mailman/listinfo/eclipselink-users >> > eclipselink-users mailing list eclipselink-users@... https://dev.eclipse.org/mailman/listinfo/eclipselink-users |
| Free embeddable forum powered by Nabble | Forum Help |
