Java and MySQL query Date

Question

My Java code is this:

public void FillCombo_Disp(){
    java.sql.Date fromDate = new java.sql.Date(jDateChooser_Date1.getDate().getTime());
    java.sql.Date toDate= new java.sql.Date(jDateChooser_Date2.getDate().getTime());
    String space = jComboBox_Space.getSelectedItem().toString();
    String [] space_split = space.split(" ");
    String id_space = space_split[0];
    String query = "SELECT ID FROM Rent WHERE ID_Space = '" + id_space + "' AND Date1 BETWEEN ? AND ? OR Dat2 BETWEEN ? AND ?";
    try {
        pst = conn.Connection().prepareStatement(query);
        pst.setDate(1, fromDate);
        pst.setDate(2, toDate);
        pst.setDate(3, fromDate);
        pst.setDate(4, toDate);

        ResultSet res = pst.executeQuery();
        int ID = 0;
        while(res.next()) {
            ID = res.getInt("ID");
        }

        if(ID!=0)
            jTextField_Disp.setText("Rented");
        else
            jTextField_Disp.setText("Free");
    } catch(Exception e) {
        JOptionPane.showMessageDialog(null, e);
    }
}

My function is to control two dates entered by a user and compare them to the database. If the dates are within the range of time you should give me back the id_rent. But I don't understand why cannot enter in while loop:

while(res.next()) {
    ID = res.getInt("ID");
}

Help me!

Could you clarify what you mean by "why cannot enter in while loop:"? — dbillz
– dbillz, Commented Jul 10, 2015 at 13:51
Your SQL statement is suspect. In MySQL, the AND operator has a higher order of precedence than the OR operator. e.g. "fee AND fi OR fo" is equivalent to "(fee AND fi) OR fo". I suspect you want to add some parens: "fee AND (fi OR fo)'. I'm also curious why the value of id_space is included as a literal in the SQL text, rather than a bind placeholder (like the date values.) I suggest that you verify that your SQL statement is working correctly (from another client). — spencer7593
– spencer7593, Commented Jul 10, 2015 at 13:59
The while loop is used to determine if my question query and I can return the data. I checked with MySQL workbench my query. — Simone94
– Simone94, Commented Jul 10, 2015 at 18:29

Jmrapp · Accepted Answer · 2015-07-10 13:51:11Z

2

You made a spelling mistake in your query so it returns no rows. You put the word "Dat2" when i believe it should be "Date2".

Current:

String query = "SELECT ID FROM Rent WHERE ID_Space = '" + id_space + "' AND Date1 BETWEEN ? AND ? OR Dat2 BETWEEN ? AND ?";

New:

String query = "SELECT ID FROM Rent WHERE ID_Space = '" + id_space + "' AND Date1 BETWEEN ? AND ? OR Date2 BETWEEN ? AND ?";

answered Jul 10, 2015 at 13:51

Jmrapp

5304 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Simone94 Over a year ago

Thanks Baseball435 :) I corrected the mistake, but it doesn't work the same :(

Collectives™ on Stack Overflow

Java and MySQL query Date

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related