spring對接InfluxDB（三）--資料擷取之多條語句

Influx提供多條語句的同時查詢（Multiple queries）：

curl -G 'http://localhost:8086/query?pretty=true' --data-urlencode "db=mydb" --data-urlencode "q=SELECT \"value\" FROM \"cpu_load_short\" WHERE \"region\"='us-west';SELECT count(\"value\") FROM \"cpu_load_short\" WHERE \"region\"='us-west'"

就是說我一次請求可以同時執行多條sql，官網明确說明隻要每個sql中間用分号（;）隔開就好。

擷取資訊的代碼：

public void getTest(){
        RestTemplate restTemplate=new RestTemplate();

        String url="http://localhost:8086/query?pretty=true&q=select * from pkk;select count(*) from pkk&db=mydb";

        String points=restTemplate.getForObject(url,String.class);

        System.out.println("points:"+points);
    }

傳回的資訊：

points:{
    "results": [
        {
            "statement_id": ,
            "series": [
                {
                    "name": "pkk",
                    "columns": [
                        "time",
                        "address",
                        "age",
                        "name"
                    ],
                    "values": [
                        [
                            "2017-07-28T01:32:03.378554614Z",
                            "shanghai",
                            ,
                            "pangkun1"
                        ],
                        [
                            "2017-07-28T01:32:03.378554614Z",
                            "shanghai",
                            ,
                            "pangkun3"
                        ],
                        [
                            "2017-07-28T01:32:03.378554614Z",
                            "shanghai",
                            ,
                            "pangkun2"
                        ]
                    ]
                }
            ]
        }
    ]
}

這裡可以看出上邊傳回的隻有第一條查詢語句的結果，第二條根本就沒有執行，或者執行後根本就沒傳回。

同樣的sql在InfluxDB的web端可以正常傳回資料，但是放到代碼中就是查詢不出結果。

是以我判斷問題應該是出在兩條sql的連接配接處–分号，仔細檢視curl中的url是說要encode的，現在看下web端執行成功的url:

通過抓包，或者直接在浏覽器中執行我代碼中的URL：

http://localhost:/query?pretty=true&q=select%20*%from%20pkk;select%20count(*)%20from%20pkk&db=mydb

仔細對比可以發現自己執行的url中的分号（;)并沒有沒加密處理，在網上搜尋可以看到其對應的編碼為%3B。

是以，現在的問題是sping的resttemplate沒有對分号進行處理。解決方案，重寫resttemplate的url處理部分。

重寫之後的代碼：

package org.springframework.web.client;

import org.springframework.http.HttpMethod;
import org.springframework.http.client.ClientHttpRequest;
import org.springframework.http.client.ClientHttpResponse;
import org.springframework.util.Assert;
import org.springframework.web.util.UriTemplateHandler;

import java.io.IOException;
import java.net.URI;
import java.net.URISyntaxException;

/**
 * Created by pangkunkun on 2017/7/21.
 */
public class DisplayRestTemplate extends RestTemplate{

    @Override
    public <T> T getForObject(String url, Class<T> responseType, Object... uriVariables) throws RestClientException {
        RequestCallback requestCallback = acceptHeaderRequestCallback(responseType);
        HttpMessageConverterExtractor<T> responseExtractor =
                new HttpMessageConverterExtractor<T>(responseType, getMessageConverters(), logger);
        return execute(url, HttpMethod.GET, requestCallback, responseExtractor, uriVariables);
    }

    // general execution

    @Override
    public <T> T execute(String url, HttpMethod method, RequestCallback requestCallback,
                         ResponseExtractor<T> responseExtractor, Object... uriVariables) throws RestClientException {

        URI expanded = getUriTemplateHandler().expand(url, uriVariables);

        try {
            //對處理完之後的url進行二次處理，;替換為%3B
            expanded=new URI(expanded.toString().replace(";","%3B"));
        }catch (Exception e){
            e.printStackTrace();
        }
        return doExecute(expanded, method, requestCallback, responseExtractor);
    }

    /**
     * Execute the given method on the provided URI.
     * <p>The {@link ClientHttpRequest} is processed using the {@link RequestCallback};
     * the response with the {@link ResponseExtractor}.
     * @param url the fully-expanded URL to connect to
     * @param method the HTTP method to execute (GET, POST, etc.)
     * @param requestCallback object that prepares the request (can be {@code null})
     * @param responseExtractor object that extracts the return value from the response (can be {@code null})
     * @return an arbitrary object, as returned by the {@link ResponseExtractor}
     */
    @Override
    protected <T> T doExecute(URI url, HttpMethod method, RequestCallback requestCallback,
                              ResponseExtractor<T> responseExtractor) throws RestClientException  {

        Assert.notNull(url, "'url' must not be null");
        Assert.notNull(method, "'method' must not be null");
        ClientHttpResponse response = null;
        try {

            ClientHttpRequest request = createRequest(url, method);
            if (requestCallback != null) {
                requestCallback.doWithRequest(request);
            }
            response = request.execute();
            handleResponse(url, method, response);
            if (responseExtractor != null) {
                return responseExtractor.extractData(response);
            }
            else {
                return null;
            }
        }
        catch (IOException ex) {
            String resource = url.toString();
            String query = url.getRawQuery();
            resource = (query != null ? resource.substring(, resource.indexOf(query) - ) : resource);
            throw new ResourceAccessException("I/O error on " + method.name() +
                    " request for \"" + resource + "\": " + ex.getMessage(), ex);
        }
        finally {
            if (response != null) {
                response.close();
            }
        }
    }
}

修改擷取的代碼：

public void getTest(){
        DisplayRestTemplaterestTemplate=new DisplayRestTemplate();

        String url="http://localhost:8086/query?pretty=true&q=select * from pkk;select count(*) from pkk&db=mydb";

        String points=restTemplate.getForObject(url,String.class);

        System.out.println("points:"+points);
    }

修改後的結果：

points:{
    "results": [
        {
            "statement_id": ,
            "series": [
                {
                    "name": "pkk",
                    "columns": [
                        "time",
                        "address",
                        "age",
                        "name"
                    ],
                    "values": [
                        [
                            "2017-07-28T01:32:03.378554614Z",
                            "shanghai",
                            ,
                            "pangkun1"
                        ],
                        [
                            "2017-07-28T01:32:03.378554614Z",
                            "shanghai",
                            ,
                            "pangkun3"
                        ],
                        [
                            "2017-07-28T01:32:03.378554614Z",
                            "shanghai",
                            ,
                            "pangkun2"
                        ]
                    ]
                }
            ]
        },
        {
            "statement_id": ,
            "series": [
                {
                    "name": "pkk",
                    "columns": [
                        "time",
                        "count_age"
                    ],
                    "values": [
                        [
                            "1970-01-01T00:00:00Z",
                            
                        ]
                    ]
                }
            ]
        }
    ]
}

現在可以看到結果是OK的，正常傳回了兩個查詢語句的資訊。

現在再來看看兩個表之間的一次查詢：

再建立另外一個measurement：

public void writePoints(){

        String url="http://localhost:8086/write?db=mydb";

        String pointValue="pangkunkun,name=pangkun4,address=shanghai age=24\n" +
                "pangkunkun,name=pangkun5,address=shanghai age=25\n" +
                "pangkunkun,name=pangkun6,address=shanghai age=26";

        RestTemplate restTemplate=new RestTemplate();

        restTemplate.postForObject(url,pointValue,Object.class);
    }

從兩個表中擷取資料

public void getTest(){
        DisplayRestTemplaterestTemplate=new DisplayRestTemplate();

        String url="http://localhost:8086/query?pretty=true&q=select * from pkk;select * from pangkunkun&db=mydb";

        String points=restTemplate.getForObject(url,String.class);

        System.out.println("points:"+points);
    }

傳回結果：

points:{
    "results": [
        {
            "statement_id": ,
            "series": [
                {
                    "name": "pkk",
                    "columns": [
                        "time",
                        "address",
                        "age",
                        "name"
                    ],
                    "values": [
                        [
                            "2017-07-28T01:32:03.378554614Z",
                            "shanghai",
                            ,
                            "pangkun1"
                        ],
                        [
                            "2017-07-28T01:32:03.378554614Z",
                            "shanghai",
                            ,
                            "pangkun3"
                        ],
                        [
                            "2017-07-28T01:32:03.378554614Z",
                            "shanghai",
                            ,
                            "pangkun2"
                        ]
                    ]
                }
            ]
        },
        {
            "statement_id": ,
            "series": [
                {
                    "name": "pangkunkun",
                    "columns": [
                        "time",
                        "address",
                        "age",
                        "name"
                    ],
                    "values": [
                        [
                            "2017-07-28T05:39:53.081302642Z",
                            "shanghai",
                            ,
                            "pangkun4"
                        ],
                        [
                            "2017-07-28T05:39:53.081302642Z",
                            "shanghai",
                            ,
                            "pangkun6"
                        ],
                        [
                            "2017-07-28T05:39:53.081302642Z",
                            "shanghai",
                            ,
                            "pangkun5"
                        ]
                    ]
                }
            ]
        }
    ]
}

如果是習慣hibernate或者JPA的朋友應該會知道，一般的表聯合查詢的時候很多都是放到一起：

public void getTest(){
        DisplayRestTemplaterestTemplate=new DisplayRestTemplate();

        String url="http://localhost:8086/query?pretty=true&q=select * from pkk,pangkunkun&db=mydb";

        String points=restTemplate.getForObject(url,String.class);

        System.out.println("points:"+points);
    }

傳回結果：

points:{
    "results": [
        {
            "statement_id": ,
            "series": [
                {
                    "name": "pangkunkun",
                    "columns": [
                        "time",
                        "address",
                        "age",
                        "name"
                    ],
                    "values": [
                        [
                            "2017-07-28T05:39:53.081302642Z",
                            "shanghai",
                            ,
                            "pangkun4"
                        ],
                        [
                            "2017-07-28T05:39:53.081302642Z",
                            "shanghai",
                            ,
                            "pangkun6"
                        ],
                        [
                            "2017-07-28T05:39:53.081302642Z",
                            "shanghai",
                            ,
                            "pangkun5"
                        ]
                    ]
                },
                {
                    "name": "pkk",
                    "columns": [
                        "time",
                        "address",
                        "age",
                        "name"
                    ],
                    "values": [
                        [
                            "2017-07-28T01:32:03.378554614Z",
                            "shanghai",
                            ,
                            "pangkun1"
                        ],
                        [
                            "2017-07-28T01:32:03.378554614Z",
                            "shanghai",
                            ,
                            "pangkun3"
                        ],
                        [
                            "2017-07-28T01:32:03.378554614Z",
                            "shanghai",
                            ,
                            "pangkun2"
                        ]
                    ]
                }
            ]
        }
    ]
}

這個傳回的資料是完整的，但是仔細對比會發現跟上邊的json結構不同。

第一種兩條語句用分号（;）隔開的傳回的是兩個或多個series（此處隻是兩條查詢，是以是兩個），而且statement_id是0和1兩個。

第二種兩張表之間用逗号隔開的，隻傳回一個series，所有的結果都在這個series中，而且statement_id的值隻有1.

這種情況很好了解，第一種用分号隔開的是兩條查詢語句，第二種用逗号隔開兩張表是一條語句。第二種不需要重寫RestTemplate。具體的兩種方式的用途也不太相同，各位可以思考下。

spring對接InfluxDB（三）--資料擷取之多條語句

繼續閱讀

聊一聊Swagger-UI的那些事情軟體簡介Swagger 原生Bootstrap-ui Bootstrap的UIKnife4j Bootstrap的更新版的UI

springboot使用 swagger-ui 2.10.5 有關版本更新帶來的問題

Spring Cloud整合Sleuth，當請求完成後，Zipkin沒有鍊路資訊

JAVA自學之路——馬士兵

Rabbitmq指令行手動建立隊列

整合 Spring 開發架構及服務端技術

SSM整合02---Spring-SpringMVC層一、添加web架構支援二、web.xml三、spring-mvc.xml

SSM架構（二）------------表現層的SpringMVC

Intellij idea基于Maven 的SSM 架構整合

微服務-性能壓測\緩存redis和分布式鎖redisson和SpringCache

Spring資料和Redis

SpringMVC的啟動過程源碼分析1 SpringMVC 配置XML的方式啟動2 SpringMVC純注解模式啟動

Java Spring 架構

java 8 Lamda 求 List 中 Bigdecimal類型的各種值求最大值求最小值求和求平均值

(SpringBoot)日志種類：log、monitor、access、out、gc、backup

SpringCloud微服務（四）之ribbon筆記ribbon是什麼？入門案例自定義均衡負載算法