Elasticsearch-Sql+Mybatis+SpringBoot集成

本文介绍了springboot如何集成elasticsearch-sql+mybatis

大家好！ElasticSearch（下面用es作为缩减）作为开源界搜索和数据分析的宠儿，受到了很多公司的青睐，无论是查询速度，还是扩展性，容错性都是很不错的，而且配合官方的logstash和kibana,以及filebeat,更是如虎添翼，但是es的transport client的api是解析非常困难，而且大家又习惯了sql语法，所以在es5.x版本以后已经有elasticsearch-sql的插件，在6.4版本以后，这个插件无需安装，自带的功能。下面给大家介绍一下es-sql的使用方法

rest方式使用es-sql

下面我给大家简要看一下如何用rest方式来执行es-sql

1. curl

   POST /_xpack/sql?format=txt
   {
    "query": "select sum(money) from trade "
   }
   

2. 学习链接地址https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-rest.html

JDBC方式用springboot+mybatis访问

1.maven依赖引入

         <dependency>
            <groupId>org.elasticsearch.plugin</groupId>
            <artifactId>x-pack-sql-jdbc</artifactId>
            <version>6.4.3</version>
        </dependency>

    如果引入失败，将jar包下载下来，用下面的方式引入,将jar包下载下来，到本地文件夹，然后引入

        <dependency>
          <groupId>org.elasticsearch.plugin</groupId>
          <artifactId>x-pack-sql-jdbc</artifactId>
          <version>6.4.3</version>
          <systemPath>${project.basedir}/../lib/x-pack-sql-jdbc-6.4.3.jar</systemPath>
          <scope>system</scope>
      </dependency>

1. mybatis config文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
       PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
       "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
   <settings>    <!-- 开启二级缓存 -->
       <setting name="cacheEnabled" value="true"/>

       <!-- 打印查询语句 -->
       <setting name="logImpl" value="STDOUT_LOGGING" />
       <setting name="useColumnLabel" value="false" />
   </settings>
</configuration>

-比较重要的地方

<setting name="useColumnLabel" value="false" />

这句话一定要添加

2. application.yml添加一下配置

spring:
 datasource: 
   es: 
     url: jdbc:es://host地址:9200
     driver-class-name: org.elasticsearch.xpack.sql.jdbc.jdbc.JdbcDriver
     mapperLocations: classpath:mapper/es/*.xml
     configLocation: classpath:config/mybatis.cfg.xml

4.EsDruidDataSourceConfig 类，我用的是druid连接池，你们在用的时候需要把这个连接池在pom文件中引入一下

import com.alibaba.druid.pool.DruidDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

import javax.sql.DataSource;

/**
* @program: 
* @description: es 数据源配置
* @author: sunyf
* @create: 2018-11-16 19:15
**/
@Configuration
@MapperScan(basePackages = {"cc.youshu.eurekaclient.dao.es"}, sqlSessionFactoryRef = "esSqlSessionFactory")
public class EsDruidDataSourceConfig {

   @Value("${spring.datasource.es.configLocation}")
   private String configLocation;

   @Value("${spring.datasource.es.mapperLocations}")
   private String bigdataMapperLocations;

   @Value("${spring.datasource.es.url}")
   private String esUrl;
   
   @Bean(name = "esDataSource")
   public DataSource esDataSource() {
       DruidDataSource dataSource = new DruidDataSource();
       dataSource.setDriverClassName("org.elasticsearch.xpack.sql.jdbc.jdbc.JdbcDriver");
       dataSource.setUrl(esUrl);
       return dataSource;
   }

   /**
    * SqlSessionFactory配置
    *
    * @return
    * @throws Exception
    */
   @Bean(name = "esSqlSessionFactory")
   public SqlSessionFactory bigdataSqlSessionFactory(@Qualifier("esDataSource") DataSource dataSource) throws Exception {
       SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
       sqlSessionFactoryBean.setDataSource(dataSource);

       PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
       //配置mapper文件位置
       sqlSessionFactoryBean.setMapperLocations(resolver.getResources(bigdataMapperLocations));
       sqlSessionFactoryBean.setConfigLocation(resolver.getResource(configLocation));
       return sqlSessionFactoryBean.getObject();
   }

}

5. 至此已经引入完毕，下面就是和你们正常的一样，写一个mapper文件，然后启动springboot项目，下面是我的mapper文件

import org.apache.ibatis.annotations.Mapper;

/**
1. @program: youshu_stat
2. @description: trade表数据库接口
3. @author: sunyf
4. @create: 2018-11-16 18:08
**/
@Mapper
public interface RwTradeMapper {
   Long testSql();
}

-下面是我的mapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="cc.youshu.eurekaclient.dao.es.RwTradeMapper">
<select id="testSql" resultType="java.lang.Long">
    select trade_no  from rw_trade limit 1
</select>
</mapper>

6. 最后我给出来我的测试类

import cc.youshu.eurekaclient.EurekaClientApplication;
import cc.youshu.eurekaclient.dao.es.RwTradeMapper;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.annotation.Rollback;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;


/**
* @program: youshu_stat
* @description: 测试
* @author: sunyf
* @create: 2018-11-16 18:16
**/

@RunWith(SpringJUnit4ClassRunner.class)
@SpringBootTest(classes = EurekaClientApplication.class)
public class ApplicationTests {

   @Autowired
   private RwTradeMapper rwTradeMapper;

   @Test
   @Rollback
   public void testRwTradeMapper() throws Exception {
       Long s= rwTradeMapper.testSql();
       System.out.println("输出订单号为"+s);
   }

}

使用es-sql需要是购买x-pack,本人提供破解的6.4.0版本jar包，其他步骤，网上教程一堆，jar包地址https://download.csdn.net/download/qq_34748569/10866788

你可以找到更多关于 es-sql的信息 https://www.elastic.co/products/stack/elasticsearch-sql.