CODEWITHSUNDEEP
javamysqlresultset
Tai Sheng Hong
Tai Sheng Hong

Reputation: 23

resultset.next() is showing output of first row only

So, I have something like this:

System.out.println("Enter owner's IC no. or plate no. : ");
String update = in.nextLine();

String sql = String.format("SELECT * FROM `vehicle` WHERE ic='%s' OR plate ='%s'",update,update);

ResultSet rs = stmt.executeQuery(sql);

if(rs.next()) {
    System.out.println("RegNo." +"\t\t"+ "Name" + "\t\t" + "IC" +"\t\t" + "Plate No." + "\t" + "Color" + "\t\t" + "Year" + "\t\t" + "Make" + "\t\t" + "Model" +"\t\t"+ "Capacity" + "\t" + "Type" +"\t\t" + "Max Load");
}
else {
    System.out.println("IC and PLate No. not found....");}

while (rs.next()) {
    regno = rs.getInt("regno");
    name = rs.getString("name");
    ic = rs.getString("ic");
    plate = rs.getString("plate");
    color = rs.getString("color");
    year = rs.getInt("year");
    make = rs.getString("make");
    model = rs.getString("model");
    capacity = rs.getDouble("capacity");
    type = rs.getString("type");
    maxload = rs.getDouble("maxload");

    System.out.println(toString());
}

What I'm trying to do is, if data is found in the database, it will then print the following table for outputs that match.

Now, It is supposed to print out every output. But, it only prints out the first one.

I believe that the following code is the cause:

 if(rs.next()) {
    System.out.println("RegNo." +"\t\t"+ "Name" + "\t\t" + "IC" +"\t\t" + "Plate No." + "\t" + "Color" + "\t\t" + "Year" + "\t\t" + "Make" + "\t\t" + "Model" +"\t\t"+ "Capacity" + "\t" + "Type" +"\t\t" + "Max Load");
}
else {
    System.out.println("IC and PLate No. not found....");}

Upvotes: 2

Views: 2084

Answers (3)

Anoop Singh
Anoop Singh

Reputation: 29

I also faced the same problem, when used select query with Prepared statement. For example-

String sqlquerySELECTAgentsWithParam = " select * from AGENTS WHERE AGENT_CODE = ( ? ) ";

it returned a single row, since I've used IF condition and then while(rs.next()) I couldn't print the single row, Hence, I've used do-while loop like below in order to print the first-row result. code snippet below- hope it will help !!

Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection(oracelDBUrl, oracleDBUser,oracleDBPwd );

if (conn != null) { System.out.println("Connected to the Oracle DB ");  }
else { System.out.println("Failed to Connect to Oracle DB "); }
PreparedStatement pstmt = conn.prepareStatement(sqlquerySELECTAgentsWithParam);
pstmt.setString(1,"<agent_id>");
ResultSet rs = pstmt.executeQuery();
boolean returnResultrs = rs.next();
System.out.println("Fetched Result is = " +returnResultrs);
if(returnResultrs)
{
    do {
        System.out.println("Inside Do - While Loop");
        System.out.println("AGENT_CODE = " + rs.getString(1)+ "has AGENT_NAME = "  +rs.getString(2));
        }
while(rs.next());

}

Upvotes: 1

Jordan
Jordan

Reputation: 2283

So I see two issues here. The biggest one is this:

System.out.println(toString());

That calls the .toString() method on the current class, which will not output any of the data from your ResultSet. At least, not based on any of the code you've shown. You're storing all of the values coming back from the ResultSet in variables, but those variables don't appear to be getting used anywhere. You need to get those variables to your .println() somehow.

The second issue is that rs.next() moves the cursor forward one row. So when you do this:

if(rs.next()) {

That causes you to skip the first row. This is actually kind of tricky to fix, because there's no good way to tell whether or not a ResultSet is empty without calling .next(). The way I'd probably handle this is to pull all of the results into objects in a list, and then do all the printing based on the list, and not on the ResultSet itself.

Upvotes: 0

AamirR
AamirR

Reputation: 12198

Use MessageFormat to format the output, and the counter to determine if empty result set, like so:

String strFormat = "RegNo. {0}\tName {1}\tIC {2}\tPlate No. {3}\tColor {4}\tYear {5}\tMake {6}\tModel {7}\tCapacity {8}\tType {9}\tMax Load {10}");

int counter = 0;

while (rs.next()) {
    counter++;

    regno = rs.getInt("regno");
    name = rs.getString("name");
    ic = rs.getString("ic");
    plate = rs.getString("plate");
    color = rs.getString("color");
    year = rs.getInt("year");
    make = rs.getString("make");
    model = rs.getString("model");
    capacity = rs.getDouble("capacity");
    type = rs.getString("type");
    maxload = rs.getDouble("maxload");

    System.out.println(MessageFormat.format(strFormat, regno, name, ic, plate, color, year, make, model, capacity, type, maxload));
}

if (counter == 0) {
    System.out.println("IC and PLate No. not found....");
}

Upvotes: 0

Related Questions