如何在插入前检查数据库的重复项？

Question

在插入到数据库之前，我想检查数据库中是否有重复项。只有当plateNo、driverID和resDate匹配时，它才被认为是重复的。

下面是我如何获取将要插入到数据库中的数据

 private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {                                         

        String client = (String) clientCmb.getSelectedItem();
        String[] cparts = client.split("-");
        String cpart = cparts[0];

        String driver = (String) driverCmb.getSelectedItem();
        String[] dparts = driver.split("-");
        String dpart = dparts[0];

        String van = (String) vanCmb.getSelectedItem();

        java.util.Date oDate = jXDatePicker2.getDate();
        DateFormat oDateFormat = new SimpleDateFormat("yyyy-MM-dd");
        String date = oDateFormat.format(oDate);
        model2.addRow(cpart, dpart, van, date);


    } 

下面是我的addRow方法的代码

public void addRow(String client, String driver, String van, String res){

        try {
            String sqlRes =  "Select * from reservation";
            rs = st.executeQuery(sqlRes);
            rs.moveToInsertRow();

            rs.updateString("clientID", client);
            rs.updateString("plateNo", van);
            rs.updateString("driverID", driver);
            rs.updateString("resDate", res);
            rs.insertRow();
            rs.moveToCurrentRow();
            rs = st.executeQuery(sqlRes);
            this.fireTableDataChanged();
        } catch (SQLException ex) {
            Logger.getLogger(MyModel2.class.getName()).log(Level.SEVERE, null, ex);
        }

Answer 1

让数据库为您完成这项工作。定义唯一索引/约束条件，指定这三个值在表中是唯一的：

create unique index unq_reservation_3 on reservation(plateNo, driverID, resDate);

如果您试图插入副本--或者执行导致副本的update --那么数据库将返回一个错误。您只需捕获错误即可。

Answer 2

使用MERGE语句：T-SQL或ORACLE，或者对于MySQL：

PreparedStatement p = con.prepareStatement("
    INSERT INTO reservation tgt (clientID, plateNo, driverID, resDate)
    SELECT (? As clientID, ? As plateNo, ? As driverID, ? As resDate)
    FROM DUAL ins
    LEFT JOIN reservation ref
        ON  ref.resDate = ins.resDate
       AND (ref.plateNo = ins.plateNo OR ref.driverID = ins.driverID)
    WHERE ref.clientID IS NULL;
");
p.setString(1, client);
p.setString(2, van);
p.setString(3, driver);
p.setString(4, res);
return p.executeUpdate(); /* 1 - Success | 0 - Ignored Duplicate */