Prepared statement fails, but SQL console works

I am working on a project for uni (happens to be due in 14 hours) and I am at a sticking point. It is a web based web store running in eclipse on apache tomcat and derby.

I have a prepared statement that checks for a user name and passwordhash, no matter what I try this statement returns 0 rows. The same sql runs in the sql scratch pad and returns what is expected.

I have used the debugger to inspect the prepared statement object and the query seems fine. The ?'s in the text 开发者_如何学运维are still in place rather than filled with the variables, but that seems normal. I have also tried to run the exact same hand written sql from the console, but without any luck.

The query I run in the sql console is

SELECT * FROM username WHERE username='user@system.com' AND passwordhash='passwordhash'

The prepared statments look like this.

            PreparedStatement pstmt = db.prepareStatement("SELECT * FROM reallynice.username " +
                "WHERE emailaddress=?" +
                " AND passwordhash=?");

        pstmt.setString(1,username);
        pstmt.setString(2, username + ":" + passwordLogin);

I am at the point where I have tried everything, and have run out of searches to make. I know this is a uni project and the standard reply is to give people somewhere to look. At this point I need spoon feed a path to go down.

EDIT Here is some more background, I have tried running a known working query in this pipeline and it also fails to return any rows.

public static User getUser(String username, String passwordHash) {
    DBBean db = new DBBean();
    System.out.println("Logging in for username " + username + " and password " + passwordHash);
    try {
        ResultSet rs;

        PreparedStatement pstmt = db.prepareStatement("SELECT * FROM reallynice.username " +
                "WHERE emailaddress=?" +
                " AND passwordhash=?");

        pstmt.setString(1,username);
        pstmt.setString(2,passwordHash);
        //PreparedStatement pstmt = db.prepareStatement("SELECT * FROM reallynice.product");

        //PreparedStatement pstmt = db.prepareStatement("SELECT * FROM reallynice.username WHERE emailaddress='user@me.com' AND passwordhash='megahashstring'");

        rs = pstmt.executeQuery();
        System.out.println("Rows returned\t" + rs.getRow());

        if(rs.getRow() < 1)
            return null;

        int id = rs.getInt("uid");              
        String name = rs.getString("name");
        String emailaddress = rs.getString("emailaddress");
        String password = rs.getString("passwordhash"); 
        boolean isAdmin = false;

        pstmt = db.prepareStatement("SELECT * FROM reallnice.admin WHERE uid= ?");
        pstmt.setInt(1, id);
        rs = pstmt.executeQuery();

        if(rs.getMetaData().getColumnCount() > 0)
            isAdmin = true;
        return new User(id,isAdmin,name,emailaddress,password);

    } catch(Exception ex) {
        System.out.println(ex);
    }


    return null;
}

I have also included the other queries I have tried for this.

---

Whenever I see someone having an experience like this: "no matter what I try this statement returns 0 rows," there are two possible reasons that come immediately to mind:

1) You aren't using the database you think you are. Derby's connection URL, if you say ";create=true", will quite happily make a new, empty database when you connect, if it doesn't find an existing database in the location you expect. This sort of problem arises from a confusion over where the databases are created; a database with a relative name will be created in whatever directory turns out to the be derby.system.home of the Derby instance that gets that connection URL. So check to see if you are using a different current working directory, or for some other reason are connecting to a different database than you think you are.

2) You aren't using the schema you think you are. Derby will quite happily create multiple schemas, and each schema has a separate set of tables, so if you are initially connecting as user A, and then later connect as user B, and don't issue SET SCHEMA, then user A and user B have completely separate sets of tables and so you won't be accessing the tables that you think you are. So check to see if you are connecting as the same user and using the same schema when you connect to the database.

---

Try changing how you display your logging statement

System.out.println("Rows returned\t" + rs.getRow());

getRow() returns the current row number, not how many records were returned. In order to user getRow() to count the number of entries in the result set you would need to move the pointer of the result set to the last entry.

You have also, not called next() yet, which means you aren't pointing at anything (and most likely the reason you always see 0 as the number). Try using

while(rs.next()){ //go through the entire ResultSet} 

or

if(rs.next()) { //access the first record in the ResultSet}

So over all, if you change your code to something like the following you may have better results.

rs = pstmt.executeQuery();
if(rs.next()){
    System.out.println("Processing Row " + rs.getRow());
    //continue on
}else{
    System.out.println("No Records");
}

If you have set your table where the username is a unique key, you can be assured this will return 0 or 1 row. Otherwise use the while() option instead of if()

EDIT:: Also as a side note, because you are not calling next()

if(rs.getRow() < 1)
            return null;

will always be 0, which returns null from your method.