Configure Database Connection in Selenium

Selenium is most popular Test automation framework in the Software Industry. Selenium support most of major languages like Java, Python, Ruby, C#, JavaScript, Perl and PHP. In this small tutorial we will give code sample to configure Database connection and run a query.

Few steps that help to setup Database integration for Selenium.

Download JDBC driver and add to project

JDBC driver is the connector of data and your program. In this tutorial we use MS SQL as Database server. You can search in google.com to find JDBC driver for your database server.

Download JDBC driver for MS SQL
https://docs.microsoft.com/en-us/sql/connect/jdbc/download-microsoft-jdbc-driver-for-sql-server?view=sql-server-ver15

Add dependencies

Next thing is to add dependency to dependency file. You need to have <dependancy>tag, <groupId>child tag ,<artifactId> sibling tag, and <verstion>sibling tag. You can see the code sample below.

<dependency>
   <groupId>mysql</groupId>
   <artifactId>mysql-connector-java</artifactId>
   <version>5.1.39</version>
</dependency>
<dependency>
   <groupId>com.microsoft.sqlserver</groupId>
   <artifactId>mssql-jdbc</artifactId>
   <version>8.2.0.jre8</version>
   <scope>test</scope>
</dependency>

Dependency file can be found in File> project structure> modules> dependencies

Lets see the Database Connection file structure in utils

package utils;

import com.sun.scenario.effect.impl.sw.sse.SSEBlend_SRC_OUTPeer;

import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import java.sql.*;

public class DbConnection {
        static Connection con = null;
        // Statement object
        private static Statement stmt;
        // Constant for Database
        public static String DB_URL = "jdbc:sqlserver://localhost:1433;databaseName=QA-DATABASE_NAME";
        // Constant for Database Username
        public static String DB_USER = "user";
        // Constant for Database Password
        public static String DB_PASSWORD = "SMBCE#@#rEIX9#$ghBE";

        public DbConnection DbConnection () throws SQLException, ClassNotFoundException, InterruptedException {
            Thread.sleep(3000);
            try {
                // Make the database connection
                String dbClass = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
                Class.forName(dbClass).newInstance();
                // Get connection to DB
                Connection con = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
                // Statement object to send the SQL statement to the Database
                stmt = con.createStatement();
            } catch (Exception e) {
                e.printStackTrace();
            }
            return this;
        }


        public DbConnection updateDatabase(Int studentId) throws SQLException {
            try {
                String query = "UPDATE students SET active = 1 WHERE id = " + studentId;
                // Get the contents of id table from DB
                int res = stmt.executeUpdate(query);

                if( res == 1 ) {
                    String selectQuery = "SELECT active FROM students WHERE id = " + studentId;
                    ResultSet resultSet = stmt.executeQuery(selectQuery);
                    while (resultSet.next()) {
                        String sid = resultSet.getString(1);
                        System.out.println(sid);
                    }
                }
                System.out.print("DB result" + res);
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return this;
        }

        public DbConnection closeDbConnection () throws Exception {
            // Close DB connection
            if (con != null) {
                con.close();
            }
            Thread.sleep(3000);
            return this;
        }
}

You can execute code as below.

Writer: Minusha