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

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

2、，在src目录下建一个包，右击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(ret

3、urn currentPage;public void setCurrentPage(int currentRecord,int pageSizeif(currentRecord%pageSize=0currentPage=currentRecord/pageSize;elsecurrentPage=currentRecord/pageSize+1;/获得和设置当前记录public int getCurrentRecord(return currentRecord;public void setCurrentRecord(int currentRecordthis.currentRecord=

4、currentRecord;/获得和设置每页记录数量public int getPageSize(return pageSize;public void setPageSize(int pageSizethis.pageSize=pageSize;/获得和设置总页数public int getTotalPage(return totalPage;public void setTotalPage(int totalRecord,int pageSizeif(totalRecord%pageSize=0totalPage=totalRecord/pageSize;elsetotalPage=tot

5、alRecord/pageSize+1;/获得和设置总记录public int getTotalRecord(return totalRecord;public void setTotalRecord(int totalRecordthis.totalRecord=totalRecord;8,用相同的方法建一个StudentInfo类完整的StudentInfo.java代码如下package bean;public class StudentInfo private int id; /学号private String name; /姓名private int age; /年龄private

6、String gender; /性别private String major; /专业public StudentInfo(public StudentInfo(int id,String name,int age,String gender,String majorthis.id=id;=name;this.age=age;this.gender=gender;this.major=major;public int getId(return id;public void setId(int idthis.id=id;public String getName(return

7、name;public void setName(String =name;public int getAge(return age;public void setAge(int agethis.age=age;public String getGender(return gender;public void setGender(String genderthis.gender=gender;public String getMajor(return major;public void setMajor(String majorthis.major=major;9，在

8、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;import java.util.ArrayList;import java.util.List;import

9、 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 /* */private static final long serialVersionUID = 1L;/

10、doPost方法public void doPost(HttpServletRequest request, HttpServletResponse responsethrows ServletException, IOException request.setCharacterEncoding("UTF-8"response.setCharacterEncoding("UTF-8"String methodName=request.getParameter("methodName"int method=Integer.parseIn

11、t(methodName;try switch(methodcase 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-gen

12、erated catch blocke.printStackTrace(; catch (SQLException e / TODO Auto-generated catch blocke.printStackTrace(; /doGet方法public void doGet(HttpServletRequest request, HttpServletResponse responsethrows ServletException, IOException doPost(request,response;/数据库连接方法public Connection connect( throws Cl

13、assNotFoundException, SQLExceptionConnection 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 conn

14、;/关闭数据库资源public void close(Statement stat,Connection conn throws SQLExceptionif(stat!=nullstat.close(;if(conn!=nullconn.close(;/插入方法public void insert(HttpServletRequest request, HttpServletResponse response throws ClassNotFoundException, SQLExceptionConnection conn=null;Statement stat=null;String i

15、d=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(;stat.execute("ins

16、ert into student(id,name,age,gender,major values("+id+",'"+name+"',"+age+",'"+gender+"','"+major+"'" close(stat,conn;/查询方法public ArrayList select(String id,String name throws ClassNotFoundException, SQLException Connectio

17、n conn=null;Statement stat=null;ResultSet rs=null;conn=connect(;stat=conn.createStatement(;ArrayList result=new ArrayList (; if(id=""&&name=""rs=stat.executeQuery("select * from student" if(id!=""&&name=""rs=stat.executeQuery("se

18、lect * from student where id="+id+"" if(id=""&&name!=""rs=stat.executeQuery("select * from student where name='"+name+"'" if(id!=""&&name!=""rs=stat.executeQuery("select * from student where id=&q

19、uot;+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("age"st.setGender(rs.getString("gender"st.setMajor(rs.getString("major"

20、result.add(st; if(rs!=nullrs.close(;close(stat,conn;return result;/条件查询跳转public void dispatch(HttpServletRequest request, HttpServletResponse response throws ClassNotFoundException, SQLException, ServletException, IOExceptionString id5=request.getParameter("id"String name5=request.getParam

21、eter("name" if(select(id5,name5.isEmpty(request.getRequestDispatcher("selectnothing.jsp".forward(request, response;elserequest.setAttribute("result", select(id5,name5;request.getRequestDispatcher("idnameselect.jsp".forward(request, response; /设置分页相关参数方法public

22、Page setpage(HttpServletRequest request, HttpServletResponse response throws ClassNotFoundException, SQLExceptionString crd=request.getParameter("currentRecord"/String id=request.getParameter("id"/ String name=request.getParameter("name"ArrayList result=select("&qu

23、ot;,"" Page pager=new Page(;pager.setTotalRecord(result.size(; pager.setTotalPage(result.size(,pager.getPageSize(;if(crd!=nullint currentRecord=Integer.parseInt(crd;pager.setCurrentRecord(currentRecord;pager.setCurrentPage(currentRecord,pager.getPageSize(;return pager;/获得分页显示的子集public void

24、 difpage(HttpServletRequest request, HttpServletResponse response throws ServletException, IOException, ClassNotFoundException, SQLException/ String id=request.getParameter("id"/ String name=request.getParameter("name"ArrayList result=select("","" Page pager=n

25、ew Page(;pager=setpage(request,response;List subResult=null; int currentRecord=pager.getCurrentRecord(;if(currentRecord=0if(pager.getTotalRecord(<8subResult=(List result.subList(0,pager.getTotalRecord(; elsesubResult=(List result.subList(0,pager.getPageSize(; else if(pager.getCurrentRecord(+pager

26、.getPageSize( subResult=(List result.subList(pager.getCurrentRecord(,pager.getCurrentRecord(+pager.getPageSize(; elsesubResult=(List result.subList(pager.getCurrentRecord(,result.size(; request.setAttribute("pager", pager;request.setAttribute("subResult", subResult;request.getReq

27、uestDispatcher("layout.jsp".forward(request, response;/信息删除方法public void delete(HttpServletRequest request, HttpServletResponse response throws ClassNotFoundException, SQLException, ServletException, IOExceptionConnection conn=null;Statement stat=null;conn=connect(;stat=conn.createStatemen

28、t(;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, HttpServletResponse response throws ClassNotFo

29、undException, SQLException, ServletException, IOExceptionString id4=request.getParameter("id"request.setAttribute("result", select(id4,""request.getRequestDispatcher("update1.jsp".forward(request, response; public void update(HttpServletRequest request, HttpSe

30、rvletResponse response throws ClassNotFoundException, SQLException, ServletException, IOExceptionConnection conn=null;Statement stat=null;String id3=request.getParameter("id"String name3=request.getParameter("name"String age3=request.getParameter("age"String gender3=req

31、uest.getParameter("gender"String major3=request.getParameter("major"conn=connect(;stat=conn.createStatement(;stat.execute("update student set id="+id3+",name='"+name3+"',age="+age3+",gender='"+gender3+"',major='&quo

32、t;+major3+"' where id="+id3+""request.setAttribute("result", select(id3,"" request.getRequestDispatcher("update.jsp".forward(request, response; 10，在webRoot目录下添加以下.jsp文件10.1 putin.jsp<% page language="java" import="java.util.*&qu

33、ot; pageEncoding="UTF-8"%><%String path = request.getContextPath(;String basePath = request.getScheme(+":/"+request.getServerName(+":"+request.getServerPort(+path+"/"%>学生信息输入学号：学号必须为数字">姓名：姓名不能为空">年龄：年龄必须为数字">性别： 男">男 女

34、">女专业：专业不能为空">提交"/>&name=<%="" %>">查看已输入信息10.2 layout.jsp<% page language="java" import="java.util.*" pageEncoding="UTF-8"%><% page import="bean.StudentInfo" %><% page import="bean.Page&qu

35、ot; %><%String path = request.getContextPath(;String basePath = request.getScheme(+":/"+request.getServerName(+":"+request.getServerPort(+path+"/"%>学生信息全部学生信息如下<%response.setCharacterEncoding("UTF-8"request.setCharacterEncoding("UTF-8"Pag

36、e pager=(Pagerequest.getAttribute("pager"List subResult=(List request.getAttribute("subResult" if(!subResult.isEmpty(for(int i=0;i StudentInfo st=subResult.get(i;out.print("out.print("out.print("out.print("out.print("out.print("%><% out.print(&

37、quot;%>" " " " " " " 学号姓名年龄性别专业"+st.getId(+""+st.getName(+""+st.getAge(+""+st.getGender(+""+st.getMajor(+"删除&methodName=<%=4 %>">修改总<%=pager.getTotalRecord( %>条记录|总<%=pager.getTotalPage

38、( %>页|当前<%=pager.getCurrentPage(+1 %>页|每页<%=pager.getPageSize( %>条|<%int last=pager.getCurrentRecord(-pager.getPageSize(;int next=pager.getCurrentRecord(+pager.getPageSize(;int currentRecord;if(last<0out.println("首页|"elseout.print("上一页|"if(next>=pager.getT

39、otalRecord(out.println("尾页|"elseout.print("下一页|"%>按学号姓名查询:学号：学号必须为数字" >姓名：查询" />返回信息输入页面10.3 update1.jsp<% page language="java" import="java.util.*" pageEncoding="UTF-8"%><% page import="bean.StudentInfo" %>&l

40、t;%String path = request.getContextPath(;String basePath = request.getScheme(+":/"+request.getServerName(+":"+request.getServerPort(+path+"/"%>学生信息要修改的学生信息如下<%int id=0;ArrayList result=new ArrayList (; result=(ArrayList request.getAttribute("result" if(!

41、result.isEmpty(for(int i=0;i StudentInfo st=result.get(i;id=st.getId(;out.print("out.print("out.print("out.print("out.print("out.print("out.print("%>" " " " " " " 学号姓名年龄性别专业"+st.getId(+""+st.getName(+""

42、+st.getAge(+""+st.getGender(+""+st.getMajor(+"将学生信息更改为：学号：<%=id %>学号不能改变">姓名：姓名不能为空">年龄：年龄不能为空">性别： 男">男 女">女专业：专业不能为空">修改"/>返回信息输入页面&id=<%="" %>&name=<%="" %>>返回信息查询页面10

43、.4 update.jsp<% page language="java" import="java.util.*" pageEncoding="UTF-8"%><% page import="bean.StudentInfo" %><%String path = request.getContextPath(;String basePath = request.getScheme(+":/"+request.getServerName(+":"+

44、request.getServerPort(+path+"/"%>修改后的信息为：<%response.setCharacterEncoding("UTF-8"request.setCharacterEncoding("UTF-8"ArrayList result=new ArrayList (; result=(ArrayList request.getAttribute("result" if(!result.isEmpty(for(int i=0;i StudentInfo st=result.g

45、et(i;out.print("out.print("out.print("out.print("out.print("out.print("out.print("%>" " " " " " " 学号姓名年龄性别专业"+st.getId(+""+st.getName(+""+st.getAge(+""+st.getGender(+""+st.getMajor(+

46、"返回信息输入页面&id=<%="" %>&name=<%="" %>>返回信息查询页面10.5 deldete.jsp<% page language="java" import="java.util.*" pageEncoding="UTF-8"%><%String path = request.getContextPath(;String basePath = request.getScheme(+":/&

47、quot;+request.getServerName(+":"+request.getServerPort(+path+"/"%><%out.print("删除成功！" %>返回信息输入页面 &id=<%="" %>&name=<%="" %>>返回信息查询页面10.6 idnameselect.jsp<% page language="java" import="java.util.*" pageEncoding="UTF-8"%><% page import="bean.StudentInfo" %><%String path = request.getCont