Creating and Using Database JDBC Connections

A JDBC connection allows you to use a relational DBMS (or, in general, whatever databases are accessible through a JDBC driver) as a data source. To set a new JDBC connection, click the New button in the Connections/Datasources dialog box (shown earlier in “DataAdapter Wizard”) to open the interface for creation of a new connection (or data source). From the list, select Database JDBC connection to bring up the window shown in “Configuring a JDBC Connection”.

Configuring a JDBC Connection

First, name the connection (using a significant name, such as Mysql – Test). Jaspersoft Studio always uses the specified name to refer to this connection.

In the JDBC Driver field, you specify the name of the JDBC driver to use for the connection to the database. The combo box proposes the names of the most common JDBC drivers (see “JDBC Drivers List”).

JDBC Drivers List

If a driver is displayed in red, the JDBC driver class for that driver is not present in the classpath and you must obtain and install the driver before using it. See Working with Your JDBC Connection.

Thanks to the JDBC URL Wizard, it is possible to automatically construct the JDBC URL to use the connection to the database by inserting the server name and the database name in the correct text fields. Click the Wizard button to create the URL.

Enter a username and password to access the database. By means of a check box option, you can save the password for the connection.

Jaspersoft Studio saves passwords in Eclipse secure storage. In some cases, this can cause problems when trying to test and save data adapters. For more information, refer to our Eclipse Secure Storage in Jaspersoft Studio Community wiki page.

If the password is empty, it is better if you specify that it be saved.

After you have inserted all the data, it is possible to verify the connection by clicking the Test button. If everything is okay, the dialog box shown in “Test Confirmation Dialog” appears.

Test Confirmation Dialog

In general, the test can fail for a number reasons, the most frequent of which 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 when the required JDBC driver is not present in the classpath. For example, suppose you want to create a connection to an Oracle database. Jaspersoft Studio has no driver for this database, but you could be deceived by the presence of the oracle.jdbc.driver.OracleDriver driver in the JDBC drivers list shown in the window for creating new connections. If you were to select this driver, when you test the connection, the studio throws the ClassNotFoundException, as shown in “ClassNotFoundError exception”.

ClassNotFoundError exception

What you have to do is to add the JDBC driver for Oracle, which is a file named ojdbc14.jar (or classes12.zip or classes11.zip for older versions) to the classpath (which is where the JVM searches for classes). As Jaspersoft Studio uses its own class loader, it's enough add the ojdbc14.jar file to the Jaspersoft Studio classpath, which is defined in your Eclipse project; the same can be done for directories containing classes and other resources. To edit the classpath, click Project > Properties > Java Build Path>Libraries, and click Add JARs. If you only need to use the driver for this specific data adapter, you can instead add the driver on the data adapter's Driver Classpath tab.

URL Not Correct

If a wrong URL is specified (for example, due to a typing error), you’ll get an arbitrary exception when you click the Test button. The exact cause of the error can be deduced by the stack trace available in the exception dialog box.

In this case, if possible, it is better to use the JDBC URL Wizard to build the JDBC URL and try again.

Parameters Not Correct for the Connection

A less-problematic error scenario occurs when you try to establish a connection to a database with the wrong parameters (for example invalid credentials (username or password) or nonexistent/inaccessible database). In this case, the same database returns a message is fairly explicit about the reason behind the failure of the connection.

Version: 
Feedback
randomness