Built statements are generated via QueryBuilder's Fluent API. Use of Fluent API allows easier build of the complex queries, comparing with hardcoding the CQL statements into the source code.
Note: The provider builders perform very little validation of the built query. There is thus no guarantee that a built query is valid, and it is definitively possible to create invalid queries.
Queries built with QueryBuilder are execute the same way as other queries - via
execute or executeAsync. When query is built with putting all data into it, then it
doesn't differ much from statement specified as string. But it's also possible to build
the query with bind markers inside it, and then convert it into prepared statement.
Generation of BuiltStatement is easy - just start with calling of one of the
QueryBuilder's methods of that represent the CQL "verb": select, update, delete,
insertInto, or truncate, providing required parameters, and then call "verb"-specific
functions to form a complete CQL statement (like, where, from, etc.). The command's
target could be specified as table name (if you set default keyspace when creating a
Session object), keyspace & table name, or as TableMetadata.
Note: The QueryBuilder doesn't provide support for full set of CQL. For example, DDL
operations (CREATE TABLE, etc.), and many others aren't supported. To perform them you
still need to use simple statements.
Selection of data is quite simple - at minimum you need to provide a list of columns to select, and the specify from which table to select these columns (and conditions 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 there is also a possibility of calling of
arbitrary function by using the fcall (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 column names for you, but you can
provide explicit name by using as right after given expression.
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 & to what type it should be casted.
Selection of data rarely happen on the whole table - in most cases, people are interested
for 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 accept 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 value to compare, but there
are also variants that receive 2 iterables for columns & values separately.
Note: as query are becoming more complex, repeating QueryBuilder at all places will make
code less readable. In this case you can import all (or only required) static functions
for code simplification (this is rewritten 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 condition you can put additional clauses into chain
of the and calls that accepts the same clauses as where:
BuiltStatement select = QueryBuilder.select().from("test", "test")
.where(eq("id", "1")).and(eq("txt", "test"));For SELECT statements you can also specify a lot of different options:
allowFilteringgenerates correspondingALLOW FILTERINGpart of query (only use if you know what you're doing!);limit&perPartitionLimitallows to specify amount of data to fetch;groupByperforms group of data;orderByallows 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"));Insertion of data is straightforward - you're specifying the target table in call to
insertInto, and then provide values to insert either by chaining the several calls to
value function, or by using the values function passing the lists or arrays of column
names & corresponding values. 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 to generate the statement that will 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 also possible to specify additional metadata for inserted data, such as, TTL or
timestamp. This is achieved by using the using function and providing the Using
object generated either by ttl, or timestamp functions of the QueryBuilder class. In
case if you want to specify both of them, you need to wrap other into the call to and
function:
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.
Updating the data 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));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 plenty of operations for work with
all types of collections (lists, maps & sets): add, discard, prepend, put,
remove, setIdx, etc. For full list see the QueryBuilder's documentation.
To update counters you can use the incr & decr functions that takes the column name,
and 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 update by
calling either ifExists to perform only if entry exists, or calling onlyIf with
Clause object - in this case row will be updated only if 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 described in previous section.
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().from("test")
.where(eq("id", "1")).and(eq("txt", "test"));Specification of conditions is similar to the other operations described above, including
conditional delete with ifExists & onlyIf.
If you're repeating the same operation very often, the more effective way will be to
create the prepared statement from the BuiltStatement. To do it, instead
of the real values, use the bind marker that is created either by calling of bindMarker
that generates unnamed placeholder, or by calling bindMarker("name") to create a named
placeholder. After the statement is generated, just prepare it as usual, then bind &
execute:
BuiltStatement selectOne2 = QueryBuilder.select().from("test", "test")
.where(eq("id", bindMarker()));
PreparedStatement preparedStatement = session.prepare(selectOne2);
ResultSet rs = session.execute(preparedStatement.bind(1));As in the case of the usual statements, you can also set additional options for given
statement, such as, consistency level (with setConsistencyLevel), enable/disable tracing
(with enableTracing/disableTracing), retry policy (with setRetryPolicy), etc.
Note: the call to these functions changes the object type from BuiltStatement to the
Statement or RegularStatement.