How to prevent inserting duplicate data into database in java mysql

Question

I am developing a table booking system for a restaurant. I want to display an error message if the date, table number and the time from which I'll be booking is already saved in the database.

            if((TableCombo.getSelectedItem().toString().isEmpty()) || CusText.getText().isEmpty()){
        JOptionPane.showMessageDialog(null,"Please fill all the required data.");
    }
    else{
        //Inserting to the DB
        tableNum = TableCombo.getSelectedItem().toString();
        date = ((JTextField)DateChoose.getDateEditor().getUiComponent()).getText();
        from = FromCombo.getSelectedItem().toString();

        try{
            {
            Statement stat = conn.createStatement();
            String select = "SELECT Table_No, Date, Time_From, COUNT(*) FROM bookings WHERE Table_No = '"+tableNum+"' AND Date = '"+date+"' AND Time_From = '"+from+"' GROUP BY Date, Table_No, Time_From HAVING COUNT(*) > 1";

            //System.out.println(select);
            ResultSet rs = stat.executeQuery(select);
            System.out.println(rs.next());

            if(rs.next() == true)
            {
            JOptionPane.showMessageDialog(null,"Already Booked!", "Error", JOptionPane.ERROR_MESSAGE);

            }

            else{
            PreparedStatement pst = conn.prepareStatement("INSERT INTO bookings (Table_No, Customer_Id, Date, Time_From, Time_To) values (?,?,?,?,?)");
            pst.setString(1, TableCombo.getSelectedItem().toString());
            pst.setString(2, CusText.getText());
            pst.setString(3, date);
            pst.setString(4, FromCombo.getSelectedItem().toString());
            pst.setString(5, ToCombo.getSelectedItem().toString());

            pst.executeUpdate();
            //Validation
            JOptionPane.showMessageDialog(null, "Successfully Added!"); 
            }
        }
            ////Keeping the Text Fields empty
            TableCombo.setSelectedIndex(0);
            CusText.setText("");
            ToCombo.setSelectedIndex(0);
            FromCombo.setSelectedIndex(0);
        }

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

Please give me an answer quickly...

On a side note your current implementation is prone to [SQL Injection](https://stackoverflow.com/questions/1582161/how-does-a-preparedstatement-avoid-or-prevent-sql-injection) — shinjw, Oct 02 '18 at 06:20

adrianriyadi · Answer 1 · 2018-10-02T06:37:08.900

0

You can make your table column to be unique. For your case it will be date, table_no, and time_from. You can do it with this SQL syntax:

ALTER TABLE bookings ADD UNIQUE INDEX(date,table_no,time_from);

But since your query is SELECT, then I suggest you to change your query a little bit to this:

SELECT date, table_no, time_from, COUNT(*)
FROM bookings
WHERE date = date AND table_no = tableNum AND time_from = from
GROUP BY date, table_no, time_from
HAVING COUNT(*) > 1

if your rs.next is TRUE, then there's duplicate item on your SQL record.

edited Oct 02 '18 at 06:37

answered Oct 02 '18 at 06:15

adrianriyadi

385
1
7
17

Thanks for friendly reminder. I edited it already. I hope it can solve his problem. @Rob – adrianriyadi Oct 02 '18 at 06:39

How to prevent inserting duplicate data into database in java mysql

1 Answers1