CODEWITHSUNDEEP
javahibernateclob
Pavithra
Pavithra

Reputation: 29

How to read CLOB column in Oracle DataBase from Java

I have a table in Database where datatype of a column(STATUS) is CLOB.I need to read that STATUS 

create table STATUS_TABLE
(
STATE_ID      number(20,0),
STATUS   clob
)

I am trying to read CLOB column as below

String getStatus = "SELECT STATUS FROM STATUS_TABLE WHERE STATE_ID="+id;
Query statusResults = session.createSQLQuery(getStatus);
List statusRes = statusResults.list();
if ((statusRes != null) && (statusRes.size() > 0)) {
        oracle.sql.CLOB clobValue = (oracle.sql.CLOB) statusRes.get(0);
        status = clobValue.getSubString(1, (int) clobValue.length());
        log.info("Status->:" + status.toString());
}

And getting the error as 

 java.lang.ClassCastException: $Proxy194 cannot be cast to oracle.sql.CLOB

How can i read clob data from DB and convert to String ?

Upvotes: 2

Views: 13105

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520918

Here is the corrected version, and an explanation appears below the code:

Query query = session.createSQLQuery("SELECT STATUS FROM STATUS_TABLE WHERE STATE_ID = :param1");
query.setInt("param1", id);
query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
List statusRes = query.list();
if (statusRes != null) {
    for (Object object : statusRes) {
        Map row = (Map)object;
        java.sql.Clob clobValue = (java.sql.Clob) row.get("STATUS");
        status = clobValue.getSubString(1, (int) clobValue.length());
        log.info("Status->:" + status.toString());
    }
}

Problems I saw with your code:

  • You were building a raw query string using concatenation. This leaves you vulnerable to SQL injection and other bad things.
  • For whatever reason you were trying to cast the CLOB to oracle.sql.CLOB. AFAIK JDBC will return a java.sql.Clob
  • You are performing a native Hibernate query, which will return a list result set whose type is not known at compile time. Therefore, each element in the list represents one record.

Upvotes: 4

Related Questions