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();
}
}