Database connection and selenium with examples

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