/*
通用分页程序
*/
select * from employee ;
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;
2.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.用Java查询数据
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
*目标:获取组合查询的员工信息及总页数
*实现:首先创建一个combineGetEmplyee(String s,int currentPage,int page_num)的方法,用来获取员工的信息。
* 其次,创建一个combineGetTotalPage(String s,int currentPage,int page_num)的方法,用来获取页数
*/
public class EmployeeDAO {
/*
* 创建方法combineGetEmplyee(String s,int currentPage,int page_num),返回值为List<Employee>
* 通过该方法,我们可以获取到查询出来的数据。
*/
public List<Employee> CombineGetEmployee(String s,int currentPage,int page_num){
//创建员工列表
List<Employee> list = new ArrayList<Employee>();
//创建Connection
Connection con = null;
//创建CallableStatemnt
CallableStatement call = null;
//创建ResultSet
ResultSet rs = null;
//构造查询的sql语句
String sql = "{call mypage.getDataByPage(?,?,?,?,?,?,?,?)}";
//建立连接
con = DBConnection.getConnection();
//发送sql语句
try {
call = con.prepareCall(sql);
//为sql语句占位符赋值
//设置表名
call.setString(1, " employee ");
//设置查询内容
call.setString(2," name,age,pro,id");
//设置查询条件
call.setString(3, s);
//设置排序方式
call.setString(4," id ");
//设置当前页数
call.setInt(5, currentPage);
//设置每页记录数
call.setInt(6,page_num);
//设置输出游标
call.registerOutParameter(7, OracleTypes.CURSOR);
//设置输出总记录数
call.registerOutParameter(8,OracleTypes.NUMBER);
//执行sql语句
call.execute();
//获取游标的值
rs = (ResultSet)call.getObject(7);
//循环获取员工信息,并将它们放到list列表中
while(rs.next()){
Employee em = new Employee();
em.setId(rs.getInt("id"));
em.setName(rs.getString("name"));
em.setAge(rs.getInt("age"));
em.setPosition(rs.getString("pro"));
list.add(em);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
//最后不要忘记关闭连接
DBConnection.close(rs, call, con);
}
//返回员工列表
return list;
}
/*
* 创建combineGetTotalPage(String s,int currentPage,int page_num)方法获取
* 记录的总页数。
*/
public int combineGetTotalPage(String s,int currentPage,int page_num){
//定义总页数totalPage
int totalPage = 0;
//创建Connection
Connection con = null;
//创建CallableStatemnt
CallableStatement call = null;
//构造查询的sql语句
String sql = "{call mypage.getDataByPage(?,?,?,?,?,?,?,?)}";
//建立连接
con = DBConnection.getConnection();
//发送sql语句
try {
call = con.prepareCall(sql);
//为sql语句占位符赋值
//设置表名
call.setString(1, " employee ");
//设置查询内容
call.setString(2," name,age,pro,id");
//设置查询条件
call.setString(3, s);
//设置排序方式
call.setString(4," id ");
//设置当前页数
call.setInt(5, currentPage);
//设置每页记录数
call.setInt(6,page_num);
//设置输出游标
call.registerOutParameter(7, OracleTypes.CURSOR);
//设置输出总记录数
call.registerOutParameter(8,OracleTypes.NUMBER);
//执行sql语句
call.execute();
//获取总的记录数
int total = call.getInt(8);
System.out.println(total);
//获取总页数
if(total%page_num != 0){
totalPage = total/page_num+1;
}else{
totalPage = total/page_num;
}
}catch(SQLException e){
e.printStackTrace();
}finally{
//最后不要忘记关闭连接
DBConnection.close(null, call, con);
}
//返回总页数totalPage
return totalPage;
}
}
4.JSP页面输入查询条件
<%@ page language="java"contentType="text/html; charset=utf-8"
pageEncoding="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>Insert title here</title>
</head>
<body>
<%
//获取CombineSelectServlet的路径
String p = basePath + "CombineSelectServlet?pages="+"";
%>
<!-- 创建form表单,表单中显示我们可能会查询的条件 -->
<form action = "<%=p%>"method = "post" name = "form1">
员工姓名:<input type = "text"name = "employee_name" value = ""/>
员工年龄:<input type = "text"name = "employee_age" value = ""/>
员工职位:<input type = "text"name = "employee_position" value = ""/>
<input type = "submit"name = "submit" value = "提交"/>
</form>
</body>
</html>
5.从combineSelect.jsp提交上来的数据,我们应该传入到servle中(CombineSelectServlet)
6.在CombineSelectServlet中,我们可以接收jsp传进来的参数,再将参数导入到EmployeeDAO中用来查询数据
package servlet;
import java.io.IOException;
import java.util.List;
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 biz.EmployeeBiz;
import dao.EmployeeDAO;
import entity.Employee;
/**
* Servlet implementation class CombineSelectServlet
*/
public class CombineSelectServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
private String s;
private int currentPage;
/**
* @see HttpServlet#HttpServlet()
*/
public CombineSelectServlet() {
super();
// TODO Auto-generated constructor 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");
//当前页码的设置
currentPage = 0;
//获取当前页码的值
String page = request.getParameter("pages");
if(page == ""){
//获取JSP页面上的查询条件,并将它们封装在Employee对象中。
Employee em = new Employee();
//判断年龄的输入是否合法?
int age = 0;
String age_str = request.getParameter("employee_age");
if(age_str == null){
age = 0;
}else{
try{
age = Integer.parseInt(age_str);
}catch(NumberFormatException e){
age = 0;
}
}
em.setAge(age);
System.out.println(request.getParameter("employee_name"));
em.setName(request.getParameter("employee_name"));
em.setPosition(request.getParameter("employee_position"));
//获取sql语句的查询条件s
s = EmployeeBiz.sqlStatement(em);
currentPage = 1;
}else if(page == null){
currentPage = 1;
}else{
try{
currentPage = Integer.valueOf(page);
}catch(NumberFormatException e){
currentPage = 1;
}
}
getEmployee(request,response);
}
public void getEmployee(HttpServletRequest request,HttpServletResponse response){
//设置每页的记录数为5
int page_num = 3;
//获取员工列表
EmployeeDAO e = new EmployeeDAO();
List<Employee> list = e.CombineGetEmployee(s, currentPage, page_num);
//将该list对象添加到request,并指定key值为"list"
request.setAttribute("list2", list);
//获取总的页数
int totalPage = e.combineGetTotalPage(s, currentPage, page_num);
//将该totalPage添加到session,并指定key值为total
HttpSession session = request.getSession();
session.setAttribute("total2", totalPage);
//将获取到的值转发至fenye.jsp页面,用来显示获取到的值
try {
request.getRequestDispatcher("combineFenYe.jsp?pages="+currentPage).forward(request,response);
} catch (ServletException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
} catch (IOException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}
}
<%@ 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>combineFenYe</title>
</head>
<body>
<%
//设置FenYeServlet的URL
String p = basePath + "CombineSelectServlet?page=";
%>
<%--获取当前页面的页码 --%>
<%
int currentPage = 0;
String pages = request.getParameter("pages");
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("list2");
//循环输出数据
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("total2");
%>
<%
//判断当前页面的页数,显示链接
if(currentPage < 1) {
currentPage = 1;
}
if(currentPage != 1){
out.println("<a href = 'CombineSelectServlet?pages="+(currentPage-1)+"'>上一页</a>");
out.println("<a href = 'CombineSelectServlet?pages="+totalPage+"'>最后一页</a>");
}
if(currentPage != totalPage){
out.println("<a href = 'CombineSelectServlet?pages="+(currentPage+1)+"'>下一页</a>");
out.println("<a href = 'CombineSelectServlet?pages="+1+"'>第一页</a>");
}
%>
</body>
</html>
8.在网页上,我们可以点击链接分页查询数据
9.工具类isEmptyTool(),判断字符串是否为空。
package tool;
/**
*
* @author lenovo
*目标:判断字符串是否为空
*实现:要判断一个字符串是否为空,我们应该判断它是否为null或者判断字符串是否是"",
* 若是,则为空,否则为非空。
*/
public class isEmptyTool {
@SuppressWarnings("null")
public static boolean isEmpty(String str){
if(str == null || str.equals("")){
return true;
}else{
return false;
}
}
}
10.EmployeeBiz来拼凑我们要查询的sql语句中的条件。
package biz;
import tool.isEmptyTool;
import entity.Employee;
/**
*
* @author lenovo
*目标:获取组合查询所需要的条件语句
*实现:我们可以获取JSp页面上输入的,然后构成条件语句。
*/
public class EmployeeBiz {
public static String sqlStatement(Employee em){
StringBuffer sql = new StringBuffer(" 1=1 ");
String name = em.getName();
int age = em.getAge();
String position = em.getPosition();
if(!isEmptyTool.isEmpty(name)){
sql.append(" and name = " + "'" + name + "'");
}
if(age != 0 ){
sql.append(" and age = " + age);
}
if(!isEmptyTool.isEmpty(position)){
sql.append(" and pro = " + "'" + position + "'");
}
String s = String.valueOf(sql);
System.out.println(s);
return s;
}
}
11.别忘了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;
}
}
这样,我们就可以成功地通过组合条件来查询我们想要的数据了。