springboot整合elasticsearch实现MySQL模糊查询

es关联mysql实现模糊查询的数据流：

 

 

 1、使用logstash同步mysql数据到es

logstash简介及基本操作：https://blog.csdn.net/yurun_house/article/details/109025588

win10安装logstash：https://www.csdn.net/tags/MtjaYg3sMzc0MjEtYmxvZwO0O0OO0O0O.html

logstash同步mysql数据至elastic：https://blog.csdn.net/shang_xs/article/details/123820059

logstash增量同步问题解决：https://blog.csdn.net/weixin_34326179/article/details/88608713

logstash.conf文件配置：

output {
  elasticsearch {
  #ES的ip地址和端口
  hosts => "http://localhost:9200"
  #hosts => ["localhost:9200","localhost:9202","localhost:9203"]
  #ES索引库名称
  index => "file_info"
  document_id => "%{id}"
  document_type => "doc"
  }
  stdout {
 #日志输出
  codec => json_lines
  }
}

input {
  stdin {
  }
  jdbc {
  jdbc_connection_string => "jdbc:mysql://localhost:3306/onepdf?serverTimezone=GMT%2B8"
  # the user we wish to excute our statement as
  jdbc_user => "root"
  jdbc_password => "12345"
  # the path to our downloaded jdbc driver
  jdbc_driver_library => "D:/maven_local_repository/mysql/mysql-connector-java/8.0.30/mysql-connector-java-8.0.30.jar"
  # the name of the driver class for mysql
  jdbc_driver_class => "com.mysql.cj.jdbc.Driver"
  jdbc_paging_enabled => "true"
  jdbc_page_size => "50000"
  #要执行的sql文件
  statement => "SELECT id,file_name filename FROM `file_info`"
  #定时配置
  schedule => "* * * * *"
  record_last_run => true
  #关闭自动小写
  lowercase_column_names => false
  last_run_metadata_path => "D:/software/logstash-7.17.6/config/logstash_metadata"
  }
}

使用elasticsearch-head可视化工具查看es，如下图：

 

 

 

2、使用代码进行模糊查询

核心代码：

@Service("BookServiceImpl")
public class BookServiceImpl {

    //@Autowired
    //private BookRepository bookRepository;
    @Autowired
    public RestHighLevelClient client;

    public void findById(String id) {
        //
        //return bookRepository.findById(id);
    }

    /**
     * 使用CrudRepository中的方法save到es
     * @param book
     * @return
     */
    public void save(Book book) {
        //return bookRepository.save(book);
    }


    public List<FileInfo> search(String keyword) throws IOException {
        SearchRequest searchRequest = new SearchRequest();
        searchRequest.indices("file_info");
        searchRequest.types("doc");
        //设置搜索条件
        SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder();
        //将多种搜索条件组合到一起，复合查询
        BoolQueryBuilder boolQueryBuilder = QueryBuilders.boolQuery();
        //如果keyword为null则代表我们要进行全文的检索
        //boolQueryBuilder must 必须满足 mustNot 必须不满足
        if (keyword.equals("null")){
            MatchAllQueryBuilder matchAllQueryBuilder = QueryBuilders.matchAllQuery();
            boolQueryBuilder.must(matchAllQueryBuilder);

        }else{
            MultiMatchQueryBuilder multiMatchQueryBuilder = QueryBuilders.multiMatchQuery(keyword, "filename").field("filename", 10);
            boolQueryBuilder.must(multiMatchQueryBuilder);
        }
        //将boolQuery设置到SearchSourceBuild中
        searchSourceBuilder.query(boolQueryBuilder);
        //设置分页
        //searchSourceBuilder.from((page-1)*size);
        //searchSourceBuilder.size(size);
        //设置高亮
        HighlightBuilder highlightBuilder = new HighlightBuilder();
        highlightBuilder.field("courseName");
        highlightBuilder.field("courseDescription");
        highlightBuilder.preTags("<font style='color:red'>");
        highlightBuilder.postTags("</font>");
        //将高亮设置到搜索请求对象中
        searchSourceBuilder.highlighter(highlightBuilder);
        //将搜索条件设置到reque中
        //searchRequest.source(searchSourceBuilder);
        SearchResponse searchResp = client.search(searchRequest, RequestOptions.DEFAULT);
        //继续解析出数据内容
        // 搜索出的结果
        SearchHits hits = searchResp.getHits();
        List list = new ArrayList();
        for (SearchHit hi:hits) {
            //解析出数据转为map
            Map<String, Object> sourceAsMap = hi.getSourceAsMap();
            //转为对象
            String s = JSON.toJSONString(sourceAsMap);
            FileInfo userTest = JSONObject.parseObject(s, FileInfo.class);
            //放置在List中
            list.add(userTest);
        }
        return list;

    }

gitee地址：https://gitee.com/caesarthegreat/elastic-search-demo.git