jGuru: Help: Selecting Data and Presenting Information

Print-friendly Version

by

[Exercise | API Docs | Short Course| Exercises]

Help is available for each task.

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

Set sQueryD to:

          "SELECT DISTINCT Type " +
          "FROM JJJJData",

Set sQuery to:

          "SELECT Cups, Type " +
          "FROM JJJJData " +
          "ORDER BY Type",

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

rs = stmt.executeQuery( sQueryD ); while( rs.next() ) { ndx++; }




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

      ducValues = new double[ndx][1];
      sucLegends = new String[ndx];

Task 4

Excute the ORDER BY query.

      rs = stmt.executeQuery( sQuery );

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

if( bFirstPass ) { sSaveValue = sValue; sucLegends[i] = sValue; bFirstPass = false; }




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

if( !sSaveValue.equals( sValue ) ) { // if the value changed i++; // Safeguard against additions if( i >= ndx ) { jta.append( "Not all values were " + "reported. Please retry." ); break; } sSaveValue = sValue; sucLegends[i] = sValue; }




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

        ducValues[i][0] += iCups;

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