CODEWITHSUNDEEP
javacouchbasesql++
Sanjana
Sanjana

Reputation: 41

writing a basic n1ql query in java

I have just started learning Couchbase. I am trying to write a basic query using java sdk but I am not able to understand how to write it. Below is the query:

 SELECT * 
        FROM users_with_orders usr 
                JOIN orders_with_users orders 
                    ON KEYS ARRAY s.order_id FOR s IN usr.shipped_order_history END

This is for joining without array:

LetPath path = select("*,META(usr).id as _ID,META(usr).cas as _CAS).from(bucketName +" usr").join(bucketname +" orders").onKeys("usr.order_id)

How should I proceed with the above query for on keys array?

Thanks!!!!

Upvotes: 4

Views: 3436

Answers (1)

Matt Ingenthron
Matt Ingenthron

Reputation: 1894

As described in the docs on Querying from the SDK, you can use either a simple string with the Java SDK or use the DSL. For example:

    // query with a simple string
    System.out.println("Simple string query:");
    N1qlQuery airlineQuery = N1qlQuery.simple("SELECT `travel-sample`.* FROM `travel-sample` WHERE name=\"United Airlines\" AND type=\"airline\"");
    N1qlQueryResult queryResult = bucket.query(airlineQuery);

    for (N1qlQueryRow result: queryResult) {
        System.out.println(result.value());
    }

    //query with a parameter using the DSL
    System.out.println("Parameterized query using the DSL:");
    Statement statement = select(path(i("travel-sample"), "*")).from(i("travel-sample")).where(x("name").eq(x("$airline_param")).and(x("type").eq(s("airline"))));
    JsonObject placeholderValues = JsonObject.create().put("airline_param", "United Airlines");
    N1qlQuery airlineQueryParameterized = N1qlQuery.parameterized(statement, placeholderValues);
    N1qlQueryResult queryResultParameterized = bucket.query(airlineQueryParameterized);

    for (N1qlQueryRow row : queryResultParameterized) {
        System.out.println(row);
    }

(I posted a full gist of this example for the imports, etc.)

See the docs for more info, but you may want to use the DSL to allow IDE code completion and Java compile time checking. When developing an interactive web application, you'll probably also want to use parameterized statements (for security) and may even want prepared statements (for performance).

Upvotes: 6

Related Questions