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

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?

Answer

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.