One of the powerful features of Chart API is its ability to fetch data from a database directly through JDBC. With this approach, all you need to do is to specify the information necessary to connect to the database and the precise form of the SQL statement. Thus your program can connect to virtually any database provided that a JDBC driver is available.

The database and query information must be stored in a DBInfo object prior to constructing the chart. Use the following code to instantiate the DBInfo object.

DBInfo dbinfo = new DBInfo(

                  "jdbc:odbc:ODBCDatabase",           // URL
                  "sun.jdbc.odbc.JdbcOdbcDriver",     // JDBC driver
                  "myName",                           // username
                  "myPassword",                       // password
                  "select * from sales");             // SQL

Since DBInfo implements the interface IDatabaseInfo, you can use the DBInfo object in the following QbChart constructor:

public QbChart(Applet parent, int dimension, int chartType, IDatabaseInfo dbinfo, ColInfo colMap, String templateFile);

In some cases, you may not wish to pass the entire database information (such as userid, password, location, driver, and the query) to ERES. You may want to make the connection yourself and just provide the result set of the query directly to the API. This can be done by creating a QueryResultSet object from the ResultSet object you have generated and passing that QueryResultSet object as the data source, instead of a DBInfo object.

// Create a QueryResult object from a java.sql.ResultSet object resultSet
QueryResultSet queryResultSet = new QueryResultSet(resultSet);

// use queryResultSet in place of IResultSet data in the following constructor
QbChart(java.applet.Applet applet, int dimension, int chartType, IResultSet data, IColumnMap cmap, java.lang.String template) 

In the above example, an instance of class QueryResultSet is created from the ResultSet object passed to the API and this instance is passed to the QbChart constructor to create the chart object. Please note that in this scenario, the chart is not refreshable. To update the chart, you will need to make the connection to the database again and pass the result set object to the chart and refresh it yourself.

// create a DBInfo object with JNDI lookup name and query
String JNDIName = "java:comp/env/jdbc/TestDB";
String query = "select * from testdata";

// The environment hashtable is empty for tomcat because ERES Server is
// running inside Tomcat context. If other application server is used,
// need to set INITIAL_CONTEXT_FACTORY and PROVIDER_URL.
Hashtable env = new Hashtable();
DBInfo dbInfo = new DBInfo(JNDIName, query, env);

public QbChart(Applet parent, int dimension, int chartType, IDatabaseInfo dbinfo, ColInfo colMap, String templateFile);

8.C.1.2. JNDI example using Tomcat and EspressManager running from servlet

Woodview database connection

Data for generating a chart can also be imported from a data file, which can be a text file as well as an XML formatted file. A chart data file (with a .dat extension) is a plain text file where each line represents one record, except the first two lines, which contain the data types and field names, respectively. Here is an example of a data file, which contains four records and where each record has three fields. The first line specifies the data type of each field, and the second line specifies the field name.

string, date, decimal
Name, Day, Volume
"John", "1997-10-3", 32.3
"John", "1997-4-3",  20.2
"Mary", "1997-9-3", 10.2
"Mary", "1997-10-04", 18.6

The use of the comma character is optional, but most database programs can output a file in this format (except for the first two lines) when exporting records from a table in text format. As a result, even if you do nott have a JDBC driver for your database, you can still quickly produce charts. You can simply export the data from your database in text format and then your program, using the ERES API, can read it.

For XML format, the specification is as follows:

<EspressData>

      <DataType>string</DataType>
      <DataType>date</DataType>
      <DataType>decimal</DataType>
      <FieldName>Name</FieldName>
      <FieldName>Day</FieldName>
      <FieldName>Volume</FieldName>
      <Row>
            <Data>John</Data>
            <Data>1997-10-3</Data>
            <Data>32.3</Data> </Row>
      <Row>

        <Data>John</Data>
        <Data>1997-4-3</Data>
        <Data>20.2</Data>
    </Row>
    <Row>
        <Data>Mary</Data>
        <Data>1997-9-3</Data>
        <Data>10.2</Data>
    </Row>
    <Row>
        <Data>Mary</Data>
        <Data>1997-10-4</Data>
        <Data>18.6</Data>
    </Row>
</EspressData>

For more details about XML Data, please refer to the Section 3.1.4 - Data from XML and XBRL Files.

Specifying the text file to use is very straight forward. Use the following constructor and replace the variable filename with the file path (relative or full path) of the data file. If you are connecting to the server, relative paths should be in respect to the ERES root directory. Otherwise, the path will be relative to the current working directory. Also replace the fileType parameter with one of the following options: QbChart.DATAFILE, QbChart.QUERYFILE, or QbChart.XMLFILE

public QbChart(Applet parent, int dimension, int chartType, int fileType, String filename, boolean doTransposeData, ColInfo colMap, String templateFile);

The same constructor can also be used for passing in XML data generated by a servlet. You can specify the fileType as QbChart.XMLFILE and the filename as the URL to your servlet (e.g. http://localhost:8080/servlet/XMLDataGenerator).

In addition to the above, ERES allows you to retrieve data and query XML files. XML data can be in virtually any format, but you need to specify a DTD file or an XML schema along with the XML data. The following code demonstrates how to set up an XML query:

// Set up the XML Data Source
String xmlfilename = "Inventory.xml";
String xmlcondition = "/Inventory/Category/Product/ProductID < 45";

XMLFieldInfo[] fields = new XMLFieldInfo[5];
fields[0] = new XMLFieldInfo(new String[] {"Inventory", "Category", "Product"}, "ProductID");
fields[0].setAttributeDataType(DTDDataType.INT);

fields[1] = new XMLFieldInfo(new String[] {"Inventory", "Category", "Product", "ProductName"});

fields[2] = new XMLFieldInfo(new String[] {"Inventory", "Category", "Product", "UnitPrice"});
fields[2].setElementDataType(DTDDataType.DOUBLE);

fields[3] = new XMLFieldInfo(new String[] {"Inventory", "Category", "Product", "UnitsInStock"});
fields[3].setElementDataType(DTDDataType.INT);

fields[4] = new XMLFieldInfo(new String[] {"Inventory", "Category", "Product", "ShipDate"});
fields[4].setElementDataType(DTDDataType.DATE);
fields[4].setDateFormat(XMLDataTypeUtil.YYYY_MM_DD);

XMLFileQueryInfo xmlInfo = new XMLFileQueryInfo(xmlfilename, fields, xmlcondition, fields);

The XMLFieldInfo instance is created using one of two constructors. The first constructor, used to select xml fields, contains one parameter. For this constructor, you need to pass in a String array that specifies each xml tag in the hierarchy leading to the target field. In the above example, fields[1-4] are created using the first constructor. The second constructor, used to select xml attributes, contains two parameters. In addition to the String array parameter, the second constructor also requires another string for the attribute name. In the above example, field[0] is created using this constructor because “ProductID” is an attribute of “Product”.

You may have also noticed that for any non-String field, you must explicitly set the data type. Once you have created the XMLFileQueryInfo instance, you can use the following constructor to create the QbChart.

public QbChart(Applet applet, int dimension, int chartType, XMLFileQueryInfo xmlInfo, boolean doTranspose, int[] transposeColumns, IColumnMap colMap, String templateFile);

You can also pass in an XML stream instead of an XML file, when using XML data as a data source. To pass in an XML stream, you would pass in the byte array containing the XML data instead of the XML data file name.

In the above example, you can pass in a XML stream via a byte array (for example, a byte array called xmlByteArray) in the XMLFileQueryInfo constructor:

XMLFileQueryInfo xmlInfo = new XMLFileQueryInfo(xmlByteArray, fields, xmlCondition, fields);

The API allows input data to be passed directly in memory, as an array. This is made possible by the interface IResultSet (defined in quadbase.util package). This interface is used to read data in the tabular form, and is quite similar to the java.sql.ResultSet interface used for JDBC result sets (but is much simpler). Users can provide their own implementations of IResultSet (which is discussed in the next section), or use one provided by ERES. The simplest implementation is provided by the class DbData (Other classes that provide an IResultSet implementation are QueryResultSet and StreamResultSet). If you can fit all the data you need for the chart in memory, you can simply pass the array as an argument in DbData with one line of code. There are three constructors for DbData:

We will use the following constructor in the example here.

public DbData(String dataType[], String fieldName[], String records[][]);

This is a similar construction to reading in data from a data file. Here, the first argument presents the data types (the first line in the data file) and the second argument presents the field names (the second line). The third argument, records[][], provides an array of records, records[i] being the ith record. The following shows how it works.

String dataType[] = {"varchar", "decimal"};
String fieldName[] = {"People", "Sales"};
String records[][] = {{"Peter", "93"}, {"Peter", "124"},
                     {"John", "110"}, {"John", "130"},
                     {"Mary", "103"}, {"Mary", "129"}};

DbData data = new DbData(dataType, fieldName, records); 

To create the chart, use the following QbChart constructor:

public QbChart(Applet parent, int dimension, int chartType, IResultSet data, ColInfo colMap, String templateFile);

For maximum flexibility, you can retrieve and prepare the dataset in any way you want and pass it to the charting engine. To pass in your class file as the data source, your class file must implement the IDataSource interface. Given below is an example of code that implements IDataSource and passes in a class file as the data source:

public class CustomClassData extends Applet implements IDataSource {

      // Setting DbData for passing data as arguments
      String dataType[] = {"string", "String", "double"};
      String fieldName[] = {"Destination", "Time", "Price"};
      String records[][] = {{"Mayfair", "13:43", "3.50"},
            {"Bond Street", "13:37", "3.75"},
            {"RickmansWorth", "13:12", "5.25"},
            {"Picadilly", "13:24", "3.00"}};
      DbData data = new DbData(dataType, fieldName, records);

      public IResultSet getResultSet()
      {
            return data; } 

}

Full Source Code

The example above creates data (DbData instance) and stores it in memory. When the getResultSet() method is called, it returns the DbData object which implements the IResultSet interface. Keep in mind that it is not necessary to create your data in this manner. As long as you are able to return an object that implements IResultSet, you can obtain the data from any data source. Use the following constructor to create your chart:

public QbChart(Applet parent, int dimension, int chartType, int fileType, String filename, ColInfo colMap, String templateFile);

For custom class files, set the fileType to QbChart.CLASSFILE and the filename to the name of the classfile.

Please note that if you are passing in your own class file as the data source and you are using the ERES Server, the class file must be accessible from the CLASSPATH of the ERES Server.

You can also pass in a parameterized class file as the data source for the chart. The parameter is obtained at run-time from the user and the data is then fetched and used to generate the chart. Note that this will only work for a stand-alone chart configuration.

public class ParameterizedClassFile implements IParameterizedDataSource {
	public IQueryInParam[] getParameters()
	{
		SimpleQueryInParam[] params = new SimpleQueryInParam[1];
		params[0] = new SimpleQueryInParam("param2", "Enter the price:", false, null, null, Types.INTEGER, new Integer(2), null);
		return params;
	}
	
	public IResultSet getResultSet(IQueryInParam[] params) {
		double price = 3.5;
		if ((params != null) && (params.length >= 1))
		{
			Object obj = params[0].getValue();
			if ((obj != null) && (obj instanceof Integer)) price = ((Integer)obj).intValue();
		}
		String dataType[] = {"string", "String", "double"};
		String fieldName[] = {"Destination",  "Time", "Price"};
		String records[][] = {{"Mayfair",  "13:43", price+""},
				{"Bond Street", "13:37", price+""},
				{"Rickmansworth ", "13:12", price+""},
				{"Picadilly",  "13:24", price+""}};
		return new DbData(dataType, fieldName, records);
	}
}

Full Source Code

When using a parameterized class file as the data source, the parameter dialog box is usually a text box with no choices available. However, you can specify what the selection choices can be (available via a drop-down box) by implementing the IQueryParamValuesProvider interface.

public class CustomParamClassFile implements IParameterizedDataSource {

	public IQueryInParam[] getParameters() {
		mySimpleQueryMultiValueInParam[] params = new mySimpleQueryMultiValueInParam[1];
		params[0] = new mySimpleQueryMultiValueInParam("region", "Select Region(s):", true, "Customers", "Region", Types.VARCHAR, "East", null);
		return params; 
	}

	public IResultSet getResultSet(IQueryInParam[] params) {
		QueryResultSet data = null;
		ResultSet rs = null;

		String paramValue = "'East'";
		if ((params != null) && (params.length >= 1)) {
			Vector selectedValues = null;
			if (params[0] instanceof IQueryMultiValueInParam)
				selectedValues = ((IQueryMultiValueInParam)params[0]).getValues();

			for (int i = 0; i < selectedValues.size(); i++) {
				if ((selectedValues.get(i) != null) && (selectedValues.get(i) instanceof String)) {
					if (i == 0) paramValue = "'" + (String)selectedValues.get(i) + "'";
					else paramValue += ",'" + (String)selectedValues.get(i) + "'"; 
				} 
			} 
		}
		String myQuery = "select cu.region, c.categoryname, count(o.orderid), sum(od.quantity), sum(p.unitprice * od.quantity) from customers cu, categories c, products p, orders o, order_details od where cu.customerid = o.customerid and c.categoryid = p.categoryid and p.productid = od.productid and o.orderid = od.orderid and cu.region in (" + paramValue + ") group by cu.region, c.categoryname";

		try {
			Class.forName("org.hsqldb.jdbcDriver");

			String url = "jdbc:hsqldb:help/examples/DataSources/database/woodview";
			Connection conn = DriverManager.getConnection(url, "sa", "");
			Statement stmt = conn.createStatement();

			rs = stmt.executeQuery(myQuery);
			data = new QueryResultSet(rs);
			// conn.close(); 
		} catch (Exception ex) {
			ex.printStackTrace(); 
		}
		return data; 
	} 

	public class mySimpleQueryMultiValueInParam extends SimpleQueryMultiValueInParam implements IQueryParamValuesProvider {

		public String paramName, promptName, tableName, colName;
		boolean mapToColumn;
		int sqlType;
		Object defaultValue;
		Vector values;
		public mySimpleQueryMultiValueInParam(String paramName, String promptName, boolean mapToColumn, String tableName, String colName, int sqlType, Object defaultValue, Vector values) {
			super(paramName, promptName, mapToColumn, tableName, colName, sqlType, defaultValue, values); 
		}

		public Vector getSelectionChoices() {
			System.out.println("getSelectionChoices called");
			try {
				Class.forName("org.hsqldb.jdbcDriver");

				String url = "jdbc:hsqldb:help/examples/DataSources/database/woodview";
				Connection conn = DriverManager.getConnection(url, "sa", "");
				Statement stmt = conn.createStatement();
				String query = "SELECT DISTINCT " + getColumnName() + " FROM " + getTableName();
				ResultSet rs = stmt.executeQuery(query);
				Vector v = new Vector();

				while (rs.next()) {
					switch (getSqlType()) {
					case Types.INTEGER:

						v.add(new Integer(rs.getInt(1)));
						break; 

					case Types.VARCHAR:

						v.add(rs.getString(1));
						break; 
					} 
				}
				stmt.close();
				conn.close();

				return v; 
			} catch (Exception ex) {
				ex.printStackTrace(); 
			}
			return null; 
		} 

	} 

}

Full Source Code

A chart can function as a view to a spreadsheet model in a Model-View-Controller (MVC) architecture. It automatically reads the spreadsheet data and plots itself. The chart registers itself as a listener to the spreadsheet model and updates itself when notified of any changes to the spreadsheet data. A spreadsheet (Java) object is provided by the user, which is an instance of a class that implements the ISpreadSheetModel interface. The event class SpreadSheetModelEvent is used by the model to notify its listeners of any changes to data. The following example shows how a spreadsheet model can be used for a chart. It uses the utility class SimpleSpreadSheet (defined in the quadbase.util package), which implements the ISpreadSheetModel interface.

	Note
	This method is applicable only to live Java program spreadsheet objects that contain data to be plotted, and hence complements the methods for reading data from a database or data file in spreadsheet format.

String[] columnVals = {"quantity", "high"};
String[] rowVals = {"coffee", "Soft Drinks", "Fruit Juice", "Water", "beer"};
Double[][] vals = { {new Double(1), new Double(30)},

                        {new Double(3), new Double(33)},
                        {new Double(7), new Double(34)},
                        {new Double(8), new Double(40)},
                        {new Double(8), new Double(40)} }; 

// Please see quadbase.util.SimpleSpreadSheet
sss = new SimpleSpreadSheet(rowVals, columnVals, vals); 

Here the data is given in a spreadsheet format and looks like the table below:

ERES then transposes the data (this is done internally) so that the data is changed to the following:

The data mapping for the chart is then done with respect to the transposed data.

Use the following constructor to create the QbChart object:

public QbChart(Applet applet, int dimension, int chartType, ISpreadSheetModel spreadsheet, IColumnMap colMap, String templateFile);

Data can be passed from an EJB data source to the chart by allowing users to query data directly from an entity bean. To add an EJB as a data source, the EJB must first be deployed in the application server and the client JAR file containing the appropriate stub classes must be added to your classpath (or the -classpath argument of the ERES Server batch file when using the API in conjunction with ERES Server).

Unlike reports, it is not necessary to create an EJBInfo object to store the connection information. Provide the names directly into the following constructor to create a QbChart object.

public QbChart(Applet applet,
               int dimension,
               int chartType,
               String jndiName,
               String homeName,
               String remoteName,
               String selectedMethodName,
               Object[] selectedMethodParamVal,
               IColumnMap cmap,
               String template);

Full Source Code

The above code can be run both as an application and as a JNLP (More about Applets in JNLP: Section 1.3.4 - Run Applets in WebStart with JNLP file).

The constructor for this class is:

public QbChart(Applet applet, int dimension, int chartType, String jndiName, String homeName, String remoteName, String selectedMethodName, Object[] selectedMethodParamVal, IColumnMap cmap, String template);

ERES also provides a functionality to merge multiple data sources together. You can create a DataSheet object from any combination of data sources, for example, data file, database or IResultSet (see “Data Passed as an Argument” section). You can use a QbChart constructor to create a chart object from an array of DataSheet.

The following example program fragment demonstrates how to combine the data from the examples in the above sections.

// Declaration of DataSheet object to be used to merge data
DataSheet dataSheet[] = new DataSheet[3]; // Declaration For Database (Data From a Database section)
DBInfo dbinfo = new DBInfo("jdbc:quadbase:/machine/schema",
"quadbase.jdbc.QbDriver", "myName","myPassword","select * from sales"); //Declaration For Data Passed as an Argument (Data Passed as an Argument section)
String dataType[] = {"varchar", "decimal"};
String fieldName[] = {"People", "Sales"};
String records[][] = {{"Peter", "93"}, {"Peter", "124"},
                      {"John" , "110"}, {"John" , "130"},
                      {"Mary" , "103"}, {"Mary" , "129"}};
DbData data = new DbData(dataType, fieldName, records); // Create DataSheet from data file (Data From a Text File section)
// DataSheet(Applet applet, String dataFile)
dataSheet[0] = new DataSheet(this, "help/examples/data/Columnar1.dat"); // Create DataSheet from database (Data From a Database section)
// DataSheet(Applet applet, IDatabaseInfo dbInfo)
dataSheet[1] = new DataSheet(this, dbinfo); // Create DataSheet from IResultSet  (Data Passed as an Argument section)
// DataSheet(Applet applet, IResultSet data)
dataSheet[2] = new DataSheet(this, data); // create QbChart
QbChart chart = new QbChart

                        (this,                 // applet
                        QbChart.VIEW2D,        // Two-Dimensional
                        QbChart.PIE            // Pie Chart
                        dataSheet,             // DataSheet
                        colInfo,               // column information
                        null);                 // No template

	Note
	After you have created a DataSheet object, you can modify it (add/delete/update row values) by using DataSheet API. Data is not refreshable if merging data from IResultSet.

Data obtained using the above methods may also be interpreted as a spreadsheet. A spreadsheet has a grid structure, much like a table. The leftmost column and first row in a spreadsheet contain labels (or headings). Each cell represents a distinct data point comprising its row label, column label, and the cell value (in contrast to the normal tabular notation, where each row represents a distinct data point).

Consider the following example:

Suppose the data in your database is arranged as four columns as shown. You can plot the three indices (Nasdaq, Dow, and SP500) as three lines with Date as the category axis if you treat the data as in spreadsheet format.

Several QbChart constructors are provided to deal with this issue. The three main ones are listed below.

QbChart(java.applet.Applet applet, int dimension, int chartType, IResultSet data, boolean

      doTransposeData, IColumnMap cmap, java.lang.String template);

QbChart(java.applet.Applet applet, int dimension, int chartType, int fileType,

      java.lang.String filename, boolean doTransposeData, IColumnMap cmap, java.lang.String
      template);

QbChart(java.applet.Applet applet, int dimension, int chartType, IDatabaseInfo dbinfo, boolean

      doTransposeData, IColumnMap cmap, java.lang.String template);

To specify that data is in spreadsheet format, you need to set the doTransposeData flag to true.

The above constructors transpose all the columns from the second column to the last column into a 3-column table. Thus, the data type from the second column onwards must be numeric otherwise the transpose will not be successful.

ERES also allows transposing of selective columns. The columns to be transposed must share the same data type. After transposing, the original columns are removed and the new columns inserted at the end of the table data. Selective transposing can be done only once; you cannot transpose certain numeric columns and then try to transpose other numeric or string columns again.

As with the complete transposing, QbChart has several constructors to allow selective transposing. The three main ones are listed below:

QbChart(java.applet.Applet applet, int dimension, int chartType, IResultSet data, boolean

      doTransposeData, int[] transposeCol, IColumnMap cmap, java.lang.String template);

QbChart(java.applet.Applet applet, int dimension, int chartType, int fileType,

      java.lang.String filename, boolean doTransposeData, int[] transposeCol, IColumnMap cmap,
      java.lang.String template);

QbChart(java.applet.Applet applet, int dimension, int chartType, IDatabaseInfo dbinfo, boolean

      doTransposeData, int[] transposeCol, IColumnMap cmap, java.lang.String template);

To specify the columns to be transposed, you need to pass in an array containing the column indices for transposeCol and set the doTransposeData flag to true.

ERES allows data to be obtained from various data sources. You can also “transpose” the data (i.e., transform the data so that the column names become part of the data) before passing the data to the desired chart type.

When the data is transposed, the original data columns (used in the transpose) are removed from the dataset and two new columns are added at the end. These two new columns would contain the transposed column names as well as the values of the original columns. For example, if the original data set has five columns and three are selected for transpose, the new data set would have five minus three (the number of transposed columns) plus two (the new columns added) or four columns.

When transposing data columns they are two things to note:

The sections below describe the various ways the data can be transposed:

QbChart(java.applet.Applet applet, int dimension, int chartType, int fileType, java.lang.String filename, boolean doTransposeData, IColumnMap cmap, java.lang.String template)

QbChart(java.applet.Applet applet, int dimension, int chartType, int fileType, java.lang.String filename, boolean doTransposeData, int[] transposeCol, IColumnMap cmap, java.lang.String template)