We have all been using af:query with “BETWEEN” criteria for fields. This works fairly nicely as long as you give both the values in the criteria while searching. If only one value is given, we get a rather user unfriendly message saying the field is required.
I believe af:query should automatically generate appropriate clause based on how many parameters are supplied in “BETWEEN” criteria. Unfortunately ADF does not handle that currently.
So I will show you how we can achieve this in a generic way. I am going to intercept the query event by providing custom query listener and then check the criteria and change the operator for the “BETWEEN” criteria fields based on how many parameters are added in the search.
To make this generic query listener first thing we have to do is to store the query expression from the af:query by using <f:attribute> tag as below.
Then update the queryListener attribute of af:query to point to our custom generic query listener as shown below.
Below is the code for the generic query listener.
1: public void genericQueryListener(QueryEvent qe) {
2: QueryDescriptor descriptor = qe.getDescriptor(); 3: ConjunctionCriterion conjCrit = descriptor.getConjunctionCriterion(); 4: FacesContext context = FacesContext.getCurrentInstance();5: //Empty criteria check
6: boolean emptyCriteria = true;
7: List<Criterion> criterionList = conjCrit.getCriterionList();8: for (Criterion criterion : criterionList) {
9: AttributeCriterion ac = (AttributeCriterion)criterion;10: for (Object object : ac.getValues()) {
11: //Chekck if the value is empty or <No Selection> item is present in choice list
12: if (object != null && object.toString().trim().length() > 0 &&
13: !"All".equalsIgnoreCase(object.toString().trim())) {
14: emptyCriteria = false;15: break;
16: } 17: } 18: }19: //If no criteria is filled display error message
20: if (emptyCriteria) {
21: FacesMessage fm =22: new FacesMessage(FacesMessage.SEVERITY_ERROR, "Atleast one search criteria is required",
23: "Atleast one search criteria is required");
24: context.addMessage(null, fm); 25: context.renderResponse();26: } else {
27: Map<AttributeCriterion, Operator> changedAttrs =28: new HashMap<AttributeCriterion, Operator>();
29: for (Criterion criterion : criterionList) {
30: AttributeCriterion ac = (AttributeCriterion)criterion; 31: Operator operator = ac.getOperator();32: if ("BETWEEN".equalsIgnoreCase(operator.getValue().toString())) {
33: String op = null; 34: String opDate = null; 35: Object val = null; 36: List<Object> list = (List<Object>)ac.getValues();37: LOGGER.finest("Values Before : {0}", list);
38: if (list.get(0) == null && list.get(1) != null) {
39: op = "<=";
40: opDate = "ONORBEFORE";
41: val = list.get(1); 42: list.set(0, val);43: } else if (list.get(1) == null && list.get(0) != null) {
44: op = ">=";
45: opDate = "ONORAFTER";
46: val = list.get(0); 47: }48: LOGGER.finest("New Operator : {0}", op);
49: LOGGER.finest("Value : {0}", val);
50: if (op != null) {
51: changedAttrs.put(ac, operator);52: for (Operator o :
53: ac.getAttribute().getSupportedOperators()) {54: if (o.getValue().toString().equalsIgnoreCase(op) ||
55: o.getValue().toString().equalsIgnoreCase(opDate)) { 56: operator = o;57: break;
58: } 59: } 60: ac.setOperator(operator); 61: }62: LOGGER.finest("Values After : {0}", ac.getValues());
63: } 64: } 65: String expression =66: (String)qe.getComponent().getAttributes().get("queryExpression");
67: if (expression != null) {
68: JSFUtils.resolveMethodExpression("#{" + expression + "}",
69: Object.class,
70: new Class[] { QueryEvent.class },
71: new Object[] { qe });
72: } 73: 74: for (Criterion criterion : criterionList) {
75: AttributeCriterion ac = (AttributeCriterion)criterion;76: if (changedAttrs.containsKey(ac)) {
77: if (">=".equalsIgnoreCase(ac.getOperator().getValue().toString())) {
78: ac.getValues().set(1, null);79: } else if ("<=".equalsIgnoreCase(ac.getOperator().getValue().toString())) {
80: ac.getValues().set(0, null); 81: } 82: ac.setOperator(changedAttrs.get(ac)); 83: } 84: } 85: } 86: }Generic query listener first checks if there is at least one criteria is filled or not. If no criteria is provided it throws and error message saying “At least one search criteria is required”. This is useful when we are performing search on large amount of data.
Generic query listener then traverses through the criteria list and checks if any of the operator is “BETWEEN”. if it finds any “BETWEEN” criteria, it will check for the values and depending on how many values are supplied it will change the operator to >= or <= or BETWEEN (ONORAFTER, ONORBEFORE , BETWEEN in case if it is date field).
All we need to do now is invoke the queryExpression we set as <f:attribute> tag on af:query.
We can see from the below screenshot how the criteria clauses are generated based on how many values are supplied in BETWEEN criteria.
The last part of the generic query listener reverts the query descriptor to its original state to avoid any changes on the screen in af:query.
I think this solution can be very useful in many ADF applications. Below are the details of the sample application and where to download.
JDeveloper Version : 11.1.1.7.0
Application Download : ADF Query Panel Between criteria customization
