BackPrevious Page Next PageNext

Adding User Define Data Sources to a Catalog

Adding a UDS to a catalog

Example 1: Adding a Flat File UDS

Example 2: Adding an SQL Data Source UDS

Example 3: Adding a Java Object Data Source UDS

Demo 1: Using a simple Java object UDS

Demo 2: Using a Java object UDS with multi-levels of collections

Example 4: Adding a Dynamic UDS

JReport Designer can access data from an external user defined data source, such as a Text file, or Excel file, which is not stored in a database, or when there is no JDBC driver available.

Adding a UDS to a catalog

The general steps to add a UDS to a JReport catalog are as follows:

  1. Create a catalog or open a catalog, then in the Catalog Manager, right-click the node of a data source and choose New User Defined Data Source from the shortcut menu.

    If you want to add the UDS to a new data source in the catalog, select any of the existing catalog data source, click New Data Source on the Catalog Manager toolbar, then in the New Data source dialog, specify the name of the data source, select the User Defined connection type and click OK.

    The New User Defined Data Source dialog appears.

    Add User Defined Data Source dialog

  2. In the Name field, specify a name for the UDS as required.
  3. Provide the class name with package name in the Class Name field. You can also click Browse to find the class file. The class you enter must exist and can be found by JReport Designer, which means the class should be in the class path of the system environment or set in ADDCLASSPATH of setenv.bat/setenv.sh. After filling in this field, the class name of the interface that the class implements will be displayed automatically behind The class implements:.
  4. Specify the parameter for the UDS in the Parameter box. The PARAMETER string must match the format defined in the UDS class.

    You can reference parameters and constant level formulas predefined in the current catalog data source in the format @FieldName and the special field User Name as @username in the PARAMETER string. For example, if the PARAMETER string of a UDS is SQL=select * from employee, and you want to use the predefined parameter p_sql to replace the part after "=", then the PARAMETER string will be SQL=@p_sql.

  5. When parameters and formulas are referenced in the PARAMETER string, you can click the Edit Format button to edit the format of their values.
  6. Check the Specify Columns option to enable the column definitions list and add column definitions. If you don't specify the column definitions, JReport will get them from the result set automatically.
  7. Click OK to add the UDS.

Example 1: Adding a flat file UDS

There are three classes used in this example and their source code are AddressListUDS.java, AddressListResultSet.java and AddressListResultSetMetaData.java. The data file is AddressesList.txt. All these files are available in <install_root>\help\samples\APIUDS\txtUDS.

AddressesList.txt is a plain text file which contains data information in a table:

Laurena    Croft    34826 Atwood St.     New York City    NY    10004     USA...
Jonathan    Hopkins    5062 Brandon Green Ave.     Minneapolis    MN    55402     USA...
Jeremy    Miner    9283 Cherry Leaf Lane     Palo Alto    CA    94303     USA...
....

You can notice that each line sequentially records the name, address, state, region, zip code, country, e-mail address and other information.

If you use your own UDS classes, you must be sure that the class files can be found when running by making sure that the directory is classpath/package name. For this demo's UDS classes, the classes belong to a package named help. Copy the above necessary files to <install_root>\help, and add the additional entry to the ADDCLASSPATH variable of the batch file setenv.bat in <install_root>\bin.

Task 1: Compiling and running the Address UDS

For example, on Windows, if you installed JReport Designer into the default directory C:\JReport\Designer,

  1. Copy the above necessary files to C:\JReport\Designer\help.
  2. Compile the Java files.

    javac -classpath "C:\JReport\Designer\lib\JREngine.jar" Address*.java

  3. Add the additional entry into the ADDCLASSPATH variable of setenv.bat in C:\JReport\Designer\bin.

    set ADDCLASSPATH=%JAVAHOME%\lib\tools.jar;C:\JReport\Designer\help;

Task 2: Adding the Address 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 Catalog Manager resource tree, right-click the data source to which the UDS is to be added, then select New User Defined Data Source from the shortcut menu.
  4. In the New User Defined Data Source dialog, enter the required information:
  5. Click OK to add the UDS.

Example 2: Adding an 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.

Task 1: 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\JRDemo.

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.

Task 2: 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 Catalog Manager resource tree, right-click the data source to which the UDS is to be added, then select New User Defined Data Source from the shortcut menu.
  4. In the New 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\\JRDemo"&USER=sa&PSWD=&SQL=select * from employee

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

  5. Click OK to add the UDS.

Example 3: Adding a Java object data source UDS

The JReport Designer UDS can use any Java object as a data source for you to create reports. To implement this function, JReport Designer provides a class UDSForJavaBean, in which the getResultSet() method creates instances for the interface JavaBeanDataProvider, and then uses the method init() to initialize the data of the created instances.

Below is the Java code for class definition:

public class UDSForJavaBean 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 interface JavaBeanDataProvider is defined as follows:

public interface JavaBeanDataProvider
{
    public void init(String dataID, Properties initprops)
		  throws JavaBeanDataProviderException;
	 public Class getMetadataJavaBean()
		  throws ClassNotFoundException;
	 public Object next() throws JavaBeanDataProviderException;
	 public boolean requireDetails(String collectionPropName);
	 public int getMaxShareTimes(String collectionPropName);
	 public int getTimeoutForSubCollection(String collectionPropName);
	 public void exit()throws JavaBeanDataProviderException;
}

For examples of how to use the Java object data interface, JReport provides you with two demos which are available in <install_root>\help\samples\APIUDS\javaUDS.

Before running the demos, you need to do the following:

  1. Copy all the content in <install_root>\help\samples\APIUDS\javaUDS to <install_root>\help\UDSForJavaBean. You need to create the UDSForJavaBean directory.
  2. Compile all the java files.

    javac -classpath <install_root>\help\UDSForJavaBean;<install_root>\lib\JREngine.jar;<install_root>\lib\log4j-core-2.10.0.jar;<install_root>\lib\log4j-api-2.10.0.jar; <install_root>\help\UDSForJavaBean\jreport\uds\javabean\*.java

  3. Copy data.txt in <install_root>\help\UDSForJavaBean to <install_root>\bin.
  4. Add the path <install_root>\help\UDSForJavaBean in the ADDCLASSPATH variable in setenv.bat in the <install_root>\bin directory.

Demo 1: Using a simple Java object UDS

In this demo, a Java object named Person will be used as the data source, you can find the person.java file in <install_root>\help\UDSForJavaBean\jreport\uds\javabean\beans. It is defined as follows:

public class Person implements Serializable
    {

    private String gender;
	 private String ssn;
	 private String emailAddress;
	 private Date birthDate;
	 private Long newbornWeight;
	 private YesNo isDataVerified;
	 private Name name;
	 private Address currentMailingAddress;
	 private Phone currentWorkPhone;n;
/**
* @return the birthDate.
*/
public Date getBirthDate()
   {
   return birthDate;
   }
}

Similar to the code above, other attributes of the Person object are defined by other Java objects, such as currentMailingAddress which is defined by the Address class.

To create a report from the Person Java object:

  1. Make the necessary preparations.
  2. In JReport Designer, open an existing catalog, then in the Catalog Manager, expand the data source to which the UDS is to be added.
  3. This demo uses generated data, in order to get the real collection of the data objects, create two parameters before importing the Person Java object as follows:

    For details about how to create parameters, see Creating a Parameter.

  4. Right-click the data source node, and select New User Defined Data Source from the shortcut menu.
  5. In the New User Defined Data Source dialog, enter Person in the Name text box.
  6. For the Class Name field, click the Browse button, go to <install_root>\help\UDSForJavaBean\jreport\uds\javabean, and then choose UDSForJavaBean.class, which will import the class jreport.uds.javabean.beans.Person with full class name.
  7. In the Parameter box, type in the following string:

    JavaBeanDS_DataProvider=jreport.uds.javabean.GenericBeanDataProvider&JavaBeanDS_RuntimeDataID=persions&GBeanProvider_BeanClsName=jreport.uds.javabean.beans.Person&GBeanProvider_UseFakeData=true&GBeanProvider_NumOfFakeData=@pNumOfFakeData&GBeanProvider_RptDataInitializer=jreport.uds.javabean.SubRptCollectionDataInitializer

    All the highlighted names in the parameter string above are the key words for the information required by this UDS and related Java class. See the detailed explanation below:

    You can use this provider (jreport.uds.javabean.GenericBeanDataProvider) or create your own provider by implementing the interface jreport.uds.javabean.JavaBeanDataProvider.

  8. Click OK to add the UDS.
  9. Create a page report with a standard banded object in it which is based on this UDS.
  10. Click the View tab to run the report. In the Enter Parameter Values dialog, type 3 as the value of the parameter pNumOfFakeData, and click OK. Three records will be returned. However, the data you get now is the generated data, because in the parameter string of the UDS, you have specified the value of the key word GBeanProvider_UseFakeData to true.
  11. In order to get the real collection of data objects, the parameter pUseFakeData will be used to control the value of the key GBeanProvider_UseFakeData value dynamically as follows.

    In the Catalog Manager, right-click the UDS Person and click Edit User Defined Data Source on the shortcut menu. In the Edit User Defined Data Source dialog, modify the value of GBeanProvider_UseFakeData to @pUseFakeData in the Parameter box.

  12. Run the report again and specify the value of pUseFakeData as false to get the real collection of data at runtime.

    Note: The key word GBeanProvider_RptDataInitializer in the data provider jreport.uds.javabean.GenericBeanDataProvider is used to specify the Java class name which implements jreport.uds.javabean.RptDataInitializer interface. So if you are using the data provider jreport.uds.javabean.GenericBeanDataProvider, you just need to provide a class which implements jreport.uds.javabean.RptDataInitializer to return a collection, list, or array of the data objects according to different reports and parameters. Also, jreport.uds.javabean.GenericBeanDataProvider can recognize vector, collection and array of objects and retrieve the objects inside of the collection one by one.

Methods in the demo

Demo 2: Using a Java object UDS with multi-levels of collections

Sometimes, the attributes of a Java object are defined by other lists/collections, such as the Java object SimpleBeanTest in <install_root>\help\UDSForJavaBean\jreport\uds\javabean\beans. It is defined as follows:

public class SimpleBeanTest implements Serializable {
    private String test;
    private long l;
    private int i;
    private int[] intarray;
    private Person[] persons;
    private Collection addresses;
   	private Date dMyDate;
}

From the code above, you can see that the Java class SimpleTestBean contains an array of Persons, a collection of addresses and an array of Int values.

For this kind of Java object, JReport Designer can create a report that gets records from the SimpleTestBean, but it cannot show the list of persons information in the same report. If you want to create such a report - each record comes from the SimpleBeanTest object, and for each record, display the list of persons information - you have to use a primary report and subreport to implement this function.

Task 1: Create the primary report

  1. Make the necessary preparations.
  2. In JReport Designer, open an existing catalog, then in the Catalog Manager, expand the data source to which the UDS is to be added.
  3. Create a parameter as follows, which is used to specify the number of data records to generate that will be shown when running the report.

    Name: pNumOfFakeData
    Value Type: Integer
    Prompt Value: Any integer number. Note that the parameter must have at least one value that is larger than 0, otherwise you will get exceptions when viewing reports.

  4. Right-click the data source node and click New User Defined Data Source on the shortcut menu. The New User Defined Data Source dialog appears.
  5. Type SimpleTestBean in the Name text box.
  6. For the Class Name filed, click the Browse button, go to <install_root>\help\UDSForJavaBean\jreport\uds\javabean, and then choose UDSForJavaBean.class. The UDS class UDSForJavaBean.class will import the class jreport.uds.javabean.beans.SimpleTestBean with full class name.
  7. In the Parameter box, type in the following string:

    JavaBeanDS_DataProvider=jreport.uds.javabean.GenericBeanDataProvider
    &JavaBeanDS_RuntimeDataID=&GBeanProvider_BeanClsName=jreport.uds.javabean.beans.SimpleBeanTest
    &GBeanProvider_UseFakeData=true&GBeanProvider_NumOfFakeData=@pNumOfFakeData
    &GBeanProvider_FakeDateSubCollectionInfo=persons,jreport.uds.javabean.beans.Person
    &GBeanProvider_RptDataInitializer=&GBeanProvider_ListOfDetailProps=persons,1,30000

    All the highlighted names in the parameter string above are the key words for the information required by this UDS and related Java class. See the detailed explanation below:

  8. Click OK and the UDS SimpleTestBean is added. In the Catalog Manager, you will see that persons appears in the resource tree in the SimpleTestBean node.
  9. Create a page report named MainRpt.cls, with a standard banded object in it as the primary report based on the UDS SimpleTestBean.

Task 2: Create the subreport

  1. Expand the data source in the catalog to which you want to add the UDS for the subreport.
  2. Create a parameter as follows, which is used to specify whether to use generated data when running the report.

    Name: pUseFakeData
    Value Type: Boolean
    Prompt Value: True

  3. Create another parameter named pRunTimeDataInfo of String type, which will be used when setting up the link between the primary report and the subreport.

    Name: pRunTimeDataInfo
    Value Type: String
    Prompt Value: persons

  4. Right-click the data source node and click New User Defined Data Source on the shortcut menu. The New User Defined Data Source dialog appears.
  5. Type PersonsAsSubRpt in the Name text box.
  6. For the Class Name text box, click the Browse button, go to <install_root>\help\UDSForJavaBean\jreport\uds\javabean, and then choose UDSForJavaBean.class.
  7. In the Parameter box, type in the following string:

    JavaBeanDS_DataProvider=jreport.uds.javabean.GenericBeanDataProvider&JavaBeanDS_RuntimeDataID=@pRunTimeDataInfo&GBeanProvider_BeanClsName=jreport.uds.javabean.beans.Person&GBeanProvider_UseFakeData=@pUseFakeData&GBeanProvider_NumOfFakeData=@pNumOfFakeData&GBeanProvider_RptDataInitializer=jreport.uds.javabean.SubRptCollectionDataInitializer

    All the highlighted names in the parameter string above are the key words for the information required by this UDS and related Java class. See the detailed explanation below:

  8. Click OK, and the UDS PersonsAsSubRpt is added.
  9. Create a page report named SubRpt.cls, with a table in it as the subreport based on the UDS PersonsAsSubRpt.

Task 3: Link the primary report and the subreport

  1. In the Catalog Manager, create a formula named NotUseFakeData to return false all the time, for example, return false. This formula will be passed into the subreport as the value of the parameter pUseFakeData in the subreport, so that when the subreport runs with the primary report, it will always use the data from the primary report instead of constructing the fake data itself.
  2. Open MainRpt.cls, add a new detail panel into the report.
  3. Selected the newly added panel and click Insert > Subreport. When a box attached to your mouse pointer, click in the panel and the Subreport dialog is displayed.
  4. Click the Browse button, choose SubRpt.cls as the subreport, then in the Parameters tab, specify values for the parameters as follows:
     Parameter Value
     pNumOfFakeData pNumOfFakeData
     pUseFakeData NotUseFakeData (gives a false value to the parameter)
     pRunTimeDataInfo persons (specifies to use the sub-collection persons of the primary report as the data source of the subreport at runtime)
  5. Click OK to insert the subreport. For more information about using subreports, see the document Subreports.
  6. Run the primary report, and you will find that the corresponding persons information is displayed in the subreport.
  7. If you want to insert another subreport which shares the same sub-collection with SubRpt.cls, you should modify the value of the key word GBeanProvider_ListOfDetailProps in the primary report UDS parameter string to GBeanProvider_ListOfDetailProps=persons,2,30000. That changes the share amount of the persons property from 1 to 2.

Example 4: Adding a dynamic UDS

JReport Designer supports the Dynamic UDS feature. This feature improves performance by retrieving only the selected fields and not all the fields. At runtime, an option is provided for you to pick up the columns you want to see in the report. In this way, JReport Designer generates a dynamic report according to your selection.

In this example, SQLDataSource is used to illustrate the usage and effect of the Dynamic UDS feature. Assume that you have generated SQLDataSource.class, start JReport Designer with the modified batch file (for details, see Compiling and running SQLDataSource in Example 2).

  1. Open an existing catalog.
  2. In the Catalog Manager resource tree, expand the data source to which the UDS is to be added.
  3. Create a parameter with the following information:
  4. Right-click the data source node, and then select New User Defined Data Source from the shortcut menu.
  5. In the New User Defined Data Source dialog, enter the following information:
  6. Click OK to add the UDS.

Next, we will create a page report directly on the UDS to test the Dynamic UDS feature. If you want to create web reports and library components on this UDS, you need to first create a business view using this UDS.

  1. Click Home/File > New > Page Report.
  2. In the Select Component for Page Report dialog, specify the report title and choose the Table (Group Left) component. Click OK.
  3. In the Data screen of the Table Wizard, choose the UDS employees from the User Defined node. Then, click Next.
  4. In the Display screen, add the fields EMPLOYEEID, employees_NOTES, employees_SALARY and HIREDATE to be displayed in the table, edit their display names to ID, Notes, Salary, Hire Date, and then click Next.
  5. In the Group screen, specify to group on the field EMPLOYEEPOSITION.
  6. Skip the Summary, Chart and filter screens, and in the Style screen, specify to display the report in the Warm style.
  7. Click Finish to create the report.
  8. Click the View tab to view the report. You will then be prompted to enter a parameter.
  9. Select select * from employee as the parameter, and you will see that data for all fields has been retrieved.

    Report created on UDS with all fields

  10. Go back and run this report again. This time, choose select salary from employee. You will notice that no groups are displayed and the group name changes to NULL. This is because only the field employees_SALARY has been selected this time. JReport Designer will make no reference to the employees_Position column, on which the group is based.

    View the report created on UDS with employees_SALARY field

Note: To make a dynamic UDS work, if the SQL statement at runtime doesn't include all the UDS columns, you need to make sure that none of the UDS columns' properties were edited, that is the Specify Columns option in the New User Defined Data Source dialog should be unchecked, otherwise exceptions will be produced when the SQL statement is used to generate a dynamic report from the UDS.

BackPrevious Page Next PageNext