JavaWeb應(yīng)用開發(fā)實(shí)驗(yàn)報告-實(shí)驗(yàn)三 JDBC調(diào)用存儲過程與事務(wù)管理

實(shí)驗(yàn)三JDBC調(diào)用存儲過程與事務(wù)管理一、實(shí)驗(yàn)內(nèi)容1、使用MySQL5創(chuàng)建存儲過程的詳細(xì)步驟：①選中user表，function->NewFunction,選擇procedure，點(diǎn)擊next，如下圖：②得到如下圖所示：③輸入以下內(nèi)容：SELECT`name`INTOuserNameFROM`user`WHEREid=userId;SELECTageINTOuserAgeFROM`user`WHEREid=userId;即得到如下圖所示，并點(diǎn)擊Save：④輸入存儲過程的名字getUserAndAgeById_2，點(diǎn)擊OK結(jié)束，即創(chuàng)建存儲過程完畢。⑤可在Functions中查看：2、請寫出CallableStatementTest的完整代碼：packagetwo;importjava.sql.CallableStatement;importjava.sql.Connection;importjava.sql.DriverManager;importjava.sql.ResultSet;importjava.sql.SQLException;importjava.sql.Types;publicclassCallableStatementTest{ publicstaticvoidmain(String[]args){//ctrl+D是刪除快捷鍵，刪除一整行 getUserNameAndAgeById_2(); } publicstaticvoidgetUserNameAndAgeById_2(){ Connectionconn=null; CallableStatementcs=null; ResultSetrs=null; try{ conn=DBConnection.getConnection(); //①定義調(diào)用存儲過程的SQL語句 Stringsql="{callgetUserNameAndAgeById_2(?,?,?)}"; //②獲取CallableStatement對象 cs=conn.prepareCall(sql); cs.setInt(1,1); cs.registerOutParameter(2,Types.VARCHAR); cs.registerOutParameter(3,Types.INTEGER); cs.execute(); StringuserName=cs.getString(2); intuserAge=cs.getInt("userAge"); System.out.println(userName+"-"+userAge); }catch(SQLExceptione){ e.printStackTrace(); }finally{ DBConnection.close(rs,cs,conn); } }}3、請寫出TransactionTest類的完整代碼：packagetwo;importjava.sql.Connection;importjava.sql.PreparedStatement;importjava.sql.ResultSet;importjava.sql.SQLException;importjava.sql.Statement;publicclassTransactionTest{ publicstaticvoidupdate()throwsSQLException{ Connectionconn=null; PreparedStatementps=null; ResultSetrs=null; try{ //1、獲得連接對象 conn=DBConnection.getConnection(); conn.setAutoCommit(false);//事務(wù)開始，設(shè)置手工提交事務(wù) //定義SQL語句 Stringsql="updateusersetmoney=money-?wherename=?";//id=? ps=conn.prepareStatement(sql); floatm1=1000; ps.setFloat(1,m1); intid1=1; Stringname1="zhangsan"; ps.setString(2,name1); ps.executeUpdate(); Stringsql2="updateusersetmoney=money+?wherename=?"; ps=conn.prepareStatement(sql2); ps.setFloat(1,m1); Stringname2="lisi"; ps.setString(2,name2); ps.executeUpdate(); mit(); //事務(wù)提交 System.out.println("轉(zhuǎn)賬成功！請前往數(shù)據(jù)庫查看！"); }catch(SQLExceptione){ //事務(wù)中某項操作不成功事務(wù)回滾 conn.rollback(); } finally{ //4、關(guān)閉資源對象 DBConnection.close(rs,ps,conn); } } publicstaticvoidmain(String[]args)throwsSQLException{ update(); }}還有用到的同一個包下的DBConnection類：packagetwo;importjava.sql.Connection;importjava.sql.DriverManager;importjava.sql.ResultSet;importjava.sql.SQLException;importjava.sql.Statement;publicclassDBConnection{ privatestaticfinalStringdriverName="com.mysql.jdbc.Driver"; privatestaticfinalStringurl="jdbc:mysql://localhost:3306/testDB"; privatestaticfinalStringuser="root"; privatestaticfinalStringpassword="root"; privateDBConnection(){} static{ try{ Class.forName(driverName); }catch(ClassNotFoundExceptione){ e.printStackTrace(); } } publicstaticConnectiongetConnection()throwsSQLException{ returnDriverManager.getConnection(url,user,password); } publicstaticvoidclose(ResultSetrs,Statementst,Connectionconn){ try{ if(rs!=null){ rs.close(); } }catch(SQLExceptione){ e.printStackTrace(); }finally{ if(conn!=null){ try{ c