Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

NPE in Spring Data JPA with IN clause

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>();
}
like image 862
ba0708 Avatar asked Jan 26 '15 13:01

ba0708


People also ask

Can we use Spring data JPA and Hibernate together?

You cant actually use both of them in the same application. For backwards compatibility. We are expanding our application and want to start using Spring Data JPA, but still keep the old hibernate implementation. Its better you develop your new application as a separate microservice and use spring data jpa ..

Do we need Hibernate with Spring data JPA?

Hibernate provides a reference implementation of the Java Persistence API that makes it a great choice as an ORM tool with the benefits of loose coupling. Remember, Spring Data JPA always requires the JPA provider such as Hibernate or Eclipse Link.

What is the difference between spring JPA and Spring data JPA?

Speaking precisely, Spring Data JPA is an add-on for JPA. It provides a framework that works with JPA and provides a complete abstraction over the Data Access Layer. Spring Data JPA brings in the concept of JPA Repositories, a set of Interfaces that defines query methods.

Does JPA use prepared statements?

Java Persistence API (JPA) provider allows prepared statements to remain open too long when using a native SQL statement to update or insert data. .


2 Answers

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 :-(

like image 119
oscar serrano Avatar answered Nov 04 '22 11:11

oscar serrano


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)
like image 36
Vlad Mihalcea Avatar answered Nov 04 '22 13:11

Vlad Mihalcea