Hibernate parameter binding issue with lists-CodePudding

I have the following method in one of my repositories

@Query(value = "select resource_id from tag where resource_type = :resourceType and value in :tags", nativeQuery = true)
    List<String> search(String resourceType, List<String> tags);

If I run this as a raw sql query, I obtain, as expected, several elements as a result:

select resource_id from tag where resource_type = 'color' and value in ('red', 'blue');

However, if I run the search method from my repository or the equivalent from the method createNativeQuery of the class EntityManager, I get no result if "tags" contains multiple values.

2022-04-06 04:36:49.726 DEBUG 14368 --- [nio-8080-exec-1] org.hibernate.SQL                        : select resource_id from tag where resource_type = ? and value in (?)
Hibernate: select resource_id from tag where resource_type = ? and value in (?)
2022-04-06 04:36:49.731 TRACE 14368 --- [nio-8080-exec-1] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [VARCHAR] - [color]
2022-04-06 04:36:49.732 TRACE 14368 --- [nio-8080-exec-1] o.h.type.descriptor.sql.BasicBinder      : binding parameter [2] as [VARCHAR] - [red]

All is well so far, I receive several results, but if I add an element in "tags"...

2022-04-06 04:36:55.041 DEBUG 14368 --- [nio-8080-exec-6] org.hibernate.SQL                        : select resource_id from tag where resource_type = ? and value in (?)
Hibernate: select resource_id from tag where resource_type = ? and value in (?)
2022-04-06 04:36:55.041 TRACE 14368 --- [nio-8080-exec-6] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [VARCHAR] - [color]
2022-04-06 04:36:55.041 TRACE 14368 --- [nio-8080-exec-6] o.h.type.descriptor.sql.BasicBinder      : binding parameter [2] as [VARCHAR] - [res,blue]

This time I receive 0 elements as a result (which is different from the raw sql result). What surprises me is this log line:

Hibernate: select resource_id from tag where resource_type = ? and value in (?)

I expected:

select resource_id from tag where resource_type = ? and value in (?, ?)

What am I doing wrong?

I'm using spring boot 2.6.6 and the default hibernate version bundled with it (5.6.7.Final if I can believe Intellij)

CodePudding user response：

Try @Param("paramName") for each parameter

Like:-
@Query(value = "select resource_id from tag where resource_type = :resourceType and value in :tags", nativeQuery = true)
List search(@Param("resourceType")String resourceType, @Param("tags")List tags);

OR may be the parameter names in your entity class are not like resource_id they may be are like resourceId.

@Query("select t.resourceId from tag t where t.resourceType = :resourceType and t.value in :tags")
List search(@Param("resourceType")String resourceType, @Param("tags")List tags);

CodePudding user response：

Use

@Query(value="select resource_id from tag where (resource_type=?1) and (value IN ?2)", nativeQuery = true);
List<String> search(String resourceType, List<String> tags);