servler+jsp+jdbc的分页简单案例(链接oracle)

xiaoxiao2021-02-28  6

分页的工具类

package lesson08; import java.util.List; public class PageTools_1 { /** * * * @param curPage 页面传入的当前页 * @param totalCount 数据库查询的总记录数 * @param pageCount 每页显示的条数 */ public PageTools_1(Integer curPage,Integer totalCount,Integer pageCount){ this.curPage=curPage; this.totalCount=totalCount; this.pageCount=pageCount==null?this.pageCount:pageCount; //上一页 this.prePage=(curPage==1?1:curPage-1); //总页数 this.totalPage=totalCount%this.pageCount==0?totalCount/this.pageCount:(totalCount/this.pageCount+1); //下一页 this.nextPage=(curPage==totalPage)?totalPage:(curPage+1); //数据库第几页每页显示的数据 开始索引和结束索引 this.startIndex=(curPage-1)*this.pageCount+1; this.endIndex=curPage*this.pageCount; } /** * 当前页(动态 由页面传递) */ private Integer curPage; /** * 上一页 * prePage=(curPage==1?1:curPage-1) * */ private Integer prePage; /** * 下一页 * nextPage=(nextPage==totalPage)?totalPage:(curPage+1) * * */ private Integer nextPage; //每页显示的条数 private Integer pageCount=10; /** * 总页数 * totalPage=(totalCount%pageCount==0?totalCount/pageCount:(totalCount/pageCount+1) * */ private Integer totalPage; //数据库的总记录数(数据库查询) private Integer totalCount; //每页的数据放入该集合中 private List date; /** * 数据库开始索引和结束索引 * * startIndex=(curPage-1)*PageCount+1 * endIndex=curPage*pageCount * */ private int startIndex; private int endIndex; public int getStartIndex() { return startIndex; } public void setStartIndex(int startIndex) { this.startIndex = startIndex; } public int getEndIndex() { return endIndex; } public void setEndIndex(int endIndex) { this.endIndex = endIndex; } public List getDate() { return date; } public void setDate(List date) { this.date = date; } public Integer getCurPage() { return curPage; } public void setCurPage(Integer curPage) { this.curPage = curPage; } public Integer getPrePage() { return prePage; } public void setPrePage(Integer prePage) { this.prePage = prePage; } public Integer getNextPage() { return nextPage; } public void setNextPage(Integer nextPage) { this.nextPage = nextPage; } public Integer getPageCount() { return pageCount; } public void setPageCount(Integer pageCount) { this.pageCount = pageCount; } public Integer getTotalPage() { return totalPage; } public void setTotalPage(Integer totalPage) { this.totalPage = totalPage; } public Integer getTotalCount() { return totalCount; } public void setTotalCount(Integer totalCount) { this.totalCount = totalCount; } //用来测试 public static void main(String[] args) { int curPage=6; int totalCount=27; int pageCount=5; PageTools_1 pt=new PageTools_1(curPage, totalCount, pageCount); System.out.println(pt.getNextPage()); System.out.println(pt.getPrePage()); System.out.println(pt.getTotalPage()); System.out.println(pt.getStartIndex()); System.out.println(pt.getEndIndex()); } } jdbc连接类

package lesson08; import java.io.IOException; import java.io.InputStream; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.Properties; public class OracleDbUtils_1 { static Properties p=new Properties(); static{ //在bin目录去读jdbcmysql.properties文件 InputStream is=OracleDbUtils_1.class.getResourceAsStream("/jdbcoracle.properties"); try { p.load(is); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } public static Connection getConnection() throws Exception{ String url=p.getProperty("url"); //获取连接服务器的ip地址 端口 和数据库 String driverClass=p.getProperty("driverClass");//告诉jdbc使用的是什么数据库 String uname=p.getProperty("username");//使用哪个账号登录 String paw=p.getProperty("password");//登录密码 Class.forName(driverClass);//加载该类 Connection conn=DriverManager.getConnection(url,uname,paw);//登录 return conn; } public static List<Map> query(String sql) throws Exception{ Connection conn=getConnection(); PreparedStatement pst= conn.prepareStatement(sql); ResultSet rs=pst.executeQuery(); // 获取包含有关 ResultSet 对象列信息的 ResultSetMetaData 对象 ResultSetMetaData rsmd=rs.getMetaData(); //获取列的总数 int columnCount=rsmd.getColumnCount(); List list=new ArrayList(); while(rs.next()){ Map map=new HashMap(); for(int i=1;i<=columnCount;i++){ //获取列名 String colName=rsmd.getColumnName(i); //获取对应的值 String colValue=rs.getString(i); //以键值对的方式存入map集合中 map.put(colName, colValue); } list.add(map); } return list; } public static void main(String[] args) throws Exception{ List<Map> result=query("select * from cc"); System.out.println(result); } }

properties文件

url=jdbc:oracle:thin:@localhost:1521:orcl driverClass=oracle.jdbc.OracleDriver username=str password=123456

查询总记录数和把相关参数传入分页工具类中

package lesson08; import java.util.List; import java.util.Map; /** * javabean是MVC设计模式的模型层(Model)操作数据 * * @author THINK *2017年10月10日 下午9:18:53 *Mytable.java *easyTop */ public class Mytable_1 { public Integer getTableListCount(String name){ if(name==null){ name=""; } int i=0; //该sql语句按条件查询的总记录数 String sql="select count(rowid) as cr from dept1 where deptno like '%"+name+"%'"; try { //调用OracleDbUtils_1类中的query()方法 查询sql语句 List<Map> result=OracleDbUtils_1.query(sql); //result.get(0):返回此列表中指定位置上的元素。 返回的是一个map集合 get("CR"): 返回指定键所映射的值 i= Integer.parseInt(result.get(0).get("CR").toString()); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } return i; } public PageTools_1 getTable(String name,Integer curPage) throws Exception{ if(name==null){ name=""; } //按条件查询返回记录的总数 Integer totalCount=getTableListCount(name); //创建PageTools_1对象 传入当前页 总记录数 每页显示的数量 PageTools_1 pt=new PageTools_1(curPage, totalCount, 100); //sql语句查询每页显示的具体数据 List<Map> result=OracleDbUtils_1.query("select * from (select t.*,rownum rn from dept1 t where t.deptno like '%"+name+"%') where rn>="+pt.getStartIndex()+" and rn<="+pt.getEndIndex()); pt.setDate(result); return pt; } }

Servlet跳转类(中转---核心控制)

package lesson08; import java.io.IOException; import javax.servlet.ServletException; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; /** * Servlet是MVC设计模式的控制层(Controller) 负责调度(javabean的创建 服务调用) * 例子 * 1、获取表单的数据 调用javabean写入数据库 * 2、调用javabean获取数据库集合 将数据传递jsp视图中 * * */ public class TableServleta extends HttpServlet { private static final long serialVersionUID = 1L; /** * @see HttpServlet#HttpServlet() */ public TableServleta() { super(); // TODO Auto-generated constructor stub } Mytable_1 my=new Mytable_1(); public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { try { Integer curPageInt=1; //从页面获取参数 获取查询的姓名 String name=request.getParameter("tt"); //获取当前页 String curPage=request.getParameter("curPage"); //页面用户可以输入指定的页数 ====获取指定页 String a=request.getParameter("gg"); if(a!=null){ curPageInt=Integer.parseInt(a); } else if(curPage!=null){ curPageInt=Integer.parseInt(curPage); } //调用Mytable_1类中 getTable() 方法 返回PageTools对象 PageTools_1 tableList=my.getTable(name,curPageInt); //把PageTools对象 设在request作用域中 request.setAttribute("tableList", tableList); request.getRequestDispatcher("/lesson08/tableView.jsp").forward(request, response); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } } public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { doGet(request, response); } }

jsp展示功能

<!-- jsp是MVC设计模式中的view层 主要是和用户进行交互(肉眼看到的界面) 只允许出现html标签 el表达式 标签 --> <%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> <title>Insert title here</title> </head> <body> <form action="${pageContext.request.contextPath }/TableServleta"> <input type="text" name="tt"/> <input type="submit" value="搜索"/> </form> <table border="1px" width="100%"> <%-- 设置一个标记 用来判断列名只输出一次 --%> <c:set var="i" value="2"></c:set> <%-- 循环获取的list中的map集合 --%> <c:forEach var="db" items="${requestScope.tableList.date }"> <%-- 当i等于2时进入 --%> <c:if test="${pageScope.i==2}"> <tr> <%-- 循环map集合获取键值对 并输出键 --%> <c:forEach var="map" items="${pageScope.db }"> <th>${pageScope.map.key}</th> </c:forEach> </tr> <%-- 修改i的值 --%> <c:set var="i" value="3"></c:set> </c:if> <tr> <%-- 循环map集合获取键值对 并输出值 --%> <c:forEach var="map" items="${pageScope.db }"> <td>${pageScope.map.value}</td> </c:forEach> </tr> </c:forEach> <tr> <td colspan="4"> <a href="${pageContext.request.contextPath }/TableServleta?curPage=1">首页</a> <a href="${pageContext.request.contextPath }/TableServleta?curPage=${requestScope.tableList.prePage}">上一页</a> <!-- 页数分页 --> <!-- 页面显示5页 --> <c:forEach var="i" begin="${requestScope.tableList.curPage}" end="${requestScope.tableList.curPage+4 }" step="1"> <c:if test="${pageScope.i<=requestScope.tableList.totalPage}"> <a href="${pageContext.request.contextPath }/TableServleta?curPage=${pageScope.i}"><input type="button" value="${pageScope.i}"/></a> <c:if test="${pageScope.i==(requestScope.tableList.curPage+4) &&(requestScope.tableList.curPage+4<requestScope.tableList.totalPage) }"> ... </c:if> </c:if> </c:forEach> 总页数 ${requestScope.tableList.totalPage} <a href="${pageContext.request.contextPath }/TableServleta?curPage=${requestScope.tableList.nextPage}">下一页</a> <a href="${pageContext.request.contextPath }/TableServleta?curPage=${requestScope.tableList.totalPage}">尾页</a> <form action="${pageContext.request.contextPath }/TableServleta"> <input type="search" name="gg" value="${requestScope.tableList.curPage }" style="margin-left:10px;width:80px;"/><input type="submit" value="跳转"/> </form> </td> </tr> </table> </body> </html>

页面的效果

转载请注明原文地址: https://www.6miu.com/read-1400237.html

最新回复(0)