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.

What is the purpose of this unit test (note: it's actually an _integration_ test, because it is using a database)? I don't see you making any assertions. What is the test supposed to be checking? — Tim Biegeleisen, Oct 08 '19 at 10:03
the purpose is basically to check the data in oracle DB. The String val will return some data which I need to assertEqual but since, dbResultSetSalary.next() returns false sometimes for me because of unavailability of data in db. Still it pass. (Need to handle this) I have updated the code. — avidCoder, Oct 08 '19 at 10:06

score 2 · Accepted Answer · answered Oct 08 '19 at 10:11

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.

TestNG has `org.testng.SkipException` which can be used for this purpose (instead of general Exception) — bhusak, Oct 08 '19 at 13:10

NikNik · Answer 2 · 2019-10-08T10:08:12.017

1

You can use hasNext():

if(!dbResultSetSalary.hasNext())
  throw new MyException("You are lying")

and if you want to check the value, then:

if(val==null or whatever)
  throw new MyException("You are lying for the second time")

edited Oct 08 '19 at 10:08

answered Oct 08 '19 at 10:02

NikNik

2,191
2
15
34

But, this If statement will execute 1 time. Since, it has multiples rows , so I am using while loop for that. Can we do this using while loop? – avidCoder Oct 08 '19 at 10:04
hasNext() is used to check the resultSet, not the value. For value check, I will update my answer and this has nothing to do with the type of database – NikNik Oct 08 '19 at 10:06
and yes, you can use it more than once – NikNik Oct 08 '19 at 10:09
But what if dbResultSetSalary.hasNext() contains multiple rows of data? Since, in first hit it will fetch the 1st row and then it will keep on going for while loop. help me to understand here. – avidCoder Oct 08 '19 at 10:10
hasNext() is true if there are more "rows" otherwise it's false – NikNik Oct 08 '19 at 10:11

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

2 Answers2