How to Connect MySQL Database in a Spring Boot Project

Spring Boot is a powerful framework that simplifies the development of Java-based applications, including web applications and microservices. One of the common requirements in many applications is the need to persist data in a database. MySQL is a popular relational database that can be seamlessly integrated into Spring Boot projects. In this blog, we will guide you through the process of connecting a MySQL database to a Spring Boot project.

Blog Explanation with Working Code

Prerequisites:

  1. Basic knowledge of Java and Spring Boot.
  2. MySQL installed on your system or accessible through a remote server.
  3. A Spring Boot project set up (you can create a new one or use an existing project).

Note: For this tutorial, we are using our basic skeleton project for springboot. You can also download the project from here.

Step 1: Add MySQL Dependency

To use MySQL in your Spring Boot project, you need to include the JPA and MySQL JDBC driver as a dependency in your project’s build file (pom.xml for Maven or build.gradle for Gradle). The JDBC driver enables Java applications to interact with MySQL databases.

For Maven users, add the following dependency:

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


<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.33</version>
</dependency>

For Gradle users, add the following dependency:

// Add the Spring Data JPA starter dependency
implementation 'org.springframework.boot:spring-boot-starter-data-jpa'


// https://mvnrepository.com/artifact/mysql/mysql-connector-java
implementation group: 'mysql', name: 'mysql-connector-java', version: '8.0.33'

Step 2 (Optional): Please refer bellow for creating a database in MySQL

mysql -u root -p

CREATE DATABASE your_database_name;
CREATE USER 'your_mysql_username'@'localhost' IDENTIFIED BY 'your_mysql_password';
GRANT ALL PRIVILEGES ON your_database_name.* TO 'your_mysql_username'@'localhost';
FLUSH PRIVILEGES;

exit

Explanation:

  • Replace your_database_name with the desired name for your database.
  • Replace your_mysql_username with the username you want to create for accessing the database.
  • Replace your_mysql_password with the password you want to set for the specified username.

These commands will create a new database with the given name and a user with the specified username and password. The user will have all privileges (i.e., full access) to the newly created database.

After creating the database and user, you can use the same credentials in the Spring Boot project’s application.properties or application.yml file under the spring.datasource properties as mentioned in Step 3 of the blog.

Step 3: Configure Database Properties

In a Spring Boot project, you typically configure the database properties in the application.properties or application.yml file, depending on your preference. Open the file and add the necessary configurations for MySQL:

For application.properties:

spring.datasource.url=jdbc:mysql://localhost:3306/your_database_name
spring.datasource.username=your_mysql_username
spring.datasource.password=your_mysql_password
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.jpa.hibernate.ddl-auto=create

For application.yml:

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/your_database_name
    username: your_mysql_username
    password: your_mysql_password
    driver-class-name: com.mysql.cj.jdbc.Driver
  jpa:
    hibernate:
      ddl-auto:create

Ensure you replace your_database_name, your_mysql_username, and your_mysql_password with your actual MySQL database name, username, and password, respectively.

Step 4: Create Data Model and Repository

Create a new package named “com.example.myproject.model” in the src/main/java directory. Inside this package, create a new Java class named “Book”.

package com.example.myproject.model;

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

@Entity
public class Book {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String title;
    private String author;

    // Constructors, getters, and setters (or use Lombok annotations)

    // Default constructor (required by JPA)
    public Book() {}

    // Parameterized constructor
    public Book(String title, String author) {
        this.title = title;
        this.author = author;
    }

    // Getters and setters
    public Long getId() {
        return id;
    }

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

    public String getTitle() {
        return title;
    }

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

    public String getAuthor() {
        return author;
    }

    public void setAuthor(String author) {
        this.author = author;
    }
}

Now, create another package named “com.example.library.repository” in the src/main/java directory. Inside this package, create a new Java interface named “BookRepository”.

package com.example.myproject.repository;

import com.example.myproject.model.Book;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface BookRepository extends JpaRepository<Book, Long> {
    // You can define custom query methods here if needed
}

Step 5: Create Service and Controller

Create a new package named “com.example.library.service” in the src/main/java directory. Inside this package, create a new Java class named “BookService“.

package com.example.myproject.service;

import com.example.myproject.model.Book;
import com.example.myproject.repository.BookRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;
import java.util.Optional;

@Service
public class BookService {
    private final BookRepository bookRepository;

    @Autowired
    public BookService(BookRepository bookRepository) {
        this.bookRepository = bookRepository;
    }

    public List<Book> getAllBooks() {
        return bookRepository.findAll();
    }

    public Book saveBook(Book book) {
        return bookRepository.save(book);
    }

    public void deleteAllBooks() {
        bookRepository.deleteAll();
    }

    public boolean deleteBookById(Long id) {
        Optional<Book> bookOptional = bookRepository.findById(id);
        if (bookOptional.isPresent()) {
            bookRepository.deleteById(id);
            return true;
        }
        return false;
    }

    // Other business logic related to books
}

Now, create a new package named “com.example.library.controller” in the src/main/java directory. Inside this package, create a new Java class named “BookController“.

package com.example.myproject.controller;

import com.example.myproject.model.Book;
import com.example.myproject.service.BookService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.List;

@RestController
@RequestMapping("/books")
public class BookController {
    private final BookService bookService;

    @Autowired
    public BookController(BookService bookService) {
        this.bookService = bookService;
    }

    @GetMapping
    public List<Book> getAllBooks() {
        return bookService.getAllBooks();
    }

    @PostMapping
    public Book addBook(@RequestBody Book book) {
        return bookService.saveBook(book);
    }

    @DeleteMapping
    public String deleteAllBooks() {
        bookService.deleteAllBooks();
        return "All books have been deleted successfully.";
    }

    @DeleteMapping("/{id}")
    public String deleteBookById(@PathVariable Long id) {
        if (bookService.deleteBookById(id)) {
            return "Book with ID " + id + " has been deleted successfully.";
        } else {
            return "Book with ID " + id + " not found.";
        }
    }

    // Other endpoints for updating books, etc.
}

Step 6: Create Table in Database for your Model

You will get error, if you don’t create table in mysql database. Log in to your mysql and use bellow code to create your table. If you have added spring.jpa.hibernate.ddl-auto=create in your application.properties file, then you dont need to create the table

USE your_database_name;

CREATE TABLE book (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    author VARCHAR(255) NOT NULL
);

Step 7: Run the Application

Now, you have completed the setup. You can run your Spring Boot application. The application will start, and you should be able to access the REST endpoints defined in the BookController. For example:

  • To get all books: GET http://localhost:8080/books
  • To add a book: POST http://localhost:8080/books with a JSON body containing book details – { "title": "Example Book", "author": "John Doe" }
  • To delete all books: DELETE http://localhost:8080/books
  • To delete book by id: DELETE http://localhost:8080/books/{id}

Note: You can create your HTML Form, submitting and displaying data.

Conclusion:

In this blog, we covered the steps to connect a MySQL database to a Spring Boot project. By adding the MySQL dependency, configuring the database properties, and creating a data model along with a repository, you can seamlessly interact with the database in your Spring Boot application. Leveraging this knowledge, you can now build more complex applications with data persistence and take advantage of the full capabilities of Spring Boot and MySQL. Happy coding!

Find this Project on Github.

Also Read How to Protect Sensitive Data in a Java Project Like Spring Boot.

Read our Blog on How to Provide Initial Default Data for Models in Spring Boot