I want to know whether is it a good idea to catch exception based on unique index of sql in java.
我想知道基于java中的sql的唯一索引捕获异常是否是一个好主意。
i want to catch an exception like 'duplicate entry for 1-0' if so then handle exception otherwise insert properly in database table?
我想捕获像'重复输入1-0'的异常,如果是这样,然后处理异常,否则在数据库表中正确插入?
3 个解决方案
#1
I say you don't do that, for two reasons:
我说你不这样做,原因有两个:
- the error messages are a bit unclear: ERROR 1062 (23000): Duplicate entry 'xxx' for key 1. Are you always 100% sure which key is 1?
- it locks in you to a specific database vendor
错误消息有点不清楚:ERROR 1062(23000):密钥1的重复条目'xxx'。您是否始终100%确定哪个密钥为1?
它会锁定您到特定的数据库供应商
I find it simpler to transactionally:
我觉得交易更简单:
- check for row's existence;
- throw an exception if the row already exists;
- insert the new row.
检查行的存在;
如果该行已存在则抛出异常;
插入新行。
Performance issues:
I say measure twice, cut once. Profile the usage for your specific use case. Top of my head I would say that the performance will not be an issue except for the heavy db usage scenarios.
我说措施两次,削减一次。描述特定用例的用法。我要说的是,除了繁重的数据库使用场景外,性能不会成为问题。
The reason is that once you perform a SELECT over that specific row, its data will be placed in the database caches and immediately used for insertion check done on the index for the INSERT statement. Also keeping in mind that this access is backed by an index leads to the conclusion that performance will not be an issue.
原因是,一旦在该特定行上执行SELECT,其数据将被放置在数据库缓存中,并立即用于对INSERT语句的索引进行插入检查。还要记住,这种访问由索引支持,这导致结果表明性能不会成为问题。
But, as always, do measure.
但是,像往常一样,做出衡量标准。
#2
I don't see why not. It's probably more efficient than running a query before the insert. It's probably better to catch the exception's error code rather than recognising the error message, though.
我不明白为什么不。它可能比在插入之前运行查询更有效。但是,捕获异常的错误代码可能更好,而不是识别错误消息。
#3
You could use the REPLACE command. It inserts/updates based on the existence of the record. And its atomic too whereas query then insert/update is not. It depends on what do you want to do if you detect the key violation?
您可以使用REPLACE命令。它根据记录的存在插入/更新。它的原子也是查询然后插入/更新不是。这取决于您在检测到密钥违规时想要做什么?
#1
I say you don't do that, for two reasons:
我说你不这样做,原因有两个:
- the error messages are a bit unclear: ERROR 1062 (23000): Duplicate entry 'xxx' for key 1. Are you always 100% sure which key is 1?
- it locks in you to a specific database vendor
错误消息有点不清楚:ERROR 1062(23000):密钥1的重复条目'xxx'。您是否始终100%确定哪个密钥为1?
它会锁定您到特定的数据库供应商
I find it simpler to transactionally:
我觉得交易更简单:
- check for row's existence;
- throw an exception if the row already exists;
- insert the new row.
检查行的存在;
如果该行已存在则抛出异常;
插入新行。
Performance issues:
I say measure twice, cut once. Profile the usage for your specific use case. Top of my head I would say that the performance will not be an issue except for the heavy db usage scenarios.
我说措施两次,削减一次。描述特定用例的用法。我要说的是,除了繁重的数据库使用场景外,性能不会成为问题。
The reason is that once you perform a SELECT over that specific row, its data will be placed in the database caches and immediately used for insertion check done on the index for the INSERT statement. Also keeping in mind that this access is backed by an index leads to the conclusion that performance will not be an issue.
原因是,一旦在该特定行上执行SELECT,其数据将被放置在数据库缓存中,并立即用于对INSERT语句的索引进行插入检查。还要记住,这种访问由索引支持,这导致结果表明性能不会成为问题。
But, as always, do measure.
但是,像往常一样,做出衡量标准。
#2
I don't see why not. It's probably more efficient than running a query before the insert. It's probably better to catch the exception's error code rather than recognising the error message, though.
我不明白为什么不。它可能比在插入之前运行查询更有效。但是,捕获异常的错误代码可能更好,而不是识别错误消息。
#3
You could use the REPLACE command. It inserts/updates based on the existence of the record. And its atomic too whereas query then insert/update is not. It depends on what do you want to do if you detect the key violation?
您可以使用REPLACE命令。它根据记录的存在插入/更新。它的原子也是查询然后插入/更新不是。这取决于您在检测到密钥违规时想要做什么?