Caution
You're viewing documentation for a previous version of Scylla Java Driver. Switch to the latest stable version.
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.
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.
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
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
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
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
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
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
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
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.
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 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.
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