## Prepared statements
Use [PreparedStatement] for queries that are executed multiple times in your application:
```java
PreparedStatement prepared = session.prepare(
"insert into product (sku, description) values (?, ?)");
BoundStatement bound = prepared.bind("234827", "Mouse");
session.execute(bound);
session.execute(prepared.bind("987274", "Keyboard"));
```
When you prepare the statement, Cassandra will parse the query string, cache the result and return a unique identifier
(the `PreparedStatement` object keeps an internal reference to that identifier):
```ditaa
client driver Cassandra
--+------------------------+----------------+------
| | |
| session.prepare(query) | |
|----------------------->| |
| | PREPARE(query) |
| |--------------->|
| | |
| | |
| | | - compute id
| | | - parse query string
| | | - cache (id, parsed)
| | |
| | PREPARED(id) |
| |<---------------|
| PreparedStatement(id) | |
|<-----------------------| |
```
When you bind and execute a prepared statement, the driver will only send the identifier, which allows Cassandra to
skip the parsing phase:
```ditaa
client driver Cassandra
--+---------------------------------+---------------------+------
| | |
| session.execute(BoundStatement) | |
|-------------------------------->| |
| | EXECUTE(id, values) |
| |-------------------->|
| | |
| | |
| | | - get cache(id)
| | | - execute query
| | |
| | ROWS |
| |<--------------------|
| | |
|<--------------------------------| |
```
You should prepare only once, and cache the `PreparedStatement` in your application (it is thread-safe). If you call
`prepare` multiple times with the same query string, the driver will log a warning.
If you execute a query only once, a prepared statement is inefficient because it requires two roundtrips. Consider a
[simple statement](../simple/) instead.
### Parameters and binding
Parameters can be either anonymous or named (named parameters are only
available with [native protocol](../../native_protocol) v2 or above):
```java
ps1 = session.prepare("insert into product (sku, description) values (?, ?)");
ps2 = session.prepare("insert into product (sku, description) values (:s, :d)");
```
To turn the statement into its executable form, you need to *bind* it to create a [BoundStatement]. As shown previously,
there is a shorthand to provide the parameters in the same call:
```java
BoundStatement bound = ps1.bind("324378", "LCD screen");
```
You can also bind first, then use setters, which is slightly more
explicit:
```java
// Positional setters:
BoundStatement bound = ps1.bind()
.setString(0, "324378")
.setString(1, "LCD screen");
// Named setters:
BoundStatement bound = ps2.bind()
.setString("s", "324378")
.setString("d", "LCD screen");
```
You can use named setters even if the query uses anonymous parameters;
Cassandra will name the parameters after the column they apply to:
```java
BoundStatement bound = ps1.bind()
.setString("sku", "324378")
.setString("description", "LCD screen");
```
This can be ambiguous if the query uses the same column multiple times,
for example: `select * from sales where sku = ? and date > ? and date <
?`. In these situations, use positional setters or named parameters.
For native protocol V3 or below, all variables must be bound. With native
protocol V4 or above, variables can be left unset, in which case they
will be ignored server side (no tombstones will be generated). If you're
reusing a bound statement you can use the `unset` method to unset variables
that were previously set:
```java
BoundStatement bound = ps1.bind()
.setString("sku", "324378")
.setString("description", "LCD screen");
// Using the unset method to unset previously set value.
// Positional setter:
bound.unset("description");
// Named setter:
bound.unset(1);
```
A bound statement also has getters to retrieve the values. Note that
this has a small performance overhead since values are stored in their
serialized form.
`BoundStatement` is **not thread-safe**. You can reuse an instance multiple times with different parameters, but only
from a single thread and only if you use synchronous calls:
```java
BoundStatement bound = ps1.bind();
// This is safe:
bound.setString("sku", "324378");
session.execute(bound);
bound.setString("sku", "324379");
session.execute(bound);
// This is NOT SAFE. executeAsync runs concurrently with your code, so the first execution might actually read the
// values after the second setString call, and you would insert 324381 twice:
bound.setString("sku", "324380");
session.executeAsync(bound);
bound.setString("sku", "324381");
session.executeAsync(bound);
```
Also, make sure you don't accidentally reuse parameters from previous executions.
### Preparing on multiple nodes
Cassandra does not replicate prepared statements across the cluster. It is the
driver's responsibility to ensure that each node's cache is up to
date. It uses a number of strategies to achieve this:
1. When a statement is initially prepared, it is first sent to a single
node in the cluster (this avoids hitting all nodes in case
the query string is wrong). Once that node replies successfully, the
driver re-prepares on all remaining nodes:
```ditaa
client driver node1 node2 node3
--+------------------------+----------------+--------------+------+---
| | | | |
| session.prepare(query) | | | |
|----------------------->| | | |
| | PREPARE(query) | | |
| |--------------->| | |
| | | | |
| | PREPARED(id) | | |
| |<---------------| | |
| | | | |
| | | | |
| | PREPARE(query) | |
| |------------------------------>| |
| | | | |
| | PREPARE(query) | |
| |------------------------------------->|
| | | | |
|<-----------------------| | | |
```
The prepared statement identifier is deterministic (it's a hash of the query string), so it is the same
for all nodes.
2. if a node crashes, it loses all of its prepared statements. So the
driver keeps a client-side cache; anytime a node is marked back up,
the driver re-prepares all statements on it;
3. finally, if the driver tries to execute a statement and finds out
that the coordinator doesn't know about it, it will re-prepare the
statement on the fly (this is transparent for the client, but will cost
two extra roundtrips):
```ditaa
client driver node1
--+-------------------------------+------------------------------+--
| | |
|session.execute(boundStatement)| |
+------------------------------>| |
| | EXECUTE(id, values) |
| |----------------------------->|
| | |
| | UNPREPARED |
| |<-----------------------------|
| | |
| | |
| | PREPARE(query) |
| |----------------------------->|
| | |
| | PREPARED(id) |
| |<-----------------------------|
| | |
| | |
| | EXECUTE(id, values) |
| |----------------------------->|
| | |
| | ROWS |
| |<-----------------------------|
| | |
|<------------------------------| |
```
You can customize these strategies through `QueryOptions`:
* [setPrepareOnAllHosts] controls whether statements are initially
re-prepared on other hosts (step 1 above);
* [setReprepareOnUp] controls whether statements are re-prepared on a
node that comes back up (step 2 above).
Changing the driver's defaults should be done with care and only in
specific situations; read each method's Javadoc for detailed
explanations.
### Avoid preparing 'SELECT *' queries
Both the driver and Cassandra maintain a mapping of `PreparedStatement` queries to their
metadata. When a change is made to a table, such as a column being added or dropped, there
is currently no mechanism for Cassandra to invalidate the existing metadata. Because of this,
the driver is not able to properly react to these changes and will improperly read rows after
a schema change is made.
Therefore it is currently recommended to list all columns of interest in
your prepared statements (i.e. `SELECT a, b, c FROM table`), instead of
relying on `SELECT *`.
This will be addressed in a future release of both Cassandra and the driver. Follow
[CASSANDRA-10786] and [JAVA-1196] for more information.
[PreparedStatement]: http://docs.datastax.com/en/drivers/java/3.4/com/datastax/driver/core/PreparedStatement.html
[BoundStatement]: http://docs.datastax.com/en/drivers/java/3.4/com/datastax/driver/core/BoundStatement.html
[setPrepareOnAllHosts]: http://docs.datastax.com/en/drivers/java/3.4/com/datastax/driver/core/QueryOptions.html#setPrepareOnAllHosts-boolean-
[setReprepareOnUp]: http://docs.datastax.com/en/drivers/java/3.4/com/datastax/driver/core/QueryOptions.html#setReprepareOnUp-boolean-
[execute]: http://docs.datastax.com/en/drivers/java/3.4/com/datastax/driver/core/Session.html#execute-com.datastax.driver.core.Statement-
[executeAsync]: http://docs.datastax.com/en/drivers/java/3.4/com/datastax/driver/core/Session.html#executeAsync-com.datastax.driver.core.Statement-
[CASSANDRA-10786]: https://issues.apache.org/jira/browse/CASSANDRA-10786
[JAVA-1196]: https://datastax-oss.atlassian.net/browse/JAVA-1196
distrib
>
Mageia
>
7
>
i586
>
media
>
core-release
>
by-pkgid
>
91bb7aae0d75a473c7417d2d08339482
>
files
>
74
