Writing custom queries in Spring JPA

Question

I have two entities Employee, and Department which are as below:

@Entity
@Table(name="employee")
public class EmployeeDAO implements Serializable {
    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    @Column(unique=true, nullable=false)
    private Long id;

    @Column(name="dept_id")
    private Long deptId;
    ...
}

@Entity
@Table(name="department")
public class DepartmentDAO implements Serializable {
    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    @Column(unique=true, nullable=false)
    private Long id;

    @Column(name="dept_name")
    private String deptName;
    ...
}

Each employee has an id as well as a deptId (which refers to the department id). However, there is no many-to-one relationship between Employee and Department.

I also have a EmployeeRepository which extends JpaRepository:

@Repository
public interface EmployeeRepository extends JpaRepository<EmployeeDAO, Long> {
    List<EmployeeDAO> findByDeptId(@Param("deptId") Long deptId);
}

What I want the findByDeptId() method to do is, given a department id, find all employees who are in that department as well as those that are in a department whose deptName is "default".

I wrote a custom query using @Query as below, but it threw errors. I'm new to JPA, so the below query might be completely wrong.

@Query("select emp from EmployeeDAO emp where emp.deptId in ((select dept.id from DepartmentDAO dept where dept.deptName='default'),:deptId))
List<EmployeeDAO> findByDeptId(@Param("deptId") Long deptId);

This is the error I get:

Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'employeeRepository': Invocation of init method failed; nested exception is java.lang.IllegalArgumentException: Validation failed for query for method public abstract java.util.List EmployeeRepository.findByDeptId(java.lang.Long)!
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1566)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:539)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:476)
    at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:303)
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:230)
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:299)
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:194)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.findAutowireCandidates(DefaultListableBeanFactory.java:1127)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:1051)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:949)
    at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredFieldElement.inject(AutowiredAnnotationBeanPostProcessor.java:533)
    ... 31 more
Caused by: java.lang.IllegalArgumentException: Validation failed for query for method public abstract java.util.List EmployeeRepository.findByDeptId(java.lang.Long)!
    at org.springframework.data.jpa.repository.query.SimpleJpaQuery.validateQuery(SimpleJpaQuery.java:87)
    at org.springframework.data.jpa.repository.query.SimpleJpaQuery.<init>(SimpleJpaQuery.java:57)
    at org.springframework.data.jpa.repository.query.JpaQueryFactory.fromMethodWithQueryString(JpaQueryFactory.java:70)
    at org.springframework.data.jpa.repository.query.JpaQueryFactory.fromQueryAnnotation(JpaQueryFactory.java:51)
    at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$DeclaredQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:137)
    at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$CreateIfNotFoundQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:202)
    at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$AbstractQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:80)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.<init>(RepositoryFactorySupport.java:357)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport.getRepository(RepositoryFactorySupport.java:192)
    at org.springframework.data.repository.core.support.RepositoryFactoryBeanSupport.initAndReturn(RepositoryFactoryBeanSupport.java:239)
    at org.springframework.data.repository.core.support.RepositoryFactoryBeanSupport.afterPropertiesSet(RepositoryFactoryBeanSupport.java:225)
    at org.springframework.data.jpa.repository.support.JpaRepositoryFactoryBean.afterPropertiesSet(JpaRepositoryFactoryBean.java:92)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1625)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1562)
    ... 41 more
Caused by: java.lang.NullPointerException
    at org.hibernate.hql.internal.ast.ParameterTranslationsImpl.getNamedParameterExpectedType(ParameterTranslationsImpl.java:92)
    at org.hibernate.engine.query.spi.HQLQueryPlan.buildParameterMetadata(HQLQueryPlan.java:415)
    at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:150)
    at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:93)
    at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:167)
    at org.hibernate.internal.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:301)
    at org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:236)
    at org.hibernate.internal.SessionImpl.createQuery(SessionImpl.java:1800)
    at org.hibernate.jpa.spi.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:328)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:483)
    at org.springframework.orm.jpa.ExtendedEntityManagerCreator$ExtendedEntityManagerInvocationHandler.invoke(ExtendedEntityManagerCreator.java:344)
    at com.sun.proxy.$Proxy83.createQuery(Unknown Source)
    at org.springframework.data.jpa.repository.query.SimpleJpaQuery.validateQuery(SimpleJpaQuery.java:81)

How should the query be?

Thanks.

Answer 1

I think you should add nativeQuery=true.

Code below:

@Query("select emp from EmployeeDAO emp where emp.deptId in ((select dept.id from DepartmentDAO dept where dept.deptName='default'),:deptId)")
List<EmployeeDAO> findByDeptId(@Param("deptId") Long deptId);