Built statements

Built statements are generated via QueryBuilder‘s Fluent API. Use of Fluent API allows easier build of complex queries, as opposed to use of hardcoded query strings.

Note: The provided builders perform very little validation of the built query. There is no guarantee that a built query is valid, and it is definitively possible to create invalid queries.

Queries built with QueryBuilder are executed the same way as other queries–via execute or executeAsync. When a query is built with inlined values, then it doesn’t differ much from a statement specified as a string. But it’s also possible to build the query with bind markers inside it, and then convert it into a prepared statement.

Basics

Generation of BuiltStatement is easy–start by calling of one of the QueryBuilder’s methods that represent the CQL’s “verb”: select, update, delete, insertInto, or truncate, provide required parameters, and then call “verb”-specific functions to form a complete CQL statement (like, where, from, etc.). The statement’s target table can be specified as a simple table name (if a default keyspace has been set when creating the Session object), as a combination of keyspace name and table name, or as a TableMetadata object.

Note: The QueryBuilder doesn’t provide support for the full set of CQL. For most of DDL operations (CREATE TABLE, etc.) you can use the SchemaBuilder. To perform other operations, for example, for role management, you still need to use simple statements.

Selecting data

Selection of data is quite simple–at minimum you need to provide a list of columns to select, and then specify from which table to select these columns (you can also optionally specify a condition, as described in the next section):

BuiltStatement selectAll1 = QueryBuilder.select("id", "t").from("test", "test");
ResultSet rs = session.execute(selectAll1);
for (Row row: rs) {
   System.out.println(row);
}

Note: The call select("column1", "column2") is really a shortcut for a chain of calls select().column("column1").column("column2").

Please note that you can’t pass the * as column name to select all columns–if you do this, you’ll get an exception about unknown column. To select all columns you either need to use select in combination with all function, or simply don’t specify a list of columns:

BuiltStatement selectAll2 = QueryBuilder.select().all().from("test", "test");
BuiltStatement selectAll3 = QueryBuilder.select().from("test", "test");

Besides selection of the specific columns, it’s also possible to call arbitrary CQL function by using the fcall method (this is just example, don’t do this on real data):

BuiltStatement sum = QueryBuilder.select().fcall("sum", column("id")).as("sum_id")
        .from("test", "test");

Note: When using functions, Cassandra will generate aliases for you, but you can provide explicit aliases by using as right after a given selector.

For often used functions, there are shortcuts, such as, countAll, ttl, writeTime, uuid, now, toJson, etc.:

BuiltStatement count = QueryBuilder.select().countAll()
        .from("test", "test");

BuiltStatement ttlAndWriteTime = QueryBuilder.select().column("id").column("t")
                .ttl("t").as("id_ttl").writeTime("t")
                .from("test", "test");

You can also cast the value of the given column to another type by using the cast function, specifying the column for which it should be performed, and to what type it should be casted.

Specifying conditions

Selection of data rarely happen on the whole table–in most cases, people are interested in particular rows, located in one or several partitions. Conditions are specified by using the where call, like this:

BuiltStatement selectOne = QueryBuilder.select().from("test", "test")
        .where(QueryBuilder.eq("id", 1));

The where function accepts the Clause object that is generated by calling QueryBuilder’s functions, such as, eq, ne, lt, in, contains, notNull, etc. In most cases, these functions receive 2 arguments: the name of the column, and the value to compare, but there are also variants that receive 2 iterables for columns and values correspondingly.

Note: as queries are becoming more and more complex, repeating QueryBuilder at all places will make code less readable. In this case to simplify the code you can import all (or only required) static functions of the QueryBuilder class (this is the same example from above):

import static com.datastax.driver.core.querybuilder.QueryBuilder.*;

//...

BuiltStatement selectOne = QueryBuilder.select().from("test", "test")
        .where(eq("id", 1));

In case if you need to specify complex conditions, you can chain additional clauses together with the and operator, that accepts the same clauses as where:

BuiltStatement select = QueryBuilder.select().from("test", "test")
        .where(eq("id", "1")).and(eq("txt", "test"));

Other selection options

For SELECT statements you can also specify a lot of different options: - allowFiltering generates a corresponding ALLOW FILTERING part of query (only use if you know what you’re doing!); - limit and perPartitionLimit allows to specify the amount of data to fetch; - groupBy performs grouping of data; - orderBy allows to specify sorting direction for specified clustering columns;

This very “artificial” example shows the use for some of them:

BuiltStatement selectOne = QueryBuilder.select().from("test")
        .where(QueryBuilder.eq("id", 1)).limit(1).allowFiltering()
        .perPartitionLimit(1).orderBy(desc("id"));

Inserting data

Insertion of data is straightforward–you specify the target table in a call to insertInto, and then provide values to insert either by chaining several calls to the value function, or by using the values function and passing lists or arrays of column names and their corresponding values. The following 2 examples are equivalent:

QueryBuilder.insertInto("test").value("id", 4).value("t", "test 4");
QueryBuilder.insertInto("test").values(Arrays.asList("id", "t"), Arrays.asList(4, "test 4"));

You can also insert JSON-formatted data by calling the json function & passing the data:

QueryBuilder.insertInto("test").json("{\"id\":4, \"t\":\"test 4\"}");

QueryBuilder also allows generation of the statement that use lightweight transactions (LWT) to check that inserted data doesn’t exist yet. You just need to add the call to ifNotExists to the statement:

QueryBuilder.insertInto("test").value("id", 4).ifNotExists();

It is also possible to specify additional metadata for inserted data, such as TTL (time to live) or timestamp. This is achieved with the using method and providing the Using object that is generated either by ttl, or timestamp functions of the QueryBuilder class. If you want to specify both, you need to chain them together with the and operator:

QueryBuilder.insertInto("test").value("id", 4).using(ttl(10)).and(timestamp(1000));

Besides this, for newer versions of Cassandra it’s possible to specify additional parameters, such as DEFAULT UNSET & DEFAULT NULL in the INSERT INTO ... JSON statements, by using defaultUnset & defaultNull correspondingly.

Update statements

Updating the data is also relatively straightforward: you specify the data to update, condition, and additional options if necessary:

BuiltStatement updateStatement = QueryBuilder.update("test").with(set("test", 1))
        .where(eq("id", 1));

The first update operation is passed as an argument to the with function, and additional operations could be chained via and calls:

BuiltStatement updateStatement = QueryBuilder.update("test").with(set("t", "test 1"))
        .and(set("x", 10)).where(eq("id", 1));

Besides the most often used set operation, there is a lot of operations for work with all types of collections (lists, maps & sets): add, discard, prepend, put, remove, setIdx, etc. For the full list of operations, see the QueryBuilder’s documentation.

To update counters you can use the incr & decr functions that take the column name, and the value by which column will be increased or decreased:

BuiltStatement query = QueryBuilder.update("counters")
        .with(incr("counter", 1)).where(eq("id", 1));

Similarly to insert statements, it’s also possible to perform conditional updates by calling either ifExists (to perform the update only if the entry exists), or by calling onlyIf with a Clause object–in this case the row will be updated only if the clause returns true:

Statement updateStatement = QueryBuilder.update("test").with(set("t", "test 1"))
        .where(eq("id", 1)).ifExists();

Setting the TTL & write timestamp is done the same way as for insert statements.

Deleting data

You can delete either the whole row matching your condition:

BuiltStatement deleteStmt = QueryBuilder.delete().from("test")
                              .where(eq("id", "1")).and(eq("txt", "test"));

or specify a list of columns to delete:

BuiltStatement deleteStmt = QueryBuilder.delete("col1", "col2").from("test")
                              .where(eq("id", "1")).and(eq("txt", "test"));

Specification of conditions is similar to the other operations described above, including conditional deletes with ifExists & onlyIf.

Prepared statements

If you’re repeating the same operation very often, the more effective way will be to create a prepared statement from the BuiltStatement. To do this, instead of the real values, use bind markers created either by calling bindMarker (which generates a positional placeholder), or by calling bindMarker("name") (which creates a named placeholder). After the statement is generated, just prepare it as usual, then bind, and execute:

BuiltStatement selectOne2 = QueryBuilder.select().from("test", "test")
        .where(eq("id", bindMarker()));
PreparedStatement preparedStatement = session.prepare(selectOne2);
ResultSet rs = session.execute(preparedStatement.bind(1));

Setting additional options

As in the case of regular statements, you can also set options on built statements, such as the consistency level (with setConsistencyLevel), enable/disable tracing (with enableTracing/disableTracing), specify retry policy (with setRetryPolicy), etc.

Note: the call to these functions changes the object type from BuiltStatement to Statement or RegularStatement, so you won’t be able to use functions specific to BuiltStatement without explicit casting.