Web Service using mySql DB

Preparation

  1. Create a basic web Service with the name accounts-service.

  2. Launch mySql server in Docker:

$ docker run --name demo-mysql -e MYSQL_ROOT_PASSWORD=password -e MYSQL_DATABASE=demo -e MYSQL_USER=demo_user -e MYSQL_PASSWORD=demo_pass -p 3306:3306 -d mysql:5.6

Validate that mySql has successfully started by looking into docker container logs:

$ docker logs demo-mysql
...
2016-01-25 04:52:44 1 [Note] mysqld: ready for connections.
Version: '5.6.28'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  MySQL Community Server (GPL)

Adding JPA

The solution to this exercise part can be found in solution1 folder.

Add data model:

src/main/java/msvcdojo/AccountsServiceApplication.java
@Entity
class Account {

    public String getUsername() {
        return username;
    }

    public Long getId() {
        return id;
    }

    @Id
    @GeneratedValue
    private Long id;

    private String username;

    Account() { // JPA only
    }

    public Account(String username) {
        this.username = username;
    }

    @Override
    public String toString() {
        final StringBuilder sb = new StringBuilder("User{");
        sb.append("id=").append(id);
        sb.append(", username='").append(username).append('\'');
        sb.append('}');
        return sb.toString();
    }
}

Add data repository:

src/main/java/msvcdojo/AccountsServiceApplication.java
@RepositoryRestResource
interface AccountRepository extends JpaRepository<Account, Long> {
    List<Account> findByUsername(@Param("username") String username);
}

Add JPA dependency to build.

build.gradle
compile("org.springframework.boot:spring-boot-starter-data-jpa:$springBootVersion")
compile("org.springframework.boot:spring-boot-starter-data-rest:$springBootVersion")
runtime("mysql:mysql-connector-java:5.1.38")

Add SQL connection to application.yml

src/main/resouces/application.yml
spring:
  jpa:
    database: MYSQL
    hibernate:
      ddl-auto: validate

  datasource:
    url: jdbc:mysql://dockerhost:3306/demo
    username: demo_user
    password: demo_pass
    driver-class-name: com.mysql.jdbc.Driver
You’re free to run your service, but, if you don’t have DB schema provisioned, you’re going to see a spectacular crash of your service with an exception that may look like this: HibernateException: Missing table: account

Adding Flyway DB migrations (Part 1)

We can not yet run the application because the database doesn’t exist yet. So we’re going to use a best-practice of Continuous Delivery - DB schema versioning and migrations.

In real life you may want to manage DB migrations separately from the source code of the service. Mostly make sure that the DB migrations are not happening on the service launch but as a separate intentionnal process.

_1. Add plugin dependency

build.gradle
classpath("org.flywaydb:flyway-gradle-plugin:3.2.1")
classpath("mysql:mysql-connector-java:5.1.38")

_2. Add plugin

build.gradle
apply plugin: 'org.flywaydb.flyway'

_3. Add Flyway migrations build section

build.gradle
flyway {
    url = 'jdbc:mysql://dockerhost:3306/demo'
    user = 'demo_user'
    password = 'demo_pass'
}

_4. Add V1 migration - initialization

src/main/resources/db/migration/V1__init.sql
CREATE TABLE account (
  id BIGINT NOT NULL AUTO_INCREMENT,
  username varchar(255) NOT NULL,
  PRIMARY KEY (id)
);

INSERT INTO account (username) VALUES ('john');
Avoid putting data and configuration in the schema migrations at all costs. This example is for demo purposes only.

_5. Apply V1 migration

$ gradlew flywayMigrate

If, at this moment, you’ll check the mySql server, you’ll find a "demo" database with a single row of data like this:

id username

1

john

Play time (part 1)

Now it’s time to run the service.

$ java -jar build\libs\accounts-service-0.0.1.jar
You can use your favorite browser to navigate REST endpoints by following http://localhost:8100/

Let’s hit the root endpoint:

$ curl http://localhost:8100/

You’ll see the following output:

{
  "_links" : {
    "accounts" : {
      "href" : "http://localhost:8100/accounts{?page,size,sort}",
      "templated" : true
    },
    "profile" : {
      "href" : "http://localhost:8100/profile"
    }
  }
}

Note the response is coming in the HATEOAS (Hypermedia as the Engine of Application State) which is a constraint of the REST application architecture.

Read more on HATEOAS in Understanding HATEOAS Spring guide.

Following the accounts path:

$ curl http://localhost:8100/accounts

Produces the following result:

{
  "_embedded" : {
    "accounts" : [ {
      "username" : "john",
      "_links" : {
        "self" : {
          "href" : "http://localhost:8100/accounts/1"
        },
        "account" : {
          "href" : "http://localhost:8100/accounts/1"
        }
      }
    } ]
  },
  "_links" : {
    "self" : {
      "href" : "http://localhost:8100/accounts"
    },
    "profile" : {
      "href" : "http://localhost:8100/profile/accounts"
    },
    "search" : {
      "href" : "http://localhost:8100/accounts/search"
    }
  },
  "page" : {
    "size" : 20,
    "totalElements" : 1,
    "totalPages" : 1,
    "number" : 0
  }
}

To get information about a single account you navigate to the item’s resource URI:

$ curl http://localhost:8100/accounts/1

The result will look like this:

{
  "username" : "john",
  "_links" : {
    "self" : {
      "href" : "http://localhost:8100/accounts/1"
    },
    "account" : {
      "href" : "http://localhost:8100/accounts/1"
    }
  }
}

Now let’s, in fact, try to use this service for some real work by adding more records to the database - you have to escape the double quotes in the echo command:

$ echo {\"username\" : \"dave\"} | curl -H "Content-type:application/json" -d @- http://localhost:8100/accounts

This produces the following output:

{
  "username" : "dave",
  "_links" : {
    "self" : {
      "href" : "http://localhost:8100/accounts/2"
    },
    "account" : {
      "href" : "http://localhost:8100/accounts/2"
    }
  }
}

Let’s run a query to find the new record:

$ curl http://localhost:8100/accounts/search/findByUsername?username=dave

If, you’ve followed all the steps carefully, you’ll be rewarded with the following result:

{
  "_embedded" : {
    "accounts" : [ {
      "username" : "dave",
      "_links" : {
        "self" : {
          "href" : "http://localhost:8100/accounts/2"
        },
        "account" : {
          "href" : "http://localhost:8100/accounts/2"
        }
      }
    } ]
  },
  "_links" : {
    "self" : {
      "href" : "http://localhost:8100/accounts/search/findByUsername?username=dave"
    }
  }
}

Adding Flyway DB migrations (Part 2)

In the normal lifecycle of the live system you’re going to modify the data models multiple times and will have a need to migrate the database alongside.

Here we’re going to add a new field role to the account table where we’re going to track their roles.

The solution to this exercise part can be found in solution2 folder.

Add a second migration (to V2):

src/main/resources/db/migration/V2__add_role.sql
ALTER TABLE account
ADD role varchar(255);

UPDATE account SET role = 'admin'
WHERE username = 'john';

Add the role mapping to the data model:

src/main/java/msvcdojo/AccountsServiceApplication.java
private String role;
public String getRole() { return role; }

And allow search by this new field in the data repository class:

src/main/java/msvcdojo/AccountsServiceApplication.java
List<Account> findByRole(@Param("role") String role);

Now, when you’ll start the service, the DB will look like this:

id username role

1

john

admin

2

dave

Let’s try to query by the new field:

$ curl http://localhost:8100/accounts/search/findByRole?role=admin

And the result will look like this:

{
  "_embedded" : {
    "accounts" : [ {
      "username" : "john",
      "role"     : "admin",
      "_links" : {
        "self" : {
          "href" : "http://localhost:8100/accounts/1"
        },
        "account" : {
          "href" : "http://localhost:8100/accounts/1"
        }
      }
    } ]
  },
  "_links" : {
    "self" : {
      "href" : "http://localhost:8100/accounts/search/findByRole?role=admin"
    }
  }
}

Adding tests

Don’t forget about tests.

tests that use external Database are part of the integration and E2E (end-to-end) test suits. They require external infrastructure and data seeding. They are prone to failures and hard to maintain.

Here is an example of a test that validates presence of the data in the Database after migration:

src/test/java/msvcdojo/AccountsServiceApplicationTests.java
@RunWith(SpringJUnit4ClassRunner.class)
@SpringApplicationConfiguration(AccountsServiceApplication.class)
public class AccountsServiceApplicationTests {

    @Autowired
    private JdbcTemplate template;

    @Test
    public void testDefaultSettings() throws Exception {
        assertEquals(new Integer(1), this.template
                .queryForObject("SELECT COUNT(*) from account WHERE username='john'", Integer.class));
    }

}

Dockerize Accounts Service

$ gradlew prepDocker
$ docker build -t msvcdojo/accounts-service:0.0.1 build/docker

Bonus