How to set where in values by array in Database client spring boot webflux-CodePudding

I am using webflux and database Client in Spring boot.I have a String type like this String status="Quoted,Order,Bought"; I want to set these values in (where in) caluse.Code is like this.

String status="Quoted,Order,Bought";
 @Override
public Flux<Quotation> statusCreatedBy(Pageable pageable, String createdBy,String status) {
    RowsFetchSpec<Quotation> list =db.sql("SELECT e.id AS e_id, e.amount AS e_amount, e.description AS e_description FROM quotation e  WHERE e.created_by = '" createdBy "' and e.name in ('" status "') LIMIT " pageable.getPageSize() " OFFSET " pageable.getOffset()).map(this::process);
    return list.all();
}

I got sql syntax like this.

where e.name in ('Quoted,Order,Bought')

what I want is

where e.name in ('Quoted','Order','Bought')

Please kindly guide me to fix this.Also I don't know the number of array values.Sometime status can 4 or 5 value depending on the user.For example,

String status='Quoted,Order,Bought,Cancel,Transfer' or Status='Quoted'

Thanks

CodePudding user response：

Replace "," with "','" using:

status.replace(",", "','")

So your statement looks like:

"… and e.name in ('"   status.replace(",", "','")   "') …"

Although hand building SQL will work, it’s best practice (and easier IMHO) to use a library to substitute variables into placeholders. Consider using JPA, Hibernate etc, .