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

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"
sqlspring-bootjpaspring-data-jparepository


Solution 1:[1]

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.

Solution 2:[2]

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);

Solution 3:[3]

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);

Solution 4:[4]

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.

Solution 5:[5]

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.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 JackJun
Solution 2 Ghandy
Solution 3 Piotr Solarski
Solution 4
Solution 5 Aymen B'm

Related Questions