Java 使用 JdbcTemplate 将大型 CLOB 插入 Oracle 失败
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22518991/
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
Using JdbcTemplate to insert large CLOB's into Oracle fails
提问by Jrop
I am interfacing with an Oracle database via Spring's JdbcTemplateutility class, and I have tried these two variants of code:
我通过 Spring 的JdbcTemplate实用程序类与 Oracle 数据库交互，并且我尝试了这两种代码变体：
jdbcTemplate.update("INSERT INTO my_table (title, content) VALUES (?, ?)", title, content);
-- or --
- 或者 -
jdbcTemplate.update(new PreparedStatementCreator() {
@Override
public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
OraclePreparedStatement ps = (OraclePreparedStatement)conn.prepareStatement("INSERT INTO my_table (title, content) VALUES (?, ?)");
ps.setString(1, title);
ps.setStringForClob(2, content);
return ps;
}
});
Where titleis a traditional VARCHAR2, and contentis a CLOB.
哪里title是传统的VARCHAR2，content是一个CLOB。
Either of these alternatives work for smaller values for content. However, when I have larger amounts of content, nothing gets inserted into the CLOBcolumn.
这些替代方法中的任何一个都适用于 的较小值content。但是，当我有大量的 时content，CLOB列中不会插入任何内容。
Interestingly enough, in both cases, titlegets updated. It's as if the query just ignores contentif there's too much, but never throws an error.
有趣的是，在这两种情况下，title都会更新。就好像查询只是忽略content了太多，但永远不会抛出错误。
Does anybody know how I should solve this?
有人知道我应该如何解决这个问题吗？
Thanks.
谢谢。
EDIT:
编辑：
Per the answer from @GreyBeardedGeek, I tried using OracleLobHandlerand DefaultLobHandler, to the same effect. Things work until my CLOB's reach a certain size.
根据@GreyBeardedGeek 的回答，我尝试使用OracleLobHandlerandDefaultLobHandler达到相同的效果。在 myCLOB达到一定大小之前，一切都会有效。
I also tried the following code, again to the same effect:
我还尝试了以下代码，再次达到相同的效果：
Connection conn = db.getDataSource().getConnection();
CLOB clob = CLOB.createTemporary(conn, false, CLOB.DURATION_SESSION);
clob.setString(1, myString);
OraclePreparedStatement ps = (OraclePreparedStatement)conn.prepareStatement("UPDATE my_table SET blob = ?");
ps.setCLOB(1, clob);
ps.execute();
I'm baffled as to why every one of these methods would work for smaller CLOB's, but then suddenly break for large ones. Is there some type of configuration in the DB that I'm missing? Or is the problem with the code?
我很困惑为什么这些方法中的每一种都适用于较小CLOB的 ，但突然间对大的无效。数据库中是否有我缺少的某种类型的配置？还是代码有问题？
采纳答案by Jrop
Okay, I feel pretty silly. As it turns out, even this simple code was storing the CLOBcorrectly:
好吧，我觉得很傻。事实证明，即使是这个简单的代码也能CLOB正确存储：
jdbcTemplate.update("UPDATE my_table SET title = ?, content = ? WHERE id = ?", getTitle(), getContentString(), getId());
The issue was my code that retrieved the CLOBback from the database. The following is my speculation based on my code (and the fix): it seems as though smaller CLOB's get cached in memory, and can be read at a later time (namely, after the connection is closed, they can still be read). However, for larger CLOB's, they must be read while the connection is still open.
问题是我CLOB从数据库中检索回来的代码。以下是我基于我的代码（和修复）的推测：似乎更小的CLOB's 被缓存在内存中，并且可以在以后读取（即，在连接关闭后，它们仍然可以被读取） . 但是，对于较大CLOB的 ，必须在连接仍然打开时读取它们。
For me, this meant the fix was as simple as reading the CLOB's contents as soon as it was available to my object. In my case, I'm not really worried about memory issues, because I don't expect that my CLOB's will contain inordinately sized contents, and so reading the value into memory immediately is an acceptable approach.
对我来说，这意味着修复就像阅读CLOB的内容一样简单，只要它对我的对象可用。就我而言，我并不真正担心内存问题，因为我不希望我的CLOB's 包含异常大小的内容，因此立即将值读入内存是一种可以接受的方法。
回答by GreyBeardedGeek
Oracle has, for as long as I can remember, required special handling for BLOBs and CLOBs. Spring JDBC has org.springframework.jdbc.support.lob.OracleLobHandler for setting the value of BLOBs and CLOBs.
从我记事起，Oracle 就要求对 BLOB 和 CLOB 进行特殊处理。Spring JDBC 有 org.springframework.jdbc.support.lob.OracleLobHandler 用于设置 BLOB 和 CLOB 的值。
There's a pretty good full example of how to use it at http://techdive.in/spring/spring-handling-blobclobbut basically, instead of ps.setStringForClob, you would do
在http://techdive.in/spring/spring-handling-blobclob 上有一个很好的完整示例，说明如何使用它，但基本上ps.setStringForClob，您可以这样做
oracleLobHandler.getLobCreator().setClobAsString(ps, 2, content);
oracleLobHandler.getLobCreator().setClobAsString(ps, 2, content);
回答by Sravanthi
SqlLobValue(String content)can be used for CLOB.
SqlLobValue(String content)可用于 CLOB。
Follow the link: http://docs.spring.io/spring/docs/current/javadoc-api/org/springframework/jdbc/core/support/SqlLobValue.html
按照链接：http: //docs.spring.io/spring/docs/current/javadoc-api/org/springframework/jdbc/core/support/SqlLobValue.html
.
.
