Tuesday, 27 February 2018

Database Operations with Selenium Wrapper Automation

When you need to connect with database and execute query then you have to do first connect with your database then execute query.

 Suppose you have scenario in that you need to execute lots of query and get result and verfy the result. So, for that every time you need to open connection, execute query and close connection. It is not good approach for any framework.

 I have created one class 'DatabaseOperations' for different operation on database. you can use this class into your automation framework and use all methods of it with object of this class.


  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import org.openqa.selenium.WebDriver;

public class DatabaseOperations 
{
 
 public Connection mConnectToServerDatabase(WebDriver driver, String vDatabaseName, String vUsername, String vPassword)
 {
  Connection sqlConnection = null;
  try
  {
   // Make database connection
   Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
  }
  catch (ClassNotFoundException E)
  {
   System.out.println("Error-SQL class not found.");
   E.printStackTrace();
  }
  
  try
  {
   // Get Database connection
   String vConnetionString = "jdbc:sqlserver://server ip\\QA:1444;databaseName";   
   sqlConnection = DriverManager.getConnection(vConnetionString, vUsername, vPassword);   
  }
  catch (Exception E)
  {
   System.out.println("Error in connection with database.");
   E.printStackTrace();
  }
  
  return sqlConnection;  
 }
 
 public Connection mConnectToLocalDatabase(WebDriver driver, String vDatabaseName, String vUsername, String vPassword)
 {
  Connection sqlConnection = null;
  try
  {
   // Make database connection
   Class.forName("com.mysql.jdbc.Driver");
  }
  catch (ClassNotFoundException E)
  {
   System.out.println("Error-SQL class not found.");
   E.printStackTrace();
  }
  
  try
  {
   // Get Database connection
   String vConnetionString = "jdbc:mysql://localhost:3306/user";   
   sqlConnection = DriverManager.getConnection(vConnetionString, vUsername, vPassword);   
  }
  catch (Exception E)
  {
   System.out.println("Error in connection with database.");
   E.printStackTrace();
  }
  
  return sqlConnection; 
 }
 
 public String[][] mExecuteQueryOnDatabase(WebDriver driver, Connection sqlConnection, String vQuery) throws SQLException
 {  
  int vRowCount, vColumnCount;  
 
  // Execute Query
  Statement st = sqlConnection.createStatement();
  ResultSet rs = st.executeQuery(vQuery);  
  ResultSetMetaData rsmd = rs.getMetaData();
     
  // Get row and column count
  vColumnCount = rsmd.getColumnCount();
  rs.last();
  vRowCount = rs.getRow();   
  rs.first();
        
  System.out.println("Number of Column in result : " + vColumnCount);
  System.out.println("Number of Row in result : " + vRowCount);      
  
  String[][] vResult = new String[vRowCount][vColumnCount];
  
  // Convert result set into String array
  int i=0;
  while(rs.next())
  {
   for(int j=0; j<vColumnCount; j++)
   {
    vResult[i][j] = rs.getString(j+1);
   }
   i++;
  }
   
  try
  {
   sqlConnection.close();
  }
  catch (SQLException E) 
  {   
   E.printStackTrace();
  }
  
  return vResult;
 }
 
 public String[][] mConnectDatabaseAndExecuteQuery(WebDriver driver, String vDatabaseType, String vDatabaseName, String vUsername, String vPassword, String vQuery) throws SQLException
 {
  Connection sqlConnection = null;
  
  if(vDatabaseType.equalsIgnoreCase("local"))
  {
   sqlConnection = mConnectToLocalDatabase(driver, vDatabaseName, vUsername, vPassword);
  }
  else if(vDatabaseType.equalsIgnoreCase("server"))
  {
   sqlConnection = mConnectToServerDatabase(driver, vDatabaseName, vUsername, vPassword);
  }
  
  String[][] vResult = mExecuteQueryOnDatabase(driver, sqlConnection, vQuery);
  
  return vResult;
 }
   
}


Please add comment if you have any question.

at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Popular