Java分页小结

最新推荐文章于 2021-07-22 11:43:14 发布

wl_ldy

最新推荐文章于 2021-07-22 11:43:14 发布

阅读量2k

点赞数

分类专栏： Java 文章标签： java sqlserver stylesheet import string sql

本文链接：https://blog.csdn.net/wl_ldy/article/details/5920099

版权

Java 专栏收录该内容

33 篇文章 0 订阅

订阅专栏

所有的分页效果和下面的截图类似 ：

一：MySql的分页

mysql的分页比较简单，只需要limit offset,length就可以获取数据了，是从第offset条记录开始查找length条记录。

1）首先在MySql中创建了user表

create table user (id int primary key auto_increment, username varchar(50), password varchar(50) )

2)与数据库连接的操作类：

public class DB { public static Connection getMySQLConnection(){ Connection conn=null; String url="jdbc:mysql://localhost:3306/test"; try { Class.forName("com.mysql.jdbc.Driver"); try { conn=DriverManager.getConnection(url,"root","admin"); } catch (SQLException e) { e.printStackTrace(); } } catch (ClassNotFoundException e) { e.printStackTrace(); } return conn; } }

3)前台JSP页面的代码效果如下：

<%@ page language="java" import="java.util.*" pageEncoding="GB18030"%> <%@page import="java.sql.*" %> <%@page import="Page.*" %> <% String path = request.getContextPath(); String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/"; Connection conn=DB.getMySQLConnection(); String sql="select count(*) from user"; PreparedStatement pstmt=conn.prepareStatement(sql); ResultSet rs=pstmt.executeQuery(); rs.next(); //总的记录数目 int RecordCount=rs.getInt(1); //每页显示的记录数 int PageSize=2; //显示的最大页码 int MaxPage=RecordCount%PageSize==0?RecordCount/PageSize:(RecordCount/PageSize+1); //需要显示的页数，默认是第一页 int PageNo=1; if(request.getParameter("pageno")!=null){ PageNo=Integer.parseInt(request.getParameter("pageno")); } //每页开始显示记录的编号 int CounterStart=0; //每页最后显示记录的编号 int CounterEnd=0; //每页能够按照PageSize的大小进行显示 if(PageNo*PageSize<=RecordCount){ CounterStart=PageSize*(PageNo-1); CounterEnd=PageSize; }else{//当前页显示的记录数目小于PageSize的大小 CounterStart=PageSize*(PageNo-1); CounterEnd=(RecordCount-PageSize*(PageNo-1)); } sql="select * from user limit "+CounterStart+","+CounterEnd; pstmt=conn.prepareStatement(sql); rs=pstmt.executeQuery(); %> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head> <base href="<%=basePath%>"> <title>My JSP 'index.jsp' starting page</title> <meta http-equiv="pragma" content="no-cache"> <meta http-equiv="cache-control" content="no-cache"> <meta http-equiv="expires" content="0"> <meta http-equiv="keywords" content="keyword1,keyword2,keyword3"> <meta http-equiv="description" content="This is my page">  <mce:script type="text/javascript"></mce:script> </head> <body> <table> <tr> <th>ID号：</th> <th>Username：</th> <th>Password：</th> </tr> <% while(rs.next()){ %> <tr> <td><%=rs.getInt("id") %></td> <td><%=rs.getString("username") %></td> <td><%=rs.getString("password") %></td> </tr> <% } %> </table> <a href="Page.jsp?pageno=1" mce_href="Page.jsp?pageno=1">首页</a> <% if(PageNo==1){//当是第一页的时候“上一页”的连接可以隐藏 %>  <% }else{ %> <a href="Page.jsp?pageno=<%=PageNo-1%>">上一页</a> <% } %> 跳转到第 <select οnchange="skip(this.value)"> <% for(int i=1;i<=MaxPage;i++){ if(i==PageNo){ %> <option selected="selected" value="<%=i%>"><%=i%></option> <% }else{ %> <option value="<%=i%>"><%=i%></option> <% } } %> </select> 页 <% if(PageNo==MaxPage){//当是最后一页的时候“下一页”的连接可以隐藏 %>  <% }else{ %> <a href="Page.jsp?pageno=<%=PageNo+1%>">下一页</a> <% } %> <a href="Page.jsp?pageno=<%=MaxPage%>">尾页</a> </body> </html>

二、SQLServer的分页：

1、SQLServer的分页方案一：(利用Not In和SELECT TOP分页)

语句形式： SELECT TOP 10 * FROM TestTable WHERE (ID NOT IN (SELECT TOP 20 id FROM TestTable ORDER BY id)) ORDER BY ID SELECT TOP 页大小 * FROM TestTable WHERE (ID NOT IN (SELECT TOP 页大小*页数 id FROM 表 ORDER BY id)) ORDER BY ID

2、分页方案二：(利用ID大于多少和SELECT TOP分页）

语句形式： SELECT TOP 10 * FROM TestTable WHERE (ID > (SELECT MAX(id) FROM (SELECT TOP 20 id FROM TestTable ORDER BY id) AS T)) ORDER BY ID SELECT TOP 页大小 * FROM TestTable WHERE (ID > (SELECT MAX(id) FROM (SELECT TOP 页大小*页数 id FROM 表 ORDER BY id) AS T)) ORDER BY ID

3、使用方案一进行的分页：

1）操作数据库的MSSQLServerDB类：

public class MSSQLServerDB { public static Connection getMSSQLServerConnection(){ Connection conn=null; String url="jdbc:sqlserver://localhost:1433;DatabaseName=Page;user=sa;password=123456"; try { Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); try { conn=DriverManager.getConnection(url); } catch (SQLException e) { e.printStackTrace(); } } catch (ClassNotFoundException e) { e.printStackTrace(); } return conn; } }

2）JSP中的代码效果如下：

<%@ page language="java" import="java.util.*" pageEncoding="GB18030"%> <%@page import="java.sql.*" %> <%@page import="Page.*" %> <% String path = request.getContextPath(); String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/"; Connection conn=MSSQLServerDB.getMSSQLServerConnection(); String sql="select count(*) from t_user"; PreparedStatement pstmt=conn.prepareStatement(sql); ResultSet rs=pstmt.executeQuery(); rs.next(); //总的记录数目 int RecordCount=rs.getInt(1); //每页显示的记录数 int PageSize=2; //显示的最大页码 int MaxPage=RecordCount%PageSize==0?RecordCount/PageSize:(RecordCount/PageSize+1); //需要显示的页数，默认是第一页 int PageNo=1; if(request.getParameter("pageno")!=null){ PageNo=Integer.parseInt(request.getParameter("pageno")); } sql="select top "+PageSize+" * from t_user where id not in (select top "+PageSize*(PageNo-1)+" id from t_user "+ "order by id) order by id"; pstmt=conn.prepareStatement(sql); rs=pstmt.executeQuery(); %> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head> <base href="<%=basePath%>"> <title>My JSP 'index.jsp' starting page</title> <meta http-equiv="pragma" content="no-cache"> <meta http-equiv="cache-control" content="no-cache"> <meta http-equiv="expires" content="0"> <meta http-equiv="keywords" content="keyword1,keyword2,keyword3"> <meta http-equiv="description" content="This is my page">  <mce:script type="text/javascript"></mce:script> </head> <body> <table> <tr> <th>ID号：</th> <th>Username：</th> <th>Password：</th> </tr> <% while(rs.next()){ %> <tr> <td><%=rs.getInt("id") %></td> <td><%=rs.getString("username") %></td> <td><%=rs.getString("password") %></td> </tr> <% } %> </table> <a href="SqlServerPage.jsp?pageno=1" mce_href="SqlServerPage.jsp?pageno=1">首页</a> <% if(PageNo==1){//当是第一页的时候“上一页”的连接可以隐藏 %>  <% }else{ %> <a href="SqlServerPage.jsp?pageno=<%=PageNo-1%>">上一页</a> <% } %> 跳转到第 <select οnchange="skip(this.value)"> <% for(int i=1;i<=MaxPage;i++){ if(i==PageNo){ %> <option selected="selected" value="<%=i%>"><%=i%></option> <% }else{ %> <option value="<%=i%>"><%=i%></option> <% } } %> </select> 页 <% if(PageNo==MaxPage){//当是最后一页的时候“下一页”的连接可以隐藏 %>  <% }else{ %> <a href="SqlServerPage.jsp?pageno=<%=PageNo+1%>">下一页</a> <% } %> <a href="SqlServerPage.jsp?pageno=<%=MaxPage%>">尾页</a> </body> </html>

4、使用方案二进行的分页：

只是将方案一中的JSP中的sql语句：

sql="select top "+PageSize+" * from t_user where id not in (select top "+PageSize*(PageNo-1)+" id from t_user "+ "order by id) order by id";

改为下面的sql语句即可：

sql="select top "+PageSize+" * from t_user where id >(select ISNULL(max(id),0) from (select top "+PageSize*(PageNo-1)+" id from t_user "+ "order by id)t) order by id";

三、Oracle的分页：

Oracle主要使用rownum进行分页的。

1、方案一：

Select ename,sal from ( Select ename,sal,rownum r from (select ename,sal from emp order by sal desc) Where rownum<=10 ) Where r>=6

2、方案二：

Select ename,sal from ( Select ename,sal,rownum r from (select ename,sal from emp order by sal desc) ) Where r>=6 and r<=10

其中第一种方案效率相对会高些。

3、使用方案一进行分页：

1）在Oracle中创建数据库表t_user

create table t_user ( id number(6), username varchar2(20), password varchar2(20), constraint user_id_pk primary key(id) )

2）连接数据库的操作类OracleDB：

public class OracleDB { public static Connection getOracleConnection(){ Connection conn=null; String url="jdbc:oracle:thin:@192.168.18.41:1521:XE"; try { Class.forName("oracle.jdbc.driver.OracleDriver"); try { conn=DriverManager.getConnection(url,"hr","hr"); } catch (SQLException e) { e.printStackTrace(); } } catch (ClassNotFoundException e) { e.printStackTrace(); } return conn; } }

其中的：String url="jdbc:oracle:thin:@192.168.18.41:1521:XE";

192.168.18.41是ip地址（若将ip地址改为localhsot会失败），

1521是Oracle的端口号，XE是Oracle的SID。

3）JSP的代码效果如下：

<%@ page language="java" import="java.util.*" pageEncoding="GB18030"%> <%@page import="java.sql.*" %> <%@page import="Page.*" %> <% String path = request.getContextPath(); String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/"; Connection conn=OracleDB.getOracleConnection(); String sql="select count(*) from t_user"; PreparedStatement pstmt=conn.prepareStatement(sql); ResultSet rs=pstmt.executeQuery(); rs.next(); //总的记录数目 int RecordCount=rs.getInt(1); //每页显示的记录数 int PageSize=2; //显示的最大页码 int MaxPage=RecordCount%PageSize==0?RecordCount/PageSize:(RecordCount/PageSize+1); //需要显示的页数，默认是第一页 int PageNo=1; if(request.getParameter("pageno")!=null){ PageNo=Integer.parseInt(request.getParameter("pageno")); } sql="select id,username,password from (select id,username,password,rownum r from (select * from t_user)"+ " where rownum<="+PageNo*PageSize+") where r>="+((PageNo-1)*PageSize+1); pstmt=conn.prepareStatement(sql); rs=pstmt.executeQuery(); %> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head> <base href="<%=basePath%>"> <title>My JSP 'index.jsp' starting page</title> <meta http-equiv="pragma" content="no-cache"> <meta http-equiv="cache-control" content="no-cache"> <meta http-equiv="expires" content="0"> <meta http-equiv="keywords" content="keyword1,keyword2,keyword3"> <meta http-equiv="description" content="This is my page">  <mce:script type="text/javascript"></mce:script> </head> <body> <table> <tr> <th>ID号：</th> <th>Username：</th> <th>Password：</th> </tr> <% while(rs.next()){ %> <tr> <td><%=rs.getInt("id") %></td> <td><%=rs.getString("username") %></td> <td><%=rs.getString("password") %></td> </tr> <% } %> </table> <a href="OraclePage.jsp?pageno=1" mce_href="OraclePage.jsp?pageno=1">首页</a> <% if(PageNo==1){//当是第一页的时候“上一页”的连接可以隐藏 %>  <% }else{ %> <a href="OraclePage.jsp?pageno=<%=PageNo-1%>">上一页</a> <% } %> 跳转到第 <select οnchange="skip(this.value)"> <% for(int i=1;i<=MaxPage;i++){ if(i==PageNo){ %> <option selected="selected" value="<%=i%>"><%=i%></option> <% }else{ %> <option value="<%=i%>"><%=i%></option> <% } } %> </select> 页 <% if(PageNo==MaxPage){//当是最后一页的时候“下一页”的连接可以隐藏 %>  <% }else{ %> <a href="OraclePage.jsp?pageno=<%=PageNo+1%>">下一页</a> <% } %> <a href="OraclePage.jsp?pageno=<%=MaxPage%>">尾页</a> </body> </html>