jGuru: Using executeUpdate()

Print-friendly Version

by

[Help | API Docs | Short Course| Exercises]

4J management has decided that selling, and possibly giving away, T shirts would be a good marketing move. As a result, there is a need for a new table named JJJJTee. Chrissie wrote the program Create4JTee.java, provided here for this purpose ( although the reader may want to take the opportunity to write a program to create the table herself. ) The SQL statement for the table is:

CREATE TABLE JJJJTee (
   Entry      INTEGER      NOT NULL, 
   Customer   VARCHAR (20) NOT NULL, 
   TSize      VARCHAR (10) NOT NULL, 
   TColor     VARCHAR (10) NOT NULL, 
   PRIMARY KEY( Entry )
                     )

There will be a survey among 4J customers to obtain size and color preferences in order to determine the type and number of Tees to order. Hal notes that there may be changes to the data and requests that S&T provide a maintenance program that can handle INSERT, UPDATE or DELETE statements.

After some thought, it seems clear to Duane that about all the working code the application will need is an executeUpdate() statement. The rest will be mostly boilerplate except for getting data to executeUpdate(). For exercise purposes, data input from an external source ( which would normally be another program, a table, a transaction table or user input, ) will be simulated by a ResourceBundle backed up by properties files. The format of these files is very basic: a sequential number key and the corresponding SQL statement:

#PropertiesResourceBundle for EURun Properties
# Key is 1, 2, 3...n; SQL DML statement to be executed
1=INSERT INTO JJJJTee VALUES ( ... )
2=INSERT INTO JJJJTee VALUES (... ) 

Four of these files are provided to simulate the data changes over the next few days ( run them in the order shown: )

• EURunInsert.properties - Initial entries



• EURunDelete.properties - Later, it's discovered that Customer numbers 20 ( Wavy ) and 24 ( Ralph ) should be removed.



• EURunUpdate.properties - Later yet, 4J management decides that only certain colors will be allowed and existing choices outside of this range must be resurveyed and updated in the table.



• EURunMixed.properties - Even later, there are a mixture of INSERTs, UPDATEs and DELETEs to be applied.

EURun requires exactly one properties file name as an input argument and should be invoked as java EURun propertiesFileName. Be sure that Create4JTee or your equivalent is run first to create the table. The tasks below are only for EURun.java.

You can use the ConnectJ program presented in Generalizing Connection Information - Interactive to review the state of the JJJJTee table after each run.

Note that the EURun.java application, in some ways, amounts to two steps forward and one step back. While providing a general updating mechanism, it requires a complete and correct SQL statement for each change. This works pretty well as a learning device and may provide some alternative ideas for processing, but is not practical in a production environment of any size.

Secondly, the application is dangerous in the sense that it attempts to blindly execute anything handed to it. Clearly, editing and/or parsing is necessary to make it minimally acceptable for any sort of real processing.

Prerequisites

• Data Retrieval
• Generalizing Connection Information - Batch

Skeleton Code

• Create4JTee.java
• ConnectU.properties
• EURunInsert.properties
• EURunUpdate.properties
• EURunDelete.properties
• EURunMixed.properties
• EURun.java

Tasks

1. In doUpdate(), retrieve the processing ResourceBundle using the input argument sargRBName; enumerate the ResourceBundle keys and get a count in the int iCount.

2. In doUpdate(), code a for loop using int i to count the number of passes and contain the current key value; On each pass, use i to create a key String and access the data for that key; Execute the retrieved SQL with executeUpdate() and get the total rows processed in int iProcessed.

3. In doUpdate(), insert code to report rows processed when loop is complete.

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

Solution Source

• Create4JTee.java
• ConnectU.properties
• EURunInsert.properties
• EURunUpdate.properties
• EURunDelete.properties
• EURunMixed.properties
• EURun.java

Demonstration

When this program has been compiled and run, it will execute any properly constructed INSERT, UPDATE, DELETE or DDL statement loaded into and in the expected format of the EURunXXX.properties files. It will report the number of rows affected and any SQL syntax problems.

On completion of a successful run, the program will report:

x rows processed.

where "x" indicates the number of rows affected.

Next Exercise

Exercises

Short Course

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