Search code examples
mysqlspring-bootspring-data-jpahql

HQL not equal and not in Query is not working


@Query("SELECT i FROM ICD10CM i WHERE i.type NOT IN ('C') AND i.name LIKE %:icdString% OR i.code LIKE %:icdString% OR i.desc LIKE %:icdString%")
    List<ICD10CM> getICD10CMBySearch(@Param("icdString") String icdString);

I'm getting the list of ICD10CM along with which have Type C, I want List of other than Type C. I Tried not in ('C'),i.type != 'C',tried <> and also tried NOT LIKE C%. I want HQL Query for this not a native Query


Solution

  • Try build a proper like condition using concat and build a proper OR condition using ()

      @Query("SELECT i 
        FROM ICD10CM i 
        WHERE i.type NOT IN ('C') 
        AND ( 
          i.name LIKE concat('%',:icdString,'%') 
          OR i.code LIKE  concat('%',:icdString,'%') 
          OR i.desc LIKE  concat('%',:icdString,'%')
        )")
          List<ICD10CM> getICD10CMBySearch(@Param("icdString") String icdString);