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

小鱼儿 2023-07-10 05:26 16阅读 0赞

# 一、前提 #

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的操作][springboot_spring-data-elasticsearch _es]  
    [ElasticSearch实战 之 es的安装和使用][ElasticSearch_ _ es]
2.

实验数据如下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dldGVyX2Ryb3A_size_16_color_FFFFFF_t_70]

# 二、实验 #

在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 不会立即生效  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dldGVyX2Ryb3A_size_16_color_FFFFFF_t_70 1]

## 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;
        }

[springboot_spring-data-elasticsearch _es]: https://blog.csdn.net/weter_drop/article/details/104023062
[ElasticSearch_ _ es]: https://blog.csdn.net/weter_drop/article/details/103990959
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dldGVyX2Ryb3A_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200229211349586.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dldGVyX2Ryb3A=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dldGVyX2Ryb3A_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200229220726147.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dldGVyX2Ryb3A=,size_16,color_FFFFFF,t_70