使用JSPSERVLETJDBC实现对数据库增删改查

1、使用JSPSERVLETJDBC实现对数据库的增改正查(精)使用JSPSERVLETJDBC实现对数据库的增改正查(精)29/29使用JSPSERVLETJDBC实现对数据库的增改正查(精)使用JSP+SERVLET+JDBC实现对数据库的增改正查第一，翻开sql*plus，输入用户名（我用的scott）密码（我设置的是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;获取和设置目前页publicintgetCurrentPage(returncurrentPage;publicvoidsetCurrentPage(intcurrentRecord,intpageSizeif(currentRecord%pageSize=0currentPage=currentRecord/pageSize;elsecurrentPage=currentRecord/pageSize+1;获取和设置目前记录publicintgetCurrentRecord(returncurrentRecord;publicvoidsetC

4、urrentRecord(intcurrentRecordthis.currentRecord=currentRecord;获取和设置每页记录数目publicintgetPageSize(returnpageSize;publicvoidsetPageSize(intpageSizethis.pageSize=pageSize;获取和设置总页数publicintgetTotalPage(returntotalPage;publicvoidsetTotalPage(inttotalRecord,intpageSizeif(totalRecord%pageSize=0totalPage=total

5、Record/pageSize;elsetotalPage=totalRecord/pageSize+1;获取和设置总记录publicintgetTotalRecord(returntotalRecord;publicvoidsetTotalRecord(inttotalRecordthis.totalRecord=totalRecord;8,用同样的方法建一个StudentInfo类圆满的StudentInfo.javapackagebean;publicclassStudentInfo代码以下privateintid;/学号privateStringname;/姓名privateintag

6、e;/年纪privateStringgender;/性别privateStringmajor;/publicStudentInfo(专业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;数据库连结方法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;封闭数据库资源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;条件查问跳转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;设置分页有关参数方法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学生信息输入学号：学号必然为数字姓名：姓名不可以为空年纪：年纪必然为数字性别：男男女女专业：专业不可以为空&name=查察已输入信息10.2layout.jsp学生信息所有学生信息以下;学号姓名年纪性别专业+st.get+st.getN+st.get+st.getGe+st.g