Hey guys!! Did you miss me? I’m back again, but this time with a bit of a surprise. Remember my old influenza project? I was just thinking about it the other day, and I thought gosh, I really miss that. Since becoming a junior evangelist at Datastax, been so busy that I’ve hardly had time for the simpler things in life, like my influenza tracker project. And with me going to Datastax Bootcamp next week (*excitement!*) in Santa Clara, I figured it would be be a good time to pick this up again. If you’re new here, look back a few blog posts and you’ll see how I got my data in using a custom loader I built.Today though, I’m going to tell you the access layer that I’ve been building. I’d like to remind everyone first that I’m using Eclipse Java EE IDE Version Kepler Service Release 1 with a Maven and Git plugin, as well as Tomcat web server. But if you are using a different IDE the situation should be similar.
Go to my GitHub account and download the source code for my influenza project, you’ll see that it composed of 4 servlets. One is the home servlet, and there is one for each type of query that can be that; that is Strain by Year, State by Date, and State by Subtype, for a total of three servlets. These three servlets do a GET request to the database to retrieve the queried for. You need to download the Datastax Java Client Driver to get this project working. You can add the following XML code to the Maven POM file and the driver automatically downloads. This is applicable to command line or an IDE like Eclipse
Cloning the project from Github
I used Eclipse with the Git plugin (it would be similar situation for other IDEs as well). It wasn’t an entirely straight forward, I had to import the project from the git folder on my computer as a new java project and a new dynamic web project after I had pulled it down from GitHub. I was a bit of lengthy process. The initial cloning wasn’t too bad, but it took me a while to figure out how to get the project working in package explorer.
1. In the Package Explorer window, right click and select Import.
2. Select the Git folder, and then click on Projects from Git.
3. Under Select the Repository Source, select Clone URI and type the URI. You can also copy and paste the credentials from GitHub
Make sure you have http selected as the protocol. Click Next
4. Keep Master branch ticked and click Next
5. Make whatever changes you would like under Local Destination and click Next
6. Wait for the project to be cloned, which will most likely take a couple of minutes.Then click Finish. And at this point, you would import a dynamic web project.
From the command line (Make sure you have git installed first):
$ git clone https://github.com/beccam/influenza-project.git
Connect to your cluster
The Influenza StatebyDate, Influenza StatebySubtype, and Influenza StrainbyYear servlets all connect to the Cassandra instance and creates a cluster object as part of their doGet method.
The connect method adds a contact point (node IP address) using the Cluster.Buildauxillary class and builds a cluster instance. If your Cassandra instance was sitting on localhost:
Cluster cluster = Cluster.builder().addContactPoint("localhost").build();
Session session = cluster.connect();
Do this at the end of your servlet or you’ll end up leaving open connections.
// Be sure to close the instance as to not leave open connections
Running the program
Make sure you have you’re instance/cluster is running as well as your web server. I use the embedded tomcat server inside eclipse. In the Eclipse console, you should see the web server starting up and connecting to the cluster.
Using prepared and Binding Statements
In my code, I use a string to query the database in 2/3 cases. This works, but it may be not be very efficient and it is the least safe way to do things because of potential CQL injection. This would be from the StatebySubtype.java servlet.
String subtype = request.getParameter("Subtype");
PrintWriter out = response.getWriter();
String state = request.getParameter("state");
String queryText = "SELECT collection_date, strain_id FROM influenza.state_subtype WHERE subtype = '"+ subtype + "'" + " AND state = '" + state + "'";
ResultSet results = session.execute(queryText);
A better way to do it, or should I say a more ‘correct’ way of doing it, would be using the prepared statements and then bind variables to them and execute the bound statement. Prepared statements only need to be parsed once, and you can use them to bind new values to them each time before execution. But I give examples of both ways in the code.
// Create the CQL query/command you want to use as a prepared statement
PreparedStatement statement_state_subtype = session.prepare(
"SELECT collection_date, strain_id FROM influenza.state_subtype"
+ " WHERE subtype = ?"
+ "AND state = ?;");
// Create a bound statement object
BoundStatement boundStatement_state_subtype = new BoundStatement(
//Bind the given parameters to the bound statement
//Execute the bound statement within the session
ResultSet results = session.execute(boundStatement_state_subtype);
Working with the driver so far has been fun, and I’m super excited to see what I can do with my pet project, still a work in the making! If you are interested in trying the driver with your own project, there’s of great info in the Datastax Apache Cassandra Java Driver 2.0 documentation that will help you get started, much more in depth detail than what I go through in this blog post. I’m going to continue working on the access layer, and eventually try to get together a decent user interface. If you’re following along and you have any questions, shoot them my way. Or if anyone knows of any cool tools that might enhance my project, feel free to send some recommendations my way! I’m always eager to learn new things.