Checking room availability using JTable and JCalendar throws SQL exception

Question

The program I wrote checks for rooms available in a hotel. I used JCalendar for the user to enter the dates and JTable to display the rooms available. The problem is that when I click on Check Availability button, it's throwing an SQL Exception.

Here are the code snippets:

public class Room_Availability extends JFrame {

//Note: Didn't add codes about instantiating panels, setting layouts and all

JLabel arr_date= new JLabel("Arrival Date:");
JLabel dep_date= new JLabel("Departure Date:");
JButton chk_btn= new JButton("Check Availability");
JDateChooser arrival = new JDateChooser();
JDateChooser departure = new JDateChooser();
JTable CheckAvail= new JTable();

Room_Availability(){

chk_btn.addActionListener(new ActionListener() {
       public void actionPerformed(ActionEvent evt) {
           CheckAvailActionPerformed(evt);
       }
   });

  }


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


   ResultSet rs = null;
    Connection conn = null;
    Statement stmt = null;
    try {
        // new com.mysql.jdbc.Driver();
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        String connectionUrl = "jdbc:mysql://localhost:3306/testing?autoReconnect=true&useSSL=false";
        String connectionUser = "root";
        String connectionPassword = "admin";
        conn = DriverManager.getConnection(connectionUrl, connectionUser,
                connectionPassword);
        stmt = conn.createStatement();

        String query= "select * from room as ro" +
                 "where ro.room_id not in "+
                " ("+
                   "select re.room_no"+
                  " from testing.booking as re "+
                   "where (arrival_date >= ? and departure_date < ?)"+
                     "or (departure_date >= ? and arrival_date < ?)"+
                 " )";

        PreparedStatement pStmt = (PreparedStatement) conn.prepareStatement(query);

        java.util.Date utilDate = arrival.getDate();
        java.sql.Date sqlDate = new java.sql.Date(utilDate.getTime());   

        java.util.Date utilDate1 = departure.getDate();
        java.sql.Date sqlDate1 = new java.sql.Date(utilDate1.getTime()); 

        pStmt.setDate(1, sqlDate);
        pStmt.setDate(2, sqlDate1);
        pStmt.setDate(3, sqlDate);
        pStmt.setDate(4, sqlDate1);


        rs = pStmt.executeQuery();

        CheckAvail.setModel(DbUtils.resultSetToTableModel(rs));


    } catch (Exception e) {
        e.printStackTrace();
    }      
  }   
}

Here is how the GUI looks like:

When I execute the query(with the same dates as I enter on the JCalendar) on MySQL database this is what is displayed and should be displayed on the JTable:

Here are the errors I am getting:

There are 4 question marks in your query, meaning it requires 4 parameters; you only specify 2 parameters. — Andy Turner, Apr 05 '16 at 13:42
I commented out the line `"or (departure_date >= ? and arrival_date < ?)"+` but I am now getting `MySQLSyntaxErrorException` — Tia, Apr 05 '16 at 13:47

score 2 · Accepted Answer · answered Apr 05 '16 at 13:46

2

I think You should set parameter for each '?' in the query. There are four '?' placeholders but You set only two of them

    pStmt.setDate(1, sqlDate);
    pStmt.setDate(2, sqlDate1);

It should be something like this

pStmt.setDate(1, sqlDate);
pStmt.setDate(2, sqlDate1);
pStmt.setDate(3, sqlDate1);
pStmt.setDate(4, sqlDate);

answered Apr 05 '16 at 13:46

Wojciech Wirzbicki

3,887
6
36
59

Thanks, I just tried it but it threw `MySQLSyntaxErrorException` – Tia Apr 05 '16 at 13:49
@Diksha, can you edit your questiowith the code you have after the attempt to fix it based on this answer? – Lajos Arpad Apr 05 '16 at 13:51
Note: The query should actually be like this for it to work accordingly: `pStmt.setDate(3, sqlDate); pStmt.setDate(4, sqlDate1);` – Tia Apr 05 '16 at 14:06

Wojciech Wirzbicki · Answer 2 · 2016-04-05T13:55:40.870

1

There is missing space in lines:

"where (arrival_date >= ? and departure_date < ?)"+
"select * from room as ro" +

Just add one space at the end of the string

"where (arrival_date >= ? and departure_date < ?) "+
"select * from room as ro " +

edited Apr 05 '16 at 13:55

answered Apr 05 '16 at 13:51

Wojciech Wirzbicki

3,887
6
36
59

It still threw `MySQLSyntaxErrorException` – Tia Apr 05 '16 at 13:53
i edited my answer, there is another line without space, better check them all – Wojciech Wirzbicki Apr 05 '16 at 13:56

Checking room availability using JTable and JCalendar throws SQL exception

2 Answers2