How do I store a string longer than 4000 characters in an Oracle Database using Java/JDBC?

Question

I’m not sure how to use Java/JDBC to insert a very long string into an Oracle database.

I have a String which is greater than 4000 characters, lets say it’s 6000. I want to take this string and store it in an Oracle database.

The way to do this seems to be with the CLOB datatype. Okay, so I declared the column as description CLOB.

Now, when it comes time to actually insert the data, I have a prepared statement pstmt. It looks like pstmt = conn.prepareStatement(“INSERT INTO Table VALUES(?)”).

So I want to use the method pstmt.setClob(). However, I don’t know how to create a Clob object with my String in it; there's no constructor (presumably because it can be potentially much larger than available memory).

How do I put my String into a Clob?

Keep in mind I’m not a very experienced programmer; please try to keep the explanations as simple as possible. Efficiency, good practices, etc. are not a concern here, I just want the absolute easiest solution. I’d like to avoid downloading other packages if it all possible; right now I’m just using JDK 1.4 and what is labelled ojdbc14.jar. I've looked around a bit but I haven't been able to follow any of the explanations I've found.

If you have a solution that doesn’t use Clobs, I’d be open to that as well, but it has to be one column.

Answer 1

You have (at least) two options:

• use connection.createClob() to create a Clob, set the data on it, and set it on the prepared statement. This will work for smaller data

• use preparedStatement.setClob(position, reader) - here you will have a Reader instance.

Answer 2

Here is an example at oracle.com for using LOB columns with Oracle and JDBC. Basically, it's inserting a LOB locator for an empty LOB (actually two, since it's demonstrating both BLOBs and CLOBs), locking the row for update, and then using the BLOB and CLOB OutputStream interfaces to write the data into the LOBs. When the "SELECT ... FOR UPDATE" is released, the LOBs will be refreshed in the database and the newly inserted data will be visible.

Answer 3

There are several methods see Java Database Connectivity (JDBC) - Convert String to CLOB and Insert into Oracle database