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();
			}
		}
	}
}