Using QueryBuilder

DSE Version: 6.7

Intro

Exercises

Slides

Resources

In previous units, when we wanted to use CQL from the driver, we used simple strings. In the next few units we will show you some more sophisticated approaches that have performance and style improvements. In this unit we will show you how to use QueryBuilder to improve your driver-based CQL queries.

No write up.

Exercise: Use QueryBuilder to Insert with the Driver

In this exercise, you will:

  • Use the QueryBuilder class

  • Make the KillrVideo app support adding videos

Constructing raw CQL strings is naive and a bit clunky. QueryBuilder creates a query for you with a more formal way to handle parameters.

Steps

1) Make sure your KillrVideo app is running and you are logged in. Close any open code files you have in the editor, then launch your KillrVideo application. Be sure you are signed in (you may already be signed in, but if you are not, sign in now). If your KillrVideo app is not running, remember, launch the app with the blue dropdown arrow as shown, and access the app with the URL http://< your workstation IP address goes here >:3000.

2) Navigate to Add a Video. Click on the dropdown arrow next to your user name in the top-right corner of the browser, then click Add a Video.

3) Get the video. Select YOUTUBE from the dropdown menu. Then, find the URL of a video you like and paste it in the YouTube URL text box. Click Get it.

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

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

4) Adjust metadata. You will notice that the next page auto-populates with values from YouTube. If you want to make any changes to the metadata, make these changes on the form and click Add Video:

5) Try in Vain to add the Video. Your KillrVideo app tells you it added the video. After you click the Add Video button, switch back to Che to investigate the log output from the attempt to add the video.

All this activity looks like something happened, but guess what... you never actually added the video. We have some additional work to do.

If you pop back over to cqlsh and SELECT all rows from the videos table, you will see no entries - because there is no videos table (well, that seems logical now doesn't it?).

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
Need the CQL to SELECT all the rows in the videos table? Click here.
SELECT * FROM killrvideo.videos;

6) Create a videos table. The first step in adding videos is creating a place to actually store them. We will use a table named videos in the killrvideo keyspace. Back in cqlsh, create a table with these columns (make the videoid the primary key).

Column Name

Column Data Type

videoid

UUID

added_date

TIMESTAMP

description

TEXT

location

TEXT

location_type

INT

name

TEXT

preview_image_location

TEXT

tags

SET

userid

UUID

Need help with the CQL to create the table? Click here.
CREATE TABLE killrvideo.videos(
    videoid UUID,
    added_date TIMESTAMP,
    description TEXT,
    location TEXT,
    location_type INT,
    name TEXT,
    preview_image_location TEXT,
    tags SET<TEXT>,
    userid UUID,
    PRIMARY KEY(videoid)
);

7) Verify your pom.xml file has the proper dependency. QueryBuilder has its own jar file. Open your scratchpad/pom.xml file and verify that Jamie and Steve did their job right and inserted the proper dependency already for you.

Close the pom.xml file once you are satisfied that the proper dependency is in place. (Guess what! We also put the dependency in killrvideo's pom.xml file for you as well. :)

8) Use QueryBuilder in ScratchPad.java. Here's your fresh ScratchPad.java code for this exercise. Copy and paste it over your existing scratchpad/src/main/java/com.datastax.academy.cloud.demo/ScratchPad.java code.

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------");

  }

  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);
  }
}

Similar to prior ScratchPad.java files we've given you, we placed the addNewVideo() method stub at the top and added some test code for you in main(). The test code creates a Video object and submits it to the addNewVideo() method.

9) Fill in the code for addNewVideo(). The code for this method has the following steps:

A) Create the template for the statement (SimpleStatementBuilder)

B) Bind the values from the Video instance (i.e., the parameter) to the statement (SimpleStatement)

C) execute() the statement

Following these steps, write the code for addNewVideo(). Don't forget...you have an awesome cheat sheet if needed.

Need more of a hint on how to proceed? 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. ...
        // etc. ...
        .builder();
                       
    //-----------------------------------------------------------------------------
    // Bind the values to the statement
    //-----------------------------------------------------------------------------
    SimpleStatement insertVideoStatement = insertVideoBuilder
      .addNamedValue("videoid", video.getVideoid())
      // etc. ...
      .build();

    //-----------------------------------------------------------------------------
    // Execute the statement
    //-----------------------------------------------------------------------------
    session.execute...
  }

  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);
  }
}
Are you lost, confused or frustrated and just want the frickin' code for ScratchPad.java? 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();
                       
    //-----------------------------------------------------------------------------
    // Bind the values to the statement
    //-----------------------------------------------------------------------------
    SimpleStatement insertVideoStatement = insertVideoBuilder
      .addNamedValue("videoid", video.getVideoid())
      .addNamedValue("userid", video.getUserid()) 
      .addNamedValue("name", video.getName())
      .addNamedValue("description", video.getDescription())
      .addNamedValue("location", video.getLocation())  
      .addNamedValue("location_type", 0)
      .addNamedValue("preview_image_location", video.getPreviewImageLocation())
      .addNamedValue("tags", video.getTags())
      .addNamedValue("added_date", video.getAddedDate())
      .build();
           
    //-----------------------------------------------------------------------------
    // Execute the statement
    //-----------------------------------------------------------------------------
    session.execute(insertVideoStatement);
  }

  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);
  }
}

10) Run the ScratchPad.java test. Once you have the code in place, it's time to Run Scratchpad by clicking on the blue dropdown.

You will need to verify that the code works as expected by investigating the videos table using cqlsh. Once you verify that your test inserted a row in the table, you can clean up the table by truncating it:

Need the CQL commands to verify the table contents and truncate the table? Click here.
// Look at the contents of the videos table here:
SELECT * FROM killrvideo.videos;
// Truncate the contents of the videos table here:
TRUNCATE killrvideo.videos;

11) Deploy the code. Copy the addNewVideo() method from the scratchpad project to VideoAccess.java within the killrvideo project (killrvideo/src/main/java/killrvideo/dataLayer/VideoAccess.java).

Did you get stuck and need to see the final code for VideoAccess.java? 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);
    
  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();
                       
    //-----------------------------------------------------------------------------
    // Bind the values to the statement
    //-----------------------------------------------------------------------------
    SimpleStatement insertVideoStatement = insertVideoBuilder
      .addNamedValue("videoid", video.getVideoid())
      .addNamedValue("userid", video.getUserid()) 
      .addNamedValue("name", video.getName())
      .addNamedValue("description", video.getDescription())
      .addNamedValue("location", video.getLocation())  
      .addNamedValue("location_type", 0)
      .addNamedValue("preview_image_location", video.getPreviewImageLocation())
      .addNamedValue("tags", video.getTags())
      .addNamedValue("added_date", video.getAddedDate())
      .build();
           
    //-----------------------------------------------------------------------------
    // Execute the statement
    //-----------------------------------------------------------------------------
    session.execute(insertVideoStatement);
  }

  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) {
      
  }
}

12) Bounce the KillrVideo app. If the KillrVideo app is still running, stop it and relaunch it.

13) Add a video. Go back to the KillrVideo home page and re-do the first five steps from this exercise (you may skip Step 1 if you are still logged in to KillrVideo).

At the end of these steps, you will see a message like this:

Of course, you got that message even when the app could not add the video. So, we'll dig deeper in the next step to make sure the app really added the video.

Also, be aware that the Click here link will not work yet. As you might guess, we will probably find out about this later as we do more development.

14) Verify your success. Investigate the videos table using cqlsh. Make sure the KillrVideo web-app added the row to the table.

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

You should see something like this:

When you see the row you added from the web-app in the table, you know your code worked! You're becoming a Cassandra app-dev ninja!

END OF EXERCISE

No FAQs.

Using QueryBuilder to insert data: use QueryBuilder to enhance creating CQL queries instead of constructing raw CQL strings.

Comments are closed.