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

1、使用JSP+SERVLET+JDBC實(shí)現(xiàn)對數(shù)據(jù)庫的增刪改查首先，打開sql*plus，輸入用戶名(我用的 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，打開 myeclipse (我用的是 myeclipse8.5 )新建一個(gè) web projectlili Edit Itcltm曲*tlripU

2、u販雨卜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-154u1 亡iNviTiihrs-L3 FotivrUw 飯時(shí)|郁ToF FiW+P卜話 HI丘 l?irK*d TrmpJrin；應(yīng) AP iAdAiiuiid TrTliii| XMTM-!Krf TfimpUlKl注 Insert.皿 IjqpMT.-耳 EJB1 SiitutiK HimP

3、enpniAic+rwLK EJB3 Enti BetEJB3 Mosagr &Ekwnl 附m網(wǎng)i|述 XML i AdaiTKd rMTidaivLi過 XML iSMir Tempi占 rT|diia+ErmiBai.5 SMk4 TiwnflihhJUtian.Ji申申3呼GJ旦 MabLu Aorm鼻 LNL1 MndljrLlJ1111-1 JII HR 13鬥理s-。亨譽(yù)廣 a， ui fl s * ae? _ ta i I響卻10 Tepiweiciiod撲點(diǎn)號1-1Cgi ChrfH 曰 ,E h*DpCIFd L-lj 口7匡|鄭遇鶯口 丹擊MHlAtlurilAz 1 E

4、 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 再選擇 class7,輸入名字Page完整的Page.java代碼如下package bea n;public class Page private int totalPage;private int curre ntPage;private int t

5、otalRecord;private int curre ntRecord;private int pageSize=8;/獲得和設(shè)置當(dāng)前頁public int getCurre ntPage()retur n curre ntPage;public void setCurre ntPage(i nt curre ntRecord,i nt pageSize) if(curre ntRecord%pageSize=0)curre ntPage=curre ntRecord/pageSize;elsecurre ntPage=curre ntRecord/pageSize+1;/獲得和設(shè)置當(dāng)前記

6、錄public int getCurre ntRecord()retur n curre ntRecord;public void setCurre ntRecord(i nt curre ntRecord)this.curre ntRecord=curre ntRecord;/獲得和設(shè)置每頁記錄數(shù)量public int getPageSize()return pageSize;public void setPageSize(int pageSize)this.pageSize=pageSize;/獲得和設(shè)置總頁數(shù)public int getTotalPage()return totalPag

7、e;public void setTotalPage(int totalRecord,int pageSize)if(totalRecord%pageSize=0)totalPage=totalRecord/pageSize;else totalPage=totalRecord/pageSize+1;/獲得和設(shè)置總記錄public int getTotalRecord()return totalRecord;public void setTotalRecord(int totalRecord) this.totalRecord=totalRecord;8,用相同的方法建一個(gè) StudentIn

8、fo 類 完整的 StudentInfo.java 代碼如下 package bean;public class StudentInfo private int id;/學(xué)號private String name;/姓名private int age;/年齡private String gender;/性別private String major;/專業(yè)public StudentInfo()public StudentInfo(int id,String name,int age,String gender,String major)this.id=id;=name;thi

9、s.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)=name;public int getAge()return age;public void setAge(int age)this.age=age;public String getGender()return gender

10、;public void setGender(String gender)this.gender=gender;public String getMajor()return major;public void setMajor(String major)this.major=major;AllServlet 類9，在 src 目錄下添加另一個(gè)包 dbservlet 在該包中建立一個(gè) 完整的 AllServlet.java 代碼如下package dbservlet;import java.io.IOException;import java.sql.Connection;import java

11、.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.ServletException;import javax.servlet.http.HttpServlet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.Http

12、ServletResponse;import bean.Page;import bean.StudentInfo;public class AllServlet extends HttpServlet /*/private static final long serialVersionUID = 1L;/doPost 方法public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException request.setCharacterEnco

13、ding(UTF-8); response.setCharacterEncoding(UTF-8);String methodName=request.getParameter(methodName);int method=Integer.parseInt(methodName);try switch(method)case 0:insert(request,response);case 1:difpage(request,response);break;case 2:delete(request,response);break;case 3:update(request,response);

14、break;case 4:update1(request,response); break;case 5: dispatch(request,response); break; catch (ClassNotFoundException e) / TODO Auto-generated catch blocke.printStackTrace(); catch (SQLException e) / TODO Auto-generated catch block e.printStackTrace();/doGet 方法public void doGet(HttpServletRequest r

15、equest, HttpServletResponse response) throws ServletException, IOException doPost(request,response);/數(shù)據(jù)庫連接方法public Connection connect() throws ClassNotFoundException, SQLException Connection conn=null;Class.forName(oracle.jdbc.driver.OracleDriver);String url=jdbc:oracle:thin:localhost:1521:orcl;Stri

16、ng user=scott;String password=tiger;conn=DriverManager.getConnection(url,user,password);return conn;/關(guān)閉數(shù)據(jù)庫資源public void close(Statement stat,Connection conn) throws SQLException if(stat!=null)stat.close();if(conn!=null)conn.close();/插入方法throwspublic void insert(HttpServletRequest request, HttpServle

17、tResponse response) ClassNotFoundException, SQLExceptionConnection conn=null;Statement stat=null;String 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=co

18、nnect();stat=conn.createStatement();stat.execute(insert 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, SQLExceptionConnection conn=null;Statement stat=null;ResultSet

19、 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(select * from student where id=+id+);if(id=&name!=)rs=stat.executeQuery(select * from student where name=+name+); if(id!=

20、&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(age);st.setGender(rs.getString(gender);st.setMajor(rs.getString(major);result.add(st); if(rs!=nul

21、l) rs.close();close(stat,conn); return result;/條件查詢跳轉(zhuǎn)public void dispatch(HttpServletRequest request, HttpServletResponse response) throws ClassNotFoundException, SQLException, ServletException, IOExceptionString id5=request.getParameter(id);String name5=request.getParameter(name); if(select(id5,nam

22、e5).isEmpty() request.getRequestDispatcher(selectnothing.jsp).forward(request, response); else request.setAttribute(result, select(id5,name5); request.getRequestDispatcher(idnameselect.jsp).forward(request, response);/設(shè)置分頁相關(guān)參數(shù)方法public Page setpage(HttpServletRequest request, HttpServletResponse resp

23、onse) throws ClassNotFoundException, SQLExceptionString crd=request.getParameter(currentRecord);/String id=request.getParameter(id);/ String name=request.getParameter(name);ArrayList result=select(,);Page pager=new Page(); pager.setTotalRecord(result.size(); pager.setTotalPage(result.size(),pager.ge

24、tPageSize(); if(crd!=null)int currentRecord=Integer.parseInt(crd); pager.setCurrentRecord(currentRecord); pager.setCurrentPage(currentRecord,pager.getPageSize();return pager;/獲得分頁顯示的子集public void difpage(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException,

25、ClassNotFoundException, SQLException/ String id=request.getParameter(id);/ String name=request.getParameter(name);ArrayList result=select(,);Page pager=new Page(); pager=setpage(request,response); List subResult=null;int currentRecord=pager.getCurrentRecord(); if(currentRecord=0)if(pager.getTotalRec

26、ord()8) subResult=(List) result.subList(0,pager.getTotalRecord(); elsesubResult=(List) result.subList(0,pager.getPageSize();else if(pager.getCurrentRecord()+pager.getPageSize()result.size() subResult=(List) result.subList(pager.getCurrentRecord(),pager.getCurrentRecord()+pager.getPageSize(); else su

27、bResult=(List) result.subList(pager.getCurrentRecord(),result.size(); request.setAttribute(pager, pager); request.setAttribute(subResult, subResult); request.getRequestDispatcher(layout.jsp).forward(request, response); /信息刪除方法 public void delete(HttpServletRequest request, HttpServletResponse respon

28、se) throws ClassNotFoundException, SQLException, ServletException, IOExceptionConnection conn=null;Statement stat=null; conn=connect(); stat=conn.createStatement();String id2=request.getParameter(id); stat.execute(delete from student where id=+id2+); request.getRequestDispatcher(delete.jsp).forward(

29、request, response);/信息修改方法public void update1(HttpServletRequest request, HttpServletResponse response) throws ClassNotFoundException, SQLException, ServletException, IOExceptionString id4=request.getParameter(id); request.setAttribute(result, select(id4,); request.getRequestDispatcher(update1.jsp).

30、forward(request, response);throwsset 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(nam

31、e);String 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=+age3+,gender=+gender3+,major=+major3+ id=+id3+);request.setAttribute(result

32、, select(id3,); request.getRequestDispatcher(update.jsp).forward(request, response);10，在 webRoot 目錄下添加以下 .jsp 文件10.1 putin.jsp base href= 學(xué)生信息輸入 !-function validate()var id=document.forms0.id.value;var name=.value;var age=document.forms0.age.value;var major=document.forms0.major.

33、value;if(id=0)alert( 學(xué)號不能為空，請輸入學(xué)號！ );return false;else if(name.length=0)alert( 姓名不能為空，請輸入姓名！ );return false;else if(age=0)alert( 請輸入合法年齡！ );return false;else if(major.length=0)alert( 專業(yè)不能為空，請輸入所學(xué)專業(yè)！);return false;elsereturn true;/document.getElementById(form).submit(); 學(xué)生信息輸入 學(xué)號： 姓名： 年齡： 性別： 男 女 專業(yè)：

34、 a href=AllServlet?methodName=&id=&name= 查看已輸 入信息 10.2 layout.jspbase href= 學(xué)生信息 !-function confirmdialog()if(window.confirm( 您確定要?jiǎng)h除此條信息？ )return true;else/ alert( 取消刪除！ );return false; 學(xué)生信息 全部學(xué)生信息如下 學(xué)號 姓名 年齡 性別 專業(yè) %response.setCharacterEncoding(UTF-8); request.setCharacterEncoding(UTF-8); Page page

35、r=(Page)request.getAttribute(pager);List subResult=(List)request.getAttribute(subResult); if(!subResult.isEmpty() for(int i=0;isubResult.size();i+)StudentInfo st=subResult.get(i);out.print();out.print(+st.getId()+); out.print(+st.getName()+); out.print(+st.getAge()+); out.print(+st.getGender()+); ou

36、t.print(+st.getMajor()+);%a href=AllServlet?id=&methodName= onclick=return confirmdialog() 刪除 ahref=AllServlet?id=&name=&methodName= 修 改 %out.print();% 總 條 記 錄 | 總 頁|當(dāng)前 頁 |每頁 條|%int last=pager.getCurrentRecord()-pager.getPageSize();int next=pager.getCurrentRecord()+pager.getPageSize();int currentRecord; if(last0) out.println( 首頁 |);elseout.print( 上 一 頁