前言
有时候,需要将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();
}
});
}
}
}