Connect to DataBase in Java and Selenium Implementation

For initial understanding on database and SQL queries,
refer MySQL Basics

To automate database testing, we will use selenium with java language.

JDBC (Java DataBase Connectivity) is an API (using Java language) to connect and play with database with SQL queries.




In this post, let's understand how we can connect with database using Java, and how we can implement in selenium automation testing.


--------------------------------------------------------
JDBC Driver : To connect to the database (Relational Database)

JDBC DriverManager : DriverManager is a class, manages list of all database drivers. it connects java applications(java code) to a proper JDBC driver.

Connection: Creates channel to database.

Statement: These objects are to send SQL queries to database.

ResultSet: Object to hold retrieved results (set of Rows) from SQL queries.
---------------------------------------------------------

Jar files required for DataBase connectivity:
We need mysql-connector-java-X.X.XX-bin.jar file for the JDBC with MySQL.
Download and install from  http://dev.mysql.com/downloads/connector/j (Jar file will be located under C:\Program Files (x86)\MySQL\MySQL Connector J )

Note: add the jar file to your eclipse project -> Java build path

Load the DB Server:

Class.forName("com.mysql.jdbc.Driver"); //For MySQL
Class.forName ("oracle.jdbc.driver.OracleDriver"); //For Oracle
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); //For MS SQL Server

Setup connection with DB:

Connection con = DriverManager.getConnection(
                         "jdbc:myDriver:myDatabase",username,password);
//For MySQL: myDriver = mysql
//For SQL Server: myDriver = sqlserver
//For Oracle: myDriver = oracle
//myDatabase: database path: e.g - //<systemipadd/Servername>/dataBaseName          

Rest of code and SQL queries are almost same for all kind of databases
Let's see MySQL database connectivity example:

public class DataBaseConn {
 private WebDriver driver;
 private Connection con = null;
 private Statement stmt = null;
 String dataBaseName = "student";
 String driver_DBPath = "jdbc:mysql://localhost:3306/";
 String DB_username = "root";
 String DB_password = "root";
 String Query;
 ResultSet res;
 
 @BeforeTest
 public void DBConnection() throws Exception
 {
  try{
   Class.forName("com.mysql.jdbc.Driver").newInstance(); //For MySQL
      Connection con = DriverManager.getConnection(
   driver_DBPath + dataBaseName ,DB_username,DB_password );
      stmt = con.createStatement();
  }catch(Exception e){System.out.println(e.getMessage());}
 }
 
 @Test
 public void tc_1() throws Exception
 {
  try{
  Query = "Select * from stdetails";
  res = stmt.executeQuery(Query);
  
  driver = new FirefoxDriver();
     driver.manage().timeouts().implicitlyWait(20, TimeUnit.SECONDS);
     driver.manage().window().maximize();
     driver.get("http://www.qavalidation.com/p/demo.html");
     
     int i=1; //to name the screenshot file
     while(res.next()) //move to the next row in result set, row by row
     {
      String FullName = res.getString("FullName");
      String Email = res.getString("Email");
      int Telephone = res.getInt("Telephone");
      String Gender = res.getString("Gender");
      
      WebElement Name = driver.findElement(By.id("username"));
       Name.clear();Name.sendKeys(FullName);
      WebElement EmailAdd = driver.findElement(By.id("email"));
              EmailAdd.clear();EmailAdd.sendKeys(Email);
      WebElement CellPh = driver.findElement(By.id("tel"));
       CellPh.clear();CellPh.sendKeys(Integer.toString(Telephone));
 
      Select sel = new Select(driver.findElement(By.name("sgender")));
      sel.selectByValue(Gender);
      
      i++;
      GetScreenShot(i);
     }
     
  }catch(Exception e){System.out.println(e.getMessage());}
 }
 
 @AfterTest
 public void tear()
 {
  con = null;
 }
 
 public void GetScreenShot(int i) throws IOException
 {
  TakesScreenshot ts = (TakesScreenshot)driver;
  File f = ts.getScreenshotAs(OutputType.FILE);
  FileUtils.copyFile(f, new File(i+".jpg"));
 }
 }

Now refresh your project and observe the 2 screenshots generated, 1.jpg and 2.jpg to validate if we have same data as the database.