CHAPTER 3
USING CASSANDRA WITH DATASTAX JAVA DRIVER
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.
OVERVIEW OF DATASTAX JAVA DRIVER
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.
SETTING THE ENVIRONMENT
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
CREATING A JAVA PROJECT
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.
CREATING A CONNECTION
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)
OVERVIEW OF THE Session CLASS
The 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.
CREATING 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.
CREATING A TABLE
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.
RUNNING THE INSERT STATEMENT
Next, 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.
RUNNING A SELECT STATEMENT
Next, 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.
CREATING AN INDEX
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
SELECTING WITH SELECT AND A WHERE FILTER
You 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)
RUNNING AN Async QUERY
As 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.
RUNNING A PreparedStatement QUERY
DataStax 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.
RUNNING THE UPDATE STATEMENT
The 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
RUNNING THE DELETE STATEMENT
The 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");
RUNNING THE BATCH STATEMENT
The 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
DROPPING AN INDEX
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");
}
DROPPING A TABLE
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
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
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();
}
}
SUMMARY
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.