February 7, 2025

Introduction

Using Rest Assured to retrieve data from a database usually combines API testing with database interaction. Although Rest Assured doesn’t operate directly with databases, we may combine it with an ORM framework (like Hibernate) or a database connection library like JDBC to retrieve data and compare it to the API response.

To retrieve data from a database and utilize it in Rest Assured tests, follow these steps:

a)We should know how to build a connection with Rest Assured.

Add mysql-connector Dependencies into pom.xml

				
					<dependency>
<groupId>com.mysql</groupId>
<artifactId>mysql-connector-j</artifactId>
<version>8.3.0</version>
</dependency>

2.We will create an object of a Connection class to establish a connection with the database.

				
					Connection mycon = null;

3.We will create an object of a Statement class to execute the set of SQL statements.

				
					Statement myst=null;

4.We will create an object of the ResultSet class to store the value in the ResultSet.

				
					ResultSet myRs=null;

5.We will create an object of Object.

				
					Object obj=null;

b) When we have a connection with the database

We will be able to have a connection with the database with the help of a class called DriverManger its basic service is to manage a set of JDBC(Java Database Connectivity) drivers.

Syntax to create a connection:

				
					mycon= DriverManager.getConnection(“jdbc:mysql://localhost:3306/student2”, “root”, “root”);

Explanation:

DriverManager.getConnection(“driver:kindof the database://localhost:portnumber/name of the schema”,”userid”, “passowrd”);

2.Create a statement

				
					myst=mycon.createStatement();

3.Execute query

				
					myrs=myst.executeQuery(SQLQuery.myquery());//we will always avoid hard coding we will create another class

Code Snippet:

				
					public class SQLQuery {
	public static String myquery()
	{
		String selectLocation="select * from student.Persons where city ='Mumbai';";
		return selectLocation;
	}

Screenshot:

4. Print the query

				
					while(myrs.next())
			
		{
			obj=myrs.getString(x);
		}

Code Snippet:

				
					public class DatabaseConnectionEx {
	public static Object DBConnection(int x) throws SQLException
	{
		Connection mycon=null;//we can start the connection with database with help of DriverManager
		Statement myst=null;//execute set of sql statement
		ResultSet myrs=null;//get the result
		Object obj=null;//get value in form of Object
		//connection created
		mycon	=DriverManager.getConnection("jdbc:mysql://localhost:3306/student","root","root");
		System.out.println("Connection Succesful");
		//create the statement
		myst=mycon.createStatement();
		//execute query
				myrs=	myst.executeQuery(SQLQuery.myquery());
		while(myrs.next())
			
		{
			obj=myrs.getString(x);
		}
		
		return obj;
	}
}

ScreenShot:

In the RestAssured payload, we have to pass a single entity in one row. We will fetch the single key from the database.

1.We will create a class(ex Createuser.java) and use Map.

Code Snippet:

				
					Map<String,Object> mp= new LinkedHashMap<String,Object>();
		mp.put("PersonId",DatabaseConnectionEx.DBConnection(1));
		mp.put("Lastname",DatabaseConnectionEx.DBConnection(2));
		mp.put("Firstname",DatabaseConnectionEx.DBConnection(3));
		mp.put("Address",DatabaseConnectionEx.DBConnection(4));
mp.put("City",DatabaseConnectionEx.DBConnection(5));
		
		
		RestAssured.baseURI="https://reqres.in";
	String response=	given().log().all().body(mp).headers("Content-Type","application/Json")
		.when().post("api/users")
		.then().assertThat().statusCode(201).extract().response().asString();
	System.out.println(response);
Output:
{
   "PersonId": "5689",
   "Lastname": "Agarwal",
   "Firstname": "Harry",
   "Address": "Park Street",
   "City": "Mumbai"
}

How to do fetch id from the response?

				
					sonPath js=new JsonPath(response);
int i=	js.getInt("id");
System.out.println(i);

{"PersonId":"5689","Lastname":"Agarwal","Firstname":"Harry","Address":"Park Street","City":"Mumbai","id":"965","createdAt":"2025-02-03T11:15:54.532Z"}
965

Conclusion:

To ensure that everything is operating as it should, we establish a connection to the database, retrieve the required data, and then compare or validate that data with the response from the API.By following this tutorial and gaining hands-on experience, you are equipping yourself with valuable knowledge that can propel your career in Software Testing Cucumber to new heights. By combining database validation with API testing, you can ensure that your application is operating properly on both the frontend (API) and backend (database).

Also read :

Interview Questions and Answers on API Rest Assured

Cucumber Framework in Selenium

Meaning of Cucumber Testing

BDD Cucumber Interview Questions and Answers

Popular Courses

Digital Marketing
Certification Course