CODEWITHSUNDEEP
javamysqljdbc
user6452836
user6452836

Reputation:

Why is the returned ResultSet null

Given this Table Structure:

enter image description here

I want to get basic details of a user from a database, so I created a ResulSet named rst (see code below). When I iterate over rst it always returns null.

public ResultSet Detail(String mobile) throws Exception {
    dbConnect(); // make connection 
    String sql = "select * from profile where mobile = ?";
    PreparedStatement pstmt = con.prepareStatement(sql);
    pstmt.setString(1, mobile);
    ResultSet rst = pstmt.executeQuery();
    dbClose(); // connection closed
    return rst;
}

Now if I iterate over rst after having received it from return, I get the null value.

try {
    ResultSet rst = new DB().Detail(mobile);
    String fname = rst.getString("firstname");
    String lname = rst.getString("lasttname");
    String date = rst.getString("dateofbirth");
    System.out.println("First name : " + fname + "\nLast Name : " + lname + "\nDate of Birth : " + date);
} catch (Exception exc) {
    exc.printStackTrace();
}

Output:

First name : null
Last name : null
Date of Birth : null

Upvotes: 2

Views: 172

Answers (2)

MWiesner
MWiesner

Reputation: 9043

According to the official documentation of ResultSet you need to call the next() method before you can access the data elements from the ResultSet:

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.

This is missing in your posted code fragment. Therefore, your code fragment no. 2 should be written as follows:

try {
     ResultSet rst = new DB().Detail(mobile);
     if(rst.next()) {
         String fname = rst.getString("firstname");
         String lname = rst.getString("lasttname");
         String date = rst.getString("dateofbirth");
         System.out.println("First name : " + fname + "\nLast Name : " + lname + "\nDate of Birth : " + date);
     } else {
         System.out.println("No data for the given parameter '" + mobile + "'");
     } 
} catch (Exception exc) {
     exc.printStackTrace();
}

As noted in the comment/answer by Jalal Kiswani, it's also recommendable to NOT close the DB-connection before you're finished processing the ResultSet.

Hope it helps.

Upvotes: 1

Jalal Kiswani
Jalal Kiswani

Reputation: 755

Closing the database connection will cause to close all related objects (resultset and prepared statement). so rs will not be valid after dbClose(); You should read the result set before closing the connection.

Upvotes: 1

Related Questions