Monday, June 24, 2019

How to use Spring Boot with MySQL database


Spring Framework simplifies working with databases by auto configuring connections, handling transactions, using ORM tool like hibernate, abstract sql by Spring Data Repository. We are going to focus on how to connect MySQL database with Spring Boot application.

Sprint Boot has many defaults. For databases, H2 in-memory database is the default database. It auto-configures in-memory databases even without connection url. Those are good for simple testing. For production use we need to use a database like MySQL.

Spring Boot selects HickariCP Datasource due to it is performance. When spring-boot-starter-data-jpa dependency in classpath it automatically pick HickariCP.

complete source code this blog post is here.

How to configure a database

Obviously, to use a database in our application we need;
  • Database driver to connect to database
  • Connection url
  • Database username and password
In Spring Boot application we need to provide atleast connection url, otherwise it will try to configure in-memory database. Using connection url it can deduce the database driver to be used. So we do not need to configure database driver.

To configure above properties, Spring externalize configuration properties using spring.datasource.*.
spring.datasource.url = jdbc:mysql://localhost/test
spring.datasource.username = dbuser
spring.datasource.password = dbpassword
spring.datasource.driver-class-name = com.mysql.jdbc.Driver // no need

If we need more fine tuning we can use other configuration properties like spring.datasource.hikari.*.

How to auto-create a database

If we like to let the application create the database for us, we can use spring.jpa.hibernate.ddl-auto property. This value is none for MySQL and create-drop for embedded databases.
spring.jpa.hibernate.ddl-auto = create

Additionally if schema.sql (DDL) and data.sql (DML) files are in resouces folder Spring Boot can pick those and populate database. We can change default location by using schema and data properties.
spring.datasource.initialization-mode = always
spring.datasource.schema = classpath:/database/schema.sql # Schema (DDL) script resource references. = classpath:/database/data.sql # Data (DML) script resource references.

Using above knowledge let's create a simple application which connects to MySQL database.

Create our database

I like to create database separately. Connect to MySQL database and create our database.
> mysql -uroot -proot
> create database book_store;
> use book_store;
// use schema.sql and data.sql to populate database

Create the project

Go to and select spring-boot-starter-data-jpa, spring-boot-starter-web, mysql-connector-java and lombok dependencies.
pom.xml will be look like this.




Configure the database

Create applicatin.yml and add below configuration properties.
      ddl-auto: validate
    url: jdbc:mysql://localhost:3306/book_store
    username: root
    password: root

This is all we need to connect to MySQL database. Let's create our domain object and repository.
public class Book implements Serializable {

    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    private String title;
    private String author;


public interface BookRepository extends JpaRepository<Book, Integer> {

Service layer

public class BookServiceImpl implements BookService {

    private final BookRepository bookRepository;

    public BookServiceImpl(BookRepository bookRepository) {
        this.bookRepository = bookRepository;

    public List<Book> findAll() {
        return bookRepository.findAll();

    public Book findById(Integer id) {
        return bookRepository.findById(id).orElse(null);

    public Book save(Book book) {

    public void delete(Integer id) {

    public Book update(Book book) {


Rest controller layer

public class BookController {

    private final BookService bookService;

    public BookController(BookService bookService) {
        this.bookService = bookService;

    @GetMapping(value = "")
    public List<Book> findAll() {
        return bookService.findAll();

    @GetMapping(value = "/{id}")
    public Book findById(@PathVariable Integer id) {
        return bookService.findById(id);

    public Book save(@RequestBody Book book) {

    @DeleteMapping(value = "/{id}")
    public void delete(@PathVariable Integer id) {

    public Book update(@RequestBody Book book) {


Now start the application and try below cURL commands.
curl -X GET http://localhost:7070/

curl -X POST \
  http://localhost:7070/ \
  -H 'Content-Type: application/json' \
  -d '{
    "title": "Java Persistence with Hibernate",
    "author": "Gavin King"

curl -X GET http://localhost:7070/1

That is the basics you want know when working with relational database with Spring Boot. You can use below references for further study.


Sunday, June 23, 2019

Microservices - Distributed tracing with Spring Cloud Sleuth and Zipkin


Microservices are very flexible. We can have multiple microservices for each domain and interact as necessary. But it comes with a price. It becomes very complex when the number of microservices grow.
Imagine a situation where you found a bug or slowness in the system. How do you find the root cause by examinig logs?

  • Collect all the logs from related microservices.
  • Pick the starting microservice and find a clue there using some id (userid, businessid, etc).
  • Pick the next microservice and check whether the previous information are there.
  • Keep going until you find which microservice has the bug.

I have followed that practise in one of my previous projects. It is very difficult and takes a lot of time to track an issue.
This is why we need to use distributed tracing in microservices. One place where we can go and see the entire trace.

It helps us by;
  • Asign unique id (correlation id) to all request.
  • Pass unique id across all the microservices automatically.
  • Record time information.
  • Log service name, unique id, span id.
  • Aggregate log data from multiple microservices into single source.

Spring Cloud Sleuth

Spring Cloud Sleuth implements a distributed tracing solution for Spring Cloud. We can capture data simply using logs or send data to a collector service like Zipkin. 
Just by adding the library into our project Spring Cloud Sleuth can;
  • Add correlation id to all request if it doesn't exist.
  • Pass the id with outbound call.
  • Add correlation information to Spring's Mapped Diagnostic Context (MDC) which internally use SL4J and Logback implementations.
  • If the collector service is configured, it can pass the log information to it.

Adding Spring Cloud Sleuth

This is very simple. We need to update our pom.xml files to include the Sleuth dependency. Let's update api-gateway, service-a and service-b pom files with this.

Now re-start applications and visit http://localhost:7060/api/service-a/

Look at the logs. In service-a you will be able to see;
2019-06-23 14:45:41.024  INFO [service-a,50937d2183890546,fc6079712896add8,false] 15445 --- [io-7000-exec-10] c.s.s.controller.MessageController       : get message

In service-b
2019-06-23 14:45:41.033  INFO [service-b,50937d2183890546,260506a7161eca33,false] 15654 --- [io-7005-exec-10] c.s.s.controller.MessageController       : serving message from b

You can see in logs it has [service_name, traceId, spanId, exportable] format. Both logs have same correlation id printed. Exportable is false because we haven't added our log tracing server yet.
Let's add it.

Zipkin Server

Zipkin is a distributed tracing system. It helps gather timing data needed to troubleshoot latency problems in microservice architectures.

We used Sleuth to add tracing information in our logs. Now we are going to use Zipkin to visualize it.

Zipkin server as a Docker container

We can find the docker command from the official site to run Zipkin server as a docker container.
docker container run -d -p 9411:9411 openzipkin/zipkin

Now our Zipkin server is available at http://localhost:9411/zipkin/

Let's add Zipkin dependency to api-gateway, service-a and service-b.


Then we need to specify where to send our tracing data. Update application.yml in api-gateway, service-a and service-b as below.
    name: service-a
    baseUrl: http://localhost:9411/
      probability: 1.0

Re-start applications and visit http://localhost:7060/api/service-a/
You will be able see exportable true this time.

Now visit http://localhost:9411/zipkin/ and click on 'Firnd Traces'. You will be able see tracing information and click on it.

Now if you click on a service name it will give you more information like below.

That is it for now. You have your base tracing module to play with.