During Data Validation in our Testing Framework, there comes a situation where we have to validate the response body .i.e. when we create a record we generally see that the record is created in UI .But we also need to validate whether the created record is present in Database as well which is very important. So we need to retrieve the data from the database and that’s why connection to database comes into picture.
In this blog, I am going to show you how to connect to a database in Java and show a sample ‘Select’ query execution. We can also try for Insert,Update and Delete queries too in the same way.
JDBC (Java Database Connectivity) is the Java API that manages connecting to a database, issuing queries and commands, and handling result sets obtained from the database.
I have created a Table named “books_entity” for my blog. And below picture shows the existing records in my database.
First make sure we have postgres jar file in our classpath.
Now that we have the required jar files and our database ready. So let’s get Started…
We need 3 main steps to follow:
1) Create a Connection
2) Submit a query (Create and Execute Query)
3) Close Connection
Step1) Create a Connection: What and How are we creating a connection. Let’s find out.
The main components we need are Type of Database, Database server, Database name, Credentials to access.
We are using SQL database .As I am testing in my local computer, I am using localhost with my port number available. And the database name is “postgres”. I have declared the variables below used to create a connection.
The imports we need to get access to a class that facilitates the standard Java database connection:
-Connection represents the connection to the database.
-DriverManager obtains the connection to the database
-SQLException handles SQL errors between the Java application and the database.
-ResultSet and Statement model the data result sets and SQL statements.
The getConnection() method of DriverManager class is used to establish connection with the database.
Here the Connection is created.
Step 2) Submit a query (Create and Execute Query)
The createStatement() method of Connection interface is used to create statement. The object of statement is responsible to execute queries with the database.
The executeQuery() method of Statement interface is used to execute queries to the database. This method returns the object of ResultSet that can be used to get all the records of a table, which we then use to iterate over the data with while (rs.next()).
Step3) Close Connection
By closing connection object statement and ResultSet will be closed automatically. The close() method of Connection interface is used to close the connection.
When we put together all the code ,it looks like this:
The Output in the Console is as follows which is the same as the first picture in this blog from Postgres.
Now we can validate the response data and assert that the record created from our TestCase is present in the database as well.