Hibernate Self Join

Question

Background

I have a table with columns id, imageId, propertyId.

There could be many images associated with a single property and there could be many properties associated with a single image.

imageId - many-to-many - propertyId

I have a list of propertyIds and I want to find out all the images associated with all the propertyIds using Hibernate.

Problem.

Just now I pick one property at a time and using the Criteria API find all the imageIds associated with this propertyId, I obtain a list of imageIds. I use this list of imageIds in the next query and say

select imageIds where imageId in (imageIds obtained in previous step) where propertyId = x

So effectively, the number of queries hitting the DB would equal to the number of propertyIds. I don't think this is a good solution, having to hit the database n times, I tried using Detached Query but did not succeed. Is there a better solution?

Also, when I finally obtain the list of imageIds, is it a good idea(let us not take into account the number of results) to store it in the user session and paginate OR is it a good idea to repeat the entire process OR shall I consider storing it in the DB all for the purpose of pagination?

Awaiting responses.

Regards, Shardul.

Answer 1

So you have a class like this(Note: I did not write this with an ide, so the code may not be syntactically correct):

public class Property {

@ManyToMany private List images; }

public class Image {

@ManyToMany private List properties; }

So a query like select property from Property property should get you the list of properties, which you can get to through normal java properties.

Is that what you're looking for, or did I misunderstand the question?

Answer 2

Here's a problem with your database schema:

You're trying to cram 3 separate associations (image - collection, image - property, collection - property) into a single ImageCollection table. This is not a good thing. While you could probably get away with this using plain SQL (at a cost of lots of rows with null columns in that table), you won't be able to properly map this in Hibernate. BTW, this table doesn't need a surrogate primary key.

What you need to do is split it into 3 separate tables and map your model as proper many-to-many in Hibernate. For example:

@Entity
public class Image {
  @ManyToMany
  @JoinTable(
    name="IMAGE_PROPERTIES",
    joinColumns=@JoinColumn(name="IMAGE_ID"),
    inverseJoinColumns=@JoinColumn(name="PROPERTY_ID")
  )
  private Collection<Property> properties;

  ...
}

IMAGE_PROPERTIES table will only have IMAGE_ID and PROPERTY_ID columns; you will have COLLECTION_IMAGES and COLLECTION_PROPERTIES tables just like it as well.

You will then be able to query images by property names like:

from Image img
  left join img.properties as prop
  with prop.name in ('Tag1', 'Tag2', 'Tag3')

If your properties are really like tags, take a look at this article for AND / OR / UNION query examples.

If you properties are really properties (that is, they have the lifecycle of owner entity and can't belong to more than one entity at once), consider splitting them into two tables (one for Images and one for Collections) and using one-to-many associations. That way you won't be needing IMAGE_PROPERTIES and COLLECTION_PROPERTIES tables from above.