Connect to database with spring boot + spring jpa and CRUD operation

Introduction

--Last time, I made an api that just returns a string in Create a web api server with spring boot. --This time, based on the previous code, we will create an api that performs CRUD (Create, Read, Update, Delete) operations on the data. --The database uses the mysql container created in Launch mysql container with docker-compose in local environment. --Implement a function that works once without considering error handling etc.

environment

• macOS Mojave 10.14.6
• Java openjdk 12.0.1
• Spring Boot 2.1.7
• Intellij IDEA CE 2019.1
• docker 18.09.2
• docker-compose 1.24.1

Data to handle

--The following data is handled with an image like the backend server of the simplified version of Twitter.

Image of api to create

Package configuration

--This time, we will create the code with the following package structure.

src/main/java/com/example/api
.
├── ApiApplication.java
├── model
│   └── Tweet.java
├── repository
│   └── TweetRepository.java
├── service
│   └── TweetService.java
└── controller
    └── TweetController.java

--model: Represents an entity (the data to be handled) --repository: Access the DB -- service: Execute business logic --controller: Process request / response from client

build.gradle settings

--Add a library to make it easier to exchange data with the database --Add the following to the dependencies of build.gradle

• org.springframework.boot:spring-boot-starter-data-jpa
  A library that provides an api (JPA: Java Persistence API) for operating RDB with java.
• mysql:mysql-connector-java
  A library that provides an api (JDBC: Java Database Connectivity) for connecting to a mysql database with java
• org.projectlombok:lombok
  A library that automatically generates frequently used code such as getter / setter and constructor with java See also Lombok Note

build.gradle

	implementation 'org.springframework.boot:spring-boot-starter-data-jpa'
	implementation 'mysql:mysql-connector-java'

	compileOnly 'org.projectlombok:lombok:1.18.8'
	annotationProcessor 'org.projectlombok:lombok:1.18.8'

DB connection settings

--Create ʻapplication.properties or ʻapplication.yml under src / main / resources and enter the information of the DB to connect to.

application.yml

spring:
  datasource:
    sqlScriptEncoding: UTF-8
    driverClassName: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://127.0.0.1:3306/testdb?useSSL=false&requireSSL=false
    username: root
    password: hoge
  #Avoid initializing the database when the application starts
  jpa:
    hibernate:
      ddl-auto: update
    #Address to the problem that table cannot be created in DB when spring boot starts
    properties:
      hibernate:
        dialect: org.hibernate.dialect.MySQL57Dialect
logging:
  level:
    sql: debug

--The reason why the setting of spring.jpa.properties.hibernate.dialect is included is that the table cannot be created when the application is started and the following error occurs. Solved by referring to here

org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL "create table tweet (tweet varchar(255) not null, primary key (tweet)) engine=MyISAM" via JDBC Statement
...(Omitted)...
Caused by: java.sql.SQLSyntaxErrorException: Specified key was too long; max key length is 1000 bytes
...(Omitted)...

Create / Read

--First, implement the function to save / retrieve tweets in the database. --At the time of Create, name and message in the request body are saved in DB, and the saved contents are returned as a response as it is. --At the time of Get, all the data stored in DB is acquired and returned as a response.

Create model

--Create a class that represents the tweet itself under the model package

Tweet.java

package com.example.api.model;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import javax.persistence.*;

@Entity
@Data
@NoArgsConstructor
@AllArgsConstructor
@Table(name = "tweet")
public class Tweet {
    @Id
    @GeneratedValue
    private int id;

    @Column(nullable = false)
    private String name;

    @Column(nullable = false)
    private String message;
}

--@Entity: Indicates that it is a JPA entity (object saved in DB) --@Data / @NoArgsConstructor / @AllArgsConstructor: Annotation of lombok. Automatically create getters, setters, constructors, etc. --@Table (name = "tweet") : Specify the table name corresponding to the entity --@Id: Annotation indicating that it is the primary key of the entity --@GeneratedValue: With this, DB will automatically number. --@Column (nullable = false): Annotation given to the column of DB. You can set whether to allow nulls with the nullable option

Creating repository

--Create a repository class under the repository package --The methods required for CRUD operations are included in JpaRepository, so all you have to do is create an interface that inherits from JpaRepository.

TweetRepository.java

package com.example.api.repository;

import com.example.api.model.Tweet;
import org.springframework.data.jpa.repository.JpaRepository;

public interface TweetRepository extends JpaRepository<Tweet, Integer> {}

Create service

--Create a service class under the service package --By adding @Autowired to tweetRepository, the instance is passed from the DI container and you can call the method without new. --The postTweet method receives the Tweet and calls the save method of the tweetRepository. --The getTweet method calls the findAll method of tweetRepository.

TweetService.java

package com.example.api.service;

import com.example.api.model.Tweet;
import com.example.api.repository.TweetRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

@Service
@Transactional
public class TweetService {
    @Autowired
    TweetRepository tweetRepository;

    public Tweet postTweet(Tweet tweet) {
        return tweetRepository.save(tweet);
    }

    public List<Tweet> getTweet() {
        return tweetRepository.findAll();
    }
}

--@Service: Indicates that it is a service class of spring MVC. Target of DI (Dependency Injection) --@Transactional: Annotation for transaction control --@Autowired: Annotation attached to the injection target of DI container

Creating a controller

--Create a controller class under the controller package --Using the @RequestMapping () annotation, the postTweet method is called when the POST method is called for the path tweet, and the getTweet method is called when it is called by the GET method. --The postTweet method uses the @RequestBody annotation to map the body of the HTTP request to the Tweet object and pass it to the tweetService's postTweet method. After executing tweetService.postTweet (), the result is returned.

TweetController.java

package com.example.api.controller;

import com.example.api.model.Tweet;
import com.example.api.service.TweetService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.List;

@RestController
@RequestMapping("tweet")
public class TweetController {
    @Autowired
    TweetService tweetService;

    @RequestMapping(method = RequestMethod.POST)
    Tweet postTweet(@RequestBody Tweet tweet) {
        return tweetService.postTweet(tweet);
    }

    @RequestMapping(method = RequestMethod.GET)
    List<Tweet> getTweet() {
        return tweetService.getTweet();
    }
}

--@RestController: Annotation given to the controller class --@RequestMapping (): Annotation for accepting HTTP requests. Specify the path to accept the request and the HTTP method --@RequestBody: Annotation that maps the body of the request to the specified object

Operation check

--Launch the application and execute the following command from the terminal

# post request
$ curl -X POST -H 'Content-Type:application/json' -d '{"name":"name", "message":"message"}' localhost:8080/tweet

# response
{"id":1,"name":"name","message":"message"}

--Check the database and it's OK if the requested content is saved

mysql> select * from tweet;
+----+-----------------+--------+
| id | message         | name   |
+----+-----------------+--------+
|  1 |message|name|
+----+-----------------+--------+
1 row in set (0.01 sec)

--Next, execute the following command from the terminal to check if the contents of the database can be obtained.

# get request
curl -X GET -H 'Content-Type:application/json' localhost:8080/tweet

# response
[{"id":1,"name":"name","message":"message"}]

--I was able to get the records in the database!

Update / Delete

--Next, implement the function to update / delete the tweet with the id specified by / tweet / {id}. --JpaRepository already implements the methods used for update / delete, so you don't need to modify the repository. (Just call from service)

fix service

--Add the following method in the class of TweetService.java

TweetService.java

    public Tweet updateTweet(Tweet tweet) {
        return tweetRepository.save(tweet);
    }

    public void deleteTweet(Integer id) {
        tweetRepository.deleteById(id);
    }

--update uses the same save () method as create --delete uses the deleteById () method to delete the entity specified by id

Fix controller

--Add the following method in the class of TweetController.java

TweetController.java

    @RequestMapping(value = "{id}", method = RequestMethod.PUT)
    Tweet putTweet(@PathVariable("id") Integer id, @RequestBody Tweet tweet) {
        tweet.setId(id);
        return tweetService.updateTweet(tweet);
    }

    @RequestMapping(value = "{id}", method = RequestMethod.DELETE)
    void deleteTweet(@PathVariable("id") Integer id) {
        tweetService.deleteTweet(id);
    }

--Specify {id} in the value option of @RequestMapping and set the corresponding method to be called when requested by/ tweet / {id}. --Map the variable {id} in the URL to the Integer id variable with the @PathVariable annotation. --When updating, set the id with the setTweet () method and then pass it to the updateTweet () method of tweetService.

Operation check

--Launch the application and execute the following command from the terminal

# put request
$ curl -X PUT -H 'Content-Type:application/json' -d '{"name":"Updated name", "message":"Updated message"}' localhost:8080/tweet/1

# response
{"id":1,"name":"Updated name","message":"Updated message"}

--Check the database and make sure the data is updated

mysql> select * from tweet;
+----+-----------------------------+--------------------+
| id | message                     | name               |
+----+-----------------------------+--------------------+
|  1 |Updated message|Updated name|
+----+-----------------------------+--------------------+
1 row in set (0.01 sec)

--Finally, execute the following command from the terminal to confirm the deletion of data.

# delete request
curl -X DELETE -H 'Content-Type:application/json' localhost:8080/tweet/1

#no response

--Check the database and it's OK if the data has been deleted!

mysql> select * from tweet;
Empty set (0.00 sec)

reference

-[First Spring Boot-Easy Java application development with "Spring Framework" (I / O BOOKS)](https://www.google.com/aclk?sa=L&ai=DChcSEwi-55Swm6XkAhWIq5YKHWZlA1AYABAJGgJ0bA&sig=AOD64_0Pw9z3 0ahUKEwjymo-wm6XkAhVsHKYKHUhJC40Q9A4I0gE & adurl =)

• Spring boot/Hibernate fails creating table