Struts2連接oracle數(shù)據(jù)庫(kù)增刪改查實(shí)例的實(shí)現(xiàn)

Struts2連接oracle數(shù)據(jù)庫(kù)增刪改查實(shí)例的實(shí)現(xiàn)說(shuō)明：我在數(shù)據(jù)庫(kù)創(chuàng)建了觸發(fā)器來(lái)自動(dòng)添加id，所以沒有出現(xiàn)設(shè)定id值。連接oracle數(shù)據(jù)庫(kù)需要class12.jar和oracle14.jar兩個(gè)包，放入lib在數(shù)據(jù)庫(kù)創(chuàng)建users表：createtableusers(idnumbernotnull,usernamevarchar2(20),passwordvarchar2(30))創(chuàng)建序列表users_xh用來(lái)id遞增Createtableusers_xh(xh_idnumber)創(chuàng)建兩個(gè)觸發(fā)器：createorreplacetriggerusers_increasebeforeinsertonusersforeachrowbeginselectxh_id+1into:New.idfromusers_xh;end;和createorreplacetriggerusers_increase_afterafterinsertonusersforeachrowdeclare--localvariablesherebeginupdateusers_xhsetxh_id=xh_id+1;endusers_increase_after;創(chuàng)建類DatabaseTest，內(nèi)容如下：publicclassDatabaseTest{ privatestaticConnectiongetConn(){ Stringdriver="oracle.jdbc.driver.OracleDriver"; Stringurl="jdbc:oracle:thin:@localhost:1521:orcl"; Stringusername="scott";//數(shù)據(jù)庫(kù)用戶名 Stringpassword="tiger";//數(shù)據(jù)庫(kù)用戶密碼 Connectionconn=null; try{ Class.forName(driver); //neworacle.jdbc.driver.OracleDriver(); conn=DriverManager.getConnection(url,username,password); System.out.println("connectsuccess"); }catch(ClassNotFoundExceptione){ e.printStackTrace(); }catch(SQLExceptione){ e.printStackTrace(); } returnconn; }publicstaticvoidmain(String[]args){ getConn(); }}Runasjavaapplication運(yùn)行結(jié)果如下，表示數(shù)據(jù)庫(kù)連接成功！這樣數(shù)據(jù)庫(kù)環(huán)境搭建無(wú)錯(cuò)，可以進(jìn)行下一步。創(chuàng)建webproject結(jié)構(gòu)如下所需的jar包：web.xml內(nèi)容：<?xmlversion="1.0"encoding="UTF-8"?><web-appxmlns:xsi="/2001/XMLSchema-instance"xmlns="/xml/ns/javaee"xmlns:web="/xml/ns/javaee/web-app_2_5.xsd"xsi:schemaLocation="/xml/ns/javaee/xml/ns/javaee/web-app_3_0.xsd"version="3.0"><display-name></display-name><welcome-file-list><welcome-file>index.jsp</welcome-file></welcome-file-list><filter><filter-name>struts2</filter-name><filter-class>org.apache.struts2.dispatcher.ng.filter.StrutsPrepareAndExecuteFilter</filter-class></filter><filter-mapping><filter-name>struts2</filter-name><url-pattern>/*</url-pattern></filter-mapping></web-app>Action配置struts.xml內(nèi)容：<?xmlversion="1.0"encoding="UTF-8"?><!DOCTYPEstrutsPUBLIC"-//ApacheSoftwareFoundation//DTDStrutsConfiguration2.1//EN""/dtds/struts-2.1.dtd"><struts> <!--struts2.0默認(rèn)的配置文件--> <includefile="struts-default.xml"></include> <!--也可以加載其他的配置文件--> <!--<includefile="mystrutsconfig.xml"></include>--> <!--添加package--> <packagename="useraction"extends="struts-default"> <!--配置login.action--> <actionname="login"class="org.struts.useraction.UserAction"method="login"> <resultname="success">/success.jsp</result> <resultname="UserNotExist">/NoUser.jsp</result> <resultname="error">/error.jsp</result> </action> <!--配置register.action--> <actionname="register"class="org.struts.useraction.UserAction"method="register"> <resultname="success">/index.jsp</result> <resultname="error">/error.jsp</result> </action> <!--配置show.action--> <actionname="show"class="org.struts.useraction.UserAction"method="show"> <resultname="success">/show.jsp</result> <resultname="error">/error.jsp</result> </action> <!--配置update.action--> <actionname="update"class="org.struts.useraction.UserAction"method="update"> <resultname="success"type="redirectAction"> <paramname="actionName">show</param> </result> <resultname="error">/error.jsp</result> </action> <!--配置delete.action--> <actionname="delete"class="org.struts.useraction.UserAction"method="delete"> <resultname="success"type="redirectAction"> <paramname="actionName">show</param> </result> <resultname="error">/error.jsp</result> </action> </package></struts>Beans源碼UserBean.java:packageorg.struts.beans;publicclassUserBean{ privateintid; privateStringusername; privateStringpassword; publicintgetId(){ returnid; } publicvoidsetId(intid){ this.id=id; } publicStringgetUsername(){ returnusername; } publicvoidsetUsername(Stringusername){ this.username=username; } publicStringgetPassword(){ returnpassword; } publicvoidsetPassword(Stringpassword){ this.password=password; }}Action源碼UserAction.java:packageorg.struts.useraction;importjava.sql.Connection;importjava.sql.DriverManager;importjava.sql.PreparedStatement;importjava.sql.ResultSet;importjava.sql.SQLException;importjava.util.ArrayList;importjava.util.List;importjava.util.Map;importjavax.servlet.http.HttpServletRequest;importmons.logging.Log;importmons.logging.LogFactory;importorg.struts.beans.UserBean;importcom.opensymphony.xwork2.ActionContext;importcom.opensymphony.xwork2.ActionSupport;publicclassUserActionextendsActionSupport{ privatestaticLoglog=LogFactory.getLog(UserAction.class); privateintid; privateStringusername; privateStringpassword; privateList<UserBean>userlist; privateMapsession; publicintgetId(){ returnid; } publicvoidsetId(intid){ this.id=id; } publicvoidsetSession(Mapsession){ this.session=session; } publicStringgetUsername(){ returnusername; } publicvoidsetUsername(Stringusername){ this.username=username; } publicStringgetPassword(){ returnpassword; } publicvoidsetPassword(Stringpassword){ this.password=password; } publicList<UserBean>getUserlist(){ returnuserlist; } publicvoidsetUserlist(List<UserBean>userlist){ this.userlist=userlist; } /** *建立數(shù)據(jù)庫(kù)連接 * *@return */ privatestaticConnectiongetConn(){ Stringdriver="oracle.jdbc.driver.OracleDriver"; Stringurl="jdbc:oracle:thin:@localhost:1521:orcl"; Stringusername="scott"; Stringpassword="tiger"; Connectionconn=null; try{ Class.forName(driver); //neworacle.jdbc.driver.OracleDriver(); conn=DriverManager.getConnection(url,username,password); //("connectsuccess"); }catch(ClassNotFoundExceptione){ e.printStackTrace(); }catch(SQLExceptione){ e.printStackTrace(); } returnconn; } /** *用戶名與密碼匹配檢查 * *@return結(jié)果標(biāo)識(shí)字符串success表示匹配成功UserNotExist表示用戶不存在error表示匹配失敗 */ privateStringuserCheck(){ ("userchecking..."); Stringname=null; Stringpass=null; Connectionconn=getConn(); Stringsql="select*fromuserswhereusername='"+username+"'"; PreparedStatementpstmt; try{ pstmt=conn.prepareStatement(sql); ResultSetrs=pstmt.executeQuery(); while(rs.next()){ name=rs.getString("username"); pass=rs.getString("password"); } rs.close(); pstmt.close(); conn.close(); }catch(SQLExceptione){ e.printStackTrace(); } if(name==null)//驗(yàn)證用戶名是否存在 return"UserNotExist"; elseif(name.equals(username)&&pass.equals(password))//驗(yàn)證登錄用戶名與密碼是否匹配 return"success"; else return"error"; } privateUserBeanquery(intid){ ("myquery..."); Connectionconn=getConn(); Stringsql="select*fromuserswhereid='"+id+"'"; PreparedStatementpstmt; UserBeanuser=newUserBean(); try{ pstmt=conn.prepareStatement(sql); ResultSetrs=pstmt.executeQuery(); while(rs.next()){ user.setId(id); user.setUsername(rs.getString("username")); user.setPassword(rs.getString("password")); System.out.println("myquery:name:"+rs.getString("username") +"\tpassword:"+rs.getString("password")); } rs.close(); pstmt.close(); conn.close(); }catch(SQLExceptione){ e.printStackTrace(); } returnuser; } /** *查看所有用戶 * *@returnList<UserBean>list */ privateList<UserBean>findAllUser(){ ("getalluserinfo..."); List<UserBean>list=newArrayList<UserBean>(); UserBeanuser; Connectionconn=getConn(); Stringsql="SELECT*FROMusersORDERBYid"; PreparedStatementpstmt; try{ pstmt=conn.prepareStatement(sql); ResultSetrs=pstmt.executeQuery(); while(rs.next()){ user=newUserBean(); user.setId(rs.getInt("id")); user.setUsername(rs.getString("username")); user.setPassword(rs.getString("password")); list.add(user); } }catch(SQLExceptione){ e.printStackTrace(); } returnlist; } /** *將注冊(cè)信息添加到數(shù)據(jù)庫(kù)表users * *@paramusername *@parampassword *@return */ privateintinsert(Stringusername,Stringpassword){ Connectionconn=getConn(); inti=0; Stringsql="insertintousers(username,password)values(?,?)"; PreparedStatementpstmt; try{ pstmt=conn.prepareStatement(sql); //Statementstat=conn.createStatement(); pstmt.setString(1,username); pstmt.setString(2,password); i=pstmt.executeUpdate(); System.out.println("insertresutl:"+i); pstmt.close(); conn.close(); }catch(SQLExceptione){ e.printStackTrace(); } returni; } privatestaticintmyUpdate(intid,StringnewPass){ Connectionconn=getConn(); inti=0; Stringsql="updateuserssetpassword='"+newPass+"'whereid='" +id+"'"; PreparedStatementpstmt; try{ pstmt=conn.prepareStatement(sql); i=pstmt.executeUpdate(); System.out.println("updateresutl:"+i); pstmt.close(); conn.close(); }catch(SQLExceptione){ e.printStackTrace(); } returni; } /** *login.action * *@return *@throwsException */ publicStringlogin()throwsException{ ("login.action..."); if(userCheck().equals("success")){ ActionContext.getContext().getSession() .put("username",getUsername()); returnSUCCESS; }elseif(userCheck().equals("UserNotExist")) return"UserNotExist"; else returnERROR; } /** *register.action * *@return *@throwsException */ publicStringregister()throwsException{ Stringname=getUsername(); Stringpass=getPassword(); inti=insert(name,pass); if(i!=0) return"success"; else return"error"; } publicStringshow()throwsException{ ("show.action..."); userlist=findAllUser(); if(userlist.size()>0) return"success"; else return"error"; } publicStringupdate()throwsException{ ("id="+id); UserBeanuser=query(id); if(null!=user){ setUsername(user.getUsername()); ("username="+username); myUpdate(id,password); return"success"; }else{ return"error"; } } publicStringdelete()throwsException{ Connectionconn=getConn(); inti=0; System.out.println("id="+id); Stringsql="deleteuserswhereid='"+id+"'"; PreparedStatementpstmt; try{ pstmt=conn.prepareStatement(sql); i=pstmt.executeUpdate(); System.out.println("deleteresutl:"+i); pstmt.close(); conn.close(); }catch(SQLExceptione){ e.printStackTrace(); } if(i>0) return"success"; else return"error"; }}index.jsp<%@pagelanguage="java"import="java.util.*" contentType="text/html;charset=GBK"%><% Stringpath=request.getContextPath(); StringbasePath=request.getScheme()+"://" +request.getServerName()+":"+request.getServerPort() +path+"/";%><%@tagliburi="/struts-tags"prefix="s"%><!DOCTYPEHTMLPUBLIC"-//W3C//DTDHTML4.01Transitional//EN"><html><head><basehref="<%=basePath%>"><title>登陸界面</title><metahttp-equiv="pragma"content="no-cache"><metahttp-equiv="cache-control"content="no-cache"><metahttp-equiv="expires"content="0"><metahttp-equiv="keywords"content="keyword1,keyword2,keyword3"><metahttp-equiv="description"content="Thisismypage"><!-- <linkrel="stylesheet"type="text/css"href="styles.css"> --></head><body> <!--提交請(qǐng)求參數(shù)的表單--> <formaction="login"method="post"> <tablealign="center"> <caption> <h3>用戶登錄</h3> </caption> <tr> <!--用戶名的表單域--> <td>用戶名：<inputtype="text"name="username"/> </td> </tr> <tr> <!--密碼的表單域--> <td>密  碼：<inputtype="password"name="password"/> </td> </tr> <tralign="center"> <tdcolspan="2"align="right"><inputtype="submit"value="登錄" name="method:login"/><inputtype="button"value="注冊(cè)" onclick="window.location.href='register.jsp'"/> </td> </tr> </table> </form></body></html>register.jsp<%@pagelanguage="java"import="java.util.*" contentType="text/html;charset=GBK"%><%Stringpath=request.getContextPath();StringbasePath=request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";%><!DOCTYPEHTMLPUBLIC"-//W3C//DTDHTML4.01Transitional//EN"><html><head><basehref="<%=basePath%>"><title>MyJSP'register.jsp'startingpage</title> <metahttp-equiv="pragma"content="no-cache"> <metahttp-equiv="cache-control"content="no-cache"> <metahttp-equiv="expires"content="0"> <metahttp-equiv="keywords"content="keyword1,keyword2,keyword3"> <metahttp-equiv="description"content="Thisismypage"> <!-- <linkrel="stylesheet"type="text/css"href="styles.css"> --></head><body><!--提交請(qǐng)求參數(shù)的表單--> <formaction="register.action"method="post"> <tablealign="center"> <caption> <h3>用戶注冊(cè)</h3> </caption> <tr> <!--用戶名的表單域--> <td>用戶名：<inputtype="text"name="username"/></td> </tr> <tr> <!--密碼的表單域--> <td>密  碼：<inputtype="password"name="password"/></td> </tr> <tralign="center"> <tdcolspan="2"align="right"><inputtype="submit"value="確認(rèn)"/><input type="reset"value="重置"/></td> </tr> </table> </form></body></html>success.jsp<%@pagelanguage="java"import="java.util.*"pageEncoding="utf-8"%><%Stringpath=request.getContextPath();StringbasePath=request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";%><!DOCTYPEHTMLPUBLIC"-//W3C//DTDHTML4.01Transitional//EN"><html><head><basehref="<%=basePath%>"><title>MyJSP'success.jsp'startingpage</title> <metahttp-equiv="pragma"content="no-cache"> <metahttp-equiv="cache-control"content="no-cache"> <metahttp-equiv="expires"content="0"> <metahttp-equiv="keywords"content="keyword1,keyword2,keyword3"> <metahttp-equiv="description"content="Thisismypage"> <!-- <linkrel="stylesheet"type="text/css"href="styles.css"> --></head><body>Thisissuccesspage.<br><br/><br/><ahref="show.action">顯示用戶列表</a></body></html>show.jsp<%@pagelanguage="java"contentType="text/html;charset=UTF-8"pageEncoding="UTF-8"%><%@tagliburi="/struts-tags"prefix="s"%><!DOCTYPEHTMLPUBLIC"-//W3C//DTDHTML4.01Transitional//EN"><html><head><title>Userslist</title><styletype="text/css"> table{ border-collapse:collapse; }</style></head><body> <br/><br/><br/><br/><tableborder="1"width="50%"align="center"> <trbgcolor="#cccc00"> <tdalign="center">UserId</td> <tdalign="center">UserName</td> <tdalign="center">Password</td> <tdcolspan="2"align="center">Options</td> </tr> <s:iteratorvalue="userlist"> <tr> <tdalign="center"><s:propertyvalue="id"/></td> <tdalign="center"><s:propertyvalue="username"/></td> <tdalign="center"> <s:propertyvalue="password"/></td> <tdalign="center"><ahref="update.jsp?id=<s:propertyvalue='id'/>&username=<s:propertyvalue='username'/>">update</a></td> <tdalign="center">