Oracle和分页 [英] Oracle and Pagination

问题描述

在MySql中，分页的概念可以通过使用 LIMIT 子句的单个SQL语句轻松实现，如下所示。

  SELECT country_id，country_name 
 FROM country c 
 ORDER BY country_id DESC 
 LIMIT 4,5， 
  

它将检索从 5 到SQL查询检索结果集中的 10 。

---

在Oracle中，使用行号可以实现同样的功能，子查询使得任务如下所示变得繁琐。

  SELECT COUNTRY_ID，COUNTRY_NAME 
从
（SELECT ROWNUM如ROW_NUM，COUNTRY_ID，COUNTRY_NAME 
从
（SELECT COUNTRY_ID，COUNTRY_NAME 
从国家
 ORDER BY country_id desc）
 WHERE rownum< = 10 
）
 WHERE row_num> = 5; 
  

在Oracle 10g（或更高版本，虽然我不确定更高版本），但这可以比如，

  SELECT country_id，country_name 
 FROM（SELECT country_id，country_name，row_number（）over （order by country_id desc）等级
来自国家）
等级6和10之间的等级; 
  

---

关于像Web应用程序这样的应用程序，的分页需要几乎无处不在，每次执行（select）查询时编写这样的SQL语句有时是一项繁琐的工作。

假设我有一个使用Java的Web应用程序。如果我使用Hibernate框架，那么有一种直接的方法可以使用Hibernate支持的一些方法来完成这项工作，比如

  List< Country> ; countryList = session.createQuery（from country order by countryId desc）
 .setFirstResult（4）.setMaxResults（5）.list（）; 
  

但是当我简单地使用与Oracle的JDBC连接时，

  String connectionURL =jdbc：oracle：thin：@localhost：1521：xe; 
连接连接=空; 
 Statement statement = null; 
 ResultSet rs = null; 
 
 Class.forName（oracle.jdbc.OracleDriver）。newInstance（）; 
 connection = DriverManager.getConnection（connectionURL，root，root）; 
 statement = connection.createStatement（）; 
 rs = statement.executeQuery（SELECT * from country）; 
  

我的问题在这种情况下，是否有一种精确的方法来使用此代码检索指定范围的行？就像在前面的例子中使用类似于 setFirstResult（）和 setMaxResults（）的方法一样。或者唯一的方法是通过使用指定的那些子查询。

解决方案

因为'否'也是一个答案：

不幸的是，您将不得不使用子查询方法。我会亲自使用排名第二的人。

In MySql, the concept of pagination can easily be implemented with a single SQL statement using the LIMIT clause something like the following.

SELECT country_id, country_name 
FROM country c 
ORDER BY country_id DESC 
LIMIT 4, 5;

It would retrieve the rows starting from 5 to 10 in the result set which the SQL query retrieves.

---

In Oracle, the same thing can be achieved using row numbers with a subquery making the task somewhat tedious as follows.

SELECT country_id, country_name
FROM
  (SELECT rownum as row_num, country_id, country_name
   FROM
    (SELECT country_id, country_name
     FROM country
     ORDER BY country_id desc)
  WHERE rownum <= 10
  )
WHERE row_num >=5;

In Oracle 10g (or higher, I'm not sure about the higher versions though), this can be made somewhat easy such as,

SELECT country_id, country_name
  FROM (SELECT country_id, country_name, row_number() over (order by country_id desc) rank
      FROM country)
 WHERE rank BETWEEN 6 AND 10;

---

Regarding an application like a web application, the concept of pagination is required to implement almost everywhere and writing such SQL statements every time a (select) query is executed is sometimes a tedious job.

Suppose, I have a web application using Java. If I use the Hibernate framework then there is a direct way to do so using some methods supported by Hibernate like,

List<Country>countryList=session.createQuery("from Country order by countryId desc")
.setFirstResult(4).setMaxResults(5).list();

but when I simply use JDBC connectivity with Oracle like,

String connectionURL = "jdbc:oracle:thin:@localhost:1521:xe";
Connection connection = null;
Statement statement = null;
ResultSet rs = null;

Class.forName("oracle.jdbc.OracleDriver").newInstance();
connection = DriverManager.getConnection(connectionURL, "root", "root");
statement = connection.createStatement();
rs = statement.executeQuery("SELECT * from country");

My question in this case, is there a precise way to retrieve a specified range of rows using this code? Like in the preceding case using the methods something like setFirstResult() and setMaxResults()? or the only way to achieve this is by using those subqueries as specified.

解决方案

Because 'No' is an answer too:

Unfortunately, you will have to use the subquery approach. I would personally use the one with the rank (the second one).

这篇关于Oracle和分页的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！