Elasticsearch 类比 mysql 实现 in and like or（Spring-Data-Elasticsearch）

一、前提

1.springboot 版本为2.2.2.RELEASE
2.spring-boot-starter-data-elasticsearch 版本 跟随springboot版本
3.elasticsearch 版本为6.4.2

需要说明的一点 ：拿mysql 类比elasticsearch 类比有点不妥，但是鉴于大部分人都是从传统关系型数据如 mysql ，慢慢才接触到elasticsearch 所以拿两者进行对比，比较好理解和快速上手！

本项目的完整源码地址：
https://github.com/Dr-Water/springdata-es-action

二 、数据准备

1. 首先准备安装好 es 和 准备实验数据 ，可以参考如下链接
   springboot集成spring-data-elasticsearch 完成对es的操作
   ElasticSearch实战 之 es的安装和使用

实验数据如下：

二、实验

在kibana中修改几条es中的数据

PUT /myes/myuser/1001
{
    "id": 1001,
    "firstName": "张",
    "lastName": "三峰",
    "age": 1001,
    "about": "中华人民共和国"
  }



PUT /myes/myuser/1002
{
    "id": 1002,
    "firstName": "张",
    "lastName": "无极",
    "age": 1002,
    "about": "中华人民共和国公民"
  }

PUT /myes/myuser/1003
{
    "id": 1003,
    "firstName": "王",
    "lastName": "科",
    "age": 1003,
    "about": "中华人民共和国"
  }

修改完记得刷新一下es ，有时候es 不会立即生效

2.1 and

select * from user where about='中华人民共和国' and firstName=‘张’

  @GetMapping("/and")
    public Page<User> query( Integer pageNum, Integer pageSize) {
        if(pageNum == null || pageNum <= 0){
            pageNum = 1;
        }
        if(pageSize == null || pageSize <= 0){
            pageSize = 5;
        }
        SearchQuery searchQuery = new NativeSearchQueryBuilder()
                    .withQuery(QueryBuilders.boolQuery()
                                        .must(QueryBuilders.termQuery("about","中华人民共和国"))
                                        .must(QueryBuilders.termQuery("firstName","张")))
                    .withPageable(PageRequest.of(pageNum - 1, pageSize))
                    .build();
        System.out.println(searchQuery.getQuery());
        Page<User> page = estemplate.queryForPage(searchQuery, User.class);
        return  page;
    }

2.2 or

select * from user where about='中华人民共和国' and firstName=‘王’

@GetMapping("/or")
    public Page<User> query2( Integer pageNum, Integer pageSize) {
        if(pageNum == null || pageNum <= 0){
            pageNum = 1;
        }
        if(pageSize == null || pageSize <= 0){
            pageSize = 5;
        }
        SearchQuery searchQuery = new NativeSearchQueryBuilder()
                .withQuery(QueryBuilders.boolQuery()
                        .should(QueryBuilders.termQuery("about","中华人民共和国"))
                        .should(QueryBuilders.termQuery("firstName","王")))
                .withPageable(PageRequest.of(pageNum - 1, pageSize))
                .build();
        System.out.println(searchQuery.getQuery());
        Page<User> page = estemplate.queryForPage(searchQuery, User.class);
        return  page;
    }

2.3 and or 连用

select * from user where  firstName='张' and( firstName='王’ or about='中华人民共和国公民')

@GetMapping("/andOr")
    public Page<User> query3( Integer pageNum, Integer pageSize) {
        if(pageNum == null || pageNum <= 0){
            pageNum = 1;
        }
        if(pageSize == null || pageSize <= 0){
            pageSize = 5;
        }
        SearchQuery searchQuery = new NativeSearchQueryBuilder()
                .withQuery(QueryBuilders.boolQuery()
                                .must(QueryBuilders.termQuery("firstName","张"))
                                .must(QueryBuilders.boolQuery()
                                        .should(QueryBuilders.termQuery("firstName","王"))
                                        .should(QueryBuilders.termQuery("about","中华人民共和国公民")))
                )
                .withPageable(PageRequest.of(pageNum - 1, pageSize))
                .build();
        System.out.println(searchQuery.getQuery());
        Page<User> page = estemplate.queryForPage(searchQuery, User.class);
        return  page;
    }

2.4 like

select * from user where about like '%中华%'

@GetMapping("/like")
    public Page<User> query4( Integer pageNum, Integer pageSize) {
        if(pageNum == null || pageNum <= 0){
            pageNum = 1;
        }
        if(pageSize == null || pageSize <= 0){
            pageSize = 5;
        }
        SearchQuery searchQuery = new NativeSearchQueryBuilder()
                .withQuery(QueryBuilders.wildcardQuery("about","*中华*"))
                .withPageable(PageRequest.of(pageNum - 1, pageSize))
                .build();
        System.out.println(searchQuery.getQuery());
        Page<User> page = estemplate.queryForPage(searchQuery, User.class);
        return  page;
    }

2.5 in

select * from user where about in ('中华人民共和国','中华人民共和国公民')

@GetMapping("/in")
    public Page<User> query5( Integer pageNum, Integer pageSize) {
        if(pageNum == null || pageNum <= 0){
            pageNum = 1;
        }
        if(pageSize == null || pageSize <= 0){
            pageSize = 5;
        }
        SearchQuery searchQuery = new NativeSearchQueryBuilder()
                .withQuery(QueryBuilders.boolQuery()
                        .should(QueryBuilders.termQuery("about","中华人民共和国"))
                        .should(QueryBuilders.termQuery("about","中华人民共和国公民")))
                .withPageable(PageRequest.of(pageNum - 1, pageSize))
                .build();
        System.out.println(searchQuery.getQuery());
        Page<User> page = estemplate.queryForPage(searchQuery, User.class);
        return  page;
    }