How to connect MySQL database in spring boot application?

MySQL is a relational database management system(RDBMS) that's launched under the Opensource license.

MySQL is the combination of two words, My and SQL. The My is the daughter's name of the MySQL co-founder, Michael Widenius, and SQL is an abbreviation of Structured Query Language.

In this tutorial, We will learn how to connect the MySQL database in the spring boot application.

The source code is available from here.

toc

Prerequisite

---

1. MySQL is installed on your machine.

2. Java version 8 or later, I'll use Amazon Corretto JDK 11

Dependencies

---

Maven Dependency

Add the following maven dependency in your pom.xml file.

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

Gradle

compile group: 'mysql', name: 'mysql-connector-java', version: '8.0.22'

Configure MySQL connection in spring boot application

---

application.properties

Let's configure MySQL in spring boot. Open application.properties file from the project explorer and type the following.

 ## Connecting MySQL
 spring.datasource.username=your username here
 spring.datasource.password=your password here
 spring.datasource.url=jdbc:mysql://localhost:3306/todos
 spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

 spring.jpa.hibernate.ddl-auto=none

spring.jpa.hibernate.ddl-auto

The spring.jpa.hibernate.ddl-auto is a Spring Data JPA specific property.

The Spring boot application by default assigns none value to all databases, but if you are using an in-memory database, then the spring boot will assign create-drop value to the spring.jpa.hibernate.ddl-auto.

The Other's possible values are's

• update: Update the database schema.
• none: No action will perform.
• create-drop: Create the schema at startup and drop after ending the application.
• validate: Validate the database entity.

application.yml

 spring:
   jpa:
     hibernate:
        ddl-auto: none
     datasource:
        username: your username here
        password: your password here
        url: jdbc:mysql://localhost:3306/todos
        driver-class-name: om.mysql.cj.jdbc.Driver

Setting Up Database

---

Create a database in MySQL with the name todos and create a table of users inside the todos database. Login to the MySQL shell to create the todos database and the users table.

Creating a todos database

 mysql> CREATE DATABASE todos;

Creating a users table

Now Select the todos database and create a users table inside the todos database.

 mysql> use todos;

  mysql> CREATE TABLE users(
            id int, 
            firstname varchar(255), 
            lastname varchar(255));

Project Structure

---

Let's test the MySQL connection

Create User repository

 package in.learnjavaskills.connectmysql.dao;
 import org.springframework.data.jpa.repository.JpaRepository;
 import org.springframework.stereotype.Repository;
 import in.learnjavaskills.connectmysql.dto.Users;
 @Repository
 public interface UsersRepository extends JpaRepository {}

Add users

 package in.learnjavaskills.connectmysql;
 import org.springframework.boot.SpringApplication;
 import org.springframework.boot.autoconfigure.SpringBootApplication;
 import org.springframework.context.ConfigurableApplicationContext;
 import in.learnjavaskills.connectmysql.dao.UsersRepository;
 import in.learnjavaskills.connectmysql.dto.Users;
 @SpringBootApplication
 public class ConnectMySqlApplication 
 {
     public static void main(String[] args) 
     {
         ConfigurableApplicationContext context = SpringApplication.run(ConnectMySqlApplication.class, args);
         UsersRepository userRepository = context.getBean(UsersRepository.class);   
         userRepository.save(
             Users.builder()
                  .firstname("Imran")
                  .lastname("Shaikh")
                  .build());          
     }
 }

Users controller

 package in.learnjavaskills.connectmysql.controller;
 import java.util.List;
 import org.springframework.beans.factory.annotation.Autowired;
 import org.springframework.web.bind.annotation.GetMapping;
 import org.springframework.web.bind.annotation.RestController;
 import in.learnjavaskills.connectmysql.dao.UsersRepository;
 import in.learnjavaskills.connectmysql.dto.Users;
 @RestController
 public class UsersController
 {
     @Autowired
     private UsersRepository usersRepository;    
     @GetMapping("allUsers")
     public List getAllUsers()
     {
         return usersRepository.findAll();
     }
 }

Restart your application to test the MySQL connection

Go to your favorite browser and hit the following URL to retrieve all the Users from the MySQL database.

http://localhost:8080/allUsers

 [
     {
         id: 1,
         firstname: "Imran",
         lastname: "Shaikh"
     }
 ]

Conclusion

---

In this article, we have seen how to configure the MySQL connection in the spring boot application.

You can find the GitHub source code from here.

Keep learning and keep growing.

(getButton) #text=(Next: Configure Multiple Databases in Spring Data JPA) #icon=(link) #color=(#2339bd)