Using Named Parameter in native SQL query with Spring Data JPA

Question

Was given the below query to run against a DB2 database however it returns below error in my Spring Boot App.

I've researched that :COL_1_1001 should be some sort of named parameter or host variable(DB2) but have not been able to find a similar example of what I'm trying to do. I have only ever used ?1 to set a parameter using @Query which to my understanding is for JPQL. Since I'm attempting a native SQL query here, not sure if this should work.

@Repository 
public interface SomeRepository extends JpaRepository<Entity, EntityID> {

@Query(value="SELECT COL_1"
              + ",COL_2"
              + ",COL_3"
              + "FROM TABLE_A"
              + "WHERE COL_1 = :COL_1_1001", nativeQuery = true)
List<Entity> getEntityDetails();
}

[ERROR]~2019-09-19-07.26.30.347CDT~~~~~ o.a.c.c.C.[.[.[.[dispatcherServlet] Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessResourceUsageException: Named parameter not bound : COL; nested exception is org.hibernate.QueryException: Named parameter not bound : COL] with root cause org.hibernate.QueryException: Named parameter not bound : COL

Piotr Podraza · Accepted Answer · 2019-09-19 14:30:29Z

5

You use variable COL_1_1001 in your query but do not provide it. You have to add an argument annotated with @Param so the value can be injected into query.

Change:

@Query(value="SELECT COL_1"
              + ",COL_2"
              + ",COL_3"
              + "FROM TABLE_A"
              + "WHERE COL_1 = :COL_1_1001", nativeQuery = true)
List<Entity> getEntityDetails();

to:

@Query(value="SELECT COL_1"
              + ",COL_2"
              + ",COL_3"
              + "FROM TABLE_A"
              + "WHERE COL_1 = :col", nativeQuery = true)
List<Entity> getEntityDetails(@Param("col") String col); // it may not be a string - use the actual tyle of COL_1

For more info look into Spring Data documentation

answered Sep 19, 2019 at 14:30

Piotr Podraza

2,0491 gold badge18 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Meryemb Over a year ago

please, I have a question can we use the same expression with list of parameters I mean doing this (...WHERE user in (:userCol)) or they are some changes to make?

iamfnizami · Accepted Answer · 2020-09-29 11:03:35Z

0

@Wallwalker, Here you need to pass parameter in query method as expected using @Param annotation.

Spring-JPA with PostgreSql: Some time this error ask to cast field and if not matched then shows.

nested exception is org.springframework.dao.InvalidDataAccessResourceUsageException: Named parameter not bound : text;

Hence, you need to cast explicitly. e.g.

age::integer=20
OR
CAST('2020-12-22' as DATE)
OR
mob_col1::text=substr(mob_array_col1::text, 1, 10) 
OR
cast(mob_col1 as text)=substr(cast(mob_array_col1 as text), 1, 10)

Reference: PostgreSql-substr

answered Sep 29, 2020 at 11:03

iamfnizami

3032 silver badges8 bronze badges

Collectives™ on Stack Overflow

Using Named Parameter in native SQL query with Spring Data JPA

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related