Thursday 24 October 2013

Database Connection in Selenium Webdriver | MySQL

This illustrates how to connect MySQL database with Selenium using Java bindings.  Make sure mysql-connector-java-5.x.x-bin.jar in your buildpath.

Configure MySQL

1| Download an install MySQL.
2| Create a database and table. e.g.,
create database one;
create table pets (name VARCHAR(20), owner VARCHAR(20), species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
3| Follow up whatever queries intended
4| Now, try to connect the schema with java via selenium webdriver for retrieving the table values.


private String dbvalue;
private String dbUrl = "jdbc:mysql://localhost:3306/one"; //Here, one is the schema/db
private String username="root";
private String password="yourpassword";
private String dbClass = "com.mysql.jdbc.Driver";

@Test
public void connectdb() throws Exception {  
driver.get("www.xyz.com");
      
try {
Class.forName(dbClass);
Connection con = DriverManager.getConnection (dbUrl,username,password);
Statement stmt = con.createStatement();

//insert values into the table | Get values from the already created schema/db
String insertvalues1 = "Insert into pets values('dashund', 'Sams', 'DOG', 'M' ,'2012-05-12', '2012-05-12');";
String insertvalues2 = "Insert into pets values('brownie', 'Sams', 'CAT', 'F' ,'2013-07-22', '0000-00-00');";
String insertvalues3 = "Insert into pets values('parrot', 'Sams', 'BIRD', 'F' ,'2011-12-02', '0000-00-00');";
stmt.executeUpdate(insertvalues1);
stmt.executeUpdate(insertvalues2);
stmt.executeUpdate(insertvalues3);

String query = "select * from pets";
System.out.println(query);
ResultSet rst = stmt.executeQuery(query);

//In a loop
while (rst.next()) {
dbvalue =rst.getString(1);
System.out.println(dbvalue);
driver.findElement(By.id(Value)).sendKeys(dbvalue);
driver.findElement(By.id(Value)).submit();
        driver.get("www.xyz.com");
}

con.close();

} catch(ClassNotFoundException e) {
e.printStackTrace();
} catch(SQLException e) {
e.printStackTrace();
}

2 comments:

  1. Hi Prashanth,

    I see that you use Selenium WebDriver and MySQL. However, if you'd like to benefit from convenience of Selenium IDE, you may be interested in SeLite. It enhances Selenese and it helps the tests to use SQLite. See https://code.google.com/p/selite/wiki/ProjectHome

    ReplyDelete
  2. really well explained blog about database connection in selenium clear explanation got after reading this blog
    selenium training institute in chennai Selenium Training in Chennai

    ReplyDelete