-- 不分页
select * from dbo.goods ;


-- offset /fetch next（2012版本及以上才有） 分页
select * from dbo.goods
order by id  
offset 0 rows
fetch next 100 rows only ;


-- 低版本
	SELECT
		* 
	FROM
		( SELECT top 100 * FROM ( SELECT top 100 * FROM dbo.goods ORDER BY id ASC ) -- 其中里面这层，必须指定按照升序排序，省略的话，查询出的结果是错误的。
		AS temp_sum_goods ORDER BY id DESC ) temp_order 
	ORDER BY
	id ASC;

select * from mysqldemo.goods ;

select * from mysqldemo.goods limit 100 ;

//goods简单分页查询  from+size 浅分页


get http://127.0.0.1:9200/goods/_search

GET /goods/_search HTTP/1.1
Host: 127.0.0.1:9200
Content-Type: application/json
Content-Length: 33

{
  "from": 1,
  "size": 100
}

 SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder() ;
BoolQueryBuilder queryBuilder = new BoolQueryBuilder();
searchSourceBuilder.query(queryBuilder).from(1).size(100);
SearchRequest searchRequest = new SearchRequest() ;
searchRequest.indices("goods");
searchRequest.source(searchSourceBuilder);
SearchResponse searchResponse = restHighLevelClient.search(searchRequest, RequestOptions.DEFAULT);
SearchHits hits = searchResponse.getInternalResponse().hits();
SearchHit[] searchHits = hits.getHits();
for (SearchHit hit : searchHits) {
    Map<String, Object> sourceAsMap = hit.getSourceAsMap();
    System.out.println(JSONUtil.toJsonStr(sourceAsMap));
}
restHighLevelClient.close();

 PageRequest pageRequest = PageRequest.of(1,100) ;
Page<Goods> goodsList = goodsRepository.findAll(pageRequest);
for (Goods goods:goodsList){
    System.out.println(goods);
}

  /**
     * 精确查询（termQuery）
     */
    @Test
    public void termQuery() {
        //查询条件(词条查询：对应ES query里的term)
        TermQueryBuilder termQueryBuilder = QueryBuilders.termQuery("categoryName", "手机");
        //创建查询条件构建器SearchSourceBuilder(对应ES外面的大括号)
        NativeSearchQuery nativeSearchQuery = new NativeSearchQueryBuilder().withQuery(termQueryBuilder).build();
        org.springframework.data.elasticsearch.core.SearchHits<Goods> searchHits = elasticsearchRestTemplate.search(nativeSearchQuery, Goods.class);
        Iterator<org.springframework.data.elasticsearch.core.SearchHit<Goods>> iterator = searchHits.iterator();
        while (iterator.hasNext()) {
            org.springframework.data.elasticsearch.core.SearchHit<Goods> searchHit = iterator.next();
            System.out.println(searchHit.getContent());
        }
    }

    /**
     * terms:多个查询内容在一个字段中进行查询
     */
    @Test
    public void termsQuery(){
        //查询条件(词条查询：对应ES query里的term)
        TermsQueryBuilder termsQueryBuilder = QueryBuilders.termsQuery("categoryName", "手机","平板电视");
        //创建查询条件构建器SearchSourceBuilder(对应ES外面的大括号)
        NativeSearchQuery nativeSearchQuery = new NativeSearchQueryBuilder().withQuery(termsQueryBuilder).build();
        org.springframework.data.elasticsearch.core.SearchHits<Goods> searchHits = elasticsearchRestTemplate.search(nativeSearchQuery, Goods.class);
        Iterator<org.springframework.data.elasticsearch.core.SearchHit<Goods>> iterator = searchHits.iterator();
        while (iterator.hasNext()) {
            org.springframework.data.elasticsearch.core.SearchHit<Goods> searchHit = iterator.next();
            System.out.println(searchHit.getContent());
        }
    }

 @Test
    public void matchQuery() {
        //查询条件(词条查询：对应ES query里的match)
        MatchQueryBuilder matchQueryBuilder = QueryBuilders.matchQuery("title", "Apple IPhone 白色").analyzer("ik_smart").operator(Operator.AND);
        //创建查询条件构建器SearchSourceBuilder(对应ES外面的大括号)
        NativeSearchQuery nativeSearchQuery = new NativeSearchQueryBuilder().withQuery(matchQueryBuilder).build();
        //查询,获取查询结果
        org.springframework.data.elasticsearch.core.SearchHits<Goods> searchHits = elasticsearchRestTemplate.search(nativeSearchQuery, Goods.class);
        //获取总记录数
        long totalHits = searchHits.getTotalHits();
        System.out.println("totalHits = " + totalHits);
        Iterator<org.springframework.data.elasticsearch.core.SearchHit<Goods>> iterator = searchHits.iterator();
        while (iterator.hasNext()) {
            org.springframework.data.elasticsearch.core.SearchHit<Goods> searchHit = iterator.next();
            System.out.println(searchHit.getContent());
        }
    }

    /**
     * match_all：查询全部。
     */
    @Test
    public void matchAllQuery(){
        //查询条件(词条查询：对应ES query里的match)
        MatchAllQueryBuilder matchAllQueryBuilder = QueryBuilders.matchAllQuery();
        //创建查询条件构建器SearchSourceBuilder(对应ES外面的大括号)
        NativeSearchQuery nativeSearchQuery = new NativeSearchQueryBuilder().withQuery(matchAllQueryBuilder).build();
        //查询,获取查询结果
        org.springframework.data.elasticsearch.core.SearchHits<Goods> searchHits = elasticsearchRestTemplate.search(nativeSearchQuery, Goods.class);
        //获取总记录数
        long totalHits = searchHits.getTotalHits();
        System.out.println("totalHits = " + totalHits);
        Iterator<org.springframework.data.elasticsearch.core.SearchHit<Goods>> iterator = searchHits.iterator();
        while (iterator.hasNext()) {
            org.springframework.data.elasticsearch.core.SearchHit<Goods> searchHit = iterator.next();
            System.out.println(searchHit.getContent());
        }
    }

    /**
     * match_phrase：短语查询，在match的基础上进一步查询词组，可以指定slop分词间隔。
     */
    @Test
    public void matchPhraseQuery(){
        //查询条件(词条查询：对应ES query里的match_all)
        MatchPhraseQueryBuilder matchPhraseQueryBuilder = QueryBuilders.matchPhraseQuery("title","华为") ;
        //创建查询条件构建器SearchSourceBuilder(对应ES外面的大括号)
        NativeSearchQuery  nativeSearchQuery = new NativeSearchQueryBuilder().withQuery(matchPhraseQueryBuilder).build();
        //查询,获取查询结果
        org.springframework.data.elasticsearch.core.SearchHits<Goods> searchHits = elasticsearchRestTemplate.search(nativeSearchQuery, Goods.class);
        //获取总记录数
        long totalHits = searchHits.getTotalHits();
        System.out.println("totalHits = " + totalHits);
        Iterator<org.springframework.data.elasticsearch.core.SearchHit<Goods>> iterator = searchHits.iterator();
        while (iterator.hasNext()) {
            org.springframework.data.elasticsearch.core.SearchHit<Goods> searchHit = iterator.next();
            System.out.println(searchHit.getContent());
        }
    }

    /**
     * multi_match：多字段查询，使用相当的灵活，可以完成match_phrase和match_phrase_prefix的工作。
     */
    @Test
    public void multiMatchQuery(){
        //查询条件(词条查询：对应ES query里的multi_match)
        MultiMatchQueryBuilder multiMatchQueryBuilder = QueryBuilders.multiMatchQuery("华为","title","categoryName").analyzer("ik_smart") ;
//        MultiMatchQueryBuilder multiMatchQueryBuilder = QueryBuilders.multiMatchQuery("华为和Apple","title","categoryName").analyzer("ik_smart") ;
        //创建查询条件构建器SearchSourceBuilder(对应ES外面的大括号)
        NativeSearchQuery nativeSearchQuery = new NativeSearchQueryBuilder().withQuery(multiMatchQueryBuilder).build();
        //查询,获取查询结果
        org.springframework.data.elasticsearch.core.SearchHits<Goods> searchHits = elasticsearchRestTemplate.search(nativeSearchQuery, Goods.class);
        //获取总记录数
        long totalHits = searchHits.getTotalHits();
        System.out.println("totalHits = " + totalHits);
        Iterator<org.springframework.data.elasticsearch.core.SearchHit<Goods>> iterator = searchHits.iterator();
        while (iterator.hasNext()) {
            org.springframework.data.elasticsearch.core.SearchHit<Goods> searchHit = iterator.next();
            System.out.println(searchHit.getContent());
        }
    }