jGuru: Help: Using Prepared Statements

Print-friendly Version

by

[Exercise | API Docs | Short Course| Exercises]

Help is available for each task.

Task 1

The application can use the ConnectU.properties file. Duane decides to set up two arrays, one containing "Mon" through "Sun", the other containing "Monday" through "Sunday" ( to match the old and load the new data, ) and use two prepared statements. This work is done for you. Your job is to set up a query String to select all columns for reporting purposes, and two update Strings for the prepared statements. One String will be used to update the new column ( LDOW ) with a corresponding value from the second array when the DOW column contains a value from the first array. The other String will be used to update the column Type from "JustJoe" to "Jus'Joe".

Insert the following query String:

    "SELECT * FROM JJJJData",

Insert the following for the first update String:

    "UPDATE JJJJData SET LDOW = ? WHERE DOW = ?",

Insert the following for the second String:

    "UPDATE JJJJData SET Type = ? WHERE Type = ?",

Task 2

The program goes through the familiar steps of accessing the ResourceBundle and retrieving the driver name, databaseURL, userID and password; loading the driver, getting a Connection and creating a Statement. Your job: add the new column -- the SQL statement should look like this: ALTER TABLE JJJJData ADD COLUMN LDOW VARCHAR (9) -- and print that the column was successfully added.

      stmt.executeUpdate( "ALTER TABLE JJJJData "  +
             "ADD COLUMN LDOW VARCHAR (9)" );
      System.out.println(
        "Column LDOW added to Table JJJJData.");

Task 3

Prepare both PreparedStatements.

      pstmt1 = con.prepareStatement( sUpdate1 );
      pstmt2 = con.prepareStatement( sUpdate2 );

Task 4

Now the LDOW column needs to be loaded. The program has two corresponding arrays, asDOW and asLDOW, set up and the int ndx defined for use as an array index. Your job: create a loop in which, for each element of the asDOW array, you set the first parameter of pstmt1 to the current index value of asLDOW and the second parameter to the current index value of asDOW ( the relevant portion of the UPDATE statement looks like SET LDOW = ? WHERE DOW = ? ) Next, perform the update and accumulate the number of rows updated for reporting, once the loop is done.

for ( ; ndx < asDOW.length; ndx++ ) { pstmt1.setString( 1, asLDOW[ndx] ); pstmt1.setString( 2, asDOW[ndx] ); iRowCount += pstmt1.executeUpdate(); } System.out.println( iRowCount + " Rows updated for LDOW in JJJJData.");




Task 5

Now it is time to update the Type column if it contains "JustJoe". Set the first parameter of pstmt2 to "Jus'Joe" and the second parameter to "JustJoe" ( the relevant portion of the UPDATE statement looks like SET Type = ? WHERE Type = ? ) Next, perform the update, get and report the number of rows updated.

pstmt2.setString( 1, "Jus'Joe" ); pstmt2.setString( 2, "JustJoe" ); iRowCount = pstmt2.executeUpdate(); System.out.println( iRowCount + " Rows updated to Jus'Joe in JJJJData.");




Task 6

Get and report all columns for all rows of JJJJData, showing the new data.

rs = stmt.executeQuery( sQuery ); while(rs.next()) // for each row of data { iEntry = rs.getInt("Entry"); sCustomer = rs.getString("Customer"); sDOW = rs.getString("DOW"); iCups = rs.getInt("Cups"); sType = rs.getString("Type"); sLDOW = rs.getString("LDOW"); // Report each Customer System.out.println( iEntry + ",\t" + sCustomer + ",\t" + sDOW + ",\t" + iCups + ",\t" + sType + ",\t" + sLDOW ); } // end while

Copyright 1996-2000 jGuru.com. All Rights Reserved.