Interacting with MySQL database using spring-boot rest api

UPDATED: 05 April 2018

In previous articles we have seen Creating first rest api in spring-boot and also Passing and validating RequestParam in spring-boot rest api now we will take one step further by interacting with database. Browse all spring tutorials here.

Project structure

build.gradle
update your build.gradle, add following dependencies and refresh the project, gradle will download the required dependencies(jar files).
// database dependencies
compile('org.springframework.boot:spring-boot-starter-data-jpa')
runtime('mysql:mysql-connector-java')
application.properties
Create new source folder under src/main/resources if not exist and add new file called application.properties. Add following lines for database connection.
#datasource configurations
spring.datasource.driverClassName=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/spring_boot_examples
spring.datasource.username=root
spring.datasource.password=root
spring.jpa.show-sql=true
#spring.jpa.properties.hibernate.format_sql=true
Now create table users in database. Create table sql script is available on github.

Source code (User.java)
We used DatabaseEntity and ModifiableEntity interfaces from Java Interfaces. Also we used Java Constraint Validation on each column.
Note: In current example we are using spring boot 1.5.7.RELEASE which uses validations framework 1.1.0. @NotEmpty, @Email are part of package org.hibernate.validator. But spring boot 2.0.0 and above which supports validation framework 2.0 so @NotEmpty, @Email, etc... are now part of javax.validation.constraints.
import java.util.Date;
import java.util.UUID;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Table;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;
import javax.validation.constraints.Size;
import org.hibernate.annotations.GenericGenerator;
import org.hibernate.validator.constraints.NotEmpty;
/**
 * @author javaQuery
 * @since 2018-02-18
 * @github https://github.com/javaquery/spring-boot-examples
 */
@Entity
@Table(name = "users")
public class User implements DatabaseEntity, ModifiableEntity{
    @Id
    @GeneratedValue(generator = "uuid2")
    @GenericGenerator(name = "uuid2", strategy = "uuid2")
    @Column(name = "id", columnDefinition = "BINARY(16)")
    private UUID id;
   
    @Column(name = "first_name")
    @NotEmpty(message = "first_name can not be empty.")
    @Size(max = 100, message = "first_name can not be more than 100 characters.")
    private String firstName;
    
    @Column(name = "last_name")
    @NotEmpty(message = "last_name can not be empty.")
    @Size(max = 100, message = "last_name can not be more than 100 characters.")
    private String lastName;
    
    @Column(name = "email")
    @NotEmpty(message = "email can not be empty.")
    @Size(max = 100, message = "email can not be more than 100 characters.")
    private String email;
    
    @Column(name = "password")
    @NotEmpty(message = "password can not be empty.")
    @Size(max = 100, message = "password can not be more than 100 characters.")
    private String password;
    
    @Temporal(TemporalType.TIMESTAMP)
    @Column(name = "created")
    private Date created = new Date();
    
    @Temporal(TemporalType.TIMESTAMP)
    @Column(name = "modified")
    private Date modified  = new Date();
    
    //getter-setter
}

Abstraction in Spring
Spring is all about abstraction via interfaces. Abstraction allows you to change the implementation without affecting caller method.

Repositories
The goal of Spring Data repository abstraction is to significantly reduce the amount of boilerplate code required to implement data access layers for various persistence stores.
Spring Repository(interface) is abstraction for data access layer.

For example in your project you are going to use data storage like MySQL(RDBMS), MongoDb (NoSQL), etc... The caller class will interact to data storage using repository(interface) so for caller class it doesn't matter which storage you are using in implementation of repository either MySQL or MongoDb as long as data persist and retrieve back.

Here we are using JpaRepository for which spring will provide default implementation of basic SQL queries (INSERT, UPDATE, DELETE, SELECT) without writing it yourself.

Source code (UserRepository.java)
import java.util.UUID;
import org.springframework.data.jpa.repository.JpaRepository;
import com.javaquery.examples.springboot.model.User;
/**
 * @author javaQuery
 * @since 2018-02-18
 */
public interface UserRepository extends JpaRepository<User, UUID>{
}
Now if you want to save record (User), all you got to do is call userRepository.save(user);. Calling save on UserRepository will generate INSERT statement and execute it. Similarly you can perform update, select and delete.

Source code (Application.java)
Add @EntityScan and EnableJpaRepositories in your Application.java.
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.domain.EntityScan;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;

/**
 * @author javaQuery
 * @since 2018-01-31
 * @github https://github.com/javaquery/spring-boot-examples
 */
@SpringBootApplication
@ComponentScan(basePackages = { "com.javaquery.examples.springboot.rest" })
@EntityScan("com.javaquery.examples.springboot.model")
@EnableJpaRepositories("com.javaquery.examples.springboot.model.repositories")
public class Application {
    public static void main(String[] args) {
       SpringApplication.run(Application.class, args);
    }
}
  • @EntityScan - Location(package) of your database entities
  • @EnableJpaRepositories - Location(package) of your code which interact with database (repositories)

Services
Service is abstraction layer for application logic implementation.

Source code (UserService.java)
UserDTO / UserUpdateDTO are request objects (JSON Payload). Source code is available under controller section.
import java.util.UUID;
import com.javaquery.examples.springboot.rest.dto.UserDTO;
import com.javaquery.examples.springboot.rest.dto.UserUpdateDTO;
/**
 * @author javaQuery
 * @since 2018-02-18
 * @github https://github.com/javaquery/spring-boot-examples
 */
public interface UserService {
    public UserDTO addUser(UserDTO userDTO);
    public UserDTO getUser(UUID id);
    public UserDTO updateUser(UserUpdateDTO userUpdateDTO, UUID id);
    public void deleteUser(UUID id);
}
Source code (UserServiceImpl.java)
import java.util.Date;
import java.util.Objects;
import java.util.UUID;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.javaquery.examples.springboot.model.User;
import com.javaquery.examples.springboot.model.repositories.UserRepository;
import com.javaquery.examples.springboot.rest.dto.UserDTO;
import com.javaquery.examples.springboot.rest.dto.UserUpdateDTO;
import com.javaquery.examples.springboot.rest.exception.EntityNotFoundException;
import com.javaquery.examples.springboot.rest.service.UserService;
/**
 * @author javaQuery
 * @since 2018-02-18
 * @github https://github.com/javaquery/spring-boot-examples
 */
@Service
public class UserServiceImpl implements UserService{

    @Autowired
    private UserRepository userRepository;
 
    @Override
    public UserDTO addUser(UserDTO userDTO) {
    Date now = new Date();
  
    /**
     * We are manually creating {@link User} object however there is mapper available  to convert to-from {@link UserDTO}.
     */
    User user = new User();
    user.setFirstName(userDTO.getFirstName());
    user.setLastName(userDTO.getLastName());
    user.setEmail(userDTO.getEmail());
    user.setPassword(userDTO.getPassword());
    user.setCreated(now);
    user.setModified(now);
    userRepository.save(user);
 
    /* set generated user id to response object */
    userDTO.setId(user.getId());
    userDTO.setPassword("");
    return userDTO;
   }

   @Override
   public UserDTO getUser(UUID id) {
    User user = userRepository.getOne(id);
    if(Objects.isNull(user)){
     /* handle this exception using {@link RestExceptionHandler} */
     throw new EntityNotFoundException(User.class, id);
    }
    return new UserDTO().build(user);
   }

   @Override
   public UserDTO updateUser(UserUpdateDTO userUpdateDTO, UUID id) {
    User user = userRepository.getOne(id);
    if(Objects.isNull(user)){
     /* handle this exception using {@link RestExceptionHandler} */
     throw new EntityNotFoundException(User.class, id);
    }
    user.setFirstName(userUpdateDTO.getFirstName());
    user.setLastName(userUpdateDTO.getLastName());
    userRepository.save(user);
    return new UserDTO().build(user);
   }

   @Override
   public void deleteUser(UUID id) {
    userRepository.delete(id);
   }
}

Controller (REST)
Controller is the entry point for all REST request.

Source code (UserDTO.java)
import java.util.UUID;
import org.hibernate.validator.constraints.Email;
import org.hibernate.validator.constraints.NotEmpty;
import javax.validation.constraints.Size;
import com.fasterxml.jackson.annotation.JsonIgnoreProperties;
import com.fasterxml.jackson.annotation.JsonInclude;
import com.fasterxml.jackson.annotation.JsonProperty;
import com.fasterxml.jackson.annotation.JsonInclude.Include;
import com.javaquery.examples.springboot.model.User;
/**
 * Object to interact using rest api.
 * @author javaQuery
 * @since 2018-02-18
 */
@JsonIgnoreProperties(ignoreUnknown = true)
@JsonInclude(Include.NON_NULL)
public class UserDTO {
 
    /* used to send database id */
    private UUID id;
 
    @NotEmpty(message = "first_name can not be empty")
    @JsonProperty("first_name")
    private String firstName;
 
    @NotEmpty(message = "last_name can not be empty")
    @JsonProperty("last_name")
    private String lastName;
    
    @Email
    @JsonProperty("email")
    private String email;
    
    @NotEmpty(message = "password can not be empty")
    @Size(min = 6, message = "password must be at least 6 character")
    @JsonProperty("password")
    private String password;
   
    // getter - setter
    
    /**
     * We are manually creating {@link UserDTO} object however there is mapper available 
     * to convert to-from {@link User}.
     * @param user
     * @return
     */
    public UserDTO build(User user){
     this.id = user.getId();
     this.firstName = user.getFirstName();
     this.lastName = user.getLastName();
     this.email = user.getEmail();
     return this;
    }
}

Source code (UserUpdateDTO.java)
import org.hibernate.validator.constraints.NotEmpty;
import com.fasterxml.jackson.annotation.JsonProperty;
/**
 * Object to interact using rest api.
 * @author javaQuery
 * @since 2018-02-18
 */
public class UserUpdateDTO {
    @NotEmpty(message = "first_name can not be empty")
    @JsonProperty("first_name")
    private String firstName;
 
    @NotEmpty(message = "last_name can not be empty")
    @JsonProperty("last_name")
    private String lastName;
 
    // getter - setter
}

Source code (UserController.java)
Http request POST, GET, PUT and POST are implemented in controller.
import java.util.UUID;
import javax.validation.Valid;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.DeleteMapping;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.PutMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import com.javaquery.examples.springboot.rest.dto.UserDTO;
import com.javaquery.examples.springboot.rest.dto.UserUpdateDTO;
import com.javaquery.examples.springboot.rest.response.SuccessResponse;
import com.javaquery.examples.springboot.rest.service.UserService;
/**
 * @author javaQuery
 * @since 2018-02-18
 */
@RestController
@RequestMapping("/api/user")
public class UserController {
 
    @Autowired
    private UserService userService; 
 
    @PostMapping
    public ResponseEntity<UserDTO> addUser(@Valid @RequestBody UserDTO userDTO){
     return ResponseEntity.ok(userService.addUser(userDTO));
    }
 
    @GetMapping
    public ResponseEntity<UserDTO> getUser(@RequestParam UUID id){
     return ResponseEntity.ok(userService.getUser(id));
    }
 
    @PutMapping
    public ResponseEntity<UserDTO> updateUser(@Valid @RequestBody UserUpdateDTO userUpdateDTO, @RequestParam UUID id){
     return ResponseEntity.ok(userService.updateUser(userUpdateDTO, id));
    }
 
    @DeleteMapping
    public ResponseEntity<?> deleteUser(@RequestParam UUID id){
     userService.deleteUser(id);
     return ResponseEntity.ok(new SuccessResponse("deleted"));
    }
}

Note: Add updated EntityNotFoundException.java and RestExceptionHandler.java from github.
Request/Response
cURL POST http://localhost:8080/api/user
-body {"first_name":"vicky","last_name":"thakor","email":"vicky.thakor@javaquery.com","password":"123456"}
-code 200
-body {"id":"95572033-9cf1-421a-94a7-dc4cdf7e9c71","first_name":"vicky","last_name":"thakor","email":"vicky.thakor@javaquery.com","password":""}
cURL GET http://localhost:8080/api/user?id=95572033-9cf1-421a-94a7-dc4cdf7e9c71
-code 200
-body {"id":"95572033-9cf1-421a-94a7-dc4cdf7e9c71","first_name":"vicky","last_name":"thakor","email":"vicky.thakor@javaquery.com"}
cURL PUT http://localhost:8080/api/user?id=95572033-9cf1-421a-94a7-dc4cdf7e9c71
-body {"first_name":"chirag","last_name":"thakor"}
-code 200
-body {"id":"95572033-9cf1-421a-94a7-dc4cdf7e9c71","first_name":"chirag","last_name":"thakor","email":"vicky.thakor@javaquery.com"}
cURL DELETE http://localhost:8080/api/user?id=95572033-9cf1-421a-94a7-dc4cdf7e9c71
-code 200
-body {"message": "deleted"}

0 comments :