Evaluating CouchDB

Building a game catalog with CouchDB

Revisiting a recurring example in this blog, this tutorial shows how to build a game catalog using CouchDB. While cataloging board games could easily be made to fit into a SQL relational structure, exploring NoSQL gives flexibility into how we represent various entities that need to be referenced. The goal of this tutorial is to provide a scenario where one may consider NoSQL, identify key required functionality, and evaluate the different approach taken by CouchDB.

Why CouchDB

CouchDB is a document store, meaning we treat all objects similarly in our database as documents. CouchDB was chosen because it is mature, open source, easy to setup and has a relatively shallow learning curve. It also provides a REST interface, and a built in admin tool called Fauxton. Data views are built using MapReduce statements, and provides CouchDB with the indexing functionality it needs to provide speedy lookups and queries. CouchDB can even handle authentication and authorization, which means you can potentially use this as a free-standing backend REST service. These features make CouchDB an attractive solution to those new to the NoSQL world.

Data Set

For this example, we will create a data set that is comprised of various types of components that we might want to catalog. There may be things like game systems, which are a collection of various other components to form a playable game (like Dungeons & Dragons). We may have kits, that contain many pieces necessary to play the game, like dice. We also have other content like books, and potentially things like, counters, figurines, card decks, homebrew games, and so on. Each of these different categories of items have different attributes, and may have many to many relationships with any other component.

For example, we can have the following object with a many to many relationship:

{
  "name": "Dungeons & Dragons",
  "type": "game-system",
  "vendor": "Wizards of the Coast",
  "contents": [
    {"type":"dice", "name":"d20", "color":"black", "sides":20},
    "type":"book", "name":"Dungeon Master's Guide", "vendor":"Wizards of the Coast"},
    {"type":"kit", "name":"Dungeons & Dragons Starter Set", "vendor":"Wizards of the Coast", "contents":[
      {"type":"dice", "name":"d20", "color":"black", "sides":20}
      {"type":"dice", "name":"d4", "color":"red", "sides":4}
    ]}
    {
  ]
}

You’ll notice that the top level object (Dungeons & Dragons) is considered a game-system, which we can think of as a collection of various game pieces we may use to play. You’ll see contents like dice and the Dungeon Master’s guide, which are single items. We also have a kit, which can belong to the game system, but also have it’s own components, like dice, which also have a relationship directly with the game-system.

The SQL Relational Way

We certainly can represent this in a relational SQL database. We would have a table of game-systems, kits, and dice, books, and any other future component. In order to express these relationships, we need to build linking tables in order for us to represent our many to many relationships. We can make something like a game-systems-dice table. Our game-systems table will have a one to many relationship to this table, which in turn, will have a many-to-one relationship with other components. We will need to do this for each relationship we need to define between an entity that has contents. In this example, this would result in 11 tables, which is somewhat manageable, but consider the fact that as we add more entities, we have to now build out additional linking tables for each potential relationship. Suddenly, this problem begins to grow exponentially!

Is NoSQL the answer?

This hopefully serves as an example of the pros and cons of a NoSQL approach. Most non-graph NoSQL databases aren’t designed to handle relational data. In fact, that’s one of the distinguishing features of most NoSQL databases. But, they do give us the benefit of not having to define and maintain these relationships up front, allowing us to define these relationships as we need them.

Goals

  • Create an efficient view
  • Support the equivalent of a SQL many-to-many relationship join
  • Present views of a composite object

Prerequisites

The following tutorial assumes you have installed Docker and can make curl requests.

Setup

  1. Run docker run -d -p 5984:5984 --name couchdb klaemo/couchdb to start an instance of CouchDB 2.0 running locally on port 5984
  2. Open your browser to http://localhost:5984/_utils to access the Fauxton admin panel.
  3. Navigate to ‘Setup’ > ‘Configure Single Node’ and specify a username and password. Leave the IP and port.

Adding Data

The following can be completed via curl requests against the REST API, or in the Fauxton admin dashboard.

  1. Create A database
    1. ‘Databases’>’Create Database’> name it “catalog”
  2. Insert some documents.
    1. ‘All Documents’>’+’>’New Doc’
  3. Here is some starting data. Typically, you’d let CouchDB create the _id, but this makes it easier for us to quickly create some relationships by copy and pasting.

Dice:

{
  "_id": "735174c9bbfec0115fa1e65e630035ed",
  "type": "dice",
  "name": "d20 Black",
  "sides": "20",
  "appearance": "black"
 }
{
  "_id": "735174c9bbfec0115fa1e65e63011d30",
  "_rev": "1-6a4c2db6ebe043ab37bd4ff06b55de97",
  "type": "dice",
  "name": "d4 Red",
  "sides": "4",
  "appearance": "red"
 }

 Kit:

{
  "_id": "735174c9bbfec0115fa1e65e6301486e",
  "type": "kit",
  "name": "Dungeons & Dragons Starter Set",
  "vendor": "Wizards of the Coast",
  "contents": [
    {
      "qty": 2,
      "id": "735174c9bbfec0115fa1e65e630035ed"
    },
    {
      "qty": 4,
      "id": "735174c9bbfec0115fa1e65e63011d30"
    }
  ]
}

Game Systems:

{
  "_id": "735174c9bbfec0115fa1e65e63016382",
  "name": "Dungeons & Dragons",
  "type": "game-system",
  "vendor": "Wizards of the Coast",
  "contents": [
    {
      "qty": 3,
      "id": "735174c9bbfec0115fa1e65e630035ed"
    },
    {
      "qty": 5,
      "id": "735174c9bbfec0115fa1e65e63011d30"
    },
    {
      "qty": 1,
      "id": "735174c9bbfec0115fa1e65e6301486e"
    }
  ]
}
{
  "_id": "735174c9bbfec0115fa1e65e63019d6e",
  "name": "Pathfinder",
  "type": "game-system",
  "vendor": "Pazio",
  "contents": [
    {
      "qty": 10,
      "id": "735174c9bbfec0115fa1e65e630035ed"
    },
    {
      "qty": 4,
      "id": "735174c9bbfec0115fa1e65e63011d30"
    }
  ]
}

Retrieving Data

  1. Query our documents
    1. All documents are automatically displayed in the admin panel at http://localhost:5984/_utils/#/database/catalog/_all_docs and available via REST API with curl http://localhost:5984/catalog/_all_docs
    2. By default, CouchDB only returns the IDs and metadata. In the Fauxton admin panel, select the “Documents” checkbox up top to include the related document. Or append ?include_docs=true to your curl request.

This alone doesn’t provide much functionality. CouchDB doesn’t know how to best present or index this information.

Creating A Simple View

A view, is similar to a SQL table, in the fact that it provides CouchDB a way to group together data. This view will search for all documents that are a game-system and emit them from a map function.

  1. Create a view by selecting “Design Documents”>”+”>”New View”
  2. Create a new design document to aggregate our views. In the input box labeled _design/, name your document “contents”.
  3. Name the index ‘game-systems’
  4. Create the following map function
function (doc) {
  if(doc.type === 'game-system'){
    emit(doc._id, null);
  }
}

This function iterates over all documents, and if they’re of type ‘game-system’ then it emits it’s key. This view has automatically created an index on the key (the game-system id). Query this view via curl http://localhost:5984/catalog/_design/contents/_view/game-systems?include_docs=true. Now all game-systems can be queried as one would view a table in SQL. But, since CouchDB doesn’t have a concept of relationships, we only see the ids referencing our contents, not a joined view where we could see their details.

Build a More Complex View

Next, build a view that displays the contents of each document. This will be used to return one side of our many to many relationship, and allow us to build a composite view of our object.

  1. Create a new view, this time indexing all of our components and their ‘parent’. The index will be an array of the parent ID, and an index, and the value will be the component object (instead of an ID)
  2. Name the view ‘owners’ and use the following map function:
function (doc) {
  if(doc.name && doc.contents) {
    for (var i in doc.contents) {
      var content = doc.contents[i];
      emit([doc._id, i], {"_id": content.id, "content": content});
    }
  }
}

This is our “Join” statement. It returns a row for each component in each object that has ‘contents’. The map function first checks if there are contents. It then loops through each element and extracts the content. It emits key value pairs as follows: The key is an array of the owner’s ID, and the index (to ensure uniqueness), the value is an object containing an _id (which will become apparent soon), and the entire content object.

The following query fetches all the contents for the D&D game-system: curl -X GET localhost:5984/catalog/_design/contents/_view/owners?include_docs=true -G --data-urlencode start_key='["735174c9bbfec0115fa1e65e63001edc",""]' --data-urlencode end_key='["735174c9bbfec0115fa1e65e63001edc",{}]'

The start_key and end_key allow you to filter your result. In this example, we’re interested in all primary keys associated with the Id of the D&D game-system.

If you query without include_docs=true, you’ll only see the id of the content, as value.content.id. When you include_docs, CouchDB uses the _id field in the value to determine which document to attach. This provides us with the remaining information needed to create our composite object.

Summary

Our Goals

The three goals to accomplish were:

  • Create an efficient view
  • Support the equivalent of a SQL many-to-many relationship join
  • Present views of a composite object

We built a view using a CouchDB design document. This is defined by a map function that emits key-value pairs for CouchDB to index. Future updates to the index are updated on write, leading to quick look ups.

Using another view, we create a map function that identifies all documents that have contents that references other documents. We create an index on the parent document’s IDs, and store the value of the contents. CouchDB provides the document related to the contents entry. We can pass query parameters to retrieve all contents for a particular document.

Unfortunately, this still requires two REST requests to fetch our data. Potentially List Functions could be used to combine multiple objects. But, could prove to be difficult to handle things like paging and sorting.

So, CouchDB is unable to completely satisfy the last goal. Potentially, a service could be built that would handle the 2 REST requests and create the composite object.

Other Benefits

  • CouchDB provides a REST API out of the box. It’s syntax is fairly straightforward, but might need to be transformed by another service if you are looking for a particular REST format.
  • Validation can be built into the API (as well as authentication)
  • Relatively easy to get started

Additional Links and References

Source Content on GitHub

Martin Fowler – Intro to NoSQL Talk

CouchDB Docs

Docker Image

Relevant Stack Overflow Answer

Posted in Tutorial Tagged with: ,

Using Spring Boot and Spring Data JPA to Access MySQL Data

In my previous post, we saw how to setup MySQL, add some test data, and run some basic SQL queries. Now, let’s create an Java application that can access our data using the Spring Data JPA library. Spring does have a decent tutorial on using Spring Data JPA, but I felt it lacked any context to the technologies being used and implements an H2 in-memory database. H2 is a great tool for certain use cases, testing, and demos, but does not help us if we want to build an application that actually persists our data between application restarts. You’ll find the code for this tutorial located on GitHub.

A Note on Having Opinions

We will be using Spring Boot to build our application, because Spring Boot takes an opinionated approach to building software. That is to say, Spring has noticed particular patterns in the industry, and has decided to create tools that standardize these patterns into an easy to use framework. The downside to this approach is that you usually have to work within the limits of the tools designed around the approach. Additionally, this adds another layer of complexity to our application, while at the same time obscuring it’s behavior. This can be troublesome because a new developer may not fully understand what the framework is doing ‘under the hood’, leading to confusion, lack of understanding, and creating difficult to debug problems. This post hopes to demystify some of the magic that happens so we can quickly get to what we want to do: Build awesome applications!

A Look at Our Tools

JPA known as the Java Persistence API was designed to be a specification of how developers can build tools that represent values in a relational database. It provides an interface that defines these specifications as a standardized way to implement this functionality. What that means for developers is that there is some familiarity and interchangeability between tools that implement these standards. Developers can choose to write their own implementation, or use existing solutions like Hibernate, EclipseLink, or what we’ll use in this post, Spring Data JPA. Implementing interfaces is a valuable tool in developing object oriented code, check out this tutorial for an introduction into understanding interfaces work and how to implement your own.

Hibernate is a library designed to provide ORM, or Object Relational Mapping. Mapping is the process of taking Java objects and serializing them to be sent and stored to a database, and deserializing them from the database to reconstruct the object. Hibernate also manages our JDBC connections to our database, and then uses it’s ORM libraries to get and fetch Java entities.

Spring Data is actually a collection of libraries designed to provide a “familiar and consistent” experience for developers when accessing data from a number of different types of  data stores. In this example we are working with a relational database, but if our needs change, switching to a different data store becomes less difficult.

Spring Data JPA is Spring’s implementation of the JPA specification. Built on top of Hibernate, Spring Data JPA also implements easy to use repositories to access and manipulate our data.

JUnit is a testing framework designed to easily create tests for our code. Typically used for unit testing, or tests designed around isolated blocks of code, JUnit helps developers write tests quickly, create assertions to validate data, and easily run tests to get fast feedback. In this tutorial, we’ll be using it more for integration testing to test overarching functionality, instead of tying ourselves to any particular way of implementing our new skills.

Getting Started

Since we’re using Spring Boot, we can use the Spring Initializr to create our application. You can generate a project from their site, which creates a ZIP file you can open in your IDE of choice, or you can use generators built into IDEs such as IntelliJ or the Spring Tool Suite plugin for Eclipse. Be sure to add the ‘JPA’ and ‘MySQL’ dependencies as you create your project. To create a new project in IntelliJ, select ‘Create New Project’ from the main menu, or from an existing project navigate to ‘File’>’New’>’Project…’ Use the ‘Spring Initializr’ to get started.

Creating a new Spring Boot project in IntelliJ

Creating a new Spring Boot project in IntelliJ

dependencies

Select your dependencies

If you have an existing Spring Boot project, add the following dependencies to <dependencies> block of your Maven pom.xml.

<dependency>
 <groupId>org.springframework.boot</groupId>
 <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

<dependency>
 <groupId>mysql</groupId>
 <artifactId>mysql-connector-java</artifactId>
 <scope>runtime</scope>
</dependency>

These two dependencies tell Maven to import the necessary libraries we need to use Spring Data JPA, as well as the required drivers to access MySQL. Once you’ve created your project, you can use Maven to build and test your application by running ‘mvn test’ to ensure everything is configured correctly.

Entities to Represent Our Data

Our first step will be to create an entity that represent the objects in the database we created in the last post. First create a package named ‘domain’ under our src/main/java/com/appname folder. In this example, we can see the directory is at the same level as the main class of our application ‘MysqlJpaDemoApplication’. In this package, create a class called ‘Game’, which we can see below.

package com.recursivechaos.gamemanager.domain;

import javax.persistence.*;

@Entity
public class Game {

 @Id
 @GeneratedValue(strategy = GenerationType.AUTO)
 private Integer id;

 private String name;

 private String description;

 public Game() {}

 public Game(String name, String description) {
   this.name = name;
   this.description = description;
 }

 public Integer getId() {
   return id;
 }

 public void setId(Integer id) {
   this.id = id;
 }

 public String getName() {
   return name;
 }

 public void setName(String name) {
   this.name = name;
 }

 public String getDescription() {
   return description;
 }

 public void setDescription(String description) {
   this.description = description;
 }
}

On line 5 we can see a JPA interface annotation called @Entity. This will signify to Spring to treat this object as a database entity. We see some more annotations on lines 8 and 9. The @Id annotation indicates that the ‘id’ field will be used as the unique primary key we defined in the earlier tutorial. Note that it is type Integer, which matches what we created in the database. The @GeneratedValue tells Spring that MySQL will automatically generate this value for us.

The next two fields, ‘name’ and ‘description’ match our database table, and Spring is smart enough to map these two String values to the varchar values in the database.

We’ll need a no-args constructor for Spring to use under the hood, and we’ll create a second constructor that takes our two fields in order to easily create new objects to store to the database.

You’ll notice that our fields are all private, so we will create getter and setter methods to change/access their values. This enforces encapsulation, as described here. This allows us to restrict access to fields if need be and modify their getting/setting behavior easily.

Repositories to Access Our Data

Next, we’ll create a repository to access our data. First, create a new package next to our domain package called ‘repository’. We’ll create a new Interface called GameRepository. Remember all that rambling at the beginning of the post about opinionated implementation, and Spring Data JPA doing all sorts of magic for us? Here’s where it happens, so follow along! We’re creating an interface, instead of a class, because we’re not actually implementing any of this code, we’re merely defining a contract to be used elsewhere in the code. We’ll use the extends keyword to inherit the behavior from Spring’s CrudRepository class. If you examine the CrudRepository class, you’ll see that Spring has already implemented some useful looking features, which are called CRUD functions. We can Create using the save methods, Read data with the find methods, Update using the same save methods, and Delete with the delete methods. Typically, these will cover the bulk of your needs, but later we’ll see how we can implement our own find methods to give us more control over what we’re trying to retrieve. We do need to provide the CrudRepository with our Entity type, as well as it’s index’s type. Below we can see our new Repository.

package com.recursivechaos.gamemanager.repository;

import com.recursivechaos.gamemanager.domain.Game;
import org.springframework.data.repository.CrudRepository;

public interface GameRepository extends CrudRepository<Game, Integer> {

}

And that’s all the code we need in order to implement JPA using Spring Data!

Accessing Our MySQL Database

The last step before we can start accessing our data is to point our application to our MySQL database. With Spring Boot, this is as simple is locating our application.properties file located in src/main/resources and adding the following properties

spring.datasource.url=jdbc:mysql://localhost/game_manager
spring.datasource.username=dbuser
spring.datasource.password=dbpass
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

We define 4 properties. The first is the connection string to our database. In our case, it is running locally, so we point to localhost. We’ll also define the path to our schema ‘game_manager’. Next we provide the username and password to access this database. Finally, we provide the name of the driver that will be used to access the database. Once Spring starts, it will read our properties by default, and use them to connect to the database.

Double Check our Application Structure

By now, your project’s application structure should look the same as below.

Our application structure

Our application structure

Creating JUnit Tests

Alright, we can almost see the light at the end of the tunnel. Let’s see how we can use our new repository to access our data! Spring provides a number of tools to help us create tests that leverage the magic of Spring. Let’s open up the MysqlJpaDemoApplicationTests class that was automatically generated. We’ll see a basic test called ‘contextLoads’. We can go ahead and run this test to see if our Spring application is configured correctly. In most IDEs this is done by right clicking the class name and selecting ‘Run tests’, or something to that effect. If everything is good, we should see our test pass.

Let’s go ahead and delete the contextLoads() test, and implement our own. Lets take a look at a basic test to see if we can load our games

package com.recursivechaos.gamemanager;

import com.recursivechaos.gamemanager.domain.Game;
import com.recursivechaos.gamemanager.repository.GameRepository;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.SpringApplicationConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import java.util.ArrayList;
import java.util.List;

import static org.junit.Assert.assertEquals;

@RunWith(SpringJUnit4ClassRunner.class)
@SpringApplicationConfiguration(classes = MysqlJpaDemoApplication.class)
public class MysqlJpaDemoApplicationTests {

 @Autowired
 GameRepository gameRepository;

 @Test
 public void testLoadGames() {
     List<Game> games = (ArrayList<Game>) gameRepository.findAll();
     assertEquals("Did not get all games", 3, games.size());
 }

}

Lets break down what’s happening here:

@RunWith(SpringJUnit4ClassRunner.class) tells our class that we want to use JUnit with Spring’s enhancements

@SpringApplicationConfiguration(classes = MysqlJpaDemoApplication.class) tells our test that we want to use setup our test class the same way we set up our application. There is a lot of things that happen when Spring Boot starts, but for now understand that our test class will operate the same way a Spring Boot application works. In our situation, it is loading our application.properties and configuring our database for us.

@Autowired This annotation tells Spring to create a GameRepository for us. In a nutshell, it sees that GameRepository is an interface, will assign an implementation to our variable that has been injected with our credentials through a process called dependency injection. If this is new to you, I recommend reading up on how Spring does dependency injection. For now, understand that this allows us to easily create our repository and connect to our database.

@Test annotation is an indicator to JUnit that this is a test inside a suite of other tests

List<Game> games = (ArrayList<Game>) gameRepository.findAll();

Here’s the actual meat and potatoes of our functionality. Reading from right to left, we call the findAll() method on our repository. This will get a connection to the database, and perform a SELECT * FROM games on our database. The findAll method is provided by the CrudRepository we extended in our class. It returns an Iterable, which we will to cast into an ArrayList,  a subtype of Iterable, that we can manipulate in a familiar fashion. We assign this to our List of type Game, called games.

assertEquals("Did not get all games", 3, games.size());

Finally, we assert what we expect (that there are 3 elements in our database) against how many elements is in our list (using the size() method). Assertions allow us to test that the desired behavior has happened as expected in our test. If you happened to have a different number of elements in your test, your assertion will fail, throwing an exception, and failing your test.

Go ahead and run your test like before and see if your assertion is correct. If it is off, confirm the number of rows in your database, and update your expected number of games to the correct amount.

Building a Better Test

We’ve already come across one problem with our test. It’s dependent on the current state of our data. This will not work going forward, as we’re going to want to demonstrate additional functionality like adding/deleting. We don’t want to have to constantly be updating our tests to do so. Let’s see how we can use JUnit’s @Before annotation to run some operations before each test ensuring our database is in a consistent state. Lets add the following code to our test class.

@Before
public void setUp() throws Exception {
 gameRepository.deleteAll();
 Game pandemic = new Game("Pandemic", "Co-op game for wannabe disease control specailists");
 Game werewolf = new Game("Werewolf", "You must find out who's secretly the werewolf, before it's too late");
 Game camelUp = new Game("Camel Up", "A high stakes game of gambling and camel racing");
 gameRepository.save(pandemic);
 gameRepository.save(werewolf);
 gameRepository.save(camelUp);
}

Here we can see we delete all existing data in our table, and add 3 games. The @Before annotation tells JUnit to execute our setUp method before each test is run. Note that this isn’t the wisest idea if you are using these tables for anything other than testing, as it will delete any data you’ve created. But, for this tutorial, it makes for good demonstration. Run your tests again, and we should always see success!

Adding Query Functionality

Lets add some extra functionality to our repository. It’s likely that we’ll want to search for our games by name, so lets add a method to our GameRepository class. Spring Data JPA lets us create query methods by simply following naming conventions described in their documentation. Add the following line to our GameRepository class.

List<Game> findByName(String name);

This tells our repository to query our database using a WHERE name="param" clause to it’s statement. Since we cannot guarantee that only one value is returned, we need to return a List of Game objects. Now lets write a test to assert the desired behavior. Add the following method to our test.

@Test
public void testFindGame() throws Exception {
 List<Game> camelUpList = gameRepository.findByName("Camel Up");
 assertEquals("Found wrong number of Camel Ups", 1, camelUpList.size());
 assertEquals("Found wrong name", "Camel Up", camelUpList.get(0).getName());
}

If we run our tests once more, we should now see two passing tests.

Now that we can effectively find a game in particular, lets see how editing and deleting entities work. Using the CrudRepository’s methods, let’s try finding a game, updating it’s name, and then deleting it. After each step, we will use assertions to ensure the desired behavior. Add the following test to see the rest of our functionality.

@Test
public void testCRUD() throws Exception {
 // Create a new game
 Game munchkin = new Game("Munchkin", "A wild game for wild people.");
 gameRepository.save(munchkin);

 // Assert it was created
 List<Game> foundGame = gameRepository.findByName(munchkin.getName());
 assertEquals("Did not find Munchkin", munchkin.getName(), foundGame.get(0).getName());

 // Edit it's description
 String newDescription = "If you love rules that change, you'll love Munchkin!";
 foundGame.get(0).setDescription(newDescription);

 // Save to the database (note that we can save not just single Entities, but collections of them as well)
 gameRepository.save(foundGame);

 // Assert it updated
 List<Game> updatedGame = gameRepository.findByName(munchkin.getName());
 assertEquals("Did not update description", newDescription, updatedGame.get(0).getDescription());

 // Delete game
 gameRepository.delete(updatedGame);

 // Assert not found
 List<Game> emptyGame = gameRepository.findByName(munchkin.getName());
 assertEquals("Should have returned no games", 0, emptyGame.size());
}

Here we can see the full range of CRUD operations on our database. Congratulations, you now can easily access and manipulate data in your very own database, and learned some new tools along the way!

Peeking Under the Hood

Add the following properties to your application.properties to see what is actually happening to your database

spring.jpa.properties.hibernate.show_sql=true
spring.jpa.properties.hibernate.use_sql_comments=true
spring.jpa.properties.hibernate.format_sql=true

These logs can be rather noisy and difficult to read, but will display the commands issued against our database. This can be useful for troubleshooting or gaining an understanding of how our queries are formed into SQL statements.

Wrap Up

In this tutorial we’ve gone from an empty project, to one that has a full toolbox of options to manipulate data in our database. We’ve seen how Spring Boot’s opinionated approach to using Spring Data JPA means we can easily configure a repository to manage our entities. Below are links to Spring Data JPA’s documentation that can take you even further by writing custom queries and creating relationships between other entities in our database.

Troubleshooting

Here are some common issues you may run into along the way

WARN: Establishing SSL connection without server’s identity verification is not recommended.

You’ll likely see this, since we didn’t establish a secure connection to our MySQL database. In a test environment this is fine, but understand that this is warning us that we are transmitting potentially sensitive data encrypted across the wire. You can ignore this error for now.

java.sql.SQLException: Access denied for user ‘badusernamepassword’@’localhost’ (using password: YES)

Double check your username and password. If necessary, you may need to create a new user and password to connect.

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown database ‘gamemanager’

Make sure the schema you created in MySQL matches the end of the url provided in your properties.

Other Resources

This Tutorial’s Source Code

Spring’s Documentation on working with SQL

Spring Data JPA Documentation

Posted in Tutorial Tagged with: , , , , ,

Introduction to SQL with MySQL

In this tutorial, we will see how to set up a local MySQL database, how to use MySQL’s Workbench to create tables and insert data, as well as introduce some basic SQL queries to manipulate our data. In future blog posts we will expand upon this by creating an application to access and manipulate this data, as well as understand how we can leverage relational databases to represent data that relates to other data. But, before we do so, we need to setup a local database and understand the basic concepts of a SQL database.

Install MySQL

If you haven’t done so already, install the MySQL Community Server. MySQL is an easy to use, relational database with great community support and should be sufficient for most projects. See MySQL’s documentation for installation instructions. The default settings should be sufficient for this tutorial, and you can skip installing additional connectors for things like Excel or Python at this time. Go ahead and configure a password for your root user, and keep this somewhere that you can find it when you inevitably forget what it is. It is best practice to add an additional MySQL user account with it’s own username and password. Resist the urge to use a known password. Windows users may want to disable MySQL from automatically starting on Windows login.

Creating Our Data in MySQL Workbench

Once MySQL has been installed, we’ll want to create a schema, a table and store some sample data in it. Open the MySQL Workbench, which is a GUI manager for our database installation. Traditionally, databases are managed via SQL queries, but tools the MySQL Workbench allow us to easily create and edit our data without worrying about the syntax of SQL yet.

In the top left under ‘MySQL Connections’ choose your ‘Local instance MySQL…’ and enter your root password.

Selecting your connection

Selecting your connection

We have now connected to our database running on our local machine. From here, we will want to create a new schema. A schema represents a collection of related objects in our database, in our case our tables and rows within our tables. To create a new schema, click the 4th icon in the toolbar.

Creating a new schema

Creating a new schema

Give your schema something meaningful to represent your collection of tables. MySQL dictates that names should be lowercase, and use the ‘_’ in place of spaces. In this example, we’re using the name “game_manager”, and then click ‘Apply’. MySQL will ask you to review the script that will be applied to the Database. In this case, our GUI has generated a SQL script that will be ran against our database: CREATE SCHEMA `game_manager`;This bit of script will instruct our database to CREATE a new SCHEMA named ‘game_manager’ for us. Go ahead and click ‘Apply’ and ‘Finish’

Naming your schema

Naming your schema

We should now see in the bottom left our new schema. Right click this schema and ‘Set as Default Schema’. This tells MySQL workbench that we want to work with this schema from now on.

Set our default schema

Set our default schema

Now, let’s create a table for us to store our data in. In the toolbar, just to the right of where we created our Schema, create a new table. In this example, we’ve named our table ‘game’. Typically your table name will be a singular noun representing the collection of things you want to store. Click an empty row in the table in the middle of the UI. Initially this will create a new column named ‘id’ of type ‘INT’ (integer). Our table needs a unique identifier in order to organize our data, and this value will ensure that we have unique entries, even if our other values match. Our database will indicate this column as our unique identifier by selecting the ‘primary key’ checkbox, and will enforce this value to be set by checking the ‘Not Null’ checkbox. We also want our values to automatically increment, so check the ‘Auto Increment’ box as well.

Click just below this value to add another column. MySQL defaults this column to ‘gamecol’ with type VARCHAR(45). Let’s rename this column something more meaningful like ‘name’. It’s default type of VARCHAR(45) means that MySQL will store any variable length character string up to 45 characters in this row. This helps the database allocate space and optimize itself. Finally, it wouldn’t make sense to store a game without a name, so let’s click the ‘Not Null’ checkbox in the bottom right to have our database enforce this.

Let’s add one more column called ‘description’ that will store a brief description of our game. Our descriptions could very possibly be longer than 45 characters, so lets give us some more space to work with by updating the ‘Data Type:’ text box in the bottom right to VARCHAR(255). We may or may not provide a description, so do not select the ‘Not Null’ box like previously. Our final result should look something like the thumbnail below. Once done, click the ‘Apply’ button.

Creating a new table

After hitting apply, we are presented again with some SQL that will be applied to our database:

CREATE TABLE `game_manager`.`game` (
 `id` INT NOT NULL AUTO_INCREMENT,
 `name` VARCHAR(45) NOT NULL,
 `description` VARCHAR(255) NULL,
 PRIMARY KEY (`id`));

What we see here is MySQL CREATEing a new TABLE into our ‘game_manager’ schema called ‘game’. It will contain our 3 columns with their name, datatype, and whether or not it will allow null values. Finally, it designates our ‘id’ column as our unique primary key. Go ahead and click ‘Apply’ and ‘Finish’ to have MySQL run this script.

Let’s take a look at our new table. Double click on our schema in the bottom left, and then double click the ‘Tables’, where we should see our new table ‘game’. If you right click the ‘game’ table and ‘Select Rows’, you shouldn’t be surprised to see no results returned. We see that MySQL has created a query in the query window SELECT * FROM game_manager.game;. This query SELECTs all of our columns from our game table (designated by the *), and since there are no restrictions, returns all results (currently none).

Select all rows

Let’s change that by adding a few rows. Click the edit button to start adding new data. Remember, we are automatically generating IDs, and requiring the ‘name’ field. Once done, hit apply to see the SQL generated:

INSERT INTO `game_manager`.`game` (`name`, `description`) VALUES ('Ticket to Ride', 'Be your own robber barron creating train lines acorss the US.');
INSERT INTO `game_manager`.`game` (`name`, `description`) VALUES ('Formula D', 'A turn based racing game. Always be upshifting!');
INSERT INTO `game_manager`.`game` (`name`, `description`) VALUES ('Imperial Assault', 'Take on the Empire in this story driven turn based game.');
INSERT INTO `game_manager`.`game` (`name`) VALUES ('Mice and Mystics');

This script will INSERT new rows INTO our ‘game’ table. It designates which columns will be inserted, and the VALUES to be inserted into them. We see all rows have a ‘name’, but not all have a ‘description’. Go ahead and apply these changes. Now if we right click on our ‘game’ table and ‘Select Rows’ we should see our new data!

Writing Our Own SQL Queries

Let’s take a minute to understand some basic SQL queries. To get a much better understanding, check out the plethora of SQL tutorials online, like this one from W3 Schools. But for now, let’s just understand the basic operations we will want our application to do.

We’ve seen MySQL’s default query for returning all rows in our database:SELECT * FROM game_manager.game; But let’s try selecting just one game:

SELECT * 
FROM game_manager.game
WHERE game.name = 'Mice and Mystics';

This should return only one value. The WHERE clause provides criteria as to what rows to return.

We can also update values in our database. This can be done via the GUI, but let’s try writing our own:

UPDATE game_manager.game
SET description='All the rules of a tabletop RPG with none of the creative options'
WHERE game.name = 'Mice and Mystics';

This query will UPDATE our table, and SET a particular column to a new value, WHERE the following conditions are met. You will probably get an error from MySQL saying “Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column…” This is MySQL trying to prevent us from accidentally updating the wrong values in our table. The safest option is to use the ‘id’ field. Consider the hypothetical scenario where you had two games both named ‘Mice and Mystics’ but wanted to update a description for only a particular version. The above query would update All games named ‘Mice and Mystics’. Let’s update our query to use the ‘id’. We should see a success log at the bottom of your screen.

View our logs

Finally, let’s delete a row from our table that is no longer needed. The following query allows us to do so:

DELETE FROM game_manager.game
WHERE game.id = 4;

Wrap Up

In this tutorial, we setup a MySQL database with a single table and a few sample data rows. We took a look at some of the SQL MySQL Workbench generates for us in its GUI, as well as wrote some of our own. Ultimately, we’re going to want to build an application that manages our data for us using some popular Java tools. In the next post, we’ll create a Java application that will retrieve and manipulate our data for us.

Posted in Tutorial Tagged with: ,

Dozer Deep Property Mapping and Date Mapping Example

Dozer has Deep Mapping and String to Date Mapping available. This is a working example of using the two.

The bread and butter lives in the Dozer.xml

<?xml version="1.0" encoding="UTF-8"?>
<mappings xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
          xmlns="http://dozer.sourceforge.net"
          xsi:schemaLocation="http://dozer.sourceforge.net
          http://dozer.sourceforge.net/schema/beanmapping.xsd">

    <mapping>
        <class-a>com.recursivechaos.mappingtest.domain.SourceObj</class-a>
        <class-b>com.recursivechaos.mappingtest.domain.DestObj</class-b>

        <field>
            <a>nestedEnum.myEnum.value</a>
            <b>myEnum</b>
        </field>

        <field>
            <a>longDate</a>
            <b date-format="MM/dd/yyyy HH:mm:ss">longDate</b>
        </field>

        <field>
            <a>shortDate</a>
            <b date-format="MM/dd/yyyy">shortDate</b>
        </field>
    </mapping>

</mappings>

To do the deep mapping (in our case, we want to get a value from a nested enum), you just need to specify the path in the mapping like so:

<field>
    <a>nestedEnum.myEnum.value</a>
    <b>myEnum</b>
</field>

And finally, date formatting is a snap. Simply provide your date format with the field element:

<field>
    <a>longDate</a>
    <b date-format="MM/dd/yyyy HH:mm:ss">longDate</b>
</field>

This basic example can be found on GitHub

Posted in Examples Tagged with: ,

Angular Material Design with Yeoman Gulp-Angular

Having been really digging what’s happening with Google’s Material Design, and now having seen enough Bootstrap to make my eyes bleed, I was itching to try it out for myself. I’ve blogged previously about my love for Yeoman, and I’ve recently started using Gulp for my build process. Fortunately, Swiip has created the Gulp-Angular generator that we can use to easily start an Angular Material  project. I wanted to prototype a responsive and modern request form.

Without much effort I was able to build a simple form using Angular :

<md-content >

    <md-card layout layout-sm="column" layout-padding class="maincard">
      <h2>How do I get a key?</h2>
      <p>
        Fill out some quick details, and request yourself an API key!
      </p>
    </md-card>

    <md-card layout-padding class="maincard">
      <div ng-controller="FormCtrl" >
      <h2> Request a Key </h2>
        <form name="userForm" >

          <div layout layout-sm="column">
            <md-input-container flex>
              <label>First name</label>
              <input ng-model="user.firstName">
            </md-input-container>
            <md-input-container flex>
              <label>Last Name</label>
              <input ng-model="user.lastName">
            </md-input-container>
          </div>

          <md-input-container flex>
            <label>Company</label>
            <input ng-model="user.company">
          </md-input-container>

          <md-input-container flex>
            <label>Phone</label>
            <input ng-model="user.phone">
          </md-input-container>

          <section layout="row" layout-sm="column" >
            <md-button class="md-raised md-primary">Request Key</md-button>
          </section>
        </form>
      </div>
    </md-card>

    <md-card layout layout-sm="column" layout-padding  class="maincard" >
      <h2>Keymaker</h2>
      <p class="lead">
        <img src="assets/images/keymaker.jpg" class="cardnail" ><br>
        We do only what we're meant to do.
      </p>
    </md-card>

  </md-content>

Which gives us the form below:

keymaker
I pushed it to Github Pages, which is an easy way to build a website for your project. You push the code to a repository, and it’s hosted at your username.github.io. You can see keymaker running at andrewbell.github.io and you can find it on my repo.

Posted in Examples Tagged with: , , , ,

Chaining Angular Promises

Angular Promises can be a difficult topic to master, and I’ve created a project to help understand how promises work and how services can be built to use them. For our example, we need to make a series of http requests in order to complete our transaction.

Our MainController is responsible for posting a transaction when a user clicks our “Sale” button, update them of the status, and display a response. We want to abstract any of the business logic in making these requests, so we will utilize our reusable TransactionService to submit our transaction, and user our controller to manipulate our UI based on our response.

function MainController(TransactionService, $log) {
    var vm = this;

    vm.runTransaction = runTransaction;
    vm.resetStatus = resetStatus;
    vm.status = '';
    vm.response = '';

    function runTransaction() {
      $log.debug("main.runTransaction started...");
      vm.response = 'YOU MUST GATHER YOUR PARTY BEFORE VENTURING FORTH...';
      var transaction = {
        'card': '4XXXXXXXXXX1111'
      };

      TransactionService.postTransaction(transaction)
        .then(function (response) {
          $log.debug("postTransaction returned: " + response);
          vm.response = response;
          vm.status = 'success';
        }, function (response) {
          $log.debug("Post Transaction request failed: " + response);
          vm.response = response;
          vm.status = 'failure';
        });

      vm.status = 'submitted';
    }

Our TransactionService handles all of our business logic, and only needs to be aware of the transaction it needs to process. It first needs to fetch two responses from different resources, our url, and our token. When both of those promises are resolved, q.all.then() executes, which can call our GatewayService with those required parameters.

function TransactionService(UrlService, TokenService, GatewayService, $q, $log) {
  var that = this;

  that.postTransaction = postTransaction;
  that.gatewayUrl = '';
  that.token = '';
  that.response = '';

  function postTransaction(transaction) {
    $log.debug("TransactionService.postTransaction() called with card: " + transaction.card);
      var independentPromises = [UrlService.getUrl(), TokenService.getToken()];
      return $q.all(independentPromises)
        .then(function(results){
          $log.debug('Prerequisite requests success: ' + JSON.stringify(results));
          that.gatewayUrl = results[0];
          that.token = results[1];
          return GatewayService.postRequest(that.gatewayUrl, that.token, transaction);
        });
  }
}

function UrlService($http, $q, $log) {First, we make a call to our UrlService. It checks to see if we have a cached url, and if it does, resolves the promise and returns it immediately. If not, it has to fetch this from an http resource (simulated with a setTimeout and a get request to a file to simulate a response). It validates the http response for valid data, which gives us the ability to reject this request if we receive a valid response, but an unexpected response body.

function UrlService($http, $q, $log) {
    var that = this;
    that.url = '';
    that.getUrl = getUrl;

    function getUrl() {
      $log.debug("UrlService.getUrl called");
      return $q(function (resolve, reject) {
        if (that.url !== '') {
          $log.debug('Returning cached url: ' + that.url);
          resolve(that.url);
        } else {
          $log.debug('No cached url, making http request...');
          setTimeout(function () { // Artificial delay
            $http.get('app/resource/url.json')
              .then(function (result) {
                if (isValidResponse(result)) {
                  $log.debug("url get received valid response: " + JSON.stringify(result));
                  that.url = parseResponse(result);
                  resolve(that.url);
                } else {
                  $log.debug("Rejecting due to invalid response");
                  reject('Invalid URL Response.');
                }
              })
              .catch(function(result){
                  $log.debug('FAILED Token, result:' + JSON.stringify(result));
                  reject('URL Request Failed.');
              });
          }, 1000);
        }
      });

      function isValidResponse(result) {
        $log.debug('Checking valid response: ' + JSON.stringify(result));
        return true;
      }

      function parseResponse(result) {
        return result.data.gateway;
      }

    }

  }

The implementation of token service is almost identical, so for brevity’s sake, we’ll assume it functions similarly.

Lastly, we make a request to the GatewayService, passing in our url, token, and transaction. This functions like previous http requests, but in a real world scenario the $http.get() would use the aforementioned 3 parameters as part of the request.

This design delegates our UI to the MainController, the business logic to the TransactionService, and the implementation of our resources to our UrlService and TokenService. Promises ensure our dependent service, Gateway Service, has it’s necessary parameters to run.

Thanks to angularjs.reddit.com and /u/gabedamien for your help cleaning this up!

Posted in Examples Tagged with: ,

Spring Boot Dependency Injection Into External Library Project

Here is an example of Spring injection of a property using annotated configuration classes. Near the bottom, you will find links to github repositories for both projects.

We have a shared code library project, called injector-core, and we have a InjectorService that will return a greeting of our name, read from a property file. We could hypothetically implement this many places, and want to inject different properties into this service depending on what property file we give it. The @Service annotation, syntactic sugar of the @Component annotation. @Components will be detected by @ComponentScan located on an @Configuration file, and identified as a candidate for injection. @Value loads the ‘name’ entry from our properties files. This functionality is provided by our PropertySourcesPlaceholderConfigure @Bean, which is in also in the @Configuration file.

package com.recursivechaos.injectorcore.service;

import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;

@Service("injectorService")
public class InjectorServiceImpl implements InjectorService {

    @Value("${name}")
    public String name;

    @Override
    public String getGreeting() {
        return name;
    }
}

In order to test this service located in our library, we want to establish a @Configuration file, where we establish beans to inject our dependencies. We use @ComponentScan to scan the designated package for any @Component (or, in our case @Service) objects we will want to inject, and instantiate. We use @PropertySource to designate a property file source, from which we will get the ‘name’ property. We’ll create a PropertySourcesPlaceholderConfigurer @Bean in order to resolve our @Value.

package com.recursivechaos.test;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;
import org.springframework.context.support.PropertySourcesPlaceholderConfigurer;

@Configuration
@ComponentScan("com.recursivechaos.injectorcore")
@PropertySource("classpath:resources/application.properties")
public class TestApplicationContext {

    //To resolve ${} in @Value
    @Bean
    public static PropertySourcesPlaceholderConfigurer propertyConfigInDev() {
        return new PropertySourcesPlaceholderConfigurer();
    }

}

We can then utilize our injected @Service in our test. @RunWith(SpringJUnit4ClassRunner.class) provides our test with some Spring functionality. @SpringApplicationConfiguration(classes = TestApplicationContext.class) designates the @Configuration class to use. @Autowired attempt to find a suitable InjectorService, which is designated in the annotated @Service(“injectorService”). Spring will then create and inject this object for us. Now, we can use our service as normal to fetch our Greeting.

package com.recursivechaos.test;

import com.recursivechaos.injectorcore.service.InjectorService;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.SpringApplicationConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import static org.junit.Assert.assertEquals;

@RunWith(SpringJUnit4ClassRunner.class)
@SpringApplicationConfiguration(classes = TestApplicationContext.class)
public class InjectorServiceTest {

    @Autowired
    InjectorService injectorService;

    @Test
    public void testInjector() {

        assertEquals("Did not inject property", "Andrew", injectorService.getGreeting());

    }

}

Now that we have this library tested, it can be installed to the local Maven repository with a ‘mvn install’.

Now, we can switch to our implementation project of this library, injector-wrapper. Here we want to be able to use our InjectorService, and inject it with properties we define for this project. This too, is a @Configuration class, as it needs to inject the @Autowired injectorService with our @Service from our library.

@SpringBootApplication is a combination of a few common annotations to provide some of it’s automagic. One of those annotations is @ComponentScan, that scans it’s package level (“com.recursivechaos.injectorwrapper”) for any @Components. Since our InjectorService is part of our com.recursivechaos.injectorcore package, it would not be picked up by the default @SpringBootApplication behavior. We designate our @ComponentScan to search the parent package of ‘com.recursivechaos’, so it will pick up the @Service notation in the other package. @Autowired works the same in the service, and our application greets us with our injected property!

Spring Boot automatically scans the classpath for an application.properties, which contained our name property.

package com.recursivechaos.injectorwrapper;

import com.recursivechaos.injectorcore.service.InjectorService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;

// Removed @SpringBootApplication and replaced with the necessary  annotations
// and set the ComponentScan to the parent package.
@Configuration
@ComponentScan("com.recursivechaos")
public class InjectorWrapperApplication implements CommandLineRunner {

    @Autowired
    InjectorService injectorService;

    public static void main(String[] args) {
        SpringApplication.run(InjectorWrapperApplication.class, args);
    }

    @Override
    public void run(String... strings) throws Exception {
        System.out.println("Hello " + injectorService.getGreeting());
    }
}

Spring Boot is a great tool for rapidly creating Spring based applications, but the automagic that Spring preforms can obscure the Spring implementation. In order to override some of this automatic configuration, understanding Spring’s implementation is important. For someone new to Spring, or like myself, coming from the days of mostly XML configuration, much of this can look foreign. One of the benefits of Spring is it’s years of support and documentation, but also covers a wide range of configuration options that can be overwhelming. Even most of Spring’s documentation relies heavily on XML configuration.

Code:

https://github.com/AndrewBell/injector-core

https://github.com/AndrewBell/injector-wrapper

Sources:

Springs JavaDocs

http://docs.spring.io/autorepo/docs/spring/current/javadoc-api/overview-summary.html

http://docs.spring.io/spring-boot/docs/current/api/overview-summary.html

Spring Documentation

http://docs.spring.io/spring/docs/current/spring-framework-reference/htmlsingle

Mykong

http://www.mkyong.com/spring/spring-is-not-working-in-value

Posted in Examples Tagged with: , , , ,

Dice and Dicebags – A dice rolling utility

Inspired by this prompt in /r/ProgrammingPrompts, I built a dice rolling utility. A user can roll one, or many dice at once, including multiple faces, and different numbers of sides. A user can hold dice, and re-roll others. It supports different types of dice, including “Fate” dice, coins, and my current addiction, X-Wing Miniatures dice. This way I could use it in conjunction with my Xciting Wing project. It follows pretty strict SOLID principles, and makes this overkill for what it does, but a great example of SOLID practices nonetheless. Since this was only a service, I relied on JUnit for Test Driven Development.

https://github.com/AndrewBell/DiceAndDicebags

Posted in Projects Tagged with: , ,

A quick map in CartoDB

I was playing around with some public data, and decided to give CartoDB a shot. It was pretty easy to get started, and there’s a ton of functionality built in.

http://cdb.io/1rdd6Ea

Posted in Examples Tagged with: ,

Java + AngularJS for Modern Web Development

Coming from an almost strictly Java background, and wanting to move into web development can be overwhelming. The world of functional programming alone can be a struggle for someone who’s not experienced with it. Using Java Servlets and JSP to build gonger, a previous project, was a daunting task, and is a dated process for modern web development.

I would need something to be the front end, and AngularboredgamesJS came highly recommended. I mocked up a demo app using Yeoman to scaffold an AngularJS project, complete with development server provided by grunt, and dependency management by bower. Yeoman’s documentation does a great job of walking through the use of these tools, but for the most part, Yeoman configures everything you’ll need to start coding. Angular has a great tutorial online, and I felt at least somewhat prepared to try my hand at my own project.

On the back-end, I wanted to see what libraries were out that could make the process of developing a REST API easier for someone new. spring.io, and JBoss RESTEasy both looked pretty well supported, but I found difficult to approach. I actually found Dropwizard the most approachable. Most assume prior knowledge of the underlying frameworks, and I end up watching a bit of this Jersey tutorial, to understand the basics. I also found Play Framework really slick. Their use of Activator is really simple to use, it can generate it’s own tutorial hosted inside the very web service you created, complete with a built in IDE. The one downside, is I felt like it’s support for strictly Java 8 and AngularJS generators were somewhat lacking. Finally, the last tool I’ve been looking at is JHipster, which is actually a yeoman build that combines an AngularJS frontend, with a Spring backend, and seems to be pretty well put together.

I ended up giving dropwizard a try, and created an REST API, for my AngularJS front end.

Posted in Examples Tagged with: , , ,