elasticsearch通过sql查询数据

素颜马尾好姑娘i 2023-06-27 08:56 16阅读 0赞

大家好，最近公司让封装一下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的查询。