Java 为什么我的 JDBC 更新不起作用？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19542770/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Why is my JDBC update not working?
提问by Robert H
I've been migrating from PostgreSQL 9.1 to Oracle 11gR2 and have run into an odd issue.
我一直在从 PostgreSQL 9.1 迁移到 Oracle 11gR2，但遇到了一个奇怪的问题。
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.
我一直在运行代码，当客户被添加到我们的 ERP 系统时更新表格。此代码自 3 月以来一直在 PostgreSQL 上运行，没有出现任何问题。现在我切换到 Oracle 相同的代码不再更新。
Original Code
原始代码
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":
没有抛出异常，但没有更改数据，所以我认为“自动提交肯定不起作用，让我们手动提交”：
New Code
新代码
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());
}
Still no luck so I added LOGGER.debug("Update to order returned {}",results);after the executeUpdatestatement and found I am returning 0, so no records are being updated.
仍然没有运气，所以我LOGGER.debug("Update to order returned {}",results);在executeUpdate声明之后添加并发现我返回 0，所以没有更新记录。
Interesting, so I tried the query via SQL Developer and it updated correctly. This brings me to my question:
有趣，所以我通过 SQL Developer 尝试了查询并且它正确更新了。这让我想到了我的问题：
Why am I unable to update my database via JDBC?
为什么我无法通过 JDBC 更新我的数据库？
Essential Data:
基本数据：
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)
order_id是类型VARCHAR(255 BYTE)。- 爪哇 7
- 在 Amazon RDS 上运行的 Oracle 11GR2
- 从运行在 Heroku 上的 PostgreSQL 9.1 迁移
- 在 PostgreSQL 上
order_id是一个character varying(255)
EDIT
编辑
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.
一个小的架构更改未被检测到并导致一个错误，其中订单 ID 实际上是人名，而不是订单 ID。我的头上出现了愚蠢的错误。无论如何，现在我已经解决了这个问题并提取了正确的订单 ID，我发现我一直挂在executeUpdate. 目前正在处理这个问题。如果我无法解决，我可能会创建一个新问题。
回答by Brian Agnew
What does getConn()return ? I suspect it's a different (pooled?) connection each time.
什么getConn()回报？我怀疑每次都是不同的（汇集？）连接。
This:
这个：
getConn().setAutoCommit(false); //Added
PreparedStatement pStmnt = getConn().prepareStatement(update);
should likely read:
应该是：
Connection c = getConn();
c.setAutoCommit(false); //Added
PreparedStatement pStmnt = c.prepareStatement(update);
i.e. if getConn()returns a different connection each time, then you've got problems.
即，如果getConn()每次都返回不同的连接，那么您就遇到了问题。
回答by Cristian Meneses
I went through this issue a few years ago, and when I used a String value on a WHERE clause, using tokens, it didn't worked. BTW, it was also an Oracle database (10g)
几年前我遇到了这个问题，当我在 WHERE 子句上使用 String 值时，使用令牌，它没有用。顺便说一句，它也是一个 Oracle 数据库（10g）
Finally, I gave up, and changed the code to concatenate the value instead of tokenize it
最后，我放弃了，并更改了代码以连接值而不是将其标记化
From
从
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());
}
To
到
update = "UPDATE shop_ca_orders SET added_customer = 'y' WHERE order_id = ' + order_id + '";
try {
getConn().setAutoCommit(false); //Added
PreparedStatement pStmnt = getConn().prepareStatement(update);
results = pStmnt.executeUpdate();
if (results > 0) {
added = true;
getConn().commit(); //Added
getConn().setAutoCommit(true); //Added
}
} catch (SQLException ex) {
LOGGER.error(ex.toString());
}
That did the trick for me... This is not the most elegant solution, but is a workaround
这对我有用......这不是最优雅的解决方案，而是一种解决方法
====================== UPDATE 1 ============================
====================== 更新 1 ========================== ==
I've done a little more research, since I also faced this problem and still didn't get a valid answer
我做了更多的研究，因为我也遇到了这个问题，但仍然没有得到有效的答案
Looks like Oracle drivers expects you to pass a String with similar length to the table definition. This happens only to fixed length columns (like CHAR), VARCHAR columns doesn't seem to be affected by this.
看起来 Oracle 驱动程序希望您将长度相似的字符串传递给表定义。这仅发生在固定长度的列（如 CHAR）上，VARCHAR 列似乎不受此影响。
It means that, if order_idis a CHAR(10) , then you should pad your String to complete the column length. A better approach would be to trim the database value to match the token value.
这意味着，如果order_id是 CHAR(10) ，那么您应该填充您的 String 以完成列长度。更好的方法是修剪数据库值以匹配令牌值。
update = "UPDATE shop_ca_orders SET added_customer = 'y' " +
" WHERE LTRIM(RTRIM(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());
}
回答by Robert H
So my issue was two fold:
所以我的问题有两个方面：
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.
首先，我的初始结果集有问题。在迁移过程中删除了一列，我认为所有引用的列都在代码中发生了变化，但是缺少一个。一旦这个问题得到解决，订单 ID 就会准确地工作，并尝试更新数据库。
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.
第二个问题是数据库挂在更新上的问题。事实证明，挂起是由 SQL Developer 对数据库（或表，不确定是哪个）持有锁引起的 - 一旦我关闭 SQL Developer，更新立即完成，事情按预期进行。
