Connect to MySql with SoapUI FREE

Abstract:

In this tutorial we will create a SoapUI project in which we will establish a connection with MySql database and will run a simple select statement test case with assertion.

Good to know before we start:

Pre-requisites:

Content:

Getting appropriate drivers

Before we can even think of connecting to any database we have to find appropriate driver which will enable us todo so. We can start looking for some hints at official SoapUI web pages at following link https://www.soapui.org/jdbc/reference/jdbc-drivers.html. Here we learn exactly what driver we need for our attemt to get in MySql. As per the suggestion provided we will look for com.mysql.jdbc.Driver driver. Web page is also suggesting what connection string shall be used but we will get to this topic bit later.
Drivers
Using google search we will easily find a link to download jdbc driver directly from MySql pages at following link https://dev.mysql.com/downloads/connector/j/ (it was mysql-connector-java-5.1.39.tar.gz at the day of writing this tutorial).

Before we start creating our project in SoapUI we need to load previously downloaded MySql drivers in there. To do so we put driver .jar file into bin/ext directory of our SoapUI instalation directory as follows (in our case it is a file with name mysql-connector-java-5.1.39-bin.jar):
Load drivers
Good, now Start or Restart your SoapUI and check if the library was loaded properly in SoapUI log tab:
Load drivers in soapui
If we see above we are ready to create our project and use our MySql connector library.

Test database

Assuming you are about to test some existong application which already has a database with data in place you can skip this step. However for our tutorial we need some data of course. It is not an aim of this tutorial to show how to create DB, table and user so we will skip instruction and show right away how are DB under test looks like. The user we have created and will use in this tutorial is tester and his password is test. This user has an access to tipsandtricks database. These information are of paramount importance for your connection string we will be creating later on. The table we are going to use for our test is called Heroes and simply contains the list of undeniable heroes of our tv screens:
database under test

Create a project in SoapUI

When we have our Database for testing ready we can proceed with Porject creation in SoapUI. We start with Generic Project creation by right clicking on our Workspace and choosing a New Generic Project option as shown on following picture (let's name our project connect-to-mysql-with-soapui):
new generic project
Afterwards we create a new Test Suite by right clicking on our new generic project and chossing and option New TestSuite (let's use default name):
new test suite
Now let's create new Test Case and name it Heroes. To do so we can right click inside our TestSuite 1 and choose New TestCase option.
new test case
Put the right name in the New TestCase pop-up window:
test case name
and we are done with project creation.

Connect to MySql

Very last step to do is to create a JDBC Request test case, setup connection string and do some simple SELECT statement. Let's start with adding new Test Step by right clicking in our new Test case and choosing JDBC Request option. We can retain a default name of the test case, we anyway have only one.
jdbc test step
Now we are facing critical part of this tutorial. We have to define proper Driver and Connection String. From the very first step (Getting appropriate drivers) we know that driver to be used is called MySql/com.mysql.jdbc.Driver. This is bit missleading. In fact we only need com.mysql.jdbc.Driver. The other property we have to define is Connection String. This we know should be in following format jdbc:mysql://:/?user=&password= to be more precise its should be something like this jdbc:mysql://hostname:port/database?user=&password=. From the DB preparation step we know all these things. Assuming we are running MySql server on localhost our values to be used in JDBC Request test step are as follows:

Set them up as follows:
jdbc connection test
and press TestConnection button. If you see The Connection Successfully Tested pop-up message we can tell that we are good to continue with some SELECT statement:
jdbc connection test

Run simple SELECT statement test case

Once we have succeded with connection to MySql database we can proceed with some simple SELECT statement and assert on it. Let's start with "select * from Heroes". Be carefull with semicolons (";"), JDBC request seems not to like it. Fill the statement in SQL Query field and run the test case (do not misstaken TestConnection button with Run TestCase one) as follows. If we did everything right we will see all our Heroes in XML format on the right side of JDBC Request:
jdbc connection test
We can also add some assertion there, but let's first imagine we would like to test if Topper Harley is Heroe number 4. To do so we are going to use WHERE clause in our simple statement just like this "select * from Heroes where ID=4". Since we know that we would like to see Topper as an output of our statement we can add following assertion to our Test Case (make sure you don't remove whitespaces otherwhise our simple assertion will not work):
jdbc connection test
Let's run our test case and enjoy results of our hard work:
jdbc connection test
You can download entire project from Downloads page.