本文共 6631 字，大约阅读时间需要 22 分钟。

数据库到 Elasticsearch 同步与 Spring Boot 实现搜索功能

一、数据库到 Elasticsearch 同步

1. 配置 JDBC 连接

在 sync_tanle.cfg 配置文件中，设置数据库 JDBC 连接信息：

input {
  jdbc {
    jdbc_connection_string => "jdbc:mysql://192.168.230.1:3306/ddd?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT+8"
    jdbc_user => "root"
    jdbc_password => "123123"
    jdbc_driver_library => "/opt/logstash-7.7.0/config/sync/mysql-connector-java-8.0.13.jar"
    jdbc_driver_class => "com.mysql.cj.jdbc.Driver"
    jdbc_paging_enabled => true
    jdbc_page_size => "50000"
    jdbc_default_timezone => "Asia/Shanghai"
    statement => "SELECT ... FROM pms_product p LEFT JOIN pms_sku_stock ps ON p.id = ps.product_id GROUP BY p.name"
    schedule => "* * * * *"
    use_column_value => true
    tracking_column => "create_time"
    tracking_column_type => "timestamp"
    last_run_metadata_path => "area_logstash_capital_bill_last_id"
    clean_run => false
  }
}
filter {
  date {
    match => [ "create_time", "yyyy-MM-dd HH:mm:ss" ]
    timezone => "Asia/Shanghai"
  }
}
output {
  elasticsearch {
    hosts => ["192.168.230.134:9200"]
    index => "jq_product"
    document_id => "%{id}"
    template_overwrite => true
  }
  stdout {
    codec => json_lines
  }
}

2. 执行同步命令

在 /opt/logstash-7.7.0/bin/ 目录下运行：

./logstash -f /opt/logstash-7.7.0/config/sync/sync_tanle.cfg

注意：每次执行前需杀死之前的 Logstash 进程：

ps -ef | grep logstash
kill -9 5388

3. 查看同步效果

运行完成后，检查 Elasticsearch 索引数据是否已同步：

curl http://192.168.230.134:9200/jq_product/_doc

二、Spring Boot 项目实现搜索功能

1. 项目配置

在 application.yml 中配置：

spring:
  application:
    name: jq-search
  elasticsearch:
    rest:
      uris: http://192.168.230.134:9200
mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
server:
  port: 5053
eureka:
  client:
    service-url:
      defaultZone: http://localhost:5051/eureka

2. 实体类定义

创建 SerchModel 实体类：

package com.jq.model;
import lombok.Data;
import org.springframework.data.elasticsearch.annotations.Document;
import org.springframework.data.elasticsearch.annotations.Field;
import org.springframework.data.elasticsearch.annotations.FieldType;
@Data
@Document(indexName = "j_product")
public class SerchModel {
    private Long id;
    @Field(type = FieldType.Text, analyzer = "ik_max_word")
    private String name;
    @Field(type = FieldType.Text)
    private String brand_name;
    @Field(type = FieldType.Text)
    private String product_category_name;
    @Field(type = FieldType.Text)
    private String publish_status;
    @Field(type = FieldType.Text)
    private String new_status;
    @Field(type = FieldType.Text, analyzer = "ik_max_word")
    private String sub_title;
    @Field(type = FieldType.Text)
    private String sku_code;
    @Field(type = FieldType.Text)
    private String price;
    @Field(type = FieldType.Text)
    private String pic;
    @Field(type = FieldType.Text)
    private String sale;
    @Field(type = FieldType.Text)
    private String sp_dat;
}

3. 分页类定义

创建 PageBean 类：

package com.jq.page;
import lombok.Data;
import java.util.List;
@Data
public class PageBean {
    private Integer page;
    private Integer size;
    private Long total;
    private List data;
}

4. 搜索逻辑实现

创建 ProductSearch 类：

package com.jq.search;
import com.jq.page.PageBean;
import lombok.Data;
@Data
public class ProductSearch extends PageBean {
    private String key;
}

创建 SearchProductServiceImpl 实现类：

package com.jq.service.impl;
import com.jq.model.SerchModel;
import com.jq.search.ProductSearch;
import com.jq.service.SearchProductService;
import org.apache.commons.lang3.StringUtils;
import org.elasticsearch.index.query.QueryBuilders;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.elasticsearch.core.ElasticsearchRestTemplate;
import org.springframework.data.elasticsearch.core.SearchHit;
import org.springframework.data.elasticsearch.core.SearchHits;
import org.springframework.data.elasticsearch.core.query.NativeSearchQuery;
import org.springframework.data.elasticsearch.core.query.NativeSearchQueryBuilder;
import org.springframework.stereotype.Service;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Stream;
@Service
public class SearchProductServiceImpl implements SearchProductService {
    @Autowired
    private ElasticsearchRestTemplate elasticsearchRestTemplate;
    @Override
    public ProductSearch searchProduct(ProductSearch productSearch) {
        if (StringUtils.isBlank(productSearch.getKey())) {
            productSearch.setKey("手机");
        }
        Pageable pageable = PageRequest.of(productSearch.getPage(), productSearch.getSize());
        NativeSearchQueryBuilder builder = new NativeSearchQueryBuilder();
        NativeSearchQuery query = builder.withQuery(QueryBuilders.queryStringQuery(productSearch.getKey()))
                .withPageable(pageable)
                .build();
        SearchHits
   
     search = elasticsearchRestTemplate.search(query, SerchModel.class);
        Stream
    
     
      > searchHitStream = search.get();
        List
      
        list = new ArrayList<>();
        searchHitStream.forEach(searchHit -> {
            list.add(searchHit.getContent());
        });
        productSearch.setData(list);
        productSearch.setTotal(search.getTotalHits());
        return productSearch;
    }
}
      
     
    
   

5. 前端代码

在 HTML 中通过 JavaScript 调用 REST API:

import request from '../../utils/request';
export const getSearchProduct = query => {
    return request({
        url: 'http://localhost:5053/search/',
        method: 'get',
        params: query
    });
};

6. Controller 控制器

在 SearchController 中实现搜索功能：

package com.jq.controller;
import com.jq.result.ResultObj;
import com.jq.search.ProductSearch;
import com.jq.service.SearchProductService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.CrossOrigin;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
@RestController
@CrossOrigin
@RequestMapping("search")
public class SearchController {
    @Autowired
    private SearchProductService searchProductService;
    @GetMapping
    public ResultObj getSearchProduct(ProductSearch productSearch) {
        ProductSearch search = searchProductService.searchProduct(productSearch);
        return ResultObj.success(search);
    }
}

通过以上配置和代码，实现了数据库数据同步到 Elasticsearch，以及基于 Spring Boot 的搜索功能，支持分页和关键词搜索。