Cant get records between two dates

Question

My problem is that I can't fetch all records that are between two dates.

I have two JDateChoosers. When I select two dates like '10-apr-2011' to '20-apr-2011' I want all the records between those dates to be displayed in my JList. But I can't get any results in the JList.

I am using mysql database.

  private void Display(java.awt.event.ActionEvent evt) {

    try 
    { 

        Class.forName("com.mysql.jdbc.Driver");  
        Connection con= 
           (Connection)DriverManager.getConnection(
             "jdbc:mysql://localhost:3306/test","root","ubuntu123");   
        java.util.Date jd =  jDateChooser1.getDate();
        java.util.Date jd1 = jDateChooser2.getDate();

        // PreparedStatement stmt = (PreparedStatement) con.prepareStatement("select date from invoice where date = ?);

        // PreparedStatement pstmt = (PreparedStatement) con.prepareStatement("SELECT date FROM invoice WHERE date BETWEEN ' ' AND ' '");

        PreparedStatement pstmt = (PreparedStatement) con.prepareStatement("SELECT date FROM invoice WHERE date >= '+jd + ' AND date <= '+jd1 + '");

        pstmt.execute();  

        ResultSet rs = pstmt.getResultSet();

        int i =0;
        DefaultListModel listModel = new DefaultListModel();

        while(rs.next())
        {   

            String [] data;
            data = new String[100];
            data [i] = rs.getString("date");
            jList1.setModel(listModel);
            listModel.addElement(data [i]);
            i = i+1;

        }
    } 
    catch (Exception e) 
    {
        System.out.println("2nd catch  " + e);
    }        
}

Can anyone tell me where my mistake is? Thanks in advance..

Answer 1

Since this is a PreparedStatement you can try:

PreparedStatement pstmt = (PreparedStatement) con.prepareStatement("SELECT date FROM invoice WHERE date >= ? AND date <= ?");
pstmt.setDate(1,new java.sql.Date(jd.getTime()));
pstmt.setDate(2,new java.sql.Date(jd1.getTime()));
ResultSet rs = pstmt.executeQuery();