Search code examples
javamysqljdbcresultsetdata-retrieval

Doesn't retrieve the records while viewing a ResultSet in JDBC

Now i m learning ResultSet types in java. Here, I've coded for viewing records in different ways. At first i displayed the whole records which is in emp4 table and then i started to view these records in different manner(last,first,next) This is exactly what i am looking for but it won't display the all records which are presented in the emp4 table. See the First Program it doesn't work but if i documented the line 41(see this in Second Program) it just working awesome. What's the problem ? is there any wrong with my code ???

CODE SAMPLE 1

package demojdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class MysqlCon{

private static final String DB_DRIVER = "com.mysql.jdbc.Driver";
private static final String DB_CONNECTION = "jdbc:mysql://localhost:3306/vinoth";
private static final String DB_USER = "root";
private static final String DB_PASSWORD = "vino";

    public static void main(String args[])throws SQLException{

        //Creating statement and connection 
        Connection dbConnection = null;
        Statement stmt = null;

        try{

            //Creating class driver
            Class.forName(DB_DRIVER);

            //Creating Database Connection
            dbConnection = DriverManager.getConnection(DB_CONNECTION,DB_USER,DB_PASSWORD);

            //Creating statement
            stmt = dbConnection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);

            //Creating query
            String sql = "SELECT id,gmail,yahoo from emp4";

            //Creating ResultSet
            ResultSet rs = stmt.executeQuery(sql);

            //Displaying database
            System.out.println("Displaying records before doing some operations");
            System.out.println(rs.getInt(1)+" "+rs.getString(2)+" "+rs.getString(3));

            System.out.println("Displaying records for last row");
            rs.last();

            int id = rs.getInt("id");
            String gmail = rs.getString("gmail");
            String yahoo = rs.getString("yahoo");

            //Displaying records in last row
            System.out.println("ID : "+id);
            System.out.println("GMAIL : "+gmail);
            System.out.println("YAHOO : "+yahoo);

            System.out.println();
            rs.first();
            System.out.println("Displaying records for first row");

            id = rs.getInt("id");
            gmail = rs.getString("gmail");
            yahoo = rs.getString("yahoo");

            //Displaying records in last row
            System.out.println("ID : "+id);
            System.out.println("GMAIL : "+gmail);
            System.out.println("YAHOO : "+yahoo);

            System.out.println();
            rs.next();
            System.out.println("Displaying records for next row");

            id = rs.getInt("id");
            gmail = rs.getString("gmail");
            yahoo = rs.getString("yahoo");

            //Displaying records in last row
            System.out.println("ID : "+id);
            System.out.println("GMAIL : "+gmail);
            System.out.println("YAHOO : "+yahoo);

        }catch(SQLException e){

            e.printStackTrace();

        }catch(ClassNotFoundException e){

             System.out.println("Plese check the driver class path "+e.getMessage());

        }finally{

            if(stmt != null){

                stmt.close();

            }
            if(dbConnection != null){

                dbConnection.close();

            }
        }
    }
}

Here, the code will works fine.....

CODE SAMPLE 2

package demojdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class MysqlCon{

private static final String DB_DRIVER = "com.mysql.jdbc.Driver";
private static final String DB_CONNECTION = "jdbc:mysql://localhost:3306/vinoth";
private static final String DB_USER = "root";
private static final String DB_PASSWORD = "vino";

    public static void main(String args[])throws SQLException{

        //Creating statement and connection 
        Connection dbConnection = null;
        Statement stmt = null;

        try{

            //Creating class driver
            Class.forName(DB_DRIVER);

            //Creating Database Connection
            dbConnection = DriverManager.getConnection(DB_CONNECTION,DB_USER,DB_PASSWORD);

            //Creating statement
            stmt = dbConnection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);

            //Creating query
            String sql = "SELECT id,gmail,yahoo from emp4";

            //Creating ResultSet
            ResultSet rs = stmt.executeQuery(sql);

            //Displaying database
            System.out.println("Displaying records before doing some operations");
           //System.out.println(rs.getInt(1)+" "+rs.getString(2)+" "+rs.getString(3));

            System.out.println("Displaying records for last row");
            rs.last();

            int id = rs.getInt("id");
            String gmail = rs.getString("gmail");
            String yahoo = rs.getString("yahoo");

            //Displaying records in last row
            System.out.println("ID : "+id);
            System.out.println("GMAIL : "+gmail);
            System.out.println("YAHOO : "+yahoo);

            System.out.println();
            rs.first();
            System.out.println("Displaying records for first row");

            id = rs.getInt("id");
            gmail = rs.getString("gmail");
            yahoo = rs.getString("yahoo");

            //Displaying records in last row
            System.out.println("ID : "+id);
            System.out.println("GMAIL : "+gmail);
            System.out.println("YAHOO : "+yahoo);

            System.out.println();
            rs.next();
            System.out.println("Displaying records for next row");

            id = rs.getInt("id");
            gmail = rs.getString("gmail");
            yahoo = rs.getString("yahoo");

            //Displaying records in last row
            System.out.println("ID : "+id);
            System.out.println("GMAIL : "+gmail);
            System.out.println("YAHOO : "+yahoo);

        }catch(SQLException e){

            e.printStackTrace();

        }catch(ClassNotFoundException e){

             System.out.println("Plese check the driver class path "+e.getMessage());

        }finally{

            if(stmt != null){

                stmt.close();

            }
            if(dbConnection != null){

                dbConnection.close();

            }
        }
    }
}

OUTPUT

Displaying records for last row ID : 5 GMAIL : naveen YAHOO : naveenrockz

Displaying records for first row ID : 1 GMAIL : vinothvino YAHOO : vinothasd

Displaying records for next row ID : 2 GMAIL : ajithvirje YAHOO : ajith234

Please make me understand. Why my code doesn't fetch any records in the CODE SAMPLE 1 Program ?

Below image represents the following records in emp4 table

enter image description here

Solution

  • The reason is that you haven't progressed the cursor of the ResultSet by calling next() before accessing the data with getInt() / getString(). Try something like this instead:

    //Creating ResultSet
ResultSet rs = stmt.executeQuery(sql);

//Displaying database
System.out.println("Displaying records before doing some operations");

if (rs.next()) {        
    System.out.println(rs.getInt(1) + " "
        + rs.getString(2) + " " + rs.getString(3));
}

    If you want to loop through the whole result set, use while (rs.next()) instead.

    Your second code snippet works because you are moving the cursor to the last position with rs.last() before accessing the column values for the first time.

    Please note that you should always inspect the return value of rs.next() / rs.last() / rs.first() methods before accessing the column values. A falsey return value indicates that the result set has no rows and would cause an exception to be thrown when calling any of the getter (rs.getInt() / rs.getString() etc.) methods of the result set.