Managing Java CLOB with JBoss and Tomcat

问题:

Working with clobs in webapps using tomcat and mysql seems to be very easy. For the following example we assume we have the table 'mytable' with a clob 'myclob' and a key 'id' This is an example to how to put a byte array inside a clob:

Connection           conn = null;
PreparedStatement    stmt = null;
ByteArrayInputStream  bis = null;
try {
    if (mydatasource != null)
        conn = mydatasource.getConnection();
    StringBuffer sb=new StringBuffer("update mytable set myclob = ? where id = ?");
    bis=new ByteArrayInputStream(myArrayOfBytes);
    stmt.setBinaryStream( 1, bis);
    stmt.setString(2, "myId");
    stmt.executeUpdate();
} catch(Exception ex){
    ex.printStackTrace();
} finally {
    try{bis.close();} catch (Exception x) {;}
    try{stmt.close();} catch (Exception x) {;}
    try{conn.close();} catch (Exception x) {;}
}

The hard thing is when i've tryed to move my webapp to jboss 5.0 and an Oracle db:

ORA-01461: can bind a LONG value only for insert into a LONG column

The question is: how to solve this error (thinking also that the method 'createClob()' does not work?

回答1:

Use PreparedStatement#setClob rather than setBinaryStream. Also, make sure your field in the oracle table is CLOB as well.

In order to create an instance of Clob use Connection#createClob.

回答2:

This question it duplicate of many questions on SO. Generally the problem is that BLOB/CLOB is not a Java object and can not lie on a Java heap. It is something like a file and it present in the database. And morevover can be created only by the database.

Therefore therefore there these ways how to create CLOB

• Connection.createClob(), this will require a roundtrinp to a database, and will create CLOB in TEMPorary tablespace. When this clob is used in the next query, data have to be copied from one disk location onto another(in worst case)
• INSERT insert into T(it, file) values(t_seq.nextval, empty_blob()) returning id, file into :id, :file; this construct is the best option from Oracle's perspective. Fewer roundtrips, no need to allocate space in TEMP tablespace. But unfortunatelly returnng clause is proprietatry SQL extension and is NOT supported by JDBC standard
• 2 steps approach. First INSERT/UPDATE the row using file=empty_blob() function and then in the 2nd step return the handle for newly created LOB via SELECT FOR UPDATEstatment.

At least in case of Oracle you can not treat LOB as if it was just another bind variable placeholder. It this case you depend on some implicit datatype conversion, whihc is not safe.

回答3:

The solution, after searching and searching is:
1- get a clob using a 'select for update' query
2- write data into clob
3- update the table with a statement execute

Here you are the code

Connection           conn = null;
PreparedStatement    stmt = null;
try {
    if (infodatasource != null)
    conn = infodatasource.getConnection();

    //Update to prevent null pointer exceptions during next query
    stmt = conn.prepareStatement("UPDATE mytable set myclob=empty_clob() WHERE id = ?");
    stmt.setString(1, idPoi);
    stmt.executeUpdate();

    stmt = conn.prepareStatement("SELECT myclob FROM mytable WHERE id = ? FOR UPDATE");
    stmt.setString(1, idPoi);
    ResultSet rs = stmt.executeQuery();
    Clob myclob=null;
    if(rs.next()) {
    myclob=rs.getClob("CARTASER");
    }

    OutputStream writer = myclob.setAsciiStream(0L);
    writer.write(myArrayOfBytes);
    writer.flush();
    writer.close();

    stmt = conn.prepareStatement("UPDATE mytable SET myclob = ? WHERE id = ?");
    stmt.setClob(1, myclob);
    stmt.setString(2, "myid");
    return stmt.execute(); //Note: explicit commit required if autocommit is off
} catch(Exception ex){
    ex.printStackTrace();
    //out.println("Cannot get connection: " + ex);
} finally {
    try{stmt.close();} catch (Exception x) {;}
    try{conn.close();} catch (Exception x) {;}
}
return false;