How to connect spring boot with Database – MySQL

In this tutorial, we will learn how about spring boot and how to connect the spring boot projects with the MySQL Database server.

There is a scenario where we need to connect a spring boot with a web application with the MySQL Database with Spring Data JPA and Hibernate Framework.

There are a few steps to follow to know how to connect a web application with MySQL Database using spring boot:

  • First, declare a dependency for MySQL  JDBC Driver. This enables the Java web application to communicate with the Database server.
  • Second, declare a dependency of Spring Data JPA.
  • Third,  specify the Database configuration in the application.properties file for the connection with the MySQL server.
  • Fourth, since we are using Spring Data JPA, we need to create an entity class. After that, we need to create a Dao Repository interface. Then we can use the Spring Data JPA API.

So, Let’s  discuss the above steps in Detail:

Declaring a dependency for MySQL JDBC Driver:

For declaring MySQL JDBC Driver, we need to declare the below lines in the pom.xml file of our spring boot project.

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

 

Connecting MySQL Driver with Spring Data JPA:

Spring Data JPA is used simplies Database programming. This is based on Java Persistence API specification with Hibernate as the implementation framework. The below lines has to be injected into pom.xml file so as to use this functionality.

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

Specify the Database configuration in the application.properties file:

Here we need to specify the datasource properties that is database URL, database name, username, password, etc. The blow lines are required examples of how database configuration is done in application.properties file.

spring.datasource.url=jdbc:mysql://localhost:3306/codespeedydatabase?serverTimezone=UTC
spring.datasource.username=root
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL8Dialect
spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true

Creation of Entity Class, Dao Repository interface, and Controller class to invoke the database execution:

First, you need to create an entity class that maps with the table to the database. The code below explains the Code Structure.

import java.util.ArrayList;
import java.util.List;
import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.OneToMany;
import javax.validation.constraints.AssertTrue;
import javax.validation.constraints.NotBlank;
import javax.validation.constraints.Pattern;
import javax.validation.constraints.Size;

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

    private String name;

    
    private String email;

   
    private String password;
     
    public User(int id, String name, String email, String password) {
        this.id = id;
        this.name = name;
        this.email = email;
        this.password = password;
       
    }
    
    public User() {
    }


    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    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;
    }
    public String getPassword() {
        return password;
    }
    public void setPassword(String password) {
        this.password = password;
    }
   
}

Then we need to create a Dao Repository interface where we can type our query to fetch our data.

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import com.smart.smartcontactmanager.entities.User;

public interface UserRepository extends JpaRepository<User,Integer>{
    
    @Query("select u from User u where u.email = :email")
    public User getUserByUsername(@Param("email") String email);
    
}

And this is how the Database is connected in a spring boot web application or any other application.

Leave a Reply

Your email address will not be published.