SoapUI JDBC

SoapUI introduces a new concept for retrieving the data or records from a database, is known as JDBC. It provides the results in XML format that can be asserted or processed in the standard way like XPath, XQuery, etc. In the SoapUI JDBC, we will learn how to create the JDBC connection, JDBC properties and the JDBC assertions.

For creating the JDBC connection in a SoapUI, we have to follows the given below steps.

Step 1: Select the project and right-click on the Test Steps of the selected test case, Add Step -> JDBC Request, as shown below.

Step 2: After selecting the JDBC Request, it shows a pop-up dialog box to specify the name for the new step, as shown below.

Step 3: As the OK button is clicked, it displays the JDBC Request window to create a JDBC connection, as shown below.

For creating the JDBC connection, we have to provide the necessary and valid information of the JDBC configuration, such as driver name and the Connection String. The valid information is used to identify the database's name and create a connection to use the defined database.

Step 4: If we want to connect with the MySQL database, we need to define the driver as com.mysql.jdbc.Driver. Similarly, we can define the driver for another database, mentioned in a document section of the SoapUI.

Step 5: To set the format of the connection string in JDBC, use the statement given below.

Where the Property is the username and value is the password related to their respective parameter values to connect with the DB.

For example,

Here we leave the password field blank because we have not saved any password in phpMyAdmin. If you set a password in phpMyAdmin, specify it.

Note: Before creating the JDBC connection for the SoapUI tool, we must define or create the Database name in the phpMyAdmin.

Step 6: After specifying the configuration details of the JDBC, click on the Run icon (triangle in green color) of the Test Connection to validate the connection. If the JDBC connection is successfully connected, it shows a Success dialog box; otherwise, it shows the failed connection details.

Step 7: As we click on the Run button, it pop-up a dialog box to successfully tested the JDBC connection, as shown below.

Execution of JDBC Query

After creating the database for JDBC, we need to define the JDBC property. The JDBC property has its property to add a parameter name to the property list that can be used as a variable to find defined items from the database using SQL queries.

For example, we need to fetch the name of a particular country and its attributes, set the SQL Query as Select * from Table_Name where Table_Name.name = :name. As the query executed in a SoapUI JDBC, it returns a particular country's name with its attributes from the JDBC database.

We have created the database table named country and execute the following queries to insert data into the table. The data we retrieve later using the JDBC SQL query.

The SQL Query is used in JDBC to search the particular item or collection of items from the database table. Let's create JDBC request to accept the countries records from the database table.

Step 1: Write the SQL Query as select * from country to select all Country's name and its additional attributes from the defined table.

Step 2: After that, click on the run button on the top left corner to execute JDBC's SQL query, as shown below.

After the execution of SQL Query, it produces the above output on the screen. The above output contains the Country's name, region, capital and call codes fetched from the database's country table.

Here, the results of the JDBC SQL Query:

JDBC SQL Query to Select or Fetch Data

Follow the steps given below to select and fetch data by using the JDBC SQL Query.

Step 1: For adding the JDBC property, click the (+) button and then pass the parameter' name and value. And then mention the SQL Query to select the country name, as shown in the below image.

Here, we passed the property name as name and the property value as Australia. After that, write the SQL Query as 'select * form country where country.name = :name'

Step 2: Click on the Run button to execute the JDBC' SQL Query, as shown below.

Here, the results of the JDBC SQL Query:

JDBC Assertion

The JDBC assertion is used to validate the data received using a JDBC request from a database table.

For using the JDBC Assertion in a SoapUI, we have to follow the given below steps.

Step 1: Click on the (+) button to add an assertion of the JDBC Request TestStep, as the below image.

Step 2: After clicking in the Add Assertion button, it shows the Add Assertion window to select the assertion, as shown.

Step 3: After clicking the Add button, it executes the JDBC SQL query correctly in SoapUI, as shown in the given image.

If the JDBC state assertion is true, the assertion is valid; Otherwise, it shows an error message. Similarly, we can validate a JDBC request TestStep using a different type of assertion in a SoapUI.