RESTful CRUD With Java and MySQL in Minutes

I keep receiving so many requests for an article on how to access MySQL database from Android application. Even though Android applications can have direct access to MySQL database server, that is not preferred due to security issues and high complexity. The easy and best solution is developing a REST web service to receive the requests from whatever the client (including Android applications) and execute them on the database connected with the web service.

There are so many articles already available on the Internet about how to create a web service to perform Create, Read, Update & Delete (CRUD) operations on a database and how to connect from an Android client. However, I wondered why most of them are using PHP to develop the web service! I believe developers find PHP a less painful language to develop web services than Java. In this article, I am going to show how to develop a RESTful CRUD Web service with Java in minutes. As a matured language Java has enough frameworks to make your task easy. I guarantee that you will never look back for a different language once you know the tools.

Prerequisite:

Step 1:
Create a new simple Maven Project with the following properties:
Group Id: com.javahelps
Artifact Id: mysql-rest-service

Step 2:
Open the pom.xml and modify it as given below. In this step, we define this project as a Spring Boot project along with the dependencies to connect the database. 
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>com.javahelps</groupId>
    <artifactId>mysql-rest-service</artifactId>
    <version>0.0.1-SNAPSHOT</version>

    <!-- Define the parent pom -->
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.5.8.RELEASE</version>
    </parent>

    <!-- Set the Java version -->
    <properties>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <!-- Spring Boot -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <!-- JPA Data -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>

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

        <!-- MySQL Connector-J -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
    </dependencies>

    <!-- Spring Boot Maven Plugin -->
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
</project>
After the modification, save the changes, right click on the project and select Maven → Update Project... to update the project. In the appeared dialog, just click OK.

Step 3:
In this step, we are going to create a configuration file that tells Spring Framework about how to connect the database.
Create a new file named application.properties inside the src/main/resource directory with the following content.
# Automatically update the database
spring.jpa.hibernate.ddl-auto=update

# The database connection URL
spring.datasource.url=jdbc:mysql://localhost:3306/user_db?useSSL=false

# Username
spring.datasource.username=root

# Password
spring.datasource.password=root

# Define the database platform
spring.jpa.database-platform=org.hibernate.dialect.MySQL5InnoDBDialect

# Define the naming strategy
spring.jpa.hibernate.naming-strategy = org.hibernate.cfg.ImprovedNamingStrategy

# Define the default schema
spring.jpa.properties.hibernate.default_schema=schema
In this file, only properties you need to consider are the spring.datasource.URL, spring.datasource.username and spring.datasource.password. Here user_db is the database name.

Step 4:
Open PhpMyAdmin or whatever the MySQL client you prefer and create an empty database user_db.
CREATE DATABASE user_db;
We have just created the database only. Tables will be automatically created at the runtime based on our Java entity classes.

Step 5:
Create a package com.javahelps.restservice in the src/main/java directory and a class Application inside the package.
package com.javahelps.restservice;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class Application {

    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }
}
This class is the starting point of our application. It is decorated as a SpringBootApplication. Running this class will run the application as a web service in a stand-alone server.

Step 6:
In the src/main/java directory, create a new package com.javahelps.restservice.entity and create a new class User. In the upcoming steps, we will develop CRUD services for this entity. 
package com.javahelps.restservice.entity;

import javax.persistence.Entity;
import javax.persistence.Id;

@Entity
public class User {

    @Id
    private String username;
    private String password;
    private String name;
    private String email;

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

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

    @Override
    public String toString() {
        return "User{" + "username='" + username + '\'' + ", password='" + password + '\'' + ", name='" + name + '\''
                + ", email='" + email + '\'' + '}';
    }
}
Here the class is decorated with the @Entity annotation and the primary key is decorated with the @Id annotation.

Step 7:
Create another package com.javahelps.restservice.repository in the src/main/java directory and create a new interface UserRepository.
package com.javahelps.restservice.repository;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.rest.core.annotation.RepositoryRestResource;

import com.javahelps.restservice.entity.User;

@RepositoryRestResource(path = "/users")
public interface UserRepository extends JpaRepository<User, String> {

}
This interface extends JpaRepository and the two generic types defined are the entity class and the type of its primary key. In this example, the entity class is User and its primary key username is String. This is a magical interface which provides all the CRUD implementations. You do not need to implement this interface anywhere. Instead, Spring Boot will provide the implementation for you.

The @RepositoryRestResource annotation marks this interface as a REST resource serving the /users URL.

Step 8:
After creating the repository, right click on the project and select Run As → Java Application. If asked, select the Application class as the main class to execute.


Step 9:
Try the REST APIs exposed by the UserRepository. You can create a new user using POST request and read all the users by sending a GET request to the same URL. To see the available functionalities, visit the http://localhost:8080/profile/users URL.

POST request to create a new user

GET request to read all users

If you are looking for a basic RESTful CRUD web service, you can stop here. The web service is up and running with all required CRUD operations. See! we have a class and an interface. Our web service is ready.

Step 10:
Even though our RESTful web service is up and running, I prefer to have more control over the API without losing the advantage of auto-generated CRUD operations. Spring Boot offers Controllers to define our own REST API. Before implementing our own controller, let's hide the UserRepository from the public.
Replace the @RepositoryRestResource(path = "/users") annotation with @RestResource(exported = false) annotation. This annotation informs Spring Boot not to export the methods as REST endpoints.
package com.javahelps.restservice.repository;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.rest.core.annotation.RestResource;

import com.javahelps.restservice.entity.User;

@RestResource(exported = false)
public interface UserRepository extends JpaRepository<User, String> {

}

Step 11:
Create a new package com.javahelps.restservice.controller in the src/main/java directory and create a new class UserController inside the package.
package com.javahelps.restservice.controller;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.DeleteMapping;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
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.RestController;

import com.javahelps.restservice.entity.User;
import com.javahelps.restservice.repository.UserRepository;

import javassist.tools.web.BadHttpRequest;

@RestController
@RequestMapping(path = "/users")
public class UserController {

    @Autowired
    private UserRepository repository;

    @GetMapping
    public Iterable<User> findAll() {
        return repository.findAll();
    }

    @GetMapping(path = "/{username}")
    public User find(@PathVariable("username") String username) {
        return repository.findOne(username);
    }

    @PostMapping(consumes = "application/json")
    public User create(@RequestBody User user) {
        return repository.save(user);
    }

    @DeleteMapping(path = "/{username}")
    public void delete(@PathVariable("username") String username) {
        repository.delete(username);
    }

    @PutMapping(path = "/{username}")
    public User update(@PathVariable("username") String username, @RequestBody User user) throws BadHttpRequest {
        if (repository.exists(username)) {
            user.setUsername(username);
            return repository.save(user);
        } else {
            throw new BadHttpRequest();
        }
    }

}
This class is decorated as a RestController and the UserRepository instance is injected using @Autowired annotation.

Step 12:
Save all the changes and run the application again. The following table summarizes all the endpoints and how they must be accessed.
HTTP Method URL Input Description
POST http://localhost:8080/users application/json
{
 "username": "admin",
 "password": "password",
 "name": "Gobinath",
 "email": "slgobinath@gmail.com"
}
Create a new user admin
GET http://localhost:8080/users N/A Read all users
GET http://localhost:8080/users/admin Path parameter (Note the URL ending with admin) Read the user with username 'admin'
PUT http://localhost:8080/users/admin Path parameter (Note the URL ending with admin)
application/json
{
 "username": "admin",
 "password": "admin",
 "name": "Gobinath",
 "email": "newemail@gmail.com"
}
Update the user admin
DELETE http://localhost:8080/users/admin Path parameter (Note the URL ending with admin) Delete the user with username 'admin'
Step 13:
As a bonus step, I explain how to add basic authentication to the REST API using the database. Add the following dependencyto the pom.xml file.
<!-- Spring Security -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-security</artifactId>
</dependency>

Create a new class SecurityConfig in the com.javahelps.restservice package.
package com.javahelps.restservice;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Configuration;
import org.springframework.security.config.annotation.authentication.builders.AuthenticationManagerBuilder;
import org.springframework.security.config.annotation.web.builders.HttpSecurity;
import org.springframework.security.config.annotation.web.configuration.EnableWebSecurity;
import org.springframework.security.config.annotation.web.configuration.WebSecurityConfigurerAdapter;
import org.springframework.security.core.authority.AuthorityUtils;
import org.springframework.security.core.userdetails.UsernameNotFoundException;

import com.javahelps.restservice.entity.User;
import com.javahelps.restservice.repository.UserRepository;

@Configuration
@EnableWebSecurity
public class SecurityConfig extends WebSecurityConfigurerAdapter {

    @Autowired
    private UserRepository userRepository;

    @Autowired
    protected void configureGlobal(AuthenticationManagerBuilder auth) throws Exception {
        auth.userDetailsService(username -> {
            User user = userRepository.findOne(username);
            if (user != null) {
                return new org.springframework.security.core.userdetails.User(user.getUsername(), user.getPassword(),
                        true, true, true, true, AuthorityUtils.createAuthorityList("USER"));
            } else {
                throw new UsernameNotFoundException("Could not find the user '" + username + "'");
            }
        });
    }

    @Override
    protected void configure(HttpSecurity http) throws Exception {
        http.authorizeRequests().anyRequest().fullyAuthenticated().and().httpBasic().and().csrf().disable();
    }
}
This class contains the Autowired UserRepository to retrieve User objects using the username and add a UserDetailsService using the username and password. As the authority list, you can return any user roles. If you have user roles defined in your database, it is better to use them. Otherwise, you can hardcode any user roles as I have defined USER here.
The configure method configures the authentication process. It enables basic authentication to all requests and disables the CSRF header check since it will cause 403 Forbidden HTTP error when sending any requests other than GET.

Now open the Postman and try the GET method. You will get 401 without authentication. Enabling Basic Authentication will let you access the REST endpoints.

GET request with invalid password

GET request with valid username and password

If there were no users when you start the application, no one can access the REST endpoints since there will not be any users to authenticate. As a solution, either you have to manually create a new user in the database or else modify the Application.java as shown below to create a new user if does not exist at the system startup.
package com.javahelps.restservice;

import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.Bean;

import com.javahelps.restservice.entity.User;
import com.javahelps.restservice.repository.UserRepository;

@SpringBootApplication
public class Application {

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

    @Bean
    protected CommandLineRunner init(final UserRepository userRepository) {

        return args -> {
            User user = new User();
            user.setUsername("admin");
            user.setPassword("admin");
            user.setName("Administrator");
            user.setEmail("admin@javahelps.com");
            userRepository.save(user);

        };
    }
}
Here the CommandLineRunner returned by the init method will be executed by Spring Boot which in turns save a user with username and password 'admin'.

Spring Boot makes RESTful web service development much easier than never before. Eventhough this article covers more than a Hello World example, I recommend you to go through the official Spring Boot Reference Guide to learn more about Spring Boot. If you are new to REST web service development, I also recommend you to read this article by Vinay Sahni on Best Practices for Designing a Pragmatic RESTful API.

If you have any questions regarding this article, feel free to use the comment box below. Also I welcome your constructive thoughts on how you find this article.

Find the project @ Git Hub.
Latest
Previous
Next Post »

Contact Form

Name

Email *

Message *