javamysql中如何防止重复数据插入数据库

beq87vna  于 2021-06-20  发布在  Mysql
关注(0)|答案(1)|浏览(322)

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

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

请尽快给我一个答案。。。

tp5buhyn

tp5buhyn1#

可以使表列唯一。就你的情况来说 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.nextTRUE ,则sql记录中有重复项。

相关问题