1 <%@ page import="java.sql.*" %>
2 <%--
3 Created by IntelliJ IDEA.
4 User: lx_sunwei
5 Date: 14-1-4
6 Time: 下午5:45
7 To change this template use File | Settings | File Templates.
8 --%>
9 <%@ page contentType="text/html;charset=UTF-8" language="java" %>
10 <%
11 int maxPage = 0 //一共有多少页
12 ,maxRowCount //一共有多少行
13 ,rowsPerPage //每页显示多少行
14 ,curPage; //当前页页码
15 Connection conn = null; //数据库连接对象
16 Statement st = null; //SQL语句对象
17 ResultSet rs = null; //结果集对象
18
19 rowsPerPage = 5; //设置每页显示5行
20
21 String curpage1 = request.getParameter("page"); //取得当前页页码
22 if (curpage1 == null){ //若没有page参数
23 curPage = 1;
24 }else {
25 curPage = Integer.parseInt(curpage1); //字符串转换为整数
26 if (curPage < 1)
27 curPage = 1;
28 }
29
30 String url = "jdbc:oracle:thin:@localhost:1521:orcl"; //连接字符串
31 try {
32 Class.forName("oracle.jdbc.driver.OracleDriver"); //装载驱动
33 } catch (ClassNotFoundException e) {
34 e.printStackTrace();
35 }
36 try {
37 conn = DriverManager.getConnection(url,"scott","tiger"); //创建连接对象
38 st = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY); //设置结果集
39 String sql = "select * from emp"; //SQL语句
40 rs = st.executeQuery(sql); //执行查询
41
42 rs.last(); //移动光标到结果集最后一行
43 maxRowCount = rs.getRow(); //返回当前行的行号 即总共的行数
44 maxPage = (maxRowCount + rowsPerPage - 1) / rowsPerPage; //总页数
45 if (curPage > maxPage)
46 curPage = maxPage;
47 } catch (SQLException e) {
48 e.printStackTrace();
49 }
50 %>
51 <html>
52 <head>
53 <meta http-equiv="content-type" content="text/html" charset="utf-8">
54 <title>JSP分页</title>
55 <link rel="stylesheet" type="text/css" href="css.css">
56 </head>
57 <body>
58 <div style="margin-left: 30%;margin-top: 10%">
59 <table>
60 <caption>SCOTT用户，EMP表中的数据</caption>
61 <tr> <!--表头-->
62 <th>EMPNO</th>
63 <th>ENAME</th>
64 <th>JOB</th>
65 <th>MGR</th>
66 <th>HIREDATE</th>
67 <th>SAL</th>
68 <th>COMM</th>
69 <th>DEPTNO</th>
70 </tr>
71 <%
72 if (maxPage > 0) {
73 try {
74 rs.absolute((curPage - 1) * rowsPerPage + 1); //移动光标到当前页的第一行
75 int i = 0;
76 while (i < rowsPerPage && !rs.isAfterLast()){ //循环输出当前的数据
77 %>
78 <tr>
79 <td><%= rs.getBigDecimal("EMPNO") %></td>
80 <td><%= rs.getString("ENAME") %></td>
81 <td><%= rs.getString("job") %></td>
82 <td><%= rs.getBigDecimal("MGR") %></td>
83 <td><%= rs.getDate("HIREDATE") %></td>
84 <td><%= rs.getBigDecimal("SAL") %></td>
85 <td><%= rs.getBigDecimal("COMM") %></td>
86 <td><%= rs.getBigDecimal("DEPTNO")%></td>
87 </tr>
88 <%if (rs.next()){
89 i++;
90 }
91 }
92 %>
93 <%
94 rs.close();
95 st.close();
96 conn.close();
97
98 } catch (SQLException e) {
99 e.printStackTrace();
100 }
101 }
102 %>
103 </table>
104 </div>
105 <div style="text-align: center; margin-top: 10%" >
106 第<%=curPage%>页，共<%=maxPage%>页
107 <%--第一页不能向前翻，最后一页不能向后翻。点击首页、尾页跳至第一页、最后一页--%>
108 <%if (curPage > 1)
109 {%>
110 <a href="pagemain.jsp?page=1">首页</a>
111 <a href="pagemain.jsp?page=<%=curPage - 1%>">上一页</a>
112 <%}else {%>
113 首页 上一页 <!--不能被触发-->
114 <%}%>
115
116 <%if (curPage < maxPage){%>
117 <a href="pagemain.jsp?page=<%=curPage + 1 %>">下一页</a>
118 <a href="pagemain.jsp?page=<%= maxPage %>">尾页</a>
119 <%}else {%>
120 下一页 尾页 <!--不能被触发-->
121 <%}%> 转至第 <form name="form1" action="pagemain.jsp" method="get">
122 <label>
123 <select name="page" onchange="document.form1.submit()">
124 <%
125 for (int j = 1; j <= maxPage; j++) {
126 if (j == curPage) {
127 %>
128 <option selected value=<%=j //当前页页码默认选中
129 %>><%= j %></option>
130 <%} else {%>
131 <option value=<%= j
132 %>><%= j%></option>
133 <%
134 }
135 }
136 %>
137 </select> 页
138 </label>
139 </form>
140 </div>
141 </body>
142 </html>
JSP 页面实现数据分页