In this tutorial, we will learn to connect MySQL database from spring boot with Spring Data
What is Spring Data
It makes it easy to use data access technologies, relational and non-relational databases, map-reduce frameworks, and cloud-based data services.
What You Will learn
End of this tutorial, you will learn to connect MySQL database from spring boot. You can execute SQL queries from Spring boot
Mysql Database Scripts
create database beginnersbug;
use beginnersbug;
CREATE TABLE students (
id int NOT NULL,
firstname varchar(255) NOT NULL,
lastname varchar(255) NOT NULL,
department int,
PRIMARY KEY (id)
);
Dependency
<!-- Added for Database connection -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
application.properties
# Add database url here
spring.datasource.url=jdbc:mysql://localhost:3306/beginnersbug
spring.datasource.username=root
spring.datasource.password=password
# Add driver class here
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5Dialect
Below class the representation of Students table
Students.java
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
@Entity
public class Students {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
@Column(name = "id")
private long id;
@Column(name = "firstname")
private String firstName;
@Column(name = "lastname")
private String lastName;
@Column(name = "department")
private String department;
public long getId() {
return id;
}
public void setId(long id) {
this.id = id;
}
public String getFirstName() {
return firstName;
}
public void setFirstName(String firstName) {
this.firstName = firstName;
}
public String getLastName() {
return lastName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
public String getDepartment() {
return department;
}
public void setDepartment(String department) {
this.department = department;
}
}
Here we extend JpaRepository. which have predefined methods like findAll(),findById(),findAllById(),save(),delete() methods
StudentDao.java
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;
import com.beginnersbug.student.model.Students;
@Repository
public interface StudentDao extends JpaRepository<Students, Long> {
}
StudentController.java
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 com.beginnersbug.student.dao.StudentDao;
import com.beginnersbug.student.model.Students;
// This annotation used to mention a class as Controller class
@RestController
public class StudentController {
@Autowired
StudentDao studentsDao;
@GetMapping("")
public List<Students> getStudentsList() {
List<Students> findAll = studentsDao.findAll();
return findAll;
}
}
@Entity
Specifies that the class is an entity. It should be the replication of table
@Id
Specifies the primary key of an entity. The primary key of the table should be defined as @id
@GeneratedValue(strategy = GenerationType.AUTO)
This will specify the generation strategies for the values of primary keys while inserting a record in table.
@Column(name = “id”)
Specifies the mapped column for a persistent property or field. If no Column
annotation is specified, the default values apply.
@Repository
This annotation will specify the interface as Dao Class
Time needed: 45 minutes
Steps
- Create Spring boot Project
Follow this tutorial to create spring boot application
https://beginnersbug.com/how-to-create-spring-boot-application/ - Create Database
Open Mysql Database and create database using below command
create database beginnersbug; - Create Table
Create Table with below command
use beginnersbug;
CREATE TABLE students (
id int NOT NULL,
firstname varchar(255) NOT NULL,
lastname varchar(255) NOT NULL,
department int,
PRIMARY KEY (id)
); - Create Entity class for Students table
Please refer above Students.java
- Create Dao Interface
please refer above StudentDao.java
- Create Controller class
Refer above StudentController.java
- Run
Navigate to main class. Right Click and click on RunAs –>JavaApplication
- Testing
Before testing please add some entry in students table
Open Browser http://localhost:8080/ . You can see the result in the browser
Github
https://github.com/rkumar9090/student
Related Articles
how to create spring boot application