[
Date Prev][
Date Next][
Thread Prev][
Thread Next][
Date Index][
Thread Index]
[
List Home]
Re: [eclipselink-users] A question of native query for pagination.
|
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