java.sql.Clob and Oracle CLOB

Method 1:

import oracle.sql.*;

public class JClob {

String tableName = null; //
String primaryKey = null; //
String primaryValue = null; //
String fieldName = null; //
String clobValue = null; //
Connection conn = null; //
/**
*
*Test*
* */
public static void main(String[] args) {
try {
JClob jc = new JClob(getConnection(),"aa","a","aaaa","c","ccc");
jc.write();
jc.read();
}
catch (Exception e) {
System.out.println(e);
e.printStackTrace();
}
}


/**
*
*Constructor
* */
public JClob(Connection connection,String tableName,String primaryKey,String primaryValue,String fieldName,String clobValue) {
this.conn = connection;
this.tableName = tableName;
this.primaryKey = primaryKey;
this.primaryValue = primaryValue;
this.fieldName = fieldName;
this.clobValue = clobValue;
}

/**
*
*
*Constructor
* */
public JClob(Connection connection,String tableName,String primaryKey,String primaryValue,String fieldName) {
this.conn = connection;
this.tableName = tableName;
this.primaryKey = primaryKey;
this.primaryValue = primaryValue;
this.fieldName = fieldName;
}

/**
*
*get connection
*
* */
public static Connection getConnection() throws SQLException,ClassNotFoundException {
Class.forName("oracle.jdbc.OracleDriver");
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.1.18:1521:portal","portal","portal");
return conn;
}

/**
*
*Read clob
*@return clob
*
* */
public String read() throws SQLException,IOException {
String rtn = null;
try {
String sql = "select " + fieldName + " from " + tableName + " where " + primaryKey + "=" + primaryValue;
//Connection conn = getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql);
//int v = Integer.parseInt(primaryValue);
//pstmt.setInt(1,v);
ResultSet rs = pstmt.executeQuery();

java.sql.Clob clob = null;
if (rs.next()) {
clob = rs.getCLOB(fieldName);
//clob = ((OracleResultSet)rs).getCLOB(fieldName);
//clob = ((org.apache.commons.dbcp.DelegatingResultSet)rs).getClob(fieldName);
//Reader in = clob.getCharacterStream();
InputStream input = clob.getAsciiStream();
int len = (int)clob.length();
byte[] by = new byte[len];
int i ;//= input.read(by,0,len);
while(-1 != (i = input.read(by, 0, by.length))) {
input.read(by, 0, i);
}
rtn = new String(by);
}
}
catch (SQLException e){
throw e;
}
catch (Exception ee) {
ee.printStackTrace();
}

return rtn;
}

/**
*
*Write clob
*
* */
public void write() throws SQLException,IOException {
String sql = "update " + tableName + " set " + fieldName + "=empty_clob() where " + primaryKey + "=" + primaryValue;
//Connection conn = getConnection();
conn.setAutoCommit(false);

PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.executeUpdate();

sql = "select " + fieldName + " from " + tableName + " where " + primaryKey + "=" + primaryValue;
Statement st = conn.createStatement();
ResultSet rs = st.executeQuery(sql);

java.sql.Clob clob ;
if (rs.next()) {
clob = ((oracle.jdbc.OracleResultSet)rs).getClob(fieldName);
//clob = ((org.apache.commons.dbcp.DelegatingResultSet)rs).getClob(fieldName);
oracle.sql.CLOB my_clob = (oracle.sql.CLOB)clob;
OutputStream writer = my_clob.getAsciiOutputStream();
byte[] contentStr = this.getContent().getBytes();
writer.write(contentStr);
writer.flush();
writer.close();
}

conn.commit();
rs.close();
st.close();
pstmt.close();
conn.setAutoCommit(true);
}

/**
*
*
* */
private String getContent() {
return this.clobValue;
}

/**
*
*
* */
public void setClobValue(String clobValue) {
this.clobValue = clobValue;
}
}

Method 2:


sql = "insert into mytable RESULT (ID,system,date) values (1,CLOB.empty_lob(),sysdate)";
statement = myCon.prepareStatement(sql);
if (statement.executeUpdate() == 1) {
ret = "success";
}
if(ret.equalsIgnoreCase("success")){
String sql2 = "select system from mytable where ID = '" + ID + "'" + " for update";
rss = statement.executeQuery(sql2);
if (rss.next()) {
//oracle.sql.CLOB clob = (oracle.sql.CLOB ) ((org.apache.commons.dbcp.DelegatingResultSet) rss).getClob(1);
//oracle.sql.CLOB clob = ( (oracle.jdbc.OracleResultSet) rss).getCLOB(1);
oracle.sql.CLOB clob = (oracle.sql.CLOB ) rss.getObject(1);
clob.putString(1, "aaaa");
sql = "update mytable set system = ?";
sql += " where ID = '" + ID + "'";
statement = myCon.prepareStatement(sql);
statement.setClob(1, clob);
if (statement.executeUpdate() == 1) {
ret = "success";
}
}
}else{
myCon.rollback();
myCon.setAutoCommit(true);

}


Q&A:


If we use Oracle9i jdbc we will get below exception on tomcat:

1.

java.sql.Clob clob=rs.getClob(1);
clob.setString(1,string);

We will get

java.sql.SQLException: Unsupported feature
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:269)
at oracle.jdbc.dbaccess.DBError.throwUnsupportedFeatureSqlException(DBError.java:689)
at oracle.sql.CLOB.setString(CLOB.java:1148)
at org.apache.jsp.dealWith$jsp._jspService(dealWith$jsp.java:317)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:107)
......

2.
java.sql.Clob clob= ((OracleResultSet)rs).getCLOB(fieldName);
clob.setString(1,string);

We will get ClassCast Exception


In this situation we must delete the ojdbc4.jar/ojdbc4_g.jar in the lib of WEB-INF/

and use:

oracle.sql.CLOB clob= (oracle.sql.CLOB)rs.getObject(1);
try {
java.io.Writer wr=clob.getCharacterOutputStream();
wr.write("sagfdsjalfdsakfdskafsdaffdsa");
wr.flush();
wr.close();
}
catch (IOException ex) {
ex.printStackTrace() ;
}

or

oracle.sql.CLOB clob= (oracle.sql.CLOB)rs.getObject(1);
clob.setString(1,"aaaaaaa");

For Oracle10g they implement the java.sql.Clob, so there is no such problem. But we still must delete the ojdbc4.jar in WEB-INF/lib sometimes.