Scylla Documentation Logo Documentation
  • Server
    • Scylla Open Source
    • Scylla Enterprise
    • Scylla Alternator
  • Cloud
    • Scylla Cloud
    • Scylla Cloud Docs
  • Tools
    • Scylla Manager
    • Scylla Monitoring Stack
    • Scylla Operator
  • Drivers
    • CQL Drivers
    • DynamoDB Drivers
Download
Menu
Scylla Java Driver Manual 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.scylladb</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

PREVIOUS
OSGi
NEXT
Conditions
  • 4.13.0.x
    • 4.13.0.x
    • 4.12.0.x
    • 4.11.1.x
    • 4.10.0.x
    • 4.7.2.x
    • 3.11.2.x
    • 3.11.0.x
    • 3.10.2.x
    • 3.7.2.x
  • Java Driver for Scylla and Apache Cassandra®
  • API Documentation
  • Manual
    • API conventions
    • Case sensitivity
    • Core driver
      • Address resolution
      • Asynchronous programming
      • Authentication
      • Bill of Materials (BOM)
      • Compression
      • Configuration
        • Reference configuration
      • Control connection
      • Custom codecs
      • Detachable types
      • Using the driver in GraalVM native images
      • Query idempotence
      • Integration
      • Load balancing
      • Logging
      • Metadata
        • Node metadata
        • Schema metadata
        • Token metadata
      • Metrics
      • Native protocol
      • Non-blocking programming
      • Paging
      • Performance
      • Connection pooling
      • Query timestamps
      • Reactive Style Programming
      • Reconnection
      • Request tracker
      • Retries
      • Using the shaded JAR
      • Speculative query execution
      • SSL
      • Statements
        • Batch statements
        • Per-query keyspace
        • Prepared statements
        • Simple statements
      • Temporal types
      • Request throttling
      • Query tracing
      • Tuples
      • User-defined types
    • Developer docs
      • Administrative tasks
      • Common infrastructure
        • Concurrency
        • Driver context
        • Event bus
      • Native protocol layer
      • Netty pipeline
      • Request execution
    • Mapper
      • Integration
        • Kotlin
        • Lombok
        • Java 14 Records
        • Scala
      • DAOs
        • Custom result types
        • Delete methods
        • GetEntity methods
        • Increment methods
        • Insert methods
        • Null saving strategy
        • Query methods
        • Query provider methods
        • Select methods
        • SetEntity methods
        • Statement attributes
        • Update methods
      • Entities
      • Mapper interface
    • OSGi
    • Query builder
      • Conditions
      • DELETE
      • Idempotence in the query builder
      • INSERT
      • Relations
      • Schema builder
        • Aggregate
        • Function
        • Index
        • Keyspace
        • Materialized View
        • Table
        • Type
      • SELECT
      • Terms
      • TRUNCATE
      • UPDATE
  • Upgrade guide
  • Frequently asked questions
  • Changelog
  • Create an issue
  • Edit this page

On this page

  • Query builder
    • General concepts
      • Fluent API
      • Datastax Enterprise
      • Immutability
      • Identifiers
    • Non-goals
      • A crutch to learn CQL
      • A better way to write static queries
    • Building queries
Logo
Docs Contact Us About Us
Mail List Icon Slack Icon
© 2022, ScyllaDB. All rights reserved.
Last updated on 25 May 2022.
Powered by Sphinx 4.3.2 & ScyllaDB Theme 1.2.2