当前位置:Gxlcms > 数据库问题 > 【知了堂学习笔记】JSP页面数据分页实现(一)——分页概念以及主流数据库的分页查询

【知了堂学习笔记】JSP页面数据分页实现(一)——分页概念以及主流数据库的分页查询

时间:2021-07-01 10:21:17 帮助过:16人阅读

  技术分享

  2.分页的实现方式

    1)假分页(不推荐):什么是假分页呢?假分页就是一次性将数据库里的数据全部取出来,存储在页面中,然后再将数据分别展示出来。这种假分页在数据少的还可以玩玩,当数据多起来的时候,这种方式就爆炸了。由于一次取出的数据太大,会导致你的页面爆炸,点下一页要等很久,用户体验感很差,所以不推荐使用。

    2)真正的分页:真正的分页就是你的页面需要多少数据,就去数据库取多少数据。这种分页就十分灵活了,在对付数据庞大的查询时,有很大的优势。所以我们这次的笔记就使用这样的方式。

二.数据库分页查询

  数据库分页查询,就是根据页面的需要,一次只查询部分的数据,当用户点击下一页的时候就继续查询另外的数据。现在主流的数据库主要有三种:Oracle、MySQL、SQL server,我们接下来就看看,三种数据库对应分页查询的方法。

 1.MySQL——相对来说最简单分页查询

  MySQL的分页查询要用到 limit关键字,方式就是 limit m,n   m表示第几条数据   n表示从m+1开始取多少条数据,比如

    select * from table limit m,n       其中m是指记录开始的index,从0开始,表示第一条记录

    具体:select * from tableName where 条件 limit 当前页码*页面容量-1,页面容量

 2.Oracle 分页

  Oracle分页要用到的关键字是 rownum (行号),我们直接看它的用法

  1)查询表中的前n条记录   : select * from Table where rownum <= n

  2)查询第 n 到第 m 条记录   :select * from (select 表名.*, rownum rn from 表名 where rownum <=m) where rn > n;
      对于这种形式的查询,oracle不像mysql那么方便,它必须使用子查询或者是集合操作来实现。

 3.SQL server的分页

   在分页查询上,我感觉SQL Server比较费劲,没有一个专门的分页的语句,并且每个版本对应的查询方式不一样,比如:  

    1)SQL Server 2000数据库分页
    Select top pagesize * from 表名 where 列名 not  in(select top pagesize*page 列名 from  表名 order by 列名) order by 列名

    2)SQL Server 2005数据库分页(后面的数据分页就大同小异了)
    Select * from (select 列名,row_搜索number() over(order by  列名1) as 别名from 表名) as t where t.列名1>=startrow and t.列名1<=endrow

三.页面的编写

  数据的分页查询学会了,我们就要规划页面如何在页面显示了。一般的网页中,我们都是以表格的样式呈现数据的,所以我们先把jsp页面做好,再去通过编写后台代码让数据呈现在页面上,这里我给出了我们测试的jsp页面:

  效果图:

技术分享

附上代码:

  

  1. <span style="color: #0000ff"><</span><span style="color: #800000">div </span><span style="color: #ff0000">class</span><span style="color: #0000ff">="search"</span><span style="color: #0000ff">></span>
  2. <span style="color: #0000ff"><</span><span style="color: #800000">p</span><span style="color: #0000ff">></span>申请日期:<span style="color: #0000ff"></</span><span style="color: #800000">p</span><span style="color: #0000ff">></span>
  3. <span style="color: #0000ff"><</span><span style="color: #800000">select </span><span style="color: #ff0000">id</span><span style="color: #0000ff">="time"</span><span style="color: #0000ff">></span>
  4. <span style="color: #0000ff"><</span><span style="color: #800000">option </span><span style="color: #0000ff">></span>选择日期<span style="color: #0000ff"></</span><span style="color: #800000">option</span><span style="color: #0000ff">></span>
  5. <span style="color: #0000ff"></</span><span style="color: #800000">select</span><span style="color: #0000ff">></span>
  6. <span style="color: #0000ff"><</span><span style="color: #800000">input </span><span style="color: #ff0000">class</span><span style="color: #0000ff">="button"</span><span style="color: #ff0000"> type</span><span style="color: #0000ff">="submit"</span><span style="color: #ff0000"> name</span><span style="color: #0000ff">=""</span><span style="color: #ff0000"> value</span><span style="color: #0000ff">="查询"</span><span style="color: #0000ff">></</span><span style="color: #800000">input</span><span style="color: #0000ff">></span>
  7. <span style="color: #0000ff"><</span><span style="color: #800000">div </span><span style="color: #ff0000">class</span><span style="color: #0000ff">="tuxin"</span><span style="color: #0000ff">><</span><span style="color: #800000">img </span><span style="color: #ff0000">src</span><span style="color: #0000ff">="../../images/图形化报表.png"</span><span style="color: #0000ff">></span> <span style="color: #0000ff"><</span><span style="color: #800000">a </span><span style="color: #ff0000">href</span><span style="color: #0000ff">="#"</span><span style="color: #0000ff">></span>图形报表<span style="color: #0000ff"></</span><span style="color: #800000">a</span><span style="color: #0000ff">></</span><span style="color: #800000">div</span><span style="color: #0000ff">></span>
  8. <span style="color: #0000ff"><</span><span style="color: #800000">div </span><span style="color: #ff0000">class</span><span style="color: #0000ff">="dayin"</span><span style="color: #0000ff">><</span><span style="color: #800000">img </span><span style="color: #ff0000">src</span><span style="color: #0000ff">="../../images/打印机.png"</span><span style="color: #0000ff">></span> <span style="color: #0000ff"><</span><span style="color: #800000">a </span><span style="color: #ff0000">href</span><span style="color: #0000ff">="#"</span><span style="color: #0000ff">></span>导出打印<span style="color: #0000ff"></</span><span style="color: #800000">a</span><span style="color: #0000ff">></</span><span style="color: #800000">div</span><span style="color: #0000ff">></span>
  9. <span style="color: #0000ff"><</span><span style="color: #800000">form</span><span style="color: #0000ff">></span>
  10. <span style="color: #0000ff"><</span><span style="color: #800000">p</span><span style="color: #0000ff">></span>采购编号:<span style="color: #0000ff"></</span><span style="color: #800000">p</span><span style="color: #0000ff">></span>
  11. <span style="color: #0000ff"><</span><span style="color: #800000">input </span><span style="color: #ff0000">type</span><span style="color: #0000ff">="text"</span><span style="color: #ff0000"> name</span><span style="color: #0000ff">=""</span><span style="color: #0000ff">></span>
  12. <span style="color: #0000ff"><</span><span style="color: #800000">p</span><span style="color: #0000ff">></span>材料名称:<span style="color: #0000ff"></</span><span style="color: #800000">p</span><span style="color: #0000ff">></span>
  13. <span style="color: #0000ff"><</span><span style="color: #800000">input </span><span style="color: #ff0000">type</span><span style="color: #0000ff">="text"</span><span style="color: #ff0000"> name</span><span style="color: #0000ff">=""</span><span style="color: #0000ff">></span>
  14. <span style="color: #0000ff"><</span><span style="color: #800000">input </span><span style="color: #ff0000">class</span><span style="color: #0000ff">="button"</span><span style="color: #ff0000"> type</span><span style="color: #0000ff">="submit"</span><span style="color: #ff0000"> name</span><span style="color: #0000ff">=""</span><span style="color: #ff0000"> value</span><span style="color: #0000ff">="查询"</span><span style="color: #0000ff">></</span><span style="color: #800000">input</span><span style="color: #0000ff">></span>
  15. <span style="color: #0000ff"><</span><span style="color: #800000">input </span><span style="color: #ff0000">type</span><span style="color: #0000ff">="reset"</span><span style="color: #ff0000"> class</span><span style="color: #0000ff">="button"</span><span style="color: #ff0000"> value</span><span style="color: #0000ff">="重置"</span><span style="color: #ff0000"> style</span><span style="color: #0000ff">="background:#e4e1e1;color:#000"</span><span style="color: #0000ff">></</span><span style="color: #800000">input</span><span style="color: #0000ff">></span>
  16. <span style="color: #0000ff"></</span><span style="color: #800000">form</span><span style="color: #0000ff">></span>
  17. <span style="color: #0000ff"></</span><span style="color: #800000">div</span><span style="color: #0000ff">></span>
  18. <span style="color: #0000ff"><</span><span style="color: #800000">div </span><span style="color: #ff0000">class</span><span style="color: #0000ff">="contents"</span><span style="color: #0000ff">></span>
  19. <span style="color: #0000ff"><</span><span style="color: #800000">table </span><span style="color: #ff0000">class</span><span style="color: #0000ff">="table"</span><span style="color: #0000ff">></span>
  20. <span style="color: #0000ff"><</span><span style="color: #800000">tr</span><span style="color: #0000ff">></span>
  21. <span style="color: #0000ff"><</span><span style="color: #800000">td</span><span style="color: #0000ff">></span>申请日期<span style="color: #0000ff"></</span><span style="color: #800000">td</span><span style="color: #0000ff">></span>
  22. <span style="color: #0000ff"><</span><span style="color: #800000">td</span><span style="color: #0000ff">></span>申请人<span style="color: #0000ff"></</span><span style="color: #800000">td</span><span style="color: #0000ff">></span>
  23. <span style="color: #0000ff"><</span><span style="color: #800000">td</span><span style="color: #0000ff">></span>采购员<span style="color: #0000ff"></</span><span style="color: #800000">td</span><span style="color: #0000ff">></span>
  24. <span style="color: #0000ff"><</span><span style="color: #800000">td</span><span style="color: #0000ff">></span>材料编号<span style="color: #0000ff"></</span><span style="color: #800000">td</span><span style="color: #0000ff">></span>
  25. <span style="color: #0000ff"><</span><span style="color: #800000">td</span><span style="color: #0000ff">></span>材料名称<span style="color: #0000ff"></</span><span style="color: #800000">td</span><span style="color: #0000ff">></span>
  26. <span style="color: #0000ff"><</span><span style="color: #800000">td</span><span style="color: #0000ff">></span>规格型号<span style="color: #0000ff"></</span><span style="color: #800000">td</span><span style="color: #0000ff">></span>
  27. <span style="color: #0000ff"><</span><span style="color: #800000">td</span><span style="color: #0000ff">></span>计量单位<span style="color: #0000ff"></</span><span style="color: #800000">td</span><span style="color: #0000ff">></span>
  28. <span style="color: #0000ff"><</span><span style="color: #800000">td</span><span style="color: #0000ff">></span>申请数量<span style="color: #0000ff"></</span><span style="color: #800000">td</span><span style="color: #0000ff">></span>
  29. <span style="color: #0000ff"><</span><span style="color: #800000">td</span><span style="color: #0000ff">></span>备注<span style="color: #0000ff"></</span><span style="color: #800000">td</span><span style="color: #0000ff">></span>
  30. <span style="color: #0000ff"><</span><span style="color: #800000">td</span><span style="color: #0000ff">></span>操作<span style="color: #0000ff"></</span><span style="color: #800000">td</span><span style="color: #0000ff">></span>
  31. <span style="color: #0000ff"></</span><span style="color: #800000">tr</span><span style="color: #0000ff">></span>
  32. <span style="color: #0000ff"><</span><span style="color: #800000">tr</span><span style="color: #0000ff">></span>
  33. <span style="color: #0000ff"><</span><span style="color: #800000">td</span><span style="color: #0000ff">></span>2017/8/7<span style="color: #0000ff"></</span><span style="color: #800000">td</span><span style="color: #0000ff">></span>
  34. <span style="color: #0000ff"><</span><span style="color: #800000">td</span><span style="color: #0000ff">></span>小贤<span style="color: #0000ff"></</span><span style="color: #800000">td</span><span style="color: #0000ff">></span>
  35. <span style="color: #0000ff"><</span><span style="color: #800000">td</span><span style="color: #0000ff">></span>张三<span style="color: #0000ff"></</span><span style="color: #800000">td</span><span style="color: #0000ff">></span>
  36. <span style="color: #0000ff"><</span><span style="color: #800000">td</span><span style="color: #0000ff">></span>1001<span style="color: #0000ff"></</span><span style="color: #800000">td</span><span style="color: #0000ff">></span>
  37. <span style="color: #0000ff"><</span><span style="color: #800000">td</span><span style="color: #0000ff">></span>玻璃<span style="color: #0000ff"></</span><span style="color: #800000">td</span><span style="color: #0000ff">></span>
  38. <span style="color: #0000ff"><</span><span style="color: #800000">td</span><span style="color: #0000ff">></span>100X100X3<span style="color: #0000ff"></</span><span style="color: #800000">td</span><span style="color: #0000ff">></span>
  39. <span style="color: #0000ff"><</span><span style="color: #800000">td</span><span style="color: #0000ff">></span>cm(厘米)<span style="color: #0000ff"></</span><span style="color: #800000">td</span><span style="color: #0000ff">></span>
  40. <span style="color: #0000ff"><</span><span style="color: #800000">td</span><span style="color: #0000ff">></span>1000<span style="color: #0000ff"></</span><span style="color: #800000">td</span><span style="color: #0000ff">></span>
  41. <span style="color: #0000ff"><</span><span style="color: #800000">td</span><span style="color: #0000ff">></span>无<span style="color: #0000ff"></</span><span style="color: #800000">td</span><span style="color: #0000ff">></span>
  42. <span style="color: #0000ff"><</span><span style="color: #800000">td</span><span style="color: #0000ff">><</span><span style="color: #800000">a </span><span style="color: #ff0000">class</span><span style="color: #0000ff">="delete"</span><span style="color: #ff0000"> href</span><span style="color: #0000ff">="#"</span><span style="color: #0000ff">><</span><span style="color: #800000">img </span><span style="color: #ff0000">src</span><span style="color: #0000ff">="../../images/delete_8e.png"</span><span style="color: #0000ff">></</span><span style="color: #800000">a</span><span style="color: #0000ff">></</span><span style="color: #800000">td</span><span style="color: #0000ff">></span>
  43. <span style="color: #0000ff"></</span><span style="color: #800000">tr</span><span style="color: #0000ff">></span>
  44. <span style="color: #0000ff"></</span><span style="color: #800000">table</span><span style="color: #0000ff">></span>
  45. <span style="color: #0000ff"><</span><span style="color: #800000">div </span><span style="color: #ff0000">class</span><span style="color: #0000ff">="paging"</span><span style="color: #0000ff">></span>
  46. <span style="color: #0000ff"><</span><span style="color: #800000">a </span><span style="color: #ff0000">class</span><span style="color: #0000ff">="button"</span><span style="color: #ff0000"> href</span><span style="color: #0000ff">=""</span><span style="color: #0000ff">></span>上一页<span style="color: #0000ff"></</span><span style="color: #800000">a</span><span style="color: #0000ff">></span>
  47. <span style="color: #0000ff"><</span><span style="color: #800000">a </span><span style="color: #ff0000">href</span><span style="color: #0000ff">="#"</span><span style="color: #0000ff">></span>1<span style="color: #0000ff"></</span><span style="color: #800000">a</span><span style="color: #0000ff">></span>
  48. <span style="color: #0000ff"><</span><span style="color: #800000">a </span><span style="color: #ff0000">href</span><span style="color: #0000ff">="#"</span><span style="color: #0000ff">></span>2<span style="color: #0000ff"></</span><span style="color: #800000">a</span><span style="color: #0000ff">></span>
  49. <span style="color: #0000ff"><</span><span style="color: #800000">a </span><span style="color: #ff0000">href</span><span style="color: #0000ff">="#"</span><span style="color: #0000ff">></span>3<span style="color: #0000ff"></</span><span style="color: #800000">a</span><span style="color: #0000ff">></span>
  50. <span style="color: #0000ff"><</span><span style="color: #800000">a </span><span style="color: #ff0000">href</span><span style="color: #0000ff">="#"</span><span style="color: #0000ff">></span>4<span style="color: #0000ff"></</span><span style="color: #800000">a</span><span style="color: #0000ff">></span>
  51. <span style="color: #0000ff"><</span><span style="color: #800000">a </span><span style="color: #ff0000">href</span><span style="color: #0000ff">="#"</span><span style="color: #0000ff">></span>5<span style="color: #0000ff"></</span><span style="color: #800000">a</span><span style="color: #0000ff">></span>
  52. <span style="color: #0000ff"><</span><span style="color: #800000">a </span><span style="color: #ff0000">class</span><span style="color: #0000ff">="button"</span><span style="color: #ff0000"> href</span><span style="color: #0000ff">=""</span><span style="color: #0000ff">></span>下一页<span style="color: #0000ff"></</span><span style="color: #800000">a</span><span style="color: #0000ff">></span>
  53. <span style="color: #0000ff"><</span><span style="color: #800000">p</span><span style="color: #0000ff">></span>共<span style="color: #0000ff"><</span><span style="color: #800000">span</span><span style="color: #0000ff">></span>5<span style="color: #0000ff"></</span><span style="color: #800000">span</span><span style="color: #0000ff">></span>页<span style="color: #ff0000">&nbsp;&nbsp;&nbsp;</span>到第<span style="color: #0000ff"></</span><span style="color: #800000">p</span><span style="color: #0000ff">></span>
  54. <span style="color: #0000ff"><</span><span style="color: #800000">input </span><span style="color: #ff0000">type</span><span style="color: #0000ff">="text"</span><span style="color: #ff0000"> name</span><span style="color: #0000ff">=""</span><span style="color: #0000ff">></span>
  55. <span style="color: #0000ff"><</span><span style="color: #800000">p </span><span style="color: #ff0000">style</span><span style="color: #0000ff">="margin:2px -1px 0 10px;"</span><span style="color: #0000ff">></span>页<span style="color: #0000ff"></</span><span style="color: #800000">p</span><span style="color: #0000ff">></span>
  56. <span style="color: #0000ff"><</span><span style="color: #800000">input </span><span style="color: #ff0000">class</span><span style="color: #0000ff">="button"</span><span style="color: #ff0000"> type</span><span style="color: #0000ff">="submit"</span><span style="color: #ff0000"> value</span><span style="color: #0000ff">="确认"</span><span style="color: #0000ff">></span>
  57. <span style="color: #0000ff"></</span><span style="color: #800000">div</span><span style="color: #0000ff">></span>

下章我们就实现页面数据的展示:

【知了堂学习笔记】JSP页面数据分页实现(一)——分页概念以及主流数据库的分页查询

标签:样式   strong   推荐   over   接下来   概念   搜索   dex   显示   

人气教程排行