oracle 将 Java 字符串转换为 CLOB
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5454830/
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
Convert Java String to CLOB
提问by aherlambang
I have the following table:
我有下表：
The problem is that when I try to use JDBC to insert a String to this field I always get the following error:
问题是，当我尝试使用 JDBC 向该字段插入字符串时，总是出现以下错误：
java.sql.SQLException: Data size bigger than max size for this type: 6019
what should I do now?
我现在该怎么办？
The java code to generate the INSERT is the following:
生成 INSERT 的 java 代码如下：
Connection conn2 = null;
if(connection==null||connection.isClosed())
{
System.out.println("Connection is null");
conn2 = connectDB();
}
//REPLACE is non-standard SQL from MySQL, it's counter part of INSERT IGNORE
String sql = "";
sql = "INSERT INTO TEST."+tablename+" ("+fields+") VALUES ("+fields.replaceAll("[^,]+", "?")+")";
System.out.println("SQL: "+sql);
PreparedStatement pstmt = conn2.prepareStatement(sql);
// start parsing
int event = r.getEventType();
while (true) {
// for each wanted element
if (event == XMLStreamConstants.START_ELEMENT
&& r.getName().toString().equalsIgnoreCase("row")) {
System.out.println("Table : "+tablename+" / Row : "+rowCount );
if (attributeCount == 0)
attributeCount = r.getAttributeCount();
//put each parameter to SQL
int f=1;
for (String field : fieldsArray){
String value = r.getAttributeValue("",field);
if("body".equalsIgnoreCase(field) && value != null) {
pstmt.setCharacterStream(f++, new CharArrayReader(value.toCharArray()), value.length());
} else {
pstmt.setString(f++, value);
}
}
pstmt.addBatch();
rowCount++;
if(rowCount%rowspercommit==0){
System.out.println("Importing at row "+rowCount+" ... ");
pstmt.executeBatch();
conn2.commit();
}
} // end for each row.
Not sure how the CREATE TABLE is, as someone did that for me
不确定 CREATE TABLE 是怎样的，因为有人为我做过
With the current code above, I am now getting this:
使用上面的当前代码，我现在得到了：
Exception in thread "main" java.lang.NullPointerException
at oracle.jdbc.dbaccess.DBData.clearItem(DBData.java:431)
at oracle.jdbc.dbaccess.DBDataSetImpl.clearItem(DBDataSetImpl.java:3528)
at oracle.jdbc.driver.OraclePreparedStatement.checkBindTypes(OraclePreparedStatement.java:3271)
at oracle.jdbc.driver.OraclePreparedStatement.setStreamItem(OraclePreparedStatement.java:1178)
at oracle.jdbc.driver.OraclePreparedStatement.setCharacterStream(OraclePreparedStatement.java:3539)
at XMLDumpImporter.importXMLFile(XMLDumpImporter.java:179)
at XMLDumpImporter.importXMLFolder(XMLDumpImporter.java:117)
at XMLDumpImporter.main(XMLDumpImporter.java:48)
at line:
在线：
if("body".equalsIgnoreCase(field) && value != null) {
pstmt.setCharacterStream(f++, new CharArrayReader(value.toCharArray()), value.length());
Which made no total sense as what could possibly be null
这完全没有意义，因为什么可能是空的
回答by Aleksi Yrttiaho
It seems that you are using setStringinstead of setCharacterStreamto set value of the clob. If you are using setStringthen the driver probably converts the value to varchar2which has size limit of 4000 characters.
看来您正在使用setString而不是setCharacterStream设置clob 的值。如果您正在使用，setString那么驱动程序可能会将varchar2大小限制为 4000 个字符的值转换为。
Hackety hack:
黑客攻击：
int fieldIndex = 0;
for(String fieldName : fieldsArray) {
String value = r.getAttributeValue("",field);
if(value != null && "body".equalsIgnoreCase(fieldName)) {
pstmt.setCharacterStream(++fieldIndex, new StringReader(value), value.length());
} else {
pstmt.setString(++fieldIndex, value);
}
}
Relevant documentation:
相关文档：
