Connect Spring Boot with MySQL

Hello everyone,

In this tutorial I will explain the proccess I followed in order to connect Spring Boot with MySQL, in order to create an API for my Front-End.

Prerequisites:

• IDE (I use Intellij IDEA so this tutorial will be based on that)

• MySql Workbench

Click here for the source code.

Create a Spring Boot project using Spring Initializr

Visit start.spring.io and select:
Project: Maven
Language: Java
Spring Boot: 3.3.0
Write the necessary fields with your content
Packaging: JAR
Java: 17

As for dependencies, we will need:

• MySQL Driver

• Spring Web

• Spring Data JPA

After these, the initializr should look like this:

Click Generate and save the folder in your desired path and extract the folder's content.

Intellij and Mysql configuration

First of all create a database in MySQL. I used MySQL Workbench for this.

Even the simpliest database will work, just like this:

Open the folder's content in your desired IDE. I will cover this tutorial using Intellij IDEA.

Open the application.properties file which is located at scr/resources/application.properties

In this file, we configure the settings that will help us connect in our database.

Write these settings in the file:

Replace ${DB_NAME}, ${DB_USER}, ${DB_PASSWORD} with your database's credentials.

These settings will help us connect with the database we created:

spring.jpa.show-sql=true:

This enables the logging of SQL statements generated by Hibernate. When set to true, Hibernate will print the SQL statements to the console.

spring.jpa.hibernate.ddl-auto=update:

This setting is used to automatically update the database schema to match the entity definitions. The value update means that Hibernate will update the existing schema, adding any new columns or tables required by the entity mappings.

logging.level.org.hibernate.SQL=DEBUG:

This sets the logging level for the Hibernate SQL logger to DEBUG. It will provide detailed information about the SQL statements being executed.

logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE:

This sets the logging level for the Hibernate type descriptor SQL binder to TRACE. This will log detailed information about the binding of parameters in SQL statements.

spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver:

This specifies the JDBC driver class name for MySQL. It tells Spring Boot which driver to use for establishing the connection to the database.

spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQLDialect:

This sets the Hibernate dialect to MySQLDialect, which is optimized for MySQL. It allows Hibernate to generate SQL statements that are compatible with MySQL.

Now, create a sub-package in the main package of your project, and call it "model". Inside, create a class calling it however you want, in my case I will call it Users.

package com.evaluation.evaluationSystem.model;

import jakarta.persistence.*;

@Entity
@Table(name = "users")
public class Users {
    public Long getId() {
        return id;
    }

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

    @Column(name = "email")
    private String email;

    @Column(name = "password")
    private String password;

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

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getPassword() {
        return password;
    }

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

In this file, in this file we define a JPA entity Users which will be mapped to a database table users. The class includes fields for id, email, and password which correspond to columns in the users table, so make sure the field align with the columns of your database.

Moving on, create another sub-package called "controller" and create a file in it.

package com.evaluation.evaluationSystem.controller;

import com.evaluation.evaluationSystem.model.Users;
import com.evaluation.evaluationSystem.repository.UserRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

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

@RestController
public class UsersController {
    @Autowired
    private UserRepository userRepository;

    @GetMapping("/users")
    public List<Users> getUsers(@RequestParam("search") Optional<String> searchParam){
        return searchParam.map(param -> userRepository.getContainingQuote(param))
                .orElse(userRepository.findAll());
    }
}

In this file, we define a RESTful API endpoint (/users) that can optionally filter Users entities based on a search parameter. It utilizes UserRepository for database interaction and returns results in JSON format due to the @RestController annotation. Replace "/users" with any endpoint you want.

Create one more ( the last one ) sub-package called repository and create a file interface ( be carefull, not class).

package com.evaluation.evaluationSystem.repository;

import com.evaluation.evaluationSystem.model.Users;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;

public interface UserRepository extends JpaRepository<Users, Long> {
    @Query("SELECT u FROM Users u WHERE u.email LIKE %:word%")
    List<Users> getContainingQuote(@Param("word") String word);
}

In this file, we define the query that will allow us to retrieve the data from the database. Make sure to edit it based on your needs. We write this query using JPQL (Java Persistence Query Language). It is a query language defined as part of the Java Persistence API (JPA) specification, which is used to perform database operations on Java objects and entities.

Your last folder structure should look like this:

Now, navigate to the main file (in my case, EvaluationSystemApplication) and run the project. If everything works well, visiting localhost:8080/users ( or the endpoint you chose ) will display your data from the database. Make sure you fill the table of the data with some content.

Conclusion

I hope this tutorial helped you. I am new too in this environment, so I learn too. Every comment and suggestion is more than welcome!

Feel free to drop a follow on my GitHub account to stay updated on my journey to develop a full stack web app using Spring Boot, MySQL and React!