Convert Oracle CLOBs to String in JSTL and tag file

Keywords:
java.lang.ClassCastException oracle.sql.CLOB cast java.lang.String CLOB jstl tag requestScope requestContext pageContext jspContext

Problem:
It's annoying when SQL that works for other vendors fails for a specific one ... in this case a "text" column in a schema is defined as "clob" in the corresponding oracle schema. Problem is that this is not necessarily equivalent - especially when querying the data. This is even more complex when the SQL is in JSTL. So with the JSTL code (where textValue is a CLOB):

<sql:query var="data" >

    select id, 

    textValue 

    from example 

    where id=?

    <sql:param value="${param['id']}"/>

</sql:query>

<c:forEach items="${data.rows}" var="row">

    <c:out value="${row.textValue}"/><br/>

</c:forEach>

You get the result:

oracle.sql.CLOB@e645e0

oracle.sql.CLOB@1f58913

oracle.sql.CLOB@fa6b82

...

Or if you try to use the textValue in something expecting a string, you'll get:

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

How do you turn a Clob to a String without filling the JSP with vendor-specific code (leaving out the argument for not having SQL in the JSP for now)?

Solution:
Great discussion of this very issue is on the OTN Forum: JSP and CLOB. It essentially involves putting the Clob to String code in a scriptlet. To keep this vendor-neutral and take some of the "ugliness" out of the JSP I'd opt for putting this code in a tag file and stick to referencing just the java.sql.* interfaces.

Step 1: create a /WEB-INF/tags/to-string.tag tag file

(Or in a subfolder - the path must start with /WEB-INF/tags/.. if using the tagdir approach).

This takes the CLOB (or other) column value and sets it back in the request context as a String.

<%-- 

    Can turn a CLOB to String for Oracle schema

--%>

<%@ tag body-content="empty" %>

<%@ attribute name="var" required="true" type="java.lang.String" %>

<%@ attribute name="value" required="true" type="java.lang.Object" %>



<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>



<%@ tag import="java.sql.*" %>

<%@ tag import="javax.servlet.jsp.*" %>

<%

    String strValue = null; 

    if (value == null) {

        strValue = ""; // NB: oracle empty string is null

    } else if (value instanceof Clob) {

        Clob clob = (Clob)value;

        long size = clob.length();

        strValue = clob.getSubString(1, (int)size);

    } else {

        strValue = value.toString();

    }

    jspContext.setAttribute(var, strValue, PageContext.REQUEST_SCOPE);

%>

In this tag file, var is the name of the variable to define in the requestScope. Note how this is done by referencing the jspContext variable.

Step 2: Use the to-string tag for your text, clob or Other columns

This involves first defining the new taglib (putting all .tag files in tagdir in the JSP scope using the "eg" prefix in this example) and then simply using the eg:to-string tag to put the string-value of the column in a "local" requestScope variable.

<%@ taglib prefix="eg" tagdir="/WEB-INF/tags" %>

<sql:query var="data" >

    select id, 

    textValue 

    from example 

    where id=?

    <sql:param value="${param['id']}"/>

</sql:query>

<c:forEach items="${data.rows}" var="row">

    <eg:to-string var="textValue" value="${row.textValue}"/>

    <c:out value="${textValue}"/><br/>

</c:forEach>