Introduction#

In this article, we'll cover how to set up MySQL as the database for your Spring Boot application using Spring Data JPA. We will go through installing MySQL, configuring it with Spring Boot, and creating an example entity to connect with the database.

1. Installing MySQL#

To get started, follow these steps to install MySQL:

Download MySQL

Download the MySQL installer from the official https://dev.mysql.com/downloads/installer/ website.

You will see two options select the 2nd option.

Unzip the file and double click on MSI installer .exe file you will see the following screen.

3. In the next wizard, choose the Setup Type, select Full.

1. Click on the Next button, it will give information about some features that may fail to install on your system due to a lack of requirements. Click on execute to resolve it.

1. Next we will see a dialog box click yes.

After clicking on the Yes button, we will see the list of the products which are going to be installed. So, if we need all products, click on the Execute button.

1. After all downloads are complete click next.

1. Next we need to configure the MySQL Server and Router. Here, I am not going to configure the Router because there is no need to use it with MySQL. We are going to show you how to configure the server only. Now, click on the Next button.

1. After clicking next, we have to configure the MySQL Server. Now, choose the Standalone MySQL Server/Classic MySQL Replication option and click on Next.

1. In the next screen, the system will ask you to choose the Config Type and other connectivity options. Here, we are going to select the Config Type as 'Development Machine' and Connectivity as TCP/IP, and Port Number is 3306, then click on Next.

1. Now, select the Authentication Method and click on Next. Select first one.

1. The next screen will ask you to mention the MySQL Root Password. After filling the password details, click on the Next button.

1. The next screen will ask you to configure the Windows Service to start the server. Keep the default setup and click on the Next button.

1. In the next wizard, the system will ask you to apply the Server Configuration, click on the Execute button.

1. Once the configuration has completed, Now, click on the Finish button to continue.

1. In the next screen, you can see that the Product Configuration is completed. Keep the default setting and click on the Next-> Finish button to complete the MySQL package installation.

1. In the next wizard, we can choose to configure the Router. So click on Next->Finish and then click the Next button.

1. In the next wizard, we will see the Connect to Server option. Here, we have to mention the root password, which we had set in the previous steps.

              Click on check button to check connection is successful or not.

1. In the next wizard, select the applied configurations and click on the Execute button.

1. After completing the above step, we will get the following screen. Here, click on the Finish button.

1. Now, the MySQL installation is complete. Click on the Finish button.

Verify MySQL installation#

Once MySQL has been successfully installed, you can verify its working via some simple tests.

Open your MySQL Command Line Client; it should have appeared with a mysql> prompt. If you have set any password, write your password here. Now, you are connected to the MySQL server, and you can execute all the SQL command at mysql> prompt as follows:

For example: Check the already created databases with show databases command:

After starting MySQL, create a new database:

CREATE DATABASE springbootdb;

2. Spring Boot Project Setup#

Next, create a Spring Boot project and add the necessary dependencies for MySQL and Spring Data JPA.

Maven Dependencies#

In your pom.xml, add the following dependencies:

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

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

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

3. Configure Spring Boot to Connect with MySQL#

In the application.properties file (or application.yml if you're using YAML), configure the connection to the MySQL database.

application.properties#

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

spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL8Dialect

This configuration:

• Sets the MySQL database URL.
• Defines the root user credentials for database access.
• Configures Hibernate to automatically update the database schema based on your entities.
• Enables SQL logging for better understanding of the queries executed.

4. Creating an Example Entity#

Now that we have the database and configuration in place, let's create an entity that maps to a MySQL table.

Student.java#

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

@Entity
public class Student {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;
    private String course;

    // Constructors, getters, and setters
    public Student() {}

    public Student(String name, String course) {
        this.name = name;
        this.course = course;
    }

    public Long getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public String getCourse() {
        return course;
    }

    public void setCourse(String course) {
        this.course = course;
    }
}

5. Creating a Repository Interface#

Spring Data JPA simplifies database operations. We can define a repository interface that extends JpaRepository to interact with the Student entity.

StudentRepository.java#

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

@Repository
public interface StudentRepository extends JpaRepository<Student, Long> {
}

6. Writing a Controller#

To interact with our Student entity, let's create a basic REST controller that allows us to add and retrieve students from the database.

StudentController.java#

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

import java.util.List;

@RestController
@RequestMapping("/students")
public class StudentController {

    @Autowired
    private StudentRepository studentRepository;

    @PostMapping
    public Student createStudent(@RequestBody Student student) {
        return studentRepository.save(student);
    }

    @GetMapping
    public List<Student> getAllStudents() {
        return studentRepository.findAll();
    }
}

7. Running the Application#

Start the Spring Boot application and test the endpoints using a tool like Postman or cURL.

POST request to /students to add a new student:

{
    "name": "John Doe",
    "course": "Computer Science"
}

GET request to /students to retrieve all students.

If the configuration is correct, you should see the data stored in your MySQL database.

In this article, we explored how to set up MySQL as the database for your Spring Boot application using Spring Data JPA. We covered the installation of MySQL, verified its functionality, configured a Spring Boot project with the necessary dependencies, and created a sample entity to connect with the database. With this setup, you can now easily interact with your MySQL database through your Spring Boot application, paving the way for building robust data-driven applications.