How to query WCS DB from Search application

Introduction
There will be several scenarios where we have to interact with the WCS DB from the search application. We can use JDBCQueryService for the same. This  class deals with complex SQL queries and can deal with select statements, aggregate functions, update, delete etc.

Example scenario: In a specific search profile boost a set of products which has got the column CATENTRY.FIELD1 set to 1.

Steps

• Write a custom expression provider CustomProductBoostExpressionProvider.java which extends AbstractSolrSearchExpressionProvider and override the invoke() method.
• Add the query to a custom .tpl file in the corresponding component configuration. For example Search/xml/config/com.ibm.commerce.catalog-ext/wc-query-utilities.tpl

BEGIN_SQL_STATEMENT
name=getCustomBoostedProducts
base_table=CATENTRY
sql= 
  SELECT catentry_id AS CATENTRY_ID_BOOSTED
FROM CATENTRY
WHERE CATENTRY.FIELD1 = ?param1?
with ur
END_SQL_STATEMENT

• In the expression provider use the below code to run the query

JDBCQueryService service = new JDBCQueryService("com.ibm.commerce.catalog");
ArrayList<String> paramList = new ArrayList(1);
paramList.add("1");
Map queryParameters = new HashMap(1);
queryParameters.put("param1", paramList );
List<HashMap> results = service.executeQuery("getCustomBoostedProducts", queryParameters);

Iterate over the list to get the catentryIds.
Iterator<HashMap> recordIterator = results.iterator();
StringBuffer sb = new StringBuffer();
List<String, String> ids= new ArrayList<String,String>();
while (recordIterator.hasNext()) {
HashMap<String, Object> record = (HashMap) recordIterator.next();
String catentryId = record.get("CATENTRY_ID_BOOSTED").toString();
                       ids.add(catentryId);
}

• Now form the solr filter query and add it to the control parameter

for (String catentryId : ids) {

StringBuilder s = new StringBuilder();

s.append("childCatentry_id");

s.append(":\"");

s.append(catentryId);

s.append("\"^");

s.append(25));

// adds the boost query

addControlParameterValue(SearchServiceConstants.CTRL_PARAM_SEARCH_INTERNAL_BOOST_QUERY, s.toString());

}

The above shows an interaction of search app with WCS DB. We can use this for any component configuration like marketing, foundation, promotion etc