JSP连接mysql数据库分页查询

来源：互联网发布：古筝淘宝网编辑：程序博客网时间：2024/04/27 17:24

谈下实现方法吧，说是JSP连接mysql，其实是在JSP中利用<% %>来直接写java代码通过JDBC连接mysql。JDBC连接各种数据库的方式就不做过多的介绍了，相信网上一搜一大堆的。

分页查询一般有两种方式：1、数据库分页 2、页面分页一般都是用前者，因为如果数据量过大的话，将所有数据一次性查询到页面再做分页，结果大家可想而知了。数据库分页针对于不同的数据库方法不同，具体方法还是那句话：网上一搜一大堆。这里主要针对mysql来说，分页的核心点就在于这个LIMIT。语句SELECT id,username,password,state FROM user ORDER BY id LIMIT StartRow , PageSize;这里的limit后面跟着的两个参数分别为起始的编号和每页显示的数目。

下图为实现界面：

具体代码如下：

<%String driverName = "com.mysql.jdbc.Driver";String userName = "root";String userPasswd = "mysql";String dbName = "warehouse";String tableName = "user"; String url = "jdbc:mysql://localhost:3307/" + dbName + "?user="+ userName + "&password=" + userPasswd;Class.forName(driverName).newInstance();Connection connection = DriverManager.getConnection(url);Statement statement = connection.createStatement();int PageSize = 10; //每页显示记录数int StartRow = 0; //开始显示记录的编号int PageNo = 0;//需要显示的页数int CounterStart = 0;//每页页码的初始值int CounterEnd = 0;//显示页码的最大值int RecordCount = 0;//总记录数;int MaxPage = 0;//总页数int PrevStart = 0;//前一页int NextPage = 0;//下一页int LastRec = 0;int LastStartRecord = 0;//最后一页开始显示记录的编号//获取需要显示的页数if (request.getParameter("PageNo") == null) { //如果为空，则表示第1页if (StartRow == 0) {PageNo = StartRow + 1; //设定为1}} else {PageNo = Integer.parseInt(request.getParameter("PageNo")); //获得用户提交的页数StartRow = (PageNo - 1) * PageSize; //获得开始显示的记录编号}//设置显示页码的初始值!!if (PageNo % PageSize == 0) {CounterStart = PageNo - (PageSize - 1);} else {CounterStart = PageNo - (PageNo % PageSize) + 1;}CounterEnd = CounterStart + (PageSize - 1);//获取总记录数ResultSet rs = statement.executeQuery("select count(*) from user");rs.next();RecordCount = rs.getInt(1);//rs = statement.executeQuery("SELECT usercode,username,password,comcode,flag_level,flag_status FROM zyd_user ORDER BY usercode DESC LIMIT "+StartRow+", "+PageSize);rs = statement.executeQuery("SELECT id,username,password,state FROM user ORDER BY id LIMIT "+ StartRow + ", " + PageSize);//获取总页数MaxPage = RecordCount % PageSize;if (RecordCount % PageSize == 0) {MaxPage = RecordCount / PageSize;} else {MaxPage = RecordCount / PageSize + 1;}%><body><form id="submitForm" name="submitForm" action="" method="post"><div class="ui_content"><div class="ui_tb"><table class="table" cellspacing="0" cellpadding="0" width="100%"align="center" border="0"><tr><th width="30"><input type="checkbox" id="all"onclick="selectOrClearAllCheckbox(this);" /></th><th>请购单号</th><th>合同号</th><th>请购人</th><th>请购时间</th><th>请购接收人</th><th>请购单状态</th><th>4</th><th>5</th><th>操作</th></tr><%int i = 1;while (rs.next()) {int bil = i + (PageNo - 1) * PageSize;int id = rs.getInt("id");String username = rs.getString("username");String password = rs.getString("password");int state = rs.getInt("state");%><tr><td><input type="checkbox" name="IDCheck" value="14458619251417"class="acb" /></td><td><%=id%></td><td><%=username%></td><td><%=password%></td><td></td><td></td><td></td><td></td><td><%=state%></td><td><a href="Purchase_examine.jsp?id=<%=id%>&username=<%=username %>&password=<%=password %>&state=<%=state %>" class="edit">审核</a></td></tr><%i++;}%></table></div><div class="ui_tb_h30"><div class="ui_flt" style="height: 30px; line-height: 30px;">共有<span class="ui_txt_bold04"><%=RecordCount%></span> 条记录，当前第<span class="ui_txt_bold04"><%=PageNo + "/" + MaxPage%></span> 页</div><div class="ui_frt"><!--    如果是第一页，则只显示下一页、尾页 --><input type="button" value="首页" class="ui_input_btn01"onclick="jumpNormalPage(1);" /><input type="button" value="上一页" class="ui_input_btn01"onclick="up(<%=PageNo%>,<%=MaxPage%>);" /><input type="button" value="下一页" class="ui_input_btn01"onclick="down(<%=PageNo%>,<%=MaxPage%>);" /><input type="button" value="尾页" class="ui_input_btn01"onclick="jumpNormalPage(<%=MaxPage%>);" /><!--     如果是最后一页，则只显示首页、上一页 -->转到第<input type="text" id="jumpNumTxt" class="ui_input_txt04"autocomplete="off" />页<input type="button" class="ui_input_btn01" value="跳转"onclick="jumpInputPage(jumpNumTxt.value ,<%=MaxPage%>);" /></div></div></div></div></form></body><%rs.close();statement.close();connection.close();%>

本人也为初入java行业的菜鸟一枚，做的东西也是度娘七拼八凑在一块儿的，目的倒是都达到了，更深层次的东西也在学习中。如果有疑问可指出，大家共同学习。技术这东西需要分享，就像大家都爱“开源”二字一样。

0 0