[ Insert multiple rows in MySQL ]

I am trying to insert the details below from the jtextfields into the database. Columns to be inserted are roomNumberSelected,guestName,passportNo,phoneNo in Guest table.

Currently when it compiles, it only inserts the final row of data. I am implementing three-tier architecture in my application. I believe I have to alter my SQL statement to insert multiple values but I'm unsure on how to go about that.

UI - GuestDetails.java

No. of rooms needed is equivalent to noOfRoomsSelected. In this case, noOfSelectedRooms = 5;

if(noOfRoomsSelected=="1"){

                if(name1.isEmpty()||passportNo1.isEmpty()||phoneNo1.isEmpty()){
                    JOptionPane.showMessageDialog(null, "Some Required field(s) are not entered. Please try again.");
                }

                else{
                g.setSelectedRoomNum(comboBox1.getSelectedItem().toString());
                g.setGuestPassportNo(tfPassportNo1.getText());
                g.setGuestName(tfGuestName1.getText());
                g.setGuestPhoneNo(tfPhoneNo1.getText());
                }
            }

            else if(noOfRoomsSelected=="2"){

                if(name1.isEmpty()||passportNo1.isEmpty()||phoneNo1.isEmpty()
                        ||name2.isEmpty()||passportNo2.isEmpty()||phoneNo2.isEmpty()){

                    JOptionPane.showMessageDialog(null, "Some Required field(s) are not entered. Please try again.");
                }

                else{
                g.setSelectedRoomNum(comboBox1.getSelectedItem().toString());
                g.setGuestPassportNo(tfPassportNo1.getText());
                g.setGuestName(tfGuestName1.getText());
                g.setGuestPhoneNo(tfPhoneNo1.getText());
                g.setSelectedRoomNum(comboBox2.getSelectedItem().toString());
                g.setGuestPassportNo(tfPassportNo2.getText());
                g.setGuestName(tfGuestName2.getText());
                g.setGuestPhoneNo(tfPhoneNo2.getText());
                }
            }

            else if(noOfRoomsSelected=="3"){

                if(name1.isEmpty()||passportNo1.isEmpty()||phoneNo1.isEmpty()
                        ||name2.isEmpty()||passportNo2.isEmpty()||phoneNo2.isEmpty()||
                        name3.isEmpty()||passportNo3.isEmpty()||phoneNo3.isEmpty()){
                    JOptionPane.showMessageDialog(null, "Some Required field(s) are not entered. Please try again.");
                }

                else {

                g.setSelectedRoomNum(comboBox1.getSelectedItem().toString());
                g.setGuestPassportNo(tfPassportNo1.getText());
                g.setGuestName(tfGuestName1.getText());
                g.setGuestPhoneNo(tfPhoneNo1.getText());
                g.setSelectedRoomNum(comboBox2.getSelectedItem().toString());
                g.setGuestPassportNo(tfPassportNo2.getText());
                g.setGuestName(tfGuestName2.getText());
                g.setGuestPhoneNo(tfPhoneNo2.getText());
                g.setSelectedRoomNum(comboBox3.getSelectedItem().toString());
                g.setGuestPassportNo(tfPassportNo3.getText());
                g.setGuestName(tfGuestName3.getText());
                g.setGuestPhoneNo(tfPhoneNo3.getText());
                   }
            }

            else if(noOfRoomsSelected=="4"){
                if(name1.isEmpty()||passportNo1.isEmpty()||phoneNo1.isEmpty()
                        ||name2.isEmpty()||passportNo2.isEmpty()||phoneNo2.isEmpty()||
                        name3.isEmpty()||passportNo3.isEmpty()||phoneNo3.isEmpty()
                        ||name4.isEmpty()||passportNo4.isEmpty()||phoneNo4.isEmpty()){
                    JOptionPane.showMessageDialog(null, "Some Required field(s) are not entered. Please try again.");
                }

                else{
            g.setSelectedRoomNum(comboBox1.getSelectedItem().toString());
            g.setGuestPassportNo(tfPassportNo1.getText());
            g.setGuestName(tfGuestName1.getText());
            g.setGuestPhoneNo(tfPhoneNo1.getText());
            g.setSelectedRoomNum(comboBox2.getSelectedItem().toString());
            g.setGuestPassportNo(tfPassportNo2.getText());
            g.setGuestName(tfGuestName2.getText());
            g.setGuestPhoneNo(tfPhoneNo2.getText());
            g.setSelectedRoomNum(comboBox3.getSelectedItem().toString());
            g.setGuestPassportNo(tfPassportNo3.getText());
            g.setGuestName(tfGuestName3.getText());
            g.setGuestPhoneNo(tfPhoneNo3.getText());
            g.setSelectedRoomNum(comboBox4.getSelectedItem().toString());
            g.setGuestPassportNo(tfPassportNo4.getText());
            g.setGuestName(tfGuestName4.getText());
            g.setGuestPhoneNo(tfPhoneNo4.getText());
                }
            }


            else if(noOfRoomsSelected=="5"){

                if(name1.isEmpty()||passportNo1.isEmpty()||phoneNo1.isEmpty()
                        ||name2.isEmpty()||passportNo2.isEmpty()||phoneNo2.isEmpty()||
                        name3.isEmpty()||passportNo3.isEmpty()||phoneNo3.isEmpty()
                        ||name4.isEmpty()||passportNo4.isEmpty()||phoneNo4.isEmpty()
                        ||name5.isEmpty()||passportNo5.isEmpty()||phoneNo5.isEmpty()){
                    JOptionPane.showMessageDialog(null, "Some Required field(s) are not entered. Please try again.");
                }

                else{
                g.setSelectedRoomNum(comboBox1.getSelectedItem().toString());
                g.setGuestPassportNo(tfPassportNo1.getText());
                g.setGuestName(tfGuestName1.getText());
                g.setGuestPhoneNo(tfPhoneNo1.getText());
                g.setSelectedRoomNum(comboBox2.getSelectedItem().toString());
                g.setGuestPassportNo(tfPassportNo2.getText());
                g.setGuestName(tfGuestName2.getText());
                g.setGuestPhoneNo(tfPhoneNo2.getText());
                g.setSelectedRoomNum(comboBox3.getSelectedItem().toString());
                g.setGuestPassportNo(tfPassportNo3.getText());
                g.setGuestName(tfGuestName3.getText());
                g.setGuestPhoneNo(tfPhoneNo3.getText());
                g.setSelectedRoomNum(comboBox4.getSelectedItem().toString());
                g.setGuestPassportNo(tfPassportNo4.getText());
                g.setGuestName(tfGuestName4.getText());
                g.setGuestPhoneNo(tfPhoneNo4.getText());
                g.setSelectedRoomNum(comboBox5.getSelectedItem().toString());
                g.setGuestPassportNo(tfPassportNo5.getText());
                g.setGuestName(tfGuestName5.getText());
                g.setGuestPhoneNo(tfPhoneNo5.getText());
                }
            }

Entity class - Guest.java

public boolean createGuestRecord(Guest g){
        boolean success = false;
        DBController db = new DBController();
        String dbQuery;

    //step 1 - establish connection to database
        try{
        db.getConnection();     

        dbQuery = "INSERT INTO guest(selectedRoomNum,guestPassportNo,guestName,guestPhoneNo,passNo)";
          dbQuery = dbQuery + " VALUES ('" + selectedRoomNum + "','" + guestPassportNo+ "','" + guestName + "','" + guestPhoneNo + "','" + passNo + "')";




        //step 3 - to insert record using updateRequest method
            if (db.updateRequest(dbQuery) == 1){ //record inserted
                success = true;
            }
        }

        catch(Exception e){
            System.out.println(e);
        }

        //step 4 - close connection
        db.terminate();

        return success;


    }

Answer 1

Of course you have only the last record. Just look at your code:

            g.setSelectedRoomNum(comboBox1.getSelectedItem().toString());
            g.setGuestPassportNo(tfPassportNo1.getText());
            g.setGuestName(tfGuestName1.getText());
            g.setGuestPhoneNo(tfPhoneNo1.getText());

            // >>>>>>>>> Nothing here <<<<<<<<<

            g.setSelectedRoomNum(comboBox2.getSelectedItem().toString());
            g.setGuestPassportNo(tfPassportNo2.getText());
            g.setGuestName(tfGuestName2.getText());
            g.setGuestPhoneNo(tfPhoneNo2.getText());

After all these assignments g has the data for row 2, row 1 data disappeared. So when the inserting starts g has the data from the last record only. Insert the rows after you assign the fields to g.