In the common database, the query statement of paging function is realized;

1.Mysql Database

SELECT * FROM table name limit (pagenum-1) *pagesize, pagesize;

Pagenum is the current page, pagesize is the number of bars per page.

Then (pagenum-1) *pagesize will be said to turn over the number of bars, followed by the PageSize bar.

Examples are as follows:

 Packagecom.inba.maya.DBConnection;ImportJava.util.*;ImportJava.sql.*;ImportCom.inba.maya.page.*;
 Public classFruitdao {PrivateConnection Conn; PrivatePreparedStatement PS; PrivateResultSet rs;  PublicFruitdao ()throwsexception{Conn=dbconnection.getconnection (); }        //This method is used to get the total number of pages, and is not associated with the following fetch per page content.      Public intGetPage (intPageSizethrowsexception{//gets the number of bars in the current table with Coun (*), noting that only one row of data is returned, and that it is an integer;String sql= "SELECT COUNT (*) from Fruit"; PS=conn.preparestatement (SQL); RS=Ps.executequery (); //then Rs.next () will inevitably point to the nextRs.next (); //since integers are necessarily integers except integers, multiply by 1.0 to decimal, and then use Math.ceil to get the smallest integer value greater than or equal to the current number;        intPagecount= (int) Math.ceil (1.0*rs.getint (1)/pagesize); returnPageCount; }        //enter two parameters, Pagenum is the first page; pagesize is the number of rows per page     PublicArraylist<fruit> Select (intPagenum,intPageSizethrowsexception{//An array that defines the type of the class currently being fetched;Arraylist<fruit> list=NewArraylist<fruit>(); //MySQL's paging statement;String sql= "SELECT * from Fruit limit?,?"; PS=conn.preparestatement (SQL); Ps.setint (1, (pagenum-1) *pagesize); Ps.setint (2, pagesize); RS=Ps.executequery (); //If Rs.next is true, the class to be assigned is instantiated first, then each field element is assigned to a variable of the corresponding class through a while loop.         while(Rs.next ()) {Fruit F=NewFruit (); F.setids (Rs.getstring (1)); F.setname (Rs.getstring (2)); F.setprice (Rs.getdouble (3)); F.setnumbers (Rs.getint (5)); F.setsource (Rs.getstring (4)); F.setimages (Rs.getstring (6)); //The value of each loop f is stored in the array list;List.add (f); }        //Finally, back out .        returnlist; }}

2.Orcale Database

First of all: The SQL statement used in Oracle for pagination display keywords: rownum.

It may be known that rownum only applies to less than or less than equals, and if it is equal to judgment, then it can only be equal to 1 and cannot be greater than the comparison.

RowNum is the number of rows that the Oracle system order is assigned to return from the query, the first row returned is assigned 1, the second row is 2, and so on.

rownum always starts at 1, regardless of whether the current record satisfies the query result, rownum returns a value of 1, if the value of this record finally satisfies all conditions, then rownum sliding Scale, the next record rownum Will return 2, otherwise the next record's rownum still returns 1.

< Span lang= "en-US" > understand this, It is clear why the general Rownum1 is unable to return the result, so the rownum for each record are 1, and Rownum1 does not satisfy the results of the query, so the next the rownum of a record is not incremented, still 1, so all records do not meet the criteria.

Simple example: SELECT * FROM Student rownum<=4

This indicates that a line with a return number less than or equal to 4 is displayed in student;

Simple SQL statement:

Pagenum: Is the current number of pages; PageSize: is the row count per page;

SELECT * FROM (select A.*, rownum ro from (SELECT * from Couser) a) where Ro between (pagenum-1) *pagesize+1 and (pagenum-1 ) *pagesize+pagesize

Instance:

 PackageCom.inba.maya.Dao;ImportJava.util.*;ImportJava.sql.*; Public classDBConnection {//Oracle Load Driver     Public StaticString qd= "Oracle.jdbc.driver.OracleDriver"; //Oracle Link Database     Public StaticString url= "Jdbc:oracle:thin: @localhost: 1521:orcl";  Public StaticString user= "GMH";  Public StaticString password= "602477126";  Public StaticConnection getconnection ()throwsexception{class.forname (QD); Connection Conn=drivermanager.getconnection (URL, user, password); returnConn; }}

 PackageCom.inba.maya.Dao;ImportJava.util.*;ImportJava.sql.*;ImportCom.inba.maya.dao.*;ImportCom.inba.maya.page.*; Public classCouserdao {PrivateConnection Conn; PrivatePreparedStatement PS; PrivateResultSet rs;  PublicCouserdao ()throwsexception{Conn=dbconnection.getconnection (); }         Public intGetPage (intPageSizethrowsexception{String SQL= "SELECT count (*) from Couser"; PS=conn.preparestatement (SQL); RS=Ps.executequery ();        Rs.next (); intPage= (int) Math.ceil (1.0*rs.getint (1)/pagesize); returnpage; }     PublicArraylist<couser> Select (intPageSizeintPagenum)throwssqlexception{ArrayList<Couser> list=NewArraylist<couser>(); //Paging statements for OracleString sql= "SELECT * FROM (select A.*, rownum ro from (SELECT * from Couser) a) where ro between? and? "; PS=conn.preparestatement (SQL); Ps.setint (1, (pagenum-1) *pagesize+1); Ps.setint (2, (pagenum-1) *pagesize+pagesize); RS=Ps.executequery ();  while(Rs.next ()) {Couser F=NewCouser (); F.setcon (Rs.getstring (1)); F.setcname (Rs.getstring (2)); F.setton (Rs.getstring (3));        List.add (f); }        returnlist; }    }

In the link of various kinds of database, not the same nothing, load driver, connect database, SQL statement is different.

3.sqlserver Statement Implementation paging:

Select top * from table name where not in (select top list * FROM table name )

Because SQL Server is not loaded on the computer, all is not demonstrated.

In fact, the operation of the database, most of the same, nothing more than to load the driver package, connect the database, SQL statements are different

In the common database, the query statement of paging function is realized;