Category: Spring

bookmark_borderSpring: Data JPA with MySQL

Posted on by Jay Jo
  1. Prerequisite
  2. Started with Spring Initializr
  3. Create database
  4. Setting up application.properties for MySQL
  5. Create the @Entity Model
  6. Create the Repository
  7. Create a Controller
  8. Execute Application
  9. Test Application
  10. Check MySQL database

Prerequisite

  • IDE (Integrated development environment)
  • JDK 8 or later
  • Maven 3.2+
  • MySQL version 5.6 or later

Started with Spring Initializr

Add Dependencies 추가

By Spring Initializr, Dependencies set up.

Dependencies: Spring Web (Spring Web Starter) Spring Data JPA, and MySQL Driver

  • Check Dependencies: Spring Web, Spring Data JPA, and MySQL Driver
<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-web</artifactId>
</dependency>

<dependency>
	<groupId>mysql</groupId>
	<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>

Create the database

$ sudo mysql --password

Add database and user setting

mysql> create database db_example; -- Creates the new database
mysql> create user 'springuser'@'%' identified by 'ThePassword'; -- Creates the user
mysql> grant all on db_example.* to 'springuser'@'%'; -- Gives all privileges to the new user on the newly created database

database name: db_example, user: springuser, password: ThePassword

Check database is created

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| db_example         |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
  • db_example database is created.

Setting up application.properties for MySQL

*Spring boot default Database is H2. So, if you want to use another DataBase, you should set up a connection in application.properties.

spring.jpa.hibernate.ddl-auto=update
spring.datasource.url=jdbc:mysql://${MYSQL_HOST:localhost}:3306/db_example?serverTimezone=Asia/Seoul&characterEncoding=UTF-8
spring.datasource.username=springuser
spring.datasource.password=ThePassword
  • database Time zone is seoul.
  • Spring is by Entity Scan to find @Entity annotaion Class.
    • spring.jpa.generate-ddl=true. if option is true, when server is started, create DDL and set up Database.
    • If you use Hibernate, you have option for set up details by spring.jpa.hibernate.ddl-auto. (none: default, create-drop: when SessionFactory is started, execute drop and create and when SessionFactory is ended, execute drop. And there are create, update, validate.

spring.jpa.hibernate.ddl-auto= none in production.

Create the @Entity Model

Create User.java in src/main/java/com/example/jpamysql

package com.example.jpamysql;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;

@Entity // This tells Hibernate to make a table out of this class
public class User {
  @Id
  @GeneratedValue(strategy=GenerationType.AUTO)
  private Integer id;

  private String name;

  private String email;

  public Integer getId() {
    return id;
  }

  public void setId(Integer id) {
    this.id = id;
  }

  public String getName() {
    return name;
  }

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

  public String getEmail() {
    return email;
  }

  public void setEmail(String email) {
    this.email = email;
  }
}
  • Hibernate automatically translates the entity into a table.

Create the Repository

Create UserRepository.java in src/main/java/com/example/jpamysql

package com.example.jpamysql;

import org.springframework.data.repository.CrudRepository;

import com.example.jpamysql.User;

// This will be AUTO IMPLEMENTED by Spring into a Bean called userRepository
// CRUD refers Create, Read, Update, Delete

public interface UserRepository extends CrudRepository<User, Integer> {

}
  • Spring automatically implements the repository interface in a bean that the same name.
    • It is called userRepository

Create a Controller

Create MainController.java in src/main/java/com/example/jpamysql

package com.example.jpamysql;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;

@Controller // This means that this class is a Controller
@RequestMapping(path="/demo") // This means URL's start with /demo (after Application path)
public class MainController {
  @Autowired // This means to get the bean called userRepository
         // Which is auto-generated by Spring, we will use it to handle the data
  private UserRepository userRepository;

  @PostMapping(path="/add") // Map ONLY POST Requests
  public @ResponseBody String addNewUser (@RequestParam String name
      , @RequestParam String email) {
    // @ResponseBody means the returned String is the response, not a view name
    // @RequestParam means it is a parameter from the GET or POST request

    User n = new User();
    n.setName(name);
    n.setEmail(email);
    userRepository.save(n);
    return "Saved";
  }

  @GetMapping(path="/all")
  public @ResponseBody Iterable<User> getAllUsers() {
    // This returns a JSON or XML with the users
    return userRepository.findAll();
  }
}

Execute Application

In root directory

$ mvn package
$ java -jar target/board-0.0.1-SNAPSHOT.jar

Test Application

the example have two HTTP endpoints.

GET localhost:8080/demo/all

POST localhost:8080/demo/add

$ curl localhost:8080/demo/add -d name=First -d email=someemail@someemailprovider.com

return Saved

$ curl 'localhost:8080/demo/all'

return [{“id”:1,”name”:”First”,”email”:”someemail@someemailprovider.com”}]

Check MySQL database

mysql> select * from db_example;
mysql> select * from User;
+----+---------------------------------+-------+
| id | email                           | name  |
+----+---------------------------------+-------+
|  1 | someemail@someemailprovider.com | First |
+----+---------------------------------+-------+

Resource: https://spring.io/guides/gs/accessing-data-mysql/

ANOTE.DEV