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

使用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 中输入合法名字，比如normal3，新建的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; /获得和设置当前页 public int getCurrentPage() return currentPage; public void setCurrentPage(int currentRecord,int pageSize) if(currentRecord%pageSize=0) currentPage=currentRecord/pageSize; else currentPage=currentRecord/pageSize+1; /获得和设置当前记录 public int getCurrentRecord() return currentRecord; public void setCurrentRecord(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) totalPage=totalRecord/pageSize; else totalPage=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; /学号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;=name;this.age=age;this.gender=gender;this.major=major;public int getId()return id;public void setId(int id)this.id=id;public String getName()return name;public void setName(String 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()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 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 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.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,response); 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 doPost(request,response); /数据库连接方法public Connection connect() throws ClassNotFoundException, SQLException Connection conn=null; Class.forName(oracle.jdbc.driver.OracleDriver);String url=jdbc:oracle:thin:localhost:1521:orcl; String user=scott; String password=tiger; conn=DriverManager.getConnection(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, SQLException Connection 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(insert 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, SQLException Connection conn=null; Statement stat=null; ResultSet rs=null; conn=connect();stat=conn.createStatement(); ArrayList result=new ArrayList(); if(id=&name=) rs=stat.executeQuery(select * from student); if(id!=&name=) rs=stat.executeQuery(select * from student where id=+id+); if(id=&name!=) rs=stat.executeQuery(select * from student where name=+name+); if(id!=&name!=) rs=stat.executeQuery(select * from student where id=+id+ and name=+name+); 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(rs!=null) rs.close(); close(stat,conn); return result; /条件查询跳转 public void dispatch(HttpServletRequest request, HttpServletResponse response) throws ClassNotFoundException, SQLException, ServletException, IOException String id5=request.getParameter(id); String name5=request.getParameter(name); if(select(id5,name5).isEmpty() request.getRequestDispatcher(selectnothing.jsp).forward(request, response); else request.setAttribute(result, select(id5,name5); request.getRequestDispatcher(idnameselect.jsp).forward(request, response); /设置分页相关参数方法public Page setpage(HttpServletRequest request, HttpServletResponse 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(result.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, 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(pager.getTotalRecord()8) subResult=(List) result.subList(0,pager.getTotalRecord(); else subResult=(List) result.subList(0,pager.getPageSize(); else if(pager.getCurrentRecord()+pager.getPageSize()result.size() subResult=(List) result.subList(pager.getCurrentRecord(),pager.getCurrentRecord()+pager.getPageSize(); else subResult=(List) result.subList(pager.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, SQLException, ServletException, IOException Connection 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); /信息修改方法 public void update1(HttpServletRequest request, HttpServletResponse response) throws ClassNotFoundException, SQLException, ServletException, IOException String id4=request.getParameter(id); request.setAttribute(result, select(id4,); request.getRequestDispatcher(update1.jsp).forward(request, response); public void update(HttpServletRequest request, HttpServletResponse response) throws ClassNotFoundException, SQLException, ServletException, IOException Connection conn=null; Statement stat=null; String id3=request.getParameter(id); String name3=request.getParameter(name); String age3=request.getParameter(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(result, select(id3,); request.getRequestDispatcher(update.jsp).forward(request, response); 10，在webRoot目录下添加以下.jsp文件10.1 putin.jsp base href= 学生信息输入 !- function validate() var id=document.forms0.id.value; var name=.value; var age=document.forms0.age.value; var major=document.forms0.major.value; if(id=0) alert(学号不能为空，请输入学号！); return false; else if(name.length=0) alert(姓名不能为空，请输入姓名！); return false; else if(age=0) alert(请输入合法年龄！); return false; else if(major.length=0) alert(专业不能为空，请输入所学专业！); return false; else return true; /document.getElementById(form).submit(); 学生信息输入 学号： 姓名： 年龄： 性别：男 女 专业： a href=AllServlet?methodName=&id=&name=查看已输入信息 10.2 layout.jsp base href= 学生信息 !- function confirmdialog() if(window.confirm(您确定要删除此条信息？) return true; else / alert(取消删除！); return false; 学生信息 全部学生信息如下 学号 姓名 年龄 性别 专业 % response.setCharacterEncoding(UTF-8); request.setCharacterEncoding(UTF-8); Page pager=(Page)request.getAttribute(pager); List subResult=(List)request.getAttribute(subResult); if(!subResult.isEmpty() for(int i=0;isubResult.size();i+) StudentInfo st=subResult.get(i); out.print(); out.print(+st.getId()+); out.print(+st.getName()+); out.print(+s