为什么我的JDBC更新不起作用? [英] Why is my JDBC update not working?
问题描述
我已经从PostgreSQL 9.1迁移到Oracle 11gR2，遇到了一个奇怪的问题.
I've been migrating from PostgreSQL 9.1 to Oracle 11gR2 and have run into an odd issue.
当客户添加到我们的ERP系统中时，我一直在运行用于更新表的代码.自三月以来，该代码已在PostgreSQL上运行，没有任何问题.现在，我切换到Oracle，不再更新相同的代码.
I've been running code that updates a table when a customer has been added to our ERP system. This code has been running on PostgreSQL with no issues since March. Now that I'm switching to Oracle the same code is no longer updating.
原始代码
update = "UPDATE store SET added_customer = 'y' WHERE order_id = ?";
try {
PreparedStatement pStmnt = getConn().prepareStatement(update);
pStmnt.setString(1, orderId);
results = pStmnt.executeUpdate();
if (results > 0) {
added = true;
}
} catch (SQLException ex) {
LOGGER.error(ex.toString());
}
没有引发异常，但没有更改任何数据，所以我认为自动提交一定不能正常工作，让我们手动提交":
No exceptions were thrown, but no data changed so I thought "autocommit must not be working, lets commit manually":
新代码
update = "UPDATE shop_ca_orders SET added_customer = 'y' WHERE order_id = ?";
try {
getConn().setAutoCommit(false); //Added
PreparedStatement pStmnt = getConn().prepareStatement(update);
pStmnt.setString(1, orderId);
results = pStmnt.executeUpdate();
if (results > 0) {
added = true;
getConn().commit(); //Added
getConn().setAutoCommit(true); //Added
}
} catch (SQLException ex) {
LOGGER.error(ex.toString());
}
仍然没有运气，所以我在executeUpdate语句之后添加了LOGGER.debug("Update to order returned {}",results);，发现我返回0，因此没有记录正在更新.
Still no luck so I added LOGGER.debug("Update to order returned {}",results); after the executeUpdate statement and found I am returning 0, so no records are being updated.
有趣的是，所以我尝试通过SQL Developer查询并正确更新了查询.这使我想到了我的问题:
Interesting, so I tried the query via SQL Developer and it updated correctly. This brings me to my question:
为什么我无法通过JDBC更新数据库?
基本数据
-
order_id是类型VARCHAR(255 BYTE). - Java 7
- 在Amazon RDS上运行的Oracle 11GR2
- 已从在Heroku上运行的PostgreSQL 9.1迁移起
- 在PostgreSQL
order_id上是character varying(255)
order_idis typeVARCHAR(255 BYTE).- Java 7
- Oracle 11GR2 running on Amazons RDS
- Migrated from PostgreSQL 9.1 running on Heroku
- On PostgreSQL
order_idis acharacter varying(255)
编辑
未检测到小的模式更改，并导致了一个错误，该错误中的订单ID实际上是该人的姓名，而不是该订单ID.我的骨头错误.无论如何，现在我已经解决了问题并提取了正确的订单ID，我发现我挂在executeUpdate上了.目前正在处理该问题.如果无法解决，我可能会提出一个新问题.
A small schema change was undetected and resulted in a bug where the order ID was actually the name of the person, and not the order ID. Bonehead error on my end. Anyways, now that I have that resolved and pulling the correct order ID I have found that I am hanging on executeUpdate. Currently working on that issue. I'll likely create a new question if I am unable to resolve.
推荐答案
所以我的问题有两个方面:
So my issue was two fold:
首先，我的初始结果集存在问题.在迁移期间，删除了一列，我以为所有引用的列的代码都已更改，但是缺少一列.解决此问题后，订单ID便可以正常工作，并尝试更新数据库.
First I had an issue with my initial result set. During the migration one column was removed, I thought all the referenced columns were changed in code, however one was missing. Once this issue was resolved the order ID worked accurately and it attempted to update the database.
第二个问题是数据库挂在更新上的问题.事实证明，挂起是由SQL Developer在数据库(或表，不确定是哪个)上持有锁引起的-一旦我关闭SQL Developer，更新立即完成，一切按预期进行.
The second issue was a problem with the database hanging on the update. It turns out that the hang was caused by SQL Developer holding a lock on the database (or table, not sure which) - once I closed SQL Developer the update immediately completed and things went as expected.
这篇关于为什么我的JDBC更新不起作用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
