11 min to complete
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 optimizing 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 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 cached that CQL statement. If ScyllaDB had seen that CQL statement, it would send back a reference to that cached CQL statement. If ScyllaDB does not have that unique query hash in its cache, it will 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 that uses prepared statements. After that, you will be able to run the lab.
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. Then, the insertQuery function will be called twice to add two additional mutants. After each insert, the table’s contents will be displayed. Finally, each user that was added is deleted, and the table’s contents are shown after each delete.
After understanding the code, let’s run the sample application using the lab virtual environment.
Coding with Python
With the coding part done, let’s set up the ScyllaDB Cluster and then run the sample application in the lab
Conclusion
In this lesson, we explained prepared statements and how they can enable Division 3 developers to run their applications more efficiently. We also learned how a Java application could take advantage of prepared statements. Division 3 recommends that you experiment with prepared statements and make your applications more efficient.
Stay safe out there!
