No matter what the project / product is the ultimate aim of the tester is to validate the data that is appearing on the final business layer .
For example
Here in the below code a String array is used to hold the SQL queries. When you have such hundreds of queries you can put them in an excel file and iterate over the excel column to read those queries.
Java code to refresh SQL queries from the spread sheet one by one.
I am using Apache POI XSSF to read the spreadsheet.
The two important methods here are
connection method - this returns a connection object
core method - this returns an String array for every SQL query executed.
Java code to refresh SQL
package TestNG;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Array;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFComment;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class DBHelper01 {
private static XSSFCell column1;
private static XSSFCell column2;
private static String value;
private static String key;
private static String VALUE;
private static int totalRows;
public static Connection connection;
private static String rsSQL;
private static DriverManager dmanager;
private static ArrayList<String> rsarray;
// Main method is used for testing
public static void main(String[] args) {
}
public static String getQueries(String KEY) {
File file = new File("C://Test//sqlqueries.xlsx");
HashMap<String, String> sqlmap = new HashMap<String, String>();
try {
FileInputStream is = new FileInputStream(file);
XSSFWorkbook workbook = new XSSFWorkbook(is);
XSSFSheet sheet = workbook.getSheetAt(0);
totalRows = (sheet.getLastRowNum() - sheet.getFirstRowNum());
// System.out.println("Total Rows " + totalRows);
for (int i = 2; i <= totalRows; i++) { // excluding the header
// starting from 1
XSSFRow row = sheet.getRow(i);
column1 = row.getCell(0);
column2 = row.getCell(1);
if (column1 == null || column2 == null) {
} else {
if (column1 != null) {
// System.out.println(column1.getStringCellValue());
key = column1.getStringCellValue();
}
if (column2 != null) {
// System.out.println(column2.getStringCellValue());
value = column2.getStringCellValue();
}
sqlmap.put(key, value);
}
}
VALUE = sqlmap.get(KEY);
workbook.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return VALUE;
}
public static Connection connection(DriverManager dmanager, String URL, String USER_NAME, String PASSWORD) {
try {
Class.forName("com.ggg.arsys.jdbc.core.Driver");
connection = DriverManager.getConnection(URL, USER_NAME, PASSWORD);
} catch (SQLException | ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return connection;
}
public static ArrayList<String> core(String KEY) {
// Arrays.asList(a)
String sqlquery = DBHelper01.getQueries(KEY);
try {
ResultSet rs = connection.createStatement().executeQuery(sqlquery);
int colCount = rs.getMetaData().getColumnCount();
rsarray = new ArrayList<String>();
while (rs.next()) {
for (int j = 1; j <= colCount; j++) {
rsSQL = rs.getString(j);
rsarray.add(rsSQL);
}
}
if (rsarray.size() == 0) {
System.out.println("No data for query");
} else if (rsarray.size() > 0) {
System.out.println(rsarray);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return rsarray;
}
}
Test NG Class for the DBHelper
package TestNG;
import org.testng.annotations.Test;
import org.testng.annotations.BeforeMethod;
import org.testng.annotations.AfterMethod;
import org.testng.annotations.DataProvider;
import org.testng.annotations.BeforeClass;
import org.testng.annotations.BeforeGroups;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.hamcrest.Matchers;
import org.junit.Assert;
import org.junit.Ignore;
import org.testng.annotations.AfterClass;
import org.testng.annotations.AfterGroups;
import org.testng.annotations.BeforeTest;
import org.testng.annotations.AfterTest;
import org.testng.annotations.BeforeSuite;
import org.testng.annotations.AfterSuite;
public class DBHelperTestCase {
private static DriverManager dmanager;
@Test(dataProvider = "dp")
public void f(Integer n, String s) {
}
@BeforeMethod
public void beforeMethod() {
}
@AfterMethod
public void afterMethod() {
}
@DataProvider
public Object[][] dp() {
return new Object[][] { new Object[] { 1, "a" }, new Object[] { 2, "b" }, };
}
@BeforeClass(alwaysRun=true)
public void beforeClass() {
DBHelper01.connection(dmanager, "jdbc:arserver://vl-pun-atm-qa12:0;mode=new", "Demo", "bmcAdm1n");
DBHelper02.connection(dmanager, "jdbc:oracle:thin:@vl-pun-atm-qa12:1521:ORA112DB", "system", "bmcAdm1n");
}
@AfterClass(enabled = false)
public void afterClass() {
try {
DBHelper01.connection.close();
System.out.println("db connection closed");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
@BeforeTest
public void beforeTest() {
}
@AfterTest
public void afterTest() {
}
@BeforeSuite
public void beforeSuite() {
}
@AfterSuite
public void afterSuite() {
}
@BeforeGroups("sanity-test")
public void t() {
System.out.println("sanity-test");
}
@AfterGroups("regression")
public void tt() {
System.out.println("regression");
}
@Test(groups = "sanity", enabled = false)
public void testCase01() {
ArrayList<String> t1 = DBHelper01.core("Test Case 01");
ArrayList<String> t2 = DBHelper02.core("Test Case 01");
Assert.assertArrayEquals(t1.toArray(), t2.toArray());
}
@Test(groups = "regression", enabled = false)
public void testCase02() {
DBHelper01.core("Test Case 02");
}
@Test(groups = "sanity", enabled = true) // enable =false = @ignore
public void testCase03() {
DBHelper01.core("Test Case 03");
}
@Test(groups = "regression", enabled = true)
public void testCase04() {
DBHelper01.core("Test Case 04");
}
@Test(groups = "regression", enabled = true)
public void testCase05() {
DBHelper01.core("Test Case 05");
}
}
<suite name="JDBC Test Suite">
<test name="dbtest">
<groups>
<run>
<include name="core" />
<include name="regression" />
</run>
</groups>
<classes>
<class name="TestNG.DBHelperTestCase" />
<methods>
</methods>
</classes>
</test>
</suite>
Results
No comments:
Post a Comment