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是什么？入门案例自定义均衡负载算法