課件課后練習(xí)代碼-經(jīng)典案例

JDBC&XMLDMSDAO原型——ServiceDAO的findAll方DMSDAO原型——BaseDAO類及ServiceDAO的validateDMSDAO原型——ServiceDetailDAO的save方法和findAllDMSDAO原型——ServiceDAO的findAllCREATETABLE CONSTRAINTSERVICE_ID_PKPRIMARY NOT NOTNULL, NOTNULL,NOTNOTToNOTaltersessionsetnls_date_format='yyyymmdd'12345',0,'20090310'12345',0,'20090301'12345',0,'20090301 '12345',0,'20090301INSERTINTOSERVICEVALUES'12345',0,'2012021023INSERTINTOSERVICEVALUES'12345',0,'2012021000INSERTINTOSERVICEVALUES'12345',0,'20120210INSERTINTOSERVICEVALUES'12345',0,'20120211INSERTINTOSERVICEVALUES'12345',0,'20120212'12345',0,'20120212INSERTINTOSERVICEVALUES'12345',0,'20120211INSERTSERVICEVALUES'12345',0,'20120211INSERTSERVICEVALUES'12345',0,'20120212INSERTSERVICEVALUES'12345',0,'20120213INSERTSERVICEVALUES'12345',0,'20120214INSERTSERVICEVALUES'12345',0,'20120215con="jdbc:oracle:thin:@localhost:1521:ORCL","scott",stmt=rs=stmt.executeQuery("selectID,ACCOUNT_ID,UNIX_HOST,LOGIN_PASSWD,CREATE_DATEfrom于第一行之前，調(diào)用其nextResultSetfalsewhileResultSet提供了getXXX(Stringcolumn)方法，例如：getInt（Stringcolumn）等，獲取當(dāng)前ResultSet對(duì)象的當(dāng)前行中指定列名的值，其中參數(shù)column表示數(shù)據(jù)庫表中的列名字。推薦使用根while(rs.next())System.out.println(rs.getInt("ID")++rs.getInt("ACCOUNT_ID")+++","+rs.getString("OS_USERNAME")++rs.getString("LOGIN_PASSWD")++}while(rs.next()){System.out.println(rs.getInt(1)+","rs.getInt(2)+","+rs.getString(3)+","+","","+rs.getString(5)+","+rs.getTimestamp(6));publicvoidtestFindAll()ServiceDAOserviceDAO=new2002,1011,6,huangrong,12345,2009-03-0115:30:05.02003,1011,0,huangrong,12345,2009-03-0115:30:10.02004,1011,3,huangrong,12345,2009-03-0115:30:15.02005,1019,6,luwushuang,12345,2012-02-1023:50:55.02006,1019,0,luwushuang,12345,2012-02-1000:00:00.02007,1020,0,weixiaobao,12345,2012-02-1011:05:20.02008,1020,0,guojing,12345,2012-02-1112:05:21.02009,1020,0,tongxiangyu,12345,2012-02-1210:05:20.02010,1020,0,baizhantang,12345,2012-02-1111:05:30.02011,1018,0,moxiaobei,12345,2012-02-1112:05:31.02012,1018,0,luxiucai,12345,2012-02-1212:06:30.02013,1018,0,lidazhui,12345,2012-02-1313:07:31.02014,1015,0,guofurong,12345,2012-02-1414:08:32.02015,1015,0,xingbutou,12345,2012-02-1515:09:33.0publicclassServiceDAOpublicvoidfindAll()Connectioncon=Statementstmt=ResultSetrs=null;trycon="scott",stmt=rs=.executeQuery("selectID,ACCOUNT_ID,LOGIN_PASSWD,CREATE_DATEfromwhile(rs.next())System.out.println(rs.getInt("ID")+ +rs.getInt("ACCOUNT_ID")+ + +","+rs.getString("OS_USERNAME")+ +rs.getString("LOGIN_PASSWD")+ + *while(rs.next()){System.out.println(rs.getInt(1)+","*rs.getInt(2)+","+rs.getString(3)+","+*","+rs.getString(5)+","+rs.getTimestamp(6));}catch(ClassNotFoundExceptione)thrownew}finallytryif(con!=null)}}catch(SQLException{}}}}}catch(SQLExceptione)thrownewpublicclassTestServiceDAOpublicvoidtestFindAll()ServiceDAOserviceDAO=newServiceDAO();StringuserName=Stringhost=rsrs=stmt.executeQuery("selectID,ACCOUNT_ID,UNIX_HOST,LOGIN_PASSWD,CREATE_DATEfromSERVICEwhere+userName+"'ANDUNIX_HOST='"+host+publicvoidfindByUsernameAndHost()Connectioncon=Statementstmt=ResultSetrs=null;trycon="scott",stmt=StringuserName=Stringhost=rs=.executeQuery("selectID,ACCOUNT_ID,LOGIN_PASSWD,CREATE_DATEfromSERVICEwhere +userName+"'ANDUNIX_HOST='"+host+while(rs.next())System.out.println(rs.getInt("ID")+ +rs.getInt("ACCOUNT_ID")+ + +","+rs.getString("OS_USERNAME")+ +rs.getString("LOGIN_PASSWD")+ + }catch(ClassNotFoundExceptione)}catch(SQLExceptione)}}}throwthrownew}catch(SQLExceptione)thrownew}try{{(con!=null)}publicvoid{ServiceDAOserviceDAO=2007,1020,0,weixiaobao,12345,2012-02-1011:05:20.0privatestaticPropertiesproperties=newprivatestaticStringdriver=properties.getProperty("jdbc.driver");privatestaticStringurl=properties.getProperty("jdbc.url");privatestaticStringuser=properties.getProperty("jdbc.user");privatestaticStringpwd=statictrydriver=url=properties.getProperty("jdbc.url");user=properties.getProperty("jdbc.user");pwd=}catch(Exceptione){thrownew}}protectedstaticConnectionopenConnection()throwsSQLExceptionreturnDriverManager.getConnection(url,user,protectedstaticvoidcloseConnection(Connectioncon)if(con!=null)try{}catch(SQLExceptione)}}}publicclassServiceDAOextendsBaseDAOpublicbooleanvalidate1(StringuserName,Stringpwd)Connectioncon=null;Statementstmt=null;ResultSetrs=null;booleanok=false;try{conopenConnection();//調(diào)用BaseDAOstmt=Stringsql="selectcount(*)fromSERVICEwhere+userName+"'ANDLOGIN_PASSWD='"+pwd+rs=if(rs.next()&&rs.getInt(1)>{ok=return}catch(SQLExceptione)thrownew}finallycloseConnection(con);//調(diào)用BaseDAOthrownew}finallycloseConnection(con);//調(diào)用BaseDAOpublicvoidtestValidate1()ServiceDAOserviceDAO=newbooleanok=serviceDAO.validate1("huangrong","12345");selectcount(*)fromSERVICEwhereAND給："goodman"，給："a'OR'b'='b"，數(shù)據(jù)庫的SERVICE表中不存在該OS帳號(hào)和同時(shí)publicvoid{ServiceDAOserviceDAO=newbooleanok=serviceDAO.validate1("goodman","a'OR'b'='b");selectcount(*)fromSERVICEwhereANDLOGIN_PASSWD='a'OR可以看到返回結(jié)果為true，說明數(shù)據(jù)庫中存在服務(wù)器的OS帳號(hào)為goodman，為a'OR問題在SL語句上，SL語句AD的后邊IPSSD='a'R''=''子句中''='b'返回都是rue，和前面的語句使用進(jìn)行連接，所以整個(gè)L語句相當(dāng)于沒有設(shè)置條件，這種現(xiàn)象在編程中叫做S注入，是應(yīng)該避免的編程方式。PaeSaen可以防止SL注入。publicbooleanvalidate(StringuserName,Stringpwd)Connectioncon=null;PreparedStatementstmt=null;ResultSetrs=null;booleanok=false;try{con=Stringsql="selectcount(*)fromSERVICEwherestmt=con.prepareStatement(sql);stmt.setString(1,userName);stmt.setString(2,pwd);rs=if(rs.next()&&rs.getInt(1)>{ok=return}catch(SQLException異} }}}finally2.3實(shí)現(xiàn)publicclassBaseDAOprivatestaticPropertiesproperties=newprivatestaticStringdriver=privatestaticStringurl=privatestaticStringuser=privatestaticStringpwd=properties.getProperty("jdbc.password");statictrydriver=url=user=pwd=}catch(Exceptione)thrownew **(con!=null)try}catch(SQLExceptione)}}}}*@throwsprotectedstaticConnectionopenConnection()throws{returnDriverManager.getConnection(url,user,**@paramprotectedstaticvoidcloseConnection(Connectioncon)publicclassServiceDAOextendsBaseDAOpublicvoid{Connectioncon=null;Statementstmt=null;ResultSetrs=null;trycon=stmt=rs=.executeQuery("selectID,ACCOUNT_ID,LOGIN_PASSWD,CREATE_DATEfromwhile{System.out.println(rs.getInt("ID")+ +rs.getInt("ACCOUNT_ID")+ + +","+rs.getString("OS_USERNAME")+ +rs.getString("LOGIN_PASSWD")+ +}*while(rs.next()){System.out.println(rs.getInt(1)+","*rs.getInt(2)+","+rs.getString(3)+","+*","+rs.getString(5)+","+rs.getDate(6));}catch(SQLExceptione) 異常thrownew}finally publicvoidfindByUsernameAndHost()Connectioncon=Statementstmt=ResultSetrs=null;trycon=stmt=con.createStatement();StringuserName=Stringhost=rs=.executeQuery("selectID,ACCOUNT_ID,LOGIN_PASSWD,CREATE_DATEfromSERVICEwhere +userName+"'ANDUNIX_HOST='"+host+while(rs.next())System.out.println(rs.getInt("ID")+ +rs.getInt("ACCOUNT_ID")+ + +","+rs.getString("OS_USERNAME")+ +rs.getString("LOGIN_PASSWD")+ + }catch(SQLExceptione) 異常thrownew}finally publicbooleanvalidate1(StringuserName,Stringpwd)Connectioncon=Statementstmt=ResultSetrs=booleanok=trycon=stmt=Stringsql="selectcount(*)fromSERVICEwhere +userName+"'ANDLOGIN_PASSWD='"+pwd+rs=if(rs.next()&&rs.getInt(1)>0)ok= return}catch(SQLExceptione) 異常thrownew}finally publicbooleanvalidate(StringuserName,Stringpwd)Connectioncon=PreparedStatementstmt=ResultSetrs=booleanok=false;trycon=Stringsql="selectcount(*)fromSERVICEwherestmt=stmt.setString(1,stmt.setString(2,rs=if(rs.next()&&rs.getInt(1)>0)ok= return}catch(SQLExceptione) 異常thrownew}finally 2.4擴(kuò)展publicvoidtestValidate()ServiceDAOserviceDAO=newbooleanok=serviceDAO.validate("huangrong",給："goodman"，給："a'OR'b'='b"，數(shù)據(jù)庫的SERVICE表中不存在該OS帳號(hào)和同時(shí)publicvoid{ServiceDAOserviceDAO=newbooleanok=serviceDAO.validate("goodman","a'OR'b'='b");CREATETABLECONSTRAINTSERVICE_DTAIL_ID_PKCREATESEQUENCESERVICE_DETAIL_SEQSTARTWITHJavaBean的規(guī)范，類中的屬性和表SERVICE_DETAIL表中的字段一樣，只需要按照J(rèn)ava名規(guī)則publicclassServiceDetailimplementsSerializableprivatestaticfinallongserialVersionUID=privateIntegerprivateIntegerprivateStringprivateStringprivateIntegerprivateDateprivateDateprivateIntegerprivateBigDecimalpublicServiceDetail()}publicIntegergetId()return publicvoidsetId(Integerid)this.id= publicIntegergetServiceid()return publicvoidsetServiceid(Integerserviceid)this.serviceid= publicStringgetHost()return publicvoidsetHost(Stringhost)this.host= publicStringgetUserName()return publicvoidsetUserName(StringuserName)this.userName= publicIntegergetPid()return publicvoidsetPid(Integerpid)this.pid= publicDategetLoginTime()return publicvoidsetLoginTime(DateloginTime)this.loginTime= publicDategetLogoutTime()return publicvoidsetLogoutTime(DatelogoutTime)this.logoutTime= publicIntegergetDuration()return publicvoidsetDuration(Integerduration)this.duration= publicBigDecimalgetCost()return publicvoidsetCost(BigDecimalcost)this.cost= publicStringtoString()return"ServiceDetail[id="+id+",serviceid="+ +",host="+host+",userName="+userName+",pid=" +",loginTime="+loginTime+",logoutTime="+ +",duration="+duration+",cost="+cost+ publicpublicclassServiceDetailDAOextendsBaseDAOprivatestaticfinalStringINSERET="insertintoSERVICE_DETAIL+"(ID,SERVICE_ID,CLIENT_HOST,OS_USERNAME,PID,LOGIN_TIME,LOGOUT_TIME,"+"DURATION,COST)(SERVICE_DETAIL_SEQ.nextval,?,?,?,?,?,?,?,在SiDailD類中新建a方法，a方法的參數(shù)是SiDail類的實(shí)例，在上面的介紹中已經(jīng)創(chuàng)建了SiDail類，之所以把SiDeail類作為a方法的參數(shù)，是因?yàn)槲覀円４娴脑攩螖?shù)據(jù)在SEIEDETI表的有九個(gè)字段，也就是說有九項(xiàng)內(nèi)容需要保存，如果不用SieDail類的實(shí)例作為ae方法的參數(shù)，那么我們的a方法將有九個(gè)參數(shù)。對(duì)于數(shù)據(jù)庫來說九個(gè)字段不是最多的時(shí)候，在企業(yè)中做項(xiàng)目的時(shí)候可能會(huì)有幾十個(gè)字段的情況，所以使用對(duì)象封裝起來是十分有必要的。publicvoidsave(ServiceDetailserviceDetail)使用PaeSaen來執(zhí)行SL語句。在L語句中有8個(gè)問號(hào)，在代碼中要給它們分別設(shè)置值，規(guī)則是：從左到右，對(duì)應(yīng)1，2，3，ieap方法，eDa方法無法將時(shí)分秒存入到數(shù)據(jù)庫。publicvoidsave(ServiceDetailserviceDetail)Connectioncon=null;PreparedStatementstmt=null;try{con=stmt=con.prepareStatement(INSERET);stmt.setInt(1,serviceDetail.getServiceid());stmt.setString(2,stmt.setString(3,serviceDetail.getUserName());stmt.setInt(4,serviceDetail.getPid());}finally}} newnewstmt.setInt(7,serviceDetail.getDuration());stmt.setBigDecimal(8,serviceDetail.getCost());}catch(SQLException{System.out.println("數(shù)據(jù)庫thrownewRuntimeException(e);publicclassTestServiceDetailDAOpublicvoidtestSave()throwsExceptionSimpleDateFormatsdf=newSimpleDateFormat("yyyy-MM-ddHH:mm:ss")ServiceDetailDAOserviceDetailDAO=newServiceDetailserviceDetail=newserviceDetail.setCost(newserviceDetail.setLoginTime(sdf.parse("2013-3-11serviceDetail.setLogoutTime(sdf.parse("2013-3-11privatestaticfinalStringFIND_ALL="selectID,SERVICE_ID,privateServiceDetailtoServiceDetail(ResultSetrs)throwsServiceDetailserviceDetail=newreturn publicList<ServiceDetail>findAll()Connectioncon=}finally }PreparedStatementstmt=trycon=PreparedStatementstmt=trycon=stmt=ResultSetrs=List<ServiceDetail>list=newArrayList<ServiceDetail>();while(rs.next()){return}catch(SQLExceptione)thrownewpublicvoidtestFindAll()throwsException{ServiceDetailDAOserviceDetailDAO=newServiceDetailDAO();List<ServiceDetail>list=serviceDetailDAO.findAll();for(ServiceDetailsd:{ServiceDetail[id=100000,serviceid=2002,host=8,publicclassServiceDetailDAOextendsBaseDAOprivatestaticfinalStringINSERET="insertintoSERVICE_DETAIL +"(ID,SERVICE_ID,CLIENT_HOST,OS_USERNAME,PID, +"DURATION,COST)(SERVICE_DETAIL_SEQ.nextval,?,?,?,?,?,?,?,privatestaticfinalStringFIND_ALL"selectID,SERVICE_ID,CLIENT_HOST,OS_USERNAME,PID, +"LOGIN_TIME,LOGOUT_TIME,DURATION,COSTfrompublicvoidsave(ServiceDetailserviceDetail)Connectioncon=PreparedStatementstmt=trycon=stmt=con.prepareStatement(INSERET);stmt.setInt(1,stmt.setString(2,stmt.setString(3,stmt.setInt(4, stmt.setInt(7,stmt.setBigDecimal(8,}catch(SQLExceptione)System.out.println("數(shù)據(jù)庫異常thrownew}finally publicList<ServiceDetail>findAll()Connectioncon=PreparedStatementstmt=trycon=stmt=ResultSetrs=List<ServiceDetail>list=newwhile(rs.next()) return}catch(SQLExceptione) 異常thrownew}finally privateServiceDetailtoServiceDetail(ResultSetrs)throwsServiceDetailserviceDetail=newreturnreturnpublicclassTestServiceDetailDAOpublicvoidtestSave()throwsExceptionSimpleDateFormatsdf=newSimpleDateFormat("yyyy-MM-ddHH:mm:ss")ServiceDetailDAOserviceDetailDAO=newServiceDetailserviceDetail=newserviceDetail.setCost(newserviceDetail.setLoginTime(sdf.parse("2013-3-11serviceDetail.setLogoutTime(sdf.parse("2013-3-11 publicvoidtestFindAll()throwsExceptionServiceDetailDAOserviceDetailDAO=newList<ServiceDetail>list=for(ServiceDetailsd:list) privatestaticfinalStringUPDATE="updateSERVICE_DETAILset+"SERVICE_ID=?,CLIENT_HOST=?,OS_USERNAME=?,PID=?,+"LOGOUT_TIME=?,DURATION=?,COST=?whereprivatestaticfinalStringFIND_BY_ID"selectID,SERVICE_ID,CLIENT_HOST,OS_USERNAME,PID,+"LOGIN_TIME,LOGOUT_TIME,DURATION,COSTfromSERVICE_DETAILwherepublicServiceDetailfindById(intid)Connectioncon=null;PreparedStatementstmt=null;trycon=stmt=con.prepareStatement(FIND_BY_ID);stmt.setInt(1,id);ResultSetrs=stmt.executeQuery();ServiceDetailserviceDetail=null;if(rs.next()){serviceDetail=}return}catch(SQLExceptione)thrownew}finally }publicvoidupdate(ServiceDetailserviceDetail)Connectioncon=null;PreparedStatementstmt=null;try{publicvoidtestFindById()throws{ServiceDetailDAOserviceDetailDAO=newServiceDetailserviceDetail=：ServiceDetail[id=100000,serviceid=2002,host=8,con=stmt=stmt.setInt(1,stmt.setString(2,stmt.setString(3,stmt.setInt(4,stmt.setDate(5,newstmt.setInt(7,stmt.setBigDecimal(8,stmt.setInt(9,}catch(SQLExceptione)thrownew}}publicvoidtestUpdate()throwsExceptionServiceDetailDAOserviceDetailDAO=newServiceDetailDAO();ServiceDetailserviceDetail=serviceDetailDAO.findById(100000);serviceDetail.setCost(newBigDecimal("100.00"));publicclassServiceDetailDAOextendsBaseDAOprivatestaticfinalStringINSERET="insertintoSERVICE_DETAIL+"(ID,SERVICE_ID,CLIENT_HOST,OS_USERNAME,PID,LOGIN_TIME,+"DURATION,COST)(SERVICE_DETAIL_SEQ.nextval,?,?,?,?,?,?,?,privatestaticfinalStringUPDATE="updateSERVICE_DETAILset+"SERVICE_ID=?,CLIENT_HOST=?,OS_USERNAME=?,PID=?,+"LOGOUT_TIME=?,DURATION=?,COST=?whereprivatestaticfinalStringFIND_BY_ID"selectID,SERVICE_ID,CLIENT_HOST,OS_USERNAME,PID,+"LOGIN_TIME,LOGOUT_TIME,DURATION,COSTfromSERVICE_DETAILwhereprivatestaticfinalStringFIND_ALL"selectID,SERVICE_ID,CLIENT_HOST,OS_USERNAME,PID, +"LOGIN_TIME,LOGOUT_TIME,DURATION,COSTfrompublicvoidsave(ServiceDetailserviceDetail)Connectioncon=PreparedStatementstmt=trycon=stmt=con.prepareStatement(INSERET);stmt.setInt(1,stmt.setString(2,stmt.setString(3,stmt.setInt(4,newnewnewnewstmt.setInt(7,s