How to fail the testng script if while(resultset.next()) returns false?

Question

Sometimes,

dbResultSetSalary.next()

doesn't return any value for my query, which is false. In that case, how to fail the testng script?

Below is my code :-

import java.sql.ResultSet;
import org.testng.annotations.Test;

public class Test21 {

    static ResultSet dbResultSetEmployeeID;
    static ResultSet dbResultSetSalary;

    @Test
    public void dbTest() throws Exception {
        DataBaseUtil conn = new DataBaseUtil();
        conn.EstablishConnection("ORACLE");
        dbResultSetEmployeeID = conn.executeQuery("SELECT * FROM EMPLOYEE WHERE EMPLOYEE_NAME = 'StackOverFlow'");
            while(dbResultSetEmployeeID.next()) {
                String id = dbResultSetEmployeeID.getString("EMP_ID");
                dbResultSetSalary = conn.executeQuery("SELECT * FROM SALARY WHERE EMP_ID = '"+id+"'");
                while(dbResultSetSalary.next()) {
                    String val = dbResultSetSalary.getString("VAL");
                    System.out.println(val);
                    assertEquals("23400", val)
                }
            }
        }
}

I am new to db connection using oracle. need some insight on this.

Answer 1

I suggest maintaining a single Boolean variable which keeps track of whether or not the test passed. It would be initialized to false, and only would be set true upon completion of a successful query with the correct value.

@Test
public void dbTest() throws Exception {
    boolean success = false; // added here

    DataBaseUtil conn = new DataBaseUtil();
    conn.EstablishConnection("ORACLE");
    String sqlName = "SELECT * FROM EMPLOYEE WHERE EMPLOYEE_NAME = 'StackOverFlow'";
    dbResultSetEmployeeID = conn.executeQuery(sqlName);
    while (dbResultSetEmployeeID.next()) {
        String id = dbResultSetEmployeeID.getString("EMP_ID");
        String sqlSalary = "SELECT * FROM SALARY WHERE EMP_ID = '" + id + "'";
        dbResultSetSalary = conn.executeQuery(sqlSalary);
        if (dbResultSetSalary.next()) {
            String val = dbResultSetSalary.getString("VAL");
            System.out.println(val);
            assertEquals("23400", val);
            success = true;
        }
    }

    // JUnit will interpret an exception as a test failure

    if (!success) {
        throw new Exception("Test failed.");
    }
}

Notes:

You are actually running an integration test here, not a unit test, because the result of your test does not just depend on a unit of code functionality, it also depends on whether your actual database be working.

One of your SQL queries is built using string concatenation. This is generally prone to SQL injection, but since you are controlling what values go in there, there is in theory no risk of SQL injection.