Spring Boot – CRUD Operations Using MySQL Database

Due to Spring Framework’s rich feature set, developers often face complexity while configuring Spring applications. To safeguard developers from this tedious and error-prone process, the Spring team launched Spring Boot as a useful extension of the Spring framework. Spring Boot eliminates the excessive configuration work by automating the decision-making tasks involved in Spring applications.

Moreover, it facilitates a faster and optimal Application Development ecosystem. Spring Boot, by default, depends upon the system memory to support its Application Development. However, with the increasing data requirements, businesses are inclining towards the idea of using MySQL as the data management facility for Spring Boot applications.

MySQL, being a cloud-based DBMS (Database Management System) can easily scale according to the developer’s needs and thus allow for faster data processing. This article will discuss the importance of setting up the Spring Boot MySQL Integration. Furthermore, the article will provide a detailed step-by-step MySQL Spring Boot connection process. Read along for more!

Steps to Integrate MySQL with Spring Boot

The following steps will help you in setting up the Spring Boot MySQL Integration:

• Step 1: Creating a MySQL Database
• Step 2: Append MySQL Dependencies
• Step 3: Set Spring Boot MySQL Connection Configuration 
• Step 4: Build a Repository Class for Spring Boot
• Step 5: Convert the Blog Class to Entity
• Step 6: Add the Controller to Spring Boot MySQL Integration

Step 1: Creating a MySQL Database

After installing the MySQL application, you must create a database table to initiate your Spring Boot MySQL Integration. On the MySQL terminal, execute the following code:

CREATE DATABASE restapi;
USE restapi;
CREATE TABLE blog (
  id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(500) NOT NULL,
  content VARCHAR(5000) NOT NULL
);

This command will create a database named “restapi” that contains the blog table.

Step 2: Append MySQL Dependencies

To connect your MySQL Database created in Step 2 to the Spring Boot application, you need to place the required dependencies (e.g. MySQL library) into the Spring Boot’s pom.xml file. These dependencies are a type of Java library that will facilitate your Spring Boot MySQL connection. In this scenario, the “mysql-connector-java” library is useful. Navigate to the dependency tag in your Spring Boot application and navigate to the pom.xml file where you must paste the following code: 

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>

Upon execution, your pom.xml file will look similar to the following:

<?xml version="1.0" encoding="UTF-8"?>
<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>me.salisuwy</groupId>
    <artifactId>SpringRest</artifactId>
    <version>1.0-SNAPSHOT</version>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.5.9.RELEASE</version>
    </parent>

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

        <!-- JPA Data (We are going to use Repositories, Entities, Hibernate, etc...) -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>

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

    <properties>
        <java.version>1.8</java.version>
    </properties>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

Step 3: Set Spring Boot MySQL Connection Configuration

Now, your Spring Boot MySQL Integration requires you to provide the database connection properties to Spring Boot. The MySQL Database URL, port, username, password, etc. falls under the required properties. Open the resource folder in Spring Boot and add the following code to provide the database-related information:

spring.datasource.url=jdbc:mysql://localhost:3306/restapi
spring.datasource.username=root
spring.datasource.password=

You must edit the above code and place the username, url, and password of your MySQL Database.

Step 4: Build a Repository Class for Spring Boot

Once the Spring Boot MySQL Integration is in place, you have to create a class in Spring Boot that can communicate with the MySQL Database. Such a “repository class” requires an interface that can extend the JpaRepository. The use of JpaRepository is essential as it adds certain functionalities like fetching all records, saving, updating, deleting, etc. The following code will create your required class:

package me.salisuwy;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface BlogRespository extends JpaRepository<Blog, Integer> {

    // custom query to search to blog post by title or content
    List<Blog> findByTitleContainingOrContentContaining(String text, String textAgain);    
}

Step 5: Convert the Blog Class to Entity

Now, create a blog class, say “Blog.java” that contains the fields of your MySQL table. Keep in mind that each instance of Blog.java is mapped to a row in your MySQL table. The following code will help you in creating a blog class and converting it into an entity:

package me.salisuwy;

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

@Entity
public class Blog {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private int id;

    private String title;
    private String content;

    public Blog() {  }

    public Blog(String title, String content) {
        this.setTitle(title);
        this.setContent(content);
    }

    public Blog(int id, String title, String content) {
        this.setId(id);
        this.setTitle(title);
        this.setContent(content);
    }

    public int getId() {
        return id;
    }

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

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public String getContent() {
        return content;
    }

    public void setContent(String content) {
        this.content = content;
    }

    @Override
    public String toString() {
        return "Blog{" +
                "id=" + id +
                ", title='" + title + ''' +
                ", content='" + content + ''' +
                '}';
    }
}

In the above code, the id column of your MySQL Table acts as the primary key. Your Spring Boot MySQL Integration uses an @Id annotation to inform Spring Framework about the required fields.

Step 6: Add the Controller to Spring Boot MySQL Integration

To further enhance your Spring Boot MySQL Integration, you can add the following command to the code in Step 5:

@Autowired
BlogRespository blogRespository;

This will allow you to use blogRepository anywhere in your controller without having to instantiate it repeatedly. The final code will look as follows:

package me.salisuwy;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.List;
import java.util.Map;

@RestController
public class BlogController {

    @Autowired
    BlogRespository blogRespository;

    @GetMapping("/blog")
    public List<Blog> index(){
        return blogRespository.findAll();
    }

    @GetMapping("/blog/{id}")
    public Blog show(@PathVariable String id){
        int blogId = Integer.parseInt(id);
        return blogRespository.findOne(blogId);
    }

    @PostMapping("/blog/search")
    public List<Blog> search(@RequestBody Map<String, String> body){
        String searchTerm = body.get("text");
        return blogRespository.findByTitleContainingOrContentContaining(searchTerm, searchTerm);
    }

    @PostMapping("/blog")
    public Blog create(@RequestBody Map<String, String> body){
        String title = body.get("title");
        String content = body.get("content");
        return blogRespository.save(new Blog(title, content));
    }

    @PutMapping("/blog/{id}")
    public Blog update(@PathVariable String id, @RequestBody Map<String, String> body){
        int blogId = Integer.parseInt(id);
        // getting blog
        Blog blog = blogRespository.findOne(blogId);
        blog.setTitle(body.get("title"));
        blog.setContent(body.get("content"));
        return blogRespository.save(blog);
    }

    @DeleteMapping("blog/{id}")
    public boolean delete(@PathVariable String id){
        int blogId = Integer.parseInt(id);
        blogRespository.delete(blogId);
        return true;
    }

}

JPA provides you with the CRUD operations (i.e. create, read, update and delete) as default in the above code. However, you can also create your queries for more complex operations.

Step 7: Testing the Application

To test your application, you can either use cURL command or Postman. Use the following cURL command to create a user in the database.

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

The output should be as follows:

Saved

Use the following command to show all the users:

$ curl http://localhost:8080/demo/all

The output should be:

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

That’s it! Your Spring Boot MySQL Integration is in place. Now you can try out the above steps by yourself to set up the Spring Boot MySQL connection for your business.

Running CRUD in Postman

Step 1: Create a Collection in Postman

Open Postman and create a new collection named “Spring Boot MySQL CRUD.”

Step 2: Set Up Requests for CRUD Operations:

Create (POST):

• URL: http://localhost:8080/blog (replace with your server address if different)
• Method: POST
• Headers:
  • Content-Type: application/json
• Body (raw):

JSON{ "title": "New Blog Post", "content": "This is the content of my new blog post." } 

Read (GET):

• Get all blogs:
  • URL: http://localhost:8080/blog
  • Method: GET
• Get a specific blog by ID:
  • URL: http://localhost:8080/blog/1 (replace 1 with the desired blog ID)
  • Method: GET

Update (PUT):

• URL: http://localhost:8080/blog/1 (replace 1 with the ID of the blog to update)
• Method: PUT
• Headers:
  • Content-Type: application/json
• Body (raw):

JSON{ "title": "Updated Blog Post Title", "content": "This is the updated content." } 

Delete (DELETE):

• URL: http://localhost:8080/blog/1 (replace 1 with the ID of the blog to delete)
• Method: DELETE

Step 3: Send Requests and Observe Responses:

• Click the “Send” button for each request to execute it.
• Observe the responses in the “Body” tab of Postman to see the results of the operations.

Importance of Spring Boot MySQL Integration

Deploying your Spring Boot based applications via MySQL instead of using in-built memory, will provide you with the following benefits:

• MySQL offers unparalleled scalability to make it easy to manage deeply integrated Spring Boot applications that operate on terabytes of data. Moreover, on-demand flexibility is the main feature of MySQL. This open-source solution allows full customization of your data and caters to the unique database server requirements of your Spring Boot-based applications for e-commerce businesses.
• Spring Boot can build stand-alone web applications with integrated servers. Now using MySQL Database instead of internal memory storage can minimize the query execution time. This way the application developed vis Spring Boot MySQL Integration, will be more suitable better for real-time work, and the users will get a better experience.
• Applications built on Spring Boot and running on MySQL are not only cost-effective for development, but also act as a viable solution for enterprise-level tasks. Spring Boot can leverage MySQL to enhance its ability to package a complete service (such as user authentication) into a self-contained, fully deployable form that also provides an API. This way it can greatly simplify the installation and deployment processes of an application. The Spring Boot MySQL Integration is also gaining popularity in the world of microservices.

To know how to create RESTful APIs in Spring Boot in 7 easy steps, refer to: Creating RESTful APIs in Spring Boot: A Step-by-Step Guide

Conclusion

You have understood the importance and steps of setting up a Spring Boot MySQL Integration. Now, to run SQL queries or perform Data Analytics on your MySQL data, you first need to export this data to a Data Warehouse. This will require you to custom-code complex scripts to develop the ETL processes. Hevo Data can automate your data transfer process, hence allowing you to focus on other aspects of your business like Analytics, Customer Management, etc. This platform allows you to transfer data from 150+ multiple sources like MySQL to Cloud-based Data Warehouses like Amazon Redshift, Snowflake, Google BigQuery, etc. It will provide you with a hassle-free experience and make your work life much easier.

Visit our Website to Explore Hevo

Frequently Asked Questions