使用JSPSERVLETJDBC實現(xiàn)對數(shù)據(jù)庫增刪改查

1、使用JSPSERVLETJDBC實現(xiàn)對數(shù)據(jù)庫的增改正查(精)使用JSPSERVLETJDBC實現(xiàn)對數(shù)據(jù)庫的增改正查(精)29/29使用JSPSERVLETJDBC實現(xiàn)對數(shù)據(jù)庫的增改正查(精)使用JSP+SERVLET+JDBC實現(xiàn)對數(shù)據(jù)庫的增改正查第一，翻開sql*plus，輸入用戶名（我用的scott）密碼（我設(shè)置的是tiger）。先建個表student，Createtablestudent(idnumber(30notnullprimarykey,namevarchar(50,agenumber(30,gendervarchar(30,majorvarchar(50;1，翻開myeclip

2、se（我用的是myeclipse8.5）新建一個webproject2，在projectname中輸入合法名字，比方normal3，新建的normal工程4，在src目錄下建一個包，右擊src選擇new在選擇package5，輸入合法名字比方bean6，在bean目錄下建一個class，右擊bean選擇new再選擇class7，輸入名字Page圓滿的Page.java代碼以下packagebean;publicclassPageprivateinttotalPage;privateintcurrentPage;privateinttotalRecord;privateintcurrentRec

3、ord;privateintpageSize=8;獲取和設(shè)置目前頁publicintgetCurrentPage(returncurrentPage;publicvoidsetCurrentPage(intcurrentRecord,intpageSizeif(currentRecord%pageSize=0currentPage=currentRecord/pageSize;elsecurrentPage=currentRecord/pageSize+1;獲取和設(shè)置目前記錄publicintgetCurrentRecord(returncurrentRecord;publicvoidsetC

4、urrentRecord(intcurrentRecordthis.currentRecord=currentRecord;獲取和設(shè)置每頁記錄數(shù)目publicintgetPageSize(returnpageSize;publicvoidsetPageSize(intpageSizethis.pageSize=pageSize;獲取和設(shè)置總頁數(shù)publicintgetTotalPage(returntotalPage;publicvoidsetTotalPage(inttotalRecord,intpageSizeif(totalRecord%pageSize=0totalPage=total

5、Record/pageSize;elsetotalPage=totalRecord/pageSize+1;獲取和設(shè)置總記錄publicintgetTotalRecord(returntotalRecord;publicvoidsetTotalRecord(inttotalRecordthis.totalRecord=totalRecord;8,用同樣的方法建一個StudentInfo類圓滿的StudentInfo.javapackagebean;publicclassStudentInfo代碼以下privateintid;/學(xué)號privateStringname;/姓名privateintag

6、e;/年紀(jì)privateStringgender;/性別privateStringmajor;/publicStudentInfo(專業(yè)publicStudentInfo(intid,Stringname,intage,Stringgender,Stringmajorthis.id=id;=name;this.age=age;this.gender=gender;this.major=major;publicintgetId(returnid;publicvoidsetId(intidthis.id=id;publicStringgetName(returnname;publicvoidset

7、Name(Stringname=name;publicintgetAge(returnage;publicvoidsetAge(intagethis.age=age;publicStringgetGender(returngender;publicvoidsetGender(Stringgenderthis.gender=gender;publicStringgetMajor(returnmajor;publicvoidsetMajor(Stringmajorthis.major=major;9，在src目錄下增添另一個包dbservlet在該包中成立一個AllServlet類圓滿的AllSe

8、rvlet.javapackagedbservlet;代碼以下importjava.io.IOException;importjava.sql.Connection;importjava.sql.DriverManager;importjava.sql.ResultSet;importjava.sql.SQLException;importjava.sql.Statement;importjava.util.ArrayList;importjava.util.List;importjavax.servlet.ServletException;importjavax.servlet.http.H

9、ttpServlet;importjavax.servlet.http.HttpServletRequest;importjavax.servlet.http.HttpServletResponse;importbean.Page;importbean.StudentInfo;publicclassAllServletextendsHttpServlet/*/privatestaticfinallongserialVersionUID=1L;/doPost方法publicvoiddoPost(HttpServletRequestrequest,HttpServletResponserespon

10、sethrowsServletException,IOExceptionrequest.setCharacterEncoding(UTF-8;response.setCharacterEncoding(UTF-8;StringmethodName=request.getParameter(methodName;intmethod=Integer.parseInt(methodName;tryswitch(methodcase0:insert(request,response;case1:difpage(request,response;break;case2:delete(request,re

11、sponse;break;case3:update(request,response;break;case4:update1(request,response;break;case5:dispatch(request,response;break;catch(ClassNotFoundExceptione/TODOAuto-generatedcatchblocke.printStackTrace(;catch(SQLExceptioneTODOAuto-generatedcatchblocke.printStackTrace(;/doGet方法publicvoiddoGet(HttpServl

12、etRequestrequest,HttpServletResponseresponsethrowsServletException,IOExceptiondoPost(request,response;數(shù)據(jù)庫連結(jié)方法publicConnectionconnect(throwsClassNotFoundException,SQLExceptionConnectionconn=null;Class.forName(oracle.jdbc.driver.OracleDriver;Stringurl=jdbc:oracle:thin:localhost:1521:orcl;Stringuser=sc

13、ott;Stringpassword=tiger;conn=DriverManager.getConnection(url,user,password;returnconn;封閉數(shù)據(jù)庫資源publicvoidclose(Statementstat,ConnectionconnthrowsSQLExceptionif(stat!=nullstat.close(;if(conn!=nullconn.close(;插入方法publicvoidinsert(HttpServletRequestrequest,HttpServletResponseresponsethrowsClassNotFoundE

14、xception,SQLExceptionConnectionconn=null;Statementstat=null;Stringid=request.getParameter(id;Stringname=request.getParameter(name;Stringage=request.getParameter(age;Stringgender=request.getParameter(gender;Stringmajor=request.getParameter(major;conn=connect(;stat=conn.createStatement(;stat.execute(i

15、nsertintostudent(id,name,age,gender,majorvalues(+id+,+name+,+age+,+gender+,+major+;close(stat,conn;查問方法publicArrayListselect(Stringid,StringnamethrowsClassNotFoundException,SQLExceptionConnectionconn=null;Statementstat=null;ResultSetrs=null;conn=connect(;stat=conn.createStatement(;ArrayListresult=ne

16、wArrayList(;if(id=&name=rs=stat.executeQuery(select*fromstudent;if(id!=&name=rs=stat.executeQuery(select*fromstudentwhereid=+id+;if(id=&name!=rs=stat.executeQuery(select*fromstudentwherename=+name+;if(id!=&name!=rs=stat.executeQuery(select*fromstudentwhereid=+id+andname=+name+;while(rs.next(StudentI

17、nfost=newStudentInfo(;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!=nullrs.close(;close(stat,conn;returnresult;條件查問跳轉(zhuǎn)publicvoiddispatch(HttpServletRequestrequest,HttpServletResponserespo

18、nsethrowsClassNotFoundException,SQLException,ServletException,IOExceptionStringid5=request.getParameter(id;Stringname5=request.getParameter(name;if(select(id5,name5.isEmpty(request.getRequestDispatcher(selectnothing.jsp.forward(request,response;elserequest.setAttribute(result,select(id5,name5;reques

19、t.getRequestDispatcher(idnameselect.jsp.forward(request,response;設(shè)置分頁有關(guān)參數(shù)方法publicPagesetpage(HttpServletRequestrequest,HttpServletResponseresponsethrowsClassNotFoundException,SQLExceptionStringcrd=request.getParameter(currentRecord;/Stringid=request.getParameter(id;Stringname=request.getParameter(na

20、me;ArrayListresult=select(,;Pagepager=newPage(;pager.setTotalRecord(result.size(;pager.setTotalPage(result.size(,pager.getPageSize(;if(crd!=nullintcurrentRecord=Integer.parseInt(crd;pager.setCurrentRecord(currentRecord;pager.setCurrentPage(currentRecord,pager.getPageSize(;returnpager;獲取分頁顯示的子集public

21、voiddifpage(HttpServletRequestrequest,HttpServletResponseresponsethrowsServletException,IOException,ClassNotFoundException,SQLExceptionStringid=request.getParameter(id;Stringname=request.getParameter(name;ArrayListresult=select(,;Pagepager=newPage(;pager=setpage(request,response;ListsubResult=null;i

22、ntcurrentRecord=pager.getCurrentRecord(;if(currentRecord=0if(pager.getTotalRecord(8subResult=(Listresult.subList(0,pager.getTotalRecord(;elsesubResult=(Listresult.subList(0,pager.getPageSize(;elseif(pager.getCurrentRecord(+pager.getPageSize(subResult=(Listresult.subList(pager.getCurrentRecord(,pager

23、.getCurrentRecord(+pager.getPageSize(;elsesubResult=(Listresult.subList(pager.getCurrentRecord(,result.size(;request.setAttribute(pager,pager;request.setAttribute(subResult,subResult;request.getRequestDispatcher(layout.jsp.forward(request,response;信息刪除方法publicvoiddelete(HttpServletRequestrequest,Htt

24、pServletResponseresponsethrowsClassNotFoundException,SQLException,ServletException,IOExceptionConnectionconn=null;Statementstat=null;conn=connect(;stat=conn.createStatement(;Stringid2=request.getParameter(id;stat.execute(deletefromstudentwhereid=+id2+;request.getRequestDispatcher(delete.jsp.forward(

25、request,response;信息改正方法publicvoidupdate1(HttpServletRequestrequest,HttpServletResponseresponsethrowsClassNotFoundException,SQLException,ServletException,IOExceptionStringid4=request.getParameter(id;request.setAttribute(result,select(id4,;request.getRequestDispatcher(update1.jsp.forward(request,respo

26、nse;publicvoidupdate(HttpServletRequestrequest,HttpServletResponseresponsethrowsClassNotFoundException,SQLException,ServletException,IOExceptionConnectionconn=null;Statementstat=null;Stringid3=request.getParameter(id;Stringname3=request.getParameter(name;Stringage3=request.getParameter(age;Stringgen

27、der3=request.getParameter(gender;Stringmajor3=request.getParameter(major;conn=connect(;stat=conn.createStatement(;stat.execute(updatestudentsetid=+id3+,name=+name3+,age=+age3+,gender=+gender3+,major=+major3+whereid=+id3+;request.setAttribute(result,select(id3,;request.getRequestDispatcher(update.jsp.forward(request,response;10，在webRoot目錄下增添以下.jsp文件10.1putin.jsp學(xué)生信息輸入學(xué)號：學(xué)號必然為數(shù)字姓名：姓名不可以為空年紀(jì)：年紀(jì)必然為數(shù)字性別：男男女女專業(yè)：專業(yè)不可以為空&name=查察已輸入信息10.2layout.jsp學(xué)生信息所有學(xué)生信息以下;學(xué)號姓名年紀(jì)性別專業(yè)+st.get+st.getN+st.get+st.getGe+st.g