Execution of PreparedStatement select fails

140 views Asked by At

I am trying to select a record from a table IsSELECTED, using preparedStatements. My table has 3 fields (date (DATETIME), morning(BOOL), noon(BOOL), night(BOOL)) and one record with theses values (2013-12-12, 0 , 0, 0). My method tries to select the record with the given date.

     public void getTodayInfo(HttpSession session, String date) 
        throws ClassNotFoundException, SQLException{
    System.out.println("flag1"); 
    String sq = "SELECT date, morning, noon, night FROM IsSELECTED WHERE date='?'";

    try {       
        System.out.println("flag2");
        Class.forName("org.sqlite.JDBC");
        c = DriverManager.getConnection(path);            
        stm = c.prepareStatement(sq);
        stm.setString(1, date);            
        System.out.println("flag3");
        ResultSet rs = stm.executeQuery(sq);
        while (rs.next()) {
            System.out.println("flag4");
            Shift s = new Shift(rs.getString(date), rs.getBoolean("morning"), rs.getBoolean("noon"), rs.getBoolean("night"));
            System.out.println("flag5" + s.toString());
            session.setAttribute("shiftsToday", s);
        }
    } catch (SQLException e) {
        System.out.println(e.getMessage());
    } finally {
        if (stm != null)
            stm.close();
        if (c != null)
    c.close();
    }

    System.out.println("Goodbye!");

}   

and nothing happens. I put some System.out.println to see where I have the problem and i get this

flag1 
flag2

Let's assume that it doesn't match with any record (but i see my table with this record, anyway) why the flag3 isn't printed?

2

There are 2 answers

1
Naman Gala On BEST ANSWER

Remove ' present around ? from your query.

String sq = "SELECT date, morning, noon, night FROM IsSELECTED WHERE date=?";

No need to pass query again while executing.

ResultSet rs = stm.executeQuery();

Also include " around date in rs.getString(date)

Shift s = new Shift(rs.getString("date"), rs.getBoolean("morning"), rs.getBoolean("noon"), rs.getBoolean("night"));

Note: if date is date field in database then you should use setDate, getDate method.

4
Titus On

You don't need to include ' around the ?, use just date = ?.

Also use:

ResultSet rs = stm.executeQuery();

instead of

ResultSet rs = stm.executeQuery(sq);