JDBC Datasource

A Java Database Connectivity (JDBC) datasource is a datasource that supports reading of data using JDBC and provides SQL query builder.

To use the DataSource Wizard, right-click on the folder where the JDBC datasource is to be created. The list of available actions will appear. Select the “Add” option, then the “Datasource” from the sub-options that appear.

The DataSource Wizard will appear. For JDBC datasource, there are six steps:

  • Select datasource type
  • Define datasource details
  • Define datasource
  • Define options
  • Define schema
  • Define security option

Select Datasource Type

In the first DataSource Wizard page, select “JDBC” from the list of available options, then click on the “Next” button.

Define Datasource Details

In this second page, the details of the JDBC datasource need to be defined.

Field Description Mandatory
Name Name of JDBC datasource. Yes
Description Brief description of JDBC datasource. No
Connection Poll Defines the connection to the JDBC source. Yes

The mandatory fields must be filled, else an error message will appear in the page with a red dot and the “Next” button will not be enabled.

Fill in the fields as required and click on the “Next” button.

Define Datasource

In the next page, the schema of the JDBC datasource need to be defined.

There are two ways to define the JDBC datasource. That is, either enter the desired DQL in the textbox or click on the “Query Builder…” button.

When the “Query Builder…” button is clicked, the “Enter Query Builder” dialog box will appear. Select as desired and click on the “Ok” button once completed.

Build the desired query and click on the “Ok” button to save the settings.

The mandatory field (i.e., “SQL”) must be filled, else an error message will appear in the page with a red dot and the “Next” button will not be enabled.

Click on the “Next” button to continue.

Define Options

In the next page, the options of the JDBC datasource need to be defined.

Field Description Mandatory
Max Rows Maximum number of rows allowed. No
Fetch Size Defines the size of the records to fetch. No
Timeout(s) Defines the timeout in seconds. No
Auto Commit If selected, any changes made to the database is immediately made permanent.
Selected by default.		 No
Read Only If selected, the data is read only.
Selected by default. 		No

If the first three fields are left empty or “0” are used, the default values from the driver are used. The “auto Commit” and “Read Only” fields are selected by default.

Enter the desired values in the first three fields and select the desired settings. Click on the “Next” button to continue.

Define Schema

In the next page, the schema of the JDBC datasource need to be defined.

There are two ways to define the schema:

  • Click on the “Infer Schema” button
  • Define the fields for the schema one by one using the buttons on the left

To add a field, click on the “Add” button at the left of the page. The “Add Column” dialog box will appear.

Key in the name of the field in the “Name” field and select the data type from the drop-down list in the “Data Type” field. Click on the “Ok” button to add the column.

Repeat the same process until all desired fields as added.

The order of the fields are according to the sequence they are added. To re-order the fields, select the desired field, then click on the “Move Up” or “Move Down” buttons to move the field to the desired position.

You can also edit the field by selecting the desired field, then clicking on the “Edit” button. The “Edit Column” dialog box will appear.

Edit the information as required and click on the “Ok” button to save the change.

If a field is added by mistake, you can remove the field by selecting it, then click on the “Delete” button.

After all required fields are added, click on the “Next” button to continue. Alternatively, if you wish to use the default security options, click on the “Finish” button to complete the process.

Security Option

The last page in the wizard for the JDBC datasource allows you to set the security options for the datasource.

There are three options.

Type Description
Read Only When selected and saved, the next time a user opens this datasource, the user will not be able to edit any details of the datasource (such as name, description and column names).
Hide Details When selected and saved, the next time when this datasource is opened, the user will only be able to see the name and description of the datasource.
Encrypted This is to be used with either Read Only or Hide Details options or both.
Checking the checkbox will prompt the user to enter a password, then re-enter to confirm the password (both password must be the same). After this is done, if another user would like to edit any selections, he will need to enter the password.

By default, none of the options are selected. Select the options are desired. More than one option can be selected. If “Encrypted” is selected, provide a password in the pop-up dialog box. Click on the “Finish” button to complete the process.

The new datasource will appear in the repository on the left.

You can change the security option of the datasource through the DataSource Wizard by clicking on the “Properties” icon at the Top Row in the Workspace after the datasource is selected. The third page in the DataSource Wizard allows you to change the security options.

Using SQL Field

You can add a prepared SQL statement in the JDBC datasource to allow dynamic filter of the data. In the datasource properties, in the “SQL” field or “Query Builder”, key in the following:

{
  "args": [{"value": "${Company}", "type: "String"}],
  "sql": ["Select * from FruitSales WHERE Company =?"]
}

Click on the “Finish” button to save the edits.

To test the effect, click on the “Load Data” icon on the toolbar. The “Dynamic Parameters” dialog box will appear.

Select the desired option and click on the “Finish” button to load the data.

Next: LDAP Datasource