You can build queries using JReport Query Editor or imported external SQL file. But in either way, the queries should be pre-defined. Yet sometimes you want to specify the query at runtime. For example, in catalog, you may build a query "select customers_id from customers", however, at runtime, You may want to fetch data from another table customers1, the query should be replaced according to the table index, like "select customers_id from customers1". Starting from version 5, JReport increases a new feature called Dynamic Query. With it, the queries could be dynamically generated and you could fetch data from different tables at runtime.
The dynamic query interface SQLStmtCreator is stored in the archive files - JREngine.zip under %JReportDesignerInstallRoot%\lib. It is contained in the package toolkit.db.api, and can be applied to any existing query in the catalog.
The following flowchart illustrates how the interface works when creating a dynamic query. The QueryInfo object will be passed from JReport Engine to the interface as an input, and the completed SQL statement should be returned from the interface. Then this completed SQL statement will be sent to the database eventually to get result set for the report.
This interface is very simple with only one method:
- getSQLStmt(QueryInfo queryInfo);
It receives information of a query and returns a SQL string. QueryInfo is a container that contains all information to build a SQL string. Users can call getXXX() methods to get all information step by step.
The structure of QueryInfo is described as follows:
- ConnectionInfo
Driver, URL, User, Password, DateFormat, TimeFormat, TimestampFormat, TransactionIsolation level, ReadOnly, QualifiedNamePattern, ExtraNamePattern, EncodingPattern- Column array
All the selected columns. Elements in this array are ColumnInfo object. ColumnInfo contains Mapping Name, Real Name, Table Info and Expression if this is a computed column.- Tables(array)
Selected tables. Elements in this array are TableInfo objects. TableInfo contains Mapping Name, Real Name, Correlation Name, Schema, Catalog.- Joins(array)
Elements in this arrays are JoinInfo objects. JoinInfo contains Column from, Column to, Operator, Join type, etc.- QBEs(array)
Part of the where condition. It is retrieved from the query builder. Elements in this array are QBEInfo objects. QBEInfo contains ColumnInfo with QBE condition bound to this column.- Ands(array)
Part of the where condition. It is retrieved from advanced search condition in the query builder. Elements in the array are AndInfo objects. AndInfo contains Left expression, Operator, Right expression, and Logic.- SubLinks(array)
If the dynamic query is for a subreport, then the SubLinks is used to the additional where clause to filter data. It is the way for how to link this query to the main report. Elements in this array are composed of SubLinksInfo objects. SubLinkInfo contains Column, Operator and Value.- Parameters (array)
Parameters used to create query, and users need to encode parameters that database can recognize. The elements in this array are ParameterInfo objects. ParameterInfo contains Name, Type and Value.- OrderBys(array)
Elements in the array are OrderByInfo objects. OrderByInfo contains Column and sorting direction.- Other Information
Query name, IsDistinct, WherePortionString etc. The value of WherePortionString is set via JRengine.setWherePortionString().Reference Notes: JReport Javadoc toolkit.db.api.SQLStmtCreator interface. You can go to http://www.jinfonet.com/webpage/kbase/kbase.htm to browse the file online.
1. Compile the java file that you write and append the path.
You should add the compiled java file with the valid path to the classpath of JReport.bat.
2. Set value of the connection property SQLStmtCreator.
The dynamic query interface is set as a property in the Connection object in the catalog.
In JReport Designer, launch Catalog Browser, expand the Connection node, select the connection, click on the Expand button to display the property of the connection. You will find the SQLStmtCreator property, which is used to set the real class name of this dynamic query object.
For example, you implemented the interface by the class UserSQLStmtCreatorImpl, then input the property value as "UserSQLStmtCreatorImpl;@para1;@para2". Para1 and Para2 are parameters used to specify criteria while creating the query.For example, school year which will change at runtime, it needs to be inserted to the report template so that the input value for these parameters at run-time could be passed into the dynamic query interface via QueryInfo object. And thus a corresponding SQL statement could be returned by this interface.
Please note that binding parameters are not supported by this feature.Note: By default, you are not allowed to change the property values in Catalog Browser. To make the property values editable, go to the File menu and select Options..., uncheck forbid editing data object properties under the Advanced tab in the Report Option dialog. Then, go back to Catalog Browser, highlight the column name that you want to edit to change or set the property values.
3. Set value of the query property EnableSQLStmtCreator
In Catalog Browser, in the Properties list of a highlighted query, there is a property named EnableSQLStmtCreator, which indicates whether this query uses the dynamic query interface to get result set or not. When it is set as true, the query could be re-generate at runtime using the dynamic query interface.
Example: Using a dynamic query
- We here provide a demo code implementing the dynamic query inerface - SQLStmtCreatorImpl.java under %JReportDesignerInstallRoot%\help\pro-meterial. This file is also available on our web site at http://www.jinfonet.com/kbase/kbase.htm (manual related materials).
This demo is to change the table name for the query sent to the Database. Specifically, when you run the report, for the tableIndex parameter, you enter 1, the query will be dynamically changed to Customer1. If you do not enter anything, you will get the result set from the Customer table.
Open this demo source code, you will see some sample methods that you can change to dynamically control the query's clauses like
Select
From
Where
Order by
...- To compile SQLStmtCreatorImpl.java you need another file MappingNameFinder.java (%JReportDesignerInstallRoot%\help\pro-meterial). You can find this file alike on our website.
Compile MappingNameFinder.java:
javac MappingNameFinder.java- Compile SQLStmtCreatorImpl.java, (suppose JReport Designer is installed under C:\JReport and the class files of the MappingNameFinder.java are under c:\jreport\help\pro-material):
javac -classpath C:\jreport\lib\JREngine.zip; c:\jreport\help\pro-material; SQLStmtCreatorImpl.java
- Modify %JReportDesignerInstallRoot%\bin\JReport.bat to add the path of SQLStmtCreatorImpl into the classpath in JReport.bat. For example, SQLStmtCreatorImpl is under %JReportDesignerInstallRoot%\help\pro-meterial, then add the class as below:
"c:\jdk1.3.1\jre\bin\java.exe" "-Dinstall.root=C:\JReport" -classpath "c:\jreport\help\pro-material;C:\JReport\lib\report.zip;C:\JReport\lib\resource.zip;C:\JReport\lib\JREngine.zip;...- Start up JReport Designer with the modified JReport.bat.
- Open report DynamicQuery.cls, and launch Catalog Browser. Select the connection, unfold its property list by clicking the Expand button, set the value of the SQLStmtCreator prorperty as SQLStmtCreatorImpl;@tableIndex. Parameter "tableIndex" is used to specify which table will be selected at runtime.
- Select query "DynamicQuery" in Catalog Browser, and set the value of its property EnableSQLStmtCreator as true.
- Save the report and view it.
- The following dialog pops up.
When you input "1" as the value of tableIndex, the report will run on table "Customers1", and when you input nothing as the value of tableIndex, the report will run on table "Cutomers".
