Spring Data JPA query with dynamic where clause based on incoming request

19 Views Asked by At

I have a situation which I need to be able to have a response with two columns (vNum, vName), and in the request I can expect either v_num or v_name to be passed (must be either one), and also can have optional parameters sent in request as well (either ownerId or rpNum). Below is the query I have that I want to return as a resultset:

select RMV.v_num,v_name
from rp.RP RP
inner join rp.rp_mp_v rmv on RP.rp_num = rmv.rp_num
where replace(owner_id,' ','') = '${ownerId}' --(ownerId string from request which is optional)
and (cast(rmv.v_num as varchar(10)) like '%vNum%' --(int from  request) 
or cast(Rmv.v_name as varchar(10)) like '%vName%'  --(string from  request) )
and RP.rp_number = ${rpNum}; -- (rpNum which is an optional int in request)

My request DTO looks like below:

@Getter
@Setter
@Builder
@AllArgsConstructor
@NoArgsConstructor
@JsonInclude(JsonInclude.Include.NON_NULL)
public class RpVendorRequest {

    private Integer vNum;
    private String vName;
    private Integer rpNum;
    private String ownerId;
}

I want to create a repository for this where I can check for null/not null on these optional parameters from the incoming request. How can I make this happen using spring data jpa? Would it make sense to modify the query to have these null/not null checks or within the request itself? I'm a little new to this so I'm not sure which direction to take. Any suggestions will help. Thank you.

0

There are 0 best solutions below