使用JBoss和Tomcat管理Java CLOB [英] Managing Java CLOB with JBoss and Tomcat

问题描述

使用tomcat和mysql在webapps中使用Clob似乎很容易. 对于以下示例，我们假设我们有一个表"mytable"，一个Clob"myclob"和一个键"id" 这是一个如何将字节数组放入Clob中的示例:

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) {;}
}

困难的是，当我尝试将Web应用程序移至jboss 5.0和Oracle数据库时:

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

ORA-01461:只能将LONG值绑定为插入到LONG列中

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

问题是:如何解决此错误(还认为方法'createClob()'不起作用?

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

推荐答案

搜索和搜索后的解决方案是:
1-使用选择更新"查询获取Clob
2-将数据写入Clob
3-使用语句execute

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

这里是代码

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;

这篇关于使用JBoss和Tomcat管理Java CLOB的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！