Java technology accumulation-use the original code to witness the paging query implementation principle

Java technology accumulation-use the original code to witness the paging query implementation principle

You are familiar with paging query. The datagrid encapsulated in easyui fully applies a series of table operations. Paging query is one of them. However, we are not familiar with the basic implementation principle of paging query. How is it actually implemented? Today, let's take a look at it!

Let's take a look at the implementation of the interface:

There are two core things that seem complicated:

I. SQL statement for paging Query

 

select user_id,user_name,password,contact_Tel,email,create_datefrom(select rownum rn,user_id,user_name,password,contact_tel,email,create_datefrom (select user_id,user_name,password,contact_tel,email,create_date from t_user where user_id <> 'root' order by user_id) where rownum <= ? ) where rn> ? 

No matter what we do, the underlying things are still inseparable, so we should understand the most primitive SQL statements.

 

Ii. entity for paging Query

 

Package com. bjpowernode. drp. util; import java. util. List; public class PageModel
 
  
{Private List
  
   
List; // The type of the table displayed by page. The private int totalRecords is implemented using the generic type. // The total number of records is private int pageSize; // The number of entries displayed on each page. private int pageNo; // the public List page
   
    
GetList () {return list;} public void setList (List
    
     
List) {this. list = list;}/*** total page number **/public int getTotalPages () {return (totalRecords + pageSize-1)/pageSize ;} /*** get the homepage */public int getTopPageNo () {return 1;}/*** get the last page * @ return */public int getBottomPageNo () {return getTotalPages ();}/*** previous page * @ return */public int getPreviousPageNo () {if (pageNo <= 1) {return 1 ;} return pageNo-1 ;} public int getNextPageNo () {if (pageNo> = getBottomPageNo () {return getBottomPageNo ();} return pageNo + 1;} public void setTotalRecords (int totalRecords) {this. totalRecords = totalRecords;} public int getTotalPages (int totalRecords) {return 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 ;}}
    
   
  
 

 

The above section describes the core of paging query. Next we only need to combine our business logic with the two cores above into a line, the paging query function can be implemented!

3. Integration with business logic:

 

/*** Query by page * @ param pageNo page number * @ param pageSize the number of data entries per page * @ return pageModel */public PageModel
 
  
FindUserList (int pageNo, int pageSize) {StringBuffer sbSql = new StringBuffer (); sbSql. append (select user_id, user_name, password, contact_Tel, email, create_date ). append (from ). append ((). append (select rownum rn, user_id, user_name, password, contact_tel, email, create_date ). append (from ). append ((). append (select user_id, user_name, password, contact_tel, email, create_date from t_user where user_id <> 'root' order User_id). append () where rownum <=? ). Append () where rn>? ); PageModel
  
   
PageModel = null; Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; User user = null; try {conn = DbUtil. getConnection (); pstmt = conn. prepareStatement (sbSql. toString (); pstmt. setInt (1, pageNo * pageSize); pstmt. setInt (2, (pageNo-1) * pageSize); // pstmt.setstring(1,userid=*rs=pstmt.exe cuteQuery (); List
   
    
UserList = new ArrayList
    
     
(); While (rs. next () {user = new User (); user. setUserId (rs. getString (user_Id); user. setUserName (rs. getString (user_name); user. setPassword (rs. getString (password); user. setContactTel (rs. getString (contact_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 {// disable DbUtil in sequence. close (rs); DbUtil. close (pstmt); DbUtil. close (conn);} return pageModel;} private int getTotalRecords (Connection conn) throws SQLException {String SQL = select count (*) from t_user where user_Id <> 'root '; preparedStatement pstmt = null; ResultSet rs = null; int count1_01_try1_pstmt1_conn.preparestatement(sql1_1_rs1_pstmt.exe cuteQuery (); rs. next (); count = rs. getInt (1);} finally {DbUtil. close (rs); DbUtil. close (pstmt);} return count ;}
    
   
  
 

 

Frontend call:

 

<%int pageNo=1;int pageSize=3;String pageNoString=request.getParameter(pageNo);if(pageNoString !=null){pageNo=Integer.parseInt(pageNoString);}PageModel
 
   pageModel=UserManager.getInstance().findUserList(pageNo, pageSize); %>
 

At this point, paging query has basically been implemented. Through this study, I have learned the basic implementation principle of paging query again, you don't need to be confused when using the datagrid encapsulated by easyui in the future!