java 操作oracle实现事物_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();

}

}

}

}