query_builder

Query builder

The query builder is a utility to generate CQL queries programmatically. For example, it could be used to:

• given a set of optional search parameters, build a search query dynamically depending on which parameters are provided;
• given a Java class, generate the CRUD queries that map instances of that class to a Cassandra table.

To use it in your application, add the following dependency:

<dependency>
  <groupId>com.datastax.oss</groupId>
  <artifactId>java-driver-query-builder</artifactId>
  <version>${driver.version}</version>
</dependency>

Here is our canonical example rewritten with the query builder:

import static com.datastax.oss.driver.api.querybuilder.QueryBuilder.*;

try (CqlSession session = CqlSession.builder().build()) {
  
  Select query = selectFrom("system", "local").column("release_version"); // SELECT release_version FROM system.local
  SimpleStatement statement = query.build();
  
  ResultSet rs = session.execute(statement);
  Row row = rs.one();
  System.out.println(row.getString("release_version"));
}

General concepts

Fluent API

All the starting methods are centralized in the QueryBuilder and SchemaBuilder classes. To get started, add one of the following imports:

// For DML queries, such as SELECT 
import static com.datastax.oss.driver.api.querybuilder.QueryBuilder.*;

// For DDL queries, such as CREATE TABLE
import static com.datastax.oss.driver.api.querybuilder.SchemaBuilder.*;

Choose the method matching your desired statement, for example selectFrom. Then use your IDE's completion and the javadocs to add query parts:

Select select =
    selectFrom("ks", "user")
          .column("first_name")
          .column("last_name")
          .whereColumn("id").isEqualTo(bindMarker());
// SELECT first_name,last_name FROM ks.user WHERE id=?

When your query is complete, you can either extract a raw query string, or turn it into a simple statement (or its builder):

String cql = select.asCql();
SimpleStatement statement = select.build();
SimpleStatementBuilder builder = select.builder();

Datastax Enterprise

The driver provides two additional entry points for DSE-specific queries: DseQueryBuilder and DseSchemaBuilder. They extend their respective non-DSE counterparts, so anything that is available on the default query builder can also be done with the DSE query builder.

We recommend that you use those classes if you are targeting Datastax Enterprise; they will be enriched in the future if DSE adds custom CQL syntax.

Currently, the only difference is the support for the DETERMINISTIC and MONOTONIC keywords when generating CREATE FUNCTION or CREATE AGGREGATE statements:

import static com.datastax.dse.driver.api.querybuilder.DseSchemaBuilder.createDseFunction;

createDseFunction("func1")
    .withParameter("param1", DataTypes.INT)
    .returnsNullOnNull()
    .returnsType(DataTypes.INT)
    .deterministic()
    .monotonic();
// CREATE FUNCTION func1 (param1 int) RETURNS NULL ON NULL INPUT RETURNS int DETERMINISTIC MONOTONIC

Immutability

All types in the fluent API are immutable. This means that every step creates a new object:

SelectFrom selectFrom = selectFrom("ks", "user");

Select select1 = selectFrom.column("first_name"); // SELECT first_name FROM ks.user
Select select2 = selectFrom.column("last_name"); // SELECT last_name FROM ks.user

assert select1 != select2;

Immutability has great benefits:

• thread safety: you can share built queries across threads, without any race condition or badly published state.
• zero sharing: when you build multiple queries from a shared "base" (as in the example above), all the queries are totally independent, changes to one query will never "pollute" another.

On the downside, immutability means that the query builder creates lots of short-lived objects. Modern garbage collectors are good at handling that, but still we recommend that you avoid using the query builder in your hot path:

• favor bound statements for queries that are used often. You can still use the query builder and prepare the result:

  // During application initialization:
  Select selectUser = selectFrom("user").all().whereColumn("id").isEqualTo(bindMarker());
  // SELECT * FROM user WHERE id=?
  PreparedStatement preparedSelectUser = session.prepare(selectUser.build());
  
  // At runtime:
  session.execute(preparedSelectUser.bind(userId));

• for queries that never change, build them when your application initializes, and store them in a field or constant for later.

• for queries that are built dynamically, consider using a cache.

Identifiers

All fluent API methods use CqlIdentifier for schema element names (keyspaces, tables, columns...). But, for convenience, there are also String overloads that take the CQL form (as see Case sensitivity for more explanations).

For conciseness, we'll use the string-based versions for the examples in this manual.

Non-goals

The query builder is NOT:

A crutch to learn CQL

While the fluent API guides you, it does not encode every rule of the CQL grammar. Also, it supports a wide range of Cassandra versions, some of which may be more recent than your production target, or not even released yet. It's still possible to generate invalid CQL syntax if you don't know what you're doing.

You should always start with a clear idea of the CQL query, and write the builder code that produces it, not the other way around.

A better way to write static queries

The primary use case of the query builder is dynamic generation. You will get the most value out of it when you do things like:

// The columns to select are only known at runtime:
for (String columnName : columnNames) {
  select = select.column(columnName)
}

// If a search parameter is present, add the corresponding WHERE clause:
if (name != null) {
  select = select.whereColumn("name").isEqualTo(name);
}

If all of your queries could also be written as compile-time string constants, ask yourself what the query builder is really buying you:

// Built version:
private static final Statement SELECT_USERS =
    selectFrom("user").all().limit(10).build();

// String version:
private static final Statement SELECT_USERS =
    SimpleStatement.newInstance("SELECT * FROM user LIMIT 10");

The built version:

• is slightly more expensive to build (admittedly, that is not really an issue for constants);
• is not more readable;
• is not necessarily less error-prone (see the previous section).

It eventually boils down to personal taste, but for simple cases you should consider raw strings as a better alternative.

Building queries

For a complete tour of the API, browse the child pages in this manual:

• statement types:
  • SELECT
  • INSERT
  • UPDATE
  • DELETE
  • TRUNCATE
  • Schema builder (for DDL statements such as CREATE TABLE, etc.)
• common topics:
  • Relations
  • Conditions
  • Terms
  • Idempotence