How do I use the same parameter multiple times in my Query using JPA?

user_15989592 0
Viewed 2409

How do I use a parameter multiple times in the same query? Here is my Query:

@Query(value = """
        SELECT * FROM person WHERE first_name = ? or last_name = ?
        """, nativeQuery = true)
List<Person> findPerson(String name);

I need to use the "name" parameter twice in the query, how do I do that?

NOTE: This is just a dummy example, for me to understand the logic.

I've tried this:

@Query(value = """
    SELECT * FROM person WHERE first_name = ?1 or last_name = ?1
    """, nativeQuery = true)
List<Person> findPerson(String name);

I get the following error:

org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1"

I've also tried this:

@Query(value = """
    SELECT * FROM person WHERE first_name = :name or last_name = :name
    """, nativeQuery = true)
List<Person> findPerson(@Param("name") String name);

This gives me the same error:

org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1"
spring-data-jpa spring-boot repository jpa sql
5 Answers

Like this

@Query(value = "SELECT * FROM person WHERE first_name = ?1 or last_name = ?1", nativeQuery = true)
List<Person> findPerson(String name);

There ?1 refers to the first arg.

You can also use a specific name to represent it. Like this:

@Query(value = "SELECT * FROM person WHERE first_name = :myName or last_name = :myName", nativeQuery = true)
List<Person> findPerson(@Param(name = "myName") String name);

Hope helpul for you.

user_124668030

I found a solution, using your exemple :

@Query(value = """
        Declare @Name Varchar(max) = :name
        SELECT * FROM person WHERE first_name = @Name or last_name = @Name
        """, nativeQuery = true)
List<Person> findPerson(String name);
user_176662900

You can simply use ?1 as reference to first parameter multiple times. Example based on your code:

@Query(value = """
    SELECT * FROM person WHERE first_name = ?1 or last_name = ?1
    """, nativeQuery = true)
List<Person> findPerson(String name);

Or you can use named parameters multiple times:

@Query(value = """
    SELECT * FROM person WHERE first_name = :name or last_name = :name
    """, nativeQuery = true)
List<Person> findPerson(@Param("name") String name);
user_62676620

Why you don't use two params as below?

@Query(value = "
SELECT * FROM person WHERE first_name = :fname or 
last_name = :lname
", nativeQuery = true)
List<Person> findPerson(@Param("fname") String 
fnamee,@Param("lname") String lname  );

Or you can use repository method as below.

List<Person>  findByFirstNameOrLastNamee(String firstName, 
String lastName);

Pass same name twice.

user_79846210

I don't know about using a Query But when using JPA syntax It's so simple you can do it by using the @Param annotation

List<Person>  findByFirstNameContainingOrLastNameContaining(
@Param("yourParam")String word1,
@Param("TheSameParamName")String word2)

Spring will use the same parameter name.

EDITED

i suppose you can modify your code like this following the same idea

@Query(value = """
    SELECT * FROM person WHERE first_name = :name or last_name = :name1
    """, nativeQuery = true)
List<Person> findPerson(
@Param("name") String name,
@Param("name") String name1);

Notice that you just need to change the secound variable name and using the @Param annotation the same value of @Param("name") will be used.

user_174465700
Related