Example: Setting JDBC Data on a Simple Graph

This example shows how to set JDBC data on a bar, line, area, or pie graph. This kind of code also works with three-dimensional bar, cube, ribbon, and area graphs. This example uses the setTabularData method of the graph.

Source data

The source data for this example is a table named BIBDEMO_ACTUAL, which has the following columns:

DIVISION (VARCHAR2)
LINE (VARCHAR2)
TIME_LEAVES (VARCHAR2)
ACTUAL_FACT (NUMBER)

The following table shows the first few rows in the BIBDEMO_ACTUAL table.

DIVISION LINE TIME_LEAVES ACTUAL_FACT
AUDIODIV REVENUE JAN00 533362.88
VIDEODIV REVENUE JAN00 403989.39
ACCDIV REVENUE JAN00 875973.66

Graph

The following figure shows the graph that this example creates.

sample graph from previous data

Grid of data for the graph

To get the desired graph, this example takes data from the BIBDEMO_ACTUAL table and calls the setTabularData method to set the data on the graph. The setTabularData method constructs a grid of data for the graph. That grid looks something like the following table.

empty cell AUDIODIV VIDEODIV ACCDIV
JAN01 386000 287600 567800
FEB01 385100 315300 610700
MAR01 493300 326200 607500
APR01 676100 394500 675600
MAY01 778300 449900 732100

Note: This grid is appropriate if the getDataRowShownAsASeries method of the graph returns true. If you map columns as series, then the years should be column labels, and the products should be row labels.

Sample code

From the BIBDEMO_ACTUAL table, this example retrieves the data for the cost of goods sold for the months in 2001. It takes the data from the ResultSet and places it in an ArrayList of object arrays, which it then passes to setTabularData. This code assumes that you have a JDBC connection and a graph.


// declared earlier ArrayList data = new ArrayList(); // this should be in a try block, after you get the connection // connection is a JDBC connection String query = "SELECT DIVISION, TIME_LEAVES, ACTUAL_FACT " + "FROM BIBDEMO_ACTUAL " + "WHERE LINE = 'COGS' AND " + "TIME_LEAVES LIKE '%01'"; Statement statement = connection.createStatement(); if (statement != null){ ResultSet resultSet = statement.executeQuery(query); if (resultSet != null){ while (resultSet.next()){ // for each element in the result set // create a 3-element Object array Object[] rowItems = new Object[3]; // first element is the column label in the grid rowItems[0] = resultSet.getString("DIVISION"); // second element is the row label in the grid rowItems[1] = resultSet.getString("TIME_LEAVES"); // third element is the data rowItems[2] = new Double(resultSet.getDouble("ACTUAL_FACT")); // add this array to the ArrayList data.add(rowItems); } // while going through ResultSet }// if resultSet != null else System.out.println("ResultSet is null"); statement.close(); } // if statement != null else System.out.println("Statement is null"); connection.close(); // code omitted here; you can end the try block // and catch any SQLExceptions // pass the ArrayList to setTabularData graph.setTabularData(data);

Specifying Graph Data Through the setTabularData Method
Data Requirements for Different Kinds of Graphs
Handling Problems in Graph Data
Bar Graphs
Line Graphs
Area Graphs
Pie Graphs