In the POM based Automation Framework that we developed with Selenium, we have used properties file to store the test data. Now lets implement HSQLDB (a lightweight database) to store and fetch all the test data in the framework.
HSQLDB (Hyper SQL Database) is a relational database management system written in Java. It has a JDBC Driver and supports a large subset of SQL-92, SQL: 2008, and SQL: 2011 standards. It offers a fast, small database engine which offers both in-memory and disk-based tables. Both embedded and server modes are available.
Download and install HSQLDB:
First of all we need to download hsqldb. It will be a zip file, extract this file.
Place the extracted folder under your project location.
Create Database and run Server:
The ways of creating a database and running the database server is different in different OS. Here we will be discussing how it can be done in mac.
Open the command prompt and move to the lib folder under your hsqldb.
Now run this command:
java -cp hsqldb.jar org.hsqldb.server.Server –database.0 file:mydb –dbname.0 mydb
This command will start the database server and create a database named mydb (We can name it whatever we want)
Access the Database UI:
To see the actual database in the UI, open the command prompt and go to lib folder under your hsqldb.
Now run this command:
java -cp hsqldb.jar org.hsqldb.util.DatabaseManagerSwing
We will get:
Select the Database type HSQL Database Engine Server
Now we need to enter the url which will be combination of ip address of your system followed by name of database.
In the above screenshot, localhost will be replaced by ip address/db name.
Once this is entered, we will be getting:
Now we need to create table. It can either be done directly from the UI where we can write the simple SQL query to create table and execute it or it can be done through the framework.
Now lets implement Database under the Framework:
We will be creating the Database file under our src.utils package. This file will contain methods for all the operations that we will be performing on the database.
Establish connection with DB:
First Of All We have to get the connection with the database.
We will be getting the IP address of the system and then use the same to connect to the database.
Connection should be established before any action on the database.
We will be using this method to create the table. With this query we are creating a table with two columns, key and value with type varchar where column key is the Primary Key of the table.
You can change the number of columns and type of column as per your requirement.
See the table in HSQLDB UI.
Insert Data Into the Table:
This method will add a new row into the table containing a key and a value.
You can execute this method and see the result into your table. It will look like this.
If we want to update any value while our execution, this method should be called there.
Execute this method and see the updated value in the database.
For deleting any row we need to execute this method.
As these methods are implemented on top of simple SQL queries itself, so it can be altered as per requirement.
Table Before deleting any Value
Table After deleting the Value
Get Data From The Table:
While executing your test case, you will be needing the data from the table to use in your script.
You can get the data using this method.
Now the question is how we will be accessing the methods of this class.
First of all we need to import hsqldb jar into our project. This jar can be downloaded from maven repository.
Now we will be creating object of DataBase class under DriverTestCase class so that it can directly be used in all our scripts.
Use the database methods in script to get the data:
With the example shown below, we are going to access the getData method of the Database class and use it in our script. We just need to pass the key and can get the value.
This is our test script file and we have fetch the value of name and email from the database, store it in a variable and will be using it in our script.
In the similar way, rest of the methods can also be accessed.