Example 2: Developing reports from SQL data source UDS

This is a fairly complex example, which executes an SQL statement and returns a result set through the JDBC API.

Below is the Java code for class definition:

import jet.datasource.*;

public class SQLDataSource implements JRUserDataSource {
	// Define data.
	public ResultSet getResultSet(String param)
			throws JRUserDataSourceException {
		// Method body.
	}

	/**
	 * Free the resources such as: Connection, Statement, ResultSet.
	 */
	public void releaseResultSet() throws JRUserDataSourceException {
		// Method body.
	}
}

The following explains the above code.

Compiling and running SQLDataSource

There is one class used in this example and its source code is SQLDataSource.java, which is available in <install_root>\help\samples\APIUDS\sqlUDS. In this example, SQLDataSource will return the result set from demo hsql db. You should set up the HSQL DB path in the URL string and point it to the demo database at <install_root>\Demo\db\DemoDB.

Copy SQLDataSource.java to <install_root>\help, and compile it to generate SQLDataSource.class. Append the path <install_root>\help to the ADDCLASSPATH variable of the batch file setenv.bat in <install_root>\bin, so that SQLDataSource can be found at runtime.

Adding the SQLDataSource UDS to a catalog

After compilation, you can now add the UDS to a JReport catalog.

  1. Start JReport Designer with the batch file you just modified.
  2. Open an existing catalog.
  3. In the Data tab of the Catalog Browser, expand the data source to which the UDS is to be added, then right-click the User Defined node and select Add User Defined Data Source from the shortcut menu.
  4. In the Add User Defined Data Source dialog, enter the required information.

    If you check the Specify Columns option, the column definitions list will be enabled and you can add column definitions. If you don't specify column definitions, JReport will obtain them from the result set automatically.

    Here, we will not specify column definitions. Instead, we will use the default ones from the ResultSet and the ResultSetMetaData. Enter employees in the Name field and SQLDataSource in the Class Name field. In the Parameter box, enter:

    DRIVER="org.hsqldb.jdbcDriver"&URL="jdbc:hsqldb:C:\\JReport\\Designer\\Demo\\db\\DemoDB"&USER=sa&PSWD=&SQL=select * from employee

    Make sure that the five bold parts in the above line must be capitalized.

  5. Click OK to add the UDS.
Developing a report with the SQLDataSource UDS

Now that the SQLDataSource UDS has been successfully added to the catalog, you can use it to develop reports as required. For details about how to create reports in JReport Designer, see Creating a page report.