The dynamic query interface

The dynamic query interface SQLStmtCreator is stored in the archive file - JREngine.jar in <install_root>\lib. It is contained in the package toolkit.db.api, and can be applied to any existing query in a catalog.

How the dynamic query interface works

The following flowchart illustrates how the interface works when creating a dynamic query. The QueryInfo object is passed from the JReport Engine to the interface as an input. Then, the completed SQL statement is returned from the interface. Finally, the completed SQL statement is sent to the database to get the result set for the report.

Method of the dynamic query interface

This interface is very simple with only one method: getSQLStmt(QueryInfo queryInfo);

It receives information of a query and returns an SQL string. QueryInfo is a container that contains all information to build an SQL string. Users can call getXXX() methods to get all information step by step.

The structure of QueryInfo is 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, and Catalog.
• Joins(array)
  Elements in this arrays are JoinInfo objects. JoinInfo includes Column from, Column to, Operator, and Join type.
• 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 the 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, the SubLinks is used for the additional WHERE clause to filter data, and this is the way to link the dynamic query to the main report. Elements in this array are composed of SubLinksInfo objects. SubLinkInfo contains Column, Operator and Value.
• Parameters (array)
  Parameters used for creating a query. Users are required to encode parameters that the 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, and WherePortionString. The value of WherePortionString is set via JRengine.setWherePortionString().