Is there a way to use constants inside Spring Data @Query annotation value?

Question

I don't want to hardcode constant values, I would rather specify them through a reference variable.

For example, rather then writing the next query:

@Query(value = "SELECT u FROM UserModel u WHERE u.status = 1")

..I would like to extract the hardcoded value '1' and write something like:

@Query(value = "SELECT u FROM UserModel u WHERE u.status = UserModel.STATUS_ACTIVE")  //doesn't compile

Is there a way to specify constants like in the second example inside spring-data queries?

Cleankod · Answer 1 · 2019-12-17T13:59:55.150

47

You have to use fully qualified class name like this:

@Query("SELECT u FROM UserModel u WHERE u.status = com.example.package.UserModel.STATUS_ACTIVE")

The bad thing about it though is that an IDE would not recognise this as an usage of the class UserModel. The only advantage is that you can keep the value in one place, which is sufficient most of the time. This has been resolved in IntelliJ IDEA 2017.1. I don't know about other IDEs.

edited Dec 17 '19 at 13:59

answered Mar 04 '14 at 11:04

Cleankod

2,220
5
32
52

2

this raises an exception that 'com not defined' .. At least for my – bert Apr 24 '14 at 23:02
Did you apply the fully qualified package name for your purpose? I mean you have to point to the correct, existing class with its fully qualified name. – Cleankod Apr 25 '14 at 07:45
Yes I did. I solved the problem in passing in the enum values to compare against. not elegant, but working – bert Apr 25 '14 at 07:47
It works for me, with fully qualified name, but the field that is being compared is defined as `@Enumerated(EnumType.STRING)` in the entity. – wpodgorski Nov 25 '15 at 13:20

score 14 · Answer 2 · answered Aug 23 '13 at 12:41

14

I would recommend creating an Enum and a field of that enum on the entity.

public enum UserModelStatus{
     ACTIVE, INACTIVE
}

public UserModel{

    /* Other fields ommitted */

    @Enumerated(EnumType.STRING)
    private UserModelStatus status;

    /* Get/Set Method */
}

Then create your repository method:

@Repository
public interface UserModelRepository extends JpaRepository<UserModel, Long>{

    public List<UserModel> findByStatus(UserModelStatus status);

}

Using Spring Data you won't even need to write JPQL just call the method like:

   @Autowired
   UserModelRepository userModelRepository;

   public void someMethod(){
       List<UserModel> userModels = userModelRepository.findByStatus(UserModelStatus.ACTIVE);
   }

answered Aug 23 '13 at 12:41

Kevin Bowersox

93,289
19
159
189

1

How can I easily negate the findByStatus(UserModelStatus status)? I mean get all userModel where UserModelStatus is **not** status? – stratosgear Jan 07 '15 at 16:19
1

findByStatusNot(UserModelStatus status) – Christoph Grimmer Dec 19 '16 at 10:09
This shows the full power of spring data. Stick to it for simple queries, no need to write sql yourself. – membersound Jun 07 '17 at 13:50

score 11 · Answer 3 · answered Aug 14 '15 at 02:40

11

Use as follows:

In the repository interface, define a constant as follows:

public static final String USER_QUERY = "SELECT u FROM UserModel u WHERE u.status = " + UserModel.STATUS_ACTIVE;

Now you can use

@Query(value=USER_QUERY)

answered Aug 14 '15 at 02:40

zdesam

2,936
3
25
32

score 8 · Answer 4 · answered Oct 10 '17 at 08:41

I've managed to use class String constant in query via SpEL T() operator, which gives you access to static methods and constants on a given class. For String I have to wrap expression with single quotes ('), probably it will be needed for you as well (if QuerySyntaxException occurs).

Try something like this,

@Query("SELECT u FROM #{#entityName} u " +
       "WHERE u.status = #{T(fully.qualified.path.UserModel).STATUS_ACTIVE}")

Note: somehow it doesn't work if you use UserModel instead of #{#entityName}.

In docs its mentioned briefly, see: https://docs.spring.io/spring/docs/current/spring-framework-reference/core.html#expressions-beandef-xml-based

Don't know since when this is supported, I've got spring-data-jpa 1.4.3, spring-framework 3.2.17

If you want to use parameterized statements with a native query, you can do a `SELECT columns FROM table_name WHERE column_name = ?#{T(fully.qualified.path.ClassName).STATIC_CONSTANT_VARIABLE}` (adding `?`: `?#{T(...`). I needed `WHERE column_name NOT IN (?#{T(namespace.ClassName).STRING_COLLECTION_VARIABLE})` and without the `?` it wasn't parameterized so didn't behave as expected. — Shadow Man, Feb 03 '22 at 00:02

score 2 · Answer 5 · answered May 14 '15 at 22:41

2

The answer to this seems to be 'No' for a standard solution.

Some JPA implementations may have solutions of their own but hibernate for one does not seem to and does not seem to support any of the methods suggested by other answers here.

answered May 14 '15 at 22:41

cmorris

591
5
13

score -2 · Answer 6 · answered Aug 23 '13 at 15:31

-2

When you want to use constants directly inside your @Query annotation you can write something like:

@Query("SELECT u FROM UserModel u WHERE u.status = " + UserModel.STATUS_ACTIVE)

answered Aug 23 '13 at 15:31

Henrik Sachse

51,228
7
46
59

13

This would return an error saying that the attribute value must be constant. – Cleankod Mar 04 '14 at 10:09
That's what I said. This answer is about using constants in the @Query annotation. – Henrik Sachse Mar 05 '14 at 10:52
1

Yes but CONSTANT + CONSTANT is not constant any more :) Hence the error. – Cleankod Mar 05 '14 at 13:56
2

This works for me. If you are expecting a string, don't forget to wrap it in single quotes `'` in the query. – Jeff Smith Apr 15 '15 at 18:56
`The value for annotation attribute Query.value must be a constant expression` – gene b. Jan 20 '22 at 01:48

Is there a way to use constants inside Spring Data @Query annotation value?

6 Answers6

Linked