Relational Database Plugin

The plugin provides capabilities to work with relational databases via JDBC and contains a set of the steps for comparison of data tables.

Java Database Connectivity (JDBC) is an application programming interface for Java, which defines how a client may access a database. Every JDBC requires using specific driver. JDBC drivers are client-side adapters (installed on the client machine, not on the server) that convert requests from Java programs to a protocol that the database management system (DBMS) can understand.

Installation

  1. Add plugin dependency into build.gradle file:

    Example 1. build.gradle
    implementation(group: 'org.vividus', name: 'vividus-plugin-db', version: '0.5.0')

  2. Define your JDBC driver and add appropriate dependency. Let’s take PostgreSQL as an example:

    • Open JDBC page on maven repository;

    • Find driver for your database and open details (PostgreSQL JDBC Driver);

    • Choose and open version (42.4.0);

    • Go to the Gradle tab and copy implementation string;

    • Paste chosen dependency into your build.gradle file:

      Example 2. build.gradle
      implementation(group: 'org.postgresql', name: 'postgresql', version: '42.4.0')
      In case Maven repository does not contain driver you need, please refer to the documentation of your database to define suitable JDBC driver.

Configuration

Database connection

For database interaction, you will need to set related properties. They have db.connection. prefix followed by the database connection name (vividus in the example). Come up with your own database name to use within the tests. Put properties into the environment.properties file:

Example 3. environment.properties
db.connection.vividus.url=jdbc:dbms://databaseUrl:port/databaseName?parameter1=true&parameter2=value (1)
db.connection.vividus.username= (2)
db.connection.vividus.password= (2)
db.connection.vividus.driver-class-name=(3)
1 Database address. Consists of:

  • jdbc: - reference to JDBC;

  • dbms: - Database Management System, e.g. postgresql;

  • //databaseUrl - address to your database;

  • :port - port number, e.g. 5432;

  • /databaseName - name of your database;

  • parameters - any additional driver parameters you need to pass;

    URL structure slightly changes depending on the database management system. You can find templates for different DBMS in setup examples.
2 Credentials for given database connection;
3 Driver class name (Optional), e.g. org.postgresql.Driver. You might need it to specify the driver name for the exact database.

Setup examples

Here are some examples of setting different databases connection for most popular DBMS-s.

MySQL
Example 4. build.gradle
implementation(group: 'com.mysql', name: 'mysql-connector-j', version: '8.0.31')
Example 5. environment.properties
db.connection.vividus.url=jdbc:mysql://mysql.db.server:3306/my_database?useSSL=false&serverTimezone=UTC
db.connection.vividus.username=vividus
db.connection.vividus.password=pa$$w0rd
Example 6. URL template
jdbc:mysql://host:port/database?parameters
Microsoft SQL Server
Example 7. build.gradle
implementation(group: 'com.microsoft.sqlserver', name: 'mssql-jdbc', version: '11.2.1.jre11')
Example 8. environment.properties
db.connection.vividus.url=jdbc:sqlserver://mssql.db.server:1433\mssql_instance;databaseName=my_database
db.connection.vividus.username=vividus
db.connection.vividus.password=pa$$w0rd
Example 9. URL template
jdbc:sqlserver://serverName\instanceName:portNumber;parameter1=value1;parameter2=value2
Azure SQL
Example 10. build.gradle
implementation(group: 'com.microsoft.sqlserver', name: 'mssql-jdbc', version: '11.2.1.jre11')
Example 11. environment.properties
db.connection.vividus.url=jdbc:sqlserver://vividusdb.database.windows.net:1433;database=demo;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;
db.connection.vividus.username=vividus
db.connection.vividus.password=pa$$w0rd
Example 12. URL template
jdbc:sqlserver://serverName:portNumber;parameter1=value1;parameter2=value2
PostgreSQL
Example 13. build.gradle
implementation(group: 'org.postgresql', name: 'postgresql', version: '42.5.0')
Example 14. environment.properties
db.connection.vividus.url=jdbc:postgresql://postgresql.db.server:5432/my_database?ssl=true&loglevel=2
db.connection.vividus.username=vividus
db.connection.vividus.password=pa$$w0rd
Example 15. URL template
jdbc:postgresql://host:port/database?parameters
Oracle Database
Example 16. build.gradle
implementation(group: 'com.oracle.database.jdbc', name: 'ojdbc10', version: '19.16.0.0')
Example 17. environment.properties
db.connection.vividus.url=jdbc:oracle:thin:@//myoracle.db.server:1521/my_servicename
db.connection.vividus.username=vividus
db.connection.vividus.password=pa$$w0rd
Example 18. URL template based on the services approach
jdbc:oracle:thin:user/password@//host:port/service
DB2
Example 19. build.gradle
implementation(group: 'com.ibm.db2.jcc', name: 'db2jcc', version: 'db2jcc4')
Example 20. environment.properties
db.connection.vividus.url=jdbc:db2://localhost:50000/my_db
db.connection.vividus.username=vividus
db.connection.vividus.password=pa$$w0rd
Example 21. URL template
jdbc:db2://<host>:<port>/<database_name>
CSV

It is possible to perform SQL queries against CSV resources. Please see the step-by-step guide for more details.

Example 22. build.gradle
implementation(group: 'net.sourceforge.csvjdbc', name: 'csvjdbc', version: '1.0.40')
Example 23. environment.properties
db.connection.csv-data.url=jdbc:relique:csv:classpath:path/to/csv/data
Example 24. URL template
jdbc:relique:csv:classpath:path/to/resources?parameters
H2

VIVIDUS allows using in-memory databases during test execution. Such databases are created at the beginning of the tests and are dropped after the run is finished. Please see the detailed guide.

Example 25. build.gradle
implementation(group: 'com.h2database', name: 'h2', version: '2.1.214')
Example 26. environment.properties
db.connection.csv-data.url=jdbc:relique:csv:classpath:path/to/csv/data
Example 27. URL template for in-memory named database
jdbc:h2:mem:databaseName
Snowflake
Example 28. build.gradle
implementation(group: 'net.snowflake', name: 'snowflake-jdbc', version: '3.13.23')
Example 29. environment.properties
db.connection.vividus.url=jdbc:snowflake://myinstance.west-europe.azure.snowflakecomputing.com/?role=DEVELOPER&warehouse=WAREHOUSE&db=MY_DB
db.connection.vividus.username=vividus
db.connection.vividus.password=pa$$w0rd
Example 30. URL template
jdbc:snowflake://account_identifier.snowflakecomputing.com/?connection_parameters

Data Comparison

Property name Acceptable values Default Description

db.duplicate-keys-strategy

NOOP

DISTINCT

NOOP

The strategy defining how records with duplicate keys should be handled:

  • NOOP - the records with duplicate keys are compared in the order they are returned by the query or presented in the expected data

  • DISTINCT - only 1 top record from left and right sides are compared, other records with duplicate keys are ignored

db.diff-limit

integer

100

The max number of records in the comparison result output

db.query-timeout

ISO-8601 duration

PT30M

The maximum time to wait for DB query completion

Table Transformers

The table transformers properties marked with bold are mandatory.

FROM_DB

FROM_DB transformer generates table based on the data retirved from the specified database using SQL query.

Parameter Description

dbKey

Database key

sqlQuery

SQL SELECT query to retrieve the data from DB

nullReplacement

The value to put in the generating table instead of NULL-s contained in the DB data set
Example 31. Generate ExampleTable using data from H2 database
{transformer=FROM_DB, dbKey=testh2db, sqlQuery=SELECT * FROM students, nullReplacement= }