|IMPORTANT: Since the first publication of the Mutant Monitoring System we have made a number of updates to the concepts and code presented in the blog series. You can find the latest version now in ScyllaDB University. Click here to be directed to the new version.
This is part 12 of a series of blog posts that provides a story arc for ScyllaDB Training.
In the previous post, we explained how to create a sample Java application that executes a few basic CQL statements with a ScyllaDB cluster using the Datastax Cassandra driver. After the code was deployed, we found that several citizens were murdered by mutants because the code was too static and not scalable. Changes must be made in order for Division 3 to protect people better by building highly-scalable and performing applications to monitor mutants. In this post, 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 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 basically a query that is parsed by ScyllaDB and then saved for later use. One of the useful 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.
INSERT INTO tb (key, val) VALUES (?,?)
The client will then be able to send an execute request specifying the statement id and providing the (bound) variables.
For more information on prepared statements, please click here. Now let’s go over how to change our existing Java application to support prepared statements.
Changing the Existing Java Application
To get started, we will first need to add a few 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 input gathered for first_name and last_name. We will reuse these statements later to add and delete data in the mutant_data table.
The first section of the application to replace is the
insertQuery function 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 second section of the application to replace is the
deleteQuery function 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 modify 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 functions 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 is shown after each delete.
With the coding part done, let’s bring up the ScyllaDB Cluster and then run the sample application in Docker.
Starting the ScyllaDB Cluster
The ScyllaDB Cluster should be up and running with the data imported from the previous blog posts.
The MMS Git repository has been updated to provide the ability to automatically import the keyspaces and data. If you have the Git repository already cloned, you can simply do a “git pull” in the scylla-code-samples directory.
git clone https://github.com/scylladb/scylla-code-samples.git
Modify docker-compose.yml and add the following line under the environment: section of scylla-node1:
Now we can build and run the containers:
docker-compose up -d
After roughly 60 seconds, the existing MMS data will be automatically imported. When the cluster is up and running, we can run our application code.
Building and Running the Java Example
To build the application in Docker, change into the java subdirectory in scylla-code-samples:
Now we can build and run the container:
docker build -t java .
docker run -d --net=mms_web --name java java
To connect to the shell of the container, run the following command:
docker exec -it java sh
Finally, the sample Java application can be run:
java -jar App.jar
The output of the application will be:
In this post we explained what prepared statements are and how they can enable developers at Division 3 to optimize their applications more efficiently. We also learned how to modify our existing Java application to 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!
- Learn more about ScyllaDB from our product page.
- See what our users are saying about ScyllaDB.
- Download ScyllaDB. Check out our download page to run ScyllaDB on AWS, install it locally in a Virtual Machine, or run it in Docker.