MySQL数据通过Java客户端写入ES

最新推荐文章于 2024-12-25 22:45:24 发布

原创最新推荐文章于 2024-12-25 22:45:24 发布 · 1k 阅读

11 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #java #elasticsearch

前言

有时候，需要将MySQL数据同步写入到ES，以方便实现搜索，建个简单的demo，来实现一下，使用的ES版本较为老旧，为7.3.2

1.项目准备

新建一个Spring Boot项目，引入相关的pom设置

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.7.16</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.example</groupId>
    <artifactId>ft-server</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>ft-server</name>
    <description>ft-server</description>
    <properties>
        <java.version>1.8</java.version>
        <!-- ES版本 -->
        <elasticsearch.version>7.3.2</elasticsearch.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>

        <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.49</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.22</version>
        </dependency>
        <!-- web 依赖 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.2.5</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
            <version>2.7.16</version>
        </dependency>
        <dependency>
            <groupId>com.ft.app</groupId>
            <artifactId>ft-common</artifactId>
            <version>0.0.1-SNAPSHOT</version>
        </dependency>


        <!-- ES依赖 -->
        <dependency>
            <groupId>org.elasticsearch.client</groupId>
            <artifactId>elasticsearch-rest-high-level-client</artifactId>
            <exclusions>
                <exclusion>
                    <groupId>org.elasticsearch</groupId>
                    <artifactId>elasticsearch</artifactId>
                </exclusion>
            </exclusions>
            <version>${elasticsearch.version}</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.elasticsearch/elasticsearch
        -->
        <dependency>
            <groupId>org.elasticsearch</groupId>
            <artifactId>elasticsearch</artifactId>
            <version>${elasticsearch.version}</version>
        </dependency>


        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <scope>test</scope>
        </dependency>

        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-extension</artifactId>
            <version>3.5.3.1</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus</artifactId>
            <version>3.5.3.1</version>
        </dependency>
        <!-- mybatis-plus 依赖 -->
        <!-- https://mvnrepository.com/artifact/com.baomidou/mybatis-plus-boot-starter -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.5.3.1</version>
        </dependency>

        <!-- mybatis-plus 代码生成器依赖 -->
        <!-- https://mvnrepository.com/artifact/com.baomidou/mybatis-plus-generator -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-generator</artifactId>
            <version>3.5.3.1</version>
        </dependency>

        <!-- freemarker 依赖 -->
        <!-- https://mvnrepository.com/artifact/org.freemarker/freemarker -->
        <dependency>
            <groupId>org.freemarker</groupId>
            <artifactId>freemarker</artifactId>
            <version>2.3.31</version>
        </dependency>

    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
        <resources>
            <resource>
                <directory>src/main/resources</directory>
                <includes>
                    <include>**/*.yml</include>
                </includes>
            </resource>
            <resource>
                <directory>src/main/java</directory>
                <excludes>
                    <exclude>**/*.java</exclude>
                </excludes>
            </resource>
            <resource>
                <directory>src/main/resources</directory>
                <includes>
                    <include>**/*.*</include>
                </includes>
            </resource>
        </resources>

    </build>

</project>

2.application.yml文件编写，设置es相关配置

elasticsearch:
  host: 192.168.6.18
  port: 9200
  from: 0
  # 每次请求返回数量
  page-size: 9999

3.新建相关实体类和ES配置类

实体类：略
ES配置类：

package com.example.ftserver.config;

import org.apache.http.HttpHost;
import org.apache.http.client.CredentialsProvider;
import org.apache.http.impl.client.BasicCredentialsProvider;
import org.apache.http.impl.nio.client.HttpAsyncClientBuilder;
import org.elasticsearch.client.RestClient;
import org.elasticsearch.client.RestClientBuilder;
import org.elasticsearch.client.RestHighLevelClient;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

/**
 * @author aaa
 * @description ES配置类
 */
@Configuration
public class ElasticSearchConfig {
    @Value("${elasticsearch.host}")
    private String host;
    @Value("${elasticsearch.port:}")
    private Integer port;


    @Bean
    public RestHighLevelClient getRestHighLevelClient(){
        final CredentialsProvider credentialsProvider = new BasicCredentialsProvider();
        RestClientBuilder restClientBuilder = RestClient.builder(new HttpHost(host, port, "http")).setHttpClientConfigCallback((HttpAsyncClientBuilder builder) ->
                builder.setDefaultCredentialsProvider(credentialsProvider));
        return new RestHighLevelClient(restClientBuilder);
    }
}

4.Service业务处理类

Service接口：

package com.example.ftserver.service;

import com.baomidou.mybatisplus.extension.service.IService;
import com.test.ft.common.entity.PositionEntity;

import java.util.List;
import java.util.Map;

/**
 * <p>
 *  服务类
 * </p>
 *
 * @author aaa
 */
public interface PositionService extends IService<PositionEntity> {

    /**
     *
     * 根据条件查询es数据
     * @param keyword 查询分词器
     * @param pageNo 当前页
     * @param pageSize 每页条数
     * @return {@link List}<{@link Map}<{@link String}, {@link Object}>>
     */
    List<Map<String, Object>> searchPos(String keyword, int pageNo, int pageSize);

    /**
     * 将MySQL数据导入到es
     */
    void importAll();
}

实现类：写入的方式有很多种，可以使用原生的jdbc连接查询之后写入，也可以使用mybatis的查询后写入

package com.example.ftserver.service.impl;

import cn.hutool.json.JSONUtil;
import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.example.ftserver.connection.DBConnection;
import com.fasterxml.jackson.annotation.JsonFormat;
import com.google.common.collect.Lists;
import com.test.ft.common.assrt.MyAssert;
import com.example.ftserver.mapper.PositionMapper;
import com.example.ftserver.service.PositionService;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.test.ft.common.entity.PositionEntity;
import com.test.ft.common.exception.CommonException;
import lombok.AllArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.elasticsearch.action.ActionListener;
import org.elasticsearch.action.bulk.BackoffPolicy;
import org.elasticsearch.action.bulk.BulkProcessor;
import org.elasticsearch.action.bulk.BulkRequest;
import org.elasticsearch.action.bulk.BulkResponse;
import org.elasticsearch.action.index.IndexRequest;
import org.elasticsearch.action.search.SearchRequest;
import org.elasticsearch.action.search.SearchResponse;
import org.elasticsearch.client.RequestOptions;
import org.elasticsearch.client.RestHighLevelClient;
import org.elasticsearch.common.unit.ByteSizeUnit;
import org.elasticsearch.common.unit.ByteSizeValue;
import org.elasticsearch.common.unit.TimeValue;
import org.elasticsearch.common.xcontent.XContentType;
import org.elasticsearch.index.query.QueryBuilders;
import org.elasticsearch.search.SearchHit;
import org.elasticsearch.search.SearchHits;
import org.elasticsearch.search.builder.SearchSourceBuilder;
import org.springframework.stereotype.Service;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.*;
import java.util.*;
import java.util.concurrent.TimeUnit;
import java.util.function.BiConsumer;

/**
 * <p>
 * 服务实现类
 * </p>
 *
 * @author aaa
 */
@Slf4j
@Service
@AllArgsConstructor
public class PositionServiceImpl extends ServiceImpl<PositionMapper, PositionEntity> implements PositionService {

    /**
     * ES客户端
     */
    private RestHighLevelClient client;

    /**
     * 索引名
     */
    private static final String POSITIOIN_INDEX = "position";

    /**
     * 根据条件查询es数据
     *
     * @param keyword  查询分词器
     * @param pageNo   当前页
     * @param pageSize 每页条数
     * @return {@link List}<{@link Map}<{@link String}, {@link Object}>>
     */
    @Override
    public List<Map<String, Object>> searchPos(String keyword, int pageNo, int pageSize) {
        List<Map<String, Object>> resultList = Lists.newArrayList();
        SearchSourceBuilder builder = new SearchSourceBuilder();
        builder.from((pageNo - 1) * pageSize);
        builder.size(pageSize);
        if (StringUtils.isBlank(keyword)) {
            builder.query(QueryBuilders.matchAllQuery());
        } else {
            builder.query(QueryBuilders.matchQuery("positionName", keyword));
        }
        builder.timeout(new TimeValue(60L, TimeUnit.SECONDS));
        try {
            SearchResponse response = client.search(new SearchRequest(POSITIOIN_INDEX).source(builder), RequestOptions.DEFAULT);
            SearchHits responseHits = response.getHits();
            SearchHit[] hits = responseHits.getHits();
            if (hits.length > 0) {
                Arrays.stream(hits).forEach(e -> resultList.add(e.getSourceAsMap()));
            }
        } catch (Exception e) {
            throw new CommonException(e);
        }

        return resultList;
    }

    /**
     * 将MySQL数据导入到es
     */
    @Override
    public void importAll() {
        writeToEsByMapper();
    }

    /**
     * 使用jdbc链接写入es
     */
    private void writeToEs() {
        BulkProcessor bulkProcessor = this.getBulkProcessor(client);
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DBConnection.getConn();
            MyAssert.notNull(conn, "链接不能为空");
            String sql = "select * from " + POSITIOIN_INDEX;
            ps = conn.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
            // 根据需求设置
            ps.setFetchSize(20);
            rs = ps.executeQuery();
            ResultSetMetaData data = rs.getMetaData();

            List<Map<String, Object>> list = new ArrayList<>();
            HashMap<String, Object> hashMap;
            int count = 0;
            String key;
            Object value;
            while (rs.next()) {
                count++;
                hashMap = new HashMap<>(128);
                for (int i = 1; i < data.getColumnCount(); i++) {
                    key = data.getColumnName(i);
                    value = rs.getString(key);
                    hashMap.put(key, value);
                }
                list.add(hashMap);
                // 每10条写一次，不足的批次的最后再一并提交
                if (count % 10 == 0) {
                    log.info("Mysql handle data number : " + count);
                    for (Map<String, Object> map : list) {
                        bulkProcessor.add(new IndexRequest(POSITIOIN_INDEX).source(map));
                    }
                    // 每提交一次便将map与list清空
                    hashMap.clear();
                    list.clear();
                }
            }
            // 处理其他未提交的数据
            list.forEach(e -> bulkProcessor.add(new IndexRequest(POSITIOIN_INDEX).source(e)));
            // 将数据刷新到es, 注意这一步执行后并不会立即生效，取决于bulkProcessor设置的刷新时间
            bulkProcessor.flush();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBConnection.close(conn, ps, rs);

        }


    }


    /**
     * 使用Mybatis plus查询写入es，可以设置自定义条件
     * 
     */
    private void writeToEsByMapper() {
        BulkProcessor bulkProcessor = this.getBulkProcessor(client);
        try {
            List<PositionEntity> mapList = baseMapper.selectList(Wrappers.lambdaQuery(PositionEntity.class));
            if (CollectionUtils.isNotEmpty(mapList)) {
                List<List<PositionEntity>> partition = Lists.partition(mapList, 10);
                for (List<PositionEntity> list : partition) {
                    for (PositionEntity map : list) {
                        // 处理带.0的时间格式
                       /* Class<? extends PositionEntity> mapClass = map.getClass();
                        Field[] fields = mapClass.getDeclaredFields();
                        Arrays.stream(fields).filter(e-> {
                            try {
                                e.setAccessible(true);
                                return Objects.nonNull(e.getAnnotation(JsonFormat.class)) &&
                                        Objects.nonNull(e.get(map));
                            } catch (IllegalAccessException ex) {
                                throw new RuntimeException(ex);
                            }
                        }).forEach(a->{
                            try {
                                a.set(map, String.valueOf(a.get(map)).replace(".0", ""));
                            } catch (IllegalAccessException e) {
                                throw new RuntimeException(e);
                            }
                        });*/
                        bulkProcessor.add(new IndexRequest(POSITIOIN_INDEX).source(JSONUtil.toJsonStr(map), XContentType.JSON));
                    }
                }
            }
            // 将数据刷新到es, 注意这一步执行后并不会立即生效，取决于bulkProcessor设置的刷新时间
            bulkProcessor.flush();
        } catch (Exception e) {
            throw new CommonException(e);
        }


    }


    private BulkProcessor getBulkProcessor(RestHighLevelClient client) {
        BulkProcessor bulkProcessor = null;
        try {
            BulkProcessor.Listener listener = new BulkProcessor.Listener() {
                @Override
                public void beforeBulk(long executionId, BulkRequest request) {
                    log.info("Try to insert data number : " + request.numberOfActions());
                }

                @Override
                public void afterBulk(long executionId, BulkRequest request, BulkResponse response) {
                    log.info("************** Success insert data number : " + request.numberOfActions() + " , id: " + executionId);
                }

                @Override
                public void afterBulk(long executionId, BulkRequest request, Throwable failure) {
                    log.error("Bulk is unsuccess : " + failure + ", executionId:" + executionId);
                }
            };
            BiConsumer<BulkRequest, ActionListener<BulkResponse>> bulkConsumer =
                    (request, bulkListener) -> client.bulkAsync(request, RequestOptions.DEFAULT, bulkListener);
            BulkProcessor.Builder builder = BulkProcessor.builder(bulkConsumer, listener);
            builder.setBulkActions(5000);
            builder.setBulkSize(new ByteSizeValue(100L, ByteSizeUnit.MB));
            builder.setConcurrentRequests(10);
            builder.setFlushInterval(TimeValue.timeValueSeconds(60L));
            builder.setBackoffPolicy(BackoffPolicy.constantBackoff(TimeValue.timeValueSeconds(1L), 3));
            // 注意点：让参数设置生效
            bulkProcessor = builder.build();
        } catch (Exception e) {
            log.error("获取处理器异常", e);
            try {

                boolean close = bulkProcessor.awaitClose(100L, TimeUnit.SECONDS);
                log.info("关闭结果：" + close);
            } catch (Exception e1) {
                log.error("关闭异常",e1);
            }
        }
        return bulkProcessor;
    }
}

4.测试查询结果

写入结果：
在这里插入图片描述
查询结果：

结语：

BulkProcessor getBulkProcessor(RestHighLevelClient client)方法，在ES官网上有样例示范，链接地址：
https://www.elastic.co/guide/en/elasticsearch/client/java-api/7.3/java-docs-bulk-processor.html

附录jdbc连接类：

package com.example.ftserver.connection;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Arrays;

/**
 * @author aaa

 * @description 数据库链接
 */
public class DBConnection {

    public static final String url = "jdbc:mysql://localhost:3306/ft_test?useUnicode=true&autoReconnect=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false";
    public static final String name = "com.mysql.jdbc.Driver";
    public static final String user = "root";
    public static final String password = "root";

    public static Connection getConn() {
        try {
            Class.forName(name);
            return DriverManager.getConnection(url, user, password);//获取连接
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public static void close(Connection connection, Statement statement) {
        close(statement, connection);
    }


    public static void close(Connection connection, Statement statement, ResultSet resultSet) {
        close(resultSet, statement, connection);
    }

    //AutoCloseable... resources：多个AutoCloseable实例，通过源码我们可以知道collection，statement，resultSet都是           AutoCloseable的子类
    private  static void close(AutoCloseable... resources) {
        if (resources != null && resources.length > 0) {
            Arrays.stream(resources).forEach(source -> {
                try {
                    if (source != null) {
                        source.close();
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                }
            });
        }
    }



}