For loop and DB records

[Eleeist]

Hi,

I am trying get all the records form "commands" table and assign particular field from each row to a array. Here is the code that I have so far:

Connection connection = connectToDatabase();
        
        PreparedStatement prest;
        String query = "SELECT * FROM commands WHERE category = ?";
        prest = connection.prepareStatement(query);
        prest.setString(1,category);
        
        ResultSet queryResult = prest.executeQuery();
        
        while (queryResult.next()) {
            
            for (int i = 0; i; i++) {
                ...
            }
        
        }

The problem is for() loop. I don't know how many times I will have to run the loop, as I don't know how many records there will be in the database table. Therefore I cannot set "i" to any fixed value.

So how can I run the loop only as many times as necessary to get all the values from the database?

Thanks

[eRaaaa]

You don`t know how many columns your own 'commands' table has? I think you misunderstand the resultset or?!

as I don't know how many records there will be in the database table

that you dont need to know?!
http://docs.oracle.com/javase/6/docs...ResultSet.html - > Moves the cursor forward one row from its current position. [..]false if there are no more rows -
so your while loop is running until there are no more rows!
You have to call one of the get-methods ....?!

[Eleeist]

Relax and don't shout :).

I know what ResultSet does. I am able to output the rows to the console. But I don't know how to add them to an array.

The way I know to easily insert values into an array is to use a for loop eg.

int[] myArray;
myArray = new int[10];

for (int i = 0; i <=10; i++) {
    myArray[i] = i;
}

In this example I KNOW that there will be 10 numbers in total inserted into an array, so I can set variable i = 10.

But when I get data from the database I DO NOT know how many results there will be, so I cannot set variable i to any number.

Maybe it's just me, but I cannot think of a method to accomplish this task.

[Shoss]

Take a look at the api eRaaaa linked to. If you have to use an array, then you can use the methods "last()", "getCount()", and "first()" to go to the last row, grab it's row number, and go back to the beginning. There is no direct way to get the count that i know of.

However, if you can switch to using an ArrayList, it'd be much simpler to just use next() with the while loop like eRaaaa was suggesting. Next grabs the next row, but returns false if there are no more rows.

[Eleeist]

Thanks Shoss.

I managed to count the rows in the database, but I get the ArrayIndexOutOfBoundException when I try to run the program that gets the data, puts it into array and then lists the items of the array.

Here I get the data from the database:

 queryResult.last();
        int count = queryResult.getRow();
        queryResult.beforeFirst();
        
        String[] myArray;
        myArray = new String[count];
        int arrayLenght = myArray.length;
        
        for (int i = 0; i <= arrayLenght; i++) {
            myArray[i] = queryResult.getString("command");
        }
        
        return myArray;

And here I display the array:

Database getCommand = new Database();
        
        String[] someoutput = getCommand.getCommands("basic");
       
        System.out.println(someoutput[0]);

(currently there is only one row in the table, so I use someoutput[0] instead of for() loop to list them all).

[Shoss]

Where does the error message say the error happened? Try debugging your code by adding some println statements around where the error is happening. That way you can see if values are what you expected them to be and how many times a loop is running.

[Eleeist]

I solved the problem (just silly mistakes :)). Thansk for all your help!

[Tolls]

Take a look at the api eRaaaa linked to. If you have to use an array, then you can use the methods "last()", "getCount()", and "first()" to go to the last row, grab it's row number, and go back to the beginning. There is no direct way to get the count that i know of.

However, if you can switch to using an ArrayList, it'd be much simpler to just use next() with the while loop like eRaaaa was suggesting. Next grabs the next row, but returns false if there are no more rows.

You don't do the last() or count() stuff, as that'll draw all the data across in one go and is not (considering you then have to reset the resul set back to the beginning) exactly performance enhancing.

The ArrayList is the usual way of doing this, so that is really what Eleeist should be doing.