Control data in the database with Java

Question

Good morning, I have a problem with a web application I'm creating. Premtto I'm quite new in this kind of programming. So my web application should take care of booking hotel rooms. My problem is going to make a check when someone tries to book a room, so that you can not book two equal rooms in the same period. Only that with the code written the check is not performed and the room is still booked. How could I do according to you? Thank you very much for your help.

try {

    Class.forName("com.mysql.cj.jdbc.Driver");
    //  out.println("driver loaded");
    Connection  con = DriverManager.getConnection("jdbc:mysql://localhost:3306/Hotel?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC", "root", "123456789");
    out.println("Connect");
    Statement  st = con.createStatement();
    Statement stmt = con.createStatement();
    out.println("connection successfull");

    String check = ("SELECT res1.id_prenotazione, res1.typeroom, res1.arrivaldate, res1.departuredate\n" +
        "FROM reservation res1, reservation res2\n" +
        "WHERE ( res1.typeroom = res2.typeroom ) \n" +
        "AND res1.id_prenotazione != res2.id_prenotazione\n" +
        "AND (res1.arrivaldate <= res2.departuredate)\n" +
        "AND (res2.arrivaldate <= res1.departuredate)");


    // ResultSet rs2  = stmt.executeQuery(check);
    out.println("<h1> Stringa chek eseguita </h1>");

    if (check) { // THIS DOESN't WORK OF COURSE
        int rs = st.executeUpdate("insert into reservation (login,email,typeroom,numroom,arrivaldate,departuredate)values ('" + login + "','" + email + "','" + typeroom + "','" + numroom + "','" + arrivaldate + "','" + departuredate + "')");
    }
    String getResultSet = ("SELECT count(*) FROM reservation WHERE arrivaldate ='" + arrivaldate + "'");
    String rs1 = ("SELECT count(*) FROM reservation WHERE arrivaldate");


    if (getResultSet != rs1) {
        int i = st.executeUpdate("DELETE FROM reservation WHERE id_prenotazione ='" + id_prenotazione + "'");

    }

TABLE reservation ( id_prenotazione int(11) NOT NULL AUTO_INCREMENT,
login varchar(45) NOT NULL,
email varchar(45) NOT NULL,
typeroom varchar(45) NOT NULL,
numroom int(11) NOT NULL,
arrivaldate datetime NOT NULL,
departuredate datetime NOT NULL,
PRIMARY KEY (id_prenotazione)

TABLE users (
login varchar(45) NOT NULL,
firstname varchar(45) NOT NULL,
lastname varchar(45) NOT NULL,
password varchar(45) NOT NULL,
PRIMARY KEY (login)

if(check) isn’t working... but check is a String? Do you not want a boolean? — achAmháin
– achAmháin, Commented Aug 31, 2018 at 10:53
@achAmháin I have tried also with boolean : Boolean check = (.....) != NULL and then IF (check) {... } But it is the same — user10138349
– user10138349, Commented Aug 31, 2018 at 10:55
You've commented out the execute query just before the out.println() statement, thats why check will probably return false — Kebab Programmer
– Kebab Programmer, Commented Aug 31, 2018 at 13:14

Joakim Danielson · Accepted Answer · 2018-08-31 15:05:27Z

0

You are using a join with the same table in your check SELECT but nothing has been inserted yet so the query is pointless. You need to check against the values for the reservation you want to store.

SELECT COUNT(*) 
FROM reservation 
WHERE numroom = $roomnumber -- I am guessing numroom is room number
  AND ($startdate >= arrivaldate AND $startdate <= departuredate)
   OR ($enddate >= arrivaldate AND $enddate <= departuredate)

Replace the variables ($...) with your values. You might want to look into how to validate dates against each other, maybe it should be <, > rather than <=, >= in the comparison.

In Java this would be

String query = "SELECT COUNT(*) FROM reservation WHERE numroom = '" + numroom + "' AND ('" + arrivaldate + "' >= arrivaldate AND '" + arrivaldate + "' <= departuredate) OR ('" + departuredate + "' >= arrivaldate AND '" + departuredate + "' <= departuredate)

edited Aug 31, 2018 at 15:05

answered Aug 31, 2018 at 11:10

Joakim Danielson

53.1k5 gold badges36 silver badges79 bronze badges

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

10 Comments

user10138349 Over a year ago

Thanks for the answer! What do you mean with "your values"?

user10138349 Over a year ago

i don't have the variables because I insert them at the moment of booking

Joakim Danielson Over a year ago

@Jack23 When you do the INSERT in your code you have the variables I am talking about, use them when checking if a reservation exists. I have updated the question

user10138349 Over a year ago

so you don't suggest to use Boolean but String, and what can i put in the if statement? if(query...)

Joakim Danielson Over a year ago

You have to execute the query against the database of course and look at the result, if count > 0 then you already have a reservation

|

Collectives™ on Stack Overflow

Control data in the database with Java

1 Answer 1

10 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related