How to use jdbc ResultSet of a SQL 'select *' query to create and populate a 2 dimensional-CodePudding

I am trying to select the entire contents of a MSSQL Server table populated with int values to serve as a 'truth table'. The goal is to select the entire SQL table, and populate it in Java as a 2 dimensional array. Here is a token source table I am working with (with the table name SANITIZED): SQLServerTable I may have a variable number of rows, but the number of columns will be fixed (so the array will not be a jagged array). The table is seeded with token values to represent row-then-column for ease of troubleshooting (13 being row 1, column 3, 24 being row 2, column 4...)

Here is the code I have tried (SANITIZED for anonymity):

public class SANITIZED {

private static String url = "jdbc:sqlserver://SANITIZED:SANITIZED;DatabaseName=SANITIZED;encrypt=true;trustServerCertificate=true";
    
public static void main(String[] args) {

int numRows = 0;
                
try {
Connection conn = DriverManager.getConnection(url, "SANITIZED", "SANITIZED");
//  Establish Connection Object
Statement statement = conn.createStatement();
//  Create a SQL statement object to send to the database
ResultSet resultSet = statement.executeQuery("select count (*) from SANITIZED");  //  Execute the statement object
//  Process the result
if (resultSet.next()) { // just in case
numRows = resultSet.getInt(1); // note that indexes are one-based
}
} catch (SQLException e) {
e.printStackTrace();
}

int[][] truthTable = new int[numRows][27];
        
try {
Connection conn = DriverManager.getConnection(url, "SANITIZED", "SANITIZED");  //  Establish Connection Object
Statement statement = conn.createStatement();
//  Create a SQL statement object to send to the database
ResultSet resultSet = statement.executeQuery("select * from SANITIZED");  //  Execute the statement object
//  Process the result
while(resultSet.next()) {

System.out.println("resultSet: "   (resultSet.getInt(1)));
System.out.println("resultSet: "   (resultSet.getInt(2)));
System.out.println("resultSet: "   (resultSet.getInt(3)));
System.out.println("resultSet: "   (resultSet.getInt(4)));
            
truthTable[numRows - 2][0] = resultSet.getInt(1);
truthTable[numRows - 2][1] = resultSet.getInt(2);
truthTable[numRows - 2][2] = resultSet.getInt(3);
truthTable[numRows - 2][3] = resultSet.getInt(4);
}

The output from the println statements is: resultSet: 11 resultSet: 12 resultSet: 13 resultSet: 14 resultSet: 21 resultSet: 22 resultSet: 23 resultSet: 24

But when I try to populate a 2 dimensional array, the Eclipse debugger is showing the SECOND row of the SQL table populating into the FIRST row of the array despite the columnIndex of the getInt() method being passed as 1. This is true even if I comment the println statements out. Here is the debugger output: truthTable (id=24) [0] (id=27) [0] 21 [1] 22 [2] 23 [3] 24 [4] 0

The code above hard codes the row of the array ([numRows - 2]) for simplicity - but ideally, the goal is to iterate over the ResultSet as necessary in hopes that a single SQL 'select *' query that returns only int values from the DB (having a specific number of columns but a variable number of rows) can be used to create and populate an array of a fixed number of columns with a variable number of rows. My thanks to the community for any assistance in understanding how this may be accomplished.

The println statements for a given index prints values from the first row of the SQL table.
The attempt to use the same indexes to populate an array (in the absence of the println statements) populates the values from the second row of the array. Why would it print the first DB row, but store the second DB row when the same index is being used?

CodePudding user response：

Try the following. You may also have introduced an issue with resultSet.getIndex() indexing from 1 rather that 0.

    List<int[]> orderedTruthTableList = new ArrayList<>();
    try {
        Connection conn = DriverManager.getConnection(url, "SANITIZED", "SANITIZED"); // Establish Connection Object
        Statement statement = conn.createStatement();
        // Create a SQL statement object to send to the database
        ResultSet resultSet = statement.executeQuery("select * from SANITIZED"); // Execute the statement object

        // Process the result
        while (resultSet.next()) {

            orderedTruthTableList.add(new int[] {resultSet.getInt(1), resultSet.getInt(2), resultSet.getInt(3), resultSet.getInt(4)});

        }
    } finally {

    }

    int[][] truthTable = orderedTruthTableList.toArray(new int[0][0]);