How to handle IndexOutofBounException if the SQL query does not return any results in Selenium?

user_11280615 0
Viewed 43

In my automation script, one data has to be fetched from SQL in order to fill a form for certain conditions. So, I run the SQL query, and store the first data (from the 0th position) in a String like this:

String name = (String) DBUtil.executeQuery("<QUERY>", new Object[0]).get(0).get("<COLUMN_Name>");

I am doing this one class where the test scripts is written. Following is the executeQuery method written in another class DBUtil, and it works fine for all the queries.

The issue that I am facing is when the query does not fetch any results, and nothing gets stored in the String name. In that case, upon running the test script, it throws IndexOutofBounException as as no result is fetched. Can anyone suggest on how to handle this part?

selenium exception sql java
1 Answers

It depends where the exception occurs. You can try adjust the query to have always a column in the result using SELECT AS, see different result of those two queries:

select newid();
select newid() as newid;

Another approach is using try/catch block:

String name = null;

try {
   name = (String) DBUtil.executeQuery("<QUERY>", new Object[0]).get(0).get("<COLUMN_Name>");
}
catch (IndexOutofBounException indexOutofBounException) {
}

Update:

Or you can leave DBUtil.executeQuery and use this:

public static List<String> getQueryResults(Connection connection, String query, String key) {
    List<String> results = new ArrayList<String>();
    try {
        Statement statement = connection.createStatement();
        statement.setQueryTimeout(60);
        ResultSet result = statement.executeQuery(query);
        while (result.next()) {
            results.add(result.getString(key));
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return results;
}

Usage example:

public static Boolean databaseExists(Connection connection, String dbName) {
    List<String> dbNames = getQueryResults(connection, "SELECT name FROM sys.databases", "name");
    if (dbNames.contains(dbName)) {
        return true;
    }
    else {
        return false;
    }
}

or similar:

public static Map<String, String> mapQueryResult(Connection connection, String query, String key, String value) {
    Map<String, String> map = new HashMap<String, String>();
    ResultSet result = null;
    try {
        result = connection.createStatement().executeQuery(query);
        while (result.next()) {
            map.put(result.getString(key), result.getString(value));
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return map;
}

Usage example:

public static String getBackupLogicalName(Connection connection, File backupFile) {
    String origin = null;
    String query = "RESTORE FILELISTONLY FROM DISK = N'" + backupFile.getAbsolutePath() + "' WITH  NOUNLOAD;";
    Map<String, String> results = mapQueryResult(connection, query, "FileGroupName", "LogicalName");
    if (results.size() > 0) {
        origin = results.get("PRIMARY");
    }
    return origin;
}
user_90149570
Related