java mysql中如何防止向数据库中插入重复数据

Question

我正在为一家餐厅开发一个餐桌预订系统。 如果数据库中已经保存了日期、表号和预订时间，我想显示一条错误消息

            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);
        }

请尽快给我一个答案

Answer 1

可以使表列唯一。对于您的情况，它将是date、table_no和time_from。您可以使用以下SQL语法：

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

但是，由于您的查询是SELECT，因此我建议您将查询更改为以下内容：

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

如果rs.next是TRUE，那么SQL记录中有重复项