jGuru: Using Prepared Statements

Print-friendly Version

by

[Help | API Docs | Short Course| Exercises]

Management at the 4J Cafe have informed Hal that their bosses like the JJJJData information, but want to see the days of the week in full, that is Monday as "Monday" and not "Mon". But, 4J Cafe management also wants reports in the old format. In addition, they've decided that "Jus'Joe" is clearly a superior name to "JustJoe" and any columns with the old value should be changed. Duane and Chrissie are very happy to be involved in such a productive project, and, whistling while they work, they review the tasks ahead.

Notice that this program changes the structure of the JJJJData table. Even so, all of the other exercise applications still work properly. Of course, they will not pick up the new data without changing SELECT or UPDATE statements ( after all, there is no magic, ) BUT, this is a powerful demonstration of the value of data independence.

Prerequisites

• Creating and Populating a Table
• Using executeUpdate()

Skeleton Code

• ConnectU.properties
• PrepareAlt.java

Tasks

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".

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.

3. Prepare both PreparedStatements.

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.

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.

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

Where help exists, the task numbers above are linked to the step-by-step help page.

Solution Source

• ConnectU.properties
• PrepareAlt.java

Demonstration

Before PrepareAlt is run, the data in the JJJJData table is structured and has values as shown in A Complete Example. After adding the LDOW column and updating the data as discussed in the excercise, PrepareAlt should report ( and the tables should contain ) the data as follows:

Column LDOW added to Table JJJJData.
15 Rows updated for LDOW in JJJJData.
2 Rows updated to Jus'Joe in JJJJData.
1,   John,     Mon,   1,   Jus'Joe,      Monday
2,   JS,       Mon,   1,   Cappuccino,   Monday
3,   Marie,    Mon,   2,   CaffeMocha,   Monday
4,   Anne,     Tue,   8,   Cappuccino,   Tuesday
5,   Holley,   Tue,   2,   MoJava,       Tuesday
6,   jDuke,    Tue,   3,   Cappuccino,   Tuesday
7,   Marie,    Wed,   4,   Espresso,     Wednesday
8,   JS,       Wed,   4,   Latte,        Wednesday
9,   Alex,     Thu,   3,   Cappuccino,   Thursday
10,  James,    Thu,   1,   Cappuccino,   Thursday
11,  jDuke,    Thu,   4,   Jus'Joe,      Thursday
12,  JS,       Fri,   9,   Espresso,     Friday
13,  John,     Fri,   3,   Cappuccino,   Friday
14,  Beth,     Fri,   2,   Cappuccino,   Friday
15,  jDuke,    Fri,   1,   Latte,        Friday

Next Exercise

Exercises

Short Course

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