How to insert value into table for given condition using Java servlet?

Question

I am working on Hotel reservation project, I made two tables Hotel and RoomType, Table RoomType has different types of rooms, now I am registering the hotel in hotel table and every hotel have different type of rooms so for that hotel I am trying to insert number of different type of rooms it has, into the separate table room using hotel_id as a foreign key. But My code is not working for inserting the number of different room types that hotel have, In the following java code first I register the hotel, if it is successful insert information of hotel in to hotel then for that hotel I am trying to insert number of different number type of room type into table RoomType, But it is not successful, I am getting error on browser as followes

You are successfully registered ...com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where hotel_id='16'' at line 1

**Hotel Table**
CREATE TABLE hotel (
  hotel_id varchar(100) NOT NULL,
  name varchar(100) NOT NULL,
  city varchar(100) NOT NULL,
  address text NOT NULL,
  Locality tinytext NOT NULL,
  PRIMARY KEY (hotel_id)
) 

**RoomType Table**
CREATE TABLE roomtype (
  hotel_id varchar(100) NOT NULL,
  roomtype1 varchar(100) UNIQUE,
  roomtype2 varchar(100) UNIQUE,
  roomtype3 varchar(100) UNIQUE,
  roomtype4 varchar(100) UNIQUE,
  FOREIGN KEY (hotel_id) REFERENCES hotel (hotel_id)
)

Javacode

try
    {
        Class.forName("com.mysql.jdbc.Driver");
        Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/hotelres","root","1234");
        PreparedStatement ps=con.prepareStatement("insert into hotel(hotel_id,name,city,address,Locality ) values('"+hotel_id+"','"+name+"','"+city+"','"+address+"','"+locality+"')");

        //PreparedStatement ps=con.prepareStatement("insert into registeruser(Name, Password) values('"+n+"','"+p+"')");
        int i=ps.executeUpdate();            
        if(i>0){
            out.print("You are successfully registered ...");
            ps=con.prepareStatement("insert into roomtype(hotel_id,roomtype1,roomtype2,roomtype3,roomtype4) values('"+hotel_id+"','"+room_type1+"','"+room_type2+"','"+room_type3+"','"+room_type4+"')where hotel_id="+"'"+hotel_id+"'");
            int j=ps.executeUpdate();
            if(j>0)
               out.print("ou are successfully registered ...\n");  
            else
                out.print("You are not able to registere ...\n");
        }
        else
            out.print("You are not able to registere ...");

SMA · Accepted Answer · 2014-12-19 12:54:21Z

3

You cant use where clause in insert statement. If you wish to modify a row where hotel id is 16 then consider using update statement.

Side note - Your SQL is vulnerable to SQL code injection

answered Dec 19, 2014 at 12:54

SMA

37.2k8 gold badges53 silver badges76 bronze badges

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

Comments

Lux · Accepted Answer · 2014-12-19 12:59:24Z

Your problem is in line:

ps=con.prepareStatement("insert into roomtype(hotel_id,roomtype1,roomtype2,roomtype3,roomtype4) values('"+hotel_id+"','"+room_type1+"','"+room_type2+"','"+room_type3+"','"+room_type4+"')where hotel_id="+"'"+hotel_id+"'");

You can't use where in insert clause. Just pass this as a parameter in values.

"insert into roomtype(hotel_id,roomtype1,roomtype2,roomtype3,roomtype4) values('"+hotel_id+"','"+room_type1+"','"+room_type2+"','"+room_type3+"','"+room_type4+"')"

BTW: passing variables by concatenation is bad practice and can provide to SQL Injection. Use instead parametralized query like here: http://www.mkyong.com/jdbc/jdbc-preparestatement-example-insert-a-record/

Collectives™ on Stack Overflow

How to insert value into table for given condition using Java servlet?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related