1

We get parameters from the from the front end :

final String currency - if NULL or empty or "ALL" means all currencies else the one the user picked. No list needed in where

final Boolean isSFlag - if null then this wont be part of where else if the column is true or flase;

Means if currency is null and isSFlag is null then select statement will be :

select col1, col2 from 'collecitonA'

if currenncy is 'INR' then

select col1, col2 from 'collecitonA' where currenncy = 'INR'

If 'INRI and flag is false then :

select col1, col2 from 'collecitonA' where currenncy = 'INR' and flagS = false

What is a good way to build this select? What I have till now:

    /** Create statement per params
 * ignore currency if null or ALL.
 * ignore isSFlag if null */
private Statement crt(final String currency, final Boolean isSFlag) {
    Statement statement = null;
    try {
        System.out.println("Cur :\"" + currency + "\", isSh " + isSFlag);
        Expression whr = createWhere(currency, isSFlag);
        //FromPath pp = select(path(i("c1"), "*"))
         //       .from(i("MutalFundData"));//.as("S") .where(whr);//can I add the where clause only if its non empty ?

        Expression slct = path(i("c1", "c2"));
        final String frm = "MutualFundData";
        if(whr == null){
            statement = select(slct).from(i(frm));
        }else{
            statement = select(slct).from(i(frm)).where(whr);
        }
        System.out.println(statement);

    } catch (Exception e) {
        System.out.println("err  " + e + ", isSh " + isSFlag);
    }
    return statement;
}

private Expression createWhere(final String currency, final Boolean isSFlag) {
    String currencyUpper = currency == null ? null : currency.toUpperCase();
    Expression where = x("1=1");//can i initialize this to some sort of empty expression o
    // Use apache fn for checking if empty
    //if its all or empty string ignore it - dont add to where clause
    if (currency != null && currency.length() > 0 && (!currency.equals("ALL"))) {
        where = where.and(x("CURRENCY")).eq(i(currencyUpper));
    }
    //for SFlag need 3 value from front end - whether all rows, or only where SFlag false or where true, use NULL or an int/ String to denote
    if (isSFlag != null) {
        where = where.and("isSFlag").eq(isSFlag);
    }
    return where;
}

 //not yet using. 
private Expression createWhere2(final String currency, final Boolean isSFlag) {
    String currencyUpper = currency == null ? null : currency.toUpperCase();
    Expression where = null;//can i initialize this to some sort of empty expression o
    // Use apache fn for checking if empty
    //if its all or empty string ignore it - dont add to where clause
    if (currency != null && currency.length() > 0 && (!currency.equals("ALL"))) {
        //where = chckAnd(where, x("CURRENCY")).eq(i(currencyUpper));
        where = x("CURRENCY").eq(i(currencyUpper);
    }
    //for SFlag need 3 value from front end - whether all rows, or only where SFlag false or where true, use NULL or an int/ String to denote
    if (isSFlag != null) {
        where = chckAnd(where, x("isSFlag").eq(isSFlag));
    }
    return where;
}

private Expression chckAnd(Expression a, Expression b){
    if(a == null)return b;
    return a.and(b);
}

This returns

Cur :"", isSh null SELECT c1.* FROM MutalFundData WHERE 1=1

Cur :"USD", isSh null SELECT c1.* FROM MutalFundData WHERE 1=1 AND CURRENCY = USD

Cur :"USD", isSh true SELECT c1.* FROM MutalFundData WHERE 1=1 AND CURRENCY = USD AND isSFlag = TRUE

Any way to :

  1. append where only if needed
  2. initialize the where but have it empty, and dont write AND if not required? Need a flag is initiazled? here using function chckAnd but what if first logical operator was OR? need another helper. Can the Couchbase library be improved?
tgkprog
  • 4,493
  • 4
  • 41
  • 70
  • 2
    If you have ideas for improving the API, [pull requests are welcome](https://github.com/couchbase/couchbase-java-client/blob/master/CONTRIBUTING.md). However, the DSL API has not been migrated to version 3 of the Couchbase Java SDK -- the recommended alternative is to build the N1QL string yourself. – dnault Aug 18 '20 at 19:23

0 Answers0