JPA: 'SELECT DISTINCT' with BLOB columns

java jpa jql
Cogsy picture Cogsy · Jan 26, 2009 · Viewed 7.9k times · Source

I'm trying to run this JPQL query:

SELECT DISTINCT i FROM Table i JOIN i.other o

which promptly fails with:

"Internal Exception: java.sql.SQLException: Columns of type 'BLOB' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT statements because comparisons are not supported for that type."

This error makes sense to me, but how do I get around it?

Answer

DanSingerman picture DanSingerman · Jan 26, 2009

You could store a hash or checksum of the blob object in another column, and use your distinct operator on that.

Example:

SELECT i from Table  WHERE id IN (
  SELECT id FROM (
    SELECT MIN(id) AS id, hash_of_i FROM Table GROUP BY hash_of_i
                 ) t
                                )

I'm sure you can write this SQL more elegantly, but it will give you an idea.

Edit - just realised that using this you can dispense with the Distinct operator altogether (it will be functionally equivalent just removing it).

Edit 2 - I am not sure my first version worked, so have rewritten it

Related questions

How to fix the Hibernate "object references an unsaved transient instance - save the transient instance before flushing" error

I receive following error when I save the object using Hibernate object references an unsaved transient instance - save the transient instance before flushing

Read More
What's the difference between JPA and Hibernate?

I understand that JPA 2 is a specification and Hibernate is a tool for ORM. Also, I understand that Hibernate has more features than JPA 2. But from a practical point of view, what really is the difference? I have experience using …

Read More
Difference between FetchType LAZY and EAGER in Java Persistence API?

I am a newbie to Java Persistence API and Hibernate. What is the difference between FetchType.LAZY and FetchType.EAGER in Java Persistence API?

Read More