1. Welcome to TechPowerUp Forums, Guest! Please check out our forum guidelines for info related to our community.

populate a java 2d array using a database table

Discussion in 'Programming & Webmastering' started by Easy Rhino, Mar 24, 2012.

  1. Easy Rhino

    Easy Rhino Linux Advocate

    Joined:
    Nov 13, 2006
    Messages:
    13,195 (4.87/day)
    Thanks Received:
    3,121
    I am struggling to figure this out. I currently have a working method that populates a 2d array using a text file with 10 rows and 6 columns.

    Code:
    g_testdata = new double[G_TESTEXAMPLES][G_ATTRIBUTES+1];
    		
    		File inTestFile = new File("testdata.dat"); //open test data file
    		Scanner inputTestFile = new Scanner(inTestFile);
    		
    		for (int i=0; i < G_TESTEXAMPLES; i++)
    		{
    			for (int j=0; j < G_ATTRIBUTES+1; j++) // populate the test data array
    			{
    				g_testdata[i][j] = inputTestFile.nextDouble(); 
    			}
    		}
    		
    		if (inputTestFile !=null) inputTestFile.close();
    that is pretty straight forward as it uses the Scanner class to read through the text file. now i want to do the same thing but using a database table.

    this is what i have so far. i keep running into: java.sql.SQLException: Column Index out of range, 0 < 1.

    Code:
    g_trainingdata = new double[G_TRAININGEXAMPLES][G_ATTRIBUTES+1];
    		try {
                String connURL = "jdbc:mysql://{sanitized}";
                String urlPass = "{sanitized}";
                String urlUser = "{sanitized}";
    
                // output user input for historical record
                String query = "select squarefeet,bedrooms,bathrooms,lotsize,garage,current_price from pricePredictor.testdata";
                
    
                Connection conn = DriverManager.getConnection(connURL,urlPass,urlUser);
                Statement stmt = conn.createStatement();
                ResultSet rs = stmt.executeQuery(query);
                
                //retrieve number of columns and rows
                int numRows, numCols;
    			if(!rs.next()){
    				return;
    			}
    			rs.last();
    			numRows = rs.getRow();
    			numCols = rs.getMetaData().getColumnCount();
    			System.out.println(numRows + " " + numCols); //for debugging purposes
    			rs.first();
             
                    //populate array using a for loop
                	for (int i=0; i < numRows; i++)
            		{
            			for (int j=0; j < numCols; j++)
            			{
            				g_trainingdata[i][j] = rs.getDouble(i); // populate the training data array
            			}
            		}
                
    		}//end of try
            catch (SQLException e) {
            	System.out.println(e);
            }
    any insight would be helpful! apparently to do this properly i need to create a List. i am lost.
    Last edited: Mar 24, 2012
  2. Kreij

    Kreij Senior Monkey Moderator Staff Member

    Joined:
    Feb 6, 2007
    Messages:
    13,881 (5.29/day)
    Thanks Received:
    5,610
    Location:
    Cheeseland (Wisconsin, USA)
  3. Easy Rhino

    Easy Rhino Linux Advocate

    Joined:
    Nov 13, 2006
    Messages:
    13,195 (4.87/day)
    Thanks Received:
    3,121
    1.7

    edit: i have no issue connecting to the database and writing a single array

    apparently this has something to do with using List and HashMap. I am not familiar with those.
  4. Kreij

    Kreij Senior Monkey Moderator Staff Member

    Joined:
    Feb 6, 2007
    Messages:
    13,881 (5.29/day)
    Thanks Received:
    5,610
    Location:
    Cheeseland (Wisconsin, USA)
    Oops, I meant to say what version of MySQL. Sorry
    In the above link the guy updated his MySQL version and it seemed to fix it.

    Can you pin down the line that's throwing the exception?
  5. Easy Rhino

    Easy Rhino Linux Advocate

    Joined:
    Nov 13, 2006
    Messages:
    13,195 (4.87/day)
    Thanks Received:
    3,121
    since mysql is spitting the error code then no but my educated guess is

    g_trainingdata[j] = rs.getDouble(i);

    when it tried to populate that array it hits the error. are you familiar with arraylist ?
  6. Kreij

    Kreij Senior Monkey Moderator Staff Member

    Joined:
    Feb 6, 2007
    Messages:
    13,881 (5.29/day)
    Thanks Received:
    5,610
    Location:
    Cheeseland (Wisconsin, USA)
    Can't you step through the code line by line with the debugger to see where it throws the exception?
  7. Easy Rhino

    Easy Rhino Linux Advocate

    Joined:
    Nov 13, 2006
    Messages:
    13,195 (4.87/day)
    Thanks Received:
    3,121
    im using eclipse and have never used a debugger before :ohwell: from what ive read best practice is to use ArrayList instead of doing what i am doing. i could be wrong though. i just want the simplest way to get the database table into the array memory lol!
    Last edited: Mar 24, 2012
  8. Kreij

    Kreij Senior Monkey Moderator Staff Member

    Joined:
    Feb 6, 2007
    Messages:
    13,881 (5.29/day)
    Thanks Received:
    5,610
    Location:
    Cheeseland (Wisconsin, USA)
    I'm not set up to run Java/MySQL on my machine. I don't think I'll be much help, ER.

    Not using "best practices" does not usually mean getting exceptions, but I'm no Java guru.
    Hopefully one of the TPU Java folks will come along to help.

    The only other thing I could suggest would be to put in more debugging output lines to see if you can narrow it down more.
    Speaking of that, do you get the output from your system.out.println before it barfs?
  9. Easy Rhino

    Easy Rhino Linux Advocate

    Joined:
    Nov 13, 2006
    Messages:
    13,195 (4.87/day)
    Thanks Received:
    3,121
    yes, it returns the correct number of rows and columns. so retrieving the table data and holding in the resultSet works fine. it is putting into the 2D array that is failing.
  10. Aquinus

    Aquinus Resident Wat-man

    Joined:
    Jan 28, 2012
    Messages:
    5,554 (6.86/day)
    Thanks Received:
    1,751
    Location:
    Concord, NH
    What line is it failing on? That would certainly help.
  11. Kreij

    Kreij Senior Monkey Moderator Staff Member

    Joined:
    Feb 6, 2007
    Messages:
    13,881 (5.29/day)
    Thanks Received:
    5,610
    Location:
    Cheeseland (Wisconsin, USA)
    Good morning, ER

    I found this ...
    Try this ...
    Code:
    for (int row = 0; row < numRows; row++)
    {
        for (int col = 1; col <= numcols; numcols++)
        {
            g_trainingdata[row][col - 1] = rs.getDouble(col);
        }
    }
    
    I'm not sure if rows are 1 based also, so you may need to change that also to start with 1 instead of zero.
    The example always use rs.next() instead of index incrementing for rows.
    If you want to use rs.next() in a while loop, you will need an incrementing varialble to manage the cursor position in the RecordSet.
    Something like ..
    Code:
    int row = 0; // or -1 if rows are zero based.
    rs.beforeFirst();
    while (rs.Next())
    {
        row++;
        for (int col = 1; col <= numcols; col++)
        {
             gtrainigdata[row][col - 1] = getDouble(col);
        }
    }
    
    Note : Looks like in your original code you were using getDouble(i). Since i was the row value you would always get the first column in the row even though you were iterating through the columns using the j valiabe. Should have been getDouble(j). That's why I changed the names of the for loop variable to make it easier to understand at a glance.

    Note2 : Take a little while and learn to use the debugger for at least line stepping and setting breakpoints. It's really pretty easy, will save you a lot of headaches when trying to pin down troublesome code and you will kick yourself for not learning it sooner. :D
    Last edited: Mar 24, 2012
  12. Easy Rhino

    Easy Rhino Linux Advocate

    Joined:
    Nov 13, 2006
    Messages:
    13,195 (4.87/day)
    Thanks Received:
    3,121
    well i knew that the getDouble(i) was the bad line i just didnt have a debugger to prove it.

    Code:
    for (int i=1; i < numRows; i++)
            		{
            			for (int j=1; j < numCols; j++)
            			{
            				g_trainingdata[i][j] = rs.getDouble(j); // populate the training data array
            			}
            		}
    seems to fix it. im going to try this now to have my program actually read from the array and perform some calculations. thanks kreij. you have proved once again a solid nights sleep and the eyes of another person always helps tremendously.
  13. Kreij

    Kreij Senior Monkey Moderator Staff Member

    Joined:
    Feb 6, 2007
    Messages:
    13,881 (5.29/day)
    Thanks Received:
    5,610
    Location:
    Cheeseland (Wisconsin, USA)
    One thought, if the array is zero based then you need to subtract one from i and j when using them for the array index.
    Also, if not making it <= to the number of rows and colums you will be skipping the last column for each row and the last row completely.
  14. Easy Rhino

    Easy Rhino Linux Advocate

    Joined:
    Nov 13, 2006
    Messages:
    13,195 (4.87/day)
    Thanks Received:
    3,121
    hrm yes you are correct. it seems that it is skipping the last column completely and it is simply using the first row every time for the amount of rows. so this code produces the following result...

    Code:
    int numRows, numCols;
    			if(!rs.next()){
    				return;
    			}
    			rs.last();
    			numRows = rs.getRow();
    			numCols = rs.getMetaData().getColumnCount();
    			System.out.println(numRows + " " + numCols);
    			rs.first();
    		
    			for (int i=1; i < numRows; i++)
    			{
    				for (int j=1; j < numCols; j++) // populate the test data array
    				{
    					g_testdata[i][j] = rs.getDouble(j); 
    					System.out.println(g_testdata[i][j]);
    				}
    				
    			}
    68 6
    10 6
    0.376
    1.0
    0.75
    0.214765100671141
    0.0
    0.376
    1.0
    0.75
    0.214765100671141
    0.0
    0.376
    1.0
    0.75
    0.214765100671141
    0.0
    0.376
    1.0
    0.75
    0.214765100671141
    0.0
    0.376
    1.0
    0.75
    0.214765100671141
    0.0
    0.376
    1.0
    0.75
    0.214765100671141
    0.0
    0.376
    1.0
    0.75
    0.214765100671141
    0.0
    0.376
    1.0
    0.75
    0.214765100671141
    0.0
    0.376
    1.0
    0.75
    0.214765100671141
    0.0

    so it reports the correct number of rows and columns but as you can see it is only counting the first 5 columns and repeating them for the amount of rows
    Last edited: Mar 24, 2012
  15. Kreij

    Kreij Senior Monkey Moderator Staff Member

    Joined:
    Feb 6, 2007
    Messages:
    13,881 (5.29/day)
    Thanks Received:
    5,610
    Location:
    Cheeseland (Wisconsin, USA)
    Okay that's weird. I can understand it missing the last column because you are telling it to use 1 less than the number of columns, but why is it stuck on the first row? lol

    Oh duh. You can't do it that way because your loop is not actually getting the next row from the RecordSet.
    You will need to use the example I posted above using rs.beforeFirst and rs.next.
  16. Easy Rhino

    Easy Rhino Linux Advocate

    Joined:
    Nov 13, 2006
    Messages:
    13,195 (4.87/day)
    Thanks Received:
    3,121
    ok, now it gets all the rows but it gets them for the number of times there are rows. so it gets row 1 10 times, row 2 10 times, row 3 10 times and so on. if i fix i <=numRows , i<=numCols it still only grabs 5 columns and is skipping the FIRST row now... here is my code and output.

    Code:
    g_testdata = new double[10][6];
    		
    		try {
    			String connURL = "jdbc:mysql://{sanitized}";
                String urlPass = "{sanitized}";
                String urlUser = "{sanitized}";
    
                String query = "select squarefeet,bedrooms,bathrooms,lotsize,garage,current_price from pricePredictor.testdata order by current_price ASC";
                Connection conn = DriverManager.getConnection(connURL,urlPass,urlUser);
                Statement stmt = conn.createStatement();
                ResultSet rs = stmt.executeQuery(query);
                
                int numRows, numCols;
    			if(!rs.next()){
    				return;
    			}
    			rs.last();
    			numRows = rs.getRow();
    			numCols = rs.getMetaData().getColumnCount();
    			System.out.println(numRows + " " + numCols);
    			rs.first();
    			while(rs.next()){
    		
    				for (int i=1; i <= numRows; i++)
    				{
    					for (int j=1; j <= numCols; j++) // populate the test data array
    					{
    						g_testdata[i][j] = rs.getDouble(j);
    						System.out.println(g_testdata[i][j]);
    						
    					}
    				}
    				
    			}
    
    68 6
    10 6
    0.3076
    1.0
    0.25
    0.335570469798658
    0.0
    Exception in thread "main" java.lang.ArrayIndexOutOfBoundsException: 6
    at neuralModified.<init>(neuralModified.java:119)
    at neuralModified.main(neuralModified.java:300)

    so in review it is skipping row #1 and column # 6
  17. Kreij

    Kreij Senior Monkey Moderator Staff Member

    Joined:
    Feb 6, 2007
    Messages:
    13,881 (5.29/day)
    Thanks Received:
    5,610
    Location:
    Cheeseland (Wisconsin, USA)
    That's because you don't need the for loop that contains i any more ...
    get rid of the rs.first() line, then
    Code:
    
    int row = 0; // set the row counter to zero
    rs.beforeFirst() // Set cursor to before the first record.
    while (rs.Next()) // iterate through the rows
    {
        row++; // increment the row number
        for (int col = 1; col <= numCols; col++) // iterate through the columns
        {
            g_trainingdata[row - 1][col - 1] = getDouble(col);
            system.out.println(g_trainingdata[row -1][col -1]);
        }
    }
    
    Last edited: Mar 24, 2012
    Easy Rhino says thanks.
  18. Easy Rhino

    Easy Rhino Linux Advocate

    Joined:
    Nov 13, 2006
    Messages:
    13,195 (4.87/day)
    Thanks Received:
    3,121
    that did it. thanks i was banging my head.
  19. Kreij

    Kreij Senior Monkey Moderator Staff Member

    Joined:
    Feb 6, 2007
    Messages:
    13,881 (5.29/day)
    Thanks Received:
    5,610
    Location:
    Cheeseland (Wisconsin, USA)
    Glad it's working !! :toast:


    There are a couple of other things you could do to optimize a bit, but I won't rattle the cage unless you want me to. :)
  20. caleb

    Joined:
    Sep 15, 2004
    Messages:
    1,528 (0.44/day)
    Thanks Received:
    200
    Location:
    Poland,Slask
    I'd like you to rattle the cage a bit :)

Currently Active Users Viewing This Thread: 1 (0 members and 1 guest)

Share This Page