在 SQL 中使用错误代码而不是显式检查？
我有一个尝试将行插入表中的查询。由于主键约束失败，查询可能会失败，因为已经存在具有该 ID 的行。
我可以在尝试插入之前检查这一点，但我也可以通过检查 SQLException 的 SQL 状态来检测这一点。这是做此类事情的有效方法吗？或者我应该事先进行明确的检查？
我问的原因是，如果我必须在执行插入之前进行显式检查，我将必须锁定正在检查的表以防止不一致。使用错误代码检查意味着我不必锁定表。
事前检查
try {
con.setAutoCommit(false);
con.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
Statement statement = con.createStatement();
ResultSet rs = statement.executeQuery("SELECT * FROM SomeTable WHERE Id = 123");
if(rs.next()) {
con.setAutoCommit(true);
System.err.println("Entry with Id 123 already exists!");
return;
}
statement = con.createStatement();
statement.executeQuery("INSERT INTO SomeTable(Id) VALUES(123)");
con.commit();
con.setAutoCommit(true);
} catch(SQLException e) {
// These 2 lines actually need to be wrapped in a try/catch too.
con.rollback();
con.setAutoCommit(true);
}
错误代码检查
try {
Statement = con.createStatement();
statement.executeQuery("INSERT INTO SomeTable(Id) VALUES(123)");
} catch(SQLException e) {
if(e.getSQLState().equals("23505"))
System.err.println("Entry with Id 123 already exists!");
}
I have a query which attempts to insert a row into a table. It is possible that the query will fail due to a primary key contraint failing because there is already a row with that ID.
I could check for this before attempting the insert, but I can also detect this by checking the SQL state of an SQLException. Is this a valid way to do this type of thing? Or should I do the explicit check before hand?
The reason I ask is that if I do have to do the explicit check before I perform the insert, I will have to lock the table I am checking from in order to prevent inconsistency. Using the error code checking means I won't have to lock the table.
Check beforehand
try {
con.setAutoCommit(false);
con.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
Statement statement = con.createStatement();
ResultSet rs = statement.executeQuery("SELECT * FROM SomeTable WHERE Id = 123");
if(rs.next()) {
con.setAutoCommit(true);
System.err.println("Entry with Id 123 already exists!");
return;
}
statement = con.createStatement();
statement.executeQuery("INSERT INTO SomeTable(Id) VALUES(123)");
con.commit();
con.setAutoCommit(true);
} catch(SQLException e) {
// These 2 lines actually need to be wrapped in a try/catch too.
con.rollback();
con.setAutoCommit(true);
}
Error code checking
try {
Statement = con.createStatement();
statement.executeQuery("INSERT INTO SomeTable(Id) VALUES(123)");
} catch(SQLException e) {
if(e.getSQLState().equals("23505"))
System.err.println("Entry with Id 123 already exists!");
}
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您的“错误代码检查”方法是有效的，而且确实是惯例。请注意，您还可以解析异常 Message 属性以获取被违反的 SQL 约束的名称，这就是为什么将约束细化是一个好主意（即，创建许多简单约束而不是创建一些复杂约束是错误的）并给每个人一个有意义的名字。
Your "error code checking" approach is valid and indeed is the convention. Note you can also parse the exception Message property for the name of the SQL constraint that was violated, which is why it is a good idea to make constraints granular (i.e. err on the side of creating many simple constraints rather than a few complex ones) and to give each a meaningful name.
就我个人而言，我更喜欢围绕这些事情进行防御性编码。我将按如下方式编写查询：
当然，您必须根据您的 SQL 风格修改该查询。也许：
插入后，您可以测试受影响的行数并确定是否存在 PK 违规。
Personally, I prefer to code defensively around such things. I would write my query as follows:
Of course, you'll have to modify that query depending on your flavor of SQL. Perhaps:
After the insert you can test your affected rowcount and determine whether or not there was a PK violation.
您应该首先通过使用 PK 的序列或自动增量列来避免此类错误。
然而，正确处理 SQLException 仍然是必要的，因为可能会发生各种其他错误。
You should avoid errors like this in the first place by using sequences or autoincrement columns for PKs.
However, proper handling of SQLExceptions is still necessary, as all kinds of other errors might occur.