使用JSP+SERVLET+JDBC實(shí)現(xiàn)對(duì)數(shù)據(jù)庫(kù)的增刪改查

1、使用JSP+SERVLET+JDBC實(shí)現(xiàn)對(duì)數(shù)據(jù)庫(kù)的增刪改查首先，打開(kāi)sql*plus，輸入用戶(hù)名(我用的 scott)密碼(我設(shè)置的是 tiger)。先建個(gè)表student. Create table stude nt ( id nu mber(30) not n ull primary key, n ame varchar(50), age nu mber(30), gen der varchar(30), major varchar(50);1，打開(kāi) myeclipse (我用的是 myeclipse8.5 )新建一個(gè) web projectlili Edit Itcltm曲*"

2、tlrip>Uu販雨卜Upwi耐Lnitrpnav AfipicrtEn PrajK：Ckuad+w護(hù)血昭ETCirl+Ehofr+VV出 Wrh也 Am PraiKtOrl+S母 HiarE Wtb咼 Srin- 4=ft RrfipHt.Sui.r Al出Rm/匕聲& Chrs寸 liWtriX#Rmarm-154u>1 亡iNviTiihrs-L3 FotivrUw 飯時(shí)|郁ToF Fi»W+P卜話 HI丘 l?irK*d TrmpJrin；應(yīng) AP iAdAiiuiid T«rTliii«| XMTM-!Krf TfimpUlKl注 I

3、nsert.皿 IjqpMT.-耳 EJB1 SiitutiK HimPenp«ni««Aic+rwLK EJB3 Enti Bet»EJB3 Mosagr &Ekwnl 附m網(wǎng)i|述 XML i AdaiTK«d rMTidaivL'i過(guò) XML iSMir Tempi占 rT|diia+ErmiBai.5 SMk4 TiwnflihhJUtian.Ji"申申3呼"GJ旦 MabLu Aorm鼻 LNL1 Mnd«ljrLlJ1111-1 JII HR 13鬥理s-。亨譽(yù)廣 a， u£i

4、 fl» s * ae? _± ta i I響卻10 Tepiweiciiod撲點(diǎn)號(hào)1-1Cgi ChrfH 曰 ,E h*DpCIFd L-lj 口7匡|鄭遇鶯口 丹擊MHlAtlurilAz 1 E l& £B 舉 B1 eS Bl 已曰iXpaa-ul亠r/pfl.nQ-|b2，在project name中輸入合法名字，比如normal3，新建的normal工程4, 在 src目錄下建一個(gè)包，右擊 src選擇 new 在選擇 package5,輸入合法名字比如bean6, 在 bean目錄下建一個(gè) class，右擊 bean選擇 new 再選擇 cl

5、ass7,輸入名字Page完整的Page.java代碼如下package bea n;public class Page private int totalPage;private int curre ntPage;private int totalRecord;private int curre ntRecord;private int pageSize=8;/獲得和設(shè)置當(dāng)前頁(yè)public int getCurre ntPage()retur n curre ntPage;public void setCurre ntPage(i nt curre ntRecord,i nt pageSiz

6、e) if(curre ntRecord%pageSize=0)curre ntPage=curre ntRecord/pageSize;elsecurre ntPage=curre ntRecord/pageSize+1;/獲得和設(shè)置當(dāng)前記錄public int getCurre ntRecord()retur n curre ntRecord;public void setCurre ntRecord(i nt curre ntRecord)this.curre ntRecord=curre ntRecord;/獲得和設(shè)置每頁(yè)記錄數(shù)量public int getPageSize()retu

7、rn pageSize;public void setPageSize(int pageSize)this.pageSize=pageSize;/獲得和設(shè)置總頁(yè)數(shù)public int getTotalPage()return totalPage;public void setTotalPage(int totalRecord,int pageSize)if(totalRecord%pageSize=0)totalPage=totalRecord/pageSize;else totalPage=totalRecord/pageSize+1;/獲得和設(shè)置總記錄public int getTotal

8、Record()return totalRecord;public void setTotalRecord(int totalRecord) this.totalRecord=totalRecord;8,用相同的方法建一個(gè) StudentInfo 類(lèi) 完整的 StudentInfo.java 代碼如下 package bean;public class StudentInfo private int id;/學(xué)號(hào)private String name;/姓名private int age;/年齡private String gender;/性別private String major;/專(zhuān)業(yè)p

9、ublic 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)th

10、=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;AllServlet 類(lèi)9，在 src 目錄下添加另一個(gè)包 dbser

11、vlet 在該包中建立一個(gè) 完整的 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.ServletEx

12、ception;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 doPos

13、t(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);tr

14、y 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

15、 catch blocke.printStackTrace(); catch (SQLException e) / TODO Auto-generated catch block e.printStackTrace();/doGet 方法public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException doPost(request,response);/數(shù)據(jù)庫(kù)連接方法public Connection connect() throws

16、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 con

17、n;/關(guān)閉數(shù)據(jù)庫(kù)資源public void close(Statement stat,Connection conn) throws SQLException if(stat!=null)stat.close();if(conn!=null)conn.close();/插入方法throwspublic void insert(HttpServletRequest request, HttpServletResponse response) ClassNotFoundException, SQLExceptionConnection conn=null;Statement stat=null;S

18、tring 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();st

19、at.execute("insert into student(id,name,age,gender,major) values("+id+",'"+name+"',"+age+",'"+gender+"','"+major+"')");close(stat,conn);/查詢(xún)方法public ArrayList<StudentInfo> select(String id,String name) throws Cl

20、assNotFoundException, SQLExceptionConnection conn=null;Statement stat=null;ResultSet rs=null;conn=connect();stat=conn.createStatement();ArrayList<StudentInfo> result=new ArrayList<StudentInfo>(); if(id=""&&name="")rs=stat.executeQuery("select * from stude

21、nt");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!="&qu

22、ot;&&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(&qu

23、ot;age");st.setGender(rs.getString("gender");st.setMajor(rs.getString("major");result.add(st); if(rs!=null) rs.close();close(stat,conn); return result;/條件查詢(xún)跳轉(zhuǎn)public void dispatch(HttpServletRequest request, HttpServletResponse response) throws ClassNotFoundException, SQLExce

24、ption, ServletException, IOExceptionString 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"

25、, select(id5,name5); request.getRequestDispatcher("idnameselect.jsp").forward(request, response);/設(shè)置分頁(yè)相關(guān)參數(shù)方法public Page setpage(HttpServletRequest request, HttpServletResponse response) throws ClassNotFoundException, SQLExceptionString crd=request.getParameter("currentRecord");/S

26、tring id=request.getParameter("id");/ String name=request.getParameter("name");ArrayList<StudentInfo> result=select("","");Page pager=new Page(); pager.setTotalRecord(result.size(); pager.setTotalPage(result.size(),pager.getPageSize(); if(crd!=null)int c

27、urrentRecord=Integer.parseInt(crd); pager.setCurrentRecord(currentRecord); pager.setCurrentPage(currentRecord,pager.getPageSize();return pager;/獲得分頁(yè)顯示的子集public void difpage(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, ClassNotFoundException, SQLExce

28、ption/ String id=request.getParameter("id");/ String name=request.getParameter("name");ArrayList<StudentInfo> result=select("","");Page pager=new Page(); pager=setpage(request,response); List<StudentInfo> subResult=null;int currentRecord=pager.getC

29、urrentRecord(); if(currentRecord=0)if(pager.getTotalRecord()<8) subResult=(List<StudentInfo>) result.subList(0,pager.getTotalRecord(); elsesubResult=(List<StudentInfo>) result.subList(0,pager.getPageSize();else if(pager.getCurrentRecord()+pager.getPageSize()<result.size() subResult

30、=(List<StudentInfo>) result.subList(pager.getCurrentRecord(),pager.getCurrentRecord()+pager.getPageSize(); else subResult=(List<StudentInfo>) result.subList(pager.getCurrentRecord(),result.size(); request.setAttribute("pager", pager); request.setAttribute("subResult",

31、 subResult); request.getRequestDispatcher("layout.jsp").forward(request, response); /信息刪除方法 public void delete(HttpServletRequest request, HttpServletResponse response) throws ClassNotFoundException, SQLException, ServletException, IOExceptionConnection conn=null;Statement stat=null; conn=

32、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, Htt

33、pServletResponse response) throws ClassNotFoundException, SQLException, ServletException, IOExceptionString id4=request.getParameter("id"); request.setAttribute("result", select(id4,""); request.getRequestDispatcher("update1.jsp").forward(request, response);th

34、rowsset wherepublic void update(HttpServletRequest request, HttpServletResponse response) ClassNotFoundException, SQLException, ServletException, IOExceptionConnection conn=null;Statement stat=null;String id3=request.getParameter("id");String name3=request.getParameter("name");St

35、ring age3=request.getParameter("age");String gender3=request.getParameter("gender");String major3=request.getParameter("major");conn=connect();stat=conn.createStatement();stat.execute("update student id="+id3+",name='"+name3+"',age="

36、;+age3+",gender='"+gender3+"',major='"+major3+"' id="+id3+"");request.setAttribute("result", select(id3,""); request.getRequestDispatcher("update.jsp").forward(request, response);10，在 webRoot 目錄下添加以下 .jsp 文件10.1 pu

37、tin.jsp<% page language="java" import="java.util.*" pageEncoding="UTF-8"%> <%String path = request.getContextPath();String basePath request.getScheme()+":/"+request.getServerName()+":"+request.getServerPort()+path+"/" %><!DOC

38、TYPE HTML PUBLIC "-/W3C/DTD HTML 4.01 Transitional/EN"><html><head><base href="<%=basePath%>"><title> 學(xué)生信息輸入 </title><meta http-equiv="pragma" content="no-cache"><meta http-equiv="cache-control" content

39、="no-cache"><meta http-equiv="expires" content="0"><meta http-equiv="keywords" content="keyword1,keyword2,keyword3"><meta http-equiv="description" content="This is my page"><!-<link rel="stylesheet&

40、quot; type="text/css" href="styles.css">-><script type="text/javascript"">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(&q

41、uot; 學(xué)號(hào)不能為空，請(qǐng)輸入學(xué)號(hào)！ ");return false;else if(name.length<=0)alert(" 姓名不能為空，請(qǐng)輸入姓名！ ");return false;else if(age<=0)alert(" 請(qǐng)輸入合法年齡！ ");return false;else if(major.length<=0)alert(" 專(zhuān)業(yè)不能為空，請(qǐng)輸入所學(xué)專(zhuān)業(yè)！");return false;elsereturn true;/document.getElementById("fo

42、rm").submit();</script></head><body><br><center><h2> 學(xué)生信息輸入 </h2><hr><form action="AllServlet" method="post" id="form" onSubmit="return validate()" ><input type="hidden" name="methodN

43、ame" value="0"/><h4> 學(xué)號(hào)： <input type="text" name="id" class="required:true" title=" 學(xué)號(hào)必須為數(shù)字 "></input><br></h4><h4> 姓名： <input type="text" name="name"title=" 姓名不能為空 "><

44、;/input><br></h4> <h4> 年齡： <input type="text" name="age"title=" 年齡必須為數(shù)字 "></input><br></h4> <h4> 性別： <input type="radio" name="gender" value=" 男"> 男<input type="radio" na

45、me="gender" value=" 女"> 女 <br></h4><h4> 專(zhuān)業(yè)： <input type="text" name="major"title=" 專(zhuān)業(yè)不能為空 "></input><br></h4><input type="submit" value=" 提交 "/></form><br><a href=&

46、quot;AllServlet?methodName=<%=1 %>&id=<%="" %>&name=<%="" %>"> 查看已輸 入信息 </a></center></body></html>10.2 layout.jsp<% page language="java" import="java.util.*" pageEncoding="UTF-8"%><%

47、 page import="bean.StudentInfo" %><% page import="bean.Page" %><%String path = request.getContextPath();String basePath request.getScheme()+":/"+request.getServerName()+":"+request.getServerPort()+path+"/" %><!DOCTYPE HTML PUBLIC &q

48、uot;-/W3C/DTD HTML 4.01 Transitional/EN"><html><head><base href="<%=basePath%>"><title> 學(xué)生信息 </title><meta http-equiv="pragma" content="no-cache"><meta http-equiv="cache-control" content="no-cache"

49、><meta http-equiv="expires" content="0"><meta http-equiv="keywords" content="keyword1,keyword2,keyword3"><meta http-equiv="description" content="This is my page"><!-<link rel="stylesheet" type="text

50、/css" href="styles.css">-><script type="text/javascript">function confirmdialog()if(window.confirm(" 您確定要?jiǎng)h除此條信息？ ")return true;else/ alert(" 取消刪除！ ");return false; </script> </head><body><br><h1> 學(xué)生信息 </h1>

51、<br> <hr><br><h3> 全部學(xué)生信息如下 </h3><table width="510" border="100" cellSpacing=1 style="border: 1pt dashed ; font-size: 15pt;" height="31"><tr><td> 學(xué)號(hào) </td><td>姓名 </td><td> 年齡 </td><

52、td> 性別 </td><td> 專(zhuān)業(yè) </td></tr><%response.setCharacterEncoding("UTF-8"); request.setCharacterEncoding("UTF-8"); Page pager=(Page)request.getAttribute("pager");List<StudentInfo> subResult=(List<StudentInfo>)request.getAttribute(&q

53、uot;subResult"); if(!subResult.isEmpty() for(int i=0;i<subResult.size();i+)StudentInfo st=subResult.get(i);out.print("<tr>");out.print("<td>"+st.getId()+"</td>"); out.print("<td>"+st.getName()+"</td>"); out.print

54、("<td>"+st.getAge()+"</td>"); out.print("<td>"+st.getGender()+"</td>"); out.print("<td>"+st.getMajor()+"</td>");%><td><a href="AllServlet?id=<%=st.getId() %>&methodName=<%=2

55、%>" onclick="return confirmdialog()"> 刪除 </a></td><td><ahref="AllServlet?id=<%=st.getId() %>&name=<%="" %>&methodName=<%=4 %>"> 修 改 </a></td><%out.print("</tr>");%></table&

56、gt;<span><font size="2"> 總 <%=pager.getTotalRecord() %> 條 記 錄 | 總 <%=pager.getTotalPage() %> 頁(yè)|當(dāng)前 <%=pager.getCurrentPage()+1 %> 頁(yè) |每頁(yè) <%=pager.getPageSize() %> 條|<%int last=pager.getCurrentRecord()-pager.getPageSize();int next=pager.getCurrentRecord(

57、)+pager.getPageSize();int currentRecord; if(last<0) out.println(" 首頁(yè) |");elseout.print("<a href='AllServlet?currentRecord="+last+"&methodName=1'> 上 一 頁(yè) </a>|");if(next>=pager.getTotalRecord()out.println(" 尾頁(yè) |");elseout.print(&quo

58、t;<a href='AllServlet?currentRecord="+next+"&methodName=1'> 下 一 頁(yè) </a>|");%></font></span><br><form action="AllServlet" method="post"><input type="hidden" name="methodName" value="5"

59、;/><h3>按學(xué)號(hào)姓名查詢(xún)：</h3>學(xué)號(hào)： <input type="text" name="id" value="" title=" 學(xué)號(hào) 必須為數(shù)字 " ></input>姓 名 ： <input type="text" name="name" value="" title=""></input><input type="submit&

60、quot; value=" 查詢(xún) " /></form><br><h3><a href=putin.jsp> 返回信息輸入頁(yè)面 </a></h3><br></body></html>10.3 update1.jsp<% page language="java" import="java.util.*" pageEncoding="UTF-8"%><% page import="

61、;bean.StudentInfo" %><%String path = request.getContextPath();String basePathrequest.getScheme()+":/"+request.getServerName()+":"+request.getServerPort()+path+"/"%><!DOCTYPE HTML PUBLIC "-/W3C/DTD HTML 4.01 Transitional/EN"><html><

62、head><base href="<%=basePath%>"><title> 學(xué)生信息修改 </title><meta http-equiv="pragma" content="no-cache"><meta http-equiv="cache-control" content="no-cache"><meta http-equiv="expires" content="0&quo

63、t;><meta http-equiv="keywords" content="keyword1,keyword2,keyword3"><meta http-equiv="description" content="This is my page"><!-<link rel="stylesheet" type="text/css" href="styles.css">-></head><

64、;body><br><h2> 學(xué)生信息 </h2> <hr><br><h3> 要修改的學(xué)生信息如下 </h3><table width="496" border="100" cellSpacing=1 style="border: 1pt dashed ; font-size: 15pt;"height="31"><tr><td> 學(xué)號(hào) </td><td>姓名 <

65、;/td><td> 年齡 </td><td> 性別 </td><td> 專(zhuān)業(yè) </td></tr><%int id=0;ArrayList<StudentInfo> result=new ArrayList<StudentInfo>();result=(ArrayList<StudentInfo>)request.getAttribute("result");if(!result.isEmpty()for(int i=0;i<result

66、.size();i+)StudentInfo st=result.get(i);id=st.getId();out.print("<tr>");out.print("<td>"+st.getId()+"</td>"); out.print("<td>"+st.getName()+"</td>"); out.print("<td>"+st.getAge()+"</td>");

67、 out.print("<td>"+st.getGender()+"</td>"); out.print("<td>"+st.getMajor()+"</td>");out.print("</tr>");%></table><h3> 將學(xué)生信息更改為： </h3><form action="AllServlet" method="post" >

68、<input type="hidden" name="methodName" value="3"/><h4> 學(xué) 號(hào) ： <input type="text" name="id"value="<%=id %>" title=" 學(xué) 號(hào) 不 能 改 變 "></input><br></h4><h4> 姓名： <input type="text&qu

69、ot; name="name"title=" 姓名不能為空 "></input><br></h4> <h4> 年齡： <input type="text" name="age"title=" 年齡不能為空 "></input><br></h4> <h4> 性別： <input type="radio" name="gender" valu

70、e=" 男"> 男<input type="radio" name="gender" value=" 女">女 <br></h4><h4> 專(zhuān)業(yè)： <input type="text" name="major"title=" 專(zhuān)業(yè)不能為空 "></input><br></h4><input type="submit" value=

71、" 修改 "/></form><br><h3><a href=putin.jsp> 返回信息輸入頁(yè)面 </a></h3><h3><a href=AllServlet?methodName=<%=1 %>&id=<%="" %>&name=<%="" %>> 返回信息 查詢(xún)頁(yè)面 </a></h3></body></html>10.4 update.jsp<% page language="java" import="java.util.*" pageEncoding="UTF-8"%>