CODEWITHSUNDEEP
javajpajql
Cogsy
Cogsy

Reputation: 5642

JPA: 'SELECT DISTINCT' with BLOB columns

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?

Upvotes: 3

Views: 8954

Answers (4)

Alireza Hanifi
Alireza Hanifi

Reputation: 434

use setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY) instead of distinct on your query.

Upvotes: 0

Cogsy
Cogsy

Reputation: 5642

Some inspiration from HermanD lead me to this working solution:

SELECT i FROM Table i WHERE EXISTS (
    SELECT e FROM Table e JOIN e.other o WHERE e.id=i.id
)

Upvotes: 2

DanSingerman
DanSingerman

Reputation: 36502

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

Upvotes: 3

Boris Pavlović
Boris Pavlović

Reputation: 64622

Values in columns of type BLOB are only pointers to actual data storage. In order to apply any of these operators you need to load the data from the BLOB and implement your own logic since the data can represent anything (image, text...)

Upvotes: 0

Related Questions