If you have learned Selenium or LeanFT and want to get your hands dirty on Selenium or LeanFT by creating an automation framework, you must know how to interact with the MySQL database using Selenium and LeanFT because any automation framework is incomplete without MySQL database connectivity.
MySQL Database is used for various purposes in automation testing like storing Test Data, Test Script Details, Test Environment details, user credentials for different environments (like Staging, UAT, Prod), configuration details, and etc.
You can also perform MySQL database testing using Selenium and LeanFT once you establish a connection with the database.
In this, article we will see how to perform the various operation with MySQL Database in Selenium or LeanFT using Java.
So, here we go!
How to Do MySQL Database Testing Using Selenium and LeanFT
MySQL database testing in Selenium and LeanFT requires the following activities:
- Read data from MySQL Database in Selenium and LeanFT
- Update record in MySQL Database in Selenium and LeanFT
- Insert a new record in MySQL Database in Selenium and LeanFT
MySQL Database Dependencies in Java
If you are not using either Maven or Gradle you can download the MySQL Connector jar from http://www.java2s.com/Code/Jar/m/Downloadmysqlconnector5118jar.htm and associate it with your project classpath.
Maven: If you are using Maven in your Java project, you can add the following dependency to your pom.xml file.
|
1 2 3 4 5 6 |
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.25</version> </dependency> |
Gradle: If you are using the Gradle project, you can add the following dependency to your build.gradle file.
|
1 2 |
// https://mvnrepository.com/artifact/mysql/mysql-connector-java implementation group: 'mysql', name: 'mysql-connector-java', version: '8.0.25' |
JDBC (Java Database Connectivity)
JDBC stands for Java Database Connectivity. JDBC is a Java API that helps us to connect and execute SQL queries with a wide range of databases using Java Programming language. JDBC provides methods to query, insert and update data in a database.
In this article, I will show you how to connect MySQL databases using Java in Selenium and LeanFT. Having said that, you can connect to any database by just providing the driver class for the required database.
Steps to Connect Java application with the MySQL Database
- Load the JDBC driver for MySQL DB using Class.forName(“com.mysql.cj.jdbc.Driver”);
- Connect to Databse using DriverManager.getConnection(dbUrl,userName,password);
- dbURL= MySQL Database HostName
- userName = MySQL Database Host user name
- password = MySQL Database Host password
- Create Statement object to fire queries to database using
- Retrieve data from the result set
- At the end close all the resources like DB connection, statement, and resultset object
How to Read Data from MySQL Database?
In order to understand how to read data from MySql Database, let’s consider the example of the following Employee table where EmpID is the primary key with auto-increment ID value.
The syntax for MySql database host URL is as follows.
jdbc:mysql://<<hostname>>:<<portNumber>>/<<DBName>>
Note: The default port number for MySql Database is 3306 if it has not been changed.
I will connect to my localhost database (mytestdb).For that the URL will be as follows.
jdbc:mysql://localhost:3306/mytestdb
The userName for my database is root and the password is an empty value. So to read data from this Employee table, we can use the following code.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class ReadDataFromMySqlDB { public static void main(String[] args) throws ClassNotFoundException, SQLException { String dbURL = "jdbc:mysql://localhost:3306/mytestdb"; //Your DB HostName URL String username = "root"; //Your Database Host User name String password = ""; // Your Database Host Password //Load MySQL JDBC Driver Class.forName("com.mysql.cj.jdbc.Driver"); //Establish connection with the database Connection con = DriverManager.getConnection(dbURL,username,password); //Create Statement object Statement stmt = con.createStatement(); String selectquery = "SELECT * FROM employee WHERE EmpID=102"; //Execute the SQL Query and store the results in ResultSet ResultSet rs = stmt.executeQuery(selectquery); //While loop to iterate through all data and print results while (rs.next()) { System.out.println(rs.getInt("EmpID")+ " | " + rs.getString("FirstName") + " | "+ rs.getString("LastName")+ " | " + rs.getString("Department")); } //Closing Database resources stmt.close(); rs.close(); con.close(); } } |
The output of the Java Code will be as follows:
How to Update MySQL Database Table Using Java?
Currently, the department name against EmpID 104 is ‘IT‘.We will update this department value to ‘Finance‘.You can use the following Java program to update the MySQL database table.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; public class UpdateMySqlDB { public static void main(String[] args) throws ClassNotFoundException, SQLException { String dbURL = "jdbc:mysql://localhost:3306/mytestdb"; //Your DB HostName URL String username = "root"; //Your Database Host User name String password = ""; // Your Database Host Password //Load MySQL JDBC Driver Class.forName("com.mysql.cj.jdbc.Driver"); //Establish connection with the database Connection con = DriverManager.getConnection(dbURL,username,password); //Create Statement object Statement stmt = con.createStatement(); String sqlQuery = "Update employee set Department ='Finance' WHERE EmpID=104"; //Execute the SQL Query and store the results in ResultSet int iStatus = stmt.executeUpdate(sqlQuery); if(iStatus==1) { System.out.println("Record was updated successfully"); } //Closing Database resources stmt.close(); con.close(); } } |
How to Insert a Record into MySQL Database in Java?
As of now, there are five records in the Employee table. We will insert a new record into it. We can use the following java program to insert a new record into the Employee table.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; public class InsertRecordInMySqlDB { public static void main(String[] args) throws ClassNotFoundException, SQLException { String dbURL = "jdbc:mysql://localhost:3306/mytestdb"; //Your DB HostName URL String username = "root"; //Your Database Host User name String password = ""; // Your Database Host Password //Load MySQL JDBC Driver Class.forName("com.mysql.cj.jdbc.Driver"); //Establish connection with the database Connection con = DriverManager.getConnection(dbURL,username,password); //Create Statement object Statement stmt = con.createStatement(); String sqlQuery = "INSERT INTO employee (FirstName,LastName,Department) VALUES('Amit','Dubey','HR');"; //Execute the SQL Query and store the results in ResultSet int iStatus = stmt.executeUpdate(sqlQuery); if(iStatus==1) { System.out.println("Record was inserted successfully"); } //Closing Database resources stmt.close(); con.close(); } } |
Conclusion
In this article, we have seen how to fetch data from the MySQL database along with the update and insert operation. Hope you would like this article. Please share this article and if you have any queries please write in the comment box.
Recommended Posts
- Read and Write Excel File in Java Using Apache POI Lib
- Configure Selenium Webdriver With Eclipse
- Page Object Model (POM) & Page Factory in Selenium WebDriver
- How to Use CSS Selector in Selenium WebDriver | 13 Examples
- XPath in Selenium WebDriver with Example
- How to Use Regular Expression in LeanFT | UFT Developer
- Object Identification Center in LeanFT | UFT Developer
