使用JSPSERVLETJDBC实现对数据库的增删改查精.docx
《使用JSPSERVLETJDBC实现对数据库的增删改查精.docx》由会员分享，可在线阅读，更多相关《使用JSPSERVLETJDBC实现对数据库的增删改查精.docx（21页珍藏版）》请在冰豆网上搜索。
使用JSPSERVLETJDBC实现对数据库的增删改查精
使用JSP+SERVLET+JDBC实现对数据库的增删改查
首先，打开sql*plus，输入用户名（我用的scott）密码（我设置的是tiger）。
先建个表student，
Createtablestudent（
idnumber（30notnullprimarykey,
namevarchar（50,
agenumber（30,
gendervarchar（30,
majorvarchar（50;
1，打开myeclipse（我用的是myeclipse8.5）新建一个webproject
2，在projectname中输入合法名字，比如normal
3，新建的normal工程
4，在src目录下建一个包，右击src选择new在选择package
5，输入合法名字比如bean
6，在bean目录下建一个class，右击bean选择new再选择class
7，输入名字Page
完整的Page.java代码如下
packagebean;
publicclassPage{
privateinttotalPage;
privateintcurrentPage;
privateinttotalRecord;
privateintcurrentRecord;
privateintpageSize=8;
//获得和设置当前页
publicintgetCurrentPage（{
returncurrentPage;
}
publicvoidsetCurrentPage（intcurrentRecord,intpageSize{
if（currentRecord%pageSize==0{
currentPage=currentRecord/pageSize;
}
else{
currentPage=currentRecord/pageSize+1;
}
}
//获得和设置当前记录
publicintgetCurrentRecord（{
returncurrentRecord;
}
publicvoidsetCurrentRecord（intcurrentRecord{
this.currentRecord=currentRecord;
}
//获得和设置每页记录数量
publicintgetPageSize（{
returnpageSize;
}
publicvoidsetPageSize（intpageSize{
this.pageSize=pageSize;
}
//获得和设置总页数
publicintgetTotalPage（{
returntotalPage;
}
publicvoidsetTotalPage（inttotalRecord,intpageSize{
if（totalRecord%pageSize==0{
totalPage=totalRecord/pageSize;
}
else{
totalPage=totalRecord/pageSize+1;
}
}
//获得和设置总记录
publicintgetTotalRecord（{
returntotalRecord;
}
publicvoidsetTotalRecord（inttotalRecord{
this.totalRecord=totalRecord;
}
}
8,用相同的方法建一个StudentInfo类
完整的StudentInfo.java代码如下
packagebean;
publicclassStudentInfo{
privateintid;//学号
privateStringname;//姓名
privateintage;//年龄
privateStringgender;//性别
privateStringmajor;//专业
publicStudentInfo（{
}
publicStudentInfo（intid,Stringname,intage,Stringgender,Stringmajor{
this.id=id;
this.name=name;
this.age=age;
this.gender=gender;
this.major=major;
}
publicintgetId（{
returnid;
}
publicvoidsetId（intid{
this.id=id;
}
publicStringgetName（{
returnname;
}
publicvoidsetName（Stringname{
this.name=name;
}
publicintgetAge（{
returnage;
}
publicvoidsetAge（intage{
this.age=age;
}
publicStringgetGender（{
returngender;
}
publicvoidsetGender（Stringgender{
this.gender=gender;
}
publicStringgetMajor（{
returnmajor;
}
publicvoidsetMajor（Stringmajor{
this.major=major;
}
}
9，在src目录下添加另一个包dbservlet在该包中建立一个AllServlet类
完整的AllServlet.java代码如下
packagedbservlet;
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.HttpServlet;
importjavax.servlet.http.HttpServletRequest;
importjavax.servlet.http.HttpServletResponse;
importbean.Page;
importbean.StudentInfo;
publicclassAllServletextendsHttpServlet{
/**
*
*/
privatestaticfinallongserialVersionUID=1L;
//doPost方法
publicvoiddoPost（HttpServletRequestrequest,HttpServletResponseresponse
throwsServletException,IOException{
request.setCharacterEncoding（"UTF-8";
response.setCharacterEncoding（"UTF-8";
StringmethodName=request.getParameter（"methodName";
intmethod=Integer.parseInt（methodName;
try{
switch（method
{
case0:
insert（request,response;
case1:
difpage（request,response;
break;
case2:
delete（request,response;
break;
case3:
update（request,response;
break;
case4:
update1（request,response;
break;
case5:
dispatch（request,response;
break;
}
}catch（ClassNotFoundExceptione{
//TODOAuto-generatedcatchblock
e.printStackTrace（;
}catch（SQLExceptione{
//TODOAuto-generatedcatchblock
e.printStackTrace（;
}
}
//doGet方法
publicvoiddoGet（HttpServletRequestrequest,HttpServletResponseresponse
throwsServletException,IOException{
doPost（request,response;
}
//数据库连接方法
publicConnectionconnect（throwsClassNotFoundException,SQLException{
Connectionconn=null;
Class.forName（"oracle.jdbc.driver.OracleDriver";
Stringurl="jdbc:
oracle:
thin:
@localhost:
1521:
orcl";
Stringuser="scott";
Stringpassword="tiger";
conn=DriverManager.getConnection（url,user,password;
returnconn;
}
//关闭数据库资源
publicvoidclose（Statementstat,ConnectionconnthrowsSQLException{
if（stat!
=null{
stat.close（;
}
if（conn!
=null{
conn.close（;
}
}
//插入方法
publicvoidinsert（HttpServletRequestrequest,HttpServletResponseresponsethrowsClassNotFoundException,SQLException{
Connectionconn=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（"insertintostudent（id,name,age,gender,majorvalues（"+id+",'"+name+"',"+age+",'"+gender+"','"+major+"'";
close（stat,conn;
}
//查询方法
publicArrayListselect（Stringid,StringnamethrowsClassNotFoundException,SQLException{
Connectionconn=null;
Statementstat=null;
ResultSetrs=null;
conn=connect（;
stat=conn.createStatement（;
ArrayListresult=newArrayList（;
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（
{
StudentInfost=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!
=null{
rs.close（;
}
close（stat,conn;
returnresult;
}
//条件查询跳转
publicvoiddispatch（HttpServletRequestrequest,HttpServletResponseresponsethrowsClassNotFoundException,SQLException,ServletException,IOException{
Stringid5=request.getParameter（"id";
Stringname5=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;
}
}
//设置分页相关参数方法
publicPagesetpage（HttpServletRequestrequest,HttpServletResponseresponsethrowsClassNotFoundException,SQLException{
Stringcrd=request.getParameter（"currentRecord";
//Stringid=request.getParameter（"id";
//Stringname=request.getParameter（"name";
ArrayListresult=select（"","";
Pagepager=newPage（;
pager.setTotalRecord（result.size（;
pager.setTotalPage（result.size（,pager.getPageSize（;
if（crd!
=null
{
intcurrentRecord=Integer.parseInt（crd;
pager.setCurrentRecord（currentRecord;
pager.setCurrentPage（currentRecord,pager.getPageSize（;
}
returnpager;
}
//获得分页显示的子集
publicvoiddifpage（HttpServletRequestrequest,HttpServletResponseresponsethrowsServletException,IOException,ClassNotFoundException,SQLException{
//Stringid=request.getParameter（"id";
//Stringname=request.getParameter（"name";
ArrayListresult=select（"","";
Pagepager=newPage（;
pager=setpage（request,response;
ListsubResult=null;
intcurrentRecord=pager.getCurrentRecord（;
if（currentRecord==0{
if（pager.getTotalRecord（<8{
subResult=（Listresult.subList（0,pager.getTotalRecord（;
}
else{
subResult=（Listresult.subList（0,pager.getPageSize（;
}
}
elseif（pager.getCurrentRecord（+pager.getPageSize（
{
subResult=（Listresult.subList（pager.getCurrentRecord（,pager.getCurrentRecord（+pager.getPageSize（;
}
else
{
subResult=（Listresult.subList（pager.getCurrentRecord（,result.size（;
}
request.setAttribute（"pager",pager;
request.setAttribute（"subResult",subResult;
request.getRequestDispatcher（"layout.jsp".forward（request,response;
}
//信息删除方法
publicvoiddelete（HttpServletRequestrequest,HttpServletResponseresponsethrowsClassNotFoundException,SQLException,ServletException,IOException{
Connectionconn=null;
Statementstat=null;
conn=connect（;
stat=conn.createStatement（;
Stringid2=request.getParameter（"id";
stat.execute（"deletefromstudentwhereid="+id2+"";
request.getRequestDispatcher（"delete.jsp".forward（request,response;
}
//信息修改方法
publicvoidupdate1（HttpServletRequestrequest,HttpServletResponseresponsethrowsClassNotFoundException,SQLException,ServletException,IOException{
Stringid4=request.getParameter（"id";
request.setAttribute（"result",select（id4,"";
request.getRequestDispatcher（"update1.jsp".forward（request,response;
}
publicvoidupdate（HttpServletRequestrequest,HttpServletResponseresponsethrowsClassNotFoundException,SQLException,ServletException,IOException{
Connectionconn=null;
Statementstat=null;
Stringid3=request.getParameter（"id";
Stringname3=request.getParameter（"name";
Stringage3=request.getParameter（"age";
Stringgender3=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
<%@pagelanguage="java"import="java.util.*"pageEncoding="UTF-8"%>
<%
Stringpath=request.getContextPath（;
StringbasePath=request.getScheme（+":
//"+request.getServerName（+":
"+request.getServerPort（+path+"/";
%>
学生信息输入
学号：
学号必须为数字">
姓名：
姓名不能为空">
年龄：
年龄必须为数字">
性别：
男">男
女">女
专业：
专业不能为空">
提交"/>
&name=<%=""%>">查看已输入信息
10.2layout.jsp
<%@pagelanguage="java"import="java.util.*"pageEncoding="UTF-8"%>
<
升级会员 