How to change the @Namednativequery.query dynamic but non-programatically?

Question

Presently how it is

@Service
 class Impl{

    public ResponseEntity<?> find(patientIdList,shapeNameList){
      someEntity =  repository.findByFilters(patientIdList,shapeNameList);

      return someEntity;
    }


 }

---

@Entity    
@NamedNativeQuery(      
        name = "MyEntity.findByFilters",
        query = "_________________________________",
        resultSetMapping = "dummyMapping"       
        ))   
@SqlResultSetMapping(
    name = "dummyMapping",
    ...
)
public class ...{
}

USE CASES desired:

when shapeNameList IS SUPPLIED by clients rest json input, then @Namednativequery.query mentioned above should be: -----------------------------------------------------------------------------------------------------------------------

query = SELECT DISTINCT a.shape_name as shapename, a.patient_id patientId, a.shape_type_id as shapetypeid  
                        FROM pairdb.tbl_sf_density a 
                        WHERE a.patient_id IN( ?1 )
                        AND a.shapeNameList IN( ?2 )  --this line needs to be dynamically incorporated 
                        group by shapename;

when shapeNameList NOT SUPPLIED by clients rest json input, then @Namednativequery.query mentioned above should be:
-----------------------------------------------------------------------------------------------------------------------

query = SELECT DISTINCT a.shape_name as shapename, a.patient_id patientId, a.shape_type_id as shapetypeid 
                        FROM pairdb.tbl_sf_density a 
                        WHERE a.patient_id IN( ?1 )
                        -- AND a.shapeNameList IN( ?2 )  --this line needs to be dynamically omitted from the query within  @NamedNativeQuery above
                        group by shapename;

• Specification + QueryDSl/Criteria seems heavy for the purpose.
• As well StringBuilder to cook up the query String also seems JDBC workaround but not proper elegant solution

I'm not being able to build the perfect solution for this dynamic change of the SQL part. Could someone help please with simple Spring JPA solution ?

OR

Can we just manipulate within the postgres SQL by some logic to switch between 'AND a.shapeNameList IN( ?2 ) ' vs ' ' using COALSCE / NULLIF / IFNULL / CASE WHEN THEN END ? anything ?

That should be the best option I guess. Any help is appreciated.

Answer 1

Specification + QueryDSl/Criteria seems heavy for the purpose.

I don't agree. Since in the comment to Mateusz answer you suggest you have multiple such parameters a Specification seems like a perfect fit.

In similar cases, find-by-example works as well, but it doesn't support in-clauses.

If you are willing to consider @Query annotations you can use SpEL support write something like this:

@Query("... where 0 = ?#{param.size} or a.shapeNameList IN :param")
public Something something(List param);

Typos in the code are not a bug but a training feature.