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

Caution

You're viewing documentation for a previous version of Scylla Java Driver. Switch to the latest stable version.

Scylla Java Driver Manual Query builder Relations

Relations¶

A relation is a clause that appears after the WHERE keyword, and restricts the rows that the statement operates on.

Relations are used by the following statements:

  • SELECT

  • UPDATE

  • DELETE

  • CREATE MATERIALIZED VIEW

The easiest way to add a relation is with a whereXxx method in the fluent API:

selectFrom("sensor_data").all()
    .whereColumn("id").isEqualTo(bindMarker())
    .whereColumn("date").isGreaterThan(bindMarker());
// SELECT * FROM sensor_data WHERE id=? AND date>?

You can also create it manually with one of the factory methods in Relation, and then pass it to where():

selectFrom("user").all().where(
    Relation.column("id").isEqualTo(bindMarker()));
// SELECT * FROM user WHERE id=?

If you call where() multiple times, the clauses will be joined with the AND keyword. You can also add multiple relations in a single call. This is a bit more efficient since it creates less temporary objects:

selectFrom("sensor_data").all()
    .where(
        Relation.column("id").isEqualTo(bindMarker()),
        Relation.column("date").isGreaterThan(bindMarker()));
// SELECT * FROM sensor_data WHERE id=? AND date>?

Relations are generally composed of a left operand, an operator, and an optional right-hand-side term. The type of relation determines which operators are available.

Simple columns¶

whereColumn operates on a single column. It supports basic arithmetic comparison operators:

| Comparison operator | Method name | |———————|————————–| | = | isEqualTo | | < | isLessThan | | <= | isLessThanOrEqualTo | | > | isGreaterThan | | >= | isGreaterThanOrEqualTo | | != | isNotEqualTo |

Note: we support != because it is present in the CQL grammar but, as of Cassandra 4, it is not implemented yet.

See above for comparison operator examples.

If you’re using SASI indices, you can also use like() for wildcard comparisons:

selectFrom("user").all().whereColumn("last_name").like(literal("M%"));
// SELECT * FROM user WHERE last_name LIKE 'M%'

in() is like isEqualTo(), but with various alternatives. You can either provide each alternative as a term:

selectFrom("user").all().whereColumn("id").in(literal(1), literal(2), literal(3));
// SELECT * FROM user WHERE id IN (1,2,3)

selectFrom("user").all().whereColumn("id").in(bindMarker(), bindMarker(), bindMarker());
// SELECT * FROM user WHERE id IN (?,?,?)

Or bind the whole list of alternatives as a single variable:

selectFrom("user").all().whereColumn("id").in(bindMarker());
// SELECT * FROM user WHERE id IN ?

For collection columns, you can check for the presence of an element with contains() and containsKey():

selectFrom("sensor_data")
    .all()
    .whereColumn("id").isEqualTo(bindMarker())
    .whereColumn("date").isEqualTo(bindMarker())
    .whereColumn("readings").containsKey(literal("temperature"))
    .allowFiltering();
// SELECT * FROM sensor_data WHERE id=? AND date=? AND readings CONTAINS KEY 'temperature' ALLOW FILTERING

Finally, isNotNull() generates an IS NOT NULL check. Note: we support IS NOT NULL because it is present in the CQL grammar but, as of Cassandra 4, it is not implemented yet.

Column components¶

whereMapValue operates on an value inside of a map:

selectFrom("sensor_data")
    .all()
    .whereColumn("id").isEqualTo(bindMarker())
    .whereColumn("date").isEqualTo(bindMarker())
    .whereMapValue("readings", literal("temperature")).isGreaterThan(literal(65))
    .allowFiltering();
// SELECT * FROM sensor_data WHERE id=? AND date=? AND readings['temperature']>65 ALLOW FILTERING

Column components support the six basic arithmetic comparison operators.

Tokens¶

whereToken hashes one or more columns into a token. It is generally used to perform range queries:

selectFrom("user")
    .all()
    .whereToken("id").isGreaterThan(bindMarker())
    .whereToken("id").isLessThanOrEqualTo(bindMarker());
// SELECT * FROM user WHERE token(id)>? AND token(id)<=?

It supports the six basic arithmetic comparison operators.

Multi-column relations¶

whereColumns compares a set of columns to tuple terms of the same arity. It supports the six basic arithmetic comparison operators (using lexicographical order):

selectFrom("sensor_data")
    .all()
    .whereColumn("id").isEqualTo(bindMarker())
    .whereColumns("date", "hour").isGreaterThan(tuple(bindMarker(), bindMarker()));
// SELECT * FROM sensor_data WHERE id=? AND (date,hour)>(?,?)

In addition, tuples support the in() operator. Like with regular columns, bind markers can operate at different levels:

// Bind the whole list of alternatives (two-element tuples) as a single value:
selectFrom("test")
    .all()
    .whereColumn("k").isEqualTo(literal(1))
    .whereColumns("c1", "c2").in(bindMarker());
// SELECT * FROM test WHERE k=1 AND (c1,c2) IN ?

// Bind each alternative as a value:
selectFrom("test")
    .all()
    .whereColumn("k").isEqualTo(literal(1))
    .whereColumns("c1", "c2").in(bindMarker(), bindMarker(), bindMarker());
// SELECT * FROM test WHERE k=1 AND (c1,c2) IN (?,?,?)

// Bind each element in the alternatives as a value:
selectFrom("test")
    .all()
    .whereColumn("k").isEqualTo(literal(1))
    .whereColumns("c1", "c2").in(
        tuple(bindMarker(), bindMarker()),
        tuple(bindMarker(), bindMarker()),
        tuple(bindMarker(), bindMarker()));
// SELECT * FROM test WHERE k=1 AND (c1,c2) IN ((?,?),(?,?),(?,?))

Custom index expressions¶

whereCustomIndex evaluates a custom index. The argument is a free-form term (what is a legal value depends on your index implementation):

selectFrom("foo")
    .all()
    .whereColumn("k").isEqualTo(literal(1))
    .whereCustomIndex("my_custom_index", literal("a text expression"));
// SELECT * FROM foo WHERE k=1 AND expr(my_custom_index,'a text expression')

Raw snippets¶

Finally, it is possible to provide a raw CQL snippet with whereRaw(); it will get appended to the query as-is, without any syntax checking or escaping:

selectFrom("foo").all().whereRaw("k = 1 /*some custom comment*/ AND c<2");
// SELECT * FROM foo WHERE k = 1 /*some custom comment*/ AND c<2

This should be used with caution, as it’s possible to generate invalid CQL that will fail at execution time; on the other hand, it can be used as a workaround to handle new CQL features that are not yet covered by the query builder.

PREVIOUS
INSERT
NEXT
Schema builder
  • 4.11.1.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
      • 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

  • Relations
    • Simple columns
    • Column components
    • Tokens
    • Multi-column relations
    • Custom index expressions
    • Raw snippets
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