JDBC connection

The properties of a JDBC connection are:

Property Name Description
Custom Query Optimizer Allows users to specify the implementation class of the QueryOptimizer interface for viewing and optimizing the query SQL statement before being sent to the DB.

Once "Custom Query Optimizer" is specified,

  • CRD and BV cache cannot be created or scheduled any more.
  • The group level pushing down will not be supported.

Data type: String
Date Format Specifies the default Date format corresponding to the database.

Data type: String
Description Specifies the description of the JDBC connection.

Data type: String
Driver Specifies the class name of the JDBC driver such as oracle.jdbc.driver.OracleDriver. While setting up the JDBC connection with the connection dialog, JReport will use the driver that is specified to connect to the database. If no driver name is filled in, JReport will use the default JDBC driver from the file jdbcdrivers.properties in <install_root>\bin file. You can add JDBC driver names into the text file, JReport will load the drivers before building a connection. If your JDBC driver name is not correct, or you do not add JDBC driver names, the message "No suitable driver" will appear.

Data type: String
Explicit Inner Join Specifies whether to use Explicit Join notation or Implicit Join notation in the Where clause for inner joins.
  • true - Uses Explicit Join notation:

    SQL: select … from A inner join B on (A.c1 = B.c2)

  • false - Uses Implicit Join notation:

    SQL: select … from A,B where A.c1 = B.c2

Data type: Boolean
Included Schemas Displays the schema names specified for the connection in the Get JDBC Connection Information dialog. The default is blank, which indicates that all schemas in the DBMS can be used in the catalog. This property is read only. To modify it, use the Get JDBC Connection Information dialog.
Name Specifies the name of the connection which, by default, is the same as the connection URL, but can also be a user friendly name for the connection.

Data type: String
Name Pattern Specifies whether or not catalog or schema is used in data manipulation. Choose an option from the drop-down list.
  • unqualified name - Neither catalog nor schema is included in data manipulation. Example: SELECT t.c FROM t
  • 2-part names - Uses schema in data manipulation. Example: SELECT schema.t.c FROM schema.t
  • 3-part names - Uses both catalog and schema in data manipulation. Example: SELECT catalog.schema.t.c from catalog.schema.t

Data type: Enumeration
Password Specifies the password for connecting with the database, which is determined by the database DBA.

Data type: String
Pre-join Specifies whether or not to apply the pre-join information defined on the data source in which the connection is set when building a query or defining join relationships in business cubes in the same data source.

Data type: Boolean
Push Down Group Query Specifies whether to push down group level summary computations in reports to the DBMS at runtime. Choose an option from the drop-down list.
  • true - JReport will push down the group level summary computations to the DBMS if the DBMS can do the computations, otherwise, JReport will do the computations itself.
  • false - JReport will do the group level summary computations itself.

Data type: Boolean
Quote Qualifier Specifies the characters, then a qualifier name which contains the characters that will not be quoted. Choose an option from the drop-down list.
  • Default (JDBC) - If selected, the program will get the extra name characters from JDBC.
  • User Defined - If selected, you can modify the quote character according to the database system being used.

Data type: Enumeration
Read Only Specifies the mode to open the connection to the JDBC data source. The initial setting is default which uses the mode specified by the DBMS Administrator which could be read only or read & write. Choose an option from the drop-down list.
  • default - May be read & write or read only depending on the DBMS default setting.
  • read only - Allows the driver to optimize performance for reporting which does not need to write to the DBMS.
  • read & write - Opens the DBMS with updates enabled which requires more processing to ensure concurrency control.

Data type: Enumeration
Security Check Specifies whether or not to check the JDBC connection security at runtime.

Data type: Boolean
SQL Statement Creator Specifies the parameters to implement the SQLStmtCreator interface (for details about the interface and its usage, refer to Dynamic queries).

Data type: String
Time Format Specifies the default Time format corresponding to the database.

Data type: String
Timestamp Format Specifies the default Timestamp format corresponding to the database.

Data type: String
Transaction Mode Specifies the transaction mode for the connection. Choose an option from the drop-down list.
  • default - Indicates the transaction information cannot be get from JDBC connection.
  • none - Indicates that transactions are not supported.
  • read uncommitted - Dirty reads, non-repeatable reads and phantom reads can occur. This mode will speed up the transaction of the catalog.
  • read committed - Dirty reads are prevented; non-repeatable reads and phantom reads can occur.
  • repeatable read - Dirty reads and non-repeatable reads are prevented; phantom reads can occur.
  • serializable - Dirty reads, non-repeatable reads and phantom reads are prevented.

Data type: Enumeration
URL Specifies the JDBC URL which establishes the connection to the database, for example jdbc:oracle:thin:@localhost:1521:ORCL.

Data type: String
User Specifies the user name for connecting to the database, which is determined by the database DBA.

Data type: String

Timestamp Format and Date Format

Symbol Meaning Presentation
y year Number
M month Number
d day in month Number
H hour in day (0~23) Number
h hour in am/pm (1~12) Number
m minute in hour Number
s second in minute Number
S millisecond Number

Example (using the US Locale):

"yyyyy.MMMMM.dd hh:mm aaa" ->> 1996.July.10 12:08 PM

Note: The Date and Timestamp format JReport supports follows that of Java. Refer to the Java API Specification java.text package DateFormat interface.

Push Down Group Query

This property is to specify whether to push down group level summary computations in reports to the DBMS at runtime. The group level summary computations can be pushed down to the DBMS when the aggregate function is Count, Sum, Maximum, Minimum, or Square Sum. The aggregate functions Average, PopulationStdDev, PopulationVariance, StdDev and Variance are not pushed down, but instead computed by JReport using the results of the pushed down functions. By pushing down the group level summary computations, the DBMS' computation capability can be made use of, and thus the reports' running efficiency will be improved.

The QueryOptimizer interface

The QueryOptimizer interface is stored in the archive file JREngine.jar in <install_root>\lib. It is contained in the package toolkit.db.queryoptimization in <install_root>\help\api.

This interface contains only one method:

Optimizer optimizeQuery(QueryInfo queryInfo);

One QueryInfo will be automatically passed into the interface, and one Optimizer object should be returned.