Last Updated On
It’s necessary for us to establish connections to many databases during test automation. This article will teach us how to use an example code snippet to establish a Java connection to Microsoft SQL Server. However, before we do so, let’s first examine the various automation situations that call for database connections.
Table of Contents
Why do we need to connect to databases in automation?
To get test data – If the database is automated, test data may be immediately retrieved and processed using a test automation script.
To verify the result – To confirm the outcome, we may use automation to cross-check the front-end result to the database’s back-end record.
To delete test data created – It is best practice in automation to remove test data that has been created. In order to do this, we use database automation and immediately execute the delete query.
To update certain data – Update queries can be used to update test data in accordance with the requirements of the test script.
Connecting to SQL Server in Java
- Get a connection to the Database
- Create a statement object
- Execute the SQL query
- Process the Result Set
Get a connection to the Database
Need a connection string in the form of a JDBC URL.
jdbc:<driver protocol>:<driver connection details>
You can connect to MySQL, MS MySql Server, or Oracle using the below syntax:
MS MySql Server - jdbc:odbc:DemoDSN
MySQL - jdbc:mysql://localhost:3306/demodb
Oracle - jdbc:orac;e:thin@myserver:1521:demodb
Failure to connect to the database will throw an exception:-
- java.sql.SQLException : bad url or credentials
- java.lang.ClassNotFoundException: JDBC driver not in classpath
Create a statement object
The statement object is based on connection. It will be used later to execute SQL query.
The DriverManager class acts as an interface between users and drivers. DriverManager helps to connect an application based on the database connection string.
String dbUrl = "jdbc:mysql://localhost:3306/demo";
String username = "student";
String password = "student";
Connection conn = DriverManager.getConnection(dbUrl,username,password)
Statement stmt = conn.createStatement();
Execute the SQL query
We use the statement object to execute the SQL Query. Pass in the SQL query.
executeQuery method returns a result table in a ResultSet object. After you obtain the result table, you need to use ResultSet methods to move through the result table and obtain the individual column values from each row.
ResultSet result = stmt.executeQuery("select * from employee");
Process the result Set
Result Set is initially placed before the first row.
Result.next() – It moves forward one row and returns true if there are more rows to process
Looping through the result set.
JDBC API is defined by package:
java.sql and javax.sql
Add mysql-connector-j dependency to the pom.xml.
<dependency>
<groupId>com.mysql</groupId>
<artifactId>mysql-connector-j</artifactId>
<version>8.2.0</version>
</dependency>
Sample code to connect to SQL Server database
import java.sql.*;
public class JdbcTest {
public static void main(String[] args) throws SQLException {
Connection conn;
Statement stmt = null;
ResultSet result = null;
String dbUrl = "jdbc:mysql://localhost:3306/demo";
String username = "student";
String password = "student1$";
try {
//Get a connection to database
conn = DriverManager.getConnection(dbUrl, username, password);
System.out.println("Database connection is successful\n");
//Create a statement
stmt = conn.createStatement();
//Execute the SQL Query
result = stmt.executeQuery("Select * from employees");
//Process the result set
while (result.next()) {
System.out.println("First_Name :" + result.getString("first_name") + " , " + ("Last_Name :" + result.getString("last_name")));
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
The output of the above program is
Let us go to MySQL Server and run the command. Before that, if you want to create an “employees” table in the demo database, please use the below script:
create database if not exists demo;
use demo;
drop table if exists employees;
CREATE TABLE `employees` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`last_name` varchar(64) DEFAULT NULL,
`first_name` varchar(64) DEFAULT NULL,
`email` varchar(64) DEFAULT NULL,
`department` varchar(64) DEFAULT NULL,
`salary` DECIMAL(10,2) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (1,'Doe','John','john.doe@foo.com', 'HR', 55000.00);
INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (2,'Public','Mary','mary.public@foo.com', 'Engineering', 75000.00);
INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (3,'Queue','Susan','susan.queue@foo.com', 'Legal', 130000.00);
INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (4,'Williams','David','david.williams@foo.com', 'HR', 120000.00);
INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (5,'Johnson','Lisa','lisa.johnson@foo.com', 'Engineering', 50000.00);
INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (6,'Smith','Paul','paul.smith@foo.com', 'Legal', 100000.00);
INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (7,'Adams','Carl','carl.adams@foo.com', 'HR', 50000.00);
INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (8,'Brown','Bill','bill.brown@foo.com', 'Engineering', 50000.00);
INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (9,'Thomas','Susan','susan.thomas@foo.com', 'Legal', 80000.00);
INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (10,'Davis','John','john.davis@foo.com', 'HR', 45000.00);
INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (11,'Fowler','Mary','mary.fowler@foo.com', 'Engineering', 65000.00);
INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (12,'Waters','David','david.waters@foo.com', 'Legal', 90000.00);
Now execute the below SQL command:-
select * from demo.employees;
The output of the above program is
We are done! Congratulations on making it through this tutorial and hope you found it useful! Happy Learning!!
QA Automation Expert 