Shulou(Shulou.com)06/01 Report--

This article introduces the relevant knowledge of "how to use jsp+mysql to achieve paging query of web pages". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

1. The core sql statement for paging query

(1) sql statement that queries the total number of records in the database:

Select count (*) from + (table name)

(2) the sql statement for the number of records per query:

Where: 0 is the index of the search, and 2 is the number of entries per search.

Select * from table name limit 0Jing 2; II. Code implementation

* the previous article wrote about these two classes, the DBconnection class: used to obtain database connections, and the Author object class. Click on the link to view the code of these two classes. Click the link to view the DBconnection class and the Author object class

(1) login page: index.jsp.

Paging query of Insert title here user list

(2) display page: userlistpage.jsp.

Query page number, name, price, quantity, date style ${author.id} ${author.name} ${author.price} ${author.num} ${author.dates} ${author.style} first page previous page next last page Home page, previous page, next page, last page, first page, last page, next last page.

(3) function realization: AuthorDao.java.

Package com.dao;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;import com.entity.Author;public class AuthorDao {public Author check (String username, int password) {Author obj = null; try {DBConnection db = new DBConnection () / / get database connection Connection conn = db.getConn (); String sql= "select * from furnitures where name =? And id =? "; PreparedStatement ps=conn.prepareStatement (sql); / / set the user name and password as parameters into the sql statement ps.setString (1pm username); ps.setInt (2pm password); / / execute the query statement ResultSet rs = ps.executeQuery () / / user name and password are correct. Data found in European style coffee table if (rs.next ()) {obj = new Author (); obj.setId (rs.getInt (1)); obj.setName (rs.getString (2)); obj.setPrice (rs.getInt (3)) Obj.setNum (rs.getInt (4)); obj.setDates (rs.getString (5)); obj.setStyle (rs.getString (6));} catch (SQLException e) {/ / TODO Auto-generated catch block e.printStackTrace ();} return obj } / * user list information query * @ return * / public List queryAuthorList () {Author obj = null; List list = new ArrayList (); try {DBConnection db = new DBConnection (); / / get database connection Connection conn = db.getConn () String sql= "select * from furnitures"; PreparedStatement ps=conn.prepareStatement (sql); / / execute query statement ResultSet rs = ps.executeQuery () / / user name and password are correct. Check the data European style coffee table / / cycle to get user information while (rs.next ()) {obj = new Author (); obj.setId (rs.getInt (1)); obj.setName (rs.getString (2)) Obj.setPrice (rs.getInt (3)); obj.setNum (rs.getInt (4)); obj.setDates (rs.getString (5)); obj.setStyle (rs.getString (6)); / / add objects to list list.add (obj) }} catch (SQLException e) {/ / TODO Auto-generated catch block e.printStackTrace ();} return list;} / * query the total number of user table records * @ return * / public int queryUserListCount () {DBConnection db; try {db = new DBConnection () Connection conn = db.getConn (); String sql = "select count (*) from furnitures"; PreparedStatement ps = conn.prepareStatement (sql); ResultSet rs = ps.executeQuery (); if (rs.next ()) {return rs.getInt (1) }} catch (SQLException e) {/ / TODO Auto-generated catch block e.printStackTrace ();} return 0 } / * query user paging data * @ param pageIndex data starting index * @ param pageSize displays the number of entries per page * @ return * / public ListqueryUserListPage (int pageIndex,int pageSize) {Author obj = null; List list = new ArrayList (); try {Connection conn = new DBConnection (). GetConn () String sql = "select * from furnitures limit?,?;"; PreparedStatement ps = conn.prepareStatement (sql); ps.setObject (1, pageIndex); ps.setObject (2 Magi pageSize); ResultSet rs = ps.executeQuery () / / traversing the result set to get user list data while (rs.next ()) {obj = new Author (); obj.setId (rs.getInt (1)); obj.setName (rs.getString (2)); obj.setPrice (rs.getInt (3)) Obj.setNum (rs.getInt (4)); obj.setDates (rs.getString (5)); obj.setStyle (rs.getString (6)); list.add (obj);} catch (SQLException e) {/ / TODO Auto-generated catch block e.printStackTrace () } return list;} / * user added * @ param obj * / public void add (Author obj) {try {DBConnection db = new DBConnection (); / / get database connection Connection conn = db.getConn () String sql= "insert into furnitures values (id,?)"; PreparedStatement ps=conn.prepareStatement (sql); ps.setObject (1, obj.getName ()); ps.setObject (2, obj.getPrice ()); ps.setObject (3, obj.getNum ()); ps.setObject (4 memobj.getDates ()) Ps.setObject (5, obj.getStyle ()); / / execute sql statement ps.execute ();} catch (SQLException e) {/ / TODO Auto-generated catch block e.printStackTrace () }} / / Delete user public void del (int id) {try {DBConnection db = new DBConnection (); / / get database connection Connection conn = db.getConn (); String sql= "delete from furnitures where id =?" PreparedStatement ps=conn.prepareStatement (sql); ps.setObject (1, id); / / execute sql statement ps.execute () } catch (SQLException e) {/ / TODO Auto-generated catch block e.printStackTrace ();}}

(4) interaction layer: AuthorListPageServlet.java.

Package com.servlet;import java.io.IOException;import java.util.List;import javax.servlet.ServletException;import javax.servlet.annotation.WebServlet;import javax.servlet.http.HttpServlet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import com.dao.AuthorDao;import com.entity.Author;import com.util.PageBean;/** * Servlet implementation class AuthorListPageServlet * / @ WebServlet ("/ AuthorListPageServlet") public class AuthorListPageServlet extends HttpServlet {private static final long serialVersionUID = 1L / * * @ see HttpServlet#HttpServlet () * / public AuthorListPageServlet () {super (); / / TODO Auto-generated constructor stub} / * @ see HttpServlet#doGet (HttpServletRequest request, HttpServletResponse response) * / protected void doGet (HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {/ / TODO Auto-generated method stub int pageSize = 2 AuthorDao ad = new AuthorDao (); / / Total number of records int record = ad.queryUserListCount (); / / received page number String strPage = request.getParameter ("currPage"); int currPage = 1 strPage / default first page if (strPage! = null) {currPage = Integer.parseInt (strPage) } PageBean pb = new PageBean (currPage,pageSize,record); / / query the result set of a page List list = ad.queryUserListPage (pb.getPageIndex (), pageSize); pb.setList (list); request.setAttribute ("pageBean", pb); request.getRequestDispatcher ("userlistpage.jsp") .forward (request, response) } / * * @ see HttpServlet#doPost (HttpServletRequest request, HttpServletResponse response) * / protected void doPost (HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {/ / TODO Auto-generated method stub doGet (request, response);}

(5) tool class: PageBean.java. The purpose is to get the result set.

Package com.util;import java.util.List;public class PageBean {private int currentPage;// current page number private int pageIndex;// data starting index private int pageSize;// entries per page private int record;// total records private int totalPage;// total pages private Listlist / / the result set / * constructor shown on each page initializes pageIndex and totalPage * @ param currentPage * @ param pageIndex * @ param pageSize * / public PageBean (int currentPage,int pageSize,int record) {this.currentPage = currentPage; this.pageSize = pageSize; this.record = record / / Total number of pages if (record% pageSize = = 0) {/ / divides, there is no extra page this.totalPage = record / pageSize;} else {/ / there is extra data, add a page this.totalPage = record / pageSize + 1 } / / calculate the data starting index pageIndex if (currentPage

This.totalPage) {this.currentPage = this.totalPage;} this.pageIndex = (this.currentPage-1) * this.pageSize;} public int getCurrentPage () {return currentPage;} public void setCurrentPage (int currentPage) {this.currentPage = currentPage;} public int getPageIndex () {return pageIndex } public void setPageIndex (int pageIndex) {this.pageIndex = pageIndex;} public int getPageSize () {return pageSize;} public void setPageSize (int pageSize) {this.pageSize = pageSize;} public int getRecord () {return record;} public void setRecord (int record) {this.record = record;} public int getTotalPage () {return totalPage } public void setTotalPage (int totalPage) {this.totalPage = totalPage;} public List getList () {return list;} public void setList (List list) {this.list = list;}} 3. Run result

(1) Home page:

(2) Middle page:

(3) the last page:

This is the end of the content of "how to use jsp+mysql to realize page paging query". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.