@Query annotation with custom Class Spring Boot Java

Question

question for JPA-Users, is the following somehow possible?

@Query(value = "SELECT * FROM Users u WHERE u.status = :sampleClass.status", 
  nativeQuery = true)
List<SampleClass> findBySampleClass(SampleClass sampleClass);

Notice the way I am trying to access SampleClass in the @Query annotation. Wasn't able to get this going, instead went for Criteria and constructed my query old-school.

Answer 1

You could use the approach as mentioned in the JPA docs:

    @Query(value = "SELECT * FROM Users u WHERE u.status = ?1",
 nativeQuery = true)
    List<SampleClass> findBySampleClass(String status);

Please refer to the official docs for further options : https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#_native_queries

Not sure why you are trying to pass the whole object though.Would you care to explain?

Answer 2

There should not be any space in between = and : so change the query to below one.

SELECT * FROM Users u WHERE u.status =: sampleClass.status

Also as your mathod name findBySampleClass, you are trying to find based on SampleClass then why only passing one parameter of SampleClass instead of object??

see this for much clarity.

You can use like below also by indexing parameters like ?1.

@Query(value = "select id,name,roll_no from USER_INFO_TEST where rollNo = ?1", nativeQuery = true)
ArrayList<IUserProjection> findUserUsingRollNo(String rollNo);

Refer : Quote from Spring Data JPA reference docs.

Also, see this section on how to do it with a named native query.

Answer 3

What you are trying to do is not possible because in native queries you have support just for binding direct variables, you can not do fancy access on objects. You could do something similar to your attempt by using either JPQL or HQL (in case you are using hibernate as an ORM provider).

But there is a problem on higher level. You are calling your method findBySampleClass, you are passing SampleClass as an input parameter and expect SampleClass as an output.This means that you would be returning the object itself from semantic point of view. This is suspicious. But going further on, you are doing something different inside the actual query specfication; you are using the status property of the object you passed in. This deviates from the convention that the method name should say what the query will be. In this case the correct(and most natural) way to go is to pass the status as a parameter, rename the method to findBySampleClassStatus or findByStatus.