employ~2.jav

java 完全探索的随书源码

import java.sql.*;

class EmployeeSalaryTransactionExample
{
  // Private instance varaibles for the connection and the prepared statement
  private Connection connection = null;

  // Default Constructor
  public EmployeeSalaryTransactionExample( Connection conn )
  {
    super();
    connection = conn;
  }

  // Public Accessor for the Connection
  public Connection getConnection()
  {
    return connection;
  }

  // Method to update the EMPLOYEE and SALARY_HISTORY tables
  public void updateEmployeeSalary( int employeeId, double oldSalary, double newSalary )
  {
    try
    {
      // Start the transaction by turning off auto commit
      getConnection().setAutoCommit( false );

      // Set up the first statement, which is valid
      String sqlString2 = "INSERT INTO SALARYHISTORY (EMPID, DATE, SALARY )";
      sqlString2 = sqlString2 + " VALUES( ?, ?, ? )";
      PreparedStatement stmt2 = getConnection().prepareStatement( sqlString2 );
      stmt2.setInt( 1, employeeId );
      Timestamp ts = new Timestamp( System.currentTimeMillis() );
      stmt2.setTimestamp( 2, ts );
      stmt2.setDouble( 3, newSalary );
      // This statement should not really execute until the commit of the transaction
      stmt2.executeUpdate();

      // Set up the second statement which should fail because of database
      // constraints on the salary value. The salary must be greated than 0
      String sqlString1 = "UPDATE EMP SET SAL = ? WHERE EMPNO = ?";
      PreparedStatement stmt1  = getConnection().prepareStatement( sqlString1 );
      stmt1.setDouble( 1, newSalary );
      stmt1.setInt( 2, employeeId );
      stmt1.executeUpdate();

      // Attempt to commit the transaction
      getConnection().commit();
    }
    catch( SQLException sqlException )
    {
      try
      {
        // There was a problem and we are rolling back the changes
        System.out.println( "Database Transaction Failed...Rolling back the changes" );
        sqlException.printStackTrace();
        getConnection().rollback();
      }
      catch( Exception ex )
      {
        ex.printStackTrace();
      }
    }
  }

  // Main method to test this class
  public static void main(String[] args)
  {
    try
    {
      // Use the previous DatabaseManager class to aquire a connection
      Connection conn = DatabaseManager.getConnection();

      // Create an instance of the example class
      EmployeeSalaryTransactionExample example = new EmployeeSalaryTransactionExample( conn );

      // Get the first employee record and use it for this example
      Statement stmt = conn.createStatement();
      ResultSet rs = stmt.executeQuery( "SELECT EMPNO, SAL FROM EMP" );

      // Just interested in the first record for this example. Make sure there is
      // at least one record
      if ( rs.next() )
      {
        int employeeId = rs.getInt( 1 );
        double oldSalary = rs.getDouble( 2 );

        // Close the result set since we don't need it anymore
        rs.close();

        // We are setting this to a negative number so that it will fail because
        // the database column has a constraint that it has to be greater than
        // zero.
        double newSalary = -10.00;
        example.updateEmployeeSalary( employeeId, oldSalary, newSalary );
      }
      else
      {
        System.out.println( "There were 0 employee records in the database" );
      }

      // Always make sure to close the connection when you are finished
      conn.close();
    }
    catch( SQLException ex )
    {
      ex.printStackTrace();
    }
  }
}