JSP+SERVLET+JDBC实现对数据库的增删改查

1、使用JSP+SERVLET+JDBC实现对数据库的增删改查首先，打开sql*plus，输入用户名（我用的root）密码（我设置的是root）。先建个表student，Create table student (id number(30) not null primary key,name varchar(50) ,age number(30),gender varchar(30),major varchar(50) );1，打开eclipse新建一个web project (UTF-8)2，在project name 中输入合法名字，比如normal3，新建的normal工程4，在src目录下

2、建一个包，右击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() re

3、turn 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

4、.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=totalRe

5、cord/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 Str

6、ing 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(in

7、t 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;p

8、ublic 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;im

9、port 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 /

10、* * */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

12、 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 Servlet

13、Exception, 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"

14、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, HttpServletRes

15、ponse 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&

16、quot;); 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+"'

17、;)"); close(stat,conn); /查询方法 public ArrayList<StudentInfo> select(String id,String name) throws ClassNotFoundException, SQLException Connection conn=null; Statement stat=null; ResultSet rs=null; conn=connect();stat=conn.createStatement(); ArrayList<StudentInfo> result=new ArrayList

18、<StudentInfo>(); if(id=""&&name="") rs=stat.executeQuery("select * from student"); if(id!=""&&name="") rs=stat.executeQuery("select * from student where id="+id+""); if(id=""&&name!="&q

19、uot;) 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

20、 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 re

21、sult; /条件查询跳转 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()

22、 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,

23、HttpServletResponse response) throws ClassNotFoundException, SQLExceptionString crd=request.getParameter("currentRecord");/String id=request.getParameter("id"); / String name=request.getParameter("name"); ArrayList<StudentInfo> result=select("","&qu

24、ot;); 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

25、void difpage(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, ClassNotFoundException, SQLException/ String id=request.getParameter("id"); / String name=request.getParameter("name"); ArrayList<StudentInfo> result=select("&q

26、uot;,""); Page pager=new Page(); pager=setpage(request,response); List<StudentInfo> subResult=null; int currentRecord=pager.getCurrentRecord(); if(currentRecord=0) if(pager.getTotalRecord()<8) subResult=(List<StudentInfo>) result.subList(0,pager.getTotalRecord(); else subRes

27、ult=(List<StudentInfo>) result.subList(0,pager.getPageSize(); else if(pager.getCurrentRecord()+pager.getPageSize()<result.size() subResult=(List<StudentInfo>) result.subList(pager.getCurrentRecord(),pager.getCurrentRecord()+pager.getPageSize(); else subResult=(List<StudentInfo>)

28、 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, Http

29、ServletResponse 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+&quo

30、t;");request.getRequestDispatcher("delete.jsp").forward(request, response); /信息修改方法 public void update1(HttpServletRequest request, HttpServletResponse response) throws ClassNotFoundException, SQLException, ServletException, IOException String id4=request.getParameter("id");

31、 request.setAttribute("result", select(id4,""); request.getRequestDispatcher("update1.jsp").forward(request, response); public void update(HttpServletRequest request, HttpServletResponse response) throws ClassNotFoundException, SQLException, ServletException, IOExceptio

32、n 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("majo

33、r"); conn=connect(); stat=conn.createStatement();stat.execute("update student set id="+id3+",name='"+name3+"',age="+age3+",gender='"+gender3+"',major='"+major3+"' where id="+id3+"");request.setAttribut

34、e("result", select(id3,""); request.getRequestDispatcher("update.jsp").forward(request, response); 10，在webRoot目录下添加以下.jsp文件10.1 putin.jsp<% page language="java" import="java.util.*" pageEncoding="UTF-8"%><%String path = request.get

35、ContextPath();String basePath = request.getScheme()+":/"+request.getServerName()+":"+request.getServerPort()+path+"/"%><!DOCTYPE HTML PUBLIC "-/W3C/DTD HTML 4.01 Transitional/EN"><html> <head> <base href="<%=basePath%>"&

36、gt; <title>学生信息输入</title><meta http-equiv="pragma" content="no-cache"><meta http-equiv="cache-control" content="no-cache"><meta http-equiv="expires" content="0"> <meta http-equiv="keywords" content

37、="keyword1,keyword2,keyword3"><meta http-equiv="description" content="This is my page"><!-<link rel="stylesheet" type="text/css" href="styles.css">-><script type="text/javascript""> function validat

38、e() 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<=

39、0) alert("请输入合法年龄！"); return false; else if(major.length<=0) alert("专业不能为空，请输入所学专业！"); return false; else return true; /document.getElementById("form").submit(); </script> </head> <body> <br> <center> <h2>学生信息输入</h2><hr>

40、; <form action="AllServlet" method="post" id="form" onSubmit="return validate()" > <input type="hidden" name="methodName" value="0"/><h4> 学号：<input type="text" name="id" class="require

41、d:true" title="学号必须为数字"></input><br></h4><h4> 姓名：<input type="text" name="name"title="姓名不能为空"></input><br></h4><h4> 年龄：<input type="text" name="age"title="年龄必须为数字"&g

42、t;</input><br></h4><h4> 性别：<input type="radio" name="gender" value="男">男 <input type="radio" name="gender" value="女">女<br></h4><h4> 专业：<input type="text" name="major&qu

43、ot;title="专业不能为空"></input><br></h4> <input type="submit" value="提交"/> </form> <br> <a href="AllServlet?methodName=<%=1 %>&id=<%="" %>&name=<%="" %>">查看已输入信息</a> &l

44、t;/center> </body></html>10.2 layout.jsp<% page language="java" import="java.util.*" pageEncoding="UTF-8"%><% page import="bean.StudentInfo" %><% page import="bean.Page" %><%String path = request.getContextPath();S

45、tring basePath = request.getScheme()+":/"+request.getServerName()+":"+request.getServerPort()+path+"/"%><!DOCTYPE HTML PUBLIC "-/W3C/DTD HTML 4.01 Transitional/EN"><html> <head> <base href="<%=basePath%>"> <title&g

46、t;学生信息</title> <meta http-equiv="pragma" content="no-cache"><meta http-equiv="cache-control" content="no-cache"><meta http-equiv="expires" content="0"> <meta http-equiv="keywords" content="keyword1,

47、keyword2,keyword3"><meta http-equiv="description" content="This is my page"><!-<link rel="stylesheet" type="text/css" href="styles.css">-> <script type="text/javascript"> function confirmdialog() if(window.co

48、nfirm("您确定要删除此条信息？") return true; else / alert("取消删除！"); return false; </script> </head> <body> <br> <h1>学生信息</h1> <br> <hr> <br> <h3>全部学生信息如下</h3> <table width="510" border="100" cellSpacing

49、=1 style="border: 1pt dashed ; font-size: 15pt;" height="31"> <tr> <td>学号</td> <td>姓名</td> <td>年龄</td> <td>性别</td> <td>专业</td> </tr> <% response.setCharacterEncoding("UTF-8"); request.setChara

50、cterEncoding("UTF-8"); Page pager=(Page)request.getAttribute("pager"); List<StudentInfo> subResult=(List<StudentInfo>)request.getAttribute("subResult"); if(!subResult.isEmpty() for(int i=0;i<subResult.size();i+) StudentInfo st=subResult.get(i); out.print(

51、"<tr>"); out.print("<td>"+st.getId()+"</td>"); out.print("<td>"+st.getName()+"</td>"); out.print("<td>"+st.getAge()+"</td>"); out.print("<td>"+st.getGender()+"</td&

52、gt;"); out.print("<td>"+st.getMajor()+"</td>"); %> <td><a href="AllServlet?id=<%=st.getId() %>&methodName=<%=2 %>" onclick="return confirmdialog()">删除</a></td> <td><a href="AllServlet?id

53、=<%=st.getId() %>&name=<%="" %>&methodName=<%=4 %>">修改</a></td> <% out.print("</tr>"); %> </table> <span><font size="2">总<%=pager.getTotalRecord() %>条记录|总<%=pager.getTotalPage() %>页

54、|当前<%=pager.getCurrentPage()+1 %>页|每页<%=pager.getPageSize() %>条| <% int last=pager.getCurrentRecord()-pager.getPageSize(); int next=pager.getCurrentRecord()+pager.getPageSize(); int currentRecord; if(last<0) out.println("首页|"); else out.print("<a href='AllServ

55、let?currentRecord="+last+"&methodName=1'>上一页</a>|"); if(next>=pager.getTotalRecord() out.println("尾页|"); else out.print("<a href='AllServlet?currentRecord="+next+"&methodName=1'>下一页</a>|"); %> </font> &

56、lt;/span> <br> <form action="AllServlet" method="post"> <input type="hidden" name="methodName" value="5"/> <h3>按学号姓名查询:</h3> 学号：<input type="text" name="id" value="" title="学号必须为数

57、字" ></input> 姓名：<input type="text" name="name" value="" title=""></input> <input type="submit" value="查询" /> </form> <br><h3><a href=putin.jsp>返回信息输入页面</a></h3> <br>

58、</body></html>10.3 update1.jsp<% page language="java" import="java.util.*" pageEncoding="UTF-8"%><% page import="bean.StudentInfo" %><%String path = request.getContextPath();String basePath = request.getScheme()+":/"+reques

59、t.getServerName()+":"+request.getServerPort()+path+"/"%><!DOCTYPE HTML PUBLIC "-/W3C/DTD HTML 4.01 Transitional/EN"><html> <head> <base href="<%=basePath%>"> <title>学生信息修改</title> <meta http-equiv="pragma&qu

60、ot; content="no-cache"><meta http-equiv="cache-control" content="no-cache"><meta http-equiv="expires" content="0"> <meta http-equiv="keywords" content="keyword1,keyword2,keyword3"><meta http-equiv="des

61、cription" content="This is my page"><!-<link rel="stylesheet" type="text/css" href="styles.css">-> </head> <body> <br> <h2>学生信息</h2> <hr> <br> <h3>要修改的学生信息如下</h3> <table width="4

62、96" border="100" cellSpacing=1 style="border: 1pt dashed ; font-size: 15pt;" height="31"> <tr> <td>学号</td> <td>姓名</td> <td>年龄</td> <td>性别</td> <td>专业</td> </tr> <% int id=0; ArrayList<StudentInfo> result=new ArrayList<StudentInfo>(); result