Integration of SpringBoot with MySQL using Spring JPA

Table of contents

Reading Time: 2 minutes

In this blog, we will learn how we can create CRUD using MySQL as a database using JPA.
Create Spring-boot Project using https://start.spring.io/ and make sure to add the following dependencies.

This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters

Show hidden characters

	<!– jpa,repository –>
	<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-data-jpa</artifactId>
	</dependency>

	<!– MySQL –>
	<dependency>
	<groupId>mysql</groupId>
	<artifactId>mysql-connector-java</artifactId>
	</dependency>

view raw

gistfile1.txt

hosted with ❤ by GitHub

Configure MySQL
Before starting with the example, just add the following configurations for MYSQL in the application.properties file

Show hidden characters

	spring.datasource.url=jdbc:mysql://localhost:3306/database_name
	spring.datasource.username=root
	spring.datasource.password=root

view raw

gistfile1.txt

hosted with ❤ by GitHub

Let’s take a simple example and persists the following JSON document in the database.

{
"id" : "1",
"name" : "Bhawna",
"age": 24
}

Add Entity Class
Add the following entity class that represents the above JSON document

Show hidden characters

	import lombok.AllArgsConstructor;
	import lombok.Builder;
	import lombok.Getter;
	import lombok.NoArgsConstructor;
	import lombok.Setter;

	import javax.persistence.Column;
	import javax.persistence.Entity;
	import javax.persistence.Id;
	import javax.persistence.Table;
	import java.sql.Timestamp;

	@Getter
	@Table(name = "student")
	@Entity
	@Builder
	@NoArgsConstructor
	@AllArgsConstructor
	public class StudentInfo {

	@Id
	@Column(name = "id")
	String id;

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

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

view raw

gistfile1.txt

hosted with ❤ by GitHub

@Entity annotation specifies that this POJO is an entity that needs to persist in the database.
@Table annotation allows us to provide the details of the table like name, schema, and constraints. In the above example, we are just mentioning the table name.
@Column annotation maps the POJO field with the table field.
@Id annotation specifies that the particular field will be treated as Primary key.

Create Repository

import org.springframework.data.jpa.repository.JpaRepository;
        import org.springframework.stereotype.Repository;

@Repository
public interface StudentRepository extends JpaRepository<StudentInfo, String> {

}

Here, JPARepository extends PagingAndSortingRepository and QueryByExampleExecutor. And internally PagingAndSortingRepository extends CrudRepository. Therefore JPARepository provides all the methods of PagingAndSortingRepository, QueryByExampleExecutor, and CrudRepository.

We can also add derivable query methods in the repository. For example:

//It will find the Student Information by name.
StudentInfo findByName(String name);

Add Controller
Now, what we need is the Controller class that simply invoke our repository method.

Show hidden characters

	import com.knoldus.wallet.model.StudentInfo;
	import com.knoldus.wallet.repository.StudentRepository;
	import org.springframework.beans.factory.annotation.Autowired;
	import org.springframework.http.HttpStatus;
	import org.springframework.web.bind.annotation.DeleteMapping;
	import org.springframework.web.bind.annotation.GetMapping;
	import org.springframework.web.bind.annotation.PathVariable;
	import org.springframework.web.bind.annotation.PostMapping;
	import org.springframework.web.bind.annotation.RequestBody;
	import org.springframework.web.bind.annotation.RestController;
	import org.springframework.web.client.HttpClientErrorException;

	@RestController
	public class StudentController {

	@Autowired
	private StudentRepository studentRepository;

	@PostMapping("/student")
	public StudentInfo addStudentDetails(@RequestBody StudentInfo studentInfo) {
	return studentRepository.save(studentInfo);
	}

	@GetMapping("/student/{id}")
	public StudentInfo getStudentById(@PathVariable String id) {
	return studentRepository.findById(id)
	.orElseThrow(() -> new HttpClientErrorException(HttpStatus.NOT_FOUND));
	}

	@DeleteMapping("/student/{id}")
	public void deleteStudentById(@PathVariable String id) {
	studentRepository.deleteById(id);
	}

	@GetMapping("/student/name/{name}")
	public StudentInfo getStudentByName(@PathVariable String name) {
	return studentRepository.findByName(name)
	.orElseThrow(() -> new HttpClientErrorException(HttpStatus.NOT_FOUND));

	}
	}

view raw

gistfile1.txt

hosted with ❤ by GitHub

So, you can see that in the above example, we are using some inbuilt methods provided by JPA and derivable query method.

Conclusion:

So, it’s pretty easy to build Spring-boot application with JPA. First of all, configure MySQL properties in the application.properties file. Add the entity that you want to persist in DB. Add repository that can consist of some derivable methods and controller that integrate the repository with the rest API.
Hope this is helpful. Please feel free to provide your suggestions 🙂

References:
https://spring.io/guides/gs/accessing-data-mysql/