Re: [eclipselink-users] A question of native query for pagination.

[Tom Ware <tom.ware@xxxxxxxxxx>]

Hi Shashi,

  When you write a native query, we cannot change the SQL on you.  That means 
that any hints that would force a change to the actual SQL string have to be 
either ignored, or dealt with in a different way.

  For firstrow and max results, we use JDBC API.  As Chris mentions, we use 
statement.setMaxRows() to set the max rows, and we adjust the pointer into the 
result set to achieve the firstResult functionality.  The behavior or these two 
operations will depend on the JDBC driver's functionality.

  Is it possible to run a JPQL or Criteria query instead.  That will give us 
the ability to construct the SQL ourselves and, for many databases allow us to 
include pagination information in the actual SQL.

  Otherwise, you will likely have to include your pagination information in the 
Native SQL String you provide us with.

-Tom

Shashikant Kale wrote:
> Hello,
>
>  
>
> I am using a native query to get a paginated data from the DB(Oracle). 
> However I don’t see the ROWNUM and FIRST_ROWS hint being added to the 
> query. I referred to the discussion at 
> http://dev.eclipse.org/mhonarc/lists/eclipselink-users/msg03084.html, 
> however I am not clear about the below response from Chris
>
>  
>
> “That is not entirely true. For maxRows, a native query or non-paginated 
> query will use statement.setMaxRows(). So depending on how the driver 
> implements this, it should not be sending the entire result set. 
> FirstResult is used to jump to the first row on the returned resultset 
> though.”
>
> Would database still return the full results set to the driver on the 
> application server and driver would then hand over only a page of data 
> to the application code or the DB itself would provide only the page of 
> data to the application?
>
>  
>
> We have an application where database CPU is hitting 100% quickly and 
> remains high for the user load. I am also working on tuning the queries, 
> however want to even know if there is anything else I am missing here.
>
>  
>
> Would appreciate any help.
>
>  
>
> Thanks,
> Shashi
>
>  
>
>
> ------------------------------------------------------------------------
>
> _______________________________________________
> eclipselink-users mailing list
> eclipselink-users@xxxxxxxxxxx
> https://dev.eclipse.org/mailman/listinfo/eclipselink-users