Paging Statement-Take the 31st to 40th record in the SQL table (the auto-Grow ID is the primary key)

SQL Server Scenario 1:

Select top * from T where ID not in (select top with ID from T order by ID) Orde by ID

SQL Server Scenario 2:

Select top * from T where ID in (select top all IDs from T order by ID) Order BY id DESC

MySQL Scenario: SELECT * FROM T order by ID limit 30,10

Oracle Scenario: SELECT * FROM (select RowNum r,* from T where r<=40) where r>30

--------------------the content to be sorted-------------------------------------

pagesize=20;

PageNo = 5;

1. Paging Technology 1 (directly using SQL statements for paging, the most efficient and recommended)

Mysql:sql = "SELECT * from articles limit" + (pageNo-1) *pagesize + "," + pageSize;

Oracle:sql = "SELECT * from" +

"(Select RowNum r,* from" +

"(SELECT * from articles ORDER BY postime Desc)" +

"Where rownum<=" + pageno*pagesize + ") tmp" +

"Where r>" + (pageNo-1) *pagesize;

Note: Line 7th guarantees that the order of the rownum is determined because the index of Oracle causes RowNum to return different values

Jianyang Hint: When there is no order BY, rownum output sequentially, once the order By,rownum is not output sequentially, this indicates that rownum is the number before sorting. If the fields in the ORDER BY clause are indexed, the rownum is also output sequentially, because the original query result set is built by reference to the order of the index tables.

Sqlserver:sql = "SELECT top * from ID not ID (select top" + (pageNo-1) *pagesize + "ID from articles)"

DataSource ds = new InitialContext (). lookup (Jndiurl);

Connection cn = Ds.getconnection ();

"SELECT * from user where id=?" --->binary directive

PreparedStatement pstmt = cn.preparesatement (sql);

ResultSet rs = Pstmt.executequery ()

while (Rs.next ())

{

Out.println (rs.getstring (1));

}

2. Non-scrollable cursors

pagesize=20;

PageNo = 5;

CN = NULL

stmt = null;

rs = null;

Try

{

Sqlserver:sql = "SELECT * from articles";

DataSource ds = new InitialContext (). lookup (Jndiurl);

Connection cn = Ds.getconnection ();

"SELECT * from user where id=?" --->binary directive

PreparedStatement pstmt = cn.preparesatement (sql);

ResultSet rs = Pstmt.executequery ()

for (int j=0;j< (pageNo-1) *pagesize;j++)

{

Rs.next ();

}

int i=0;

while (Rs.next () && i<10)

{

i++;

Out.println (rs.getstring (1));

}

}

Cacth () {}

Finnaly

{

if (rs!=null) Try{rs.close ();} catch (Exception e) {}

if (STM ...).

If (CN ... ...).

}

3. Scrollable cursors

pagesize=20;

PageNo = 5;

CN = NULL

stmt = null;

rs = null;

Try

{

Sqlserver:sql = "SELECT * from articles";

DataSource ds = new InitialContext (). lookup (Jndiurl);

Connection cn = Ds.getconnection ();

"SELECT * from user where id=?" --->binary directive

PreparedStatement pstmt = cn.preparesatement (sql,resultset.type_scroll_insensitive,...);

Depending on the exception sqlfeaturenotsupportedexception of this line of code, it is possible to determine whether the driver supports scrollable cursors

ResultSet rs = Pstmt.executequery ()

Rs.absolute ((pageNo-1) *pagesize)

int i=0;

while (Rs.next () && i<10)

{

i++;

Out.println (rs.getstring (1));

}

}

Cacth () {}

Finnaly

{

if (rs!=null) Try{rs.close ();} catch (Exception e) {}

if (STM ...).

If (CN ... ...).

}

Paging Statement-Take the 31st to 40th record in the SQL table (the auto-Grow ID is the primary key)