Is this N1QL Query Correct?

Java SDK
1

I use N1QL Query, which select a number of document by pagination.
It seems to work properly.
But sometimes it starts to throw TimeoutException and after that all N1QL Query which is executed on that WAS never succeed.

My DAO Code is below.

public List getPartnerList(int count, int pageNumber) throws DAOException {

	List<Partner> list = new ArrayList<Partner>();
	Gson gson = new Gson();
	try {
        
		StringBuilder builder = new StringBuilder();
		builder.append("SELECT * FROM Cms ")
		builder.append("WHERE type='partner'");
		builder.append(" ORDER BY `regDate` DESC ");
		builder.append(" limit ").append(count).append(" offset ").append(count*(pageNumber-1));
		
		logger.debug("getPartnerList query. query:{}", builder.toString());
		

		N1qlQuery query = N1qlQuery.simple(builder.toString());
		
		cmsBucket
	    .async()
	    .query(query)
	    .flatMap(AsyncN1qlQueryResult::rows)
		.timeout(Constants.QUERY_TIMEOUT, TimeUnit.SECONDS)
	    .toBlocking()
	    .forEach(row -> {
			try {
				JsonObject obj = row.value();
				Object doc = obj.get("Cms");
				Partner partner = gson.fromJson(doc.toString(), Partner.class);
				list.add(partner);	
			} catch (Exception e) {
				logger.error("Can't get PartnerList. error:{}", e);
			}
	    });
	} catch(Exception e) {
		logger.error("Can't get PartnerList. error:{}", e);
		throw new DAOException("Can't get PartnerList. error:{}"+e.getMessage());
	}
	
    return list;
}

On java SDK tutorial pages, I couldn’t find N1QL Async Select Query examples.
Is there any wrong with the above code?
Please help me out.

2

As written, I believe the problem is you’re chaining in the .flatMap() rather early and thus it’s not going to be able to use the streaming parser and be fully async. Is it timing out after 75s? There is an example that uses the streaming parser and processes rows as they come in here in YCSB.

I know that’s a rather complex example, but hopefully this points you the correct direction. We should add a good example of this to the dev guide. I’ll get that added to our list of to-dos.

3

@andypark the code doesn’t look too bad - which client version are you on? How many rows are you fetching?

4

@ingenthr @daschl , Thanks for your help.
But @ingenthr, I think your example looks similar with my code.

@daschl, my client version is 2.3.3 and I use Couchbase 4.0 CE.
Actually, I’ve tested from 2.2.8 to above already.

I fetched 2000 documents per once and iterate over almost 50 times.

5

@ingenthr @daschl Finally, I found the solution.
Please check my code below.

public List getPartnerList(int count, int pageNumber) throws DAOException {

List<Partner> list = new ArrayList<Partner>();
Gson gson = new Gson();
try {
    
	StringBuilder builder = new StringBuilder();
	builder.append("SELECT * FROM Cms ")
	builder.append("WHERE type='partner'");
	builder.append(" ORDER BY `regDate` DESC ");
	builder.append(" limit ").append(count).append(" offset ").append(count*(pageNumber-1));
	
	logger.debug("getPartnerList query. query:{}", builder.toString());
	

	N1qlQuery query = N1qlQuery.simple(builder.toString());
	
	cmsBucket
       .async()
       .query(query)
       .doOnNext(result -> {
		if(!result.parseSuccess()) {
		    throw new RuntimeException("Error while parsing N1QL Result. Query: " + query
									+ ", Errors: " + result.errors());
		}
	})
	.flatMap(AsyncN1qlQueryResult::rows)
	.timeout(Constants.QUERY_TIMEOUT, TimeUnit.SECONDS)
	.map(row -> {
		JsonObject obj = row.value();
		Object doc = obj.get("Cms");
		Partner partner = gson.fromJson(doc.toString(), Partner.class);
		return partner;
	})
	.toBlocking()
	.forEach(row -> {
		list.add(row);
	});

} catch(Exception e) {
	logger.error("Can't get PartnerList. error:{}", e);
	throw new DAOException("Can't get PartnerList. error:{}"+e.getMessage());
}

return list;

}

1 Like
6

Looks good to me, thanks for sharing! :slight_smile: