JDBC Select * from Where x=(?)

Question

I'm trying to make a method that retrieves a customer from a database and stores it in an object. But for some reason my sql statement is not working.

This is my method.

    public class CustomerDAO {

private String url = "jdbc:h2:tcp://localhost/project;IFEXISTS=TRUE";

Set<Customer> customerCollection = new TreeSet<>();


public Customer loginCustomer(String user,String pass) throws SQLException{

    String sql = "SELECT * FROM Customers WHERE USERNAME=(?) AND PASSWORD=(?) ;";

    try(
        //get connection form database
        Connection dbCon = JdbcConnection.getConnection(url);
        //create a statement
        PreparedStatement stmt = dbCon.prepareStatement(sql);                   
        ){

        stmt.setString(1, user);
        stmt.setString(2, pass);

        ResultSet rs = stmt.executeQuery();

        Customer c = new Customer(rs.getString("username"), rs.getString("name"), rs.getString("email"),
                                    rs.getString("address"), rs.getString("creditCardDetails"), rs.getString("password"));


        if(c.getUsername().contains(user))
            return c;
        else
            return null;


}catch (SQLException ex){
        throw new RuntimeException(ex);
    }

}

I tested it using this

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

    CustomerDAO dao = new CustomerDAO();

    Customer c = dao.loginCustomer("jesus", "1234");

    System.out.println(c.getName());
    System.out.println(c.getPassword());
    System.out.println(c.getAddress());



}

}

but i get these errors.

        Exception in thread "main" java.lang.RuntimeException: org.h2.jdbc.JdbcSQLException: No data is available [2000-187]
    at DAO.CustomerDAO.loginCustomer(CustomerDAO.java:54)
    at web.NewMain.main(NewMain.java:25)
Caused by: org.h2.jdbc.JdbcSQLException: No data is available [2000-187]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
    at org.h2.message.DbException.get(DbException.java:179)
    at org.h2.message.DbException.get(DbException.java:155)
    at org.h2.message.DbException.get(DbException.java:144)
    at org.h2.jdbc.JdbcResultSet.checkOnValidRow(JdbcResultSet.java:3186)
    at org.h2.jdbc.JdbcResultSet.get(JdbcResultSet.java:3192)
    at org.h2.jdbc.JdbcResultSet.get(JdbcResultSet.java:3209)
    at org.h2.jdbc.JdbcResultSet.getString(JdbcResultSet.java:302)
    at DAO.CustomerDAO.loginCustomer(CustomerDAO.java:43)
    ... 1 more
Java Result: 1

Running the statement directly from the database console works fine. for example

SELECT * FROM Customers WHERE USERNAME='jesus' AND PASSWORD='1234' ;

Probably duplicate of http://stackoverflow.com/questions/26905227/unable-to-get-results-from-h2-db — Vishal, Sep 23 '15 at 04:31

score 2 · Answer 1 · answered Sep 23 '15 at 04:43

2

You have to move the cursor to the next row : Add

rs.next();

after :

 ResultSet rs = stmt.executeQuery();

answered Sep 23 '15 at 04:43

Rahman

3,755
3
26
43

score 1 · Accepted Answer · answered Sep 23 '15 at 04:46

1

Before invoking the getter methods of resultsets you need to move the cursor forward. Have a look at http://docs.oracle.com/javase/7/docs/api/java/sql/ResultSet.html#next%28%29

Also, in case to avoid empty result set, you can modify your code as

Customer c=null;    
if(rs.next()){
    c = new Customer(rs.getString("username"), rs.getString("name"), rs.getString("email"),
     rs.getString("address"), rs.getString("creditCardDetails"), rs.getString("password"));

}
if(null != c && c.getUsername().contains(user))
            return c;
        else
            return null;

answered Sep 23 '15 at 04:46

Balwinder Singh

2,272
5
23
34

Thanks so much! make so much sense now. – Matt Laird Sep 23 '15 at 06:10
@MattLaird If it resolves your query, then you can accept the answer – Balwinder Singh Sep 23 '15 at 06:12

JDBC Select * from Where x=(?)

2 Answers2