英文:
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);
然后,当执行一个会生成重复 crn
的 INSERT
操作时,查询会进入到 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 = "INSERT IGNORE INTO Classes (crn, subject, creditHours, title, capacity, instructor, schedule) "
+ "VALUES (?, ?, ?, ?, ?, ?, ?)";
但是为了使其生效,列 crn
应该有一个唯一约束。<br/>看起来它是表的 PRIMARY KEY
,所以已经是唯一的。<br/>
如果列 crn
没有唯一约束,您可以像这样使用 NOT EXISTS
:
query = "INSERT INTO Classes (crn, subject, creditHours, title, capacity, instructor, schedule) "
+ "SELECT ?, ?, ?, ?, ?, ?, ? "
+ "WHERE NOT EXISTS (SELECT 1 FROM Classes WHERE crn = ?)";
因此,您需要将 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 = "INSERT IGNORE INTO Classes (crn, subject, creditHours, title, capacity, instructor, schedule) "
+ "VALUES (?, ?, ?, ?, ?, ?, ?)";
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 = "INSERT INTO Classes (crn, subject, creditHours, title, capacity, instructor, schedule) "
+ "SELECT ?, ?, ?, ?, ?, ?, ? "
+ "WHERE NOT EXISTS (SELECT 1 FROM Classes WHERE crn = ?)";
so you will have to pass as the 8th parameter of the Prepared Statement crn
again:
preparedStmt.setInt(8, crn);
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论