What does "if (rs.next())" mean?

Question

I am currently getting the error,

java.sql.SQLException: Method 'executeQuery(String)' not allowed on prepared statement.

because I am using

PreparedStatement stmt = conn.prepareStatement(sql);

and also had

ResultSet rs = stmt.executeQuery(sql);

in my code.

I now need to remove the ResultSet line but that leaves me with having to deal with the following code:

if (rs.next()) {
    messages.add(ActionMessages.GLOBAL_MESSAGE, new ActionMessage("login.successful"));
    request.getSession(true).setAttribute("USERNAME", rs.getString("USERNAME"));
    request.getSession(true).setAttribute("BALANCE", rs.getString("BALANCE"));
    request.setAttribute("msg", "Logged in successfully");

I'm not sure I completely understand what

 if (rs.next())

does. Could someone explain this code to me? If I have a better understanding of that I believe I'll have a better idea on how to deal using the PreparedStatement results with the logic being used for rs. Also any help to deal with changing that logic would be greatly appreciated too.

Fucoid

Answer 1

As to the concrete problem with that SQLException, you need to replace

ResultSet rs = stmt.executeQuery(sql);

by

ResultSet rs = stmt.executeQuery();

because you're using the PreparedStatement subclass instead of Statement. When using PreparedStatement, you've already passed in the SQL string to Connection#prepareStatement(). You just have to set the parameters on it and then call executeQuery() method directly without re-passing the SQL string.

See also:

• JDBC tutorial - Using Prepared Statements

---

As to the concrete question about rs.next(), it shifts the cursor to the next row of the result set from the database and returns true if there is any row, otherwise false. In combination with the if statement (instead of the while) this means that the programmer is expecting or interested in only one row, the first row.

See also:

• Javadoc on ResultSet#next() method
• Problem with SQL, ResultSet in java

Superannuated

Thank you very much! This helped out very much! – Fucoid

@Fucoid To your defense, the exception explanation isn't very clear at all. It implies that you have used the wrong method and NOT the wrong parameter. – Renitarenitent

Answer 2

The next() moves the cursor froward one row from its current position in the resultset. so its evident that if(rs.next()) means that if the next row is not null (means if it exist), Go Ahead.

Now w.r.t your problem,

ResultSet rs = stmt.executeQuery(sql);  //This is wrong
                                  ^       

note that executeQuery(String) is used in case you use a sql-query as string.

Whereas when you use a PreparedStatement, use executeQuery() which executes the SQL query in this PreparedStatement object and returns the ResultSet object generated by the query.

Solution :

Use : ResultSet rs = stmt.executeQuery();

Lanceolate

Thank you very much! You explained the change I needed to make very well! – Fucoid

Answer 3

The next() method (offcial doc here) simply move the pointer of the result rows set to the next row (if it can). Anyway you can read this from the offcial doc as well:

Moves the cursor down one row from its current position.

This method return true if there's another row or false otherwise.

Camilacamile

Answer 4

I'm presuming you're using Java 6 and that the ResultSet that you're using is a java.sql.ResultSet.

The JavaDoc for the ResultSet.next() method states:

Moves the cursor froward one row from its current position. 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.

When a call to the next method returns false, the cursor is positioned after the last row. Any invocation of a ResultSet method which requires a current row will result in a SQLException being thrown.

So, if(rs.next(){ //do something } means "If the result set still has results, move to the next result and do something".

As BalusC pointed out, you need to replace

ResultSet rs = stmt.executeQuery(sql);

with

ResultSet rs = stmt.executeQuery();

Because you've already set the SQL to use in the statement with your previous line

PreparedStatement stmt = conn.prepareStatement(sql);

If you weren't using the PreparedStatement, then ResultSet rs = stmt.executeQuery(sql); would work.

Extracellular

sorry, don't have enough rep to do that. – Fucoid

Answer 5

Look at the picture, it's a result set of a query select * from employee

and the next() method of ResultSet class help to move the cursor to the next row of a returned result set which is rs in your example.

:)

Lobo

Answer 6

Since Result Set is an interface, When you obtain a reference to a ResultSet through a JDBC call, you are getting an instance of a class that implements the ResultSet interface. This class provides concrete implementations of all of the ResultSet methods.

Interfaces are used to divorce implementation from, well, interface. This allows the creation of generic algorithms and the abstraction of object creation. For example, JDBC drivers for different databases will return different ResultSet implementations, but you don't have to change your code to make it work with the different drivers

In very short, if your ResultSet contains result, then using rs.next return true if you have recordset else it returns false.

Emission

Answer 7

First thing, you don't need to write

ResultSet rs = stmt.executeQuery(sql);

just write

ResultSet rs = stmt.executeQuery();

The above mentioned syntax is used for Statements not for PreparedStatement.

Second thing, rs.next() checks if the result set contains any values or not. It returns a boolean value as well as it moves the cursor to the first value in the result set because initially it is at BEFORE FIRST Position. So if you want to access first value in result set, you need to write rs.next().

Unstring