i have a clob column , but how can i search it?

magician · **Joined:** Sat Nov 01, 2003 6:14 am **Posts:** 30

just like string column : where a like '%something%'

or anyone has other solutions?

thanks for your reply!

christian · **Posted:** Thu Feb 05, 2004 4:18 am

Depends on the database, in most, you can't. LOBs suck.

tpischke · **Posted:** Wed Nov 10, 2004 3:27 pm

I'd also like to query a Clob field. Other than this short thread, I haven't found much info. Would it be correct to sum up this issue as "You can't..."?

Of course, it's possible using native SQL in the FirstSQL OO database I'm using, via the Clob's toString() method, but this doesn't apply to HQL.

I could alway use Session.createSQLQuery(), but then I have to write a lot of glue code to actually construct my return objects, so I'm posting this in case someone knows a better alternative.

tpischke · **Posted:** Wed Nov 10, 2004 3:41 pm

cakewalk. Hibernate team got dis one covuhed.

Here's a native SQL clob query that gets mapped automatically to the right persistent object.
------------------------------------------------

Code:

 public Library selectByTitle(String title) throws DAOException {
        String str = "select {lib.*} from Library lib where lib.title.compareTo(CAST( :title AS CLOB)) = 0";
        SyncQuery query = HibernateUtil.getSession().createSQLQuery(str, "lib", Library.class);
        query.setParameter("title", title);
        return (Library) query.uniqueResult();
    }