When you create a report with a JDBC connection, you specify an SQL query to extract records from the database. You can also use this connection for a subreport or a personalized lookup function for decoding specific data. For this reason, JasperReports provides a java.sql.Connection parameter called REPORT_CONNECTION. You can use this parameter in any expression you like, with this parameters syntax:

$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. You don't need to register all the selected fields—only those effectively used in the report. For each field, specify name and type. Conversion of SQL and JAVA types shows SQL types and the Java objects they map to.

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 doesn't include special types like BLOB, CLOB, ARRAY, STRUCT, and REF, because these types cannot be managed automatically by JasperReports. However, you can 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 an 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. Set sort and filter options in the Report query dialog by clicking the Dataset and Query button .

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.

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