Java 如何在JDBC的PreparedStatement中使用setClob()

How to use setClob() in PreparedStatement inJDBC

提问by user3369905

Here are the info:

以下是信息：

1. I have a String
2. I want to insert a record in a table with the String in a column whose datatype is CLOB.
3. I would like to use setClob()method of the preparedstatement.

1. 我有一个字符串
2. 我想在数据类型为CLOB.
3. 我想使用setClob()preparedstatement的方法。

So my question is how to create a Clobobject from this String so that I can use setClob()method.

所以我的问题是如何Clob从这个 String创建一个对象，以便我可以使用setClob()方法。

Thanks in advance, Naveen

提前致谢，纳文

回答by TA Nguyen

For CLOB it is of String already. So, just use .setString() and that should work. One thing about ORACLE jdbc if you are using it, it like the CLOB INPUT parameter to be the last one in your statement especially with a large data.

对于 CLOB，它已经是 String 了。所以，只需使用 .setString() 就可以了。关于 ORACLE jdbc 的一件事，如果您正在使用它，它就像 CLOB INPUT 参数是您语句中的最后一个参数，尤其是对于大数据。

Example:

例子：

INSERT INTO MY_TABL (NUM_COL, VARC_COL, VARC_COL, TS_COL, CLOB_COL) 
   VALUES(?,?,?,?,?);

As you can see, the CLOB_COL is of type CLOB and should be last so that when you do .setString(5) and 5 is the last index.

正如您所看到的，CLOB_COL 是 CLOB 类型并且应该是最后一个，这样当您执行 .setString(5) 和 5 是最后一个索引时。

回答by Evgeniy Dorofeev

If you want to write a String to CLOB column just use PreparedStatement.setString.

如果要将字符串写入 CLOB 列，只需使用PreparedStatement.setString.

If you want to know how to create a CLOB from String this is it

如果您想知道如何从 String 创建 CLOB，就是这样

    Clob clob = connection.createClob();
    clob.setString(1, str);

回答by Visruth

You may create the clob from a connection object as follows

您可以按如下方式从连接对象创建 Clob

Connection con = null;// write code to make a connection object
Clob clob = con.createClob();
String str = "this is a stirng";
clob.setString(1, str );

PreparedStatement ps = null;// write code to create a prepared statement
ps.setClob(4, clob);

Or you may try the alternative code as follows :

或者您可以尝试以下替代代码：

//alternative way       
String str = "this is a stirng";
ByteArrayInputStream inputStream = new ByteArrayInputStream(str.getBytes());
InputStreamReader inputStreamReader = new InputStreamReader(inputStream);

int parameterIndex = 1;
PreparedStatement ps = null;// write code to create a prepared statement
ps.setClob(parameterIndex, inputStreamReader);

回答by valepu

Today i had an issue with a Clob field because i was using "setString" to set the parameter, but then i had this error while testing with a very long string: "setString can handle only Strings with less than 32766 characters"

今天我遇到了一个 Clob 字段的问题，因为我使用“setString”来设置参数，但是我在测试一个很长的字符串时遇到了这个错误：“setString 只能处理少于 32766 个字符的字符串”

I used connection.createClob but it gave me this exception:

我使用了 connection.createClob 但它给了我这个例外：

java.lang.AbstractMethodError: org.apache.tomcat.dbcp.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.createClob()Ljava/sql/Clob;

So looking for this exception i found this using CLOB in java throwing exceptionand the accepted answer (using setCharacterStream instead of setClob) worked for me

所以寻找这个异常我发现这个 在java抛出异常中使用CLOB并且接受的答案（使用setCharacterStream而不是setClob）对我有用

Copy/Pasted from the accepted answer (so all credits are for a_horse_with_no_name )

从接受的答案中复制/粘贴（因此所有学分都用于 a_horse_with_no_name ）

StringReader reader = new StringReader(userAbout);
PreparedStatement insertClob = dbCon.prepareStatement("UPDATE user_data SET user_about=? WHERE user_id=?");
insertClob.setCharacterStream(1, reader, userAbout.length());
insertClob.setInt(2,userId);

回答by Dave Lyndon

I had a specific variation of this issue which required to insert a clob into an Oracle database from java code running on that db. None of the answers here quite worked for me.

我有这个问题的一个特定变体，它需要从在该数据库上运行的 Java 代码将一个 clob 插入到 Oracle 数据库中。这里没有一个答案对我有用。

I eventually found solution, the trick being to use oracle.sql.CLOB

我最终找到了解决方案，诀窍是使用 oracle.sql.CLOB

This the approach I discovered:

这是我发现的方法：

create table test_clob (
    c clob
);

create or replace and compile java source named java_clob_insert as

import java.sql.Connection;
import java.sql.PreparedStatement;
import oracle.sql.CLOB;
import java.io.Writer;

public class JavaClobInsert {

    public static void doInsert () {

        try {

            //create the connection and statement
            Connection oracleConn =
                (new oracle.jdbc.OracleDriver()).defaultConnection();

            String stmt = "INSERT INTO test_clob values (?)";

            PreparedStatement oraclePstmt = oracleConn.prepareStatement(stmt);

            //Imagine we have a mysql longtext or some very long string
            String s = "";
            for (int i = 0; i < 32768; i++) {
                s += i % 10;
            }

            //Initialise the Oracle CLOB
            CLOB clob;
            clob = CLOB.createTemporary(oracleConn, true, CLOB.DURATION_CALL);

            //Good idea to check the string is not null before writing to clob
            if (s != null) {
                Writer w = clob.setCharacterStream( 1L );
                w.write(s);
                w.close();
                oraclePstmt.setClob(1, clob);
            } else {
                oraclePstmt.setString(1, "");
            }

            //clean up
            oraclePstmt.executeUpdate();
            oracleConn.commit();
            oraclePstmt.close();
            oracleConn.close();

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
/

create or replace procedure clob_insert as language java name 
    'JavaClobInsert.doInsert()';    
/

begin
    clob_insert;
end;
/

select *
from   test_clob; 

回答by Dale

My answer is slightly different than others...

我的回答和别人的略有不同...

I had a PreparedStatement, stmt, and was using stmt.setString(colIndex, value)for updates to my database that had a CLOB column.

我有一个PreparedStatement，stmt以及使用stmt.setString(colIndex, value)更新到我的数据库有一个CLOB列。

This worked without fail for me when inserting and updating rows in the database table.

在数据库表中插入和更新行时，这对我来说没有失败。

When others tested this code though they would occasionally see an exception occur:

当其他人测试此代码时，尽管他们偶尔会看到发生异常：

ORA-22275: invalid LOB locator

It only seemed to happen on updates, not inserts - not sure why on that, when valuewas null. And I only ever had this occur with Oracle databases, not MSSQL or DB2.

它似乎只发生在更新上，而不是插入时 - 不知道为什么，什么时候value为空。我只在 Oracle 数据库中出现过这种情况，而不是 MSSQL 或 DB2。

Anyway to fix it I changed the logic to test for a null value

无论如何修复它我改变了逻辑来测试一个空值

if (value == null) {
  stmt.setNull(colIndex, java.sql.Types.CLOB);
}
else {
  stmt.setString(colIndex, value);
}

This worked without fail for me and others!

这对我和其他人来说都没有失败！