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.

• The format of the String variable parameter is DRIVER=driver&URL=url&USER=user&PSWD=password&SQL=sql.

  For example, we used HSQL DB as data source, and C:\JReport\Designer\Demo\db\DemoDB as the hsql db path; "sa" as the user's name, "" as the password, and select * from authors as the SQL string. The parameter string may be similar to:

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

  If you want to use predefined parameters in the JReport catalog, you can input the parameter string as:

  DRIVER="org.hsqldb.jdbcDriver"&URL="jdbc:hsqldb:C:\\JReport\\Designer\\Demo\\db\\DemoDB"&USER=@un&PSWD=&SQL=@sql

  Where, @sql is a parameter predefined in the catalog, and its default value is "select * from employee". The default value of @un is "sa".

  Note: Every time when running, java.sql.Resultset must return the same meta data (UDS fields), including the number and order of fields, and the field properties such as column name, SQL type, precision, scale, nullable, currency, and array.
• The getResultSet() method will parse the parameter string, build a connection to the URL, and then execute the SQL statement.
• The releaseResultSet() method will release the resource such as Connection, Statement, and ResultSet.

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.