Karate Clients - JDBC | InditexTech Karate Tools

Java Client to interact with DataBases using JDBC from karate.

It provides features to execute SQL queries, SQL inserts, updates, deletes, … and SQL scripts from file.

How to set it up:
- Define (if applicable) the JDBC client dependencies in the project POM
- Define the JDBC client configuration in the file src/test/resources/config/db/<db-driver>-<env>-config.yml
How to use it in the karate files:
- JDBC Client Features and Usage

The included JDBC Drivers are:

Maria DB
PostgreSQL

POM Configuration

POM Karate Tools

If the project has been generated using the Karate Tools Archetype the pom will already contain the corresponding configuration.

Add the karatetools dependency in the karate pom:

<properties>
  ...
  <!-- Karate Tools -->
  <karatetools.version>X.X.X</karatetools.version>
</properties>

<dependencies>
  ...
  <!-- Karate Tools -->
  <dependency>
    <groupId>dev.inditex.karate</groupId>
    <artifactId>karatetools-starter</artifactId>
    <version>${karatetools.version}</version>
    <scope>test</scope>
  </dependency>
</dependencies>

POM Driver

MariaDB POM

karatetools-starter already includes the Maria DB JDBC dependencies.

If you need to change the dependency version, you can include it in the pom as follows:

  <properties>
    ...
    <!-- Karate Clients -->
    <!-- Karate Clients - JDBC - MariaDB -->
    <mariadb-java-client.version>X.X.X</mariadb-java-client.version>
  </properties>

  <dependencies>
    ...
    <!-- Karate Clients -->
    <!-- Karate Clients - JDBC - MariaDB -->
    <dependency>
      <groupId>org.mariadb.jdbc</groupId>
      <artifactId>mariadb-java-client</artifactId>
      <version>${mariadb-java-client.version}</version>
    </dependency>
  </dependencies>

PostgreSQL POM

karatetools-starter already includes the PostgreSQL JDBC dependencies.

If you need to change the dependency version, you can include it in the pom as follows:

  <properties>
    ...
    <!-- Karate Clients -->
    <!-- Karate Clients - JDBC - PostgreSQL -->
    <postgresql.version>X.X.X</postgresql.version>
  </properties>

  <dependencies>
    ...
    <!-- Karate Clients -->
    <!-- Karate Clients - JDBC - PostgreSQL -->
    <dependency>
      <groupId>org.postgresql</groupId>
      <artifactId>postgresql</artifactId>
      <version>${postgresql.version}</version>
    </dependency>
  </dependencies>

Additional Drivers POM

If you need to add a different JDBC Driver, you can include it in the pom as follows:

SQL Server

  <properties>
    ...
    <!-- Karate Clients -->
    <!-- Karate Clients - JDBC - SQL Server -->
    <sqlserver.version>11.2.3.jre17</sqlserver.version>
  </properties>

  <dependencies>
    ...
    <!-- Karate Clients -->
    <!-- Karate Clients - JDBC - SQL Server -->
    <dependency>
      <groupId>com.microsoft.sqlserver</groupId>
      <artifactId>mssql-jdbc</artifactId>
      <version>${mssql-jdbc.version}</version>
    </dependency>
  </dependencies>

Client Configuration

Configuration parameters for the JDBC Clients. These values can be overwritten by the corresponding (DB specific) system properties. System properties can be injected by CI/CD.

If the project has been generated using the Karate Tools Archetype the archetype would have prompted for the creation of the configuration files.

This client can be configured for multi-environment execution with a config file per environment:

\---src
    \---test
        \---resources
            \---config
                \---db
                    <db>-config-local.yml
                    ...
                    <db>-config-pre.yml

For example:

\---src
    \---test
        \---resources
            \---config
                \---db
                    mariadb-config-local.yml
                    ...
                    mariadb-config-pre.yml
                    ...
                    postgresql-config-local.yml
                    ...
                    postgresql-config-pre.yml

JDBC Client Configuration Properties

⚙️ jdbc-url - JDBC URL to connect to the data base. JDBC URL is database specific.

jdbc:[protocol]//[host]:[port]/[database][?properties]

⚙️ driver-class-name - JDBC Driver Class Name.

⚙️ username - user name to connect

⚙️ password - password to connect

⚙️ health-query - SQL query to use to check if the DB is available.

JDBC Client Configuration Properties - MariaDB

Example

# docker-compose environment variables:  NA
# docker-compose folder structure:       mariadb/<DB_NAME>/scripts
jdbc-url: jdbc:mariadb://localhost:3306/KARATE
driver-class-name: org.mariadb.jdbc.Driver
username: karate
password: karate-pwd
health-query: SELECT 1 FROM DUAL

JDBC Client Configuration Properties - PostgreSQL

Example

# docker-compose environment variables:  <POSTGRES_DB> <POSTGRES_USER> <POSTGRES_PASSWORD>
# docker-compose folder structure:       NA
jdbc-url: jdbc:postgresql://localhost:5432/KARATE
driver-class-name: org.postgresql.Driver
username: karate
password: karate-pwd
health-query: SELECT 1

Client Features and Usage

Instantiate JDBCClient

New instance of the JDBCClient providing the configuration as a map loaded from a yaml file.

Java Signature

public JDBCClient(final Map<Object, Object> configMap)

Gherkin Usage

# public JDBCClient(final Map<Object, Object> configMap)
# Instantiate JDBCClient
Given def config = read('classpath:config/db/postgresql-config-' + karate.env + '.yml')
Given def JDBCClient = Java.type('dev.inditex.karate.db.JDBCClient')
Given def jdbcClient = new JDBCClient(config)

Check if JDBC is available

Checks if the JDBC connection can be established using the configured health query

Returns true is connection is available, false otherwise

Java Signature

public Boolean available()

Gherkin Usage

# public Boolean available()
When def available = jdbcClient.available()
Then if (!available) karate.fail('JDBC Client not available')

Execute JDBC Update Script from file

Execute a set of SQL update statements (INSERT, UPDATE, DELETE, TRUNCATE, …) defined in a file, each executable SQL line must end with the separator ";". The separator can be ignored when executing each SQL depending on the DB, for example for DB2 ";" must be ignored.

Returns the aggregated number of row count for the executed SQL lines.

Java Signature

public int executeUpdateScript(final String script, final boolean ignoreSeparator)

Gherkin Usage

# public int executeUpdateScript(final String script, final boolean ignoreSeparator)
# Truncate Table and Insert 2 records
Given def scriptSQL = karate.readAsString('classpath:scenarios/db/JDBCClient-PostgreSQL.sql')
When def insertedScript = jdbcClient.executeUpdateScript(scriptSQL, false)
Then karate.log('jdbcClient.executeUpdateScript(',scriptSQL,')=',insertedScript)
Then assert insertedScript == 2

Execute JDBC Update SQL

Execute a single SQL update statement (INSERT, UPDATE, DELETE, TRUNCATE, …).

Returns the row count for the executed SQL statement or 0 for SQL statements that return nothing (such as TRUNCATE)

Java Signature

public int executeUpdate(final String sql)

Gherkin Usage

# public int executeUpdate(final String sql)
# Insert a record
Given def insertSQL = "INSERT INTO DATA (NAME, VALUE) VALUES ('karate-03', 3)"
When def inserted = jdbcClient.executeUpdate(insertSQL)
Then karate.log('jdbcClient.executeUpdate(',insertSQL,')=',inserted)
Then assert inserted == 1

Execute JDBC Query SQL

Executes a single SQL query statement,

Returns a JSON Array representing the obtained ResultSet, where each row is a map << column name, ResultSet value >>

For example:

SELECT ID, NAME, VALUE FROM DATA ORDER BY ID

ID|NAME     |VALUE|
--+---------+-----+
 1|karate-01|    1|
 2|karate-02|    2|
 3|karate-03|    3|

will return

[
  { "ID": 1, "NAME": "karate-01", "VALUE": 1 },
  { "ID": 2, "NAME": "karate-02", "VALUE": 2 },
  { "ID": 3, "NAME": "karate-03", "VALUE": 3 }
]

Java Signature

public List<Map<String, Object>> executeQuery(final String sql)

Gherkin Usage

# public List<Map<String, Object>> executeQuery(final String sql)
# Select inserted records
Given def selectSQL = "SELECT ID, NAME, VALUE FROM DATA ORDER BY VALUE ASC";
When def result = jdbcClient.executeQuery(selectSQL)
Then karate.log('jdbcClient.executeQuery(',selectSQL,')#=',result.length)
Then karate.log('jdbcClient.executeQuery(',selectSQL,')=',result)
Then assert result.length == 3
Then match result[0].id == '#notnull'
Then match result[0].name == 'karate-01'
Then match result[0].value == 1
Then match result[1].id == '#notnull'
Then match result[1].name == 'karate-02'
Then match result[1].value == 2
Then match result[2].id == '#notnull'
Then match result[2].name == 'karate-03'
Then match result[2].value == 3