In this post, we’ll explore the process of connecting a Spring Boot application to a MySQL database. The Spring Boot application establishes a connection to a MySQL database, retrieves data from a table using JPA, and uses a Rest Controller to showcase the data in a browser. The installation of Tomcat and execution of the REST web services are facilitated by Spring Web. The Spring Boot application establishes a database connection using the credentials specified in the application properties file. Here’s a detailed step-by-step process of connecting a MySQL database using the JPA repository in a Spring Boot application:
Step 1: Create Spring Boot Project
Create a Spring Boot project in the Spring Tool Suite by navigating to the menu and selecting File -> New -> Spring Starter Project. Provide project details, including the project name, location, artifact, group, package, and description. On the project dependency selection page, choose “Spring Web,” “Spring Data JPA,” and “MySQL Driver.” These specific dependencies are essential for the creation of the Spring Boot project.
Step 2: Create pom.xml File
The Spring Boot application is constructed through the pom.xml file using Maven as the build tool. Upon creating a Spring Boot project, the pom.xml is automatically generated, and when you choose the specified dependencies during project creation, they are included in the pom.xml. This allows the Spring Boot application to load all the necessary dependent JARs. The added dependencies in the pom.xml will look like the example below.
<dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jpa</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> </dependencies>
Step 3: Create Database, Table, Insert Rows
Before connecting the Spring Boot application to the MySQL database, it’s essential to install MySQL and set up a database. Connect to the MySQL database server and create a database, for example, named “test.” Create a table within this database and insert some sample rows. This table will be used to showcase data from the database through the Spring Boot application. Execute the following SQL query to create a table and insert rows:
create table employee
(
empid int primary key AUTO_INCREMENT,
empname varchar(100),
salary double
);
insert into employee (empname,salary) values('Kim',1000);
insert into employee (empname,salary) values('Bill',1000);
insert into employee (empname,salary) values('Don',1000);
Step 4 : Add Database Credential in Application.properties
The MySQL database server creates the database table. To connect to the database, the Spring Boot application needs to be configured with MySQL database credentials. Configure the database credentials using the application properties file located in the src/main/resources folder. The configuration should resemble the following example. Specify the database URL, username, password, dialect, and other necessary properties to establish a connection.
src/main/resources/application.properties
spring.datasource.url=jdbc:mysql://localhost/test spring.datasource.username=root spring.datasource.password=your_password spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5Dialect spring.jpa.show-sql=true spring.jpa.hibernate.ddl-auto=update
Replace your_password with the actual password you have set for your MySQL database.
Step 5: Create Entity Class
In the Spring Boot application, the entity class serves to model a database table. Each property of the entity class corresponds to a column in the database table. The entity class is responsible for storing the values of each row in the Spring Boot application. To facilitate the assignment and retrieval of values, the entity class is equipped with getter and setter methods.
Employee.java
package com.test;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
@Entity
public class Employee {
@GeneratedValue(strategy = GenerationType.AUTO)
@Id
private int empid;
private String empname;
private int salary;
public int getEmpid() {
return empid;
}
public void setEmpid(int empid) {
this.empid = empid;
}
public String getEmpname() {
return empname;
}
public void setEmpname(String empname) {
this.empname = empname;
}
public int getSalary() {
return salary;
}
public void setSalary(int salary) {
this.salary = salary;
}
}
Step 6: Create Repository Class
Spring Boot facilitates the creation of a repository class that encapsulates database connections and supports fundamental CRUD (Create, Read, Update, Delete) operations. The Repository interface includes methods dedicated to inserting, updating, deleting, and querying database values from a table. This repository class effectively manages database connections and basic database operations.
EmployeeRepository.java
package com.test;
import org.springframework.data.jpa.repository.JpaRepository;
public interface EmployeeRepository extends JpaRepository<Employee, Long>{
}
Step 7: Create Rest Controller Class
The rest controller class operates within the Tomcat web server to host a REST web service. This controller defines the web service and exposes an API for browser access. Invoking the REST API triggers the execution of the method from the rest controller class. The Rest controller class seamlessly connects to the repository class, which in turn retrieves data from the MySQL database.
EmployeeController.java
package com.test;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;
@RestController
public class EmployeeController {
@Autowired
EmployeeRepository employeeRepository;
@RequestMapping(value="/employee", method=RequestMethod.GET)
public List getEmployeeData() {
List list = employeeRepository.findAll();
return list;
}
}
Step 8: Run the Spring Boot Application
Runthe Spring Boot application once you’ve completed the creation of all Java classes. To launch the Spring Boot application, choose ‘Run’ from the menu. The console window will showcase the startup logs of the Spring application, including the log for initiating Tomcat on port 8080. Confirm that Tomcat is running smoothly without any errors. If any errors appear in the console window, address and rectify them accordingly.
Step 9: Call the REST API
To access the REST API, type http://localhost:8080/employee in your browser’s address bar. This URL will invoke the REST web service configured in the REST controller class. The REST controller, in turn, utilizes the repository interface to fetch data from the database. The retrieved information from the database table will be mapped to the Employee entity class, and the entity class will be presented as a JSON object in your browser.
