CODEWITHSUNDEEP
springjpaopenjpa
NeplatnyUdaj
NeplatnyUdaj

Reputation: 6242

Combine parameters and hardcoded clauses in Spring JPA @Query

I have a user repository in the application which works nicely for cases like this one: 

@Query(" FROM UserEntity ue WHERE ue.site.id = :site_id 
         and ue.name = :username")
User findByUsername(@Param("site_id") String siteId,
                    @Param("username") String userName);

There is now a new option in one of the user fields, which should prevent the user to appear anywhere in the application. So instead of modifying the whole application, I've decided to modify just the queries in repositories with hardcoded clause like this: 

@Query(" FROM UserEntity ue WHERE ue.site.id = :site_id 
         and ue.name = :username and ue.state != 'Disabled'")
User findByUsername(@Param("site_id") String siteId,
                    @Param("username") String userName);

(The changed part is and ue.state != 'Disabled')

The problem is, that such query doesn't return anything no matter what the value of state is.

I've also tried ue.state not like 'Disabled', but with the same result. I've seen a lot of examples of using @Query, but didn't find any with hardcoded clauses. Is that even possible?

Upvotes: 1

Views: 7123

Answers (1)

prashant thakre
prashant thakre

Reputation: 5147

Yes you can pass the hardcoded values without changing the method signature.

JPA repository

@Query(" FROM Users ue WHERE ue.userLogin = :userLogin  and ue.userName != 'admin'")
public Users cehckeme(@Param("userLogin") String userLogin);

Test Code

ApplicationContext ctx = new ClassPathXmlApplicationContext(
                    "spring_model.xml");
usersRepository = (UsersRepository) ctx.getBean("usersRepository"); 
           System.out.println(usersRepository.cehckeme("pthakre").getUserLogin());

Genrated query 

Hibernate: 
select
    * 
from
    ( select
        users0_.USER_ID as USER1_2_,
        users0_.USER_LOGIN as USER2_2_,
        users0_.USER_NAME as USER3_2_,
        users0_.USER_PASSWORD as USER4_2_ 
    from
        MED_USERS users0_ 
    where
        users0_.USER_LOGIN=? 
        and users0_.USER_NAME<>'admin' ) 
where
    rownum <= ?

Upvotes: 4

Related Questions