[JAVA] 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

Data to handle

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

item name Mold Description
id int ID unique to the tweet
name String The name of the person who tweeted
message String Tweet content

Image of api to create

url HTTP method Description
/tweet POST Save a tweet
/tweet GET Get all tweets
/tweet/{id} PUT Update tweets with the specified id
/tweet/{id} DELETE Delete the tweet with the specified id

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

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

Recommended Posts

Connect to database with spring boot + spring jpa and CRUD operation
Attempt to SSR Vue.js with Spring Boot and GraalJS
Connect Spring Boot and Angular type-safely with OpenAPI Generator
Database linkage with doma2 (Spring boot)
Implement REST API with Spring Boot and JPA (Application Layer)
Implement REST API with Spring Boot and JPA (Infrastructure layer)
Until INSERT and SELECT to Postgres with Spring boot and thymeleaf
Implement REST API with Spring Boot and JPA (domain layer)
8 things to insert into DB using Spring Boot and JPA
HTTPS with Spring Boot and Let's Encrypt
Implement CRUD with Spring Boot + Thymeleaf + MySQL
Add spring boot and gradle to eclipse
Implementation method for multi-data source with Spring boot (Mybatis and Spring Data JPA)
How to use MyBatis2 (iBatis) with Spring Boot 1.4 (Spring 4)
How to use built-in h2db with spring boot
Try to implement login function with Spring Boot
Create CRUD apps with Spring Boot 2 + Thymeleaf + MyBatis
Try to automate migration with Spring Boot Flyway
[Java] Article to add validation with Spring Boot 2.3.1.
I wanted to gradle spring boot with multi-project
[Introduction to Spring Boot] Authentication function with Spring Security
Download with Spring Boot
Settings for connecting to MySQL with Spring Boot + Spring JDBC
Testing JPA entities and repositories using Spring Boot @DataJpaTest
Try using DI container with Laravel and Spring Boot
Switch environment with Spring Boot application.properties and @Profile annotation
Automatically map DTOs to entities with Spring Boot API
Try using OpenID Connect with Keycloak (Spring Boot application)
Spring Security usage memo: Cooperation with Spring MVC and Boot
I started MySQL 5.7 with docker-compose and tried to connect
Spring Boot with Spring Security Filter settings and addictive points
I tried to get started with Spring Data JPA
How to boot by environment with Spring Boot of Maven
Database environment construction with Docker in Spring boot (IntellJ)
Output embedded Tomcat access log to standard output with Spring Boot
Try Spring Boot from 0 to 100.
Generate barcode with Spring Boot
Hello World with Spring Boot
Implement GraphQL with Spring Boot
Handle Java 8 date and time API with Thymeleaf with Spring Boot
Spring Boot Introductory Guide I tried [Accessing Data with JPA]
Get started with Spring boot
Hello World with Spring Boot!
Extract SQL to property file with jdbcTemplate of spring boot
Run LIFF with Spring Boot
SNS login with Spring Boot
Introduction to Spring Boot ① ~ DI ~
File upload with Spring Boot
Spring Boot starting with copy
Introduction to Spring Boot ② ~ AOP ~
How to call and use API in Java (Spring Boot)
Connect to DB with Java
Connect to MySQL 8 with Java
Spring Boot starting with Docker
Flow until output table data to view with Spring Boot
Hello World with Spring Boot
Set cookies with Spring Boot
Use Spring JDBC with Spring Boot
With Spring boot, password is hashed and member registration & Spring security is used to implement login function.
Add module with Spring Boot
Getting Started with Spring Boot