实战 | 使用Spring Boot + Elasticsearch + Logstash 实现图书查询检索服务

input {
    stdin {
    }
    jdbc {
      # mysql数据库连接
      jdbc_connection_string => "jdbc:mysql://localhost:3306/book_test?characterEncoding=utf-8&useSSL=false&serverTimezone=UTC"
      # mysqly用户名和密码
      jdbc_user => "root"
      jdbc_password => "root"
      # 驱动配置
      jdbc_driver_library => "C:\Users\Administrator\Desktop\logstash-7.5.1\mysql\mysql-connector-java-8.0.20.jar"
      # 驱动类名
      jdbc_driver_class => "com.mysql.cj.jdbc.Driver"
      #jdbc_paging_enabled => "true"
      #jdbc_page_size => "50000"
    jdbc_default_timezone => "Asia/Shanghai"
      # 执行指定的sql文件
      statement_filepath => "C:\Users\Administrator\Desktop\logstash-7.5.1\mysql\sql\bookquery.sql"
    use_column_value => true
    # 是否将字段名转换为小写，默认true（如果有数据序列化、反序列化需求，建议改为false）；
    lowercase_column_names => false
    # 需要记录的字段，用于增量同步，需是数据库字段
    tracking_column => updatetime
    # Value can be any of: numeric,timestamp，Default value is "numeric"
    tracking_column_type => timestamp
    # record_last_run上次数据存放位置；
    record_last_run => true
    #上一个sql_last_value值的存放文件路径, 必须要在文件中指定字段的初始值
    last_run_metadata_path => "C:\Users\Administrator\Desktop\logstash-7.5.1\mysql\sql\logstash_default_last_time.log"
    # 是否清除last_run_metadata_path的记录，需要增量同步时此字段必须为false；
    clean_run => false
      # 设置监听 各字段含义 分 时 天 月  年 ，默认全部为*代表含义：每分钟都更新
      schedule => "* * * * *"
      # 索引类型
      type => "id"
    }
}
output {
    elasticsearch {
        #es服务器
        hosts => ["10.2.1.231:9200"]
        #ES索引名称
        index => "book"
        #自增ID
        document_id => "%{id}"
    }
    stdout {
        codec => json_lines
    }
}

select * from book where updatetime >= :sql_last_value
order by updatetime desc

logstash.bat -f C:\Users\Administrator\Desktop\logstash-7.5.1\mysql\mysql.conf

@Document( indexName = "book" , replicas = 0)
public class Book {
    @Id
    private Long id;
    @Field(analyzer = "ik_max_word",type = FieldType.Text)
    private String bookName;
    @Field(analyzer = "ik_max_word",type = FieldType.Text)
    private String author;
    private float price;
    private int page;
    @Field(type = FieldType.Date,format = DateFormat.custom,pattern = "yyyy-MM-dd'T'HH:mm:ss.SSS'Z'")
    private Date createTime;
    @Field(type = FieldType.Date,format = DateFormat.custom,pattern = "yyyy-MM-dd'T'HH:mm:ss.SSS'Z'")
    private Date updateTime;
    @Field(analyzer = "ik_max_word",type = FieldType.Text)
    private String category;
    public Long getId() {
        return id;
    }
    public void setId(Long id) {
        this.id = id;
    }
    public String getBookName() {
        return bookName;
    }
    public void setBookName(String bookName) {
        this.bookName = bookName;
    }
    public String getAuthor() {
        return author;
    }
    public void setAuthor(String author) {
        this.author = author;
    }
    public float getPrice() {
        return price;
    }
    public void setPrice(float price) {
        this.price = price;
    }
    public int getPage() {
        return page;
    }
    public void setPage(int page) {
        this.page = page;
    }
    public String getCategory() {
        return category;
    }
    public void setCategory(String category) {
        this.category = category;
    }
    public Book(){
    }
    public Date getCreateTime() {
        return createTime;
    }
    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }
    public Date getUpdateTime() {
        return updateTime;
    }
    public void setUpdateTime(Date updateTime) {
        this.updateTime = updateTime;
    }
}

public class BookQuery {
    public String category;
    public String bookName;
    public String author;
    public int priceMin;
    public int priceMax;
    public int pageMin;
    public int pageMax;
    public String sort;
    public String sortType;
    public int page;
    public int limit;
}

@RestController
public class ElasticSearchController {
    @Autowired
    private ElasticsearchRestTemplate elasticsearchRestTemplate;
    /**
     * 查询信息
     * @param
     * @return
     */
    @PostMapping(value = "/book/query")
    public JSONResult query(@RequestBody BookQuery bookQuery){
        Query query= getQueryBuilder(bookQuery);
        SearchHits<Book> searchHits = elasticsearchRestTemplate.search(query, Book.class);
        List<SearchHit<Book>> result = searchHits.getSearchHits();
        return JSONResult.ok(result);
    }
    public Query getQueryBuilder(BookQuery query) {
               BoolQueryBuilder builder = boolQuery();
        // 匹配器 模糊查询部分，分析器使用ik (ik_max_word)
        List<QueryBuilder> must = builder.must();
        if (query.getBookName()!=null && !query.getBookName().isEmpty())
            must.add(wildcardQuery("bookName", "*" +query.getBookName()+ "*"));
        if (query.getCategory()!=null && !query.getCategory().isEmpty())
            must.add(wildcardQuery("category", "*" +query.getCategory()+ "*"));
        if (query.getAuthor()!=null && !query.getAuthor().isEmpty())
            must.add(wildcardQuery("author", "*" +query.getAuthor()+ "*"));
        // 筛选器 精确查询部分
        List<QueryBuilder> filter = builder.filter();
        // 范围查询
        if (query.getPriceMin()>0 && query.getPriceMax()>0) {
            RangeQueryBuilder price = rangeQuery("price").gte(query.getPriceMin()).lte(query.getPriceMax());
            filter.add(price);
        }
        // 范围查询
        if (query.getPageMin()>0 && query.getPageMax()>0) {
            RangeQueryBuilder page = rangeQuery("page").gte(query.getPageMin()).lte(query.getPageMax());
            filter.add(page);
        }
        // 分页
        PageRequest pageable = PageRequest.of(query.getPage() - 1, query.getLimit());
        // 排序
        SortBuilder sort = SortBuilders.fieldSort("price").order(SortOrder.DESC);
        //设置高亮效果
        String preTag = "<font color='#dd4b39'>";//google的色值
        String postTag = "</font>";
        HighlightBuilder.Field highlightFields = new HighlightBuilder.Field("category").preTags(preTag).postTags(postTag);
        Query searchQuery = new NativeSearchQueryBuilder()
                .withQuery(builder)
                .withHighlightFields(highlightFields)
                .withPageable(pageable)
                .withSort(sort)
                .build();
        return searchQuery;
    }
}