# Built statements

Built statements are generated via [QueryBuilder](https://java-driver.docs.scylladb.com/scylla-3.11.5.x/api/com/datastax/driver/core/querybuilder/QueryBuilder.html)’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](https://java-driver.docs.scylladb.com/scylla-3.11.5.x/api/com/datastax/driver/core/querybuilder/QueryBuilder.html)’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](https://java-driver.docs.scylladb.com/scylla-3.11.5.x/api/com/datastax/driver/core/TableMetadata.html) 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](https://java-driver.docs.scylladb.com/scylla-3.11.5.x/api/com/datastax/driver/core/schemabuilder/SchemaBuilder.html).  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):

```java
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:

```java
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):

```java
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.:

```default
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](https://java-driver.docs.scylladb.com/scylla-3.11.5.x/api/com/datastax/driver/core/querybuilder/QueryBuilder.html#cast-java.lang.Object-com.datastax.driver.core.DataType-)
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:

```java
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](https://java-driver.docs.scylladb.com/scylla-3.11.5.x/api/com/datastax/driver/core/querybuilder/QueryBuilder.html#eq-java.lang.Iterable-java.lang.Iterable-),
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):

```java
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`:

```java
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:

```java
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:

```java
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:

```java
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:

```java
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:

```java
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:

```java
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:

```java
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](https://java-driver.docs.scylladb.com/scylla-3.11.5.x/api/com/datastax/driver/core/querybuilder/QueryBuilder.html)’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:

```java
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:

```java
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:

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

or specify a list of columns to delete:

```java
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:

```java
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.