Simple CRUD Using Spring Boot, JPA and MySQL

You can view and download the complete source code of this tutorial from my github account.

The demo for the API is available on https://crudbudget.herokuapp.com/api.

In this tutorial, we will create a simple CRUD (Create Read Update Delete) backend API to manage Expenses using Spring Boot, JPA and MySQL.

This API will then be consumed by frontend web or app.

For this tutorial, we will need the following tools: (The older or newer version should also works).

  1. Spring Tool Suite (I use v3.8.2)
  2. MySQL Community Server and MySQL Workbench (GUI Tool)
  3. Postman to test the API call

Project Setting

First, we need to create the project using Spring Initializr (https://start.spring.io/), a web based tool to create Spring Boot projects.

Fill out the form with the following information:

  • Project : Maven Project
  • Language : Java
  • Spring Boot : 2.1.5
  • Group : com.danielniko
  • Artifact : crudbudget
  • Options : Packaging –> Jar, Java –> 8
  • Dependencies : Web, JPA, MySQL

Do not worry if you forgot to add dependencies in this form as you can easily add then in the pom.xml file later on.

Click “Generate the project” to download the project zip file and extract it to your Spring Tool Suite (STS) workspace.

Open STS and import the file by selecting File –> Import –> Maven –> Existing Maven Projects.

  • Root Directory : (browse to the extracted zip file)
  • Make sure pom.xml is checked.

For other input, just go with the default value, then click “Finish”.

If there is no error in setting, wait for several minutes as STS will download all dependencies needed.

Common Error that can occurs at the time of this writing.

Error : Unknown (Line 1 in pom.xml)

Solution : This happen in spring boot 2.1.5.RELEASE, so just downgrade to 2.1.3.RELEASE. Modify the version to 2.1.3.RELEASE.

Right click on the project in the Package Explorer and Choose Maven –> Update Project. Accept the default values and click “OK”.

Now, try to Run the application. Choose Run –> Run Configurations… on the Top menu.

Choose “Spring Boot App” from the left tree and click the new button icon.

  • Name : crudbudget
  • Project : crudbudget
  • Main type : Choose CrudBudgetApplication

Apply and click “Run” and watch the console for any error messages.

If you got the following error : Failed to configure a DataSource: ‘url’ attribute is not specified and no embedded datasource could be configured, it is to be expected because we have not configure the database connection yet.


Database Creation and Connection

Spring Boot actually support database, tables, relationship creation automatically when running the application. However, this is not always a good case as sometimes we are given a fixed database structure from the beginning.

I this tutorial, we will create the database manually.

Create new file “schema.sql” in src/main/resources with the following content. This file will not do anything in Spring Boot other than just an information for DDL creation.


create database crudbudget;
create table expense (
    expense_id int auto_increment,
    name varchar(30) not null,
    description varchar(200),
    category_id int not null,
    amount decimal(13,2) not null,
    created_date timestamp not null,
    modified_date timestamp,
    primary key (expense_id)
);
create table category (
    category_id int auto_increment,
    name varchar(30) not null,
    description varchar(200),
    created_date timestamp,
    modified_date timestamp,
    primary key (category_id)
);
alter table expense 
add foreign key (category_id) references category(category_id)
;
insert into category (name, description, created_date, modified_date)
values('test','This is a test category',curdate(), curdate())
;

Execute the above sql commands on your MySQL workbench to create our tables: Expense and Category with one Category can have many Expenses. We also insert one sampe category to test the inquiry later on.

Next, open “application.properties” file in src/main/resources and input the following :


## Spring DATASOURCE
spring.datasource.url = jdbc:mysql://localhost:3306/crudbudget?useSSL=false&allowPublicKeyRetrieval=true
spring.datasource.username = your_db_username
spring.datasource.password = your_db_password

## Hibernate Properties
# The SQL dialect makes Hibernate generate better SQL for the chosen database
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5InnoDBDialect

## Hibernate Logging
logging.level.org.hibernate.SQL= DEBUG

Make sure your database is up and running and your database credential is correct and try to run “crudbudget” again.

Furthermore, your pom.xml file should contain the following dependencies when you create the project using Spring Initializr. If not, just add these dependencies.

		<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>

If there are no error messages, that means Spring Boot can succesfully connect to your DB. Now we can start program the application logic.


Programming JPA and REST endpoints

Create new classes, Expense.java and Category.java inside com.danielniko.crudbudget.domain to hold Expense and Category entity/domain

package com.danielniko.crudbudget.domain;

import java.util.Date;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.EntityListeners;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;

import org.springframework.data.annotation.CreatedDate;
import org.springframework.data.annotation.LastModifiedDate;
import org.springframework.data.jpa.domain.support.AuditingEntityListener;

@Entity
@EntityListeners(AuditingEntityListener.class)
public class Expense {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long expenseId;
    private String name;
    private String description;
    private double amount;
    
    @Column(name = "created_date", nullable = false, updatable = false)
    @CreatedDate
    private Date createdDate;

    @Column(name = "modified_date")
    @LastModifiedDate
    private Date modifiedDate;
    
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "categoryId")
    private Category category;
	
    public Expense() {}
    
    public Expense(String name, String description, Category category, double amount) {
    	this.name = name;
    	this.description = description;
    	this.amount = amount;
    	this.category = category;
    }

	public Long getExpenseId() {
		return expenseId;
	}

	public void setExpenseId(Long expenseId) {
		this.expenseId = expenseId;
	}

	public String getDescription() {
		return description;
	}

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

	public String getName() {
		return name;
	}

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

	public double getAmount() {
		return amount;
	}

	public void setAmount(double amount) {
		this.amount = amount;
	}

	public Category getCategory() {
		return category;
	}

	public void setCategory(Category category) {
		this.category = category;
	}

	public Date getCreatedDate() {
		return createdDate;
	}

	public void setCreatedDate(Date createdDate) {
		this.createdDate = createdDate;
	}

	public Date getModifiedDate() {
		return modifiedDate;
	}

	public void setModifiedDate(Date modifiedDate) {
		this.modifiedDate = modifiedDate;
	}

	@Override
	public String toString() {
		return "Expense [expenseId=" + expenseId + ", name=" + name + ", description=" + description + ", amount="
				+ amount + "]";
	}
	
}

 

package com.danielniko.crudbudget.domain;

import java.util.Date;
import java.util.List;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.OneToMany;

import org.springframework.data.annotation.CreatedDate;
import org.springframework.data.annotation.LastModifiedDate;

import com.fasterxml.jackson.annotation.JsonIgnore;
import com.fasterxml.jackson.annotation.JsonIgnoreProperties;

@Entity
@EntityListeners(AuditingEntityListener.class)
@JsonIgnoreProperties({"hibernateLazyInitializer", "handler"})
public class Category {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long categoryId;
	
    private String name;
    private String description;
	
    @Column(name = "created_date", nullable = false, updatable = false)
    @CreatedDate
    private Date createdDate;

    @Column(name = "modified_date")
    @LastModifiedDate
    private Date modifiedDate;
	
	@OneToMany(cascade = CascadeType.ALL, mappedBy="category")
	@JsonIgnore
    private List<Expense> expenses;

	public Long getCategoryId() {
		return categoryId;
	}
	public void setCategoryId(Long categoryId) {
		this.categoryId = categoryId;
	}
	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;
	}
	public List<Expense> getExpenses() {
		return expenses;
	}
	public void setExpenses(List<Expense> expenses) {
		this.expenses = expenses;
	}
	public Date getCreatedDate() {
		return createdDate;
	}
	public void setCreatedDate(Date createdDate) {
		this.createdDate = createdDate;
	}
	public Date getModifiedDate() {
		return modifiedDate;
	}
	public void setModifiedDate(Date modifiedDate) {
		this.modifiedDate = modifiedDate;
	}
	@Override
	public String toString() {
		return "Category [categoryId=" + categoryId + ", name=" + name + "]";
	}

}

We use several annotations in those 2 classes. First we use @Entity to let Spring know that this is entity class and should be used to in JPA operations to represent tables in database.

Next, we use @EntityListeners(AuditingEntityListener.class) to automatically insert timestamp on created_date during INSERT and modified_date on UPDATE operation (Usually for auditing purposes). This annotation is paired with @Column@CreatedDate and @LastModifiedDate. In order for this to work, we need to also enable the auditing in our application class. Open CrudbudgetApplication.java and add @EnableJpaAuditing

package com.danielniko.crudbudget;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.data.jpa.repository.config.EnableJpaAuditing;

@SpringBootApplication
@EnableJpaAuditing
public class CrudbudgetApplication {

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

}

Currently, we dont annotate properties such as name, description, etc with @Column because we want to keep it simple. However, it is better to annotate your properties with @Column to include more attributes such as max length, nullable or default value.

Moreover, we use @ManyToOne in Expense and @OneToMany in Category because 1 Category can have many Expenses. Next we use @JoinColumn  to complete Foreign Key relationship between two entities which we already set in the previous SQL statement.

Next, create classes to be used as data access object to perform data manipulation. Fortunately , Spring already provides common CRUD operations so we just extend CrudRepository to access the methods.

Create ExpenseRepository.class and CategoryRepository.class inside com.danielniko.crudbudget.repository package.

package com.danielniko.crudbudget.repository;

import org.springframework.data.repository.CrudRepository;

import com.danielniko.crudbudget.domain.Expense;

public interface ExpenseRepository extends CrudRepository <Expense, Long> {

}

package com.danielniko.crudbudget.repository;

import org.springframework.data.repository.CrudRepository;

import com.danielniko.crudbudget.domain.Category;

public interface CategoryRepository extends CrudRepository <Category, Long> {

}

Finally, we can create the REST endpoint. Spring provides direct access to REST data endpoint without we manually create controller for each entity.

By adding the base path for the rest endpoint, we can directly perform CRUD manipulation on the entities.

Open again your application.properties file and add the following entry at the bottom. We use “/api” as the base path.

spring.data.rest.basePath=/api

Next, add the following dependency on your pom.xml

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

Now run “crudbudget” again. Open localhost:8080/api and see how Spring automatically provides REST endpoints for our entities.

{
  "_links" : {
    "expenses" : {
      "href" : "http://localhost:8080/api/expenses"
    },
    "categories" : {
      "href" : "http://localhost:8080/api/categories"
    },
    "profile" : {
      "href" : "http://localhost:8080/api/profile"
    }
  }
}

We can even insert directly using POST method. Open your postman and use the following information :
POST : localhost:8080/api/categories
Use application/json as header and add raw Request Body as follow

{
 "name":"danielniko",
 "description":"from postman"
}

Press send and view the console log in Spring Tool Suite and the data will be inserted succesfully.
The response generated is as follows.

{
    "name": "danielniko",
    "description": "from postman",
    "createdDate": "2019-06-10T09:29:16.557+0000",
    "modifiedDate": "2019-06-10T09:29:16.557+0000",
    "_links": {
        "self": {
            "href": "http://localhost:8080/api/categories/8"
        },
        "category": {
            "href": "http://localhost:8080/api/categories/8"
        },
        "expenses": {
            "href": "http://localhost:8080/api/categories/8/expenses"
        }
    }
}


As you can see, the created date and modified date is inserted automatically. It is a very good functionality from Spring to enable fast timestamp auditing.
For expense, it is quite trickier because we need to supply the Category object too inside the request body
POST : localhost:8080/api/expenses

{
	"name":"expense test",
	"description":"from postman",
	"category" : "http://localhost:8080/api/categories/8",
	"amount":"500000"
}