JDBC IVOutlineQuiz 1Stored ProceduresDistributed ApplicationWhere to put computation?Client-Server Trade-offStored ProceduresStepsJava Stored Procedure (Oracle)From clientReturn values?Summary – Stored ProceduresJDBC ClassesBatch UpdatingLimit Data MovementNon-linear Communication CostsExampleStepsBatch processingSummary - Batch ProcessingTransactionsTransactionsExampleStepsExampleHomework #2Homework #2InteractionCommandsHow to design?AdaptabilityCommand PatternCentral LoopCommand Line InteractionCommand HierarchyAdviceProgramming exampleJDBC IVIS 3134.24.2003Outline Quiz 1 Stored procedures Batch processing Transactions Homework #2Quiz 1 Ave: 9.25 Mode: 10 Min: 3 Max: 15Stored ProceduresDistributed ApplicationClientprogramDatabaseJDBCDriverWhere to put computation? All client use database just to store copy to client to compute All server use client just to display use database for computationClient-Server Trade-offIt dependsClientServer busyServerEither OKServer availableData largeData smallStored Procedures Database-resident code Serve many purposes computations too complex for SQL triggers move computation to the dataSteps1. Write stored procedure2. Create a callable statement3. Set the parameters4. Call the procedure5. Possibly get return valuesJava Stored Procedure (Oracle)public static void updateAvailableRoomData(int hotelId, String roomType, int mumOfRooms, String updateFlag){ Connection connection = null; // Database Connection Object try {connection = new OracleDriver().defaultConnection(); String addBookedSeats = " + "; String subTotalSeats = " - ";if (updateFlag.equals("COUT")) { addBookedSeats = " - "; subTotalSeats = " + ";} if (roomType.equals("ORCL")) roomType = "OTHR"; PreparedStatement pstmt = connection.prepareStatement("UPDATE ROOM_AVAILABILITY " + " SET BOOKED_" + roomType + " = BOOKED_" + roomType + addBookedSeats + mumOfRooms + " , TOTAL_" + roomType + " = TOTAL_" + roomType + subTotalSeats + mumOfRooms + " WHERE HOT_ID = ? AND BOOKING_DATE = " + " ( SELECT MAX(BOOKING_DATE) FROM ROOM_AVAILABILITY " + " WHERE HOT_ID = ? )" ); pstmt.setInt(1,hotelId); // Bind the Hotel ID input parameterpstmt.setInt(2,hotelId); // Bind the Hotel Id input parameter intnoRecordsUpdated = pstmt.executeUpdate(); // Execute the Statement … et cetera …From clientCallableStatement stmt = con.prepareCall (“Call Hotel_HotelBookingsSample_updateAvailableRoomData (?,?,?,?)”);stmt.setInt (1, 5);stmt.setString (2, “Single”);stmt.setInt (3, 1);stmt.setString (4, “COUT”);stmt.execute();Return values?stmt.registerOutParameter (3, Types.INTEGER);stmt.execute();int result = Stmt.getInt(3);Summary – Stored Procedures Embed computation in database using database-specific code  benefits move computation to data drawbacks SP code not portable maintenance problematic still frequently used for performance benefitsJDBC ClassesDriverManagerConnectionStatementPreparedStatementCallableStatementResultSetData Types «Creates»getConnection«Creates»createStatement«Creates»prepareStatement«Creates»prepareCall«Creates»executeQuery«Creates»executeQuery«Creates»getXXX«Modifies»setXXX«Modifies»setXXX«Creates»getXXXBatch UpdatingLimit Data Movement Cost of moving data between client and server One solution Stored procedure: move computation to data Another Try to reduce the cost by reducing overhead Communication costs are “chunky”Non-linear Communication CostsCommunication timeData quantityExample Suppose 12 small operations Execution time = 12 * (op_time + comm_in + comm_out) If grouped together = 12 * op_time + comm_in + comm_outSteps1. Turn off auto commit2. For each update, call addBatch instead of executeUpdate3. when complete, call executeBatchBatch processingcon.setAutoCommit(false);… JDBC calls …Statement stmt = con.prepareStatement (“INSERT ...;”);... set parameters ...// ready to updatestmt.addBatch(); // instead of executeUpdate()... more stuff ...stmt2.addBatch ();... more stuff ...stmt3.addBatch (); // updates completecon.executeBatch();con.setAutoCommit(true);Summary - Batch Processing Useful when there are many updates Benefits Lower communication overhead to database Problems Each statement treated separately No chance to handle exceptions that affect later updatesTransactions Goal In the face of machine failure, keep the database consistentTransactions Atomicity No partial success Consistency End point is consistent Isolation Transaction invisible until completed Durability Committed actions survive system failureExample Reservation is added Actions Record inserted into Reservation table Capacity table updated with new reservation countSteps 1. Set auto commit to false 2. perform updates as normal 3. Commit transaction 4. Set auto commit back to true If there is a failure, rollback the transaction typically exception handlerExampletry { conn.setAutoCommit (false);addReservation (conn);updateCapacity (conn);conn.commit ();} catch (SQLException e){ try { conn.rollback ();DBUtilities.displaySQLException (e);} catch (SQLException e2) { DBUtilities.displaySQLException (e2); }} finally{ try{ conn.setAutoCommit (true);} catch (SQLException e3) { DBUtilities.displaySQLException (e3); }}Homework #2Homework #2 Frequent Flyer Program Database Interactive text modeInteraction% java -cp "." FFTextModeSetting up connection to jdbc:odbc:hwk2FF: levelEnter benefit level: 1Id Name Since Level5 Rubble, Barney 02/01/01 19 Mouse, Minnie 02/03/85 110 Spratt, Jack 11/11/99 1FF: add-flightEnter flight id: 2032Enter flight no: 359Enter date (mm/dd/yy): 3/4/03Enter origin: ORDEnter destination: PHXEnter miles: 1500Enter member ids: (-1 to halt) 2Enter member ids: (-1 to halt) 8Enter member ids: (-1 to halt) -1FF: exitCommands add-flight find-by-level help exit flights delete-flight find by name membersHow to design?Adaptability What are the most likely vectors of change?Command Pattern Represent users action with an object Framework in which command objects are created and executedCentral Loop Loop Create command object Set parameters ExecuteCommand Line Interaction% java FFTextMode delete-flight 37%Command HierarchyAdvice Start right away Don’t use JDK 1.3 Close statements and result sets Start