炸巧克力球

書名： 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.

Tip

Troubleshooting

If you experience connection issues, don't forget to check the Request Log tab next to Assertions at the bottom of the JDBC Request TestStep. It can sometimes provide extra debugging, such as JDBC errors.

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, and JDBC 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.

Tip

SoapUI Extensions

Libraries added to <SoapUI installation>/java/app/bin/ext are added to SoapUI's classpath. You will need to restart SoapUI after adding a library JAR before it will be accessible.
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
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 TestStep can 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.

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>

Lastly, we can check the query results using Assertions. Under the Assertions tab, add a new XPath Match Assertion:
```
XPath: //Results[1]/ResultSet[1]/Row[1]/INVOICE.COMPANY[1]
Expected Results: comp1
```
This Assertion should pass, assuming the first result has COMPANY=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.

官术网_书友最值得收藏!

Creating and checking data with the JDBC Request TestStep

Getting ready

Tip

Tip

How to do it...

Tip

Tip

Tip

How it works...

See also

官术网_书友最值得收藏!

SoapUI Cookbook

Creating and checking data with the JDBC Request TestStep

Getting ready

Tip

Tip

How to do it...

Tip

Tip

Tip

How it works...

See also