CODEWITHSUNDEEP
javamysqlprepared-statement
Lytthouse
Lytthouse

Reputation: 1

MySQL prepared statement in Java not returning a value

I have a web service method that executes a prepared statement against a MySQL database. When I run this statement in MySQL I get a returned value:

SELECT first_name
  FROM lytthouse_airlines.passenger
 WHERE passenger_id = 1

;

but when I run this prepared statement in my java class, it doesn't work:

PreparedStatement getName = connection.prepareStatement(
   "SELECT first_name " +
   " FROM lytthouse_airlines.passenger " +
   " WHERE passenger_id = ? "
 );

getName.setInt(1, passengerId);
rs = getName.executeQuery();
returnValue = "After executing query";

if (rs.next()) 
{
   returnValue = "inside rs.next";
   returnValue = rs.getString("first_name");
}

When I test this what I get in the returnValue contains "After executing query"; which would imply that there is no ResultSet (rs) returned and therefore not getting into the if (rs.next()) logic.

Anyone have any clue what's wrong?

New Content

After Guo's response I changed the code a bit so I could see the value of passengerId. When I run a Test of the web service I get this very interesting result.

Method parameter(s)

Type    Value
int     1
Method returned

java.lang.String : "After executing query, with a passengerId of 0"

I have double-checked and I am not changing the value of passengerId anywhere in the method before it gets to they query.

Another new comment

I think the problem has nothing to do with the prepared statement it seems to have to do with the method itself. I change the very beginning of the method to look like this

@WebMethod(operationName = "getPassengerName")
public String getPassengerName(@WebParam(name = "passengerId ") int passengerId)
{
   //MySQL elements
   Connection connection;
   ResultSet rs = null;

   String returnValue = "start method with passengerId having a value of: " + passengerId;

and when I run the test I get this:

getPassengerName Method invocation


Method parameter(s)

Type    Value
int     1
Method returned

java.lang.String : "start method with passengerId having a value of: 0"

I think this calls for a new question. I will pose a new question now. Thank you again for everyone who looked at this and provided feedback. I sure do appreciate it.

Upvotes: 0

Views: 723

Answers (3)

djechlin
djechlin

Reputation: 60758

This just means rs.next() returns false. System.err.println("error message") or just actually using a debugger (you should actually use a debugger) may better help you. So you just don't have a record with passenger_id 1. Did you verify this in your SQL editor directly?

Upvotes: 0

Guo Song
Guo Song

Reputation: 352

I test the result in my localhost, and its result is right. So I think you have not a record whose passenger_id = 1.

Upvotes: 1

Pankaj Sharma
Pankaj Sharma

Reputation: 679

Add rs.beforeFirst() before your if.

Hope this helps.

Upvotes: 0

Related Questions