<%@ 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>
<%
//定义表单即将要提交的servlet的地址
String p = basePath + "AddEmployeeServlet";
%>
<!-- 定义要输入的员工信息 -->
<form action = "<%=p%>"method = "post" name= "form3">
员工姓名：<input type = "text"name = "name" value= ""/>
员工年龄：<input type = "text"name = "age" value= ""/>
员工职位：<input type = "text"name = "position" value= ""/>
员工部门： <input type = "text"name = "dept" value= ""/>
<input type = "submit"name = "submit" value= "提交"/>
</form>
</body>
</html>
<2>AddEmployeeServlet.java
package servlet;
import java.io.IOException;
import java.io.PrintWriter;
import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import dao.EmployeeDAO;
import entity.Employee;
/**
* Servlet implementation class AddEmployeeServlet
* 目标：实现员工信息的值的获取，并将值传递至EmployeeDAO中的addEmployee()方法中，以便能够将数据插入到数据库。
* 实现：我们通过request.getParameter()获取员工信息的值，并将之封装在一个Employee对象中，
* 然后通过EmployeeDAO中的addEmployee()方法将这些值传递给Oracle数据库。
*/
public class AddEmployeeServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public AddEmployeeServlet() {
super();
}
/**
* @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
//设置编码方式
request.setCharacterEncoding("UTF-8");
response.setCharacterEncoding("gb2312");
//获取Employee的信息值
String name = request.getParameter("name");
String age = request.getParameter("age");
String position = request.getParameter("position");
String dept = request.getParameter("dept");
//创建Employee对象，并将值传递给该对象。
Employee em = new Employee();
em.setName(name);
em.setAge(Integer.parseInt(age));
em.setDept(dept);
em.setPosition(position);
//将封装出来的employee对象传递给EmployeeDAO中的addEmployee()方法
EmployeeDAO e = new EmployeeDAO();
//获取rs的值
String rs = e.addEmployee(em);
System.out.println(rs);
//创建out对象
PrintWriter out = response.getWriter();
//判断是否添加成功并作出反应。
if(rs.equals("插入成功")){
out.println("<script>alert('插入成功！')</script>");
request.getRequestDispatcher("ok.jsp").forward(request, response);
}else{
out.println("<script>alert('插入失败！')</script>");
request.getRequestDispatcher("fail.jsp").forward(request, response);
}
}
}
<3>EmployeeDAO的addEmployee(Employee em)方法
/*
* 在addEmployee(Employee em)方法中，我们可以将员工的信息插入到Oracle的employee表中。
*/
public String addEmployee(Employee em){
//创建Connection
Connection con = null;
//创建CallableStatement
CallableStatement call = null;
//创建rs
String rs = null;
//获取输入值（即获取新增员工信息）
String name = em.getName();
int age = em.getAge();
String position = em.getPosition();
String dept = em.getDept();
//定义sql语句
String sql = "{call addEmployee(e_seq.nextval,?,?,?,?,?)}";
//建立连接
con = DBConnection.getConnection();
try {
//发送sql语句
call = con.prepareCall(sql);
//为占位符设置值
call.setString(1, name);
call.setInt(2,age);
call.setString(3, position);
call.setString(4,dept);
call.registerOutParameter(5, OracleTypes.VARCHAR);
//执行sql语句
call.execute();
//获取rs
rs = call.getString(5);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return rs;
}
<4>ok.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>
<%
//定义表单即将要提交的servlet的地址
String p = basePath + "SelectServlet?page=";
%>
<form action = "<%=p%>"method = "post" name= "form4">
<input type = "submit"name = "submit" value= "查询所有员工信息"/>
</form>
</body>
</html>
<5>fail.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>
<%
//定义表单即将要提交的servlet的地址
String p = basePath + "AddEmployeeServlet";
%>
<form action = "add.jsp"method = "post" name= "form4">
<input type = "submit"name = "submit" value= "返回添加页面"/>
</form>
</body>
</html>
2.我们实现数据的查询功能
1)当我们添加数据成功后，我们会跳转到ok.jsp页面，在这个页面中，有一个查询所有员工信息的按钮
2）点击这个按钮，我们开始查询功能
3）首先，表单会将我们的页面跳转到SelectServlet？page= 页面，这样我们就会转到SelectServlet.java的类中。
4）在这个类中，我们将会获取到目前要查询的当前页面的值，另外，我们还会定义每页即将显示的记录条数，
然后，我们将这两个参数传到EmployeeDAO中的getAllEmployee（int currentPage,int page_num）中。
5）在EmployeeDAO中的getAllEmployee(int currentPage,int page_num)中，我们可以获取到查询出来的某一页的列表。
6）我们还可以通过EmployeeDAO中的getTotalRecords(int currentPage,int page_num)方法获取到总的记录条数。
7）在servlet中，我们会将总的记录条数转换为总页数，然后，我们将员工信息列表及总页数转到select.jsp页面。
8）在select.jsp页面中，我们可以循环输出我们查询到的员工信息，另外，我们还可以点击上一页、下一页、最后一页、第一页的链接进行数据的跳转。
9）这样，我们便可以实现数据的分页查询功能了
10）具体实现代码如下所示：
<1>SelectServelt.java
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 SelectServlet
*/
public class SelectServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public SelectServlet() {
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 = 5;
//获取员工列表
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 = 0;
if(total%page_num != 0){
totalPage = total/page_num+1;
}else{
totalPage = total/page_num;
}
System.out.println(totalPage);
//将该total添加到session，并指定key值为total
HttpSession session = request.getSession();
session.setAttribute("total", totalPage);
//将获取到的值转发至fenye.jsp页面，用来显示获取到的值
request.getRequestDispatcher("select.jsp?page="+currentPage).forward(request,response);
}
}
<2>EmployeeDAO中的getAllEmployee(int currentPage,int page_num)方法
/*
* 通过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,dept,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"));
e.setDept(rs.getString("dept"));
//将该对象添加到list列表中
list.add(e);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
//最后不要忘记关闭连接
DBConnection.close(rs, call, con);
}
//返回Employee列表
return list;
}
<3>EmployeeDAO的getTotalRecords(int currentPage,int page_num)方法
/*
* 通过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>select.jsp
<%@ page language="java"contentType="text/html; charset=UTF-8" import = "java.util.*,entity.*"
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>
<%
String p = basePath + "SelectServlet?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>
<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();
String dept = em.getDept();
int id = em.getId();
System.out.print("************"+id);
%>
<tr>
<td><%=name %></td>
<td><%=age %></td>
<td><%=position %></td>
<td><%=dept %></td>
</tr>
<%} %>
</table>
<br/>
<%
//获取总页数
Integer totalPage = (Integer)session.getAttribute("total");
%>
<%
//判断当前页面的页数，显示链接
if(currentPage < 1) {
currentPage = 1;
}
if(currentPage != 1){
out.println("<a href = 'SelectServlet?page="+(currentPage-1)+"'>上一页</a>");
out.println("<a href = 'SelectServlet?page="+totalPage+"'>最后一页</a>");
}
if(currentPage != totalPage){
out.println("<a href = 'SelectServlet?page="+(currentPage+1)+"'>下一页</a>");
out.println("<a href = 'SelectServlet?page="+1+"'>第一页</a>");
}
%>
</body>
</html>
3.我们实现数据的删除功能
1）要实现数据的删除功能，我们可以在select.jsp页面上添加一个删除链接，通过这个链接，我们可以获取到员工的id号
2）点击删除链接，我们可以跳转到DeleteServlet
3）在servlet中，我们可以通过传递过来的id，将该id传递到EmployeeDAO中的deleteEmployee（int id）方法
4）通过EmployeeDAO 中的deleteEmployee(int id) 方法，我们删除数据库中相应的数据，返回一个是否删除的信息
5）在servlet中，我们获取到EmployeeDAO中传递过来的删除成功与否的信息，若删除成功，我们返回SelectServlet进行查询，若删除失败，我们返回select.jsp页面。
6）这样，我们便实现了数据的删除
7）具体实现代码如下所示：
<1>select.jsp页面中的删除链接
<td><a href = "DeleteServlet?id=<%=id %>">删除</a></td>
<2>DeleteServlet.java
package servlet;
import java.io.IOException;
import java.io.PrintWriter;
import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import dao.EmployeeDAO;
/**
* Servlet implementation class DeleteServlet
*/
public class DeleteServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public DeleteServlet() {
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
request.setCharacterEncoding("UTF-8");
int id = Integer.parseInt(request.getParameter("id"));
System.out.println(id);
int page = 0;
if(request.getParameter("pages") == null){
page = 1;
}else{
try{
page = Integer.parseInt(request.getParameter("pages"));
}catch(NumberFormatException e){
page = 1;
}
}
EmployeeDAO e = new EmployeeDAO();
if(e.deleteEmployee(id).equals("删除成功！")){
request.getRequestDispatcher("SelectServlet?pages="+page).forward(request, response);
}else{
PrintWriter out = response.getWriter();
out.println("<script>alert('删除失败！');location.href='select.jsp?page="+page+"';</script>");
}
}
}
<3>EmployeeDAO 中的deleteEmployee(int id) 方法
public String deleteEmployee(int id){
//创建Connection
Connection con = null;
//创建CallableStatement
CallableStatement call = null;
//创建rs
String rs = null;
//构建sql语句
String sql = "{call deleteEmployee(?,?)}";
//创建连接
con = DBConnection.getConnection();
try {
//发送SQL语句
call = con.prepareCall(sql);
//为占位符设置值
call.setInt(1, id);
call.registerOutParameter(2, OracleTypes.VARCHAR);
//执行sql语句
call.execute();
//获取rs的值
rs = call.getString(2);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
//关闭连接
DBConnection.close(null, call, con);
}
return rs;
}
4.我们实现数据的修改功能
1）同样的，我们在select.jsp页面上添加一个修改的链接
2）点击修改链接，我们可以跳转到UpdateEmployeeServlet，在这个servlet中，我们可以通过对参数para进行匹配，然后进行相应的操作。
3）如果参数为getemployee,我们会跳转到getEmployee()方法中，在这个方法中，我们会通过EmployeeDAO中的getEmployee(int id)方法来查询出该id所属的员工信息，并将该员工的信心显示到update.jsp页面
4)在update.jsp页面，我们可以对该员工的信息进行修改，并将修改后的信息通过提交按钮提交给UpdateEmployeeServlet。
5）在UpdateEmployeeServlet中，我们通过参数update匹配，跳转到updateEmployee（）方法
6）在 UpdateEmployee（）方法中，我们可以获取到表单提交的信息（即修改后的员工信息），然后，我们通过EmployeeDAO中的updateEmployee（Employee em）方法对员工的信息进行修改，然后返回一个 是否修改成功的rs
7）通过对rs进行判断，若更新成功，则返回SelectServlet，若更新失败，还是返回SelectServlet。
8）这样，我们便实现了数据的修改功能
9） 具体实现代码如下所示：
<1>修改链接
<td><a href = "UpdateEmployeeServlet?para=getemployee&id=<%=id%>">修改</a></td>
<2>UpdateEmployeeServlet
package servlet;
import java.io.IOException;
import java.io.PrintWriter;
import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import dao.EmployeeDAO;
import entity.Employee;
/**
* Servlet implementation class UpdateServlet
*/
public class UpdateEmployeeServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
private int currentPage ;
private int id;
/**
* @see HttpServlet#HttpServlet()
*/
public UpdateEmployeeServlet() {
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
//设置编码方式
request.setCharacterEncoding("UTF-8");
response.setCharacterEncoding("gb2312");
String para = request.getParameter("para");
if(para.equals("getemployee")){
getEmployee(request,response);
}
if(para.equals("update")){
updateEmployee(request,response);
}
}
public void getEmployee(HttpServletRequest request,HttpServletResponse response){
//设置当前页数
currentPage = 0;
String page = request.getParameter("pages");
if(page == null){
currentPage = 1;
}else{
try{
currentPage = Integer.parseInt(page);
}catch(NumberFormatException e){
currentPage = 1;
}
}
id = Integer.parseInt(request.getParameter("id"));
EmployeeDAO e = new EmployeeDAO();
Employee em = e.getEmployee(id);
request.setAttribute("em",em);
try {
request.getRequestDispatcher("update.jsp").forward(request, response);
} catch (ServletException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
} catch (IOException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}
public void updateEmployee(HttpServletRequest request,HttpServletResponse response){
//获取文本框修改后的员工信息
Employee em = new Employee();
em.setId(id);
System.out.println(id);
em.setName(request.getParameter("name"));
System.out.println(request.getParameter("name"));
em.setAge(Integer.parseInt(request.getParameter("age")));
em.setDept(request.getParameter("dept"));
em.setPosition(request.getParameter("position"));
//获取结果，看修改是否成功
EmployeeDAO e = new EmployeeDAO();
String rs = e.updateEmployee(em);
System.out.println("------------"+rs);
PrintWriter out = null;
try {
out = response.getWriter();
} catch (IOException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
if(rs.equals("更新成功")){
try {
request.getRequestDispatcher("SelectServlet?page="+currentPage).forward(request, response);
} catch (ServletException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
} catch (IOException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}else{
out.print("<script>alert('更新失败！')</script>");
}
}
}
<3>EmployeeDAO中的getEmployee(int id)方法
/*
* 通过getEmployee（int id）我们可以获取到id所属的员工信息
*/
public Employee getEmployee(int id){
Employee em = new Employee();
//创建Connection
Connection con = null;
//创建CallableStatement
CallableStatement call = null;
//构建sql语句
String sql = "{call selectEmployee(?,?,?,?,?)}";
//创建连接
con = DBConnection.getConnection();
try {
//发送sql语句
call = con.prepareCall(sql);
//为占位符设置值
call.setInt(1, id);
call.registerOutParameter(2, OracleTypes.VARCHAR);
call.registerOutParameter(3, OracleTypes.NUMBER);
call.registerOutParameter(4, OracleTypes.VARCHAR);
call.registerOutParameter(5, OracleTypes.VARCHAR);
//执行sql语句
call.execute();
//为Employee对象赋值
em.setName(call.getString(2));
em.setAge(call.getInt(3));
em.setDept(call.getString(4));
em.setPosition(call.getString(5));
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
DBConnection.close(null, call, con);
}
return em;
}
<4>update.jsp
<%@ page language="java"contentType="text/html; charset=UTF-8" import = "java.util.*,entity.*"
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>
<%
//获取要修改的记录的id
int id = Integer.parseInt(request.getParameter("id"));
//获取form即将要提交的路径
String p = basePath + "UpdateEmployeeServlet?para=update";
%>
<%
Employee em = (Employee)request.getAttribute("em");
String name = em.getName();
int age = em.getAge();
String dept = em.getDept();
String position = em.getPosition();
%>
<form action = "<%=p %>"method = "post" name="form5">
员工姓名：<input type = "text"name = "name" value= "<%=name %>"/>
员工年龄：<input type = "text"name = "age" value= "<%=age %>"/>
员工部门：<input type = "text"name = "dept" value="<%=dept %>"/>
员工职位：<input type = "text"name = "position" value= "<%=position %>"/>
<input type = "submit"name = "submit" value= "提交"/>
</form>
</body>
</html>
<5>EmployeeDAO中的updateEmployee（Employee em）方法
/*
* 通过updateEmployee(Employee em)方法，我们可以修改员工信息
*/
public String updateEmployee(Employee em){
//定义返回结果
String rs = null;
//创建Connection
Connection con = null;
//创建CallableStatement
CallableStatement call = null;
//构建sql语句
String sql = "{call updateEmployee(?,?,?,?,?,?)}";
//建立连接
con = DBConnection.getConnection();
//获取Employee的信息
int id = em.getId();
String name = em.getName();
int age = em.getAge();
String dept = em.getDept();
String position = em.getPosition();
try {
//发送sql语句
call = con.prepareCall(sql);
//为占位符设置值
call.setInt(1, id);
call.setString(2, name);
call.setInt(4, age);
call.setString(3, dept);
call.setString(5, position);
call.registerOutParameter(6, OracleTypes.VARCHAR);
//执行sql语句
call.execute();
//获取结果
rs = call.getString(6);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
//返回结果
return rs;
}
5.在本次编程中，我们需要用到的存储过程的sql语句如下：
<1>{call addEmployee(e_seq.nextval,?,?,?,?,?)}
<2>{call deleteEmployee(?,?)}
<3>{call selectEmployee(?,?,?,?,?)}
<4>{call updateEmployee(?,?,?,?,?,?)}
<5>{call mypage.getDataByPage(?,?,?,?,?,?,?,?)}
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;
这样，我们便实现了数据最基本的增删改查功能。
327
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
