connect MySQL database from spring boot

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

Github

https://github.com/rkumar9090/student

Related Articles

how to create spring boot application