jGuru: Selecting Data and Presenting Information

Print-friendly Version

by

[Help | API Docs | Short Course| Exercises]

While Hal is grabbing some brew at 4J, one of the middle management pointy heads stops by and mentions that the big boss wants "something that gives a quick grasp of coffee sales by type of coffee. By tomorrow." Hal makes a quick call to Duane.

After some thought, Chrissie decides to use the basic framework presented in Generalizing Connection Information - Interactive. Duane has done some research on presentation and decides to use the Lightweight Chart Beans package from alphaWorks, which is small, no cost and can be used in production programs.

Reader Note: This exercise won't discuss how to use the package, but there are instructions, javadoc and demos in the download, and enough code to get you started in the solution. After downloading the package, unzip it and copy the JFC 1.1 version jar ( s11_chart.jar ) into JAVA_HOME/jre/lib/ext.

Prerequisites

• Data Retrieval
• Generalizing Connection Information - Interactive

Skeleton Code

• ConnectJ.properties
• ChartJ.java

Tasks

1. Duane and Chrissie could just get Hal to take a look at the data and determine how many coffe types are curremtly sold, but this can ( and probably will ) change. They would prefer to handle this dynamically. One method that can be used involves the SQL DISTINCT keyword, which returns only the unique values in a column. With a DISTINCT query, the program can count the number of Types returned and know how to set up the chart. Once that information has been obtained, the program will requery, selecting Cups and Types. To save some work and lend things to generalization, Chrissie orders the data by Types, which allows her to use certain code structures. Here are the SQL statements needed:

   SELECT DISTINCT Type FROM JJJJData and SELECT Cups, Type FROM JJJJData ORDER BY Type

   
   

   Your job is to set up Strings with the appropriate SQL statements.

2. In the constructor, Duane changes the size of the textarea and now uses it only for reporting errors. Also, the chart is created and some basic set up work is done. The additional work is concentrated in the doConnect method, after getting connected and creating a Statement. The control variables are initialized, with bFirstPass set to true, and i and ndx, set to zero. Now it's your turn:

   Query for DISTINCT Type and accumulate a count in ndx.

3. Now that the app knows how many coffee types currently exist, create apropriately sized arrays for the chart values and legends, using the ducValues and sucLegends variables.

4. Excute the ORDER BY query.

5. In the read loop, the Cups and Type data for each row is retrieved, using the getXXX( int ) form rather than using the column name, which is slightly more efficient.

   Your job: Extract the Type from the first row and store it in the save variable sSaveValue for later comparison. Also store this value in the first element of our sucLegends array.

6. As mentioned previously, using ORDER BY saves some work, here in determining when the Type has changed and which array elements to use for storing the data. Another consideration: In a multi-user, production database, things can change rapidly. The technique here, to illustrate DISTINCT and simplify code, is to use two queries, but this allows the possibility that the data has changed between retrievals, meaning that there may be more or fewer Types than expected. As a result, safeguard code should be included to avoid potential OutOfBounds problems with the arrays.

   Your job: On every pass, determine if Type has changed. If so, increment the current index variable i. Determine if the loop has gone past the maximum allocated array element. If so, indicate to the client that some data was not reported and suggest a retry; then break out of the loop. Otherwise, store the current Type value in sSaveValue for later comparison and store this value in the current i element of the sucLegends array.

7. On every pass, accumulate Cups totals in the the ducValues array using the current element [i][0]. After this code, loading and displaying the chart data is done for you.

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

Solution Source

• ConnectJ.properties
• ChartJ.java

Demonstration

When this program has been compiled and run, it will generate a bar chart of consumed coffee by coffee type using data from JJJJData.

The resulting display should look like this:

Next Exercise

Exercises

Short Course

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