Couchbase参数化的N1QL查询IN语句 [英] Couchbase parameterized N1QL query IN statement

问题描述

使用 com.couchbase.client，java-client 版本 2.2.7 ，我一直无法使n1ql查询正常工作，该查询使用带有多个项目的IN语句，请参见我的示例查询和下面的Java代码

Using com.couchbase.client, java-client version 2.2.7 I have been unable to get a n1ql query working that uses an IN statement with multiple items see my example query and java code below

public int getCountForDuration(Long startTime, Long endTime, String ids){
    JsonObject placeHolders = JsonObject.create().put("ids", ids).put("startTime", startTime).put("endTime", endTime);
    N1qlQuery query = N1qlQuery.parameterized(COUNT_STATEMENT, placeHolders)            
    N1qlQueryResult result = bucket.query(query);
    ...
}

public static final String COUNT_STATEMENT = "select count(*) as count " +
            "from bucketName " +
            "where docType = 'docId' " +
            "and (id IN [$ids]) " + <----- OFFENDING LINE
            "and publishTimestamp between $startTime and $endTime";

我尝试使用(')，()和(`)等设置 ids ，例如:

I've tried setting ids using ('), ("), and (`) such as:

ids = "'123', '456'";
ids = "\"123\" , \"456\";
ids = "`123`,`456`"; 

当有多个ID时，这些都不起作用.但是，如果只有一个，例如 ids ='123'" ，它可以正常工作.如果我在终端上使用CBQ进行查询，我的查询也会起作用.

None of these are working when there are multiple ids however if there is only one such as ids = "'123'" it works fine. Also my query works if I use it using CBQ on the terminal.

我的问题是我如何创建一个参数化的N1QL查询，可以在一个IN语句中包含多个项目?

My question is this how do I crate a parameterized N1QL query which can take multiple items in an IN statement?

推荐答案

删除语句中 $ ids 周围的括号，然后将实际ID放在 placeholders 中 JsonArray 对象应该可以工作:

Removing the brackets around the $ids in the statement and putting the actual ids into placeholders as a JsonArray object should work:

JsonObject placeHolders = JsonObject.create()
    .put("ids", JsonArray.from("id1", "id2", "id3"))
    .put("startTime", startTime)
    .put("endTime", endTime);

这篇关于Couchbase参数化的N1QL查询IN语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！