CODEWITHSUNDEEP
javaspring-bootjpacrud
Jamie
Jamie

Reputation: 41

Why does this native query work when hardcoded but not with @Param?

I'm trying to run the following query

select * from Person where name || '|' || age in ('Fred|19','Sam|17','Chris|20')

Code using JPA

public interface PersonRepository extends JpaRepository<Person, Long> {
    @Query(value = ":query", nativeQuery = true)
    List<Person> findAllInList(@Param("query") String query);
}

The above gives

SQL Error: 900, SQLState: 42000
ORA-00900: invalid SQL statement

So I did some experimenting and just hard coded the string in

public interface PersonRepository extends JpaRepository<Person, Long> {
    @Query(value = "select * from Person where name || '|' || age in ('Fred|19','Sam|17','Chris|20')", nativeQuery = true)
    List<Person> findAllInList();
}

The above gives me the correct result. The query also works when running it using DBeaver and Oracle SQL developer. My original thought was that substituting parameters into 'value' using @Param is just a 1 to 1 replacement so something else must be going on. What am I missing here to make it work as a parameter?

Upvotes: 2

Views: 597

Answers (1)

Davide D&#39;Alto
Davide D&#39;Alto

Reputation: 8236

My original thought was that substituting parameters into 'value' using @Param is just a 1 to 1 replacement so something else must be going on

It's not. You cannot substitute any SQL substring. The JPA specification define where you can place parameters.

If you want to pass queries as parameter, it's easier to use a custom repository and the entity manager:

@Repository
public interface PersonRepository {
 
    List<Person> findAllInList(String query);
}
    
public class PersonRepositoryImpl implements PersonRepository {
     
    @PersistenceContext
    private EntityManager entityManager;
 
    @Override
    public List<Person> findAllInList(String query) {
        return entityManager
            .createNativeQuery(query, Person.class)
            .getResultList();
    }
}

Upvotes: 1

Related Questions