Check if Entity is in list of mapped entities

Question

I have the following Hibernate code which I believe should be working, but it throws an error:

org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.hibernate.exception.SQLGrammarException: ERROR: syntax error at or near "." Position: 503

The relevant code is the following:

@Override
public List<RepositoryLink> getRepositoryLinks(final DugaUser user) {
    Query query = sessionFactory.getCurrentSession()
        .createQuery("from RepositoryLink link where :user in (link.dugaUsers)");
    query.setParameter("user", user);
    return query.list();
}

Where RepositoryLink is the following:

@Entity
@Table(name = "repository_links")
public class RepositoryLink {
    @Id
    @GeneratedValue
    private Integer id;

    @OneToOne
    @JoinTable(name = "repository_links_github_repositories",
        joinColumns = {@JoinColumn(name = "github_repository_id", referencedColumnName = "id")},
        inverseJoinColumns = {@JoinColumn(name = "repository_link_id", referencedColumnName = "id")})
    private GithubRepository githubRepository;

    @OneToMany
    @JoinTable(name = "repository_links_duga_users",
        joinColumns = {@JoinColumn(name = "duga_user_id", referencedColumnName = "id")},
        inverseJoinColumns = {@JoinColumn(name = "repository_link_id", referencedColumnName = "id")})
    private List<DugaUser> dugaUsers = new ArrayList<>();

    public Integer getId() {
        return id;
    }

    public GithubRepository getGithubRepository() {
        return githubRepository;
    }

    public void setGithubRepository(final GithubRepository githubRepository) {
        this.githubRepository = githubRepository;
    }

    public List<DugaUser> getDugaUsers() {
        return dugaUsers;
    }

    public void setDugaUsers(final List<DugaUser> dugaUsers) {
        this.dugaUsers = dugaUsers;
    }
}

I could probably get it working with some joins, but I thought it would be nicer if I could get the in syntax to work, why doesn't it work like this?

Try removing the parenthesis within `(link.dugaUsers)`. EclipseLink for example doesn't like it. — gtgaxiola, Apr 16 '15 at 17:06
usually I've seen the `in clause` is a named parameter as in [This answer](http://stackoverflow.com/questions/3126366/doing-an-in-query-with-hibernate) Looking at the Hibernate documentation I don't see an example of the type you are trying. — gtgaxiola, Apr 16 '15 at 17:12
@gtgaxiola Yeah, I've noticed that too, but was not able to find out either why this should in fact not be working, nor was I able to find any SO questions that were about this, for some reason, there are plenty of questions about it the other way around — skiwi, Apr 16 '15 at 17:13
@MartinFrey Thanks, that worked! Please add it as answer so I can accept it ;) — skiwi, Apr 16 '15 at 17:19

score 5 · Accepted Answer · answered Apr 16 '15 at 17:21

5

I cannot elaborate directly anymore why this works, without checking the hibernate docs myself. ;)

Use this in your query: IN elements(link.dugaUsers)

answered Apr 16 '15 at 17:21

Martin Frey

10,025
4
25
30

Check if Entity is in list of mapped entities

1 Answers1