Datasource with Hsqldb

Introduction Hsqldb

This document contains information about the Hsqldb database how to install and connect with AIV.This document has step-by-step information on how to install, connect Hsqldb Database with AIV and will show one use case example.

HyperSQL Database (HSQLDB) is a modern relational database manager that conforms closely to the SQL:2011 standard and JDBC 4 specifications. It supports all core features and RDBMS. HSQLDB is used for development, testing, and deployment of database applications.HSQLDB is also suitable for business intelligence, ETL, and other applications that process large data sets.

Objective

Objective of this document is to connect Hsqldb Database with AIV.

Prerequisites

1. Hsqldb Jar

2. Java Eclipse

3. Hsqldb Database

Skill Level Required

Basic knowledge of Database.

1. Download Hsqldb JDBC driver:

To get the JDBC driver, Go the vendor’s end and download the (Hsqldb-connector-java.jar) Driver file from here as shown in figure below:

Go to the folder where you have downloaded this file and extract the zip file.

When you go inside the folder you will be able to see ** lib **folder.

copy the hsqldb & hsqldb-jdk8 jar file and add it into the AIV.

Restart the Tomcat server after adding a jar in the lib folder.

2. Download the java Eclipse from here.

Follow steps given in Video link in order to Install the Eclipse Click here to open link

Open the Eclipse and click on open Perspective from the top right corner,as shown in figure below:

Select the Database Development and click on Open button.

Now, right click on the Database Connection and click on New, as shown in figure below:

It will display the Connection Profile in that select the HSQLDB and Give name.

Click on New Driver Definition and choose HSQLDB JDBC driver, as shown in figure below:

Now go to the Jar List and select the Driver files click on edit this jar file is located at HSQLDb >> Lib Folder.

Click on Open and Ok.

It will redirect you on Specify Driver and Connection Details Page, as shown in figure below:

Insert the following data in the connection Dialog box:

Click on Test Connection Button and it will display message Ping Succeeded, as shown in figure below:

Click on Finish and Close the Eclipse.

Connecting hsqldb Datasource with AIV

Go to the Master data section and click on Data Source, as shown in the figure below:

Click on Create button from the bottom horizontal menu bar from Datasource section, as shown in the figure below:

The Create Datasource window will open, as shown in the figure below:

Search for Hsqldb data source from Top-right search bar in Datasource section, as shown in the figure below:

Click on Hsqldb to create a data source using SQLite.

As you click on Hsqldb the create dialog box will pop-up on screen, as shown in the figure below:

Wait Time: Wait Time Analysis for Database Optimization. Wait time analysis helps you understand how much time each SQL statement is spending across all its executions in a given period of time.

Is JNDI: Java Naming and Directory Interface is the name of the interface in the Java programming language. It is an API( Application Program Interface) that works with servers and can fetch files from a database using naming conventions.

Class: Database class Contains methods for creating and manipulating data.

Connection URL: A database connection URL is a string that your DBMS JDBC driver uses to connect to a database.It can contain information such as where to search for the database, the name of the database to connect to, and configuration properties.

Username: Username is the name of the user that is connected to the database.

Password: You can set a password on an Access database to prevent unauthorized access to the database file.

Extra Configuration: Using Extra Configuration you can add extra fields as per ypur requirements.

Insert the following data in the Datasource connection Dialog box:

Click on Test Connection Button to Check Database Connection, as shown in figure below:

After Connection Successful, Click on ok and save button to close the Dialog box.

To create a dataset Select the Hsqldb DataSource and click on Create Dataset, as shown in the figure below:

When you click on create Dataset the following screen will be display.

Enter the Following details in Dataset.

Name: Hsqldb (Add name as per your requirements)

Source: Hsqldb (select the data source which you have created)

DataSource: JDBC