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
.* FROMMutalFundData
WHERE 1=1Cur :"USD", isSh null SELECT
c1
.* FROMMutalFundData
WHERE 1=1 AND CURRENCY =USD
Cur :"USD", isSh true SELECT
c1
.* FROMMutalFundData
WHERE 1=1 AND CURRENCY =USD
AND isSFlag = TRUE
Any way to :
- append where only if needed
- 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?