JPA in query does not seem to return any result

Question

I am trying to run an in query (springboot+jpa+mysql). I have enabled the debug logs, the query seem to be fine, however spring jpa does not return any result.

Here are the configs:

  <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.4.2</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>

     <dependencies>
        <!-- Spring dependencies -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.23</version>
            <scope>runtime</scope>
        </dependency>
    </dependencies>

Here is my repository:

@Query(value = "select * from targeting_locations where targeting_location_master_id in (:targetingLocationMasterIds)", nativeQuery = true)
List<TargetingLocations> findAllByTargetingLocationMasterIdIn(@Param("targetingLocationMasterIds") List<Long> targetingLocationMasterIds);

// Or

@Query("SELECT t FROM TargetingLocations t WHERE t.targetingLocationMasterId IN :targetingLocationMasterIds")
    List<TargetingLocations> findAllByTargetingLocationMasterIdIn(@Param("targetingLocationMasterIds") List<Long> targetingLocationMasterIds);

Point to be noted here is that, the column in 'in' query is not the primary key.

@Entity
@Table(name = "targeting_locations")
public class TargetingLocations {

    @Id
    @Column(name = "id")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    public Long id;

    @Column(name = "geohash")
    public String geoHash;

    @Column(name = "site_name")
    public String siteName;

    @Column(name = "city")
    public String city;

    @Column(name = "country")
    public String country;

    @Column(name = "state")
    public String state;

    @Column(name = "latitude")
    public Double latitude;

    @Column(name = "longitude")
    public Double longitude;

    @Column(name = "targeting_type")
    public String targetingType;

    @Column(name = "targeting_data")
    public String targetingData;

    @Column(name = "targeting_location_master_id")
    public Long targetingLocationMasterId; // ----> in query in this column

Query generated:

Hibernate: 
    select
        targetingl0_.id as id1_1_,
        targetingl0_.city as city2_1_,
        targetingl0_.country as country3_1_,
        targetingl0_.geohash as geohash4_1_,
        targetingl0_.latitude as latitude5_1_,
        targetingl0_.longitude as longitud6_1_,
        targetingl0_.site_name as site_nam7_1_,
        targetingl0_.state as state8_1_,
        targetingl0_.targeting_data as targetin9_1_,
        targetingl0_.targeting_location_master_id as targeti10_1_,
        targetingl0_.targeting_type as targeti11_1_ 
    from
        targeting_locations targetingl0_ 
    where
        targetingl0_.targeting_location_master_id in (
            ?
        )
2021-02-23 16:22:46.199 TRACE 28282 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [BIGINT] - [13063]

Have you ran above query. Is it returning results? Try findAllByTargetingLocationMasterIdList instead of findAllByTargetingLocationMasterIdIn or check https://stackoverflow.com/questions/18987292/spring-crudrepository-findbyinventoryidslistlong-inventoryidlist-equivalen — Vivek, Feb 23 '21 at 11:29
could you try using `Is` instead of `In` and unwrap the single element collection, just to see, if the id actually matches? — Jens Schauder, Feb 23 '21 at 12:14

score 1 · Answer 1 · answered Feb 23 '21 at 12:26

Just define that in your repository as a method.

List<TargetingLocations> findByTargetingLocationMasterIdIn(List<Long> targetingLocationMasterIds);

Remove @Query and also the name should start with findBy... not findAll...

What you want to achieve will be implemented and come automatically from spring-jpa

JPA in query does not seem to return any result

1 Answers1