elasticsearch jdbc问题及解决

mvn依赖

    <repositories>
        <repository>
            <id>elastic.co</id>
            <url>https://artifacts.elastic.co/maven</url>
        </repository>
    </repositories>
    
        <dependency>
            <groupId>org.elasticsearch.plugin</groupId>
            <artifactId>x-pack-sql-jdbc</artifactId>
            <version>6.4.0</version>
        </dependency>

问题1：
目前最新的版本是6.5.1，使用这个版本的jdbc会报如下错误

java.lang.NoClassDefFoundError: org/elasticsearch/xpack/sql/client/Version

这是一个bug，会在6.5.2版本解决，折腾了好久，太激进终究不是好事，所以最好使用低版本的jdbc
issues:https://github.com/elastic/elasticsearch/issues/35786

问题2：
异常

current license is non-compliant for [jdbc]

原因：JDBC需要一个白金（或试用）许可证。

解决办法：
修改XPackBuild.java 和LicenseVerifier.java 重新编译elasticsearch；
下载源码

git clone https://github.com/elastic/elasticsearch.git
切换到6.5.1分支
git checkout v6.5.1

修改源码：

vi LicenseVerifier.java

package org.elasticsearch.license;
public class LicenseVerifier
{
    public static boolean verifyLicense(final License license, final byte[] encryptedPublicKeyData) {
        return true;
    }
    public static boolean verifyLicense(final License license) {
        return true;
    }
}

vi XPackBuild.java

package org.elasticsearch.xpack.core;
import org.elasticsearch.common.io.*;
import java.net.*;
import org.elasticsearch.common.*;
import java.nio.file.*;
import java.io.*;
import java.util.jar.*;
public class XPackBuild
{
    public static final XPackBuild CURRENT;
    private String shortHash;
    private String date;
    @SuppressForbidden(reason = "looks up path of xpack.jar directly")
    static Path getElasticsearchCodebase() {
        final URL url = XPackBuild.class.getProtectionDomain().getCodeSource().getLocation();
        try {
            return PathUtils.get(url.toURI());
        }
        catch (URISyntaxException bogus) {
            throw new RuntimeException(bogus);
        }
    }
    XPackBuild(final String shortHash, final String date) {
        this.shortHash = shortHash;
        this.date = date;
    }
    public String shortHash() {
        return this.shortHash;
    }
    public String date() {
        return this.date;
    }
    static {
        final Path path = getElasticsearchCodebase();
        String shortHash = null;
        String date = null;
        Label_0157: {
            shortHash = "Unknown";
            date = "Unknown";
        }
        CURRENT = new XPackBuild(shortHash, date);
    }
}

编译环境

jdk-11.0.1(jdk需要时10以上版本)

配置阿里云仓库

cd elasticsearch-6.5.1
vi build.gradle
在文件最后加上

allprojects{
    repositories {
        def REPOSITORY_URL = 'http://maven.aliyun.com/nexus/content/groups/public/'
        all { ArtifactRepository repo ->
            if(repo instanceof MavenArtifactRepository){
                def url = repo.url.toString()
                if (url.startsWith('https://repo1.maven.org/maven2') || url.startsWith('https://jcenter.bintray.com/')) {
                    project.logger.lifecycle "Repository ${repo.url} replaced by $REPOSITORY_URL."
                    remove repo
                }
            }
        }
        maven {
            url REPOSITORY_URL
        }
    }
}

编译

./gradlew assemble

包生成路径

elasticsearch-6.5.1/distribution/archives/tar/build/distributions/elasticsearch-6.5.1-SNAPSHOT.tar.gz

部署参照

https://blog.csdn.net/woloqun/article/details/84770233

配置文件和之前不同的是添加：xpack.security.enabled: false，贴下完整的

cluster.name: myes
node.name: node2
path.data: /home/qun/data/es/data
path.logs: /home/qun/data/es/log
http.port: 9200
network.host: 0.0.0.0
discovery.zen.ping.unicast.hosts: ["192.168.120.5", "192.168.120.6","192.168.120.7"] 
discovery.zen.minimum_master_nodes: 2
http.cors.enabled: true 
http.cors.allow-origin: "*"
xpack.security.enabled: false

接下来是申请license，地址

https://license.elastic.co/registration

注册完后会给邮箱发邮件，邮件里有license文件下载地址，文件大致像下面这样

xxx-xxx-42c7a741-db4e-492e-8cc1-ac1b8b40defe-v5.json

重命名文件

mv xxx-xxx-42c7a741-db4e-492e-8cc1-ac1b8b40defe-v5.json license.json

修改license.json 文件

"type":"platinum"   #白金版
"expiry_date_in_millis":2524579200999   #截止日期 2050年

导入license.json文件

curl -XPUT -u elastic 'http://192.168.120.5:9200/_xpack/license' -H "Content-Type: application/json" -d @license.json
Enter host password for user 'elastic':
{"acknowledged":true,"license_status":"valid"}

使用elastic用户[elasticsearch自带的]，会提是输入密码，默认为：changeme；到这里，elasticsearch配置就结束了，启动

bin/elasticsearch

测试程序

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;
public class Test {

    public static void main(String[] args) throws Exception{

        Class.forName("org.elasticsearch.xpack.sql.jdbc.jdbc.JdbcDriver");
        String elasticsearchAddress = "192.168.120.5:9200";
        String address = "jdbc:es://http://" + elasticsearchAddress;
        Properties properties = new Properties();
        Connection connection =
                DriverManager.getConnection(address, properties);

        Statement statement = connection.createStatement();
        String sql = "select about,content,message,title,user from blog";
        ResultSet results = statement.executeQuery(sql);
        while (results.next()){
             System.out.println(results.getString(1)+"\t"+results.getString(2)+
                    "\t"+results.getString(3)+"\t"+results.getString(4));
        }
    }
}

打印

i'd like to play	asdf	null	红楼梦
null	null	trying out Elasticsearch	null
about	使用okhttp的put方式增加index	message	title

其实可以直接使用okhttp3发起get，post，put请求elasticsearch，如果是sql语句的话可以使用接口

http://192.168.120.5:9200/_xpack/sql?format=json

伪代码如下

String url = "http://192.168.120.5:9200/_xpack/sql?format=json";
        Map<String,Object> map = new HashMap<String,Object>();
        map.put("query", "select * from  mess limit 3");
        String result = OkHttpUtil.postOrPutOrDelete(url,map,null,OkHttpUtil.OkHttpMethod.POST);

        String[] split = result.split("\n");
        int i = 0;
        for(String s:split){
            System.out.println(++i+"\t"+s);
        }

返回结果json结果

{
    "columns":[
        {
            "name":"dddd",
            "type":"text"
        },
        {
            "name":"field",
            "type":"text"
        },
        {
            "name":"message",
            "type":"text"
        },
        {
            "name":"postDate",
            "type":"date"
        },
        {
            "name":"user",
            "type":"text"
        }
    ],
    "rows":[
        [
            null,
            null,
            "trying out Elasticsearch2",
            "2018-12-04T06:54:32.234Z",
            "kimchy2"
        ],
        [
            null,
            null,
            "trying out Elasticsearch8",
            "2018-12-04T06:54:32.235Z",
            "kimchy8"
        ],
        [
            null,
            null,
            "trying out Elasticsearch1",
            "2018-12-04T06:54:32.119Z",
            "kimchy1"
        ]
    ]
}

参考：

• https://blog.csdn.net/wfs1994/article/details/79998650
• https://blog.csdn.net/wfs1994/article/details/80421922
  end