Made withSpring BootClip

How to connect Mysql using Spring boot Java and also create table in database tutorial

U

United Top Tech

Creator on the Spring Boot channel

Watch on YouTube ↗

153K viewsDec 20, 202211:40 runtime

Learn how to connect a Spring Boot application to a local MySQL database using Java and IntelliJ IDEA. This tutorial walks you through setting up a project with Spring Initializr, including essential dependencies like Spring Web, Spring Data JPA, and the MySQL driver. Discover…

Chapters

#springboot#clip#frameworks

Key Concepts: Connecting Spring Boot to MySQL

This document outlines the key steps to connect a Spring Boot application to a local MySQL database and automatically generate a table schema using Spring Data JPA.

## Project Initialization

Create a new project using the Spring Initializr with the following essential dependencies:

Spring Web: For building web applications.
Spring Data JPA: For database access and object-relational mapping (ORM).
MySQL Driver: The JDBC driver for connecting to MySQL.
Lombok: A library to reduce boilerplate code like getters, setters, and constructors.

Tip
Using Lombok's @Data annotation on your entity class saves significant time by automatically generating getters, setters, toString(), and constructor methods.

## Database Configuration (`application.properties`)

All database connection and behavior settings are configured in src/main/resources/application.properties.

1. Connection Credentials:

spring.datasource.url=jdbc:mysql://localhost:3306/your_database_name
spring.datasource.username=your_username
spring.datasource.password=your_password

Note
Ensure the database (your_database_name) exists in your MySQL instance before running the application.

2. JPA & Hibernate Properties:

spring.jpa.show-sql=true: Displays the generated SQL queries in the console, which is useful for debugging.
spring.jpa.hibernate.ddl-auto=update: This tells Hibernate to update the database schema to match the entity model. It will create tables if they don't exist.
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL8Dialect: Specifies the SQL dialect for MySQL 8.

Important
The hibernate.dialect property string is case-sensitive and must be written exactly as shown to avoid errors.

## Entity Mapping

A Java class is used to model the database table. This is achieved through annotations.

Example Student.java:

@Data
@Entity
@Table(name = "student") // Sets the table name in the database
public class Student {

    @Id // Marks this field as the primary key
    @Column(name = "id")
    private int id;

    @Column(name = "mark")
    private int mark;

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

@Entity: Marks the class as a JPA entity (a table in the database).
@Table: (Optional) Specifies the exact name of the database table.
@Id: Designates the primary key field.
@Column: (Optional) Maps the field to a database column, allowing you to specify the column name.

## Execution and Verification

Run Configuration: In IntelliJ IDEA, create a new Application run configuration and select your main application class.
Run Application: Execute the run configuration.
Verify:
- Check the console for the "Started application" message and the CREATE TABLE SQL statement (because show-sql is true).
- Connect to your MySQL database and refresh the schemas. The new table (e.g., student) should appear with the defined columns (id, mark, name).

Introduction and Project Goal

Hi everyone. In this video, I will be showing you how to connect Spring Boot with local MySQL. So let's get started.

So I'm going to use IntelliJ as well as my local MySQL Workbench, and I will also show you how to create a table only by using Spring Boot Java code rather than creating it manually. So let's get started now.

Spring Initializr Project Setup

First thing what you need to do is you need to just go to your browser and need to search for Spring Initializr. You'll be getting this first link over here, Spring Initializer. Just click it and here you need to add the dependencies as well as the name of your project and the versions. So I will go with Gradle Groovy, that is fine. Language is Java and let me just now change the name as Spring Pro, something like that. You can just give it to everywhere. Yeah, you can just paste it and you can just change it here as well. Yeah, this is totally up to your choice. And JAR is fine and Java 17 works for me. So these are fine now.

We will be using some dependencies over here. So just click on Add Dependency. And first thing we need to search for Spring Web. Just add it, Spring Web. And I'll click again Add Dependency. And next one is going to be the Spring Data JPA. So it's going to be Spring Data JPA. Here we will be having and I will add the MySQL driver as well. MySQL driver. And final one is going to be Lombok. The reason why I'm adding Lombok is because we don't want to like manually create the Getters and Setters or the constructors. We can just use the annotations presented inside Lombok.

So these are basically the four dependencies that I'm adding. So this is the entire stuff. All right. So once you are done creating all these, you just click on generate. So it will be getting downloaded in the form of a zip file. So here you can see, right, Spring Pro. So now let me just go to my downloads folder. So here you can see it is in the form of a WinRAR zip file. So just right click it and just click on extract files and just click on OK. So it will be getting extracted over here itself. Yeah.

Opening the Project in IntelliJ IDEA

So here you can see this is the file. We'll be having source and main.test. So this is the .java file over here. Yeah, so this is the file.

So now let me just open this particular folder as a project in IntelliJ. So for that just go to IntelliJ IDEA. And after opening IntelliJ IDEA you need to go to File and need to go to Open and need to go to downloads. And here we'll be having the Spring Pro, the spring project. Okay. Just click on that and click on OK. And just click on this window.

So at first, it will take some time to load. So just be patient. I think it will take some time for indexing as well as the Gradle to build. So just be patient at first. So here you can see it will be showing you updating index and importing Gradle. So Gradle build. So these two processes are really important and you need to like wait for some time until both the processes are getting completed. So here you can see now all the processes have been now completed and there is no loading or anything like that.

Configuring Database Connection URL

So first thing what we need to do is we will be just going to my main Java and here you can see this is my main application over here. So now we will be making some changes in application.properties. So for that, where we need to go is resources and here you will be having application.properties. Just double click it and here you will be having the application.properties.

So first, we will be adding our local URL of our MySQL database. So it's going to be spring.datasource.url=jdbc:mysql://localhost:3306/ and next you need to specify your database name that is going to be present in your MySQL workbench. So I will just name it as newDB and you need to specify colon 3306 because this is not like the random port number, this is the port number where MySQL workbench is running. So be specific to that.

So once you've added your database you have to create this particular database in MySQL workbench, otherwise it will throw you an error. So I will just go to my MySQL workbench and create this particular database newDB. And if I just now refresh it over here, I'll be having the database created.

Adding Database Credentials

So this is fine. Our next thing we need to specify the username and password of your MySQL database. So let me just now copy this thing. So it's going to be spring.datasource.username and you need to specify the username which is root for me. This may vary from person to person. And again, spring.datasource.password. So you need to specify your password. Let me specify mine. So this is fine. These are some of the credentials that we need to give over here.

Configuring JPA and Hibernate Properties

So after this, we will be using some connection properties for our connection. So it's going to be spring.jpa.show-sql=true. So this command is basically like used to display the SQL command in our console. So I told you like we will be creating a table at the end. So the creation of that particular table and its SQL command will be displayed at the console. So that's the reason why we are adding this particular property. And next thing are going to be some of the default properties. Okay. It's going to be spring.jpa.generate-ddl=true. Next one is going to be spring.jpa.hibernate.ddl-auto=update. And final thing, let me just now copy this and paste it, .properties.hibernate.dialect=org.hibernate.dialect.MySQL8Dialect.

Okay, so here to be careful. M is capital, y is small, SQL are caps, 8, D is capital, and dialect. I think like you have to be like super careful over here because even if you like make a slightest of mistakes, you'll be getting an error and it will take like so much of time. So this is like the code basically. Like you can just pause the video and take a screenshot of it and type it out and verify it. So just take your time.

Creating the Student Entity Class

So once you're done like adding the values in application.properties, next we'll be creating a .java file. So for that you have to just go to this particular folder, main/java/com.springPro, the project name. Just right click over here and go to New and create a Java Class over here. And let me name it as Student and let me just create a class. So this is the way through which we'll be creating a table over in our database. So as you can see in my like MySQL bench, inside my database, I'm not having a table. So now we will be creating a table within this particular database. Yeah, I will just select Class. And here you can see Student class has been created. So now I will specify the columns of the class. It's going to be private int id;. Yeah, and next one is going to be private mark. Next one is going to be private... what was that... name. Yeah, this is like this. Okay, we need to specify the data type as well. Its string is going to be of type int.

Annotating the Entity Class

So now we will be like using multiple annotations now. So first annotation is going to be @Entity because this is going to be a table. Okay. So for that we will be using @Entity as well as @Table annotation. So, @Entity and next one is going to be @Table. And here we will be specifying the table name that we want to get in our MySQL Workbench. So I will just go and type table and we will have a name, and I will give it as student in caps. This is going to be the table name.

And next one we will be using the @Data entity because we don't want to set getter and setter, everything. Getter, setter, constructor, we have to like add that manually. But by using this @Data annotation from Lombok it will automatically create everything. So it saves us time as well as code length. So this is good, you need to use this.

And after this is pretty much straightforward. We'll be using the @Id annotation for this. Next one, I will give the column name, @Column. So I will just add the name as id. And I'll just copy this now and paste it and paste it here as well. I will just change it as mark and I will just change it as name over here as well. So this is fine I guess now.

Configuring and Running the Application

After doing all these things, you have to like add the configuration over here because our run button is currently disabled. So what you can do is you can just click on Add Configuration, you need to just click on Plus and want to click on Application. And like, you will be having showing you module not specified. Let me name it as Project first. Let me name it as like project. And I will go to no module and I will just create springPro.main. You need to click on the main thing over here. Just click on it and it will be automatically like detecting the class. If I just click it, so it will automatically detect the class for us. So just click that and click on OK.

Yeah, so here you can see now everything is configured. You need to just add the main.main over here. I think this will be displayed only if you have like configured proper Java and you have allowed your Gradle to build. So please wait, okay? If anything is loading, don't cancel it, allow it to like completely load everything. So just click on Apply and click on OK here as well. So after we run this program, we will be basically like having a table within this newDB database, which means that we are successfully connected to our local MySQL workbench. That's what we are trying to do in this video.

Verifying the Results

So if I just now run it, I hope there aren't any errors in this. Yeah, this is fine I guess. So after some times, if there aren't errors, we will be like getting this particular thing: Spring Boot application has started. So here you can see right, started application. Which means that there isn't any errors. And like if I just scroll up, so here you can see that particular SQL command has been now executed over here. Okay. This is what it meant by the show over here. All right.

So now if I go to my MySQL workbench and if I just now refresh it, so here you can see the table has been created and student has been now created over here. And I didn't create this manually, it has been automatically created by Spring Boot command. And there aren't any errors over here as well. It's perfectly running fine. And if I do select * from student, so here you can see ID, Mark, and Name. So these are the columns that I have specified over here in my class. So this is working perfectly fine now. So you can also try it out and like comment it down how it is working and if you're able to connect to your MySQL workbench.

Conclusion

So I hope you would have found this video useful. I've also done many other tutorials in Java CRUD operations by using Node.js, MongoDB and MySQL. I will be also doing CRUD with Spring Boot, MySQL and MongoDB. So do check my playlist of my channel. Subscribe and support me. Thanks for watching.