updatecustomerrepid.java

java 完全探索的随书源码

import java.sql.*;

public class UpdateCustomerRepID
{
  // Private instance varaibles for the connection and the prepared statement
  private Connection connection = null;
  private PreparedStatement stmt = null;

  // The SQL to be used for the PreparedStatement. The question marks are
  // for the input parameters that will be passed in
  private static String UPDATE_SQL = "UPDATE CUSTOMER SET REPID = ? WHERE CUSTID = ?";

  // Default Constructor
  public UpdateCustomerRepID( Connection conn ) throws SQLException
  {
    super();
    connection = conn;
    // Create the instance of the PreparedStatement for this class
    stmt = connection.prepareStatement( UpdateCustomerRepID.UPDATE_SQL );
  }

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

  // Print out all of the Customers to the console
  public void printAllCustomers()
  {
    // Local variable for the Statement object
    Statement stmt = null;
    ResultSet rs = null;
    try
    {
      // Create a Statement object from the Connection
      stmt = getConnection().createStatement();
      // Ask the Customer for it's insert sql statement
      String sql = "SELECT CUSTID, REPID FROM CUSTOMER";
      rs = stmt.executeQuery( sql );

      while( rs.next() )
      {
        String id = rs.getString( 1 );
        String repId = rs.getString( 2 );

        System.out.println( "CUSTID: " + id + " REPID: " + repId );
      }
    }
    catch( SQLException ex )
    {
      ex.printStackTrace();
    }
  }

  // This method performs the update through the PreparedStatement
  public void updateCustomerRepId( String customerNumber, int repId ) throws SQLException
  {
    stmt.setInt( 1, repId );
    stmt.setString( 2, customerNumber );
    stmt.executeUpdate();
  }

  // The main method for this example
  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
      UpdateCustomerRepID example = new UpdateCustomerRepID( conn );

      System.out.println( "Before running the update on the records." );
      example.printAllCustomers();

      Statement custStatement = conn.createStatement();
      ResultSet rs = custStatement.executeQuery( Customer.getReadAllSQL() );
      while( rs.next() )
      {
        // Get the customer number(id) from the record
        String customerNumber = rs.getString( 1 );
        // Set the phone number to all zero's
        int repId = 1;
        // Execute the PreparedStatement
        example.updateCustomerRepId( customerNumber, repId );
      }
      System.out.println( "After running the update on the records." );
      example.printAllCustomers();

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

}