Oracle/MySql/SQL Sqlserver paging query, oraclesqlserver

Oracle/MySql/SQL Sqlserver paging query, oraclesqlserver
Brief Introduction

A brief summary of the paging query statements for Oracle, MySql, and SQL Server databases.

 

Oracle paging Query

For example, two pieces of data are displayed on each page. Now you need to query the second page, that is, 3-4 pieces of data.

Query statement:

1 select * from (2        select dept.*,rownum num from dept where rownum <= 43 ) d where d.num >= 3

 

Explanation: The rownum column is used for paging. The end row of the query is set in the subquery, And the start row of the query is set in the parent query.

Note: The rownum column in The subquery must be an alias.

MySQL paging Query

For example, 10 data entries are displayed on each page. You need to query the third page, that is,-30 data entries.

Query statement:

1 SELECT * FROM `tab_sys_menu` LIMIT 20, 10

Explanation: Use limit for paging query. The first parameter after limit is to set the start row of the query, and the second parameter is to set the number of rows to be queried (that is, the number displayed on each page ).

Note: The start row starts from 0.

SQL Server paging Query

For example, 10 data entries are displayed on each page. You need to query the third page, that is,-30 data entries.

Query statement:

1 select top 10 * from Room where RoomId not in (2     select top 20 RoomId from Room3 )

Explanation: The subquery is used for paging. The top value in the subquery is set to the row to be excluded. For example, if you want to query from 21st data entries, the first 20 data entries are definitely not required, for example, if you want to query 41st data records, the first 40 data records are definitely not required.

The top value of the parent query is set to the number of queried rows (that is, the number displayed on each page ).

 

Extension

In fact, paging query statements do not have to be written by yourself. If the dao layer of the project is implemented using hibernate, there are two ways to conveniently implement paging query.

They are Criteria queries and HQL queries.

Criteria query: After the Criteria object is created, there are two methods: setMaxResults (set the number of records displayed on each page) and setFirstResult (set the number of rows to start the query ).

HQL Query: As shown in the preceding figure, after a query is created based on the Session, the Query objects include setMaxResults (set the number of records displayed on each page) and setFirstResult (set the number of rows to query) method.

Example: session. createQuery (hql). setFirstResult (result. getPageNo ()-1) * result. getPageSize (). setMaxResults (size). list ();