Java SQL – 仅插入新条目到表格中

huangapple go评论83阅读模式
英文:

Java SQL - Insert into table only new entries

问题

我正在尝试向我的数据库中插入新条目,但只想插入新条目。如果我要添加的带有已存在于数据库中的 CRN 的课程,则希望跳过它以避免重复。

以下是我目前的代码。我已经尝试了几种不同的方法,但始终会收到异常:

java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'EXCEPT crn' at line 1

在没有 "EXCEPT crn" 的情况下,数据库条目可以正常工作,但是会添加重复条目。

try {
    String query = null;
    try {
        query = "INSERT INTO Classes (crn, subject, creditHours, title, capacity, instructor, schedule) "
                + "VALUES (?, ?, ?, ?, ?, ?, ?) EXCEPT crn";
    } catch(Exception e) {
        conn.close();
    }

    PreparedStatement preparedStmt = conn.prepareStatement(query);
    preparedStmt.setInt(1, crn);
    preparedStmt.setString(2, subject);
    preparedStmt.setInt(3, creditHours);
    preparedStmt.setString(4, title);
    preparedStmt.setString(5, capacity);
    preparedStmt.setString(6, instructor);
    preparedStmt.setString(7, schedule);
    preparedStmt.executeUpdate();
} catch (SQLException e) {
    e.printStackTrace();
}
英文:

I am trying to insert new entries into my database, but only the new entries. If a class with the crn that I am adding already exists in the database then I would like to skip it to not have duplicates.

Below is the code I have right now. I have tried a few different methods but I keep getting exception:

java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'EXCEPT crn' at line 1

The database entry works fine without the "EXCEPT crn", but again it adds duplicates.

try {
        String query = null;
        try {
            query = "INSERT INTO Classes (crn, subject, creditHours, title, capacity, instructor, schedule) "
                    + "VALUES (?, ?, ?, ?, ?, ?, ?) EXCEPT crn";
        } catch(Exception e) {
            conn.close();
        }

        PreparedStatement preparedStmt = conn.prepareStatement(query);
        preparedStmt.setInt(1, crn);
        preparedStmt.setString(2, subject);
        preparedStmt.setInt(3, creditHours);
        preparedStmt.setString(4, title);
        preparedStmt.setString(5, capacity);
        preparedStmt.setString(6, instructor);
        preparedStmt.setString(7, schedule);
        preparedStmt.executeUpdate();
    } catch (SQLException e) {
        e.printStackTrace();
    }

答案1

得分: 1

> 如果我正在添加的课程(使用CRN)在数据库中已经存在,那么我希望跳过它,以避免重复。

在MySQL中,我建议使用 insert ... on duplicate key 语法

INSERT INTO Classes (crn, subject, creditHours, title, capacity, instructor, schedule)
VALUES (?, ?, ?, ?, ?, ?, ?)
ON DUPLICATE KEY UPDATE crn = VALUES(crn);

为了使此方法生效,您需要在 crn 列上创建一个唯一约束(或类似的约束):

ALTER TABLE Classes ADD CONSTRAINT cs_classes_uniq_crn UNIQUE(crn);

然后,当执行一个会生成重复 crnINSERT 操作时,查询会进入到 UPDATE 子句,实际上执行一个无操作操作。

英文:

> If a class with the crn that I am adding already exists in the database then I would like to skip it to not have duplicates.

In MySQL, I would recommend the insert ... on duplicate key syntax:

INSERT INTO Classes (crn, subject, creditHours, title, capacity, instructor, schedule)
VALUES (?, ?, ?, ?, ?, ?, ?)
ON DUPLICATE KEY UPDATE crn = VALUES(crn);

For this to work, you need a unique constraint (or the like) on column crn:

ALTER TABLE Classes ADD CONSTRAINT cs_classes_uniq_crn UNIQUE(crn);

Then, when an INSERT occurs that would generate a duplicate crn, the query goes to the UPDATE clause, that actually performs a no-op.

答案2

得分: 0

可以修改您想要的行吗?您可以在它们上面添加唯一约束,以便它们无法接受具有相同值的列。

ALTER TABLE table_name
ADD UNIQUE (column_name);

如果您需要多列,可以像这样将约束添加到表中:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column1, column2, 等等);
英文:

Can you alter the row or rows you want? You could just put a unique constraint on them so they can't accept columns that have the same value.

ALTER TABLE table_name
ADD UNIQUE (column_name);

If you need multiple columns you can add the constraint to the table like this:
ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column1, column2, etc);

答案3

得分: 0

这是翻译后的内容:

尝试执行的 SQL 语句无效,因为 MySql 不支持 EXCEPT。<br/>
如果您想要跳过已经存在的行的插入,您可以使用 INSERT IGNORE

query = &quot;INSERT IGNORE INTO Classes (crn, subject, creditHours, title, capacity, instructor, schedule) &quot;
        + &quot;VALUES (?, ?, ?, ?, ?, ?, ?)&quot;;

但是为了使其生效,列 crn 应该有一个唯一约束。<br/>看起来它是表的 PRIMARY KEY,所以已经是唯一的。<br/>
如果列 crn 没有唯一约束,您可以像这样使用 NOT EXISTS

query = &quot;INSERT INTO Classes (crn, subject, creditHours, title, capacity, instructor, schedule) &quot;
        + &quot;SELECT ?, ?, ?, ?, ?, ?, ? &quot;
        + &quot;WHERE NOT EXISTS (SELECT 1 FROM Classes WHERE crn = ?)&quot;;

因此,您需要将 Prepared Statement 的第 8 个参数再次设置为 crn

preparedStmt.setInt(8, crn);
英文:

The SQL statement that you try to execute is invalid because MySql does not support EXCEPT.<br/>
Since you want to skip the insertion of rows that already exist you can use INSERT IGNORE:

query = &quot;INSERT IGNORE INTO Classes (crn, subject, creditHours, title, capacity, instructor, schedule) &quot;
        + &quot;VALUES (?, ?, ?, ?, ?, ?, ?)&quot;;

but for this to work there should be a unique constraint for the column crn.<br/>It seems like it is the PRIMARY KEY of the table so it is already unique.<br/>
If there isn't a unique constraint for the column crn you can use NOT EXISTS like this:

query = &quot;INSERT INTO Classes (crn, subject, creditHours, title, capacity, instructor, schedule) &quot;
        + &quot;SELECT ?, ?, ?, ?, ?, ?, ? &quot;
        + &quot;WHERE NOT EXISTS (SELECT 1 FROM Classes WHERE crn = ?)&quot;;

so you will have to pass as the 8th parameter of the Prepared Statement crn again:

preparedStmt.setInt(8, crn);

huangapple
  • 本文由 发表于 2020年4月7日 05:16:20
  • 转载请务必保留本文链接:https://go.coder-hub.com/61069118.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定