Database validation using Java : Selenium

Automation of Database Testing using Java in Selenium

Database validation can be automated using Java in selenium webdriver. This is done using JDBC ("Java Database Connectivity") which is a Java API that allows to connect with database and execute SQL statements. It is a connector used for the connectivity between the Java Programming language and a wide range of databases with the help of corresponding database drivers. The JDBC API provides the following classes and interfaces

• DriverManager class 
• Connection interface 
• Statement interface 
• ResultSet interface

In order to test your Database using Selenium, you have to download the driver(jar file). For ex. SQL Server driver can be downloaded from

https://docs.microsoft.com/de-de/sql/connect/jdbc/download-microsoft-jdbc-driver-for-sql-server?view=sql-server-2017

MYSQL driver can be downloaded from

https://search.maven.org/search?q=g:com.google.protobuf%20AND%20a:protobuf-java

Oracle driver:

https://www.oracle.com/technetwork/database/features/jdbc/jdbc-ucp-122-3110062.html

Then click on Download button in next page.

Now select sqljdbc_7.0.0.0_enu.tar.gz  and then click on Next button

Unzip the downloaded file twice. Copy the mssql-jdbc-7.0.0.jre8 or mssql-jdbc-7.0.0.jre10 jar and paste it in libs folder of your project in Eclipse IDE and the right click on the jar and click on Add to build path as below

Once it is done, you need to follow below steps:

  Ø  Database Connection:

            In order to make a connection to the database the syntax is

            DriverManager.getConnection(URL, "username", "password" )

Where,

• username is the login text used while login into database.

• password is text used while login into database.
• URL is of format jdbc:databaseType://ipaddress:portnumber/database-name"
• databaseType - The name of database you are trying to connect. For example to connect to oracle database this value will be "oracle"

For DB2 database -- ‘db2’

For MYSQL database -- ‘mysql

So connection string for SQL Server database with DBname "TestDB "

“jdbc:sqlserver://localhost:1433;instanceName=MSSQLSERVER;database=TestDB”

So Connection string will be:

Connection conn = DriverManager.getConnection(“jdbc:sqlserver://localhost:1433;instanceName=MSSQLSERVER;database=TestDB”,” testuser1”,” testpassword”);
Ø  Querying Database :

Once connection is made, Statement interface is used to send SQL statements.

Statement sm = conn.createStatement();

Once the statement variable is created use the executeQuery method to execute the SQL statements as below:

ResultSet rs = sm.executeQuery("SELECT *  FROM Emp"); // Emp is table name

Ø  Processing results:

while(rs.next()) {

System.out.print(rs.getString("ID"));

System.out.print("  ");

System.out.println(rs.getString("Name"));

Finally Code in eclipse IDE looks like as