CODEWITHSUNDEEP
javamysqlspring
MortenT
MortenT

Reputation: 13

Resultset from MySql using Java/Spring is empty, and it should not have been

I am building code to access a database using Spring and Java from my standard dataobjects. My local setup uses a MySQL-installation, for simplicity. I have been able to perform inserts fine using my code, but I cannot get data out of select-operations...

I have a table in MySql. The database is called jonathan, the table is called Project, and it has an id column in it.

There was originally one row, but I have now inserted three rows in the table, which have been inserted using my java/spring-code.

I have singled out my problem and condensed the testing of the particular problem here, I have removed any where-clause to make sure there is no parameter-mapping problem involved. I have debugged the code to make sure I do receive a datasource.

public class TestDb extends MappingSqlQuery {
    public TestDb() {
        super(StorageFactory.getDataSource(), "Select id from jonathan.Project");
    }
    @Override
    protected Object mapRow(ResultSet arg0, int arg1) throws SQLException {
        System.out.println(arg1+" rows");
        for(int i=0;i<arg1;i++) {
            System.out.println(i+": "+arg0.getString(i));
        }
        return null;
    }
}

When I test this

public static void main(String[] args) {
    TestDb t = new TestDb();
    t.execute();
    System.out.println("Test done");
}

I get

0 rows
Test done

I have tried different casings on the table name also.

Solution: I confirmed that I could select the data using only jdbc, so the problem was how I accessed the results.

I found that the following worked:

public class TestDb extends MappingSqlQuery {
    public TestDb() {
        super(StorageFactory.getDataSource(), "Select id from jonathan.project");
    }
    @Override
    protected Object mapRow(ResultSet arg0, int arg1) throws SQLException {
        System.out.println(arg1 + " rows?");
        boolean b=arg0.first();
        while (b) {
            System.out.println(arg0.getInt(1));
            b=arg0.next();
        }
        return null;
    }
}

The print says 0 rows?

100000
100001
100002
Test done

I still wonder what arg1 is supposed to give me, because as far as I can tell it always says 0. I expected it to hold the number of rows selected. Also, the documentation for ResultSet says

A ResultSet cursor is initially positioned before the first row; the first call to the method next makes the first row the current row; the second call makes the second row the current row, and so on.

giving me the idea I could just next() through the whole ResultSet, but if I do first() and then next() it appears to work just fine.

Upvotes: 1

Views: 1857

Answers (3)

user330315
user330315

Reputation:

Doing first() and then next() is wrong, you only need next().

first() might even fail if you have a FORWARD_ONLY ResultSet.

The correct way to loop through a result set is:

while (arg0.next())
{ 
    // call arg0.getXXX() here
}

But with Spring you don't need that. mapRow() will be called for each row in the ResultSet and Spring will do the looping (based on next()) for you.

I still wonder what arg1 is supposed to give me

Well, it helps to read the JavaDocs:

Parameters:
  rs - ResultSet we're working through
  rowNum - row number (from 0) we're up to

So your method should look like this:

@Override
protected Object mapRow(ResultSet arg0, int arg1) throws SQLException {
    System.out.println("At row #" + arg1);
    System.out.println(i+": "+arg0.getString(1)); // get column 1 from the result set
    return null;
}

As your SELECT only returns a single column, there is no need for a loop (over the columns) inside the mapRow() method

Upvotes: 0

Flamboyaa
Flamboyaa

Reputation: 36

MappingSqlQuery is meant to be used for converting each row to one object and return a list containing all objects in a list. So mapRow is called once for each row in the ResultSet. But when you manually iterate the cursor you end the ResultSet on the first call to mapRow, that's why arg1 isn't incremented. Normally you would have had spring call your implementation of mapRow 3 times and arg1 would be 0 to 2.

I know this answer is way too late and you've probably solved this years ago but I hope it might help someone else with the same problem :-)

Upvotes: 1

LaGrandMere
LaGrandMere

Reputation: 10359

Edit :

Can you try at the beginning of mapRow :

if (!this.isCompiled()) System.out.println("Compilation needed !");

Then please try also at the beginning of mapRow :

if (!arg0.first()) System.out.println("Empty RS !");
else System.out.println("RS contains lines!");

If you've got "RS contains lines" text, then do i<=arg1 instead of i<arg1 in your loop. Elsewhere, I'll try to find something else to help you :)

Upvotes: 1

Related Questions