How to use @NamedQuery in spring a CrudRepository @Query?

Question

I want to make use of a @NamedQuery inside a JpaRepository. But it does not work:

public interface MyEntityRepository extends JpaRepository<MyEntity, Long> {
    @Query(name = MyEntity.FIND_ALL_CUSTOM)
    List<MyEntity> findAllCustom(Pageable pageable);
}

@Entity
@NamedQuery(
    name = MyEntity.FIND_ALL_CUSTOM, query = "select * from MyEntity me where me.age >= 18"
)
public class MyEntity {
    public static final String FIND_ALL_CUSTOM = "findAllCustom";
}

Result:

org.springframework.data.mapping.PropertyReferenceException: No property findAllCustom found for type MyEntity!
    at org.springframework.data.mapping.PropertyPath.<init>(PropertyPath.java:75)
    at org.springframework.data.mapping.PropertyPath.create(PropertyPath.java:327)
    at org.springframework.data.mapping.PropertyPath.create(PropertyPath.java:307)
    at org.springframework.data.mapping.PropertyPath.from(PropertyPath.java:270)
    at org.springframework.data.mapping.PropertyPath.from(PropertyPath.java:241)
    at org.springframework.data.repository.query.parser.Part.<init>(Part.java:76)
    at org.springframework.data.repository.query.parser.PartTree$OrPart.<init>(PartTree.java:235)
    at org.springframework.data.repository.query.parser.PartTree$Predicate.buildTree(PartTree.java:373)
    at org.springframework.data.repository.query.parser.PartTree$Predicate.<init>(PartTree.java:353)
    at org.springframework.data.repository.query.parser.PartTree.<init>(PartTree.java:84)
    at org.springframework.data.jpa.repository.query.PartTreeJpaQuery.<init>(PartTreeJpaQuery.java:61)
    at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$CreateQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:94)
    at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$CreateIfNotFoundQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:205)
    at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$AbstractQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:72)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.<init>(RepositoryFactorySupport.java:369)
    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:1633)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1570)
    ... 28 more

Update:

public interface MyEntityRepository extends JpaRepository<MyEntity, Long> {
    List<MyEntity> findAllCustom(Pageable pageable);
}

@Entity
@NamedQuery(
    name = "MyEntity.findAllCustom", query = "select * from MyEntity me where me.age >= 18"
)
public class MyEntity {

}

Still same exception:

PropertyReferenceException: No property findAllCustom found for type MyEntity!

score 10 · Accepted Answer · edited May 23 '17 at 10:32

10

Take a look at the documentation of Spring Data JPA - Using JPA NamedQueries.

I advise you follow the conventions set in the documentation (starting with the simple name of the configured domain class, followed by the method name separated by a dot). Cut the underscore and name the query like

@NamedQuery(name = "MyEntity.findAllCustom", query="...")

or even better add a suggestive name like findByAge or sth.

To allow execution of these named queries all you need to do is to specify MyEntityRepository as follows:

public interface MyEntityRepository extends JpaRepository <MyEntity, Long> {
    List<MyEntity> findAllCustom();
}

I implemented it with the JpaRepository as the documentation exemplifies. But you could try with a simple CrudRepository and see if that works.

I think the problem was you where using @Query and the Queries annotated to the query method will take precedence over queries defined using @NamedQuery. Read the docs for the @Query usage, i think you where also using it wrong.

Update To use the Pageable, according to this answer

to apply pagination, a second subquery must be derived. Because the subquery is referring to the same fields, you need to ensure that your query uses aliases for the entities/tables it refers to

that means you would rewrite your query like query ="select * from MyEntity me where me.age >= 18".

The example was used for @Query, but that is also a named query so it should apply to your case as well. The only difference is that with @Query you actually bind them directly rather than annotating them to the domain class.

Update 2

I tried in my own app. First off you should have the query using the alias instead of * (i.e me). Secondly the string you use FIND_ALL_CUSTOM is not following the convention which is "MyEntity.findAllCustom".

Solution

Copy paste this:

public interface MyEntityRepository extends JpaRepository<MyEntity, Long> {
    List<MyEntity> findAllCustom(Pageable pageable);
    List<MyEntity> findAllCustom();
}

@Entity
@NamedQuery(
    name = MyEntity.FIND_ALL_CUSTOM, query = "select me from MyEntity me where me.age >= 18"
)
public class MyEntity {
    public static final String FIND_ALL_CUSTOM = "MyEntity.findAllCustom";
}

Both will work. For the one with the pageable method argument call it as myEntityRepository.allCustom(new PageRequest(0,20)). Ofc, you know that myEntityRepository is injected.

edited May 23 '17 at 10:32

Community

1
1

answered Aug 06 '15 at 07:55

Laurentiu L.

6,566
1
34
60

I just discovered that it indeed works as expected, BUT my problem is the parameter `Pageable pageable` that somehow seems to confuse spring in resolving the named query. Do you know what can be done here? – membersound Aug 06 '15 at 08:07
Well the PagingAndSortingRepository abstraction over CrudRepository should make pagination over any entity i think. Assuming you called the method with Pageble argument correctly (say new PageRequest(1, 20) ). There have been reported bugs around this issue( http://forum.spring.io/forum/spring-projects/data/104718-using-spring-data-jpa-named-queries-and-pagination ) . – Laurentiu L. Aug 06 '15 at 08:28
@membersound however, i don't think it's a bug. I updated the answer with a possible explanation. – Laurentiu L. Aug 06 '15 at 08:31
OK, anyways the `@NamedQuery` is only found without `Pagable` argument. As soon as I add it, the error exception above is raised. – membersound Aug 06 '15 at 09:13
Have you added an alias to the query? You mean it can't be called without an argument and without an annotation like in my answer? – Laurentiu L. Aug 06 '15 at 09:19
Yes I added an alias, the setup is now exactly as in my initial question (with `pageable` argument). And that does not work as the startup already fails with the exception above. If I remove the argument, everything works fine! – membersound Aug 06 '15 at 09:35
But the whole point of the answer is that the setup of your initial question is not proper. Use the entity repository in the example with the named query convention and with the updated query and it should work. – Laurentiu L. Aug 06 '15 at 09:37
Sorry, in my code I already changed to `extends JpaRepository`, anyways same result. Also when using alias (the problem is not that the sql query is not executed, but not found at all by the name!). – membersound Aug 06 '15 at 09:39
extends JpaRepository is not the only change. In order for the named query to be found you need to eliminate the @Query annotation altogether and use List findAllCustom() with the NamedQuery(name=MyEntity.findAllCustom). – Laurentiu L. Aug 06 '15 at 09:46
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/85288/discussion-between-membersound-and-laurentiu-l). – membersound Aug 06 '15 at 09:56
Please see my update. Still same error without @Query annotation. – membersound Aug 06 '15 at 09:56
@membersound i updated the answer with the corrections you need to make. – Laurentiu L. Aug 06 '15 at 11:43
Ok, thanks for the solution. Your example worked indeed. Thereby I could identify my real problem: in my code I have an additional method as follows: `List findAllCustom(Sort sort);` When I add this method to your example code, also you code throws the exception. Do you know why this is the case? I need to have both methods, one with pageable and one with sort. – membersound Aug 06 '15 at 12:00
@membersound Indeed if i add a org.springframework.data.domain.Sort, it throws a no property found exception (interestingly if i add a different/wrong package's Sort interestingly it deploys with no error). This may indeed be a bug, or it may not have been inteded to be used this way / no such feature available. For this, you could ask a new question. – Laurentiu L. Aug 06 '15 at 12:12
@membersound if it helps you could apply a static ordering with JPQL by adding an OrderBy clause. None of the guides i went over mention anything about adding a Sort param to a named query. – Laurentiu L. Aug 06 '15 at 12:15

How to use @NamedQuery in spring a CrudRepository @Query?

1 Answers1

Solution