/*
通用分页程序
*/
create or replace package mypage
as
type page_cur is ref cursor;
procedure getDataByPage(
p_tableName varchar2,--表名
p_fields varchar2,--字段
p_filter varchar2,--条件
p_sort varchar2,--排序
p_curpage number,--当前页码
p_pageSize number,--记录数
p_cursor out page_cur,--游标
p_totalRecords out number--总记录数
);
end mypage;
create or replace package body mypage as
procedure getDataByPage(
p_tableName varchar2,
p_fields varchar2,
p_filter varchar2,
p_sort varchar2,
p_curpage number,
p_pageSize number,
p_cursor out page_cur,
p_totalRecords out number
) as
v_sql varchar2(1000) := '';
w_Text varchar2(100);
o_Text varchar2(100);
begin
if p_filter is not null then
w_Text := 'where' || p_filter;
end if;
if p_sort is not null then
o_Text := 'order by' || p_sort;
end if;
v_sql := 'select * from
(
select rownum rm,'||p_fields||' from
(select * from '||p_tableName||w_Text||o_Text ||') T
where rownum <=:1
)
where rm >:2';
dbms_output.put_line(v_sql);
open p_cursor for v_sql using p_curpage*p_pageSize,p_pageSize*(p_curpage-1);
v_sql := 'select count(*) from '||p_tableName||w_Text||o_Text;
dbms_output.put_line(v_sql);
execute immediate v_sql into p_totalRecords;
end getDataByPage;
end;
在该分页程序中,我们可以设置查询的表名、字段名、条件、排序、当前页码、记录数,最后我们的查询结果会放在游标中,还可以返回总的记录条数。该笔记中,我们选择Employee表,查询该表中的姓名,年龄、职位信息。
(3)分页程序有了,现在我们应该在Eclipse中编写程序,以便能够获取到数据。
1)我们首先要有一个工程(Dynamic Web Project),本笔记中,我们的工程名为JSPZuoYe。
2)我们建立连接数据库的包名以及类名,本笔记中,我们使用daobases包,DBConnection类。在该类中,我们会进行Java与Oracle的连接。
具体的实现过程如下所示:
package daobases;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
*
* @author lenovo
* 目标:实现Java与Oracle的连接。
* 具体实现:我们首先创建一个getConnection()方法,通过该方法,我们可以连接Java与Oracle数据库。
* 其次,我们应该关闭连接,具体关闭Connection,ResultSet,CallableStatement。
*/
public class DBConnection {
//我们创建静态方法getConnection()方法获取连接,最后返回一个Connection对象
public static Connection getConnection(){
//创建Connection对象
Connection con = null;
try {
//加载驱动程序,抛出一个ClassNotFound异常
Class.forName("oracle.jdbc.driver.OracleDriver");
//建立Oracle与Java的连接,抛出SQLException异常
con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl","scott","scott");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
//返回Connection对象
return con;
}
/*
* 最后,我们不要忘了关闭连接,关闭连接有三个步骤:
* (1)关闭ResultSet;
* (2)关闭CallableStatement;
* (3)关闭Connection
* 我们创建静态方法close(ResultSet rs,CallableStatement call,Connection con)来关闭连接
*/
public static void close(ResultSet rs,CallableStatement call,Connection con){
//关闭rs
if(rs != null){
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//关闭call
if(call != null){
try {
call.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//关闭con
if(con != null){
try {
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
3)我们建立包dao和类 EmployeeDAO,在该类中,我们将会获取查询出来的员工信息以及记录的总条数。另外,别忘了创建实体类,我们将实体类创建在包为entity中,类名为Employee。
Employee具体代码如下:
package entity;
/**
*
* @author lenovo
*目标:建立Employee实体
*实现:为Employee实体添加id,name,age,position属性以及get、set方法
*/
public class Employee {
private int id;
private String name;
private int age;
private String position;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getPosition() {
return position;
}
public void setPosition(String position) {
this.position = position;
}
}
EmployeeDAO的具体实现如下:
package dao;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import oracle.jdbc.internal.OracleTypes;
import daobases.DBConnection;
import entity.Employee;
/**
*
* @author lenovo
*目标:获取员工信息及总记录数
*实现:首先创建一个getAllEmployee()的方法,用来获取员工的信息。
* 其次,创建一个getTotalRecords()的方法,用来获取总的记录条数
*/
public class EmployeeDAO {
/*
* 通过getAllEmployee()方法获取Employee列表。
* 参数为当前页数currentPage,每页记录数page_num
*
*/
public List<Employee> getAllEmployee(int currentPage,int page_num){
//创建Connection对象
Connection con = null;
//创建CallableStatement对象
CallableStatement call = null;
//创建ResultSet对象
ResultSet rs = null;
//创建Employee列表
List<Employee> list = new ArrayList<Employee>();
//获取连接
con = DBConnection.getConnection();
//建立sql语句
String sql = "{call mypage.getDataByPage(?,?,?,?,?,?,?,?)}";
try {
//获取call的值
call = con.prepareCall(sql);
//用call为存储过程赋值
//设置表名为employee
call.setString(1, " employee ");
//设置属性值为name,age,position
call.setString(2," name,age,pro,id ");
//设置条件为1=1
call.setString(3," 1=1 ");
//设置排序方式为按id排序
call.setString(4," id ");
//设置当前页码为currentPage
call.setInt(5,currentPage);
//设置每页的记录数为page_num
call.setInt(6,page_num);
//设置游标输出
call.registerOutParameter(7, OracleTypes.CURSOR);
//设置输出总记录数
call.registerOutParameter(8, OracleTypes.NUMBER);
//执行存储过程查询数据
call.execute();
//获取表中数据,即Employee信息
rs = (ResultSet)call.getObject(7);
//循环获取每一行记录,并将每一行的记录放入Employee对象中。
while(rs.next()){
//创建Employee对象,并为该对象赋值。
Employee e = new Employee();
e.setId(rs.getInt("id"));
e.setName(rs.getString("name"));
e.setAge(rs.getInt("age"));
e.setPosition(rs.getString("pro"));
//将该对象添加到list列表中
list.add(e);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
//最后不要忘记关闭连接
DBConnection.close(rs, call, con);
}
//返回Employee列表
return list;
}
/*
* 通过getTotalRecords()方法获取总的记录数。
* 参数为当前页数currentPage,每页记录数page_num
*/
public int getTotalRecords(int currentPage,int page_num){
//定义总记录数
int total = 0;
//创建Connection对象
Connection con = null;
//创建CallableStatement对象
CallableStatement call = null;
//获取连接
con = DBConnection.getConnection();
//建立sql语句,存储过程的sql语句一定要写对,{call 包头名.包体名(参数占位符)}
String sql = "{call mypage.getDataByPage(?,?,?,?,?,?,?,?)}";
try {
//获取call的值
call = con.prepareCall(sql);
//用call为存储过程赋值
//赋值时被忘了参数两端的空格,根据存储过程的不同,传参方式可能不同
//设置表名为employee
call.setString(1, " employee ");
//设置属性值为name,age,position
call.setString(2," name,id ");
//设置条件为1=1
call.setString(3," 1=1 ");
//设置排序方式为按id排序
call.setString(4," id ");
//设置当前页码为currentPage
call.setInt(5, currentPage);
//设置每页的记录数为page_num
call.setInt(6,page_num);
//设置游标输出
call.registerOutParameter(7, OracleTypes.CURSOR);
//设置输出总记录数
call.registerOutParameter(8, OracleTypes.NUMBER);
//执行存储过程查询数据
call.execute();
//获取总记录数
total = call.getInt(8);
}catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
//最后不要忘记关闭连接
DBConnection.close(null, call, con);
}
return total;
}
}
4)现在,我们数据也可以获取出来了,接下来我们要找出参数,参数从哪处呢?我们可以先创建一个servelt,我们本笔记中用的是servelt包下的FenYeServelt。
FenYeServelt的具体实现如下:
package servlet;
import java.io.IOException;
import java.util.List;
import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import dao.EmployeeDAO;
import entity.Employee;
/**
* Servlet implementation class FenYeServlet
* 通过FenYeServlet获取数据的值
*/
public class FenYeServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public FenYeServlet() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see Servlet#init(ServletConfig)
*/
public void init(ServletConfig config) throws ServletException {
// TODO Auto-generated method stub
}
/**
* @see Servlet#destroy()
*/
public void destroy() {
// TODO Auto-generated method stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
doPost(request,response);
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
//设置编码方式为UTF-8
request.setCharacterEncoding("UTF-8");
response.setCharacterEncoding("UTF-8");
//当前页码的设置
int currentPage = 0;
//获取当前页码的值
String page = request.getParameter("page");
if(page == null){
currentPage = 1;
}else{
try{
currentPage = Integer.valueOf(page);
}catch(NumberFormatException e){
currentPage = 1;
}
}
//设置每页的记录数为10
int page_num = 10;
//获取员工列表
EmployeeDAO e = new EmployeeDAO();
List<Employee> list = e.getAllEmployee(currentPage, page_num);
//将该list对象添加到request,并指定key值为"list"
request.setAttribute("list", list);
//获取总的记录数
int total = e.getTotalRecords(currentPage, page_num);
int totalPage = (int)Math.ceil(total/page_num);
//将该total添加到session,并指定key值为total
HttpSession session = request.getSession();
session.setAttribute("total", totalPage);
//将获取到的值转发至fenye.jsp页面,用来显示获取到的值
request.getRequestDispatcher("fenye.jsp?page="+currentPage).forward(request,response);
}
}
通过FenYeServlet,我们获取到了list(员工列表),totalPage(总页数)的值。
5)我们要将我们查到的数据通过JSP页面显示出来,本笔记我们采用fenye.jsp页面
具体实现如下:
<%@ page language="java"contentType="text/html; charset=UTF-8" import = "java.util.*,entity.*"
pageEncoding="UTF-8"%>
<!-- 设置编码方式为UTF-8 -->
<%
String path = request.getContextPath();
String basePath = request.getScheme() + "://" + request.getServerName() +
":" + request.getServerPort() + path + "/";
%>
<!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>fenye</title>
</head>
<body>
<%
//设置FenYeServlet的URL
String p = basePath + "FenYeServlet?page=";
%>
<%--获取当前页面的页码 --%>
<%
int currentPage = 0;
String pages = request.getParameter("page");
if(page == null){
currentPage = 1;
}else{
try{
currentPage = Integer.parseInt(pages);
}catch(NumberFormatException e){
currentPage = 1;
}
}
%>
<!-- 创建表格,显示数据 -->
<table border = "1"align = "center">
<tr>
<th>员工姓名</th>
<th>员工年龄</th>
<th>员工职位</th>
</tr>
<%
//查询数据
List<Employee> list = (List<Employee>)request.getAttribute("list");
//循环输出数据
for(int i = 0; i < list.size(); i++){
Employee em = list.get(i);
String name = em.getName();
int age = em.getAge();
String position = em.getPosition();
%>
<tr>
<td><%=name %></td>
<td><%=age %></td>
<td><%=position %></td>
</tr>
<%} %>
</table>
<br/>
<%
//获取总页数
Integer totalPage = (Integer)session.getAttribute("total");
%>
<%
//判断当前页面的页数,显示链接
if(currentPage < 1) {
currentPage = 1;
}
if(currentPage != 1){
out.println("<a href = 'FenYeServlet?page="+(currentPage-1)+"'>上一页</a>");
out.println("<a href = 'FenYeServlet?page="+totalPage+"'>最后一页</a>");
}
if(currentPage != totalPage){
out.println("<a href = 'FenYeServlet?page="+(currentPage+1)+"'>下一页</a>");
out.println("<a href = 'FenYeServlet?page="+1+"'>第一页</a>");
}
%>
</body>
</html>
这样,我们就可以实现分页显示数据的功能了。
注意,当我们第一次访问页面时,我们应该访问:http://127.0.0.1:8080/JSPZuoYe/FenYeServlet?page=1,因为如果我们直接访问JSP页面,会因为访问不到值而报错。另外,别忘了Java与Oracle连接时要导jar包。