Connect Database Using Selenium WebDriver

Tutorial Connecting to DataBase using Selenium WebDriver 

Web Driver cannot directly connect to Database. You can only interact with your Browser using Web Driver. For this we use JDBC("Java Database Connectivity").The JDBC API is a Java API for accessing virtually any kind of tabular data.The value of the JDBC API is that an application can access virtually any data source and run on any platform with a Java Virtual Machine.

In simplest terms, a JDBC technology-based driver ("JDBC driver") makes it possible to do three things:

1.Establish a connection with a data source
2.Send queries and update statements to the data source
3.Process the results

 1.Establish a connection with a data source
The traditional way to establish a connection with a database is to call the method
DriverManager.getConnection(URL,  "username", "password" )
URL :   jdbc:<subprotocol>:<subname>
<subprotocol>-the name of the driver or the name of a database connectivity mechanism
<subname> - The point of a subname is to give enough information to locate the data source .(Includes IP address , Port number and exact name of DataSource)

For connecting to MYSQL URL will be
jdbc:mysql://localhost:3306/hoale

2.Send queries and update statements to the data source
A Statement object is used to send SQL statements to a database over the created connection in Step 1.
Statement-created by the Connection.createStatement methods. A Statement object is used for sending SQL statements with no parameters.
PreparedStatement-created by the Connection.prepareStatement methods. A PreparedStatement object is used for precompiled SQL statements. These can take one or more parameters as input arguments (IN parameters).
CallableStatement-created by the Connection.prepareCall methods. CallableStatement objects are used to execute SQL stored procedures
In Short
createStatement methods-for a simple SQL statement (no parameters)
prepareStatement methods-for an SQL statement that is executed frequently
prepareCall methods-for a call to a stored procedure

3.Process the results
A ResultSet is a Java object that contains the results of executing an SQL query.We will have separate post on it.The JDBC API provides three interfaces for sending SQL statements to the database.

Prerequisites:

-      mysql-connector-java-5.1.0-bin.jar  or above versions

-      Create table Employee in Navicat

-      Reading a old http://howtesting.blogspot.com/2013/01/creating-html5-page.html create a sample webform

This is example for connection DataBase(mysql) using Selenium WebDriver

 

package com;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

import org.openqa.selenium.By;

import org.openqa.selenium.WebDriver;

import org.openqa.selenium.WebElement;

import org.openqa.selenium.firefox.FirefoxDriver;

import org.testng.annotations.AfterTest;

import org.testng.annotations.BeforeTest;

import org.testng.annotations.Test;

import com.mysql.jdbc.ResultSet;

import com.mysql.jdbc.Statement;

import com.thoughtworks.selenium.SeleneseTestBase;

public class ConnectDB extends SeleneseTestBase{

     WebDriver driver;

     String url ="";

     @BeforeTest

public void setUp() throws Exception{

     driver = new FirefoxDriver();

     url = "file:///D:/ECLIPSE/workspace_eclipseclassic/ConnectDB/src/com/modules/HTML5Demo.html";

     driver.get(url);

}

     @Test

     public void CreateDB() throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException{

           //Prepare connection

           String url1 ="jdbc:mysql://localhost:3306/hoale";

           // Load Microsoft SQL Server JDBC driver

           String dbClass = "com.mysql.jdbc.Driver";

           Class.forName(dbClass).newInstance();

           //Get connection to DB

           Connection con = DriverManager.getConnection(url1, "root", "");

           //Create Statement

           Statement stmt = (Statement) con.createStatement();

           // method which returns the requested information as rows of data

           ResultSet result = (ResultSet) stmt.executeQuery("select * from employee");

           if(result.next())

           {

                String id = result.getString("ID");

                String info = result.getString("Info");

                driver.getCurrentUrl();

                WebElement a = driver.findElement(By.id("txtID"));

                a.sendKeys(id);

                WebElement b = driver.findElement(By.id("txtInfo"));

                b.sendKeys(info);

                WebElement btnclick = driver.findElement(By.id("btnclick"));

                btnclick.click();

                System.out.print("Passed");

           }

     }

    

     @AfterTest

public void tearDown(){

     driver.close();

}

}

 

Basics of TestNG Frameworks

TestNG Framework

TestNG is testing framework inspired from JUnit and NUnit but introducing some more new functionality that makes it more powerful  and easier to use. It is open source automated testing framework.

Benefits of TestNG

1. It gives you HTML report of execution.
2. Annotation in TestNG make tester's life easy.
3. Test cases can grouped together and prioritized in well manner.
4. Parallel testing is possible and we can generates logs also by enabling log4j.xml.

Test Case Writing

1. Very first thing is, write the business logic of the test.
2. Use TestNG annotations in the your test code.
3. Add the information about your test (e.g. Class names, methods names, groups names..) in testng.xml file.
4. Once xml is redy with everything run it as TestNG.

Annotations in TestNG

@BeforeSuite: The annotated method will be run before all tests in this suite have run.
@AfterSuite: The annotated method will be run after all tests in this suite have run.
@BeforeTest: The annotated method will be run before any test method belonging to the classes inside the tag is run.
@AfterTest: The annotated method will be run after all the test methods belonging to the classes inside the tag have run.
@BeforeClass: The annotated method will be run before the first test method in the current class is invoked.
@AfterClass: The annotated method will be run after all the test methods in the current class have been run.
@BeforeMethod: The annotated method will be run before each test method.
@AfterMethod: The annotated method will be run after each test method.
@Test: The annotated method is a part of a test case.

Install TestNG in IntellijIDEA 

 1. Select Help menu --> click on find action --> enter TestNG --> select TestNG from the listed plugins and install it.

2. After installation and restarting it, just verify if TestNG was needed successfully installed. Right click on your prooject and see if TestNG is dispalyed.

How to run Test Suite using TestNG

1. Create project let say "DemoProject".

2. Create package e.g. "com.company".

3. Create class files under the given project which are having number of test cases.

4. Right click on project and create new file "testng.xml" 

5. Open the testng.xml file and write the structure as follows:

 

<suite name="Test-Suite" >
<test name="Tools-QA">
<classes>
    <class name="<package name 1>.<class name 1"> />
    <class name="<package name 2>.<class name 2"> />
    <class name="<package name 3>.<class name 3"> />
    ...................

    ...................

</classes>
</test>
</suite>

6. Once create the file and mentioned the appropriate package name and class name then run this file as TestNG which will be executed all the test cases (class files) and execution will be done in the sequential manner. If one do not want to run second test then just comment it out and run the testng.xml file which will give you detail result of test suite.

7. After execution of this suite one can see the report in HTML format by clicking on "Export Test Result" icon on the top right corner of the left side console.

Will keep posting remaining.........

How do I get date, month, year values from calender java script...

I want to get date from the calender in javascript using selenium webdriver. Below are the few lines to get and set date from javascript calender. Used Javascript executor with getElementById and passed specific value when you want to set. 

Let's try this out and get experience....

Code:

JavascriptExecutor js = (JavascriptExecutor) driver;

driver.findElement(By.id("purchaseDatebtn")).click();

((JavascriptExecutor)driver).executeScript("document.getElementById(\"purchaseDate\").value = \"10/07/2014\" "); 

element=driver.findElement(By.id("depositDate"))

((JavascriptExecutor)driver).executeScript("document.getElementById(\"depositDate\").value = \"10/07/2014\" ");

Select Date Using Selenium Webdriver

How To Select Date From DatePicker Using Selenium WebDriver.......

A lot of application has datepicker which shows calenders upon clicking on it. The reason is to test, Does it shows calender after clicking on it and get the user selected date. So here currently working on Selenium WebDriver. I want to select values in date range from the drop down.. I want to know how can i select the values as Date, Month and year in the date picker drop down.

Following code will open the url in firefox once it come up, click on text box which shows calender and then naviage to February and select the defined date in the code below. It will show name of month in console window.  

Here we go.........

import org.junit.Test;
import org.openqa.selenium.*;
import org.openqa.selenium.firefox.FirefoxDriver;
import org.testng.annotations.BeforeTest;
import org.openqa.selenium.WebElement;
import java.util.List;
import java.util.concurrent.TimeUnit;

 

public class demoDatePicker {

    public static void main(String[] args) {

               WebDriver driver = new FirefoxDriver();

        driver.get("http://jqueryui.com/datepicker/");
        driver.manage().window().maximize();

        WebElement ele = driver.findElement(By.className("demo-frame"));
        driver.switchTo().frame(ele);

        driver.manage().timeouts().implicitlyWait(60, TimeUnit.SECONDS);
        // Click on text box so that date picked will come
        driver.findElement(By.id("datepicker")).click();
        driver.manage().timeouts().implicitlyWait(60, TimeUnit.SECONDS);

        for (int i = 0; i < 12; i++) {
            // Get month string
            String month = driver.findElement(By.className("ui-datepicker-month")).getText();
            if(month.equalsIgnoreCase("February"))
                break;
            driver.findElement(By.xpath("/html/body/div/div/a[2]/span")).click();
            System.out.println(month);
        }

        // Date picker is a table so that navigate to each cell, If particular
        // cell matches value 20 then select it
        WebElement dateWidget = driver.findElement(By.className("ui-datepicker-calendar"));

        List<WebElement> rows = dateWidget.findElements(By.tagName("tr"));
        List<WebElement> columns = dateWidget.findElements(By.tagName("td"));

        // select 20th date
        for (WebElement row : rows) {
            for (WebElement col : columns) {
                if (col.getText().equals("20")) {
                    col.click();
                    break;
                }
            }
        }
    }
}

How to write data to Excel Sheet in Selenium WebDriver

Before start writting data to excel sheet make sure Apache POI library is downloaded and add into your IDE you are going to use. The same library is being used for reading data from excel file. So this is Microsoft API which used for reading and writting data from/to Microsoft stuff.

Below code is performed using TestNg. Now writting data to excel sheet in a sequential order or maintain insertion order of keys, Order in which keys are inserted in to LinkedHashMap.

Following is the code to write to excel workbook:

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.junit.Assert;
import org.junit.Before;
import org.openqa.selenium.firefox.FirefoxDriver;
import org.testng.annotations.AfterClass;
import org.testng.annotations.BeforeClass;
import org.testng.annotations.Test;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.LinkedHashMap;
import java.util.Set;

/**
 * Created by Gaurav on 5/26/2014.
 */
public class demoWriteToExcel {

    LinkedHashMap<String,Object[]> table;
    HSSFWorkbook hssfWorkbook;
    HSSFSheet hssfSheet;
    FirefoxDriver firefoxDriver;

    @BeforeClass
    public void setUp(){

        hssfWorkbook = new HSSFWorkbook();
        hssfSheet = hssfWorkbook.createSheet("TestData");
        

        //Used linkedHashMap to maintain insertion order of keys

        table = new LinkedHashMap<String, Object[]>();
        table.put("1",new Object[]{"SrNo", "TestCase", "Result"});

        firefoxDriver = new FirefoxDriver();
        firefoxDriver.get("http://newtours.demoaut.com/");

    }

    @Test
    public void login() {

       // It will insert data into table when it can reach to the login page.

        Assert.assertEquals(firefoxDriver.getTitle(), "Welcome: Mercury Tours");
        table.put("2", new Object[]{"1", "I can reach login page", "Pass"});

    }

    // Initialization of rows and cells to get data into the excel sheet.

    @AfterClass
    public void filldata(){

        Set<String> strings = table.keySet();

        //rows loop

        int rowCount = 0;
        for(String key: strings){

            HSSFRow row = hssfSheet.createRow(rowCount);
            Object[] objects = table.get(key);

            //columns loop

            int cellcount =0;
            for(Object obj: objects) {

                HSSFCell cell = row.createCell(cellcount);
                cell.setCellValue(obj.toString());
                cellcount++;
            }
            rowCount++;
            }

        try {

           // Following path is given where the workbook will get created

            FileOutputStream fileOutputStream = new FileOutputStream(new File("C:\\Users\\Gaurav\\Desktop\\Selenium_TC\\Selenium_practicals\\gaurav.xls"));
            hssfWorkbook.write(fileOutputStream);
            fileOutputStream.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

}

Actions In Selenium using Selenium WebDriver

 

There are different kind of actions can be perform in selenium webdriver. So every action needs to build and then it will be ready to perform. The actions will be responsible for every action performed in browser. In most of the cases we will use .Build() and .perform() after initialization of every new action.

So following are the actions that can be perform:

1. To build multiple actions in a order and then return the result of that particular action:

new Actions(driver).Build();

2. To execute currently built action and no return:

new Actions(driver).Perform();

3. To perform particular action on the browser to be perform:

new Actions(driver).Build().Perform(); 

It is better to specify by locator using findelement.

4. To click the mouse on last know mouse co-ordinates and return the actions:

new Actions(driver).Click();

5. To click the mouse on specified element and return the actions:

new Actions(driver).Click(draggable);

6. To click and hold the mouse button on last known mouse co-ordinates and return the actions:

new Actions(driver).ClickAndHold();

7. To drag-and -drop from one element to another and that return the actions:

new Actions().DragAndDrop(draggable, droppable);

8. To drag-and-drop to specified offset on one element and that returns the actions:

new Actions(driver).DragAndDropToOffset(draggable, 25, 35);

9. To move the mouse over specified element and that return the actions:

new Actions(driver).MoveToElement(draggable);

10. To move the mouse to the specified offset of the top left corner of the specified element and that returns the actions:

new Actions(driver).MovetoElement(draggable, 25, 35);

11. To release the mouse button at the last known mouse co-oridinates and that return the actions:

new Actions(driver).Release();

12 To release the mouse button on specified element and that returns the actions:

new Actions(driver).Release(draggable);

13. To send the multiple keystrokes in specific order to the browser and that return the actions:

new Actions(driver). SendKeys(Keys.Alt) 

In this above syntax key parameter is string.

14. To send sequence of key strokes to the specified element in the browser and that return the actions:

new Actions(driver) .SendKeys(draggable, Keys.Alt);

In this above syntax key parameter as string.

15. To send a modifier key down message to the browser and that return the actions:

new Actions(driver).KeyDown(Keys.Alt)

16. To send a modifier key down message to the specified element in the browser and that return the actions:

 new Actions(driver).KeyDown(draggable, Key.Alt);

17. To send a modifier key up message to the browser and that return the actions:

new Actions(driver).KeyUp(Keys.Alt)

18. To send a modifier key up message to the specified element in the browser and that return the actions:

 new Actions(driver).KeyUp(draggable, Key.Alt);  

 

Now we will see how different actions we can use in selenium.

 

public class demoActions {

        WebDriver driver;

        @Before

        public void setup() {

            driver = new FirefoxDriver();

            driver.get("http://newtours.demoaut.com/");

            driver.manage().window().maximize();

        }

   /* @After

    public void tearDown(){

        wd.quit();

    }*/

        @Test

        public void shouldDoUnitTesting() {

            WebElement uName = driver.findElement(By.name("userName"));

            WebElement pwd = driver.findElement(By.name("password"));

            WebElement login = driver.findElement(By.name("login"));

            Actions actions = new Actions(driver);

            Action build = actions.moveToElement(uName)

                    .click(uName)

                    .keyDown(Keys.SHIFT)

                    .sendKeys("s")

                    .keyUp(Keys.SHIFT)

                    .sendKeys("oftedge100")

                    .keyDown(Keys.CONTROL)

                    .sendKeys("a" +"c")

                    .keyUp(Keys.CONTROL)

                    .click(pwd)

                    .keyDown(Keys.CONTROL)

                    .sendKeys("v")

                    .keyUp(Keys.CONTROL)

                    .click(login)

                    .build();

                    build.perform();

        }

    }

Thanks to Savita Shinde....