NPE in Spring Data JPA with IN clause

Question

I am using Spring Data JPA 1.7.1 with Hibernate 4.3.7 as my JPA provider. I have the following Spring Data JPA repository:

@Repository
public interface CompanyRepository extends JpaRepository<Company, Integer> {
    @EntityGraph(value = "graph.company.search.results", type = EntityGraph.EntityGraphType.FETCH)
    @Query("SELECT c FROM Company c WHERE c.id IN :companyIds")
    List<Company> findByCompanyIdsForSearchResults(@Param("companyIds") Set<Integer> companyIds);
}

The following code invokes the above repository method:

Set<Integer> companyIds = new HashSet<>();
companyIds.add(100000);
// companyIds.add(100001); // This line breaks the code
List<Company> companies = this.companyRepository.findByCompanyIdsForSearchResults(companyIds);

I am experiencing strange behavior with the above. First of all, if I only put one ID in the set, then two Company instances are returned in my list, even though the ID is obviously unique. Secondly, if I add more than one ID to the set, then the code fails with the following NullPointerException:

java.lang.NullPointerException
    org.hibernate.param.NamedParameterSpecification.bind(NamedParameterSpecification.java:67)
    org.hibernate.loader.hql.QueryLoader.bindParameterValues(QueryLoader.java:616)
    org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1901)
    org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1862)
    org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1839)
    org.hibernate.loader.Loader.doQuery(Loader.java:910)
    org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:355)
    org.hibernate.loader.Loader.doList(Loader.java:2554)
    org.hibernate.loader.Loader.doList(Loader.java:2540)
    org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2370)
    org.hibernate.loader.Loader.list(Loader.java:2365)
    org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:497)
    org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:387)
    org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:236)
    org.hibernate.internal.SessionImpl.list(SessionImpl.java:1264)
    org.hibernate.internal.QueryImpl.list(QueryImpl.java:103)
    org.hibernate.jpa.internal.QueryImpl.list(QueryImpl.java:573)
    org.hibernate.jpa.internal.QueryImpl.getResultList(QueryImpl.java:449)
    sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    java.lang.reflect.Method.invoke(Method.java:483)
    org.springframework.orm.jpa.SharedEntityManagerCreator$DeferredQueryInvocationHandler.invoke(SharedEntityManagerCreator.java:362)
    com.sun.proxy.$Proxy217.getResultList(Unknown Source)
    org.springframework.data.jpa.repository.query.JpaQueryExecution$CollectionExecution.doExecute(JpaQueryExecution.java:110)
    org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:74)
    org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:98)
    org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:89)
    org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:421)
    org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:381)
    org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    org.springframework.data.repository.core.support.RepositoryFactorySupport$DefaultMethodInvokingMethodInterceptor.invoke(RepositoryFactorySupport.java:512)
    org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:98)
    org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:262)
    org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:95)
    org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136)
    org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodIntercceptor.invoke(CrudMethodMetadataPostProcessor.java:122)
    org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
    org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207)
    com.sun.proxy.$Proxy177.findByCompanyIdsForSearchResults(Unknown Source)

    ...

I have also tried to change the Set to a List, but with the same result. The generated query's WHERE clause looks like so: where company0_.id in (?)

Am I doing anything wrong, or is this a bug? The reason why I am using @Query instead of naming my method findByIdIn is that I want the freedom to name my method something unique depending on the context (because the entity graph depends on the context in which the method is invoked from). Is what I am trying to do even supported?

Thank you in advance.

Edit #3: It turns out that the exception is thrown because of the @EntityGraph annotation. This is quite strange because I have used entity graphs with other repository methods that have a @Query annotation, and everything worked fine. It seems, however, as if the combination of @EntityGraph and the IN clause causes problems. It seems to be a bug. If I remove the IN clause and change the method to look up a single company, everything works fine with the entity graph. Does anyone have any ideas for a solution or workaround? I could "manually" JOIN FETCH my associations, but this is not nearly as pretty as using an entity graph.

Edit #2: Interestingly, everything works as expected if I write a custom repository method as described here. The following code works fine:

public List<Company> test(Set<Integer> companyIds) {
    String jpql = "select c from Company c where c.id in :companyIds";
    Query q = this.getEntityManager().createQuery(jpql);
    q.setParameter("companyIds", companyIds);
    List results = q.getResultList(); // Contains X entities, and works with > 1 company IDs as well

    return null;
}

Apparently the problem has something to do with Spring Data JPA's automatic implementation of my interface method. I could just use a "custom" implementation, but it would be much nicer if I could use the first approach so I am still on the look for a solution to the original problem.

Edit #1: Below is source code of the Company entity (excluding getters and setters).

@Entity
@Table(name = "company", uniqueConstraints = {
        @UniqueConstraint(columnNames = { "slug" })
})
@NamedEntityGraphs({
        @NamedEntityGraph(
                name = "graph.company.profile.view",
                attributeNodes = {
                        @NamedAttributeNode(value = "city"),
                        @NamedAttributeNode(value = "acknowledgements"),
                        @NamedAttributeNode(value = "industries"),
                        @NamedAttributeNode(value = "companyServices", subgraph = "companyServices")
                },
                subgraphs = {
                        @NamedSubgraph(name = "companyServices", attributeNodes = {
                                @NamedAttributeNode(value = "service")
                        })
                }
        ),

        @NamedEntityGraph(
        name = "graph.company.search.results",
        attributeNodes = {
                @NamedAttributeNode(value = "city"),
                @NamedAttributeNode(value = "acknowledgements"),
                @NamedAttributeNode(value = "industries"),
                @NamedAttributeNode(value = "companyServices", subgraph = "companyServices")
        },
        subgraphs = {
                @NamedSubgraph(name = "companyServices", attributeNodes = {
                        @NamedAttributeNode(value = "service")
                })
        }
)
})
public class Company {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column
    private int id;

    @NotEmpty
    @Length(min = 5, max = 100)
    @Column(length = 100, nullable = false)
    private String name;

    @Length(min = 3, max = 50)
    @Column(length = 50)
    private String slug;

    @Column
    private Double rating;

    @Column(name = "number_of_reviews")
    private int numberOfReviews;

    @Length(min = 5, max = 50)
    @Column(name = "street_name", length = 50)
    private String streetName;

    @Length(max = 25)
    @Column(name = "street_number", length = 25)
    private String streetNumber;

    @Length(min = 8, max = 11)
    @Column(name = "phone_number", length = 11)
    private String phoneNumber;

    @Length(min = 8, max = 11)
    @Column(name = "second_phone_number", length = 11)
    private String secondPhoneNumber;

    @Length(min = 50, max = 175)
    @Column
    private String teaser;

    @Length(min = 50, max = 5000)
    @Column
    private String description;

    @Length(min = 8, max = 8)
    @Column(name = "ci_number", nullable = false)
    private long ciNumber;

    @ManyToOne(fetch = FetchType.EAGER, targetEntity = City.class, optional = false)
    @JoinColumn(name = "postal_code")
    private City city;

    @ManyToMany(fetch = FetchType.LAZY)
    @JoinTable(name = "company_acknowledgement", joinColumns = @JoinColumn(name = "company_id"), inverseJoinColumns = @JoinColumn(name = "acknowledgement_id"))
    @OrderBy("name")
    private Set<Acknowledgement> acknowledgements = new HashSet<Acknowledgement>();

    @ManyToMany(fetch = FetchType.LAZY)
    @JoinTable(name = "company_industry", joinColumns = @JoinColumn(name = "company_id"), inverseJoinColumns = @JoinColumn(name = "industry_id"))
    @OrderBy("name")
    private Set<Industry> industries = new HashSet<Industry>();

    @OneToMany(fetch = FetchType.LAZY, mappedBy = "company")
    private Set<CompanyService> companyServices = new HashSet<CompanyService>();

    @OneToMany(fetch = FetchType.LAZY, targetEntity = Review.class, mappedBy = "company")
    private Set<Review> reviews = new HashSet<Review>();

    @OneToMany(fetch = FetchType.LAZY, targetEntity = Like.class, mappedBy = "company")
    private Set<Like> likes = new HashSet<Like>();

    @OneToMany(fetch = FetchType.LAZY, mappedBy = "company")
    private Set<AccountCompany> accountCompanies = new HashSet<AccountCompany>();
}

The in clause works without braces - you should remove them. Only old versions of Hibernate required that and it was always a bug that it did. And what if you use another data type, such as a List? Historically what kind of object is expected for the parameter of an in-clause was always a bit finnicky. — Gimby, Jan 26 '15 at 13:35
@Gimby The result is the same if I use a List. I put the parenthesis because I got an error if I didn't. It turns out that the error is the same `NullPointerException` that I get above. But without parenthesis in the `IN` clause, I get this even if I only have one integer in the List/Set. If I add the parenthesis, it only occurs with > 1 integers. Funky stuff. I will remove them, but the NPE exception is still thrown. — ba0708, Jan 26 '15 at 13:41
Funky indeed. Can you try not using spring but just invoking a regular JPA query using an entitymanager to see if at least that works as expected? — Gimby, Jan 26 '15 at 13:52
@Gimby I tested it out by writing a custom repository method, and everything worked as expected. It just doesn't work with Spring Data JPA's automatic implementation. I have edited the question with a code sample. — ba0708, Jan 27 '15 at 13:07

score 5 · Answer 1 · answered Mar 27 '15 at 15:20

5

After facing the same problem and googling a little bit, I found it is an Hibernate bug: https://hibernate.atlassian.net/browse/HHH-9230

But it doesn't seem they are working on it... there is no assignee to this issue :-(

It seems the people at Hibernate doesn't care too much about their JPA implementation... they have some live bugs for a long time and it seems they don't plan to fix them :-(

answered Mar 27 '15 at 15:20

oscar serrano

231
3
12

Thank you for the information! Very useful to know that it is indeed a bug in Hibernate and not on my end. Unfortunately I noticed that the bug was reported in the middle of 2014, so it will probably be a long time until it is fixed. :-( – ba0708 Mar 27 '15 at 15:31
1

This is not the right attitude towards OSS projects. Hibernate is open source, so we should all contribute. The fact that Red Hat pays for some development, it doesn't mean we don't have to get involved. – Vlad Mihalcea Aug 27 '15 at 13:52

Vlad Mihalcea · Answer 2 · 2018-01-05T13:19:32.167

3

Try it with an indexed parameter instead and without the @EntityGraph:

@Query("SELECT c FROM Company c WHERE c.id IN ?1")
List<Company> findByCompanyIdsForSearchResults(Set<Integer> companyIds);

If it works with the index parameter, try adding the @EntityGraph and see it working.

As for the EntityGraph, you must be aware of the Cartesian Product issue you are generating:

@NamedAttributeNode(value = "acknowledgements"),
@NamedAttributeNode(value = "industries"),
@NamedAttributeNode(value = "companyServices", subgraph = "companyServices")

All these associations are one-to-many relations, so when you fetch the Company you will end up with a Cartesian Product between:

Company
Acknowledgement
Industry
CompanyService

This will perform very badly and you should always JOIN FETCH at most one one-to-many association.

I suggest you remove the EntityGraph and use a JOIN FETCH for all many-to-one and at most one-to-many association.

To initialize the other associations you could either:

use Hibernate.initialize
use SUBSELECT or Batch Fetching optimization (Hibernate specific)

edited Jan 05 '18 at 13:19

answered Jan 29 '15 at 22:08

Vlad Mihalcea

142,745
71
566
911

It turns out that the exception is thrown because of the `@EntityGraph` annotation. Without it, the results are loaded correctly. This is strange because I have used the annotation with other repository methods that have the `@Query` annotation, and it worked fine. But it seems as if something goes wrong when it is used in combination with the `IN` clause. I guess this is a bug. I guess I could use `JOIN FETCH` in my query, but the entity graph approach is so much cleaner. Do you have any ideas for a solution/workaround? – ba0708 Jan 30 '15 at 09:40
The reason why I accepted getting back the cartesian product of my entity and its associations is that the number of objects (rows) in each collection will be low (1-5 for most of them), because this is restricted by my business logic. I didn't test it, but I was thinking that the overhead of creating the cartesian product is not too bad compared to issuing multiple queries against my database. I am not sure exactly at which point one approach is faster than the other, as I'd have to test that further. Thank you very much for mentioning this. – ba0708 Jan 30 '15 at 10:48

NPE in Spring Data JPA with IN clause

2 Answers2