Paging query and SQL paging Query

Paging query and SQL paging Query
In fact, there is no difference between paging query and other queries. The main difference is that the query statement is not available, and paging query may be a little more troublesome. Next we will explain how to implement our paging query function:

1. Code display 1. encapsulate the PageModel object to save the basic information of some paging queries.

/*** Encapsulate paging information */</span> public class PageModel {// result set </span> private List list; // query the number of records </span> private int totalRecords; // The number of data records per page </span> private int pageSize; // page number </span> private int pageNo; // total page number </span> public int getTotalPages () {return (totalRecords + pageSize-1)/pageSize ;} // get the homepage </span> public int getTopPageNo () {return 1 ;}// get the last page </span> public int getButtomPageNo () {return getTotalPages ();} // previous page </span> public int getPreviousPageNo () {if (pageNo <= 1) {return 1;} return pageNo-1 ;} // next page </span> public int getNextPageNo () {if (pageNo >=gettotalpages () {return getTotalPages ();} return pageNo + 1;} public List getList () {return list;} public void setList (List list) {this. list = list;} public int getTotalRecords () {return totalRecords;} public void setTotalRecords (int totalRecords) {this. totalRecords = totalRecords;} public int getPageSize () {return pageSize;} public void setPageSize (int pageSize) {this. pageSize = pageSize;} public int getPageNo () {return pageNo;} public void setPageNo (int pageNo) {this. pageNo = pageNo ;}}</span>

2. Compile the paging Query Method

/*** Query by page * @ param pageNo page number * @ param pageSize how many pieces of data per page * @ return pageMode */public PageModel findUserList (int pageNo, int pageSize) {// use StringBuffer to store the paging query statement StringBuffer sbSql = new StringBuffer (); sbSql. append ("select user_id, user_name, password, contract_tel, email, create_date "). append ("from "). append ("("). append ("select ROWNUM rn, user_id, user_name, password, contract_tel, email, create_date "). a Ppend ("from "). append ("("). append ("select * from T_USER where USER_ID <> 'root' order by USER_ID "). append (") where ROWNUM <=? "). Append (") where rn>? "); // Create the Connection object and PreparedStatement object Connection conn = null; PreparedStatement pstmt = null; // create a ResultSet to store the query result ResultSet rs = null; PageModel pageModel = null; try {// get connection conn = DbUtil. getConnection (); pstmt = conn. prepareStatement (sbSql. toString (); // you can specify the pstmt parameter. setInt (1, pageNo * pageSize); pstmt. setInt (2, (pageNo-1) * pageSize); rs1_pstmt.exe cuteQuery (); List userList = new ArrayList (); // process the query result while (rs. next () {User user = new User (); user. setUserId (rs. getString ("USER_ID"); user. setUserName (rs. getString ("USER_NAME"); user. setPassword (rs. getString ("PASSWORD"); user. setContactTel (rs. getString ("CONTRACT_TEL"); user. setEmail (rs. getString ("EMAIL"); user. setCreateDate (rs. getTimestamp ("CREATE_DATE"); userList. add (user) ;}pagemodel = new pageModel (); PageModel. setList (userList); pageModel. setTotalRecords (getTotalRecords (conn); pageModel. setPageSize (pageSize); pageModel. setPageNo (pageNo);} catch (SQLException e) {e. printStackTrace ();} finally {DbUtil. close (rs); DbUtil. close (pstmt); DbUtil. close (conn) ;}// return pageModel;} public int getTotalRecords (Connection conn) throws SQLException {String SQL = "select count (*) from t_user where user_id <> 'root' "; PreparedStatement pstmt = null; ResultSet rs = null; int count = 0; try {pstmt = conn. prepareStatement (SQL); rs1_pstmt.exe cuteQuery (); rs. next (); count = rs. getInt (1);} finally {DbUtil. close (rs); DbUtil. close (pstmt);} return count ;}

2. Functional Analysis: in fact, querying by page is essentially a query statement:
Table 3 in part 3 -- start select user_id, user_name, password, contract_tel, email, create_date from

(Part 2 Table 2 -- start select ROWNUM rn, user_id, user_name, password, contract_tel, email, create_date from (part 1 Table 1 --- start)

Select * from T_USER where USER_ID <> 'root' order by USER_ID "first part table 1 --- end") where ROWNUM <= (PageNo * pageSize)Table 2 in the second part -- Conclusion) where rn> (PageNo-1) * pageSizeTable 3 in Part 3-conclusion

Iii. Conclusion: As shown above, querying by page is actually a nested query of tables. Step 1: first check the content to be queried, such as the first part. Step 2: remove the part of records greater than the maximum record, such as the second part. Step 3: remove the part smaller than the minimum record, such as the third part. The rest is the paging query data we need.