Working with Your JDBC Connection

When the report is created by using a JDBC connection, you specify a SQL query to extract the records to print from the database. This connection can also be used by a subreport or, for example, by a personalized lookup function for the decoding of particular data. For this reason, JasperReports puts at your disposal a special parameter named REPORT_CONNECTION of the java.sql.Connection type; it can be used in whatever expression you like, with a parameters syntax as follows:

$P{REPORT_CONNECTION}

This parameter contains the java.sql.Connection class passed to JasperReports from the calling program.

The use of JDBC or SQL connections is the simplest and easiest way to fill a report.

Fields Registration

In order to use SQL query fields in a report, you need to register them. It is not necessary to register all the selected fields—only those effectively used in the report are enough. For each field, you must specify its name and type. Conversion of SQL and JAVA types shows the mapping of the SQL types to the corresponding Java types.

Conversion of SQL and JAVA types

SQL Type

Java Object

SQL Type

Java Object

CHAR

String

REAL

Float

VARCHAR

String

FLOAT

Double

LONGVARCHAR

String

DOUBLE

Double

NUMERIC

java.math.BigDecimal

BINARY

byte[]

DECIMAL

java.math.BigDecimal

VARBINARY

byte[]

BIT

Boolean

LONGVARBINARY

byte[]

TINYINT

Integer

DATE

java.sql.Date

SMALLINT

Integer

TIME

java.sql.Time

INTEGER

Integer

TIMESTAMP

java.sql.Timestamp

BIGINT

Long

 

 

The table does not include the BLOB and CLOB types and other special types, such as ARRAY, STRUCT, and REF, because these types cannot be managed automatically by JasperReports. However, it is possible to use them by declaring them generically as Object and managing them by writing supporting static methods. The BINARY, VARBINARY, and LONGBINARY types should be dealt with in a similar way. With many databases, BLOB and CLOB can be declared as java.io.InputStream.

Whether a SQL type is converted to a Java object depends on the JDBC driver used.

For the automatic registration of SQL query fields, Jaspersoft Studio relies on the type proposed for each field by the driver itself.

Filtering Records

The records retrieved from a data source (or from the execution of a query through a connection) can be ordered and filtered. Sort and filter options may be set from the Report query dialog box by clicking the Dataset and Query button .

Filter Expression Tab and Expression Editor

Clicking the Data Preview tab shows your filtered data.The filter expression must return a Boolean object: true if a particular record can be kept, false otherwise.

Data Preview

If no fields can be selected with the Add field button, check to see if the report contains fields. If it does not, close the query dialog and register the fields and resume the sorting.

Version: 
Feedback