使用JSP+SERVLET+JDBC实现对数据库的增删改查(详细).doc

1、使用 JSP+SERVLET+JDBC 实现对数据库的增删改查首先，打开 sql*plus，输入用户名（我用的 scott）密码（我设置的是 tiger） 。先建个表 student，Create table student (id number(30) not null primary key,name varchar(50) ,age number(30),gender varchar(30),major varchar(50) );1，打开 myeclipse（我用的是 myeclipse8.5）新建一个 web project2，在 project name 中输入合法名字，比如 no

2、rmal3，新建的 normal 工程4，在 src 目录下建一个包，右击 src 选择 new 在选择 package5，输入合法名字 比如 bean6，在 bean 目录下建一个 class，右击 bean 选择 new 再选择 class7，输入名字 Page完整的 Page.java 代码如下package bean;public class Page private int totalPage;private int currentPage;private int totalRecord;private int currentRecord;private int pageSize=8

3、;/获得和设置当前页public int getCurrentPage()return currentPage;public void setCurrentPage(int currentRecord,int pageSize)if(currentRecord%pageSize=0)currentPage=currentRecord/pageSize;elsecurrentPage=currentRecord/pageSize+1;/获得和设置当前记录public int getCurrentRecord()return currentRecord;public void setCurrent

4、Record(int currentRecord)this.currentRecord=currentRecord;/获得和设置每页记录数量public int getPageSize()return pageSize;public void setPageSize(int pageSize)this.pageSize=pageSize;/获得和设置总页数public int getTotalPage()return totalPage;public void setTotalPage(int totalRecord,int pageSize)if(totalRecord%pageSize=0

5、)totalPage=totalRecord/pageSize;elsetotalPage=totalRecord/pageSize+1;/获得和设置总记录public int getTotalRecord()return totalRecord;public void setTotalRecord(int totalRecord)this.totalRecord=totalRecord;8,用相同的方法建一个 StudentInfo 类完整的 StudentInfo.java 代码如下package bean;public class StudentInfo private int id;

6、/学号private String name; /姓名private int age; /年龄private String gender; /性别private String major; /专业public StudentInfo()public StudentInfo(int id,String name,int age,String gender,String major)this.id=id;this.name=name;this.age=age;this.gender=gender;this.major=major;public int getId()return id;public

7、 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 getGender()return gender;public void setGender(String gender)this.gender=gender;public String getMajor()

8、return major;public void setMajor(String major)this.major=major;9，在 src 目录下添加另一个包 dbservlet 在该包中建立一个 AllServlet 类完整的 AllServlet.java 代码如下package dbservlet;import java.io.IOException;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import

9、 java.sql.Statement;import java.util.ArrayList;import java.util.List;import javax.servlet.ServletException;import javax.servlet.http.HttpServlet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import bean.Page;import bean.StudentInfo;public class AllServlet

10、 extends HttpServlet /* */private static final long serialVersionUID = 1L;/doPost 方法public void doPost(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException request.setCharacterEncoding(“UTF-8“);response.setCharacterEncoding(“UTF-8“);String methodName=request.

11、getParameter(“methodName“);int method=Integer.parseInt(methodName);try switch(method)case 0:insert(request,response);case 1:difpage(request,response);break; case 2:delete(request,response);break; case 3:update(request,response);break;case 4:update1(request,response);break;case 5:dispatch(request,res

12、ponse);break; catch (ClassNotFoundException e) / TODO Auto-generated catch blocke.printStackTrace(); catch (SQLException e) / TODO Auto-generated catch blocke.printStackTrace(); /doGet 方法public void doGet(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException d

13、oPost(request,response);/数据库连接方法public Connection connect() throws ClassNotFoundException, SQLExceptionConnection conn=null; Class.forName(“oracle.jdbc.driver.OracleDriver“);String url=“jdbc:oracle:thin:localhost:1521:orcl“; String user=“scott“; String password=“tiger“; conn=DriverManager.getConnect

14、ion(url,user,password); return conn;/关闭数据库资源public void close(Statement stat,Connection conn) throws SQLExceptionif(stat!=null)stat.close();if(conn!=null)conn.close();/插入方法public void insert(HttpServletRequest request, HttpServletResponse response) throws ClassNotFoundException, SQLExceptionConnecti

15、on conn=null;Statement stat=null;String id=request.getParameter(“id“);String name=request.getParameter(“name“);String age=request.getParameter(“age“);String gender=request.getParameter(“gender“);String major=request.getParameter(“major“);conn=connect();stat=conn.createStatement();stat.execute(“inser

16、t into student(id,name,age,gender,major) values(“+id+“,“+name+“,“+age+“,“+gender+“,“+major+“)“); close(stat,conn);/查询方法public ArrayList select(String id,String name) throws ClassNotFoundException, SQLExceptionConnection conn=null;Statement stat=null;ResultSet rs=null;conn=connect();stat=conn.createS

17、tatement();ArrayList result=new ArrayList();if(id=“ if(id!=“ if(id=“ if(id!=“ while(rs.next()StudentInfo st=new StudentInfo();st.setId(rs.getInt(“id“);st.setName(rs.getString(“name“);st.setAge(rs.getInt(“age“);st.setGender(rs.getString(“gender“);st.setMajor(rs.getString(“major“); result.add(st);if(r

18、s!=null)rs.close();close(stat,conn);return result;/条件查询跳转public void dispatch(HttpServletRequest request, HttpServletResponse response) throws ClassNotFoundException, SQLException, ServletException, IOExceptionString id5=request.getParameter(“id“);String name5=request.getParameter(“name“); if(select

19、(id5,name5).isEmpty()request.getRequestDispatcher(“selectnothing.jsp“).forward(request, response);elserequest.setAttribute(“result“, select(id5,name5);request.getRequestDispatcher(“idnameselect.jsp“).forward(request, response);/设置分页相关参数方法public Page setpage(HttpServletRequest request, HttpServletRes

20、ponse response) throws ClassNotFoundException, SQLExceptionString crd=request.getParameter(“currentRecord“);/String id=request.getParameter(“id“);/ String name=request.getParameter(“name“);ArrayList result=select(“,“);Page pager=new Page();pager.setTotalRecord(result.size(); pager.setTotalPage(resul

21、t.size(),pager.getPageSize();if(crd!=null)int currentRecord=Integer.parseInt(crd);pager.setCurrentRecord(currentRecord);pager.setCurrentPage(currentRecord,pager.getPageSize();return pager;/获得分页显示的子集public void difpage(HttpServletRequest request, HttpServletResponse response) throws ServletException,

22、 IOException, ClassNotFoundException, SQLException/ String id=request.getParameter(“id“);/ String name=request.getParameter(“name“);ArrayList result=select(“,“);Page pager=new Page();pager=setpage(request,response);List subResult=null;int currentRecord=pager.getCurrentRecord();if(currentRecord=0)if(

23、pager.getTotalRecord() result.subList(0,pager.getTotalRecord();elsesubResult=(List) result.subList(0,pager.getPageSize(); else if(pager.getCurrentRecord()+pager.getPageSize() result.subList(pager.getCurrentRecord(),pager.getCurrentRecord()+pager.getPageSize();elsesubResult=(List) result.subList(page

24、r.getCurrentRecord(),result.size();request.setAttribute(“pager“, pager);request.setAttribute(“subResult“, subResult);request.getRequestDispatcher(“layout.jsp“).forward(request, response);/信息删除方法public void delete(HttpServletRequest request, HttpServletResponse response) throws ClassNotFoundException

25、, SQLException, ServletException, IOExceptionConnection conn=null;Statement stat=null;conn=connect();stat=conn.createStatement();String id2=request.getParameter(“id“);stat.execute(“delete from student where id=“+id2+“);request.getRequestDispatcher(“delete.jsp“).forward(request, response); /信息修改方法pub

26、lic void update1(HttpServletRequest request, HttpServletResponse response) throws ClassNotFoundException, SQLException, ServletException, IOExceptionString id4=request.getParameter(“id“);request.setAttribute(“result“, select(id4,“);request.getRequestDispatcher(“update1.jsp“).forward(request, respons

27、e); public void update(HttpServletRequest request, HttpServletResponse response) throws ClassNotFoundException, SQLException, ServletException, IOExceptionConnection conn=null;Statement stat=null;String id3=request.getParameter(“id“);String name3=request.getParameter(“name“);String age3=request.getP

28、arameter(“age“);String gender3=request.getParameter(“gender“);String major3=request.getParameter(“major“);conn=connect();stat=conn.createStatement();stat.execute(“update student set id=“+id3+“,name=“+name3+“,age=“+age3+“,gender=“+gender3+“,major=“+major3+“ where id=“+id3+“);request.setAttribute(“res

29、ult“, select(id3,“); request.getRequestDispatcher(“update.jsp“).forward(request, response); 10，在 webRoot 目录下添加以下.jsp 文件10.1 putin.jsp“学生信息输入 function validate()var id=document.forms0.id.value;var name=document.forms0.name.value;var age=document.forms0.age.value;var major=document.forms0.major.value;

30、if(id学生信息输入学号：姓名：年龄：性别：男女专业：else/ alert(“取消删除！“);return false; 学生信息 全部学生信息如下学号姓名年龄性别专业subResult=(List)request.getAttribute(“subResult“);if(!subResult.isEmpty()for(int i=0;i“);out.print(“+st.getId()+“);out.print(“+st.getName()+“);out.print(“+st.getAge()+“);out.print(“+st.getGender()+“);out.print(“+st

31、.getMajor()+“); %总条记录|总页|当前 页| 每页 条|上一页|“);if(next=pager.getTotalRecord()out.println(“尾页|“);elseout.print(“下一页|“);%按学号姓名查询:学号：姓名：返回信息输入页面 10.3 update1.jsp“学生信息修改 学生信息 要修改的学生信息如下学号姓名年龄性别专业result=new ArrayList();result=(ArrayList)request.getAttribute(“result“);if(!result.isEmpty()for(int i=0;i“);out.p

32、rint(“+st.getId()+“);out.print(“+st.getName()+“);out.print(“+st.getAge()+“);out.print(“+st.getGender()+“);out.print(“+st.getMajor()+“); out.print(“);%将学生信息更改为：学号： “ title=“学号不能改变“姓名：年龄：性别：男女 专业：返回信息输入页面 result=(ArrayList)request.getAttribute(“result“);if(!result.isEmpty()for(int i=0;i“);out.print(“+

33、st.getId()+“);out.print(“+st.getName()+“);out.print(“+st.getAge()+“);out.print(“+st.getGender()+“);out.print(“+st.getMajor()+“); out.print(“);%返回信息输入页面%返回信息输入页面 result=(ArrayList)request.getAttribute(“result“);if(!result.isEmpty()for(int i=0;i“);out.print(“+st.getId()+“);out.print(“+st.getName()+“);out.print(“+st.getAge()+“);out.print(“+st.getGender()+“);out.print(“+st.getMajor()+“); %返回信息输入页面 &id=&name=返回信息查询页面11, 修改 web.xml 文件（在 WEBINF 目录下）将index.jsp改为putint.jsp，修改或如下putin.jsp