- SoapUI Cookbook
- Rupert Anderson
- 887字
- 2021-07-23 20:19:19
Creating and checking data with the JDBC Request TestStep
If you need a quick way to access and check external SQL-based data, the JDBC Request TestStep is a good place to start. The pro version of the JDBC Request TestStep adds ease-of-use functionality, which is useful for less technical users, but by no means essential if you have a reasonable grasp of SQL and aren't afraid to enter a JDBC connection string (URL). We'll concentrate on using the open source version here.
Getting ready
As the example for this recipe, we'll access a MySQL database, but any JDBC data source would work, although the SQL syntax may vary. To use the JDBC Request TestStep, the main things we'll need are:
- The database or access to it: If you don't already have MySQL, then download the latest version from http://dev.mysql.com/downloads/mysql/. The installation instructions for each platform are also provided there.
- The JDBC driver: Please download the MySQL connector from http://dev.mysql.com/downloads/connector/j/ if you don't already have it. You will also need the driver class name; for MySQL, it is
com.mysql.jdbc.Driver. - The JDBC connection string (URL): JDBC connection strings are very easy to find on Google, if you don't have yours already. The simple form of the MySQL connection string's URL is
jdbc:mysql://<hostname>:<port>/<db name>?user=<username>&password=<password>.Tip
Pro version configuration
The pro version of SoapUI simplifies the preceding two requirements. You still need to obtain and add the JDBC driver yourself, but a Configuration section is provided to select the driver class and build the connection string URL using parameters. See http://www.soapui.org/JDBC/testing-jdbc-databases.html if you need more info.
The project for this recipe can be found at <chapter 2 samples>/JDBCTestStep-soapui-project.xml.
How to do it...
We'll start by creating an empty project, TestSuite and TestStep. Then, we'll add the MySQL JDBC driver and configure the JDBC Request TestStep to connect to the MySQL database. Finally, we'll create a MySQL test table, enter data in it, and use the JDBC Request TestStep to query the test data and use an Assertion to check its values. Perform the following steps:
- First off, we're going to need a SoapUI project. It doesn't matter what type of project; create a new Generic Project with
TestSuite,TestCase, andJDBC Request TestStep. - Assuming you've installed MySQL, add the MySQL JDBC connector JAR, that is,
mysql-connector-java-5.1.17-bin.jar, to<SoapUI installation>/java/app/bin/ext/, and restart SoapUI. - When restarted, open the JDBC Request TestStep window and configure the following:
- Driver (class name):
com.mysql.jdbc.Driver - Connection String (URL): for example,
jdbc:mysql://localhost:3306/test?user=root&password=rooty - Click on TestConnection, and you should see a pop up that contains The Connection Successfully Tested
- Driver (class name):
- Now, we can set up a test table and some test data. The pro version has a full graphical query builder to help build queries—for more information see http://www.soapui.org/JDBC/testing-jdbc-databases.html.
Tip
MySQL Workbench
A free and very good graphical editor tool is MySQL Workbench (http://dev.mysql.com/downloads/workbench/). Along with most DB-related tasks, this can also generate queries for you.
For the open source version:
- Open a connection to your MySQL database using your preferred means; for example, if using the MySQL command line:
./mysql --user=root --password=rooty test - Create a test invoice table in your database, for example:
CREATE TABLE test.invoice ( id int(11) NOT NULL, company varchar(45) DEFAULT NULL, amount double DEFAULT NULL, due_date datetime DEFAULT NULL, PRIMARY KEY (id), UNIQUE KEY id_UNIQUE (id) )
- Add two test invoice records:
INSERT INTO invoice (id,company,amount,due_date) VALUES (1,'comp1',100,'2014-09-30 00:00:00'); INSERT INTO invoice (id,company,amount,due_date) VALUES (2,'comp2',200,'2014-12-01 00:00:00');
Tip
The JDBC Request TestStep can run any DDL and SQL statements
Depending on the privileges of the DB user you connect as, SoapUI's
JDBC Request TestStepcan also create data, for example, insert, delete, update, as well as perform DDL statements such as create, drop tables. While this is convenient here in this recipe, it's not normally allowed, nor is it a good practice in a professional environment to connect as the root user.
- Open a connection to your MySQL database using your preferred means; for example, if using the MySQL command line:
- Next, we can add a simple SQL query to select all the test data and see it in the XML view:
- Enter the following SQL statement in the SQL Query box:
select * from invoice
- Click on run (the green arrow), and you should see the invoice test data:
<Results> <ResultSet fetchSize="0"> <Row rowNumber="1"> <INVOICE.ID>1</INVOICE.ID> <INVOICE.COMPANY>comp1</INVOICE.COMPANY> <INVOICE.AMOUNT>100</INVOICE.AMOUNT> <INVOICE.DUE_DATE>2014-09-30 00:00:00.0</INVOICE.DUE_DATE> </Row> <Row rowNumber="2"> <INVOICE.ID>2</INVOICE.ID> <INVOICE.COMPANY>comp2</INVOICE.COMPANY> <INVOICE.AMOUNT>200</INVOICE.AMOUNT> <INVOICE.DUE_DATE>2014-12-01 00:00:00.0</INVOICE.DUE_DATE> </Row> </ResultSet> </Results>
- Enter the following SQL statement in the SQL Query box:
- Lastly, we can check the query results using
Assertions. Under the Assertions tab, add a newXPath Match Assertion:XPath: //Results[1]/ResultSet[1]/Row[1]/INVOICE.COMPANY[1] Expected Results: comp1
- This
Assertionshould pass, assuming the first result hasCOMPANY=comp1.
How it works...
The JDBC Request TestStep functionality is just the TestStep equivalent of a SQL database client. Apart from being used in TestCase to check query results using Assertions, it's also potentially useful for test data set up and teardown, for example, for test data in a web service or mock service backend database.
See also
- There are also two
JDBC Request TestStepspecificAssertiontypes,JDBC StatusandJDBC Timeout Assertions—for more information see http://www.soapui.org/JDBC/getting-started.html - The next recipe, Parameterizing SQL queries with the JDBC Request TestStep