Working with Database JDBC Connections

A JDBC connection lets you use a database accessed through a JDBC driver (such as a relational DBMS). When you use a JDBC connection in a report, you must specify a query.

Creating a Database JDBC Connection

To create a new JDBC connection:

Create the connection globally or locally:

•

To create the connection globally, right-click Data Adapters in the Repository Explorer and choose Create Data Adapter.

•

To create the connection local to a project, click

, enter a name and location for the data adapter in the DataAdapter File dialog box, and then click Next.

The Data Adapter wizard appears (see Data Adapter Wizard).

From the list, select Database JDBC connection to open the Data Adapter dialog.

Configuring a JDBC Connection

Name the connection (use a significant name like Mysql – Test). This is the name that will appear on the list of available connections when you create a report.

In the JDBC Driver field, specify the JDBC driver to use for your database connection. The drop-down displays the names of the most common JDBC drivers.

JDBC Drivers List

If a driver is displayed in red, the JDBC driver class for that driver is not present in the class path and you must obtain and install the driver before using it. See Using a Database JDBC Connection.

JasperReports Server includes the TIBCO JDBC drivers for the following commercial databases: Oracle, MS SQLServer and DB2. In some cases, these drivers provide functionality not provided by the vendors' driver. However, there may be some differences in queries between the two drivers. You can use the TIBCO drivers, or you can choose to install and use the driver supplied by the database vendor.

If you upload your reports to JasperReports Server, make sure to use the same driver in both JasperReports Server and Jaspersoft Studio.

Enter the connection URL.

Enter a username and password to access the database. If the password is empty, it is better if you specify that it be saved. You can choose to save the password in one of two ways:

•

Clear text – This is not secure, but can sometimes be convenient when working in a developer or staging environment.

•

Eclipse secure storage – This is the correct option for security, but can be difficult to work with when testing and saving adapters. In addition, it can make it difficult to share adapters with other developers or deploy data adapters to JasperReports Server.

After you've inserted all the data, click the Test button to verify the connection. If everything's okay, you'll see a message that the test was successful.

Click OK to exit the message.

Click Finish to create the connection.

Troubleshooting a Database JDBC Connection

When the tests fail, the most common exceptions are:

•

A ClassNotFoundError was thrown.

•

The URL is not correct.

•

Parameters are not correct for the connection (database is not found, the username or password is wrong, etc.).

ClassNotFoundError

The ClassNotFoundError exception occurs whenever a data adapter fails to load a class it requires. In the context of JDBC connections, the most likely cause is that the required JDBC driver is not present in the classpath. In general, a data adapter has two classpaths it uses to find libraries. First the adapter looks at any paths that were specified inside the data adapter when it was created. If it cannot load the libraries or classes it needs using its internal paths, the data adapter uses the Jaspersoft Studio classpath to look for them.

The Jaspersoft Studio classpath is defined in your Eclipse project. As Jaspersoft Studio uses its own class loader, it's enough to add resources such as jar files and directories containing classes to the Jaspersoft Studio classpath.

For example, suppose you want to create a connection to an Oracle database. Jaspersoft Studio does not ship the vendor's driver for this database. If you choose the oracle.jdbc.driver.OracleDriver driver, when you test the connection, you'll see the ClassNotFoundException, as shown in “ClassNotFoundError exception”. You need to add the JDBC driver for Oracle, ojdbc14.jar, to the classpath.

ClassNotFoundError exception

To add a resource to the Jaspersoft Studio classpath:

If you add a resource to the Jaspersoft Studio classpath, it will be available to all data adapters. In addition to JARs, you can add variables, libraries, class folders, and external class folders. To add a jar to the Jaspersoft Studio classpath:

Click Project > Properties > Java Build Path>Libraries, and click Add JARs or Add External JARs.

Browse to locate the jar you want to add.

Select the file you want to add to the classpath.

Click OK.

To add a JAR file to a data adapter's classpath:

If you need to use the driver only for this data adapter, you can instead add the driver on the data adapter's Driver Classpath tab.

If the adapter is not already open, double-click its icon in the Repository Explorer or Project Explorer to open it.

Click on the Driver Classpath tab.

Click Add and browse to locate the jar you want to add. If you want to add a different file type, use the menu at the bottom right.

Click on the jar and Open.

The location of the file you chose is added to the driver classpath.

URL Not Correct

If a wrong URL is specified, you’ll get an exception when you click the Test button. You can find the exact cause of the error using the stack trace provided in the exception.

Parameters Not Correct for the Connection

If you try to establish a connection to a database with the wrong parameters (for example, invalid credentials or inaccessible database), the database returns a message that is fairly explicit about the reason behind the failure of the connection.

Using a Database JDBC Connection

When you create a report with a JDBC connection, you specify a query to extract records from the database. The query language you use depends on the connection type; the most common query type is an SQL query.

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 actually 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 in a report can be ordered and filtered. Set sort and filter options in the Report query dialog by clicking the Dataset and Query button .

Filter Expression Tab and Expression Editor

Clicking the Data Preview tab shows a subset of 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 not, close the query dialog, register the fields, and resume the sorting.

Using JDBC Connections for Subreports

You can also use a JDBC 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.

Sign In