ScyllaDB Docs 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

Was this page helpful?

ScyllaDB Java Driver is available under the Apache v2 License. ScyllaDB Java Driver is a fork of DataStax Java Driver. See Copyright here.