JAVA操作Oracle数据库中的事务

实验1：

create table yggz(code int, salary number(7,2));
insert into yggz values(1, 1000);
insert into yggz values(2, 150);
commit;

完成任务：

如果1号员工的salary多余300元，则从1号员工的salary中减少300元，同时加到2号员工的salary上。

实验2：

create table yggz(code int, salary number(7,2));
insert into yggz values(1, 1000);
insert into yggz values(2, 150);
commit;

完成任务：

如果1号员工的salary 多余300元，则从1号员工的salary中减少300元，同时加到2号员工的salary上，但是还要确保转账后1号员工的salary多于转账后的2号员工的salary。

package com.oaj;import java.sql.*;public class TestJdbcOdbc {String driver="oracle.jdbc.driver.OracleDriver";
String strUrl="jdbc:oracle:thin:@localhost:1521:orcl";
Statement stmt=null;
ResultSet rs=null;
Connection conn=null;
CallableStatement cstmt=null;
float salary=0;
float salary2=0;
String sqlStr=null;
        PreparedStatement ps=null;
public static void main(String[] args)
{
new TestJdbcOdbc().test2();
}
public void test1()
{
try
{
Class.forName(driver);
conn=DriverManager.getConnection(strUrl,"scott","scott");
conn.setAutoCommit(false);
 //得到1号与昂工的工资
sqlStr="select salary from yggz where code=1";
ps=conn.prepareStatement(sqlStr);
rs=ps.executeQuery();
while(rs.next())
{
salary=rs.getFloat(1);
}
if(salary<300)
{
throw new RuntimeException("小于300元，不能转账");
}
sqlStr="update yggz set salary=salary-300 where code=1";
ps=conn.prepareStatement(sqlStr);
ps.executeUpdate(sqlStr);sqlStr="update yggz set salary=salary+300 where code=2";
ps=conn.prepareStatement(sqlStr);
ps.executeUpdate();conn.commit();
System.out.println("---成功！");}
catch(SQLException ex)
{
if(conn!=null)
{
try
{
conn.rollback();
System.out.println("失败");}
catch(Exception ex2)
{
ex2.printStackTrace();
}}
}
catch(Exception ex)
{
ex.printStackTrace();
}
finally
{
try
{
if(rs!=null)
{
rs.close();}
if(ps!=null)
{
ps.close();
}
if(conn!=null)
{
conn.close();
conn=null;
}
}
catch(SQLException ex)
{
ex.printStackTrace();
}
}
}
public void test2()
{
try
{
Class.forName(driver);
conn=DriverManager.getConnection(strUrl,"scott","scott");
conn.setAutoCommit(false);
 //得到1号与昂工的工资
sqlStr="select salary from yggz where code=1";
ps=conn.prepareStatement(sqlStr);
rs=ps.executeQuery();
while(rs.next())
{
salary=rs.getFloat(1);
}
if(salary<300)
{
throw new RuntimeException("小于300元，不能转账");
}
//设置一个保存点
Savepoint point1=conn.setSavepoint("Point1");sqlStr="update yggz set salary=salary-300 where code=1";
ps=conn.prepareStatement(sqlStr);
ps.executeUpdate(sqlStr);sqlStr="update yggz set salary=salary+300 where code=2";
ps=conn.prepareStatement(sqlStr);
ps.executeUpdate();//再次取一号员工工资和二号员工的工资
sqlStr="select salary from yggz where code=1";
ps=conn.prepareStatement(sqlStr);
rs=ps.executeQuery();
while(rs.next())
{
salary=rs.getFloat(1);
}sqlStr="select salary from yggz where code=2";
ps=conn.prepareStatement(sqlStr);
rs=ps.executeQuery();
while(rs.next())
{
salary2=rs.getFloat(1);
}if(!(salary>salary2))
{
conn.rollback(point1);
System.out.println("转账失败！");
}
else
{
conn.commit();
System.out.println("---成功！");
}conn.commit();}
catch(SQLException ex)
{
if(conn!=null)
{
try
{
conn.rollback();
System.out.println("失败");}
catch(Exception ex2)
{
ex2.printStackTrace();
}}
}
catch(Exception ex)
{
ex.printStackTrace();
}
finally
{
try
{
if(rs!=null)
{
rs.close();}
if(ps!=null)
{
ps.close();
}
if(conn!=null)
{
conn.close();
conn=null;
}
}
catch(SQLException ex)
{
ex.printStackTrace();
}
}
}
}