Date Formatting in Flexible Search

[former_member992972]

In our environment, we are using the embedded HSQL db for our local development and mysql db for our live environments.

My question is, what is the best practice for writing DB-agnostic date formatted strings in flexible search? Meaning, in code, I'd like to write a query with a formatted date and have it work on either DB.

[arvind-kumar_avinash]

The best way to handle this situation is to use ConfigurationService and find the name of the database e.g. if you execute the following code in the groovy console of hAC running in your local environment, it will print HSQLDB specific query; and in your live environment, it will print MySQL specific query.

 dbDriver=spring.getBean("configurationService").getConfiguration().getString("db.driver")

 if (dbDriver.contains("hsqldb")){
   queryString="SELECT {pk} FROM {Product} WHERE {modifiedtime} >= TO_DATE('2019/03/19','YYYY/MM/DD')"
 } else if (dbDriver.contains("mysql")){
   queryString="SELECT {pk} FROM {Product} WHERE {modifiedtime} >= STR_TO_DATE('2019/03/19', '%Y/%m/%d')"
 }

 print queryString

In your code, you need to inject ConfigurationService as follows:

 @Resource(name = "configurationService")
 private ConfigurationService configurationService;

and then use it like

 configurationService.getConfiguration().getString("db.driver")
 String queryString="";
 if (dbDriver.contains("hsqldb")){
   queryString="SELECT {pk} FROM {Product} WHERE {modifiedtime} >= TO_DATE('2019/03/19','YYYY/MM/DD')"
 } else if (dbDriver.contains("mysql")){
   queryString="SELECT {pk} FROM {Product} WHERE {modifiedtime} >= STR_TO_DATE('2019/03/19', '%Y/%m/%d')"
 }
 final FlexibleSearchQuery query = new FlexibleSearchQuery(queryString);