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.
Recommended Comments
There are no comments to display.