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