Selenium

Java Database Connectivity

This is very important for report testing or if there are more data on webpage and we need to compare that data with database data.
The report cannot be tested with excel sheet as there are many combinations for data also sorting feature for webpages. If we want to compare with excel sheet we need to maintain lots of excel sheets. That is very difficult.
What generally QA person who is working on a report or any table testing is.
Ask a developer to give query which he has used for generating reports for various combinations.
Uses same query for testing results on a webpage, for this we need to know how to fetch data from database through Java.
Below are some basic steps we need to perform.

Let’s say we have table employee. Create this table for learning this concept.

CREATE TABLE employee (
`eid` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
`ename` VARCHAR(45) NOT NULL,
`ephone` VARCHAR(45) NOT NULL, PRIMARY KEY (`eid`)
)

Insert some data for testing using the below given query:
insert into employee values (1,’javabykiran’,’8888809416’); insert into employee values (2,’javabykiran’,’8888809416’);

List all rows for the specified columns
mysql> SELECT name, price FROM products;
Start writing code using main method because at a later stage this code can be used for comparing the actual and expected results.
Data which we are fetching from the database will give the actual and expected result while the data available on the webpage is actual data.

Class employeeRetrival {
    public static void main(){
    // Here’s code for DB connectivity
    1. Load MySQL/Oracle driver
    2. Create connection
    3. Create a query
    4. Create a statement for placing our query
    5. Fire query to DB through statement object
    6. Retrieve results in the Result Set object
    7. Create arraylist for an employee object so that we can place result set data into array List.
    }
}

First, we need to add the jar file for MySQL driver in our project. We download it from the MySQL website as shown in the below image.

Once we download it, we will add it into our project as shown in the above image. Project structure will be as shown in the below image:

package com.javabykiran; 
import java.sql.Connection; 
import java.sql.DriverManager; 
import java.sql.ResultSet; 
import java.sql.Statement; 
public class JdbcEx {
    public static void main(String[] args) throws Exception { 
        Class.forName(“com.mysql.jdbc.Driver”);
        Connection connection = DriverManager.getConnection(
                                “jdbc:mysql://localhost:3306/test”,“root”, “root”);
        Statement stmt = connection.createStatement();
        String sql = “select ename,ephone from employee”; 
        ResultSetrs = stmt.executeQuery(sql); 
        while(rs.next()){
            String ename = rs.getString(1); 
            System.out.println(ename); 
            String ephone = rs.getString(2); 
            System.out.println(ephone);
        }
    }
}

This program prints data from the database.
This result set data needs to be converted into array list so that we can compare it with web table data.
This proves very helpful in testing data in bulk.
Now we will write complete example for getting arraylist from one method and then iterating it over all elements.

package com.javabykiran; 
import java.sql.Connection; 
import java.sql.DriverManager; 
import java.sql.ResultSet; 
import java.sql.Statement; 
import java.util.ArrayList;
public class JdbcEx {
    Connection createConnection() throws Exception {
        Class.forName(“com.mysql.jdbc.Driver”);
        Connection connection = DriverManager.getConnection(
                                “jdbc:mysql://localhost:3306/test”,“root”, “root”);
        return connection;
    }
    ArrayList< Employee>createEmployeesDataFromDB() throws Exception {
        ArrayList< Employee>alEmp = new ArrayList< Employee>();
        Employee emp = new Employee();
        Statement stmt = createConnection().createStatement(); 
        String sql = “select eid,ename,ephone from employee”; 
        ResultSetrs = stmt.executeQuery(sql);
        while (rs.next()) {
            inteid = rs.getInt(1); 
            System.out.println(eid); 
            emp.setEmpId(eid);
            String ename = rs.getString(2); 
            System.out.println(ename);
            emp.setEmpName(ename);
            String ephone = rs.getString(3); 
            System.out.println(ephone); 
            emp.setEmpPhone(ephone); 
            alEmp.add(emp);
        }
        return alEmp;
    }
    public void retriveArrayList() throws Exception { 
        ArrayList< Employee>alEmp = createEmployeesDataFromDB();

        // Here we can compare data for test cases.
        for (Employee employee : alEmp) {
            System.out.println(employee.getEmpName());
            System.out.println(employee.getEmpId()); 
            System.out.println(employee.getEmpPhone());
        }
    }
    public static void main(String[] args) throws Exception { 
        JdbcExjdbcEx=new JdbcEx(); 
        jdbcEx.createEmployeesDataFromDB(); 
        jdbcEx.retriveArrayList();
    }
}

package com.javabykiran; 
    public class Employee {
        intempId;
        String  empName; 
        String empPhone; 
        public intgetEmpId() {
            return empId;
        }
    public void setEmpId(intempId) { 
        this.empId = empId;
    }
    public String getEmpName() { 
        return empName;
    }
    public void setEmpName(String empName) { 
        this.empName = empName;
    }
    public String getEmpPhone() { 
        return empPhone;
    }
    public void setEmpPhone(String empPhone) { 
        this.empPhone = empPhone;
    }
}

Creating and Deleting a Database:
- mysql> DROP DATABASE southwind;
- mysql> CREATE DATABASE IF NOT EXISTS southwind;
- mysql> DROP DATABASE IF EXISTS southwind;
Show Create Database:
mysql> SHOW CREATE DATABASE southwind \G
Creating and Deleting a Table:
- Show the current (default) database
  mysql> SELECT DATABASE();
- Show all the tables in the current database
  mysql> SHOW TABLES;

Create the table “products”:

mysql> CREATE TABLE IF NOT EXISTS products (
    productID INT UNSIGNED NOT NULL AUTO_INCREMENT, 
    productCode CHAR(3) NOT NULL DEFAULT ‘’,
    name VARCHAR(30) NOT NULL DEFAULT ‘’, 
    quantity INT UNSIGNED NOT NULL DEFAULT 0,
    price DECIMAL(7,2) NOT NULL DEFAULT 99999.99,
    PRIMARY KEY (productID)
);

Show all tables to confirm that “products” table has been created:
mysql> SHOW TABLES;
Describe the fields (columns) of the “products” table:
mysql> DESCRIBE products;
Show CREATE TABLE statement used by MySQL to create this table:
mysql> SHOW CREATE TABLE products \G
Inserting Rows
Insert a row with all the column values:
mysql> INSERT INTO products VALUES (1001, ‘PEN’, ‘Pen Red’, 5000, 1.23);
Insert multiple rows in one command
Inserting NULL to the auto_increment column results in max_value + 1:
mysql> INSERT INTO products VALUES (NULL, ‘PEN’, ‘Pen Blue’, 8000, 1.25), (NULL, ‘PEN’, ‘Pen Black’, 2000, 1.25);
Insert value to selected columns
Missing value for the auto_increment column also results in max_value + 1:
mysql> INSERT INTO products (productCode, name, quantity, price) VALUES (‘PEC’, ‘Pencil 2B’, 10000, 0.48), (‘PEC’, ‘Pencil 2H’, 8000, 0.49);
Missing columns get their default values:
mysql> INSERT INTO products (productCode, name) VALUES (‘PEC’, ‘Pencil HB’);
2nd column (productCode) is defined to be NOT NULL:
mysql> INSERT INTO products values (NULL, NULL, NULL, NULL, NULL);
show table:
mysql> SELECT * FROM products;
Remove the specific row:
mysql> DELETE FROM products WHERE productID = 1006;

Selenium

Java Database Connectivity

Why DB connectivity needed for QA Automation?

Java integration with Database

Java and DB Integration Example

Some more examples of queries