Built statements¶

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.

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

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

//...

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

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

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.