skiclubdb.java

Java程序设计高级教程（第三版）

// installation instructions:
// set static fields as described in comments below

package com.nearhills;

import java.sql.*;
import java.util.*;

import com.nearhills.*;

public class SkiClubDB {

   static Connection connection = null;
   static final String dbDriver = 
      "COM.ibm.db2.jdbc.app.DB2Driver";
   static final String dbUrl= "jdbc:DB2:SKICLUB"; 
   static final String dbName = "SKICLUB";
    //  set dbOwer to schema name used for tables
   static final String dbOwner = "SKICLUB"; 
   // set dbUser to any user on your Windows 
   static final String dbUser = "USERID"; 
   // set dbPassword to the windows password for dbUser
   static final String dbPassword = "PASSWORD";
   static final String memberTable = "MEMBERS";
   static final String tripTable = "SKITRIPS";
   static final String bookTable = "BOOKINGS";
 
   synchronized static Connection getConnection() 
      throws DBopException 
   {
      if ( connection == null ) { 
         try {
         Class.forName( dbDriver );
            connection = DriverManager.getConnection(
            dbUrl, dbUser, dbPassword);
         } catch ( ClassNotFoundException e ) {
             throw new DBopException (
             "Cannot load database driver: for DB2, " +
             "your classpath must include " +
             "SQLLIB\\JAVA\\DB2JAVA.ZIP." );
         } catch ( SQLException e ) {
             throw new DBopException ( 
             "Cannot connect to database: for DB2, " +
             "check that DB2 is running and the SKICLUB " 
             + "database exists." );
         }
      }
      return connection;
   }

   synchronized static ResultSet getAllColumns( 
      String[] tables, String condition )
      throws DBopException 
   {
      String[] columns = {"*"};
      return getSelectedColumns( columns, tables, 
         condition );
   }
   
   synchronized static ResultSet getSelectedColumns( 
      String[] columns, String[] tables, 
      String condition ) 
      throws DBopException 
  { 
      String select = "SELECT ";
      try {
        Statement query = getConnection().
           createStatement();
        for ( int i = 0; i < columns.length; i++ ) {
           select += columns[i];
           select += i + 1 < columns.length ? "," : " ";
        }
        select += "FROM ";
        for ( int i =0; i < tables.length; i++ ) { 
           if ( dbOwner.length() > 0 ) {
              select += dbOwner + ".";
           }  
           select +=  tables[i];
           select +=  i + 1 < tables.length ? "," : " ";       
        }
        select += condition;
        System.out.println( select );
        return query.executeQuery( select );
      } catch ( SQLException e ) {
          throw new DBopException (
             "Cannot extract data with SQL statement:\n " 
             + select );
      }
  }

   
   synchronized static public int getNextId( ) 
      throws SQLException, DBopException
   {
      Statement query = getConnection().createStatement();
      ResultSet result = query.executeQuery(
         "SELECT MAX(ID) FROM " + 
         ( dbOwner.length( ) > 0 ? dbOwner + "." : "" ) + 
         memberTable );
      int newId = 1;
      if(result.next()==true) {
         newId = result.getInt(1) + 1;
      }
      return newId;
   }	
   
   synchronized static public void addMember( 
      ClubMember member ) 
      throws SQLException, DBopException 
   {
      int rowInserted = 0;
      Statement insert = getConnection().
         createStatement();
      String addrecord = "INSERT INTO " + 
         ( dbOwner.length( ) > 0 ? dbOwner + "." : "" ) +
         memberTable + " VALUES (" + member.getId() + 
         ",'" + member.getFirstName() + "','" +
         member.getMiddleName() + "','" +
         member.getLastName() + "','" +
         member.getPhoneNumber() + "','" +
         member.getAbility() + "')" ;
      System.out.println( addrecord );
      rowInserted = insert.executeUpdate(addrecord);
      if ( rowInserted != 1 ) { 
         throw new DBopException( 
            "Unable to add new member to database" );
      }
      return;
   }  
   
   synchronized static public void updateMember( 
      ClubMember member )
      throws SQLException, DBopException 
   {
      Statement update = getConnection().createStatement();
      String changeRecord = "UPDATE " +  
         ( dbOwner.length( ) > 0 ? dbOwner + "." : "" ) +
         memberTable + " SET " +  
         "FNAME = '" + member.getFirstName() + "'," +
         "MNAME = '" + member.getMiddleName() + "'," +
         "LNAME = '" + member.getLastName() + "'," +
         "PHONENUM = '" + member.getPhoneNumber() + "'," +
         "ABILITY = '" + member.getAbility() + "'" +
         "WHERE ID = " + member.getId();
      System.out.println( changeRecord );
      int changed = update.executeUpdate( changeRecord );  
      if ( changed < 1 ) {  
         throw new DBopException(
            "No changes made to databare" );
      } else if ( changed > 1 ) {
         throw new DBopException(
            "More than one member's data was affected: "   
            + changed + "records changed." );
      }      
      return;
   }  
   
      synchronized static public ClubMember 
         getMemberByID ( int id ) 
         throws SQLException, DBopException, 
         BadDataException
   {
      String[] tables = { memberTable };
      ResultSet result = getAllColumns( tables, 
         "WHERE ID = " + id );
      if ( ! result.next() ) { 
         return null;
      }
      ClubMember member = new ClubMember();
      member.setId( result.getInt( "ID" ) );
      member.setFirstName(
         result.getString( "FNAME" ).trim() );
      member.setMiddleName( 
         result.getString( "MNAME" ).trim() );
      member.setLastName( 
         result.getString( "LNAME" ).trim() );
      member.setPhoneNumber( 
         result.getString( "PHONENUM" ).trim() );
      member.setAbility( 
         result.getString( "ABILITY" ).trim() );
      if ( result.next( ) ) {
          throw new DBopException (
             "Duplicate Member ID: " + id );
      }
      return member;
   }   
   
   synchronized static public ClubMember getMemberByName ( 
         String firstName, String lastName ) 
      throws SQLException, DBopException, BadDataException 
   {
      String[] tables = { memberTable };
      ResultSet result = getAllColumns( tables, 
         " WHERE FNAME = '" + firstName + 
         "' AND LNAME = '" + lastName + "'" );
      if ( ! result.next() ) { 
         return null;
      }
      ClubMember member = new ClubMember();
      member.setId( result.getInt( "ID" ) );
      member.setFirstName(
         result.getString( "FNAME" ).trim() );
      member.setMiddleName( 
         result.getString( "MNAME" ).trim() );
      member.setLastName( 
         result.getString( "LNAME" ).trim() );
      member.setPhoneNumber( 
         result.getString( "PHONENUM" ).trim() );
      member.setAbility( 
         result.getString( "ABILITY" ).trim() );
      if ( result.next( ) ) {
          throw new DBopException (
             "Duplicate member name: " 
             + firstName + " " + lastName );
      }
      return member;
   }        
   
   
   synchronized static public Vector retrieveTrips( )
      throws DBopException
   {
      Vector trips = new Vector();
      try { 
         String[] tables = { tripTable };         
         ResultSet result = getAllColumns( tables, 
           "ORDER BY ID" );
         while( result.next() ) { 
            SkiTrip trip = new SkiTrip();
            trip.setId( result.getInt( "ID" ) );
            trip.setDate( result.getDate( "DAY" ) );
            trip.setResort( result.getString( "RESORT" ).trim() );
            trip.setCapacity( result.getInt( "CAPACITY" ) );
            trip.setBooked( result.getInt( "BOOKED" ) );
            trips.addElement(trip);
         }
      } catch ( SQLException e ) {
          throw new DBopException (
             "Error reading Table: " + tripTable);
      }
      return trips;
   }

   
   synchronized static public Vector getSkierTrips( 
      int skierID )
      throws DBopException
   {  
      String[] tables = { tripTable, bookTable };
      String condition = "WHERE " +
         ( dbOwner.length( ) > 0 ? dbOwner + "." : "" ) + 
         tripTable + "."  + "ID" + "=" + 
         ( dbOwner.length( ) > 0 ? dbOwner + "." : "" ) +
         bookTable + "." + "TRIPID" +
         " AND " + 
         ( dbOwner.length( ) > 0 ? dbOwner + "." : "" ) +
         bookTable + "." + "MEMBERID=" + skierID; 
      Vector trips = new Vector();     
      try { 
         ResultSet result = getAllColumns( 
            tables, condition );
         while( result.next() ) { 
            SkiTrip trip = new SkiTrip();
            trip.setId( result.getInt( "ID" ) );
            trip.setDate( result.getDate( "DAY" ) );
            trip.setResort( result.getString( "RESORT" ).trim() );
            trip.setCapacity( result.getInt( "CAPACITY" ) );
            trip.setBooked( result.getInt( "BOOKED" ) );
            trips.addElement(trip);
         }
      } catch ( SQLException e ) {
          throw new DBopException (
             "Error reading Table: " + tripTable);
      }
      return trips;
   }
      
   
   synchronized static public Vector retrieveBookings( 
      int skierID )
      throws DBopException
   {
      Vector trips = new Vector();
      try {
         String[] tables = { bookTable };
         String[] columns = { "TRIPID" };
         ResultSet result = getSelectedColumns( columns, 
            tables, " WHERE MEMBERID = " + skierID + 
            " ORDER BY TRIPID" );
         while( result.next() ) { 
            trips.addElement( new Integer 
               ( result.getInt( "TRIPID" ) ) );
         }
      } catch ( SQLException e ) {
          throw new DBopException (
             "Error reading Table: " + bookTable);
      }
      return trips;
   }

   
   synchronized public static void updateTrip( 
      SkiTrip trip )
      throws DBopException, SQLException
   {  
      Statement update = 
         getConnection().createStatement();
      String doChange = "UPDATE " +
         ( dbOwner.length( ) > 0 ? dbOwner + "." : "" ) +
         tripTable + " SET " +  
         "BOOKED = " + trip.getBooked() +
         " WHERE ID = " + trip.getId();
      System.out.println( doChange );
      int changed = update.executeUpdate( doChange );  
      if ( changed != 1 ) {  
         throw new DBopException(
            "Error updating booked count for trip "
            + trip.getId() );
      }
      return;     
   }    
      
   
   synchronized public static void addBooking( 
      Booking booking) 
      throws DBopException, SQLException 
   {
      int rowInserted = 0;
      Statement insert = getConnection().createStatement();
      String addrecord = "INSERT INTO " + 
         ( dbOwner.length( ) > 0 ? dbOwner + "." : "" ) +
         bookTable + " VALUES (" + booking.getMemberId() + 
         "," + booking.getTripId() + ")";
      System.out.println( addrecord );
      rowInserted = insert.executeUpdate(addrecord);
      if ( rowInserted != 1 ) { 
         throw new DBopException( 
            "Problem adding booking for trip" + 
            booking.getTripId() );
      }
      return;
   }     
      
   
   synchronized public static void dropBooking( 
      Booking booking) 
      throws DBopException, SQLException 
   {
      int rowDeleted = 0;
      Statement delete = getConnection().
         createStatement();
      String addrecord = "DELETE FROM " + 
         ( dbOwner.length( ) > 0 ? dbOwner + "." : "" ) +
         bookTable + " WHERE MEMBERID="+ 
         booking.getMemberId() + 
         " AND TRIPID=" + booking.getTripId();
      System.out.println( addrecord );
      rowDeleted = delete.executeUpdate(addrecord);
      if ( rowDeleted != 1 ) { 
         throw new DBopException( 
            "Problem dropping booking for trip" + 
            booking.getTripId() );
      }
      return;
   }  
     
       
   public static void main( String [] args )
      throws SQLException, DBopException, 
      ClassNotFoundException
   {
      System.out.println( "Getting Database driver" );
      Class.forName( dbDriver );
      System.out.println( "Getting Database connection" ); 
      getConnection();
      System.out.println( "Database ready" );
      return;
   } 

}