How do I select the nth element in a Collection by the number in an @IndexColumn?

Question

I have an ItemEntity class, which has a collection of ImageEntity's. I want to be able to fetch a specific ImageEntity, given its index in the collection. For example, for an ItemEntity with 10 ImageEntity's, I want the 4th ImageEntity in as few database hits as possible.

I've read Hibernate In Action and googled, but every piece of documentation I can find is very terse in describing @IndexColumn, with no detailed examples of how to use it.

I can use item.getImages() to fetch a List<ImageEntity>, and then call .get(4) on that.. but that involves loading the whole collection into memory to be able to call get() on it.

What I'd like to do is something like:

int itemId = 111; // id of the item I want to fetch the image for
int wantImageNum = 4; // index of the image I want, in the item's list
imageId = .. somehow get the id of the 4th ImageEntity ..;
ImageEntity img = session.load(ImageEntity.class, imageId);

I have used @IndexColumn to let Hibernate manage the ordering of ImageEntity's in the collection, i.e.:

public class ItemEntity {
...
 @ManyToMany(cascade={CascadeType.MERGE,
    CascadeType.PERSIST,
    CascadeType.REFRESH}, fetch=FetchType.LAZY)
 @JoinTable(name = "ItemImages",
   joinColumns = {
    @JoinColumn(name="item_id", referencedColumnName="id") // id in this class           
   },
   inverseJoinColumns = {
    @JoinColumn(name="image_id") // id in ImageEntity
   }
 )
 @org.hibernate.annotations.IndexColumn(name="idx", base=1)
 private List images = new ArrayList();

Thus, there is a 'join table' that looks like this:

table ItemImages (
    item_id
    image_id
    idx
)

I could do something very dirty with plain SQL, i.e.

select image_id from ItemImages where item_id = :itemId and idx = :wantImageNum;

That is clearly horrible. I can't pull a similar trick using either HQL or Criteria queries because ItemImages is not a mapped entity, it's a join table being managed by Hibernate.

Answer 1

The index() HQL function is what you're looking for:

select image
from ItemEntity item
join item.images image
where index(image) = 4
and item.id = 111

It has been standardized in JPA 2.0 also as INDEX. From the specification:

4.6.17.2.2 Arithmetic Functions

functions_returning_numerics::=
        ABS(simple_arithmetic_expression) |
        SQRT(simple_arithmetic_expression) |
        MOD(simple_arithmetic_expression, simple_arithmetic_expression) |
        SIZE(collection_valued_path_expression) |
        INDEX(identification_variable)

(...)

The INDEX function returns an integer value corresponding to the position of its argument in an ordered list. The INDEX function can only be applied to identification variables denoting types for which an order column has been specified.

In the following example, studentWaitlist is a list of students for which an order column has been specified:

SELECT w.name
FROM Course c JOIN c.studentWaitlist w
WHERE c.name = ‘Calculus’
AND INDEX(w) = 0

References

• Hibernate Core Reference Guide
  • 14.10. Expressions
• JPA 2.0 Specification
  • Section 4.6.17.2.2 Arithmetic Functions