The DataStax Java driver is designed for CQL 3. The driver provides connection pooling, node discovery, automatic failover, and load balancing. The driver supports prepared statements. Queries can be run synchronously or asynchronously. The driver provides a layered architecture. At the bottom is the core layer, which handles connections to the Cassandra cluster. The core layer exposes a low-level API on which a higher-level layer may be built. In this chapter, you will connect with Cassandra server using the DataStax Java driver and perform create, read, update, delete (CRUD) operations on the database.
The main package for the DataStax Java driver core is com.datastax.driver.core
. The main classes in the package are shown in Figure 3.1.
The classes shown in Figure 3.1 are discussed in Table 3.1.
To set the environment, you must download the following software:
DataStax Java driver for Apache Cassandra–Core from http://mvnrepository.com/artifact/com.datastax.cassandra/cassandra-driver-core/2.0.1
Eclipse IDE for Java EE developers from http://www.eclipse.org/downloads/moreinfo/jee.php
Apache Cassandra apache-cassandra-2.0.4-bin.tar.gz or a later version from http://cassandra.apache.org/download/
Java SE 7 from http://www.oracle.com/technetwork/java/javase/downloads/index-jsp-138363.html
Metrics Core metrics-core-3.0.1.jar from http://mvnrepository.com/artifact/com.codahale.metrics/metrics-core/3.0.1
1. Extract the Apache Cassandra TAR file to a directory (for example, C:Cassandraapache-cassandra-2.0.4).
2. Add the bin
folder, C:Cassandraapache-cassandra-2.0.4in, to the PATH
environment variable.
3. Start Apache Cassandra server with the following command: cassandra
–f
In this section, you will use the DataStax Java driver in a Java application for which you need to create a Java project in Eclipse IDE. Follow these steps:
1. Select File > New > Other.
2. In the New window, select the Java Project wizard as shown in Figure 3.2. Then click Next.
3. In the Create a Java Project screen, specify a project name (Datastax) and choose a directory location or select the Use Default location checkbox. Then select the default JRE, which has been set to 1.7, and click Next, as shown in Figure 3.3.
4. Select the default options in the Java Settings screen and click Finish, as shown in Figure 3.4. A Java project is created.
5. Add a Java class to the project. To begin, choose File > New > Other. Then, in the New dialog box, select Java > Java Class and click Next, as shown in Figure 3.5.
6. In the New Java Class wizard, select a source folder (Datastax/src) and specify the package as datastax
. Then specify the Java class name (CQLClient
) and click Finish, as shown in Figure 3.6. A Java class is added to the Java project, as shown in the Package Explorer in Figure 3.7.
7. To be able to access Cassandra from the Java application using DataStax, you need to add some JAR files to the application’s Java build path. Right-click the Datastax project node in Package Explorer and select Properties. Then, in the Properties window, select the Java Build Path node and click the Add External JARs button to add external JAR files. Finally, add the JAR files listed in Table 3.2.
8. The external JAR files required for accessing Cassandra from a DataStax Java client application are shown in the Eclipse IDE Properties wizard. Click OK after adding the required JAR files, as shown in Figure 3.8.
In later sections, you will develop a Java application to connect with the Cassandra server using the DataStax Java driver and run CQL 3 queries to create, select, update, and delete data from the server. First, however, we will discuss how to connect with the Cassandra server.
In this section, you will connect to the Cassandra server. To begin, add a connection()
method to the CQLClient application. In the method, create an instance of Cluster
, which is the main entry point for the driver. The Cluster
instance maintains a connection with one of the server nodes to keep information on the state and current topology of the cluster. The driver discovers all the nodes in the cluster using auto-discovery of nodes, including new nodes that join later. Build a Cluster.Builder
instance, which is a helper class to build Cluster
instances, using the static
method builder()
.
You need to provide the connection address of at least one of the nodes in the Cassandra cluster for the DataStax driver to be able to connect with the cluster and discover other nodes in the cluster using auto-discovery. Using the addContactPoint(String)
method of Cluster.Builder
, add the address of the Cassandra server running on the localhost
(127.0.0.1
). Next, invoke the build()
method to build the Cluster
using the configured address(es). The methods may be invoked in sequence, as you don’t need the intermediary Cluster.Builder
instance.
Cluster cluster = Cluster.builder().addContactPoint("127.0.0.1").build();
Get the metadata of the cluster using the getMetadata()
method. The metadata includes the nodes in the cluster with their status. Creating a Cluster
instance does not by itself create a connection with the server. Getting metadata requires a connection with the server for which a connection is established, unless the getMetadata()
method is invoked after the init()
or connect()
method is invoked, which establishes a connection with the server. Obtain the cluster name using the getClusterName()
method in the Metadata
class. The getAllHosts()
method returns a set of all the known hosts in the cluster. Iterate over the set to output the hosts’ data center, address, and rack. The Cluster
class provides the methods discussed in Table 3.3 to connect the Cassandra server.
Next, invoke the connect()
method to create a session on the cluster. A session is represented with the Session
class, which holds multiple connections to the cluster. A Session
instance is used to query the cluster. The Session
instance provides policies on which node in the cluster to use for querying the cluster. The default policy is to use a round-robin on all the nodes in the cluster. Session
is also used to handle retries of failed queries. Session
instances are thread-safe, and a single instance is sufficient for an application. But a separate Session
instance is required if connecting to multiple keyspaces, as a single Session
instance is specific to a particular keyspace only.
Session session = cluster.connect();
The initial CQLClient application to create a connection with the server appears in Listing 3.1. You will develop the application in upcoming sections to add a keyspace, a table and run CQL 3 queries.
package datastax; import com.datastax.driver.core.Cluster; import com.datastax.driver.core.Host; import com.datastax.driver.core.Metadata; import com.datastax.driver.core.Session; public class CQLClient { private static Cluster cluster; private static Session session; public static void main(String[] args) { connection(); } private static void connection() { cluster = Cluster.builder().addContactPoint("127.0.0.1"). build(); Metadata metadata = cluster.getMetadata(); System.out.printf("Connected to cluster: %s ", metadata.getClusterName()); for (Host host : metadata.getAllHosts()) { System.out.printf("Datacenter: %s; Host: %s; Rack: %s ", host.getDatacenter(), host.getAddress(), host.getRack()); } session = cluster.connect(); } }
Right-click the CQLClient application and select Run As > Java Application, as shown in Figure 3.9.
A connection with the server is established and the cluster’s data center, host, and rack information is output, as shown in Figure 3.10.
If the Cassandra server is not running, the following exception is generated when a connection is attempted:
com.datastax.driver.core.exceptions.NoHostAvailableException: All host(s) tried for query failed (tried: /127.0.0.1 (com.datastax.driver.core. TransportException: [/127.0.0.1] Cannot connect)) at com.datastax.driver.core.ControlConnection.reconnectInternal (ControlConnection.java:179) at com.datastax.driver.core.ControlConnection.connect(ControlConnection. java:77) at com.datastax.driver.core.Cluster$Manager.init(Cluster.java:890) at com.datastax.driver.core.Cluster$Manager.access$100(Cluster.java:806) at com.datastax.driver.core.Cluster.getMetadata(Cluster.java:217) at datastax.CQLClient.connection(CQLClient.java:43) at datastax.CQLClient.main(CQLClient.java:23)
Session
CLASSThe Session
class provides several methods to prepare and run queries on the server. The methods to prepare or run queries are discussed in Table 3.4.
You need to create a keyspace in which to store tables. In the next section, you will create a keyspace.
In this section, you will create a keyspace using the Session
object to run a CQL 3 statement. Add a createKeyspace()
method to create a keyspace in the CQLClient application. CQL 3 has added support to run CREATE
statements conditionally, which is only if the object to be constructed does not already exist. The IF NOT EXISTS
clause is used to create conditionally. Create a keyspace called datastax
using replication with the strategy class SimpleStrategy
and a replication factor of 1
.
private static void createKeyspace() { session.execute("CREATE KEYSPACE IF NOT EXISTS datastax WITH replication " + "= {'class':'SimpleStrategy', 'replication_factor':1};"); }
Invoke the createKeyspace()
method in the main
method and run the CQLClient application to create a keyspace.
Next, you will create a column family, which is called a table in CQL 3. Add a createTable()
method to CQLClient
. The CREATE TABLE
command also supports IF NOT EXISTS
to create a table conditionally. CQL 3 has added the provision to create a compound primary key—that is, a primary key created from multiple component primary key columns. In a compound primary key, the first column is called the partition key. To demonstrate different aspects of using a compound primary key, create three different tables, catalog
, catalog2
, and catalog3
. Each of the tables has columns catalog_id
, journal
, publisher
, edition
, title
, and author
. In the catalog
table, the compound primary key is made from the catalog_id
and journal
columns, with catalog_id
being the partition key. In catalog2
, the same two columns are used in the compound key, but the journal
column is used as the partition key. In catalog3
, three columns are used in the compound key: catalog_id
, journal
, and publisher
. Invoke the execute(String)
method to create three tables, catalog
, catalog2
, and catalog3
, as follows:
private static void createTable() { session.execute("CREATE TABLE IF NOT EXISTS datastax.catalog (catalog_id text, journal text,publisher text, edition text,title text,author text,PRIMARY KEY (catalog_id, journal))"); session.execute("CREATE TABLE IF NOT EXISTS datastax.catalog2 (catalog_id text, journal text,publisher text, edition text,title text,author text,PRIMARY KEY (journal, catalog_id))"); session.execute("CREATE TABLE IF NOT EXISTS datastax.catalog3 (catalog_id text, journal text,publisher text, edition text,title text,author text,PRIMARY KEY (journal, catalog_id, publisher))"); }
Prefix the table name with the keyspace name. Invoke the createTable()
method in the main
method and run the CQLClient application to create the three tables.
INSERT
STATEMENTNext, you will add data to the three tables—catalog
, catalog2
, and catalog3
—using the INSERT
statement. Use the IF NOT EXISTS
keyword to add rows conditionally. When a compound primary key is used, all the component primary key columns must be specified, including the values for the compound key columns. For example, run the following CQL 3 query using a Session
object:
session.execute("INSERT INTO datastax.catalog (catalog_id, publisher, edition, title,author) VALUES ('catalog1', 'Oracle Publishing', 'November-December 2013', 'Engineering as a Service','David A. Kelly') IF NOT EXISTS");
Because the primary key component column, journal
, is not specified in the CQL 3 statement, the following exception is generated.
Exception in thread "main" com.datastax.driver.core.exceptions. InvalidQueryException: Missing mandatory PRIMARY KEY part journal
Add an insert()
method to the CQLClient
class and invoke the method in the main
method. Then add three rows identified by the row IDs catalog1
, catalog2
, and catalog3
to each of the tables (catalog
, catalog2
, and catalog3
). For example, the three rows are added to the catalog
table as follows:
private static void insert() { session.execute("INSERT INTO datastax.catalog (catalog_id, journal, publisher, edition,title,author) VALUES ('catalog1','Oracle Magazine', 'Oracle Publishing', 'November-December 2013', 'Engineering as a Service','David A. Kelly') IF NOT EXISTS"); session.execute("INSERT INTO datastax.catalog (catalog_id, journal, publisher, edition,title,author) VALUES ('catalog2','Oracle Magazine', 'Oracle Publishing', 'November-December 2013', 'Quintessential and Collaborative','Tom Haunert') IF NOT EXISTS"); session.execute("INSERT INTO datastax.catalog (catalog_id, journal, publisher) VALUES ('catalog3', 'Oracle Magazine','Oracle Publishing') IF NOT EXISTS"); }
Run the CQLClient application to add the three rows of data to each of the tables.
SELECT
STATEMENTNext, you will run a SELECT
statement to select columns from a table. Add a select()
method to run SELECT
statement(s). First, select all the columns from the catalog table using *
for column selection:
ResultSet results = session.execute("select * from datastax.catalog");
A row in the result set, represented by the ResultSet
interface, is represented with the Row
class. Iterate over the result set to output the column value or each of the columns:
private static void select() { ResultSet results = session.execute("select * from datastax.catalog"); for (Row row : results) { System.out.println("Catalog Id: " + row.getString("catalog_id")); System.out.println("Journal: " + row.getString("journal")); System.out.println("Publisher: " + row.getString("publisher")); System.out.println("Edition: " + row.getString("edition")); System.out.println("Title: " + row.getString("title")); System.out.println("Author: " + row.getString("author")); System.out.println(" "); System.out.println(" "); } }
Run the CQLClient application to select the rows from the datastax.catalog
table and output the columns as shown in Figure 3.11.
CQL 3 has added support for the ORDER BY
clause to order the result in ascending order (ASC
) by default. But the ORDER BY
clause is supported only if the partition key is restricted by an EQ
or IN
. To demonstrate, run the following query with ORDER BY
on the catalog_id
column:
ResultSet results = session.execute("select * from datastax.catalog ORDER BY catalog_id DESC");
This generates the following exception:
Caused by: com.datastax.driver.core.exceptions.InvalidQueryException: ORDER BY is only supported when the partition key is restricted by an EQ or an IN.
The catalog_id
column is the partition key in the catalog
table, so if ORDER BY
is to be used on that table, then the catalog_id
column must be restricted with an EQ
or IN
. But restricting catalog_id
would not be useful to demonstrate ordering of rows, as the result has only one row. Instead, use the catalog2
table, which has the journal
column as the partition column. Restrict the journal
column and use the ORDER BY
clause on the catalog_id
column as follows:
ResultSet results = session.execute("select * from datastax.catalog2 WHERE journal='Oracle Magazine' ORDER BY catalog_id DESC");
When the application is run, the rows are selected in descending order of the catalog_id
— that is, catalog3
, then catalog2
, and then catalog1
—as indicated by the output in Figure 3.12.
If the compound primary key has more than two columns, the ORDER BY
condition must be used on the second column. To demonstrate, use ORDER BY
on the publisher
column in the catalog3
table, which has three columns—journal
, catalog_id
, and publisher
, with publisher
being the third column.
ResultSet results = session.execute("select * from datastax.catalog3 WHERE journal='Oracle Magazine' ORDER BY publisher");
When the preceding query is run, the following exception is generated:
Caused by: com.datastax.driver.core.exceptions.InvalidQueryException: Order by currently only support the ordering of columns following their declared order in the PRIMARY KEY
To demonstrate the use of ORDER BY
with more than two columns in the primary key, specify the EQ
on the partition key, which is journal
in catalog3
, and the ORDER BY
on catalog_id
, which is the second column in the compound primary key:
ResultSet results = session.execute("select * from datastax.catalog3 WHERE journal='Oracle Magazine' ORDER BY catalog_id");
When the application is run, the rows are selected in ascending order of the catalog_id
— catalog1
, then catalog2
, followed by catalog3
. (Refer to Figure 3.11.)
Next, we will discuss filtering a query with the WHERE
clause. The columns used for filtering in the WHERE
clause must be indexed. The primary key column(s) is indexed automatically, so the primary key column(s) can be used in the WHERE
clause as such. If a non-indexed column is used in the WHERE
clause, the following exception is generated:
com.datastax.driver.core.exceptions.InvalidQueryException: No indexed columns present in by-columns clause with Equal operator
In the next section, you will create a secondary index on a non-primary key column title in the catalog
table.
A new secondary index on a column in a table is created with the CREATE INDEX
command. Add a createIndex()
method in the CQLQuery
class and invoke the method in the main
method. Then add a secondary index to the title
column using the CREATE INDEX
command. The CREATE INDEX
command supports the IF NOT EXISTS
clause. The IF NOT EXISTS
clause does not take into consideration whether a previously created index by the same name is for the same table definition as the new index or a different table definition. For example, if a previously created index named titleIndex
is for some table definition and a new index named titleIndex
is for a different table definition, and the IF NOT EXISTS clause is used, it would still not create the new index named titleIndex even though the new index has a different table definition.
The IF NOT EXISTS
clause should be used only if a previously created index by the same name could not have been created or is unlikely to have been created previously for another table with a different table definition (perhaps a primary key with a single column instead of a compound primary key).
private static void createIndex() { session.execute("CREATE INDEX titleIndex ON datastax.catalog (title)"); }
Run the CQLQuery application to create a secondary index on the title
column in the catalog
table. If the following exception is generated, it is better to drop the index and create it again if it is not certain that the index by the same name was created for the same table as required.
com.datastax.driver.core.exceptions.InvalidQueryException: Index already exists
SELECT
AND A WHERE
FILTERYou can refine a SELECT
query using a WHERE
clause. The WHERE
clause must specify the primary key component column(s), which is automatically indexed, or a column with a secondary index. We will discuss using SELECT
with WHERE
using different columns. Add a selectFilter()
method to the CQLQuery
class and invoke the method in the main
method. In the first example, select all the columns using the title
column in the WHERE
clause. The title
column has a secondary index defined on it and therefore can be used in the WHERE
clause.
private static void selectFilter() { ResultSet results = session.execute("SELECT catalog_id, journal, publisher, edition,title,author FROM datastax.catalog WHERE title='Engineering as a Service'"); for (Row row : results) { System.out.println("Journal: " + row.getString("journal")); System.out.println("Publisher: " + row.getString("publisher")); System.out.println("Edition: " + row.getString("edition")); System.out.println("Title: " + row.getString("title")); System.out.println("Author: " + row.getString("author")); System.out.println(" "); System.out.println(" "); } }
The output from the preceding query is as follows:
Journal: Oracle Magazine Publisher: Oracle Publishing Edition: November-December 2013 Title: Engineering as a Service Author: David A. Kelly
Select all columns from the catalog
table where the catalog_id
is "catalog2"
. The catalog_id
is the partition key in the catalog
table. Iterate over the result set to output the columns:
private static void selectFilter() { ResultSet results = session.execute("SELECT catalog_id, journal, publisher, edition,title,author FROM datastax.catalog WHERE catalog_id='catalog2'"); for (Row row : results) { System.out.println("Journal: " + row.getString("journal")); System.out.println("Publisher: " + row.getString ("publisher")); System.out.println("Edition: " + row.getString("edition")); System.out.println("Title: " + row.getString("title")); System.out.println("Author: " + row.getString("author")); System.out.println(" "); System.out.println(" "); } }
The following output is generated:
Journal: Oracle Magazine Publisher: Oracle Publishing Edition: November-December 2013 Title: Quintessential and Collaborative Author: Tom Haunert
Different versions of the selectFilter()
method are included in the code listing for CQLClient
at the end of this chapter with some or all versions commented out. De-comment the version that is to be tested. If the primary key is a compound key, the partition key can be used in the WHERE
clause without the other primary key component columns. However, a non-partition key cannot be used alone in a similar manner. To demonstrate, run the following query:
ResultSet results = session.execute("SELECT catalog_id, journal, publisher, edition,title,author FROM datastax.catalog WHERE journal='Oracle Magazine'");
The following exception is generated:
Caused by: com.datastax.driver.core.exceptions.InvalidQueryException: Cannot execute this query as it might involve data filtering and thus may have unpredictable performance. If you want to execute this query despite the performance unpredictability, use ALLOW FILTERING
To run the preceding query, add ALLOW FILTERING
to the SELECT
statement:
ResultSet results = session.execute("SELECT catalog_id, journal, publisher, edition,title,author FROM datastax.catalog WHERE journal='Oracle Magazine' ALLOW FILTERING");
The following output is generated with the preceding query:
Journal: Oracle Magazine Publisher: Oracle Publishing Edition: November-December 2013 Title: Engineering as a Service Author: David A. Kelly Journal: Oracle Magazine Publisher: Oracle Publishing Edition: November-December 2013 Title: Quintessential and Collaborative Author: Tom Haunert Journal: Oracle Magazine Publisher: null Edition: null Title: null Author: null
All the component columns in a compound primary key can be used in the WHERE
clause in any order, as in the following example:
ResultSet results = session.execute("SELECT catalog_id, journal, publisher, edition,title,author FROM datastax.catalog WHERE journal='Oracle Magazine' AND catalog_id='catalog2'");
This query generates the following output:
Journal: Oracle Magazine Publisher: Oracle Publishing Edition: November-December 2013 Title: Quintessential and Collaborative Author: Tom Haunert
Another example of using the WHERE
clause is using the IN
clause with a primary key column:
ResultSet results = session.execute("SELECT catalog_id, journal, publisher, edition,title,author FROM datastax.catalog WHERE catalog_id IN ('catalog2', 'catalog3')");
The preceding query generates the following output:
Journal: Oracle Magazine Publisher: Oracle Publishing Edition: November-December 2013 Title: Quintessential and Collaborative Author: Tom Haunert Journal: Oracle Magazine Publisher: null Edition: null Title: null Author: null
The IN
predicates can be used only on primary key columns. For example, if the IN
predicate is used on the title
column, which is an indexed column, the following exception is generated:
Exception in thread "main" com.datastax.driver.core.exceptions. InvalidQueryException: IN predicates on non-primary-key columns (title) is not yet supported
In CQL 3, the WHERE
clause allows greater than (>
) and less than (<
) relations on all the columns other than the first, which still must have the =
comparison. In the following example, the second column in the WHERE
clause has the >
relation:
ResultSet results = session .execute("SELECT catalog_id, journal, publisher, edition,title,author FROM datastax.catalog2 WHERE journal='Oracle Magazine' AND catalog_id > 'catalog1'");
The output from the preceding query is as follows:
Catalog Id: catalog2 Journal: Oracle Magazine Publisher: Oracle Publishing Edition: November-December 2013 Title: Quintessential and Collaborative Author: Tom Haunert Catalog Id: catalog3 Journal: Oracle Magazine Publisher: Oracle Publishing Edition: null Title: null Author: null
The last column in a WHERE
clause can have any type of relation if all the preceding columns have been specified with the =
comparison. In the following example, the last column has the >=
relation with all the preceding columns being identified with the =
comparison:
ResultSet results = session .execute("SELECT catalog_id, journal, publisher, edition,title,author FROM datastax.catalog2 WHERE journal='Oracle Magazine' AND catalog_id >= 'catalog1'");
The result of the query is as follows:
Catalog Id: catalog1 Journal: Oracle Magazine Publisher: Oracle Publishing Edition: November-December 2013 Title: Engineering as a Service Author: David A. Kelly Catalog Id: catalog2 Journal: Oracle Magazine Publisher: Oracle Publishing Edition: November-December 2013 Title: Quintessential and Collaborative Author: Tom Haunert Catalog Id: catalog3 Journal: Oracle Magazine Publisher: Oracle Publishing Edition: null Title: null Author: null
If the primary key is a compound key and the partition key is used in the WHERE
clause, only the EQ
and IN
relations are supported on the partition key. To demonstrate, use the >
relation on the partition key:
ResultSet results = session.execute("SELECT catalog_id, journal, publisher, edition,title,author FROM datastax.catalog WHERE catalog_id > 'catalog1'");
The following exception is generated:
Caused by: com.datastax.driver.core.exceptions.InvalidQueryException: Only EQ and IN relation are supported on the partition key (unless you use the token() function) at com.datastax.driver.core.Responses$ Error.asException(Responses.java:96)
Async
QUERYAs discussed, the Session
class supports two methods to run the CQL 3 query asynchronously: executeAsync(Query query)
and executeAsync(String query)
. Asynchronously implies that the method returns immediately and the processing of the application continues, the result being returned later. The Async
methods return a ResultSetFuture
object. A ResultSetFuture
object is not a ResultSet
object but a future on a ResultSet
object. The ResultSetFuture
class provides the methods listed in Table 3.5 to get the result of the query.
The ResultSetFuture
class provides the methods in Table 3.6 to cancel, or interrupt a future result set object.
The ResultSetFuture
class provides some other methods, which are discussed in Table 3.7.
Add an asyncQuery()
method to the CQLClient
class and invoke the method from the main
method. Then invoke the executeAsync(String)
method to return a ResultSetFuture
object.
ResultSetFuture resultsFuture = session.executeAsync("Select * from datastax.catalog");
Invoke the getUninterruptibly(long timeout,TimeUnit unit)
method on the ResultSetFuture
object with the timeout
set to 1,000,000 ms.
ResultSet results = resultsFuture.getUninterruptibly(1000000,TimeUnit. MILLISECONDS);
Iterate over the ResultSet
object to output the result of the query. If getUninterruptibly
throws a TimeoutException
, invoke the cancel(true)
method to cancel the future.
try { ResultSet results = resultsFuture.getUninterruptibly(1000000, TimeUnit.MILLISECONDS); for (Row row : results) { System.out.println("Journal: " + row.getString ("journal")); System.out.println("Publisher: " + row.getString ("publisher")); System.out.println("Edition: " + row.getString ("edition")); System.out.println("Title: " + row.getString("title")); System.out.println("Author: " + row.getString("author")); System.out.println(" "); System.out.println(" "); } } catch(TimeoutException e) { resultsFuture.cancel(true); System.out.println(e); }
Run the CQLClient application to output the result of the query. The result of the query is the same as it would be with the synchronous execute()
method, as shown in Figure 3.13.
Why use the async
version? If the query is expected to take an inordinate amount of time, it may be suitable to use the async
version while the processing of the application continues and to cancel or interrupt the query if required. Next, you’ll see how to cancel a query result set future after a specified duration. Set the timeout to 1 ms. Then run the CQLClient
method with the timeout set to 1 ms. Even a short running query may not return with such a small timeout. As indicated by the TimeoutException
in Figure 3.14, the result set future gets timed out before the result can be retrieved.
PreparedStatement
QUERYDataStax driver has the provision to create a prepared statement, which is a query with bind variables. The BoundStatement
is used to bind values to the bind variables of a PreparedStatement
. In this section, you will create a prepared statement and subsequently bind values to the bind variables using a BoundStatement
. The BoundStatement
class extends the Query
class. You will run the query in the BoundStatement
using the Session
class method execute(Query query)
. Add a preparedStmtQuery()
method to the CQLClient
class and invoke the method in the main
method. Create a PreparedStatement
using the Session
class method prepare(String query)
.
PreparedStatement stmt = session.prepare("SELECT catalog_id, journal, publisher, edition,title,author FROM datastax.catalog WHERE title=?");
The prepared statement has a bind variable for the title
column. Create a BoundStatement
from the PreparedStatment
object using the BoundStatement(PreparedStatement statement)
constructor.
BoundStatement boundStmt = new BoundStatement(stmt);
The BoundStatement
class provides the bind(Object... values)
method to bind values to the bind variables of a PreparedStatement
. The values are bound to the bind variables in the order specified. The first value is bound to the first bind variable, the second value to the second bind variable. Set the value of the title
variable:
boundStmt.bind("Engineering as a Service");
Run the query in the BoundStatement
, which extends Query
, using the execute(Query query)
method in the Session
class. Iterate over the ResultSet
using an enhanced for
loop to output the columns.
ResultSet results = session.execute(boundStmt); for (Row row : results) { System.out.println("Journal: " + row.getString ("journal")); System.out.println("Publisher: " + row.getString ("publisher")); System.out.println("Edition: " + row.getString ("edition")); System.out.println("Title: " + row.getString("title")); System.out.println("Author: " + row.getString("author")); System.out.println(" "); System.out.println(" "); }
The result of running a query with a prepared statement is shown in the Eclipse IDE in Figure 3.15.
UPDATE
STATEMENTThe UPDATE
statement is used to update the columns in one or more rows based on a relation specified in the WHERE
clause. CQL 3 has added a provision to run the UPDATE
conditionally based on the condition in the IF
clause. Run the following UPDATE
statement to update the edition and author columns in the catalog1
table based on the condition in the IF
clause:
session.execute("UPDATE datastax.catalog SET edition = '11/12 2013', author = 'Kelley, David A.' WHERE catalog_id = 'catalog1' AND journal='Oracle Magazine' IF edition='November-December 2013'");
Next, run a SELECT
statement to output the modified columns:
ResultSet results = session.execute("SELECT catalog_id, journal, publisher, edition,title,author FROM datastax.catalog WHERE catalog_id='catalog1'"); for (Row row : results) { System.out.println("Journal: " + row.getString ("journal")); System.out.println("Publisher: " + row.getString ("publisher")); System.out.println("Edition: " + row.getString ("edition")); System.out.println("Title: " + row.getString("title")); System.out.println("Author: " + row.getString("author")); System.out.println(" "); System.out.println(" "); }
The catalog1
row column values after the update are shown in Eclipse IDE in Figure 3.16.
Because the primary key is a compound primary key, all the component columns in the primary key must be specified in the WHERE
clause. For example, if only the catalog-id
column is specified in the WHERE
clause, the following exception is generated:
Exception in thread "main" com.datastax.driver.core.exceptions. InvalidQueryException: Missing mandatory PRIMARY KEY part journal
DELETE
STATEMENTThe DELETE
statement is used to delete some selected columns from table row(s) or all the columns from table row(s). With a compound primary key, using the DELETE
statement is somewhat different than if using a single column primary key. The partition key may be used for the row specification in the WHERE
clause to delete the entire row. For example, the following deletes the catalog1
row:
private static void delete() { session.execute("DELETE from datastax.catalog WHERE catalog_id='catalog1'"); }
A SELECT
query after the deletion outputs only the catalog2
and catalog3
rows:
Catalog Id: catalog2 Journal: Oracle Magazine Publisher: Oracle Publishing Edition: November-December 2013 Title: Quintessential and Collaborative Author: Tom Haunert Catalog Id: catalog3 Journal: Oracle Magazine Publisher: null Edition: null Title: null Author: null
Although the partition key may be used alone to identify a row in the WHERE
clause, the other columns may not be used individually. To demonstrate, specify only the primary key component column journal
in the WHERE
clause:
session.execute("DELETE from datastax.catalog WHERE journal='Oracle Magazine'");
This generates the following exception:
Caused by: com.datastax.driver.core.exceptions.InvalidQueryException: Missing mandatory PRIMARY KEY part catalog_id
The journal
column may be specified in the WHERE
clause in addition to the partition key catalog_id
:
session.execute("DELETE from datastax.catalog WHERE catalog_id='catalog1' AND journal='Oracle Magazine'");
Individual columns to be deleted may be specified in the DELETE
statement, but a primary key component column cannot be deleted with column specification. To demonstrate, include the journal
column to delete using the following query:
session.execute("DELETE journal, publisher from datastax.catalog WHERE catalog_id='catalog2'");
This generates the following exception:
Caused by: com.datastax.driver.core.exceptions.InvalidQueryException: Invalid identifier journal for deletion (should not be a PRIMARY KEY part)
If columns are to be deleted selectively, all the primary key component columns must be specified in the WHERE
clause to identify the row. To demonstrate, run the following query to delete the publisher
and edition
columns from the catalog
table, but don’t specify the journal
column in the WHERE
clause:
session.execute("DELETE publisher, edition from datastax.catalog WHERE catalog_id='catalog2'");
This generates the following exception:
Caused by: com.datastax.driver.core.exceptions.InvalidQueryException: Missing mandatory PRIMARY KEY part journal since edition specified
To delete columns selectively, you must specify all the primary key component columns in the WHERE
clause:
session.execute("DELETE publisher, edition from datastax.catalog WHERE catalog_id='catalog2' AND journal='Oracle Magazine'");
If a SELECT
query is run after the deletion, null
is output for the deleted columns publisher
and edition
for the catalog2
row. (See Figure 3.17.)
If some of the column values have been deleted, a subsequent INSERT
with all the columns specified and with an IF NOT EXISTS
condition does not add the new row values, even though some of the column values have been deleted. For example, if, after the preceding deletion of two columns, you attempt to run the INSERT
statement, the INSERT
statement is not run and the publisher
and edition
column values stay null
.
session.execute("INSERT INTO datastax.catalog (catalog_id, journal, publisher, edition,title,author) VALUES ('catalog2','Oracle Magazine', 'Oracle Publishing', 'November-December 2013', 'Quintessential and Collaborative','Tom Haunert') IF NOT EXISTS");
BATCH
STATEMENTThe BATCH
statement is used to run a batch or group of INSERT
, UPDATE
, and DELETE
statements. Add a batch()
method to the CQLQuery
class and invoke the method from the main
method. To demonstrate the BATCH
statement, create a table catalog4
.
session.execute("CREATE TABLE IF NOT EXISTS datastax.catalog4 (catalog_id text, journal text,publisher text, edition text,title text,author text,PRIMARY KEY (journal, catalog_id, publisher))");
Run a BATCH
statement to add three rows of data to the catalog4
table.
session.execute("BEGIN BATCH INSERT INTO datastax.catalog4 (catalog_id, journal, publisher, edition,title,author) VALUES ('catalog1','Oracle Magazine', 'Oracle Publishing', 'November-December 2013', 'Quintessential and Collaborative','Tom Haunert') INSERT INTO datastax.catalog4 (catalog_id, journal, publisher, edition,title,author) VALUES ('catalog2','Oracle Magazine', 'Oracle Publishing', 'November-December 2013', '','') INSERT INTO datastax.catalog4 (catalog_id, journal, publisher, edition,title,author) VALUES ('catalog3','Oracle Magazine', 'Oracle Publishing', 'November-December 2013', '','') APPLY BATCH");
Run a SELECT
query after the BATCH
statement. The following rows are output:
Catalog Id: catalog1 Journal: Oracle Magazine Publisher: Oracle Publishing Edition: November-December 2013 Title: Quintessential and Collaborative Author: Tom Haunert Catalog Id: catalog2 Journal: Oracle Magazine Publisher: Oracle Publishing Edition: November-December 2013 Title: Author: Catalog Id: catalog3 Journal: Oracle Magazine Publisher: Oracle Publishing Edition: November-December 2013 Title: Author:
The IF NOT EXISTS
condition, which may be used with individual INSERT
, UPDATE
, and DELETE
statements, cannot be used with the same statements in a BATCH
statement, either applied to individual statements or the batch. To demonstrate, run the following BATCH
statement:
session.execute("BEGIN BATCH INSERT INTO datastax.catalog (catalog_id, journal, publisher, edition,title,author) VALUES ('catalog2','Oracle Magazine', 'Oracle Publishing', 'November-December 2013', 'Quintessential and Collaborative','Tom Haunert') IF NOT EXISTS INSERT INTO datastax.catalog (catalog_id, journal, publisher, edition,title,author) VALUES ('catalog3','Oracle Magazine', 'Oracle Publishing', 'November-December 2013', '','') IF NOT EXISTS INSERT INTO datastax.catalog (catalog_id, journal, publisher, edition,title,author) VALUES ('catalog4','Oracle Magazine', 'Oracle Publishing', 'November-December 2013', '','') IF NOT EXISTS APPLY BATCH");
This generates the following exception:
Caused by: com.datastax.driver.core.exceptions.InvalidQueryException: Conditional updates are not allowed in batches
CQL 3 has added the provision to drop an index conditionally using the IF EXISTS
clause. First, run the USE
command to select a keyspace. Then drop the titleIndex
conditionally as follows:
private static void dropIndex() { session.execute("USE datastax"); session.execute("DROP INDEX IF EXISTS titleIndex"); }
CQL 3 has added the provision to drop a table conditionally. For example, drop the catalog
table in the datastax keyspace
using the IF EXISTS
clause as follows:
private static void dropTable() { session.execute("DROP TABLE IF EXISTS datastax.catalog"); }
Dropping a keyspace may also be done conditionally using the IF EXISTS
clause. For example, drop the datastax
keyspace as follows:
private static void dropKeyspace() { session.execute("DROP KEYSPACE IF EXISTS datastax"); }
The Cassandra cluster connection may be closed using the Cluster.close()
method in the closeConnection()
method.
The CQLClient application used in this chapter appears in Listing 3.2. Sections of the code that demonstrate different aspects or usages of an API have been commented out and may be de-commented for testing.
Listing 3.2 The CQLClient Application
package datastax; import java.util.concurrent.TimeUnit; import java.util.concurrent.TimeoutException; import com.datastax.driver.core.BoundStatement; import com.datastax.driver.core.Cluster; import com.datastax.driver.core.Host; import com.datastax.driver.core.Metadata; import com.datastax.driver.core.PreparedStatement; import com.datastax.driver.core.ResultSet; import com.datastax.driver.core.ResultSetFuture; import com.datastax.driver.core.Row; import com.datastax.driver.core.Session; import com.google.common.util.concurrent.AbstractFuture; public class CQLClient { private static Cluster cluster; private static Session session; public static void main(String[] args) { connection(); createKeyspace(); createTable(); insert(); //select(); //dropIndex(); //createIndex(); //selectFilter(); //asyncQuery(); //preparedStmtQuery(); //update(); //delete(); batch(); //dropTable(); //dropKeyspace(); //closeConnection(); } private static void connection() { cluster = Cluster.builder().addContactPoint("127.0.0.1").build(); Metadata metadata = cluster.getMetadata(); System.out.printf("Connected to cluster: %s ", metadata.getClusterName()); for (Host host : metadata.getAllHosts()) { System.out.printf("Datacenter: %s; Host: %s; Rack: %s ", host.getDatacenter(), host.getAddress(), host.getRack()); } session = cluster.connect(); } private static void createKeyspace() { session.execute("CREATE KEYSPACE IF NOT EXISTS datastax WITH replication " + "= {'class':'SimpleStrategy', 'replication_factor':1};"); } private static void createTable() { session.execute("CREATE TABLE IF NOT EXISTS datastax.catalog (catalog_id text, journal text,publisher text, edition text,title text,author text,PRIMARY KEY (catalog_id, journal))"); session.execute("CREATE TABLE IF NOT EXISTS datastax.catalog2 (catalog_id text, journal text,publisher text, edition text,title text,author text,PRIMARY KEY (journal, catalog_id))"); session.execute("CREATE TABLE IF NOT EXISTS datastax.catalog3 (catalog_id text, journal text,publisher text, edition text,title text,author text,PRIMARY KEY (journal, catalog_id, publisher))"); } private static void insert() { session.execute("INSERT INTO datastax.catalog (catalog_id, journal, publisher, edition,title,author) VALUES ('catalog1','Oracle Magazine', 'Oracle Publishing', 'November-December 2013', 'Engineering as a Service','David A. Kelly') IF NOT EXISTS"); session.execute("INSERT INTO datastax.catalog (catalog_id, journal, publisher, edition,title,author) VALUES ('catalog2','Oracle Magazine', 'Oracle Publishing', 'November-December 2013', 'Quintessential and Collaborative','Tom Haunert') IF NOT EXISTS"); session.execute("INSERT INTO datastax.catalog (catalog_id, journal, publisher) VALUES ('catalog3', 'Oracle Magazine','Oracle Publishing') IF NOT EXISTS"); session.execute("INSERT INTO datastax.catalog2 (catalog_id, journal, publisher, edition,title,author) VALUES ('catalog1','Oracle Magazine', 'Oracle Publishing', 'November-December 2013', 'Engineering as a Service','David A. Kelly') IF NOT EXISTS"); session.execute("INSERT INTO datastax.catalog2 (catalog_id, journal, publisher, edition,title,author) VALUES ('catalog2','Oracle Magazine', 'Oracle Publishing', 'November-December 2013', 'Quintessential and Collaborative','Tom Haunert') IF NOT EXISTS"); session.execute("INSERT INTO datastax.catalog2 (catalog_id, journal, publisher) VALUES ('catalog3', 'Oracle Magazine','Oracle Publishing') IF NOT EXISTS"); session.execute("INSERT INTO datastax.catalog3 (catalog_id, journal, publisher, edition,title,author) VALUES ('catalog1','Oracle Magazine', 'Oracle Publishing', 'November-December 2013', 'Engineering as a Service','David A. Kelly') IF NOT EXISTS"); session.execute("INSERT INTO datastax.catalog3 (catalog_id, journal, publisher, edition,title,author) VALUES ('catalog2','Oracle Magazine', 'Oracle Publishing', 'November-December 2013', 'Quintessential and Collaborative','Tom Haunert') IF NOT EXISTS"); session.execute("INSERT INTO datastax.catalog3 (catalog_id, journal, publisher) VALUES ('catalog3', 'Oracle Magazine','Oracle Publishing') IF NOT EXISTS"); } private static void select() { //ResultSet results = //session.execute("select * from datastax.catalog"); //ResultSet results = //session.execute("select * from datastax.catalog2 WHERE journal='Oracle Magazine' ORDER BY catalog_id DESC"); //ResultSet results = //session.execute("select * from datastax.catalog3 WHERE journal='Oracle Magazine' ORDER BY publisher"); //generates error ResultSet results = session .execute("select * from datastax.catalog3 WHERE journal='Oracle Magazine' ORDER BY catalog_id"); for (Row row : results) { System.out.println("Catalog Id: " + row.getString("catalog_id")); System.out.println("Journal: " + row.getString("journal")); System.out.println("Publisher: " + row.getString("publisher")); System.out.println("Edition: " + row.getString("edition")); System.out.println("Title: " + row.getString("title")); System.out.println("Author: " + row.getString("author")); System.out.println(" "); System.out.println(" "); } } private static void createIndex() { session.execute("CREATE INDEX titleIndex ON datastax.catalog (title)"); } private static void selectFilter() { /* * ResultSet results = session .execute( * "SELECT catalog_id, journal, publisher, edition,title,author FROM datastax.catalog2 WHERE journal='Oracle Magazine' AND catalog_id > 'catalog1'" * ); for (Row row : results) { System.out.println("Catalog Id: " + * row.getString("catalog_id")); System.out.println("Journal: " + * row.getString("journal")); System.out.println("Publisher: " + * row.getString("publisher")); System.out.println("Edition: " + * row.getString("edition")); System.out.println("Title: " + * row.getString("title")); System.out.println("Author: " + * row.getString("author")); System.out.println(" "); * System.out.println(" "); } */ /* * ResultSet results = session .execute( * "SELECT catalog_id, journal, publisher, edition,title,author FROM datastax.catalog WHERE catalog_id > 'catalog1'" * ); for (Row row : results) { System.out.println("Catalog Id: " + * row.getString("catalog_id")); System.out.println("Journal: " + * row.getString("journal")); System.out.println("Publisher: " + * row.getString("publisher")); System.out.println("Edition: " + * row.getString("edition")); System.out.println("Title: " + * row.getString("title")); System.out.println("Author: " + * row.getString("author")); System.out.println(" "); * System.out.println(" "); } */ /* * ResultSet results = session .execute( * "SELECT catalog_id, journal, publisher, edition,title,author FROM datastax.catalog2 WHERE journal='Oracle Magazine' AND catalog_id >= 'catalog1'" * ); for (Row row : results) { System.out.println("Catalog Id: " + * row.getString("catalog_id")); System.out.println("Journal: " + * row.getString("journal")); System.out.println("Publisher: " + * row.getString("publisher")); System.out.println("Edition: " + * row.getString("edition")); System.out.println("Title: " + * row.getString("title")); System.out.println("Author: " + * row.getString("author")); System.out.println(" "); * System.out.println(" "); } */ /* * ResultSet results = session .execute( * "SELECT catalog_id, journal, publisher, edition,title,author FROM datastax.catalog WHERE title='Engineering as a Service'" * ); for (Row row : results) { System.out.println("Journal: " + * row.getString("journal")); System.out.println("Publisher: " + * row.getString("publisher")); System.out.println("Edition: " + * row.getString("edition")); System.out.println("Title: " + * row.getString("title")); System.out.println("Author: " + * row.getString("author")); System.out.println(" "); * System.out.println(" "); * * } */ /* * ResultSet results = session .execute( * "SELECT catalog_id, journal, publisher, edition,title,author FROM datastax.catalog WHERE catalog_id='catalog2'" * ); */ /* * ResultSet results = session .execute( * "SELECT catalog_id, journal, publisher, edition,title,author FROM datastax.catalog WHERE journal='Oracle Magazine' ALLOW FILTERING" * ); */ /* * ResultSet results = session .execute( * "SELECT catalog_id, journal, publisher, edition,title,author FROM datastax.catalog WHERE journal='Oracle Magazine' AND catalog_id='catalog2'" * ); for (Row row : results) { System.out.println("Journal: " + * row.getString("journal")); System.out.println("Publisher: " + * row.getString("publisher")); System.out.println("Edition: " + * row.getString("edition")); System.out.println("Title: " + * row.getString("title")); System.out.println("Author: " + * row.getString("author")); System.out.println(" "); * System.out.println(" "); * * } */ /* * ResultSet results = session .execute( * "SELECT catalog_id, journal, publisher, edition,title,author FROM datastax.catalog WHERE catalog_id IN ('catalog2', 'catalog3')" * ); */ /* * ResultSet results = session .execute( * "SELECT catalog_id, journal, publisher, edition,title,author FROM datastax.catalog WHERE title IN ('Quintessential and Collaborative', 'Engineering as a Service')" * ); */ /* * for (Row row : results) { System.out.println("Journal: " + * row.getString("journal")); System.out.println("Publisher: " + * row.getString("publisher")); System.out.println("Edition: " + * row.getString("edition")); System.out.println("Title: " + * row.getString("title")); System.out.println("Author: " + * row.getString("author")); System.out.println(" "); * System.out.println(" "); * } */ } private static void asyncQuery() { ResultSetFuture resultsFuture = session .executeAsync("Select * from datastax.catalog"); try { ResultSet results = resultsFuture.getUninterruptibly(1000000, TimeUnit.MILLISECONDS); for (Row row : results) { System.out.println("Journal: " + row.getString("journal")); System.out.println("Publisher: " + row.getString("publisher")); System.out.println("Edition: " + row.getString("edition")); System.out.println("Title: " + row.getString("title")); System.out.println("Author: " + row.getString("author")); System.out.println(" "); System.out.println(" "); } } catch (TimeoutException e) { resultsFuture.cancel(true); System.out.println(e); } } private static void preparedStmtQuery() { PreparedStatement stmt = session .prepare("SELECT catalog_id, journal, publisher, edition,title,author FROM datastax.catalog WHERE title=?"); BoundStatement boundStmt = new BoundStatement(stmt); boundStmt.bind("Engineering as a Service"); ResultSet results = session.execute(boundStmt); for (Row row : results) { System.out.println("Journal: " + row.getString("journal")); System.out.println("Publisher: " + row.getString("publisher")); System.out.println("Edition: " + row.getString("edition")); System.out.println("Title: " + row.getString("title")); System.out.println("Author: " + row.getString("author")); System.out.println(" "); System.out.println(" "); } } private static void update() { session.execute("UPDATE datastax.catalog SET edition = '11/12 2013', author = 'Kelley, David A.' WHERE catalog_id = 'catalog1' AND journal='Oracle Magazine' IF edition='November-December 2013'"); ResultSet results = session .execute("SELECT catalog_id, journal, publisher, edition,title,author FROM datastax.catalog WHERE catalog_id='catalog1'"); for (Row row : results) { System.out.println("Journal: " + row.getString("journal")); System.out.println("Publisher: " + row.getString("publisher")); System.out.println("Edition: " + row.getString("edition")); System.out.println("Title: " + row.getString("title")); System.out.println("Author: " + row.getString("author")); System.out.println(" "); System.out.println(" "); } } private static void delete() { //session.execute("DELETE journal, publisher from datastax.catalog WHERE catalog_id='catalog2'");//generates //error //session.execute("DELETE from datastax.catalog WHERE catalog_id='catalog1'"); ////equivalent //session.execute("DELETE from datastax.catalog WHERE journal='Oracle Magazine'");//generates //error /* * session.execute( * "DELETE from datastax.catalog WHERE catalog_id='catalog1' AND journal='Oracle Magazine'" * );//equivalent ResultSet results = * session.execute("select * from datastax.catalog"); for (Row row : * results) { System.out.println("Catalog Id: " + * row.getString("catalog_id")); System.out.println("Journal: " + * row.getString("journal")); System.out.println("Publisher: " + * row.getString("publisher")); System.out.println("Edition: " + * row.getString("edition")); System.out.println("Title: " + * row.getString("title")); System.out.println("Author: " + * row.getString("author")); System.out.println(" "); * System.out.println(" "); * * } */ /* * Caused by: com.datastax.driver.core.exceptions.InvalidQueryException: * Missing mandatory PRIMARY KEY part journal since publisher specified */ //session.execute("DELETE publisher, edition from datastax.catalog WHERE catalog_id='catalog2'");//generates //error //session.execute("DELETE publisher, edition from datastax.catalog WHERE catalog_id='catalog1' AND journal='Oracle Magazine'"); //session.execute("DELETE from datastax.catalog WHERE catalog_id='catalog1'"); //session.execute("DELETE from datastax.catalog WHERE journal='Oracle Magazine'");//generates //error //session.execute("DELETE publisher, edition from datastax.catalog WHERE catalog_id='catalog2'"); //session.execute("DELETE publisher, edition from datastax.catalog WHERE catalog_id='catalog2' AND journal='Oracle Magazine'"); ResultSet results = session.execute("select * from datastax.catalog"); for (Row row : results) { System.out.println("Catalog Id: " + row.getString("catalog_id")); System.out.println("Journal: " + row.getString("journal")); System.out.println("Publisher: " + row.getString("publisher")); System.out.println("Edition: " + row.getString("edition")); System.out.println("Title: " + row.getString("title")); System.out.println("Author: " + row.getString("author")); System.out.println(" "); System.out.println(" "); } } private static void batch() { // session.execute("BEGIN BATCH INSERT INTO datastax.catalog (catalog_id, journal, publisher, edition,title,author) VALUES ('catalog2','Oracle Magazine', 'Oracle Publishing', 'November-December 2013', 'Quintessential and Collaborative','Tom Haunert') IF NOT EXISTS INSERT INTO datastax.catalog (catalog_id, journal, publisher, edition,title,author) VALUES ('catalog3','Oracle Magazine', 'Oracle Publishing', 'November-December 2013', '','') IF NOT EXISTS INSERT INTO datastax.catalog (catalog_id, journal, publisher, edition,title,author) VALUES ('catalog4','Oracle Magazine', 'Oracle Publishing', 'November-December 2013', '','') IF NOT EXISTS APPLY BATCH"); session.execute("CREATE TABLE IF NOT EXISTS datastax.catalog4 (catalog_id text, journal text,publisher text, edition text,title text,author text,PRIMARY KEY (journal, catalog_id, publisher))"); session.execute("BEGIN BATCH INSERT INTO datastax.catalog4 (catalog_id, journal, publisher, edition,title,author) VALUES ('catalog1','Oracle Magazine', 'Oracle Publishing', 'November-December 2013', 'Quintessential and Collaborative','Tom Haunert') INSERT INTO datastax.catalog4 (catalog_id, journal, publisher, edition,title,author) VALUES ('catalog2','Oracle Magazine', 'Oracle Publishing', 'November-December 2013', '','') INSERT INTO datastax.catalog4 (catalog_id, journal, publisher, edition,title,author) VALUES ('catalog3','Oracle Magazine', 'Oracle Publishing', 'November-December 2013', '','') APPLY BATCH"); ResultSet results = session.execute("select * from datastax.catalog4"); for (Row row : results) { System.out.println("Catalog Id: " + row.getString("catalog_id")); System.out.println("Journal: " + row.getString("journal")); System.out.println("Publisher: " + row.getString("publisher")); System.out.println("Edition: " + row.getString("edition")); System.out.println("Title: " + row.getString("title")); System.out.println("Author: " + row.getString("author")); System.out.println(" "); System.out.println(" "); } } private static void dropIndex() { session.execute("USE datastax"); session.execute("DROP INDEX IF EXISTS titleIndex"); } private static void dropTable() { session.execute("DROP TABLE IF EXISTS datastax.catalog"); } private static void dropKeyspace() { session.execute("DROP KEYSPACE IF EXISTS datastax"); } private static void closeConnection() { cluster.close(); } }
In this chapter, you used the DataStax Java driver to access the Cassandra server from a Java application developed in the Eclipse IDE. You used CQL 3 statements to create a keyspace, create a table, insert rows in the table, create an index, select rows and columns from the table, update table rows, delete table rows and columns, run a batch of statements, drop an index, drop a table, and drop a keyspace. In the next chapter, you will learn how to use Apache Cassandra with PHP, an open source, object-oriented, server-side language.