This example shows how to set JDBC data on a bubble graph. This example uses
the setTabularData
method of the graph.
The source data for this example is a table named BIBDEMO_ANALYTIC_FACTS, which has the following columns:
GEOG_LEAVES (VARCHAR2)
PROD_LEAVES (VARCHAR2)
CHAN_LEAVES (VARCHAR2)
TIME_LEAVES (VARCHAR2)
SALES (NUMBER)
UNITS (NUMBER)
COSTS (NUMBER)
PROMO (NUMBER)
QUOTA (NUMBER)
The following table shows the first few rows in the BIBDEMO_ANALYTIC_FACTS table:
GEOG_LEAVES | PROD_LEAVES | CHAN_LEAVES | TIME_LEAVES | SALES | UNITS | COSTS | PROMO | QUOTA |
---|---|---|---|---|---|---|---|---|
TORONTO | PORTCD | RETAIL | JAN00 | 2192 | 14 | 2555 | 19.98 | 2862.63 |
MONTREAL | PORTCD | RETAIL | JAN00 | 648 | 3 | 770 | 10.26 | 715.66 |
OTTAWA | PORTCD | RETAIL | JAN00 | 648 | 3 | 770 | 9.58 | 715.66 |
Note: This table has data for all of the months in 2000 and for the first five months of 2001.
The following figure shows the graph that this example creates.
To get the desired graph, this example takes data from the BIBDEMO_ANALYTIC_FACTS
table and calls the setTabularData
method to set the data on the
graph. The setTabularData
method constructs a grid of data for
the graph. The following table shows a portion of that grid. Only the columns
for three Geography values appears in this table. The complete grid has columns
for all Geography values in the ResultSet
.
![]() |
TORONTO SALES | TORONTO COSTS | TORONTO PROMO | MONTREAL SALES | MONTREAL COSTS | MONTREAL PROMO | OTTAWA SALES | OTTAWA COSTS | OTTAWA PROMO |
---|---|---|---|---|---|---|---|---|---|
JAN01 | 2131 | 2590 | 22.86 | 575 | 665 | 5.04 | 525 | 455 | 5.19 |
FEB01 | 2207 | 2415 | 24.68 | 640 | 735 | 7.82 | 572 | 420 | 3.22 |
MAR01 | 2207 | 2590 | 18.52 | 639 | 735 | 8.69 | 572 | 455 | 2.83 |
APR01 | 2207 | 2520 | 16.2 | 639 | 700 | 7.38 | 572 | 420 | 3.18 |
MAY01 | 2206 | 2870 | 17.14 | 638 | 910 | 10.64 | 571 | 525 | 6.66 |
Notice that this grid has three columns for Toronto, three for Montreal, and three for Ottawa. The "TORONTO SALES" label identifies the unique column for all of the Sales values for Toronto. The "TORONTO COSTS" and "TORONTO PROMO" are different labels, ensuring the appropriate three-column group of data for Toronto for a bubble graph.
Note: This grid is appropriate if the getDataRowShownAsASeries
method of the graph returns true
. If you map columns as series,
then the grid should have three distinct rows for each Geography value.
In an OLAP data source, you could simply place the Geography and Measure dimensions on the column edge, and the data source would create a grid that has two layers of metadata on the column edge. The simple data source that setTabularData creates supports only one layer in the column edge, so you must provide distinct labels for each column. In a bubble graph, these labels appear in the tooltips for markers, but they do not appear in any other labels in the graph.
Notice, also, that the Sales, Costs, and Promo columns always occur in the same order. The first measure column for each Geography is plotted along the X-axis in the bubble graph. The second column in the group is plotted along the Y-axis, and the third column is used to calculate the size of the bubble in the graph.
From the BIBDEMO_ANALYTIC_FACTS table, this example retrieves the data for
tuners sold in the retail channel 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
. For a bubble
graph, the code iterates over a Geography value three times, in order to make
three separate columns in the grid of data for the graph.
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 if (connection != null){ String query = "SELECT GEOG_LEAVES, TIME_LEAVES, SALES, COSTS, PROMO " + "FROM BIBDEMO_ANALYTIC_FACTS " + "WHERE PROD_LEAVES='TUNER'" + "AND CHAN_LEAVES='RETAIL'" + "AND TIME_LEAVES LIKE '%01'"; Statement statement = connection.createStatement(); if (statement != null){ ResultSet resultSet = statement.executeQuery(query); // identify the three fact columns in the ResultSet String [] measures = {"SALES", "COSTS", "PROMO"}; if (resultSet != null){ while (resultSet.next()){ // for each measure for(int i = 0; i < 3; i++){ // create a 3-element Object array Object[] rowItems = new Object[3]; // first element is the column label in the grid // concatenate Geography value with a measure rowItems[0] = resultSet.getString("GEOG_LEAVES") + " " + measures[i]; // second element is the row label in the grid // row label is the same for each measure rowItems[1] = resultSet.getString("TIME_LEAVES"); // third element is the data value // get the value from the appropriate // fact column in the ResultSet rowItems[2] = new Double(resultSet.getDouble(measures[i])); // add this array to the ArrayList data.add(rowItems); } // for each measure } // 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(); } // if connection != null // 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
Bubble Graphs