populate a java 2d array using a database table

[Easy Rhino]

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.

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.

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.

 

[Kreij]

What version of Java?
Here's something similar

 

[Easy Rhino]

what version of java?
here's something similar

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.

 

[Kreij]

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?

 

[Easy Rhino]

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 ?

 

[Kreij]

Can't you step through the code line by line with the debugger to see where it throws the exception?

 

[Easy Rhino]

Can't you step through the code line by line with the debugger to see where it throws the exception?

im using eclipse and have never used a debugger before 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!

 

[Kreij]

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?

 

[Easy Rhino]

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?

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.

 

[Aquinus]

What line is it failing on? That would certainly help.

 

[Kreij]

Good morning, ER

I found this ...

The ResultSet interface declares getter methods (for example, getBoolean and getLong) for retrieving column values from the current row. You can retrieve values using either the index number of the column or the alias or name of the column. The column index is usually more efficient. Columns are numbered from 1. For maximum portability, result set columns within each row should be read in left-to-right order, and each column should be read only once.

Try this ...

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

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.

 

[Easy Rhino]

well i knew that the getDouble(i) was the bad line i just didnt have a debugger to prove it.

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.

 

[Kreij]

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.

 

[Easy Rhino]

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

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

 

[Kreij]

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.

 

[Easy Rhino]

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.

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

 

[Kreij]

That's because you don't need the for loop that contains i any more ...
get rid of the rs.first() line, then

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]);
    }
}

 

[Easy Rhino]

That's because you don't need the for loop that contains i any more ...
get rid of the rs.first() line, then

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]);
    }
}

that did it. thanks i was banging my head.

 

[Kreij]

Glad it's working !!


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.

 

[caleb]

I'd like you to rattle the cage a bit