Database Automation with JOOQ

This article will look at how we can automate database interaction in End 2 End automation using the JOOQ library in Java.

When we do End 2 End automation of an application, we generally consider 2 parts.

1.  Input part : ( Entry point of an application like UI front-end.) 
2.  Output part : (Storage point of an application like database back-end.)

The whole application looks like this:

In the End 2End automation perspective, users input the data through the UI front end, and business logic gets that data to perform all the business operations. The final output data is stored in the database. In an automation script, we have to automate the input part and output verification parts.

Our end objective would be to validate the output data with expected data so that we have to first fetch the data from the database to validate; here JOOQ library helps us to interact with the database in a simple and easier way. 

There are a lot of tools in the market to help the input UI automation means the UI front-end application but the options are very limited when we have to automate the output database end of the application.

JOOQ library helps us with the automation database part.

Using the JOOQ library, we can easily write the database interaction code that we used in End 2 End application automation.

In the below section, I will demonstrate the database interaction code using JOOQ.

Prerequisites:
1. We need a database with some application data.
For this example, we are using the H2 database, so please download and install the H2 database based on your operating system.

CREATE TABLE Persons (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
);

INSERT INTO PERSONS (PersonID, LastName, FirstName, Address,City) VALUES (1, 'TOM', 'CHERRY', 'NOIDA','DELHI');
INSERT INTO PERSONS (PersonID, LastName, FirstName, Address, City) VALUES (2, 'TOMMY', 'SUNNY', 'NOIDA','DELHI');

H2 database:

3. Create a maven java project and add the below dependencies:

<dependency>
            <groupId>org.yaml</groupId>
            <artifactId>snakeyaml</artifactId>
            <version>1.21</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/com.h2database/h2 -->
        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <version>2.1.214</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.jooq/jooq -->
        <dependency>
            <groupId>org.jooq</groupId>
            <artifactId>jooq</artifactId>
            <version>3.17.3</version>
        </dependency>
        <dependency>
            <groupId>org.junit.jupiter</groupId>
            <artifactId>junit-jupiter-engine</artifactId>
            <version>5.9.0</version>
            <scope>test</scope>
        </dependency>  

JOOQ for Database Automation

To connect the database, we have to first create a file where we can keep all the information related to a database like an URL, username and password, etc. for this, we have created "database.yaml" and placed it under the resources folder.

To read the yaml file we are using the snake yaml library.

package yaml;


import org.yaml.snakeyaml.Yaml;
import org.yaml.snakeyaml.constructor.Constructor;

import java.io.InputStream;

public class YamlFileReader {

    public DBYamlModel getYamlData()  {
        Yaml yaml = new Yaml(new Constructor(DBYamlModel.class));
        InputStream inputStream = this.getClass()
                .getClassLoader()
                .getResourceAsStream("database.yaml");
        DBYamlModel db = yaml.load(inputStream);
        return db;
    }
}

public Result<Record> getQueryResult(String sqlQuery) throws SQLException {
    try (Connection c = getDBConnection()) {
        return DSL.using(c).fetch(sqlQuery);
    }
}

public <T> List<T> getQueryResultWithType(String sqlQuery, Class<T> className) throws SQLException {
   return getQueryResult(sqlQuery)
            .stream().map(e -> e.into(className)).toList();

}

Please see the full code to connect with the H2 database and fetch the data using SQL query:

package database;

import org.jooq.Result;
import org.jooq.Record;
import org.jooq.impl.DSL;
import yaml.DBYamlModel;
import yaml.YamlFileReader;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.List;

public class DatabaseUtil {


    private Connection getDBConnection() throws SQLException {
        YamlFileReader yamlFileReader = new YamlFileReader();
        DBYamlModel yamlData = yamlFileReader.getYamlData();
        return DriverManager.getConnection(
                yamlData.getUrl(),
                yamlData.getUsername(),
                yamlData.getPassword());

    }


    public Result<Record> getQueryResult(String sqlQuery) throws SQLException {
        try (Connection c = getDBConnection()) {
            return DSL.using(c).fetch(sqlQuery);
        }
    }

    public <T> List<T> getQueryResultWithType(String sqlQuery, Class<T> className) throws SQLException {
       return getQueryResult(sqlQuery)
                .stream().map(e -> e.into(className)).toList();

    }

}

This is a java class where we are displaying the final database output result of one query.  

package database;

public class DBQueryTesting {

    public static void main(String[] args) throws Exception {
        String sqlQuery = "Select * from Persons";
        DatabaseUtil databaseUtil = new DatabaseUtil();
        
        databaseUtil
                .getQueryResult(sqlQuery)
                .forEach(System.out::println);
    }
}

There is a way to convert the database result response to the java class.

For example, we have database table PERSONS with 5 columns, so we have created a Person record with sample fields. Now we can map easily the database response to java code, and we can fetch column values using the java field only.

public record Persons(Integer Id,String LastName,String FirstName, String Address, String City) {
}

package database;
import Data.Persons;

public class DBQueryTesting {

    public static void main(String[] args) throws Exception {
        String sqlQuery = "Select * from Persons";
        DatabaseUtil databaseUtil = new DatabaseUtil();

        databaseUtil
                .getQueryResultWithType(sqlQuery, Persons.class)
                .forEach(System.out::println);

    }

}

This is a way we can write database interaction code which we can use in our End 2 End automation where we are fetching the data from the database and performing the data verification. 

import Data.Persons;
import database.DatabaseUtil;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.DisplayName;
import org.junit.jupiter.api.Test;

import java.sql.SQLException;
import java.util.List;

import static org.junit.jupiter.api.Assertions.assertEquals;

public class DBCheckTest {
    DatabaseUtil databaseUtil;

    @BeforeEach
    void setUp(){
        databaseUtil = new DatabaseUtil();
    }

    @DisplayName("Test the Persons table should contains one record with Firstname as CHERRY")
    @Test
    void testPersonsFirstName() throws SQLException {
        String sqlQuery = "Select * from Persons";
        List<Persons> persons=databaseUtil.getQueryResultWithType(sqlQuery, Persons.class).stream()
                .filter(e ->e.FirstName().equals("CHERRY")).toList();

        assertEquals(1, persons.size());
        assertEquals("TOM", persons.get(0).LastName());
    }

    @DisplayName("Test the Persons table should contains two record with city as DELHI")
    @Test
    void testPersonsCityCount() throws SQLException {
        String sqlQuery = "Select * from Persons";
        List<Persons> persons=databaseUtil.getQueryResultWithType(sqlQuery, Persons.class).stream()
                .filter(e ->e.City().equals("DELHI")).toList();

        assertEquals(2, persons.size());
    }

}

Please find the GitHub repo.

This is a just small example. There are so many features that JOOQ supports.

JOOQ library offers so many methods, classes, and operation in a functional style which really help us to write code in a simple, easy and concise manner so that everyone can understand the business context easily.