在 Java 中读取 CLOB 到 String 和 String 到 CLOB 的最有效解决方案？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2169732/
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
Most efficient solution for reading CLOB to String, and String to CLOB in Java?
提问by Jonas
I have a big CLOB (more than 32kB) that I want to read to a String, using StringBuilder. How do I do this in the most efficient way? I can not use the "int length" constructor for StringBuilder since the lenght of my CLOB is longer than a "int" and needs a "long" value.
我有一个很大的 CLOB（超过 32kB），我想使用 StringBuilder 将其读入字符串。我如何以最有效的方式做到这一点？我不能为 StringBuilder 使用“int length”构造函数，因为我的 CLOB 的长度比“int”长并且需要“long”值。
I am not that confortable with the Java I/O classes, and would like to get some guidance.
我对 Java I/O 类不太满意，希望得到一些指导。
Edit- I have tried with this code for clobToString():
编辑-我已经尝试使用此代码用于 clobToString()：
private String clobToString(Clob data) {
StringBuilder sb = new StringBuilder();
try {
Reader reader = data.getCharacterStream();
BufferedReader br = new BufferedReader(reader);
String line;
while(null != (line = br.readLine())) {
sb.append(line);
}
br.close();
} catch (SQLException e) {
// handle this exception
} catch (IOException e) {
// handle this exception
}
return sb.toString();
}
采纳答案by Barend
I can not use the "int length" constructor for
StringBuildersince the length of my CLOB is longer than aintand needs alongvalue.
我不能使用“int length”构造函数，
StringBuilder因为我的 CLOB 的长度比 a 长int并且需要一个long值。
If the CLOB length is greater than fits in an int, the CLOB data won't fit in a String either. You'll have to use a streaming approach to deal with this much XML data.
如果 CLOB 长度大于适合 int，则 CLOB 数据也不会适合 String。您必须使用流方法来处理这么多 XML 数据。
If the actual length of the CLOB is smaller than Integer.MAX_VALUE, just force the longto intby putting (int)in front of it.
如果 CLOB 的实际长度小于Integer.MAX_VALUE，只需将 放在它前面long来强制它。int(int)
回答by Omar Al Kababji
Ok I will suppose a general use, first you have to download apache commons, there you will find an utility class named IOUtils which has a method named copy();
好的，我假设一个一般用途，首先你必须下载apache commons，在那里你会找到一个名为 IOUtils 的实用程序类，它有一个名为 copy() 的方法；
Now the solution is: get the input stream of your CLOB object using getAsciiStream() and pass it to the copy() method.
现在的解决方案是：使用 getAsciiStream() 获取 CLOB 对象的输入流并将其传递给 copy() 方法。
InputStream in = clobObject.getAsciiStream();
StringWriter w = new StringWriter();
IOUtils.copy(in, w);
String clobAsString = w.toString();
回答by Edwin Lee
If you really must use only standard libraries, then you just have to expand on Omar's solution a bit. (Apache's IOUtils is basically just a set of convenience methods which saves on a lot of coding)
如果您真的必须只使用标准库，那么您只需稍微扩展 Omar 的解决方案。（Apache 的 IOUtils 基本上只是一组节省大量编码的便捷方法）
You are already able to get the input stream through clobObject.getAsciiStream()
您已经能够通过输入流 clobObject.getAsciiStream()
You just have to "manually transfer" the characters to the StringWriter:
您只需要将字符“手动传输”到 StringWriter：
InputStream in = clobObject.getAsciiStream();
Reader read = new InputStreamReader(in);
StringWriter write = new StringWriter();
int c = -1;
while ((c = read.read()) != -1)
{
write.write(c);
}
write.flush();
String s = write.toString();
Bear in mind that
请记住
- If your clob contains more character than would fit a string, this won't work.
- Wrap the InputStreamReader and StringWriter with BufferedReader and BufferedWriter respectively for better performance.
- 如果您的 clob 包含的字符多于适合字符串的字符数，这将不起作用。
- 分别用 BufferedReader 和 BufferedWriter 包裹 InputStreamReader 和 StringWriter 以获得更好的性能。
回答by Stan Sokolov
My answer is just a flavor of the same. But I tested it with serializing a zipped content and it worked. So I can trust this solution unlike the one offered first (that use readLine) because it will ignore line breaks and corrupt the input.
我的回答只是一个味道。但是我通过序列化压缩的内容对其进行了测试并且它起作用了。所以我可以相信这个解决方案，不像第一个提供的解决方案（使用 readLine），因为它会忽略换行符并破坏输入。
/*********************************************************************************************
* From CLOB to String
* @return string representation of clob
*********************************************************************************************/
private String clobToString(java.sql.Clob data)
{
final StringBuilder sb = new StringBuilder();
try
{
final Reader reader = data.getCharacterStream();
final BufferedReader br = new BufferedReader(reader);
int b;
while(-1 != (b = br.read()))
{
sb.append((char)b);
}
br.close();
}
catch (SQLException e)
{
log.error("SQL. Could not convert CLOB to string",e);
return e.toString();
}
catch (IOException e)
{
log.error("IO. Could not convert CLOB to string",e);
return e.toString();
}
return sb.toString();
}
回答by gavenkoa
What's wrong with:
有什么问题：
clob.getSubString(1, (int) clob.length());
?
?
For example Oracle oracle.sql.CLOBperforms getSubString()on internal char[]which defined in oracle.jdbc.driver.T4CConnectionand just System.arraycopy()and next wrap to String... You never get faster reading than System.arraycopy().
例如，Oracleoracle.sql.CLOB执行getSubString()内部char[]定义，oracle.jdbc.driver.T4CConnection并且只是System.arraycopy()和下一个换行到String......你永远不会比System.arraycopy().
UPDATEGet driver ojdbc6.jar, and decompile CLOBimplementation, and study which case would faster based on internals knowledge.
UPDATE获取驱动ojdbc6.jar，并反编译CLOB实现，并根据内部知识研究哪种情况会更快。
回答by Rohit
public static String readClob(Clob clob) throws SQLException, IOException {
StringBuilder sb = new StringBuilder((int) clob.length());
Reader r = clob.getCharacterStream();
char[] cbuf = new char[2048];
int n;
while ((n = r.read(cbuf, 0, cbuf.length)) != -1) {
sb.append(cbuf, 0, n);
}
return sb.toString();
}
The above approach is also very efficient.
上述方法也非常有效。
回答by kayz1
public static final String tryClob2String(final Object value)
{
final Clob clobValue = (Clob) value;
String result = null;
try
{
final long clobLength = clobValue.length();
if (clobLength < Integer.MIN_VALUE || clobLength > Integer.MAX_VALUE)
{
log.debug("CLOB size too big for String!");
}
else
{
result = clobValue.getSubString(1, (int) clobValue.length());
}
}
catch (SQLException e)
{
log.error("tryClob2String ERROR: {}", e);
}
finally
{
if (clobValue != null)
{
try
{
clobValue.free();
}
catch (SQLException e)
{
log.error("CLOB FREE ERROR: {}", e);
}
}
}
return result;
}
回答by Khaled.K
CLOB are like Files, you can read parts of it easily like this
CLOB 就像文件，您可以像这样轻松阅读其中的一部分
// read the first 1024 characters
String str = myClob.getSubString(0, 1024);
and you can overwrite to it like this
你可以像这样覆盖它
// overwrite first 1024 chars with first 1024 chars in str
myClob.setString(0, str,0,1024);
I don't suggest using StringBuilder and fill it until you get an Exception, almost like adding numbers blindly until you get an overflow. Clob is like a text file and the best way to read it is using a buffer, in case you need to process it, otherwise you can stream it into a local file like this
我不建议使用 StringBuilder 并填充它直到你得到一个异常，几乎就像盲目地添加数字直到你得到一个溢出。Clob 就像一个文本文件，读取它的最佳方法是使用缓冲区，以防您需要处理它，否则您可以像这样将其流式传输到本地文件中
int s = 0;
File f = new File("out.txt");
FileWriter fw new FileWriter(f);
while (s < myClob.length())
{
fw.write(myClob.getSubString(0, 1024));
s += 1024;
}
fw.flush();
fw.close();
回答by Naveen K Reddy
If using Mule, below are the steps.
如果使用 Mule，以下是步骤。
Follow below steps.
请按照以下步骤操作。
Enable streaming in the connector i.e. progressiveStreaming=2
在连接器中启用流，即ProgressiveStreaming=2
Typecast DB2 returned CLOB to java.sql.Clob (IBM Supports this type cast)
类型转换 DB2 将 CLOB 返回到 java.sql.Clob（IBM 支持这种类型转换）
Convert that to character stream (ASCII stream sometimes may not support some special characters). So you may to use getCharacterStream()
将其转换为字符流（ASCII 流有时可能不支持某些特殊字符）。所以你可以使用 getCharacterStream()
That will return a "reader" object which can be converted to "String" using common-io (IOUtils).
这将返回一个“reader”对象，该对象可以使用 common-io (IOUtils) 转换为“String”。
So in short, use groovy component and add below code.
所以简而言之，使用 groovy 组件并添加以下代码。
clobTest = (java.sql.Clob)payload.field1
bodyText = clobTest.getCharacterStream()
targetString = org.apache.commons.io.IOUtils.toString(bodyText)
payload.PAYLOADHEADERS=targetString return payload
Note:Here I'm assuming "payload.field1" is holding clob data.
注意：这里我假设“payload.field1”保存了clob数据。
That's it!
就是这样！
Regards Naveen
问候纳文
回答by Hemant
private String convertToString(java.sql.Clob data)
{
final StringBuilder builder= new StringBuilder();
try
{
final Reader reader = data.getCharacterStream();
final BufferedReader br = new BufferedReader(reader);
int b;
while(-1 != (b = br.read()))
{
builder.append((char)b);
}
br.close();
}
catch (SQLException e)
{
log.error("Within SQLException, Could not convert CLOB to string",e);
return e.toString();
}
catch (IOException e)
{
log.error("Within IOException, Could not convert CLOB to string",e);
return e.toString();
}
//enter code here
return builder.toString();
}
