HOw to get result from subquery as param in query

Question

how to write query such that i want to set result from subquery to the parameter of main query. the written query works fine in postgreSQL Query Editor with set dummy value as parameter

Error Log:

org.hibernate.QueryException: Space is not allowed after parameter prefix ':'[select * from public.users as u where u.username=:username and u.password=:password and u.role_id=:(select id from public.user_roles as ur where ur.role=:user_type)] 
at org.hibernate.engine.query.spi.ParameterParser.parse(ParameterParser.java:175) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]"

Souce Code:

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import com.hcs.sws.model.User;

@Repository
public interface UserRepository extends JpaRepository<User, Integer> {
    @Query(value ="select * from public.users as u where u.username=:username and u.password=:password and u.role_id=:(select id from public.user_roles as ur where ur.role=:user_type)",nativeQuery=true)
    public int authenticateUser(@Param("username") String username,@Param("password") String password,@Param("user_type") String user_type);
}

score 0 · Answer 1 · answered Mar 26 '19 at 04:25

first i get result from subquery then store it in variable in java code.After that use that variable for main query.

int role_id=userRoleRepository.findIdByRole(user_type);
        int res = userRepository.authenticateUser(username, password,role_id);
        System.out.println("Query Result: "+res);

HOw to get result from subquery as param in query

1 Answers1