Posted on

Querying data in elastic search

Hi All,

A common issue in elastic search as querying data from ES server. As typical RDBMS server, we can write like

“select * from tablename where columnA is null”

Similarly, if we have to find this data in ES in kibana , then how we can write it

It will be like

GET IndexName/TypeName/_search
{
"query": {
"bool": {
"must_not": {
"exists": {
"field": "object_desc"
}
}
}
}
}

Similarly we can write this in java API ES 2.2 as below

QueryBuilder query = QueryBuilders.boolQuery()
.mustNot(QueryBuilders.existsQuery("object_desc"));

Client client = getClient();
SearchResponse response = client.prepareSearch(indexName)
.setTypes(type)
.setSearchType(SearchType.QUERY_AND_FETCH)
.setQuery(query)
.setFrom(0)
.setSize(10000)
.setExplain(false)
.execute()
.actionGet();
SearchHit[] searchHits = response.getHits().getHits();

ES query API is quite good and you can test in kibana console.

Happy searching with techartifact…

Posted on

Dynamically changing query in view object in Oracle ADF

I have also written in my past post that how can you change the query my old post to change query . Here is another way.

Sometime requirements can be dynamically change SQL query where clause in View Object. I found on internet .so for the reference, i am sharing.

To use this functionality, you need to override buildWhereClause(StringBuffer sqlBuffer, int noBindVars) method in view object implementation. You use sqlBuffer parameter in this method (which contains SQL query where in StringBuffer) to change SQL in correlation to you needs. Just replace, remove, add string in this parameter. Just remember to use same bind variables names which will be use in VO SQL call (generic bind variables names are named like :vc_temp_1, :vc_temp_2,…).

    @Override  
        protected boolean buildWhereClause(StringBuffer sqlBuffer, int noBindVars) {  
      
            //call super and see if there is where clause.  
            boolean hasWhereClause = super.buildWhereClause(sqlBuffer, noBindVars);  
      
            if (hasWhereClause) {   
                //modify existing where query.  
            }  
            else {   
                //or if you add where clause then return true;  
                hasWhereClause = false;   
      
            }  
      
            return hasWhereClause;  
        }
Posted on

Changing the WHERE clause or VO Query at runtime in Oracle ADF

Requirement- To change the WHERE clause of a query at run time:

Create a String containing the new WHERE clause. Do not include the word “WHERE”. If you do want to remove the WHERE clause entirely, use null. For example, either of the following could be appropriate definitions:

Pass this string into setWhereClause(). For example, if OrdVO is a variable containing the view object instance to be changed, you would call:

String whereClause = “ORDER_TOTAL > 500”;
String whereClause = null;
ordVO.setWhereClause(whereClause);

You can write this method in AmImpl.Java.For example

    public void executeSearchInstanceNoExp(String instanceNo) {
    String whereClause="instance_status = 'EXPIRED'";
    ViewObjectImpl searchVO = this.getEmployeeVo(); //relaventVO
    searchVO.setWhereClause(whereClause);
    searchVO.executeQuery(); //executeVO with Criteria

    }

you can add ORDER BY clauses of a query at runtime by calling setOrderByClause() on the view object.

String orderByClause = "ORDER_TOTAL";
ordVO.setOrderByClause(orderByClause);

Change the VO query at runtime or changing the Table or view at runtime

createViewObjectFromQueryStmt is used to change the query .You can write this method in AmImpl.Java

    public void executeSearchVoExp() {
       String sqlStatement= "select * from EMP";
        ViewObject dynamicVO = this.findViewObject("SearchByCustomerVo1");  
        dynamicVO.remove();  
        dynamicVO = this.createViewObjectFromQueryStmt("SearchByCustomerVo1", sqlStatement);  
        dynamicVO.executeQuery(); //executeVO with Criteria

    }