11## Prepared statements
22
3- When Cassandra executes a query, the first thing it does is parse the
4- query string to an internal representation. If the same query is used
5- often, you can use a prepared statement, which allows Cassandra to cache
6- that representation, and save time and resources for subsequent
7- executions. Prepared statements are typically parameterized, using
8- different values for each execution:
3+ Use [ PreparedStatement] for queries that are executed multiple times in your application:
94
105``` java
116PreparedStatement prepared = session. prepare(
127 " insert into product (sku, description) values (?, ?)" );
138
14- BoundStatement bound;
15-
16- bound = prepared. bind(" 234827" , " Mouse" );
9+ BoundStatement bound = prepared. bind(" 234827" , " Mouse" );
1710session. execute(bound);
1811
19- bound = prepared. bind(" 987274" , " Keyboard" );
20- session. execute(bound);
12+ session. execute(prepared. bind(" 987274" , " Keyboard" ));
2113```
2214
23- Statements should be be prepared only once. If you call ` prepare `
24- multiple times with the same query string, the driver will log a
25- warning. So your application should cache the ` PreparedStatement ` object
26- once it's been created (this can be as simple as storing it as a field
27- in a DAO).
15+ When you prepare the statement, Cassandra will parse the query string, cache the result and return a unique identifier
16+ (the ` PreparedStatement ` object keeps an internal reference to that identifier):
17+
18+ ``` ditaa
19+ client driver Cassandra
20+ --+------------------------+----------------+------
21+ | | |
22+ | session.prepare(query) | |
23+ |----------------------->| |
24+ | | PREPARE(query) |
25+ | |--------------->|
26+ | | |
27+ | | |
28+ | | | - compute id
29+ | | | - parse query string
30+ | | | - cache (id, parsed)
31+ | | |
32+ | | PREPARED(id) |
33+ | |<---------------|
34+ | PreparedStatement(id) | |
35+ |<-----------------------| |
36+ ```
37+
38+ When you bind and execute a prepared statement, the driver will only send the identifier, which allows Cassandra to
39+ skip the parsing phase:
40+
41+ ``` ditaa
42+ client driver Cassandra
43+ --+---------------------------------+---------------------+------
44+ | | |
45+ | session.execute(BoundStatement) | |
46+ |-------------------------------->| |
47+ | | EXECUTE(id, values) |
48+ | |-------------------->|
49+ | | |
50+ | | |
51+ | | | - get cache(id)
52+ | | | - execute query
53+ | | |
54+ | | ROWS |
55+ | |<--------------------|
56+ | | |
57+ |<--------------------------------| |
58+ ```
59+
60+
61+ You should prepare only once, and cache the ` PreparedStatement ` in your application (it is thread-safe). If you call
62+ ` prepare ` multiple times with the same query string, the driver will log a warning.
63+
64+ If you execute a query only once, a prepared statement is inefficient because it requires two roundtrips. Consider a
65+ [ simple statement] ( ../simple/ ) instead.
2866
2967### Parameters and binding
3068
@@ -36,9 +74,8 @@ ps1 = session.prepare("insert into product (sku, description) values (?, ?)");
3674ps2 = session. prepare(" insert into product (sku, description) values (:s, :d)" );
3775```
3876
39- To turn the statement into its executable form, you need to * bind* it
40- and provide values for the parameters. As shown previously, there is a
41- shorthand form to do it all in one call:
77+ To turn the statement into its executable form, you need to * bind* it to create a [ BoundStatement] . As shown previously,
78+ there is a shorthand to provide the parameters in the same call:
4279
4380``` java
4481BoundStatement bound = ps1. bind(" 324378" , " LCD screen" );
@@ -74,7 +111,7 @@ for example: `select * from sales where sku = ? and date > ? and date <
74111
75112For native protocol V3 or below, all variables must be bound. With native
76113protocol V4 or above, variables can be left unset, in which case they
77- will be ignored server side (no tombstones will be generated). If you're
114+ will be ignored server side (no tombstones will be generated). If you're
78115reusing a bound statement you can use the ` unset ` method to unset variables
79116that were previously set:
80117
@@ -95,21 +132,19 @@ A bound statement also has getters to retrieve the values. Note that
95132this has a small performance overhead since values are stored in their
96133serialized form.
97134
98- ### How the driver handles prepared statements
135+ ` BoundStatement ` is ** not thread-safe** . You can reuse an instance multiple times with different parameters, but only
136+ from a single thread, and only if you use the synchronous API ([ Session#execute] [ execute ] is fine,
137+ [ Session#executeAsync] [ executeAsync ] is not).
138+ Also, make sure you don't accidentally reuse parameters from previous executions.
99139
100- When the driver prepares a statement, it sends the query string to
101- Cassandra, which caches the statement and returns an identifier. Later,
102- when the driver needs to execute the statement, it just sends the
103- identifier and parameter values. Note that the identifier is
104- deterministic, so it will always be the same for all nodes (it's a
105- actually a hash of the query string).
140+ ### Preparing on multiple nodes
106141
107- Prepared statements are not replicated across the cluster. It is the
142+ Cassandra does not replicate prepared statements across the cluster. It is the
108143driver's responsibility to ensure that each node's cache is up to
109144date. It uses a number of strategies to achieve this:
110145
1111461 . When a statement is initially prepared, it is first sent to a single
112- node in the cluster (this prevents from hitting all nodes in case
147+ node in the cluster (this avoids hitting all nodes in case
113148 the query string is wrong). Once that node replies successfully, the
114149 driver re-prepares on all remaining nodes:
115150
@@ -135,6 +170,9 @@ date. It uses a number of strategies to achieve this:
135170 |<-----------------------| | | |
136171 ```
137172
173+ The prepared statement identifier is deterministic (it's a hash of the query string), so it is the same
174+ for all nodes.
175+
1381762. if a node crashes, it loses all of its prepared statements. So the
139177 driver keeps a client-side cache; anytime a node is marked back up,
140178 the driver re-prepares all statements on it;
@@ -184,5 +222,9 @@ Changing the driver's defaults should be done with care and only in
184222specific situations; read each method's Javadoc for detailed
185223explanations.
186224
225+ [PreparedStatement]: http://docs.datastax.com/en/drivers/java/3.0/com/datastax/driver/core/PreparedStatement.html
226+ [BoundStatement]: http://docs.datastax.com/en/drivers/java/3.0/com/datastax/driver/core/BoundStatement.html
187227[setPrepareOnAllHosts]: http://docs.datastax.com/en/drivers/java/3.0/com/datastax/driver/core/QueryOptions.html#setPrepareOnAllHosts-boolean-
188- [setReprepareOnUp]: http://docs.datastax.com/en/drivers/java/3.0/com/datastax/driver/core/QueryOptions.html#setReprepareOnUp-boolean-
228+ [setReprepareOnUp]: http://docs.datastax.com/en/drivers/java/3.0/com/datastax/driver/core/QueryOptions.html#setReprepareOnUp-boolean-
229+ [execute]: http://docs.datastax.com/en/drivers/java/3.0/com/datastax/driver/core/Session.html#execute-com.datastax.driver.core.Statement-
230+ [executeAsync]: http://docs.datastax.com/en/drivers/java/3.0/com/datastax/driver/core/Session.html#executeAsync-com.datastax.driver.core.Statement-
0 commit comments