Coding with Java Part 2

In a previous lesson, we explained how to create a sample Java application that executes a few basic CQL statements with a ScyllaDB cluster using the Java driver. After the code was deployed, we found that mutants murdered several citizens because the code was too static and not scalable. Changes must be made for Division 3 to protect people better by building highly-scalable and performing applications to monitor mutants. In this lesson, we will explore how we can optimize the existing Java code with prepared statements.

What Are Prepared Statements?

Prepared statements will enable developers at Division 3 to optimize our applications, so they run more efficiently. Most or all of the Cassandra compatible drivers support prepared statements. With that in mind, what you learn here can benefit you regardless of the programming language used. A prepared statement is a query that is parsed by ScyllaDB and then saved for later use. One of the valuable benefits is that you can continue to reuse that query and modify variables in the query to match variables such as names, addresses, and locations. Let’s dive a little deeper to see how it works.

When asked to prepare a CQL statement, a client library will send a CQL statement to ScyllaDB. ScyllaDB will then create a unique fingerprint for that CQL statement by MD5 hashing the CQL statement. ScyllaDB will use this hash to check its query cache to see if it has already cached that CQL statement. If ScyllaDB had seen that CQL statement, it will send back a reference to that cached CQL statement. If ScyllaDB does not have that unique query hash in its cache, it will then proceed to parse the query and insert the parsed output into its cache.

The client will then be able to send an execute request specifying the statement id and providing the (bound) variables, as we will see next.

Now, let’s go over a Java application which uses prepared statements.

Setting Up the ScyllaDB Cluster

If you ran the lesson Coding with Java Part 1, you can skip directly to the next section, “The Java Code”. Otherwise, follow this procedure to remove previous clusters and set up a new cluster.

Next, create the keyspace catalog and the table mutant_data

docker exec -it scylla-node1 cqlsh

CREATE KEYSPACE catalog WITH REPLICATION = { 'class' : 'NetworkTopologyStrategy','DC1' : 3};

use catalog;

CREATE TABLE mutant_data (
   first_name text,
   last_name text,
   address text,
   picture_location text,
   PRIMARY KEY((first_name, last_name)));

Now let’s add a few mutants to the catalog with the following statements:

insert into mutant_data ("first_name","last_name","address","picture_location") VALUES ('Bob','Loblaw','1313 Mockingbird Lane', 'http://www.facebook.com/bobloblaw');
insert into mutant_data ("first_name","last_name","address","picture_location") VALUES ('Bob','Zemuda','1202 Coffman Lane', 'http://www.facebook.com/bzemuda');
insert into mutant_data ("first_name","last_name","address","picture_location") VALUES ('Jim','Jeffries','1211 Hollywood Lane', 'http://www.facebook.com/jeffries');

The Java Code

We will use the file PreparedStatement_App.java, which you can find in the directory: scylla-code-samples/mms/java/java-app-ps/src/

We will first go over each section of the code before running it.

To get started, in addition to the libraries we used in the previous lesson, we will also add two more libraries to our application:

The PreparedStatement and BoundStatement libraries provide the functions to create prepared statements. Moving on, we can add two prepared statements to our application:

The first prepared statement is named insert. This statement will add data programmatically for first_name, last_name, address, and picture_location based on input from the application. The second prepared statement is named delete and will delete entries in the table, based on input for first_name and last_name. We will reuse these statements later to add and delete data in the mutant_data table.

The insertQuery function is defined as follows:

This function will take input for first_name, last_name, address, and picture_location and then bind to our prepared statement named insert and execute the query. By using prepared statements, we can reuse these functions over and over to add data to the catalog table.

The deleteQuery function is defined as follows:

In this function, we will take first_name and last_name inputs and then bind and execute the delete prepared statement. Using this prepared statement, we can reuse these functions over and over to delete data from the catalog table.

Finally, we need to define the main function as follows to pass input to the functions when the application starts:

First, the contents of the catalog table will be displayed, followed by calling the insertQuery function twice, to add two additional mutants. After each insert is done, the contents of the table will be displayed. Finally, each user that was added is deleted, and the contents of the table are shown after each delete.

After understanding the code, let’s run the sample application in the Docker container we previously set up.

Building the Java Example

If you previously built the java  Docker you can skip directly to “Running the Java Example”.

Otherwise, to build the application in Docker, change into the java subdirectory in scylla-code-samples. If you are still in cqlsh exit first.

exit

cd scylla-code-samples/mms/java

Now we can build and run the container:

docker build -t java-app .

docker run -d --net=mms_web --name some-java-app java-app

To connect to the shell of the container, run the following command:

docker exec -it some-java-app sh

Running the Java Example

Finally, the sample Java application can be run:

cd java-app-ps/target

java -jar PreparedStatement_App-jar-with-dependencies.jar

The output of the application will be:

Keep in mind that the output might be different if you did not run the previous lesson as the content of the table would not be the same.

Conclusion

In this lesson, we explained what prepared statements are and how they can enable developers at Division 3 to run their applications more efficiently. We also learned how a Java application could take advantage of prepared statements. Division 3 recommends that you keep experimenting with prepared statements and continue to make your applications more efficient.

Stay safe out there!