title

fire chief's random developer tidbits

Thursday, November 29, 2012

Hibernate SQLQuery and Oracle CLOB (long text)

This is possibly very obvious but it took me a while to find the answer.  Using existing native queries (SQLQuery) in hibernate I have a CLOB column I needed to map using a result set transformer.  Note when you use one addScalar() you must map every column with addScalar() that you want returned.  Use the org.hibernate.type.TextType (hibernate 3.5, it looks like hibernate 4 would be TextType.TEXTTYPE).

String sql = "SELECT BLAH_ID as id, FOO_DETAIL as detailText FROM blah";

Query query =
            session.createSQLQuery(sql)
            .addScalar("id")
            .addScalar("detailText", new TextType())
            .setResultTransformer(Transformers.aliasToBean(SomePojo.class));

class SomePojo {
    private Long id;
    private String detailText;

    ...getters and setters...
}
Now the real question is why did they do this native hibernate inside a Spring JpaRepository and not use NamedParameterJDBCTemplate with BeanPropertyRowMapper? It's very similar but I find the Spring style cleaner. Time to refactor.

2 comments:

  1. This do not work for me in hibernate 3.3GA. Do I need hibernate 3.5???

    ReplyDelete
    Replies
    1. I don't see any differences between the two versions of SQLQueryImpl that would make it not work in that version. http://grepcode.com/file_/repo1.maven.org/maven2/org.hibernate/hibernate-core/3.5.5-Final/org/hibernate/impl/SQLQueryImpl.java/?v=diff&id2=3.3.0.GA Maybe you could try making a simple self contained example and posting it here or on Stackoverflow. If you link to the stackoverflow question from here, that's fine with me.

      Delete