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 SELECT

SELECT¶

Start your SELECT with the selectFrom method in QueryBuilder. There are several variants depending on whether your table name is qualified, and whether you use identifiers or raw strings:

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

SelectFrom selectUser = selectFrom("user");

Note that, at this stage, the query can’t be built yet. You need at least one selector.

Selectors¶

A selector is something that appears after the SELECT keyword, and will become a column in the result set. Its simplest form is a column identifier, but it can be a more complex expression.

The easiest way to add a selector is with one of the fluent shortcuts:

selectFrom("user")
    .column("first_name")
    .column("last_name");
// SELECT first_name,last_name FROM user

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

selectFrom("user").selector(
    Selector.column("first_name"));
// SELECT first_name FROM user

If you have multiple selectors, you can also use selectors() to add them all in a single call. This is a bit more efficient since it creates less temporary objects:

selectFrom("user").selectors(
    Selector.column("first_name"),
    Selector.column("last_name"));
// SELECT first_name,last_name FROM user

Use an alias to give a selector a different name in the result set:

selectFrom("user").column("first_name").as("first");
// SELECT first_name AS first FROM user

selectFrom("user").selector(
    Selector.column("first_name").as("first"));
// SELECT first_name AS first FROM user

The query builder provides many kinds of selectors. Some of them only work with newer Cassandra versions, always check what your target platform supports.

Star selector and count¶

all is the classic “star” selector that returns all columns. It cannot be aliased, and must be the only selector:

selectFrom("user").all();
// SELECT * FROM user

selectFrom("user").all().as("everything");
// throws IllegalStateException: Can't alias the * selector

If you add it to a query that already had other selectors, they will get removed:

selectFrom("user").column("first_name").all();
// SELECT * FROM user

If you add other selectors to a query that already had the star selector, the star selector gets removed:

selectFrom("user").all().column("first_name");
// SELECT first_name FROM user

If you add multiple selectors at once, and one of them is the star selector, an exception is thrown:

selectFrom("user").selectors(
    Selector.column("first_name"),
    Selector.all(),
    Selector.column("last_name"));
// throws IllegalArgumentException: Can't pass the * selector to selectors()

countAll counts the number of rows:

selectFrom("user").countAll();
// SELECT count(*) FROM user

Columns¶

We’ve already shown how column works:

selectFrom("user")
    .column("first_name")
    .column("last_name");
// SELECT first_name,last_name FROM user

When all your selectors are simple columns, there is a convenience shortcut to add them in one call:

selectFrom("user").columns("first_name", "last_name");
// SELECT first_name,last_name FROM user

Arithmetic operations¶

Selectors can be combined with arithmetic operations.

| CQL Operator | Selector name | |————–|—————| | a+b | add | | a-b | subtract | | -a | negate | | a*b | multiply | | a/b | divide | | a%b | remainder |

selectFrom("rooms")
    .multiply(Selector.column("length"), Selector.column("width"))
    .as("surface");
// SELECT length*width AS surface FROM rooms

Operations can be nested, and will get parenthesized according to the usual precedence rules:

selectFrom("foo")
    .multiply(
        Selector.negate(Selector.column("a")),
        Selector.add(Selector.column("b"), Selector.column("c")));
// SELECT -a*(b+c) FROM foo

Note: as shown in the examples above, arithmetic operations can get verbose very quickly. If you have common expressions that get reused throughout your application code, consider writing your own shortcuts:

public static Selector multiplyColumns(String left, String right) {
  return Selector.multiply(Selector.column(left), Selector.column(right));
}

selectFrom("rooms")
    .selector(multiplyColumns("length", "width"))
    .as("surface");
// SELECT length*width AS surface FROM rooms

Casts¶

Casting is closely related to arithmetic operations; it allows you to coerce a selector to a different data type. For example, if height and weight are two int columns, the following expression uses integer division and returns an int:

selectFrom("user")
    .divide(
        Selector.multiply(Selector.column("weight"), literal(10_000)),
        Selector.multiply(Selector.column("height"), Selector.column("height")))
    .as("bmi");
// SELECT weight*10000/(height*height) AS bmi FROM user

What if you want a floating-point result instead? You have to introduce a cast:

selectFrom("user")
    .divide(
        Selector.multiply(
            Selector.cast(Selector.column("weight"), DataTypes.DOUBLE),
            literal(10_000)),
        Selector.multiply(Selector.column("height"), Selector.column("height")))
    .as("bmi");
// SELECT CAST(weight AS double)*10000/(height*height) AS bmi FROM user

Type hints are similar to casts, with a subtle difference: a cast applies to an expression with an already well-established type, whereas a hint is used with a literal, where the type can be ambiguous.

selectFrom("foo").divide(
    // A literal 1 can be any numeric type (int, bigint, double, etc.)
    // It defaults to int, which is wrong here if we want a floating-point result.
    Selector.typeHint(literal(1), DataTypes.DOUBLE),
    Selector.column("a"));
// SELECT (double)1/a FROM foo

Sub-elements¶

These selectors extract an element from a complex column, for example:

  • a field from a user-defined type:

    selectFrom("user").field("address", "street");
    // SELECT address.street FROM user
    
  • an element, or range of elements, in a set or a map:

    selectFrom("product").element("features", literal("color"));
    // SELECT features['color'] FROM product
    
    selectFrom("movie").range("ratings", literal(3), literal(4));
    // SELECT ratings[3..4] FROM movie
    
    selectFrom("movie").range("ratings", literal(3), null);
    // SELECT ratings[3..] FROM movie
    
    selectFrom("movie").range("ratings", null, literal(3));
    // SELECT ratings[..3] FROM movie
    

Collections of selectors¶

Groups of selectors can be extracted as a single collection, such as:

  • a list or set. All inner selectors must return the same CQL type:

    selectFrom("user").listOf(
        Selector.column("first_name"),
        Selector.column("last_name"));
    // SELECT [first_name,last_name] FROM user
    
    selectFrom("user").setOf(
        Selector.column("first_name"),
        Selector.column("last_name"));
    // SELECT {first_name,last_name} FROM user
    
  • a map. All key and value selectors must have consistent types. In most cases, Cassandra will require a type hint for the outer map, so the query builder can generate that for you if you provide the key and value types:

    Map<Selector, Selector> mapSelector = new HashMap<>();
    mapSelector.put(literal("first"), Selector.column("first_name"));
    mapSelector.put(literal("last"), Selector.column("last_name"));
    
    selectFrom("user").mapOf(mapSelector, DataTypes.TEXT, DataTypes.TEXT);
    // SELECT (map<text,text>){'first':first_name,'last':last_name} FROM user
    
  • a tuple. This time the types can be heterogeneous:

    selectFrom("user").tupleOf(
        Selector.column("first_name"),
        Selector.column("birth_date"));
    // SELECT (first_name,birth_date) FROM user
    

Functions¶

Function calls take a function name (optionally qualified with a keyspace), and a list of selectors that will be passed as arguments:

selectFrom("user").function("utils", "bmi", Selector.column("weight"), Selector.column("height"));
// SELECT utils.bmi(weight,height) FROM user

The built-in functions ttl and writetime have convenience shortcuts:

selectFrom("user").writeTime("first_name").ttl("last_name");
// SELECT writetime(first_name),ttl(last_name) FROM user

Literals¶

Occasionally, you’ll need to inline a CQL literal in your query; this is not very useful as a top-level selector, but could happen as part of an arithmetic operation:

selectFrom("foo").quotient(literal(1), Selector.column("a"));
// SELECT 1/a FROM foo

See the terms section for more details on literals.

Raw snippets¶

Lastly, a selector can be expressed as a raw CQL snippet, that will get appended to the query as-is, without any syntax checking or escaping:

selectFrom("user").raw("first_name, last_name /*some random comment*/");
// SELECT first_name, last_name /*some random comment*/ FROM user

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.

Relations¶

Relations get added with the whereXxx() methods:

selectFrom("user").all().whereColumn("id").isEqualTo(literal(1));
// SELECT * FROM user WHERE id=1

You can also create and add them manually:

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

Like selectors, they also have fluent shortcuts to build and add in a single call:

Relations are a common feature used by many types of statements, so they have a dedicated page in this manual.

Other clauses¶

The remaining SELECT clauses have a straightforward syntax. Refer to the javadocs for the fine print.

Groupings:

selectFrom("sensor_data")
    .function("max", Selector.column("reading"))
    .whereColumn("id").isEqualTo(bindMarker())
    .groupBy("date");
// SELECT max(reading) FROM sensor_data WHERE id=? GROUP BY date

Orderings:

import com.datastax.oss.driver.api.core.metadata.schema.ClusteringOrder;

selectFrom("sensor_data")
    .column("reading")
    .whereColumn("id").isEqualTo(bindMarker())
    .orderBy("date", ClusteringOrder.DESC);
// SELECT reading FROM sensor_data WHERE id=? ORDER BY date DESC

Limits:

selectFrom("sensor_data")
    .column("reading")
    .whereColumn("id").isEqualTo(bindMarker())
    .limit(10);
// SELECT reading FROM sensor_data WHERE id=? LIMIT 10

selectFrom("sensor_data")
    .column("reading")
    .whereColumn("id").isEqualTo(bindMarker())
    .perPartitionLimit(bindMarker("l"));
// SELECT reading FROM sensor_data WHERE id IN ? PER PARTITION LIMIT :l

Filtering:

selectFrom("user").all().allowFiltering();
// SELECT * FROM user ALLOW FILTERING
PREVIOUS
Type
NEXT
Terms
  • 4.10.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
      • 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

  • SELECT
    • Selectors
      • Star selector and count
      • Columns
      • Arithmetic operations
      • Casts
      • Sub-elements
      • Collections of selectors
      • Functions
      • Literals
      • Raw snippets
    • Relations
    • Other clauses
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