如何将日期筛选器与 MuleSoft OData (版本 2.0) 一起使用

Question

我正在使用MuleSoft（3.9）OData（2.0）RAML并将查询传递给Oracle数据库。在网址中添加日期筛选器 &$filter=START_DATE le datetime'2016-01-01T11:00:00'引发数据库错误：

SQL 命令未正确结束。

如何将日期筛选器添加到 OData RAML？

数据库查询正在生成为 select....where START_DATE <= datetime'2016-01-01T11:00:00' 。我们需要使用 to_date 显式转换吗？

Answer 1

使用以下变量将 OData 筛选器解析为 SQL 筛选器

%var odataFilterToSQLFilter = (odataFilter) -> 
    (( odataFilter replace "eq null" with "is null" 
     replace "ne null" with "is not null" 
     replace " eq " with " = " 
     replace " ne " with " != " 
     replace " gt " with " > " 
     replace " lt " with " < " 
     replace " ge " with " >= " 
     replace " le " with " <= " 
     replace " and " with " AND " 
     replace " or " with " OR " ) splitBy " " map  (
         ("TO_DATE('" ++ (($ replace "datetime'" with "" ) replace "T" with " ") ++ ",'yyyy-MM-dd HH24:MI:SS')") when $ as :string contains "datetime" otherwise $
     )) joinBy " "
%var toSQLWhere = (odataFilter) -> (" WHERE " ++ odataFilterToSQLFilter(odataFilter)) unless odataFilter == null otherwise ""
---
"SELECT " ++ generateSqlFields(filters.select) ++ " FROM $remoteEntityName"
 ++ ( 
    (toSQLWhere(filters.filter))