Using PreparedStatement

DSE Version: 6.7

Intro

Exercises

Slides

Resources

In previous units we learned how to insert rows using simple strings and QueryBuilder. In this unit we advance to using PreparedStatement. PreparedStatements bring all kinds of good optimizations to your app, and they are easy to incorporate!

No write up.

Exercise: Use PreparedStatement to Insert Videos

In this exercise, you will:

  • Use PreparedStatement

  • Modify the previous insert video code to use PreparedStatement

In the previous exercise, we took a step forward from using simple strings for inserting rows, to using QueryBuilder. In this exercise we will build on this advancement by using PreparedStatement. PreparedStatement helps the driver and the database optimize statement execution. Further, you can use PreparedStatement to isolate processing that the driver and your database only need to perform once - letting you reuse the result of this processing. The bottom line is that your app and database will have better performance when using PreparedStatement.

Steps

1) Clear out the videos table. Functionally, in this exercise we will do the same thing we did in the previous exercise, but we will do it in a better way. To prepare for this, let's TRUNCATE the videos table so we can start with a clean slate.

Need the command to launch cqlsh? Click here.
Launch cqlsh with the following (remember the password is KVPassword):
cqlsh --username KVUser --cqlshrc /projects/creds/cqlshrc
Want a reminder on how to TRUNCATE the videos table? Click here.
TRUNCATE killrvideo.videos;

2) Update ScratchPad.java. Time for a fresh ScratchPad.java. This version is similar to the previous exercise's version, except we set you up to use PreparedStatements instead. Copy this code over your existing ScratchPad.java file contents (scratchpad/src/main/java/com.datastax.academy.cloud.demo/ScratchPad.java).

package com.datastax.academy.cloud.demo;

import java.io.File;
import java.time.Instant;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;

import com.datastax.oss.driver.api.core.cql.BatchStatement;
import com.datastax.oss.driver.api.core.cql.BoundStatement;
import com.datastax.oss.driver.api.core.cql.DefaultBatchType;
import com.datastax.oss.driver.api.core.cql.PreparedStatement;
import com.datastax.oss.driver.api.core.cql.ResultSet;
import com.datastax.oss.driver.api.core.cql.Row;
import com.datastax.oss.driver.api.core.cql.SimpleStatement;
import com.datastax.oss.driver.api.core.cql.SimpleStatementBuilder;
import com.datastax.dse.driver.api.core.DseSession;
import com.datastax.oss.driver.api.core.DefaultConsistencyLevel;
import com.datastax.oss.driver.api.querybuilder.QueryBuilder;
import com.datastax.oss.driver.api.querybuilder.insert.RegularInsert;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class ScratchPad {
  static final Logger LOGGER = LoggerFactory.getLogger(ScratchPad.class);
  
  public static void addNewVideo(DseSession session, Video video) throws Exception {
    LOGGER.debug("------Start add new video------");

    // Create the template for the statement
    SimpleStatementBuilder insertVideoBuilder = 
      QueryBuilder.insertInto("killrvideo", "videos")
        .value("videoId", QueryBuilder.bindMarker())
        .value("userId", QueryBuilder.bindMarker())
        .value("name", QueryBuilder.bindMarker())
        .value("description", QueryBuilder.bindMarker())
        .value("location", QueryBuilder.bindMarker())
        .value("location_type", QueryBuilder.bindMarker())
        .value("preview_image_location", QueryBuilder.bindMarker())
        .value("tags", QueryBuilder.bindMarker())
        .value("added_date", QueryBuilder.bindMarker())
        .builder();
                       
    //-----------------------------------------------------------------------------                   
    // A) Create a SimpleStatement from your insertVideoBuilder
    //-----------------------------------------------------------------------------

    //-----------------------------------------------------------------------------
    // B) prepare() that SimpleStatement making a PreparedStatement
    //-----------------------------------------------------------------------------

    //-----------------------------------------------------------------------------
    // C) bind() your PreparedStatement calling appropriate setter methods
    //-----------------------------------------------------------------------------
        
    //-----------------------------------------------------------------------------
    // D) execute() your BoundStatement
    //-----------------------------------------------------------------------------

  }

  public static void main(String[] args) {
    LOGGER.info("Starting main()...");
    try {
      SessionManagement.initSession();
      DseSession session = SessionManagement.getSession();
    
      // Here's a Video instance for the Funny Cat video
      Video video = new Video(UUID.randomUUID(), UUID.randomUUID(), "Funny Cat", 
        "Funny Cat is Funny", "5yFfCAaedgA",
         0, "https://img.youtube.com/vi/_u28VT47vmc/hqdefault.jpg", 
         null, Instant.now());
            
      // Here's the test call
      addNewVideo(session, video);
    }
    catch (Exception e) { e.printStackTrace(); }
    finally { SessionManagement.closeSession(); }
    System.exit(0);
  }
}

3) Code like a ninja. We outlined four tasks in the code for you to do here:

A) Create a SimpleStatement from your insertVideoBuilder

B) prepare() that SimpleStatement making a PreparedStatement

C) bind() your PreparedStatement calling appropriate setter methods for all video fields

D) execute() your BoundStatement

Here are the setter methods you will need:

  • setUuid()
  • setString()
  • setInstant() (for added_date)
  • setSet()

And here's a bit more help with setSet() as you must pass the type of objects the set contains:

.setSet("column-name", column-value, String.class);

Don't forget...you got a cheat sheet if needed.

Need more help with a bit of a code outline? Click here.
public static void addNewVideo(DseSession session, Video video) throws Exception {
  LOGGER.debug("------Start add new video------");

  // Create the template for the statement
  SimpleStatementBuilder insertVideoBuilder = 
    QueryBuilder.insertInto("killrvideo", "videos")
      .value("videoId", QueryBuilder.bindMarker())
      .value("userId", QueryBuilder.bindMarker())
      .value("name", QueryBuilder.bindMarker())
      .value("description", QueryBuilder.bindMarker())
      .value("location", QueryBuilder.bindMarker())
      .value("location_type", QueryBuilder.bindMarker())
      .value("preview_image_location", QueryBuilder.bindMarker())
      .value("tags", QueryBuilder.bindMarker())
      .value("added_date", QueryBuilder.bindMarker())
      .builder();
  
  //-----------------------------------------------------------------------------                   
  // A) Create a SimpleStatement from your insertVideoBuilder
  //-----------------------------------------------------------------------------
  SimpleStatement insertVideoStatement = insertVideoBuilder. ...

  //-----------------------------------------------------------------------------
  // B) prepare() that SimpleStatement making a PreparedStatement
  //-----------------------------------------------------------------------------
  PreparedStatement insertVideoStatementPrepared = ...

  //-----------------------------------------------------------------------------
  // C) bind() your PreparedStatement calling appropriate setter methods
  //-----------------------------------------------------------------------------
  BoundStatement insertVideoStatementBound = insertVideoStatementPrepared.bind()
    .setUuid("videoid", video.getVideoid())
    // etc.
        
  //-----------------------------------------------------------------------------
  // D) execute() your BoundStatement
  //-----------------------------------------------------------------------------
  session. ...
  
}
Are you really stuck and just want the solution? Click here.
package com.datastax.academy.cloud.demo;

import java.io.File;
import java.time.Instant;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;

import com.datastax.oss.driver.api.core.cql.BatchStatement;
import com.datastax.oss.driver.api.core.cql.BoundStatement;
import com.datastax.oss.driver.api.core.cql.DefaultBatchType;
import com.datastax.oss.driver.api.core.cql.PreparedStatement;
import com.datastax.oss.driver.api.core.cql.ResultSet;
import com.datastax.oss.driver.api.core.cql.Row;
import com.datastax.oss.driver.api.core.cql.SimpleStatement;
import com.datastax.oss.driver.api.core.cql.SimpleStatementBuilder;
import com.datastax.dse.driver.api.core.DseSession;
import com.datastax.oss.driver.api.core.DefaultConsistencyLevel;
import com.datastax.oss.driver.api.querybuilder.QueryBuilder;
import com.datastax.oss.driver.api.querybuilder.insert.RegularInsert;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class ScratchPad {
  static final Logger LOGGER = LoggerFactory.getLogger(ScratchPad.class);
  
  public static void addNewVideo(DseSession session, Video video) throws Exception {
    LOGGER.debug("------Start add new video------");

    // Create the template for the statement
    SimpleStatementBuilder insertVideoBuilder = 
      QueryBuilder.insertInto("killrvideo", "videos")
        .value("videoId", QueryBuilder.bindMarker())
        .value("userId", QueryBuilder.bindMarker())
        .value("name", QueryBuilder.bindMarker())
        .value("description", QueryBuilder.bindMarker())
        .value("location", QueryBuilder.bindMarker())
        .value("location_type", QueryBuilder.bindMarker())
        .value("preview_image_location", QueryBuilder.bindMarker())
        .value("tags", QueryBuilder.bindMarker())
        .value("added_date", QueryBuilder.bindMarker())
        .builder();
         
    //-----------------------------------------------------------------------------              
    // A) Create a SimpleStatement from your insertVideoBuilder
    SimpleStatement insertVideoStatement = insertVideoBuilder.build();
    //-----------------------------------------------------------------------------

    //-----------------------------------------------------------------------------
    // B) prepare() that SimpleStatement making a PreparedStatement
    //-----------------------------------------------------------------------------
    PreparedStatement insertVideoStatementPrepared = session.prepare(insertVideoStatement);

    //-----------------------------------------------------------------------------
    // C) bind() your PreparedStatement calling appropriate setter methods
    //-----------------------------------------------------------------------------
    BoundStatement insertVideoStatementBound = insertVideoStatementPrepared.bind()
      .setUuid("videoid", video.getVideoid())
      .setUuid("userid", video.getUserid()) 
      .setString("name", video.getName())
      .setString("description", video.getDescription())
      .setString("location", video.getLocation())
      .setInt("location_type", 0)
      .setString("preview_image_location", video.getPreviewImageLocation())
      .setInstant("added_date", Instant.now())
      .setSet("tags", video.getTags(), String.class);
          
    //-----------------------------------------------------------------------------
    // D) execute() your BoundStatement
    //-----------------------------------------------------------------------------
    session.execute(insertVideoStatementBound);
  }

  public static void main(String[] args) {
    LOGGER.info("Starting main()...");
    try {
      SessionManagement.initSession();
      DseSession session = SessionManagement.getSession();
    
      // Here's a Video instance for the Funny Cat video
      Video video = new Video(UUID.randomUUID(), UUID.randomUUID(), "Funny Cat", 
        "Funny Cat is Funny", "5yFfCAaedgA",
         0, "https://img.youtube.com/vi/_u28VT47vmc/hqdefault.jpg", 
         null, Instant.now());
            
      // Here's the test call
      addNewVideo(session, video);
    }
    catch (Exception e) { e.printStackTrace(); }
    finally { SessionManagement.closeSession(); }
    System.exit(0);
  }
}

4) Run the scratchpad project. Again, you will know your code works by looking at the videos table in cqlsh.

Need the CQL to view the contents of the videos table? Click here.
SELECT * FROM killrvideo.videos;

5) Get ready to deploy the code. Before we can move the code into the KillrVideo app, we need to TRUNCATE the videos table... again. Do that now.

Want yet another reminder on how to TRUNCATE the videos table? Click here.
TRUNCATE killrvideo.videos;

6) Deploy the code. You've done this enough to know the drill, but it's our job to state the obvious, so here we go. Copy the code for addNewVideo() from ScratchPad.java to VideoAccess.java in the killrvideo project (killrvideo/src/main/java/killrvideo/dataLayer/VideoAccess.java).

Are you confused or lost? As a last resort, click here to get the VideoAccess.java code.
package killrvideo.dataLayer;

import java.io.File;
import java.time.Instant;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;

import com.datastax.oss.driver.api.core.cql.BatchStatement;
import com.datastax.oss.driver.api.core.cql.BoundStatement;
import com.datastax.oss.driver.api.core.cql.DefaultBatchType;
import com.datastax.oss.driver.api.core.cql.PreparedStatement;
import com.datastax.oss.driver.api.core.cql.ResultSet;
import com.datastax.oss.driver.api.core.cql.Row;
import com.datastax.oss.driver.api.core.cql.SimpleStatement;
import com.datastax.oss.driver.api.core.cql.SimpleStatementBuilder;
import com.datastax.dse.driver.api.core.DseSession;
import com.datastax.oss.driver.api.core.DefaultConsistencyLevel;
import com.datastax.oss.driver.api.querybuilder.QueryBuilder;
import com.datastax.oss.driver.api.querybuilder.insert.RegularInsert;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import killrvideo.entity.LatestVideos;
import killrvideo.entity.Video;

public class VideoAccess {
  private static final Logger LOGGER = LoggerFactory.getLogger(UserAccess.class);
    
  public static void addNewVideo(DseSession session, Video video) throws Exception {
    LOGGER.debug("------Start add new video------");

    // Create the template for the statement
    SimpleStatementBuilder insertVideoBuilder = 
      QueryBuilder.insertInto("killrvideo", "videos")
        .value("videoId", QueryBuilder.bindMarker())
        .value("userId", QueryBuilder.bindMarker())
        .value("name", QueryBuilder.bindMarker())
        .value("description", QueryBuilder.bindMarker())
        .value("location", QueryBuilder.bindMarker())
        .value("location_type", QueryBuilder.bindMarker())
        .value("preview_image_location", QueryBuilder.bindMarker())
        .value("tags", QueryBuilder.bindMarker())
        .value("added_date", QueryBuilder.bindMarker())
        .builder();
                       
    //-----------------------------------------------------------------------------
    // A) Create a SimpleStatement from your insertVideoBuilder
    //-----------------------------------------------------------------------------
    SimpleStatement insertVideoStatement = insertVideoBuilder.build();

    //-----------------------------------------------------------------------------
    // B) prepare() that SimpleStatement making a PreparedStatement
    //-----------------------------------------------------------------------------
    PreparedStatement insertVideoStatementPrepared = session.prepare(insertVideoStatement);

    //-----------------------------------------------------------------------------
    // C) bind() your PreparedStatement calling appropriate setter methods
    //-----------------------------------------------------------------------------
    BoundStatement insertVideoStatementBound = insertVideoStatementPrepared.bind()
      .setUuid("videoid", video.getVideoid())
      .setUuid("userid", video.getUserid()) 
      .setString("name", video.getName())
      .setString("description", video.getDescription())
      .setString("location", video.getLocation())
      .setInt("location_type", 0)
      .setString("preview_image_location", video.getPreviewImageLocation())
      .setInstant("added_date", Instant.now())
      .setSet("tags", video.getTags(), String.class);
          
    //-----------------------------------------------------------------------------
    // D) execute() your BoundStatement
    //-----------------------------------------------------------------------------
    session.execute(insertVideoStatementBound);
  }
  
  public static Video getVideo(DseSession session, UUID videoId) throws Exception {
    LOGGER.debug("------Start get video------");
    return null;  
  }

  public static List<LatestVideos> getLatestVideos(DseSession session) throws Exception {
    LOGGER.debug("------Start get latest videos------");
    return new ArrayList<LatestVideos>();
  } 
  
  public static void init(DseSession session) {
      
  }
}

7) Bounce the KillrVideo app. Stop the KillrVideo service and run it again so that the process uses your new code.

8) Insert a video. Use the KillrVideo app in your other browser tab and go through the steps to add the video again. These are the same steps as the first four steps from the previous exercise. If you can't remember how to add a video, please consult the exercise in the QueryBuilder unit (here's a link).

Don't want to hunt for a YouTube URL? Click here

https://www.youtube.com/watch?v=XyNlqQId-nk

9) Verify the changes worked. Use cqlsh to inspect the videos table. Do you see the row you added? If yes, then great! You now have the PreparedStatement tool in your Cassandra toolbox and you know how to use it with deadly force! Except... We gots somethin' nasty that needs fixin'.

Need the CQL to view the contents of the videos table? Click here.
SELECT * FROM killrvideo.videos;

10) Truncate the videos table. Let's clear out the videos table again before we make another change.

Need the CQL to truncate the videos table? Click here.
TRUNCATE TABLE killrvideo.videos;

11) Repent of your evil coding ways. What we have right now isn't good. We prepare()d the statement in the addNewVideo() method. That means every time this method runs, the database recompiles our INSERT statement. Kinda defeats the purpose of prepare()ing a statement now doesn't it? We want to prepare() once for the entire application lifetime.

Did you notice the small init() method at the bottom of the VideoAccess.java file?

Yeah, we slipped that in there wondering if you would catch it. Foregoing a long discussion here, the website invokes init() once at startup...which means...it is an excellent place to prepare() a statement! See what we did there?

12) Move your prepare() call. Move all of addNewVideo()'s driver code up to and including the prepare() call into the VideoAccess.init() method. You will also have to make your PreparedStatement variable a static class level field. In our solution we called this variable insertVideoStatementPrepared.

Confused? Tired? Or just want the changes made for you? Click here.
package killrvideo.dataLayer;

import java.io.File;
import java.time.Instant;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;

import com.datastax.oss.driver.api.core.cql.BatchStatement;
import com.datastax.oss.driver.api.core.cql.BoundStatement;
import com.datastax.oss.driver.api.core.cql.DefaultBatchType;
import com.datastax.oss.driver.api.core.cql.PreparedStatement;
import com.datastax.oss.driver.api.core.cql.ResultSet;
import com.datastax.oss.driver.api.core.cql.Row;
import com.datastax.oss.driver.api.core.cql.SimpleStatement;
import com.datastax.oss.driver.api.core.cql.SimpleStatementBuilder;
import com.datastax.dse.driver.api.core.DseSession;
import com.datastax.oss.driver.api.core.DefaultConsistencyLevel;
import com.datastax.oss.driver.api.querybuilder.QueryBuilder;
import com.datastax.oss.driver.api.querybuilder.insert.RegularInsert;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import killrvideo.entity.LatestVideos;
import killrvideo.entity.Video;

public class VideoAccess {
  private static final Logger LOGGER = LoggerFactory.getLogger(UserAccess.class);
  
  static PreparedStatement insertVideoStatementPrepared;
    
  public static void addNewVideo(DseSession session, Video video) throws Exception {
    LOGGER.debug("------Start add new video------");

    //-----------------------------------------------------------------------------
    // C) bind() your PreparedStatement calling appropriate setter methods
    //-----------------------------------------------------------------------------
    BoundStatement insertVideoStatementBound = insertVideoStatementPrepared.bind()
      .setUuid("videoid", video.getVideoid())
      .setUuid("userid", video.getUserid()) 
      .setString("name", video.getName())
      .setString("description", video.getDescription())
      .setString("location", video.getLocation())
      .setInt("location_type", 0)
      .setString("preview_image_location", video.getPreviewImageLocation())
      .setInstant("added_date", Instant.now())
      .setSet("tags", video.getTags(), String.class);
          
    //-----------------------------------------------------------------------------
    // D) execute() your BoundStatement
    //-----------------------------------------------------------------------------
    session.execute(insertVideoStatementBound);
  }
  
  public static Video getVideo(DseSession session, UUID videoId) throws Exception {
    LOGGER.debug("------Start get video------");
    return null;  
  }

  public static List getLatestVideos(DseSession session) throws Exception {
    LOGGER.debug("------Start get latest videos------");
    return new ArrayList();
  } 
  
  public static void init(DseSession session) {
    // Create the template for the statement
    SimpleStatementBuilder insertVideoBuilder = 
      QueryBuilder.insertInto("killrvideo", "videos")
        .value("videoId", QueryBuilder.bindMarker())
        .value("userId", QueryBuilder.bindMarker())
        .value("name", QueryBuilder.bindMarker())
        .value("description", QueryBuilder.bindMarker())
        .value("location", QueryBuilder.bindMarker())
        .value("location_type", QueryBuilder.bindMarker())
        .value("preview_image_location", QueryBuilder.bindMarker())
        .value("tags", QueryBuilder.bindMarker())
        .value("added_date", QueryBuilder.bindMarker())
        .builder();
                       
    //-----------------------------------------------------------------------------
    // A) Create a SimpleStatement from your insertVideoBuilder
    //-----------------------------------------------------------------------------
    SimpleStatement insertVideoStatement = insertVideoBuilder.build();

    //-----------------------------------------------------------------------------
    // B) prepare() that SimpleStatement making a PreparedStatement
    //-----------------------------------------------------------------------------
    insertVideoStatementPrepared = session.prepare(insertVideoStatement);
  }
}

13) Bounce your killrvideo server. You know the drill. Bounce your killrvideo server again and add another video using the web UI. Then check the videos table for the new row.

Need the CQL to view the contents of the videos table? Click here.
SELECT * FROM killrvideo.videos;

Doesn't that feel good to be using PreparedStatements properly? Just remember, prepare() once and use many times.

END OF EXERCISE

No FAQs.

Using PreparedStatement to insert data: use PreparedStatement to optimize CQL statement execution.

Comments are closed.