JAVAEE ----- MySQL paging technology (with search)

JAVAEE ----- MySQL paging technology (with search)
Requirements:

Why does paging technology need to be used? When we query data in the database, we need to return the data to the display page. The database contains a large amount of data, all of which are displayed on too many pages. Therefore, we need to use the paging technology, different data is displayed on each page.

Solution:

1. We must determine the number of entries displayed on each page. pageSize = 20

2. We need to query the total number of data records, sunNums

3. We need to calculate the total number of pages required-use the total data/pageSize, and determine whether the entire page is split. sunNums/pageSize + (sunNums % pageSize = 0? 0: 1)

4, we need to pass from the front-end page (pageNum), calculate the need to show where the data starts (pageNum-1) * pageSize

5. We need to encapsulate the queried data into a list. >, Save the map, and return the map to the foreground.

PS: Of course, you need to change the parameter when you need to search. For example, you need to upload the query conditions to the dao layer from the foreground for processing. Here we use the layered technology front-end -----> servlet -----> service ------> dao

Database statements used:

 

Select count (*) FROM stud // query the total amount of data SELECT * FROM stud LIMIT 120th, 20 // starting FROM data records, the next 20 data records

 

Core code:

The core code is mainly the DAO data layer.

Easy version: No search

 

Map
 
  
Map = new HashMap
  
   
(); Integer pageSize = 20; // number of entries displayed per page String SQL = "select count (1) from stud "; // execute this SQL statement to obtain the total number of data records
  
 

// The c3p0 data pool is used here (the point function is added and C3P0Utils is made). The dbutils toolkit contains (QueryRunner) QueryRunner run = new QueryRunner (C3p0Utils. getDs (); String sumNum = "" + run. query (SQL, new ScalarHandler (); // encapsulate the data into one row and one column is a result value. You do not need to process it yourself here. You can directly use int sunNums = Integer in the package. valueOf (sumNum); // The number of all data entries

// Calculate the number of pages. int allPageNum = sunNums/pageSize + (sunNums % pageSize = 0? 0: 1); map. put ("sumNum", allPageNum); // how many pages are stored together SQL = "select * from stud limit" + (pageNum-1) * pageSize + "," + pageSize; // System. out. println (SQL + "-----" + pageNum); List
 
  
> List = run. query (SQL, new MapListHandler (); map. put ("list", list); // Add to map // System. out. println (list); return map;
 

The code above can only implement simple paging of data without searching for it,

 

Effect:

The previous and next pages are completed in servlet.

Full Version: (with search)

In the search, the data we transmit from the foreground is encapsulated in a value object. Therefore, in the background, you only need to take the value from the value object VO.

 

Map
 
   map  =new  HashMap
  
   ();Integer pagesize=20;
  
 

/** The new technology is used here, which is compatible with the query part and the query ALl function, because the data transmitted from the front-end may not exist here (no input from the front-end ), this query method is compatible with all query methods */

String SQL = "select count (*) from stud where 1 = 1"; String sql2 = "select * from stud where 1 = 1"; // write 1 = 1, it can match the following condition to match if (stud. getId ()! = Null & stud. getId (). trim (). length ()> 0) {SQL = SQL + "And id like '%" + stud. getId () + "% '"; sql2 = sql2 + "and id like' %" + stud. getName () + "% '"; // fuzzy query} if (stud. getName ()! = Null & stud. getName (). trim (). length ()> 0) {SQL = SQL + "and name like '%" + stud. getName () + "% '"; sql2 = sql2 + "and name like' %" + stud. getName () + "% '";} QueryRunner run = new QueryRunner (C3p0Utils. getDs (); // String str = "" + run. query (SQL, new ScalarHandler (); // obtain all the data. The total number of int sumNum = Integer. valueOf (str); // convert it to int type // calculate the total number of pages together with sumNum = sumNum/pagesize + (sumNum % pagesize) = 0? ); Map. put ("sumNum", sumNum); // uploads the total number of pages to the foreground List
 
  
> List = run. query (sql2 + "limit" + (pageNum-1) * pagesize + "," + pagesize, new MapListHandler (); map. put ("list", list); return map;
 

The background technology is like this. When we transmit data to the foreground, we also need to display problems on the servlet processing page, such as the previous page, the next page, and the page will automatically move.

 

 

Servlet Control
Servlet section, you need to control the previous page, next page, after the search, the page Click is also the warmth of the search

Public void doPost (HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {// inject IstudService service = new ServiceDAO (); // The number of pages transmitted from the foreground must be accepted, if not, the value is 1response. setContentType ("text/html; charset = UTF-8"); // The value read from the foreground may contain Chinese characters and must be encoded. filter can be used to filter requests. setCharacterEncoding ("UTF-8"); Integer pageNum = null; Map
 
  
Map = new HashMap
  
   
(); // Obtain the number of clicked pages from the front-end. For the first time, there is no data. The default value is 1 String num = request. getParameter ("num"); // null is not written, and 1if (num = null | num. trim (). length () = 0) {pageNum = 1;} else {pageNum = Integer. valueOf (num); // otherwise, the passed value will be parsed}
  
 

The submission method needs to be different below, because form submission requires a query condition. Directly clicking the page number is where the query condition goes down and the database queries the data, which is different and must be different:

Stud stud = new Stud ();/** the technology used here, hyperlink and form submission are two different methods, so they are distinguished, of course, this is why * data is saved for the next use */if (request. getMethod (). inclusignorecase ("get") {stud = (Stud) request. getSession (). getAttribute ("stud"); // if you click a hyperlink, you can directly obtain the value in the session.} else {String id = request. getParameter ("id"); String name = request. getParameter ("name"); stud. setId (id); stud. setName (name); request. getSession (). setAttribute ("stud", stud); // store the data in the session. After the result is searched, the page number will not be transferred to other pages.} try {map = service. query (pageNum, stud); map. put ("current", pageNum); // the current page is saved, but the frontend does not need a hyperlink to read.} catch (Exception e) {e. printStackTrace ();}

The following is the technology for displaying the previous and next pages on the page.

// Pagination is required. We need to calculate the number, start, and end of each page, and then calculate int startPage = 1 after clicking; // start position int endPage = 10; // end position int sizePage = 10; // determine the number of int sunNum = (Integer) (map. get ("sumNum");/** the most important thing is ----- Based on the passed parameters * first, you must determine whether the first page is fully occupied, if the page is not full, give the endPage all * If one page cannot be occupied, we need to set start and end, start = add half of the current page, end is equal to start + sizePage, however, you need to determine if * exceeds the range of the total number of pages, and then assign a value **/if (sunNum
 
  
(EndPage-startPage)/2) {startPage = pageNum-sizePage/2 + 1; endPage = startPage + sizePage; if (sunNum
  
 

Finally, the encapsulated data is passed to the foreground:

request.setAttribute("result", map);request.getRequestDispatcher("/jsps/show.jsp").forward(request, response);

Front-end page front-end page uses jstl to obtain data in the container

$ {M. id}, $ {m. name}
<Previous Page $ {X} $ {X} Next page>
:

Conditional query: