Results 1 to 8 of 8
  1. #1
    Eleeist is offline Member
    Join Date
    May 2011
    Posts
    84
    Rep Power
    0

    Default For loop and DB records

    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:

    Java Code:
    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

  2. #2
    eRaaaa is offline Senior Member
    Join Date
    Oct 2010
    Location
    Germany
    Posts
    787
    Rep Power
    6

    Default Re: For loop and DB records

    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 ....?!

  3. #3
    Eleeist is offline Member
    Join Date
    May 2011
    Posts
    84
    Rep Power
    0

    Default Re: For loop and DB records

    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.

    Java Code:
    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.

  4. #4
    Shoss's Avatar
    Shoss is offline Member
    Join Date
    Feb 2011
    Posts
    94
    Rep Power
    0

    Default Re: For loop and DB records

    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.

  5. #5
    Eleeist is offline Member
    Join Date
    May 2011
    Posts
    84
    Rep Power
    0

    Default Re: For loop and DB records

    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:

    Java Code:
     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:

    Java Code:
    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).

  6. #6
    Shoss's Avatar
    Shoss is offline Member
    Join Date
    Feb 2011
    Posts
    94
    Rep Power
    0

    Default Re: For loop and DB records

    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.

  7. #7
    Eleeist is offline Member
    Join Date
    May 2011
    Posts
    84
    Rep Power
    0

    Default Re: For loop and DB records

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

  8. #8
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,225
    Rep Power
    20

    Default Re: For loop and DB records

    Quote Originally Posted by Shoss View Post
    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.

Similar Threads

  1. deleting records in a db
    By droidus in forum New To Java
    Replies: 1
    Last Post: 11-08-2011, 08:16 PM
  2. Throttling records
    By jitman in forum New To Java
    Replies: 0
    Last Post: 03-11-2010, 07:18 PM
  3. Moving records from one Arraylist to another?
    By JavaStudent23 in forum New To Java
    Replies: 4
    Last Post: 02-17-2010, 10:52 PM
  4. insert records
    By rudravaram in forum JDBC
    Replies: 3
    Last Post: 01-03-2010, 06:54 PM
  5. How to delete the records
    By kiran kumar in forum Java Servlet
    Replies: 6
    Last Post: 11-09-2008, 02:16 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •