Lightweight Transactions with the Driver

DSE Version: 6.7

Intro

Exercises

Slides

Resources

In previous units we learned how to build the login functionality for KillrVideo. But, it's not very helpful to have users login if they can't register with the site! In this unit we'll build out the code to support user registration.

No write up.

Exercise: Create a User with the Driver

In this exercise, you will:

  • Insert a new user

  • Prevent upserting existing user accounts by using Lightweight Transactions

  • Implement the website register functionality

Inserting data with the driver is nearly as simple as retrieving data with the driver. We create an INSERT command populating the appropriate data and send it down to the database. In this exercise, we will create users. We will also use lightweight transactions with the driver to avoid inserting duplicate users.

Steps

1) Be sure your killrvideo website is running. Sign out of your website if necessary. Then navigate to the user registration page by either clicking the Register button or going to http://< your workstation IP here >:3000/account/register

2) Register a test user. Fill out the form with dummy data then click the Register button. Switch back to Che (looking at the Run KillrVideo tab) and notice the log trace in the output.

We see some statements in the log indicating traces through the service and data layer. Careful investigation in the log indicates the user already exists. Whaaaaa?

3) Review the KillrVideo registration app code. In the killrvideo project, navigate to your killrvideo/src/main/java/killrvideo/dataLayer/UserAccess.java code file and scroll to the createNewUser() method.

createNewUser() returns a Boolean. true indicates the insert succeeded, and false indicates the user already exists. We could get fancier indicating other errors via exceptions, but let's start simple. We hard coded false, so the website currently reports that any user you register already exists.

Your mission, should you choose to accept it, is to implement the createNewUser() method similar to how you did the previous exercises. However, the difference here is that you are inserting data instead of reading it. But don't freak out! We'll help you through it step-by-step.

4) In the scratchpad project, open ScratchPad.java. Remember that in previous exercises we used the scratchpad project to build and test methods before we moved the code into the KillrVideo app. This approach lets us test code without interacting with the browser. Let's follow that same approach here. Open scratchpad/src/main/java/com.datastax.academy.cloud.demo/ScratchPad.java.

5) Refresh your scratchpad code. Start off with a fresh ScratchPad.java file by copying the following code and pasting it to replace your existing 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 boolean createNewUser(DseSession session, 
    String password, User user) throws Exception {
    LOGGER.debug("------Start createNewUser------");
    return false;
  }

  public static void main(String[] args) {
    LOGGER.info("Starting main()...");
    try {
      SessionManagement.initSession();
      DseSession session = SessionManagement.getSession();
    
      User user = new User(
        java.util.UUID.randomUUID(),
        "Fred",
        "Flintstone",
        "fred@bedrock.com",
        Instant.now());
    
      boolean result = createNewUser(session, "Fred'sP@ssw0rd", user);
      LOGGER.info("result = " + result);
      
      // Test adding an existing user by re-adding the user you just added
      if (result) {
        boolean secondResult = createNewUser(session, "Fred'sP@ssw0rd", user);
        LOGGER.info("secondResult = " + secondResult);
      }
    }
    catch (Exception e) { e.printStackTrace(); }
    finally { SessionManagement.closeSession(); }
    System.exit(0);
  }
}

Notice we now have the stubbed out createNewUser() method towards the top. We also added test code in main(), which creates a User object and submits it to createNewUser(). If createNewUser() returns true (indicating success), main() then tries to re-add the same User object again (which should fail because the User should already exist in the database).

6) Run scratchpad. Let's make sure everything compiles and runs before we start adding code. To run the scratchpad project, click on the blue dropdown arrow and select Run ScratchPad:

Of course, the test fails because we have not yet implemented the createNewUser() method:

7) Fill in the code for createNewUser(). Here's what you need to do: First, write a statement to insert data into the user_credentials table using a lightweight transaction. The lightweight transaction prevents upserting any existing users. Lightweight transactions with the driver return a ResultSet containing an [applied] column (the column name includes the brackets). Read this column value directly or use the wasApplied() method to determine if the insert was successful. If so, execute() another INSERT into the users table for the new user. Refer to your cheat sheet if necessary.

Want a comment outline to help you along? Click here.
  public static boolean createNewUser(DseSession session, 
    String password, User user) throws Exception {
    LOGGER.debug("------Start createNewUser------");

  //-----------------------------------------------------------------------------
  // Setup an insert command into the user_credentials table
  // using a lightweight transaction.
  //-----------------------------------------------------------------------------
  SimpleStatement insertCredentialsCommand = SimpleStatement.newInstance(...

  //-----------------------------------------------------------------------------
  // execute() your insert and either use the one() function on your ResultSet
  // to pull the single Row and examine the value of the [applied] column
  // OR just use wasApplied() on your ResultSet. If it failed, return false.
  //-----------------------------------------------------------------------------
  ResultSet meResultSet = ...

  //-----------------------------------------------------------------------------
  // Determine from the results if the INSERT was applied
  //-----------------------------------------------------------------------------
  boolean wasApplied = ...

  if(wasApplied)
  {
    //-----------------------------------------------------------------------------
    // Build up a second command containing another insert into the users
    // table. Be sure to include all the data for all the columns.
    //-----------------------------------------------------------------------------
    SimpleStatement insertUserCommand = SimpleStatement.newInstance(...

    //-----------------------------------------------------------------------------
    // execute() your command.
    //-----------------------------------------------------------------------------
    session...
  }
  
  // If everything was successful, you will want to return true.
  return wasApplied;
}
Are you still stuck and need 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 boolean createNewUser(DseSession session, 
    String password, User user) throws Exception {
    LOGGER.debug("------Start createNewUser------");

    //-----------------------------------------------------------------------------
    // Setup an insert command into the user_credentials table
    // using a lightweight transaction.
    //-----------------------------------------------------------------------------
    SimpleStatement insertCredentialsCommand = SimpleStatement.newInstance(
      "INSERT INTO killrvideo.user_credentials (email, password, userid) VALUES " + 
      "(?, ?, ?) IF NOT EXISTS", user.getEmail(), password, user.getUserid());

    //-----------------------------------------------------------------------------
   // execute() your insert and either use the one() function on your ResultSet
   // to pull the single Row and examine the value of the [applied] column
   // OR just use wasApplied() on your ResultSet. If it failed, return false.
    //-----------------------------------------------------------------------------
    ResultSet meResultSet = session.execute(insertCredentialsCommand);

    //-----------------------------------------------------------------------------
    // Determine from the results if the INSERT was applied
    //-----------------------------------------------------------------------------
    boolean wasApplied = meResultSet.wasApplied();
       
    if(wasApplied)
    {
      //-----------------------------------------------------------------------------
      // Build up a second command containing another insert into the users
      // table. Be sure to include all the data for all the columns.
      //-----------------------------------------------------------------------------
      SimpleStatement insertUserCommand = SimpleStatement.newInstance(
        "INSERT INTO killrvideo.users (userid, created_date, email, firstname, lastname) " +
        "VALUES (?, ?, ?, ?, ?)", user.getUserid(), Instant.now(),
        user.getEmail(), user.getFirstname(), user.getLastname());
             
      //-----------------------------------------------------------------------------
     // execute() your command.
      //-----------------------------------------------------------------------------
      session.execute(insertUserCommand);
    }

    // If everything was successful, you will want to return true.
    return wasApplied;
  }

  public static void main(String[] args) {
    LOGGER.info("Starting main()...");
    try {
      SessionManagement.initSession();
      DseSession session = SessionManagement.getSession();
    
      User user = new User(
        java.util.UUID.randomUUID(),
        "Fred",
        "Flintstone",
        "fred@bedrock.com",
        Instant.now());
    
      boolean result = createNewUser(session, "Fred'sP@ssw0rd", user);
      LOGGER.info("result = " + result);
      
      // Test adding an existing user by re-adding the user you just added
      if (result) {
        boolean secondResult = createNewUser(session, "Fred'sP@ssw0rd", user);
        LOGGER.info("secondResult = " + secondResult);
      }
    }
    catch (Exception e) { e.printStackTrace(); }
    finally { SessionManagement.closeSession(); }
    System.exit(0);
  }
}

8) Run your tests. Test your code by running scratchpad. When the test runs correctly, you will see output like so:

The first INSERT succeeds, but the second one doesn't.

If you try to run this test a second time, the first test will fail because you already inserted the record during the initial test run. To make the test run successfully a second time, you will need to delete the records from both the users table and the user_credentials table. Here is an example cqlsh session where we deleted a user with the email address fred@bedrock.com and a user ID of c88b4e8f-628e-45e2-ab79-091cac9126d9. You see that we first needed to SELECT the user to find the user ID:

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 the CQL commands shown above? Click here.
Here's the CQL - remember to copy and paste the userid:
// Be sure to replace the correct user ID as specified
SELECT * FROM killrvideo.user_credentials WHERE email = 'fred@bedrock.com';
DELETE FROM killrvideo.users WHERE userid = < user ID goes here >;
DELETE FROM killrvideo.user_credentials WHERE email = 'fred@bedrock.com';

9) Deploy the code. Once you are satisfied that your version of createNewUser() works correctly, you can copy and paste the code for this method into UserAccess.java in the killrvideo project.

Are you a little unsure exactly what this code should look like? Click here to see the updated UserAccess.java.
package killrvideo.dataLayer;

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

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.User;

public class UserAccess {

  private static final Logger LOGGER = LoggerFactory.getLogger(UserAccess.class);
  
  public static boolean createNewUser(DseSession session, 
    String password, User user) throws Exception {
    LOGGER.debug("------Start createNewUser------");

    //-----------------------------------------------------------------------------
    // Setup an insert command into the user_credentials table
    // using a lightweight transaction.
    //-----------------------------------------------------------------------------
    SimpleStatement insertCredentialsCommand = SimpleStatement.newInstance(
      "INSERT INTO killrvideo.user_credentials (email, password, userid) VALUES " + 
      "(?, ?, ?) IF NOT EXISTS", user.getEmail(), password, user.getUserid());

    //-----------------------------------------------------------------------------
   // execute() your insert and either use the one() function on your ResultSet
   // to pull the single Row and examine the value of the [applied] column
   // OR just use wasApplied() on your ResultSet. If it failed, return false.
    //-----------------------------------------------------------------------------
    ResultSet meResultSet = session.execute(insertCredentialsCommand);

    //-----------------------------------------------------------------------------
    // Determine from the results if the INSERT was applied
    //-----------------------------------------------------------------------------
    boolean wasApplied = meResultSet.wasApplied();
       
    if(wasApplied)
    {
      //-----------------------------------------------------------------------------
      // Build up a second command containing another insert into the users
      // table. Be sure to include all the data for all the columns.
      //-----------------------------------------------------------------------------
      SimpleStatement insertUserCommand = SimpleStatement.newInstance(
        "INSERT INTO killrvideo.users (userid, created_date, email, firstname, lastname) " +
        "VALUES (?, ?, ?, ?, ?)", user.getUserid(), Instant.now(),
        user.getEmail(), user.getFirstname(), user.getLastname());
             
      //-----------------------------------------------------------------------------
      // Execute() your command.
      //-----------------------------------------------------------------------------
      session.execute(insertUserCommand);
    }

    // If everything was successful, you will want to return true.
    return wasApplied;
  }

  public static UUID getAuthenticatedIdByEmailPassword(DseSession session,
    String email, String password) throws Exception {
    LOGGER.debug("------Start getAuthenticatedIdByEmailPassword------");

    //-----------------------------------------------------------------------------
	// Create a string to SELECT the user from user_credentials based on email
	// TBD: Create the SELECT command string that selects from user_credentials:
    //-----------------------------------------------------------------------------
    String command = "SELECT * FROM killrvideo.user_credentials WHERE email = ?";
    // Create the SimpleStatement that combines the command with the email:
    SimpleStatement statement = SimpleStatement.newInstance(command, email);
	// Execute the statement and get the result set
	ResultSet meResultSet = session.execute(statement);
	// Get the row from the result set
	Row meRow = meResultSet.one();
	// Create a UUID for the returned user ID and initialize it to null
	UUID userId = null;
	// If the row exists,
	if (meRow != null) {
		// Get the password value from the row
		String passwordFromDB = meRow.getString("password");
		// If the password value from the row equals password parameter,
		if (password.equals(passwordFromDB)) {
			// Set the returned user ID to the row’s user ID
			userId = meRow.getUuid("userid");
		}
	}

	// Return the user ID
	return userId;
  }

  public static User getUserById(DseSession session, 
    UUID userid) throws Exception {
    LOGGER.debug("------Start getUserById------");

    // Create a string to SELECT the user from users based on parameterized userid
	String command = "SELECT * FROM killrvideo.users WHERE userid = ?";
    // Create the SimpleStatement that combines the command with the userid
    SimpleStatement statement = SimpleStatement.newInstance(command, userid);
    // Execute the query string and get the result set
    ResultSet meResultSet = session.execute(statement);
    // Get the row from the result set
    Row meRow = meResultSet.one();
    	
    // Extract all the values from the row and create a User object
    String firstname = meRow.getString("firstname");
    String lastname = meRow.getString("lastname");
    String email = meRow.getString("email");
    Instant createdAt = meRow.getInstant("created_date");
    User user = new User(userid, firstname, lastname, email, createdAt);
   	
    // Return the User object    	
    return user;
  }
}

10) Bounce the web server and register. Stop KillrVideo and restart it. Try registering as a new user (i.e., a user that is not yet in your tables) within your KillrVideo app.

11) Verify that your new code works. To verify your changes worked correctly, look at the log output. You should see the statement Boom shakalaka 'new toys!' in the log output:

When you successfully register a new user, the web app will redirect to the home page. Further, you can use cqlsh to look at the contents of the users and user_credentials tables to see your new user entries.

12) Verify your lightweight transaction prevents duplicate users. Sign out of your website and then try re-registering using the same email address as you used before.

If you were successful in your coding endeavors, the lightweight transaction will prevent you from registering twice, and you will see this error message in the website:

Finally, when you can register a new user but not duplicates, slap yourself on the back for a job well done! You're becoming a Cassandra hero! Get up and do a dance so everyone knows you've come this far. Get the party going on!

Don't want to do a dance? Click here, and we will do one for you.
Dance off!

END OF EXERCISE

No FAQs.

Creating a user with the DSE Java Driver: define a new method for creating users and test the changes to your application.

Comments are closed.