elasticsearch通过sql查询数据

大家好，最近公司让封装一下es的客户端api，然后我一顿劈里啪啦的封装了50，60个api，领导告诉我，我封装的不行，不是他们想要的结果。他们想要的是，别人调用的时候，只需要传几个参数就可以的那种，而不需要了解es内部的原理。好吧，这个我服。

作为小卒，我们只有祈祷公司有需求，那么我才不会被裁员。

开始咱们的sql吧：

/**
 * 通过sql进行查询
 * @param  t 实体类
 * @param sql sql脚本（支持mysql语法）
 * @return
 * @throws Exception
 */
public List<T> queryBySQL(String sql, Class<T> t) throws Exception;

@Override
public List<T> queryBySQL(String sql, Class<T> t ) throws Exception {
    String result = null;
    String host = elasticsearchProperties.getHost();
    if (StringUtils.isEmpty(host)) {
        host = Constant.DEFAULT_ES_HOST;
    }
    String ipport = "";
    String[] hosts = host.split(",");
    if (hosts.length == 1) {
        ipport = hosts[0];
    } else {//随机选择配置的地址
        int randomindex = new Random().nextInt(hosts.length);
        ipport = hosts[randomindex];
    }
    ipport = "http://" + ipport;
    logger.info(ipport + "/_sql?format=" + SqlFormat.JSON.getFormat());
    logger.info("{\"query\":\"" + sql + "\"}");

    String username = elasticsearchProperties.getUsername();
    String password = elasticsearchProperties.getPassword();
    if (!StringUtils.isEmpty(username)) {
        result = HttpClientTool.execute(ipport + "/_sql?format=" + SqlFormat.JSON.getFormat(), "{\"query\":\"" + sql + "\"}", username, password);
    }else {
        result = HttpClientTool.execute(ipport + "/_sql?format=" + SqlFormat.JSON.getFormat(), "{\"query\":\"" + sql + "\"}");
    }
    JSONObject sourceAsJsonObject = JSONObject.parseObject(result);
    System.out.println("-----------"+sourceAsJsonObject);
    List<T> query = EsSqlUtils.query(sourceAsJsonObject, t);
    return query;

}

package com.cjkj.es.util;

import org.apache.http.HttpEntity;
import org.apache.http.HttpResponse;
import org.apache.http.auth.AuthScope;
import org.apache.http.auth.UsernamePasswordCredentials;
import org.apache.http.client.CredentialsProvider;
import org.apache.http.client.HttpClient;
import org.apache.http.client.methods.HttpPost;
import org.apache.http.client.methods.HttpRequestBase;
import org.apache.http.client.methods.HttpUriRequest;
import org.apache.http.config.Registry;
import org.apache.http.config.RegistryBuilder;
import org.apache.http.conn.socket.ConnectionSocketFactory;
import org.apache.http.conn.socket.LayeredConnectionSocketFactory;
import org.apache.http.conn.socket.PlainConnectionSocketFactory;
import org.apache.http.conn.ssl.SSLConnectionSocketFactory;
import org.apache.http.conn.ssl.SSLContexts;
import org.apache.http.conn.ssl.TrustStrategy;
import org.apache.http.entity.StringEntity;
import org.apache.http.impl.client.BasicCredentialsProvider;
import org.apache.http.impl.client.CloseableHttpClient;
import org.apache.http.impl.client.HttpClientBuilder;
import org.apache.http.impl.conn.PoolingHttpClientConnectionManager;
import org.apache.http.protocol.HTTP;
import org.apache.http.util.EntityUtils;

import javax.net.ssl.SSLContext;
import java.io.UnsupportedEncodingException;
import java.security.KeyManagementException;
import java.security.KeyStore;
import java.security.KeyStoreException;
import java.security.NoSuchAlgorithmException;
import java.security.cert.CertificateException;
import java.security.cert.X509Certificate;
import java.util.Map;

/**

 * @description: http客户端工具
 * @author: sheng zhang
 * @create:  2019-12-26 12:53
 **/
public class HttpClientTool {
   private static HttpClient mHttpClient = null;

   private static CloseableHttpClient getHttpClient(HttpClientBuilder httpClientBuilder) {
      RegistryBuilder<ConnectionSocketFactory> registryBuilder = RegistryBuilder.<ConnectionSocketFactory>create();
      ConnectionSocketFactory plainSF = new PlainConnectionSocketFactory();
      registryBuilder.register("http", plainSF);
      //指定信任密钥存储对象和连接套接字工厂
      try {
         KeyStore trustStore = KeyStore.getInstance(KeyStore.getDefaultType());
         //信任任何链接
         TrustStrategy anyTrustStrategy = new TrustStrategy() {
            @Override
            public boolean isTrusted(X509Certificate[] x509Certificates, String s) throws CertificateException {
               return true;
            }
         };
         SSLContext sslContext = SSLContexts.custom().useTLS().loadTrustMaterial(trustStore, anyTrustStrategy).build();
         LayeredConnectionSocketFactory sslSF = new SSLConnectionSocketFactory(sslContext, SSLConnectionSocketFactory.ALLOW_ALL_HOSTNAME_VERIFIER);
         registryBuilder.register("https", sslSF);
      } catch (KeyStoreException e) {
         throw new RuntimeException(e);
      } catch (KeyManagementException e) {
         throw new RuntimeException(e);
      } catch (NoSuchAlgorithmException e) {
         throw new RuntimeException(e);
      }
      Registry<ConnectionSocketFactory> registry = registryBuilder.build();
      //设置连接管理器
      PoolingHttpClientConnectionManager connManager = new PoolingHttpClientConnectionManager(registry);
      //构建客户端
      return httpClientBuilder.setConnectionManager(connManager).build();
   }

   private synchronized static HttpClient getESHttpClient() {
      if (mHttpClient == null) {
//            HttpParams params = new BasicHttpParams();
//            //设置基本参数
//            HttpProtocolParams.setVersion(params, HttpVersion.HTTP_1_1);
//            HttpProtocolParams.setContentCharset(params, Constants.CHARSET);
//            HttpProtocolParams.setUseExpectContinue(params, true);
//            //超时设置
//            /*从连接池中取连接的超时时间*/
//            ConnManagerParams.setTimeout(params, Constants.CONMANTIMEOUT);
//            /*连接超时*/
//            HttpConnectionParams.setConnectionTimeout(params, Constants.CONTIMEOUT);
//            /*请求超时*/
//            HttpConnectionParams.setSoTimeout(params, Constants.SOTIMEOUT);
//            //设置HttpClient支持HTTp和HTTPS两种模式
//            SchemeRegistry schReg = new SchemeRegistry();
//            schReg.register(new Scheme("http", PlainSocketFactory.getSocketFactory(), 80));
//            schReg.register(new Scheme("https", SSLSocketFactory.getSocketFactory(), 443));
//            PoolingClientConnectionManager cm = new PoolingClientConnectionManager(schReg);
//            cm.setMaxTotal(Constants.MAXTOTAL);
//            cm.setDefaultMaxPerRoute(Constants.DEFAULTMAXPERROUTE);
//            mHttpClient = new DefaultHttpClient(cm,params);
         mHttpClient = getHttpClient(HttpClientBuilder.create());
      }
      return mHttpClient;
   }

   private synchronized static HttpClient getESHttpClient(String username,String password){
      if(mHttpClient == null){
         mHttpClient = getHttpClientWithBasicAuth(username, password);
      }
      return mHttpClient;
   }

   private static HttpClientBuilder credential(String username, String password) {
      HttpClientBuilder httpClientBuilder = HttpClientBuilder.create();
      CredentialsProvider provider = new BasicCredentialsProvider();
      AuthScope scope = new AuthScope(AuthScope.ANY_HOST, AuthScope.ANY_PORT, AuthScope.ANY_REALM);
      UsernamePasswordCredentials credentials = new UsernamePasswordCredentials(username, password);
      provider.setCredentials(scope, credentials);
      httpClientBuilder.setDefaultCredentialsProvider(provider);
      return httpClientBuilder;
   }

   /**
    * 获取支持basic Auth认证的HttpClient
    * @param username
    * @param password
    * @return
    */
   private static CloseableHttpClient getHttpClientWithBasicAuth(String username, String password){
      return getHttpClient(credential(username, password));
   }

   //设置头信息，e.g. content-type 等
   private static void setHeaders(HttpRequestBase req, Map<String, String> headers){
      if(headers == null){
         return;
      }
      for(Map.Entry<String, String> header : headers.entrySet()){
         req.setHeader(header.getKey(), header.getValue());
      }
   }

   

   /**
    * 执行http请求
    *
    * @param url
    * @param obj
    * @return
    * @throws Exception
    */
   public static String execute(String url, String obj) throws Exception {
      HttpClient httpClient = null;
      HttpResponse response = null;
      httpClient = HttpClientTool.getESHttpClient();
      HttpUriRequest request = postMethod(url, obj);
      response = httpClient.execute(request);
      HttpEntity entity1 = response.getEntity();
      String respContent = EntityUtils.toString(entity1, HTTP.UTF_8).trim();
      return respContent;
   }

   /**
    * 执行http请求
    *
    * @param url
    * @param obj
    * @return
    * @throws Exception
    */
   public static String execute(String url, String obj, String username, String password) throws Exception {
      HttpClient httpClient = null;
      HttpResponse response = null;
      httpClient = HttpClientTool.getESHttpClient(username, password);
      HttpUriRequest request = postMethod(url, obj);
      response = httpClient.execute(request);
      HttpEntity entity1 = response.getEntity();
      String respContent = EntityUtils.toString(entity1, HTTP.UTF_8).trim();
      return respContent;
   }

   private static HttpUriRequest postMethod(String url, String data)
         throws UnsupportedEncodingException {
      HttpPost httpPost = new HttpPost(url);
      if (data != null) {
         httpPost.setEntity(new StringEntity(data, "UTF-8"));
      }
      httpPost.addHeader("Content-Type", "application/json");
      return httpPost;
   }

//    static class Constants {
//        /** 编码*/
//        public static final String CHARSET = HTTP.UTF_8;
//        /*从连接池中取连接的超时时间*/
//        public static final int CONMANTIMEOUT = 2000;
//        /*连接超时*/
//        public static final int CONTIMEOUT = 2000;
//        /*请求超时*/
//        public static final int SOTIMEOUT = 5000;
//        /*设置整个连接池最大连接数*/
//        public static final int MAXTOTAL = 6;
//        /*根据连接到的主机对MaxTotal的一个细分*/
//        public static final int DEFAULTMAXPERROUTE = 3;
//    }
}

 

 

package com.cjkj.es.repository.response;


import com.alibaba.fastjson.JSONObject;
import lombok.Data;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class EsSqlUtils {
    private static final Logger log = LoggerFactory.getLogger(EsSqlUtils.class);

    /**
     * esSql查询
     *
     * @param className 目标类的class
     * @return
     */
    public static <T> List<T> query(JSONObject response, Class<T> className) {
        List<Columns> columns = JSONObject.parseArray(response.getString("columns"), Columns.class);
        List<List> rows = JSONObject.parseArray(response.getString("rows"), List.class);
        EsModel esModel = new EsModel(columns, rows);
        return esModelToJavaObject(esModel, className);
    }

    /**
     * model转换实体类
     * @param esModel
     * @param className
     * @param <T>
     * @return
     * @author sheng zhang
     */
    private static <T> List<T> esModelToJavaObject(EsModel esModel, Class<T> className) {
        Field[] fields = className.getDeclaredFields();
        List<T> result = new ArrayList<>(esModel.getRows().size());
        esModel.getRows().forEach(row -> {
            try {
                // 判断是不是基本数据类型
                if (!isBasicType(className)) {
                    // 新增实体
                    T t = className.newInstance();
                    for (int i = 0; i < fields.length; i++) {
                        // 设置该属性可以修改
                        fields[i].setAccessible(true);
                        for (int j = 0; j < esModel.getColumns().size(); j++) {
                            // 判断属性名和es返回的列名一致
                            if (fields[i].getName().equals(esModel.getColumns().get(j).getName())) {
                                String type = esModel.getColumns().get(j).getType();
                                Object val = row.get(j);
                                System.out.println(fields[i].getType());
                                //实体类中字段的类型
                                Class enteyType = fields[i].getType();
                                // 时间转换Time  // 这里的弊端。时间转换没有办法识别。我只能通过这个列名有没有Time这个单词来判断是不是时间
                                 if (ObjectUtils.identityToString(val) != null && isdate(val.toString())) {
                                    type = "time";
                                }
                                Object o = castValue(type, val, enteyType);
                                fields[i].set(t, o);
                            }
                        }
                    }
                    result.add(t);
                } else {
                    Class enteyType = fields.getClass();
                    String type = esModel.getColumns().get(0).type;
                    Object val = row.get(0);
                    // 基本数据类型
                    Object o = castValue(type, val, enteyType);

                    result.add((T) o);
                }
            } catch (InstantiationException | IllegalAccessException e) {
                e.printStackTrace();
            }
        });

        return result;
    }

    /**
     * 功能：判断字符串是否为日期格式
     *
     * @param strDate
     * @return
     */
    public static boolean isdate(String strDate) {
        Pattern pattern = Pattern
                .compile("^((\\d{2}(([02468][048])|([13579][26]))[\\-\\/\\s]?((((0?[13578])|(1[02]))[\\-\\/\\s]?((0?[1-9])|([1-2][0-9])|(3[01])))|(((0?[469])|(11))[\\-\\/\\s]?((0?[1-9])|([1-2][0-9])|(30)))|(0?2[\\-\\/\\s]?((0?[1-9])|([1-2][0-9])))))|(\\d{2}(([02468][1235679])|([13579][01345789]))[\\-\\/\\s]?((((0?[13578])|(1[02]))[\\-\\/\\s]?((0?[1-9])|([1-2][0-9])|(3[01])))|(((0?[469])|(11))[\\-\\/\\s]?((0?[1-9])|([1-2][0-9])|(30)))|(0?2[\\-\\/\\s]?((0?[1-9])|(1[0-9])|(2[0-8]))))))(\\s(((0?[0-9])|([1-2][0-3]))\\:([0-5]?[0-9])((\\s)|(\\:([0-5]?[0-9])))))?$");
        Matcher m = pattern.matcher(strDate);
        if (m.matches()) {
            return true;
        } else {
            return false;
        }
    }


    /**
     * 主要针对Es返回的类型转换成数据类型包装类
     *
     * @param type es结果集中返回的类型
     * @param val  需要转换的值
     * @time 23:11
     */
    private static Object castValue(String type, Object val, Class enteyType) {
        if (StringUtils.isBlank(type) || val == null || enteyType == null) {
            return null;
        }
        if (enteyType.equals(String.class)) {
            return val.toString();
        } else if (enteyType.equals(Integer.class)) {
            return (Integer) val;
        } else if (enteyType.equals(Long.class)) {
            return Long.valueOf(String.valueOf(val));
        } else if (enteyType.equals(Short.class)) {
            return (Short) val;
        } else if (enteyType.equals(Double.class)) {
            return (Double) val;
        } else if (enteyType.equals(Float.class)) {
            return (Float) val;
        } else if (enteyType.equals(Character.class)) {
            return (Character) val;
        } else if (enteyType.equals(Byte.class)) {
            return (Byte) val;
        } else if (enteyType.equals(Date.class)) {
            return DateUtil.utcToLocal(val + "");
        }
        return null;
//        if ("text".equals(type)) {
//            try {
//                return val.toString();
//                // 防止时间转换失败
//            }catch (IllegalArgumentException e) {
//                return DateUtil.utcToLocal(val + "");
//            }
//        }else if ("long".equals(type)) {
//            try {
//                return Long.parseLong(val + "");
//            }catch (IllegalArgumentException e){
//                 Number num = (Number)Integer.parseInt(val + "");
//                return num.intValue();
//            }catch (Exception e){
//                Number num = (Number)Integer.parseInt(val + "");
//                return num.intValue();
//            }
//        } else if ("time".equals(type)) {
//            // 防止时间转换错误
//            try {
//                return new Date(Long.parseLong(val + "")); // 时间戳转换
//            }catch (NumberFormatException e) {
//                return DateUtil.utcToLocal(val + ""); // 时间转换为yyyy-MM-dd HH:mm:ss 可以更换
//            }
//        }
    }

    /**
     * 判断是不是基本数据类型包装类
     *
     * @author youao.du@gmail.com
     * @time 22:47
     * @params sheng zhang
     */
    private static boolean isBasicType(Class className) {
        if (className == null) {
            return false;
        } else if (className.equals(String.class)) {
            return true;
        } else if (className.equals(Integer.class)) {
            return true;
        } else if (className.equals(Long.class)) {
            return true;
        } else if (className.equals(Short.class)) {
            return true;
        } else if (className.equals(Double.class)) {
            return true;
        } else if (className.equals(Float.class)) {
            return true;
        } else if (className.equals(Character.class)) {
            return true;
        } else if (className.equals(Byte.class)) {
            return true;
        }

        return false;
    }


    /**
     * 针对ES返回数据定义的Dto
     *
     * @author youao.du@gmail.com
     * @time 23:12
     */
    @Data
    static class EsModel {
        private List<Columns> columns;
        private List<List> rows;

        public EsModel(List<Columns> columns, List<List> rows) {
            this.columns = columns;
            this.rows = rows;
        }
    }

    /**
     * EsQuery中使用sql查询。他会返回的列
     *
     * @author youao.du@gmail.com
     * @time 23:13
     */
    @Data
    static class Columns {
        /**
         * 列名
         */
        private String name;

        /**
         * 列的类型
         */
        private String type;
    }
}

 

package com.cjkj.es.repository.response;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.TimeZone;

public class DateUtil {

    /**
     *
     * <p>Description: 本地时间转化为UTC时间</p>
     * @param localTime
     * @return
     * @author zs
     *
     */
    public static Date localToUTC(String localTime) {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Date localDate= null;
        try {
            localDate = sdf.parse(localTime);
        } catch (ParseException e) {
            e.printStackTrace();
        }
        long localTimeInMillis=localDate.getTime();
        /** long时间转换成Calendar */
        Calendar calendar= Calendar.getInstance();
        calendar.setTimeInMillis(localTimeInMillis);
        /** 取得时间偏移量 */
        int zoneOffset = calendar.get(java.util.Calendar.ZONE_OFFSET);
        /** 取得夏令时差 */
        int dstOffset = calendar.get(java.util.Calendar.DST_OFFSET);
        /** 从本地时间里扣除这些差量，即可以取得UTC时间*/
        calendar.add(java.util.Calendar.MILLISECOND, -(zoneOffset + dstOffset));
        /** 取得的时间就是UTC标准时间 */
        Date utcDate=new Date(calendar.getTimeInMillis());
        return utcDate;
    }

    /**
     *
     * <p>Description:UTC时间转化为本地时间 </p>
     * @param utcTime
     * @return
     * @author zs
     *
     */
    public static Date utcToLocal(String utcTime){
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        sdf.setTimeZone(TimeZone.getTimeZone("UTC"));
        Date utcDate = null;
        try {
            utcDate = sdf.parse(utcTime);
        } catch (ParseException e) {
            e.printStackTrace();
        }
        sdf.setTimeZone(TimeZone.getDefault());
        Date locatlDate = null;
        String localTime = sdf.format(utcDate.getTime());
        try {
            locatlDate = sdf.parse(localTime);
        } catch (ParseException e) {
            e.printStackTrace();
        }
        return locatlDate;
    }

    public static void main(String[] args) {
        String time = "2018-10-19 04:23:34";
        System.out.println(DateUtil.utcToLocal(time));
    }
}

 

以上可以实现了通过sql的查询。