Search code examples
javajpa

JPA Query ElementCollection key

I have an entity class called User with the variable username with an ElementCollection map to represent "friendship" between two users, mapped by the userId, the start date and the id of the friend. The code for that relation is as follows:

@ElementCollection(fetch = FetchType.EAGER)
@CollectionTable(name = "friendship", joinColumns = @JoinColumn(name="userId"))
@MapKeyColumn(name = "friendId")
@Column(name = "startDate")
private Map<Integer, Date> friendShipMap;

I want to display the friends on a profile page for each user. I know I can just access the map via Java and get the ids (and find the usernames for the friends like that), but I'd like to use a custom @Query to get all the friends usernames for a given id. Somehow, the map is confusing me and I fail to find a proper statement for this.

Any help on this is greatly appreciated.

Edit: To clarify, I would like to use a custom @Query statement for the following SQL statement:

select u.username from users u join friendship m on u.userId = m.friendId where m.userId = 1;

So I thought it would be basically like this:

@Query("SELECT u.username FROM users u join u.friendShipMap m on u.userId = key(m) where m.userId = :userId")
List<User> getFriends(@Param("userId") int userId);

But I can't use m.userId, it says "cannot dereference scalar collection element: userId".

The corresponding table looks like this:

userId | startDate |friendId
Solution

  • You can use this @Query

    @Query("select u.username from Users u inner join u.friendship f where f.friendId = :userId")

    Make sure that you have an association between your entities Users and Friendship. Should be a @OneToMany relation I think.

    And it should be in a List<String> instead of Map.

    EDIT:1

    If I understand it correctly, you will need another class friendship.

    Maybe you need an embeddable class (friendship) described here.

    http://www.thejavageek.com/2014/01/31/jpa-elementcollection-annotation/

    Or you can create a new entity.

    http://www.concretepage.com/hibernate/elementcollection_hibernate_annotation

    And this is also a good example:

    https://en.wikibooks.org/wiki/Java_Persistence/ElementCollection