How to fetch all data without the "out of range" SQL Exception?

Question

I'm extracting data from a database, and I'm getting com.microsoft.sqlserver.jdbc.SQLServerException: The index 15 is out of range. during RunTime.

My connection class is executed as the following:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.Statement;

public class SQLConnection {
    
    private String type = "jdbc:sqlserver:";
    private String url = "//thinkThisIsAnActualAddress;";
    private String database = "database=SomeDatabase;";
    private String user = "user=NotAUser;";
    private String password = "password=NotAPass;";
    private String connectionURL;
    private final String query = "SELECT FIELD1, FIELD2, FIELD3, FIELD4, FIELD4, FIELD5"
    + " FIELD6, FIELD7, FIELD8, FIELD9, FIELD10, FIELD11,"
    + " FIELD12, FIELD13, FIELD14"
    + " FROM TABLE_OWNER.TABLE_FIELDS_DTL"
    + " LEFT JOIN TABLE_OWNER2.TABLE_FIELDS_DTL2"
    + " ON TABLE_OWNER.TABLE_FIELDS_DTL.SOME_FOREIGN_KEY_SK = TABLE_OWNER2.TABLE_FIELDS_DTL.SOME_FOREIGN_KEY_SK"
    + " WHERE THIS_FIELD = 'X'";
    ResultSet resultSet = null;

    public String getURL(){
        makeString();
        return connectionURL;
    }

    public void makeString(){
        connectionURL = type + url + database + user + password;
    }

    public void connect(){
       
        // Load SQL Server JDBC driver and establish connection.
        System.out.print("Dialing the Matrix... ");
        try (Connection connection = DriverManager.getConnection(getURL());
            Statement statement = connection.createStatement();) {
             
            // Connection message
            System.out.println("You are in, Neo...");    
            
            resultSet = statement.executeQuery(query);

            int i = 1;
            while(resultSet.next()){
                
                System.out.println(resultSet.getString(i) + " ");
                i++;   
            }

        } catch (SQLException e) {
            System.out.println();
            e.printStackTrace();
        }
    }
}

While I understand why it's happening, in this while loop:

int i = 1;
while(resultSet.next()){
    System.out.println(resultSet.getString(i) + " ");
    i++;   
}

I don't quite understand how to fetch the data without it, since there are 14 fields, and an arbitrary number of rows in the database that matches that query, depending on the day. A simple "fix" would be swapping the line while(resultSet.next()) to while(resultSet.next() && i < 14), that would fix the exception, but not achieve the result I'm looking for, the ability to iterate through all rows that matches the query.

What I'm missing here?

I did not understand what you exactly ask, is it related to SQL or related to the loop. if related to SQL result, it is coming right and you save it in resultSet and able to iterate it. So you may ask for the loop, if it is the case, consider checking the link https://stackoverflow.com/questions/85190/how-does-the-java-for-each-loop-work — Useme Alehosaini, Dec 14 '20 at 23:44
I thought it was related to SQL, I suppose I'm confused on how the data structure is behaving under that while loop. I was checking the documentation for it, and it seems that this would be the correct answer for it: https://docs.oracle.com/javase/tutorial/jdbc/basics/retrieving.html. I'll check on this right now. — Beauregard Lionett, Dec 14 '20 at 23:49
Look at the output you are printing. You are getting only the first field from the first record, only the second field from the second record and so on. — tgdavies, Dec 14 '20 at 23:51
I was just checking the database records, @tgdavies, and you are correct. My loop is totally crazy. I suppose I gotta learn how to do this thing properly (working long hours for COVID vaccine project and my brain is melting). Thanks! — Beauregard Lionett, Dec 14 '20 at 23:55
It's coming @tgdavies, the first distributed to my company is happening tomorrow, if I'm not mistaken. :D — Beauregard Lionett, Dec 15 '20 at 00:16
I don' know you want to achieve here, but the exception is clear. You have only 14 columns, and you increase `i` by 1 each time in the loop, when `i>14`, you are retrieving the 15th column in a row which is certainly an exception. — haoyu wang, Dec 15 '20 at 03:33

score 1 · Accepted Answer · answered Dec 15 '20 at 02:13

If you want to read every row, you use a while (rs.next()) loop, which will fire for every row.

If, for an individual row, you want to read every column, you'd use a .getString(col) or .getInt(col) or .getObject(col, LocalDateTime.class) or getWhateverYouWant method, passing for the col arg either a 1-based index, or, the name of the column you want to fetch.

If you want both, well, you'd have to grab the metadata to know how many columns there are, or, you just check your own query and hardcode the number. Thus, for your specific code, if you want to print every column from every row:

while (rs.next()) {
    for (int i = 1; i <= 14; i++) System.out.println(rs.getString(i));
}

If the query returns 4 rows, this will print 4*14 = 56 lines of text.

Haha, that's exactly what I've coded yesterday after I figured out what was happening. Thanks anyway for answering it here, you offer much more depth and I really appreciate it, so I'm accepting it as the answer. Thanks a bunch! — Beauregard Lionett, Dec 15 '20 at 16:34

How to fetch all data without the "out of range" SQL Exception?

1 Answers1