在迭代交接项目的时候，发现原项目用的是org.elasticsearch.common.xcontent，其本质还是在拼接DSL，代码的可读性和可维护性都比较差，如果不借助SQL转DSL工具，对于不太熟悉语法的人来说，调试耗时，故改成ES-SQL。
本项目的es版本是6.8.x，所以支持es的sql语法，代码sample如下：

ES-SQL

  1. package net.xinhuamm.nmedia.cms.sample;
  3. import okhttp3.Call;
  4. import okhttp3.Credentials;
  5. import okhttp3.MediaType;
  6. import okhttp3.OkHttpClient;
  7. import okhttp3.Request;
  8. import okhttp3.RequestBody;
  9. import okhttp3.Response;
  11. import java.io.IOException;
  12. import java.util.HashMap;
  13. import java.util.Map;
  14. import java.util.concurrent.TimeUnit;
  16. /**
  17.  * @author zhanqian
  18.  * @Date 2021/6/21 13:45
  19.  * @Description TODO
  20.  */
  21. public class ElasticSqlSample {
  22.     static final int timeOut = 10 * 1000;
  24.     public static void main(String[] args) throws IOException {
  25.         Map<String, Object> param = new HashMap<>();
  26.         // param.put("query", "select name, searchAll FROM cms_index where match(type, '4') and match(lesseeId, '44cfaf082c804f168584b632fa16610e') and match(searchAll, '1') and (match(name, '1') or  match(creator, '1') or  match(tags, '1') or   match(content, '1') or  match(description, '1') or  match(labelAiText, '1') or  match(personAiText, '1')) ");
  27.         param.put("query", "select name FROM cms_index where match(type, '4') and match(lesseeId, '44cfaf082c804f168584b632fa16610e') and match(searchAll, '融媒') and match('name^6,creator^5,tags^4,content^3,description^2,labelAiText^2,personAiText^2', '融媒') limit 1");
  28.         param.put("fetch_size", 2);
  29.         String url = "http://localhost:9200/_xpack/sql?format=json";
  30.         System.out.println(okhttpPost(url, param));
  31.     }
  33.     /**
  34.      * Post请求 带Authorization认证
  35.      * */
  36.     public static String okhttpPost(String get_url, Map<String, Object> map){
  37.         OkHttpClient client = new OkHttpClient.Builder()
  38.             .connectTimeout(10000L, TimeUnit.MILLISECONDS)
  39.             .readTimeout(10000L, TimeUnit.MILLISECONDS)
  40.             .build();
  41.         final String credential = Credentials.basic("account", "password");
  42.         String result = "";
  43.         // RequestBody fromBody = generateParametersForPost(get_data).build();
  44.         MediaType JSON = MediaType.parse("application/json; charset=utf-8");
  45.         RequestBody fromBody = RequestBody.create(JSON, com.alibaba.fastjson.JSON.toJSONString(map));
  46.         Request request = new Request.Builder()
  47.             .url(get_url)
  48.             .header("Authorization", credential)
  49.             .header("Accept", "application/json")
  50.             .post(fromBody)
  51.             .build();
  52.         Call call = client.newCall(request);
  54.         try {
  55.             Response response = call.execute();
  56.             //判断是否成功
  57.             if (response.isSuccessful()){
  58.                 result = response.body().string();
  59.             } else {
  60.                 return "请求失败";
  61.             }
  62.             System.out.println(result);
  63.         } catch (Exception e) {
  64.             e.printStackTrace();
  65.             System.out.println("网络GET请求失败！提示信息："+e.getMessage());
  66.         }
  67.         return result;
  68.     }
  69. }

单独抽取出SQL：

  1. select name 
  2. FROM cms_index 
  3. where match(type, '4') 
  4.     and match(lesseeId, '44cfaf082c804f168584b632fa16610e') 
  5.   and match(searchAll, '融媒') 
  6.   and match('name^6,creator^5,tags^4,content^3,description^2,labelAiText^2,personAiText^2', '融媒') 
  7. limit 1
  1. sql中支持es DSL语义match等操作
  2. 如果想用boost，搜索权重。参考：'name^6,creator^5,tags^4,content^3,description^2,labelAiText^2,personAiText^2', '融媒' 表示该6个字段都匹配’融媒’字段，并且以’^’后的数字即为权重

目前REST API支持的参数不是很多，其他参数可以参考上面的文档，如分页超时时间等参数：

  1. param.put("fetch_size", 2);

ES-SQL返回数据封装

ES-SQL format=json的情况下，返回的json格式不太好封装成对象，故借助CGLIB，根据返回的动态字段，创建动态对象。

CGLIB

  1. package net.xinhuamm.nmedia.cms.model;
  3. import lombok.extern.slf4j.Slf4j;
  4. import org.junit.Test;
  5. import org.springframework.cglib.beans.BeanGenerator;
  6. import org.springframework.cglib.beans.BeanMap;
  8. import java.util.HashMap;
  9. import java.util.Map;
  11. /**
  12.  * @author zhanqian
  13.  * @Date 2021/6/21 19:52
  14.  * @Description cglib动态生成bean
  15.  */
  16. @Slf4j
  17. public final class CglibBean {
  19.     /**
  20.      * 实体object
  21.      */
  22.     private Object object = null;
  24.     private BeanMap beanMap = null;
  26.     public CglibBean() {
  27.         super();
  28.     }
  30.     public CglibBean(Map<String, Class<?>> propertyMap) {
  31.         this.object = generateBean(propertyMap);
  32.         this.beanMap = BeanMap.create(this.object);
  33.     }
  35.     /**
  36.      * 给bean属性赋值
  37.      *
  38.      * @param property 属性名
  39.      * @param value    值
  40.      */
  41.     public void setValue(String property, Object value) {
  42.         beanMap.put(property, value);
  43.     }
  45.     /**
  46.      * 通过属性名得到属性值
  47.      *
  48.      * @param property 属性名
  49.      * @return 值
  50.      */
  51.     public Object getValue(String property) {
  52.         return beanMap.get(property);
  53.     }
  55.     /**
  56.      * 将成员属性map生成对象类
  57.      *
  58.      * @param propertyMap
  59.      * @return
  60.      */
  61.     public Object generateBean(Map<String, Class<?>> propertyMap) {
  62.         if (propertyMap == null || propertyMap.isEmpty()) {
  63.             return null;
  64.         }
  65.         BeanGenerator generator = new BeanGenerator();
  66.         for (Map.Entry<String, Class<?>> entry : propertyMap.entrySet()) {
  67.             generator.addProperty(entry.getKey(), entry.getValue());
  68.         }
  69.         return generator.create();
  70.     }
  72.     @Test
  73.     public void test() throws ClassNotFoundException {
  74.         Map<String, Class<?>> propertyMap = new HashMap<>();
  75.         propertyMap.put("id", Class.forName("java.lang.Long"));
  76.         propertyMap.put("name", Class.forName("java.lang.String"));
  77.         CglibBean bean = new CglibBean(propertyMap);
  78.         bean.setValue("id", 1);
  79.         bean.setValue("name", "zhanqian");
  81.         log.info("id:{}, name:{}", bean.getValue("id"), bean.getValue("name"));
  82.     }
  83. }

EsHelper

  1. package net.xinhuamm.nmedia.cms.helper;
  3. import com.alibaba.fastjson.JSONArray;
  4. import com.alibaba.fastjson.JSONObject;
  5. import net.xinhuamm.nmedia.cms.model.CglibBean;
  6. import net.xinhuamm.nmedia.common.util.StringUtils;
  7. import org.junit.Test;
  9. import java.io.BufferedReader;
  10. import java.io.File;
  11. import java.io.FileReader;
  12. import java.io.IOException;
  13. import java.util.ArrayList;
  14. import java.util.HashMap;
  15. import java.util.List;
  16. import java.util.Map;
  18. /**
  19.  * @author zhanqian
  20.  * @Date 2021/6/21 20:06
  21.  * @Description TODO
  22.  */
  23. public class EsHelper {
  25.     /**
  26.      * es返回数据类型和java数据类型映射
  27.      */
  28.     Map<String, Class<?>> classPropertyMap = new HashMap<String, Class<?>>() {
  29.         {
  30.             try {
  31.                 put("boolean", Class.forName("java.lang.Boolean"));
  32.                 put("byte", Class.forName("java.lang.Byte"));
  33.                 put("short", Class.forName("java.lang.Short"));
  34.                 put("long", Class.forName("java.lang.Long"));
  35.                 put("integer", Class.forName("java.lang.Integer"));
  36.                 put("string", Class.forName("java.lang.String"));
  37.                 put("double", Class.forName("java.lang.Double"));
  38.                 put("float", Class.forName("java.lang.Float"));
  39.                 put("half_float", Class.forName("java.lang.Float"));
  40.                 put("text", Class.forName("java.lang.String"));
  41.                 put("keyword", Class.forName("java.lang.String"));
  42.                 put("ip", Class.forName("java.lang.String"));
  43.             } catch (ClassNotFoundException e) {
  44.                 e.printStackTrace();
  45.             }
  46.         }
  47.     };
  49.     /**
  50.      * 封装es sql返回值
  51.      * @param json  聚合查询的返回值参考 @see "聚合查询返回值.txt"
  52.      * @return 按每一行row为单位对象，返回list
  53.      */
  54.     public List<CglibBean> getEsSqlResult(String json) {
  55.         JSONObject jsonObject = JSONObject.parseObject(json);
  56.         JSONArray columns = jsonObject.getJSONArray("columns");
  57.         Map<String, Class<?>> propertyMap = new HashMap<>();
  59.         int size = columns.size();
  60.         String[] nameProps = new String[size];
  61.         Class[] typeProps = new Class[size];
  62.         for (int i = 0; i < size; i++) {
  63.             JSONObject column = (JSONObject)columns.get(i);
  64.             String name = column.getString("name");
  65.             Class<?> type = classPropertyMap.get(StringUtils.lowerCase(column.getString("type")));
  66.             propertyMap.put(name, type);
  67.             nameProps[i] = name;
  68.             typeProps[i] = type;
  69.         }
  71.         List<CglibBean> list = new ArrayList<>();
  72.         //通过rows数据，生成对象
  73.         JSONArray rows = jsonObject.getJSONArray("rows");
  74.         for (Object object : rows) {
  75.             //通过columns的元数据属性，动态生成类
  76.             CglibBean cglibBean = new CglibBean(propertyMap);
  78.             JSONArray row = (JSONArray)object;
  79.             for (int i = 0; i < size; i++) {
  80.                 Object value = row.get(i);
  81.                 if (value == null) {
  82.                     continue;
  83.                 }
  85.                 Class typeProp = typeProps[i];
  86.                 // 分开判断是因为部分类型，强转会失败，如es的colums中返回是Long类型，对应的rows值在java中为Integer，强行set到Long类型中会报错
  87.                 if (typeProp == Long.class && value != null) {
  88.                     cglibBean.setValue(nameProps[i], Long.parseLong(value.toString()));
  89.                 } else {
  90.                     cglibBean.setValue(nameProps[i], value);
  91.                 }
  92.             }
  93.             list.add(cglibBean);
  94.         }
  96.         return list;
  97.     }
  99.     @Test
  100.     public void test() throws IOException {
  101.         StringBuffer path = new StringBuffer(System.getProperty("user.dir"));
  102.         path.append(File.separator + "doc" + File.separator + "agg-test.json");
  103.         File file = new File(path.toString());
  104.         BufferedReader reader = null;
  105.         StringBuffer sbf = new StringBuffer("");
  106.         try {
  107.             reader = new BufferedReader(new FileReader(file));
  108.             String tmp;
  109.             while ((tmp = reader.readLine()) != null) {
  110.                 sbf.append(tmp);
  111.             }
  112.             reader.close();
  113.         } finally {
  114.             if (reader != null) {
  115.                 reader.close();
  116.             }
  117.         }
  118.         String json = sbf.toString();
  119.         List<CglibBean> esSqlResult = new EsHelper().getEsSqlResult(json);
  120.         System.out.println(esSqlResult);
  121.     }
  123.     public static void main(String[] args) throws IOException {
  125.         System.out.println();
  126.     }
  127. }

文献：
[1] elasitic官方网站6.8版本[OL].https://www.elastic.co/guide/en/elasticsearch/reference/6.8/xpack-sql.html