CODEWITHSUNDEEP
javamysqljdbcresultset
S. Agarwal
S. Agarwal

Reputation: 31

Error while extracting from result set

I am trying to get data from a mysql table. My query returns one value when I run it manually on phpmyadmin but when I run from Java code it gives an empty set. This is the query:

SELECT real_name
from doctorlogin
where password='1234' and id='X11111X';

Code:

public ArrayList<ArrayList<String>> query(String statement) throws SQLException {
    ResultSet rs = stmt.executeQuery(statement);
    int numcols = rs.getMetaData().getColumnCount();
    ArrayList<ArrayList<String>> result = new ArrayList<ArrayList<String>>();
    if (rs.isBeforeFirst()) {
        do {
            ArrayList<String> row = new ArrayList<String>(); // new list per row
            int i = 0;
            while (i <= numcols) {  // don't skip the last column, use <=
                row.add(rs.getString(i++));
            }
            result.add(row); // add it to the result
        } while (rs.next());
    }

    return result;
}

public String login(String username, String password, String domain) throws SQLException {
    String query = "SELECT real_name from " + domain + "login where password='" + password + "' and id='" + username + "';";
    System.out.println(query);
    ArrayList<ArrayList<String>> r = query(query);

    if (r.size() > 1 || r.size() == 0)
        return "Login Failed";
    else
        return r.get(0).get(0);
}

After making the edit, the code still does not fetch any result even though I get one row in phpmyadmin.

public ArrayList<ArrayList<String>> query(String statement) throws SQLException {
    ResultSet rs = stmt.executeQuery(statement);
    int numcols = rs.getMetaData().getColumnCount();
    ArrayList<ArrayList<String>> result = new ArrayList<ArrayList<String>>();
    while ( rs.next() ) {
        ArrayList<String> row = new ArrayList<String>(); // new list per row
        for ( int i = 1; i <= numcols; i++ ) {
            row.add(rs.getString(i));
        }
        result.add(row); // add it to the result
    }
    return result;
}

Screenshots of query on phpmyadmin -> https://drive.google.com/drive/folders/0B_yqoCAAV2rFdDZqUzlmeXljNWs

Upvotes: 0

Views: 247

Answers (2)

RealSkeptic
RealSkeptic

Reputation: 34618

You cannot retrieve information from the row before calling rs.next(). So the usual format of a ResultSet loop is simply

while (rs.next()) {
    // Retrieve the column values
}

There is no need for the if statement there. If there are no returned rows, the if is false and you'll return an empty list. But the same is true for the simple while above. There is no need for do...while() - you first need to move the cursor, only then read the values.

Finally, columns are indexed from 1, not from 0. Thus your i should start from 1. But why not replace it with a for loop instead of a while?

while ( rs.next() ) {
     ArrayList<String> row = new ArrayList<String>(); // new list per row
     for ( int i = 1; i <= numcols; i++ ) {
         row.add(rs.getString(i));
     }
     result.add(row); // add it to the result
}

You should read the ResultSet documentation as the information is all there.

Upvotes: 2

Yassin Hajaj
Yassin Hajaj

Reputation: 21975

row.add(rs.getString(i++));

should be changed to

row.add(rs.getString(++i));

Because the row 0 is not accessible. The row count begins at 1.

In the second code, there's incrementing before getting the object from the DB so the first value is 1.

Upvotes: 0

Related Questions