Using CrudRepository in Spring Boot with MySQL for Data Manipulation

In a previous lesson we saw how we could connect a MySQL database in a core Java application using a JDBC connection. If you can remember previously we created a separate class for our database connection with all the configuration details and stuff. Spring Boot’s main ideology is to reduce the workforce spent on configurations and setting up the project and, to increase efficient time utilization on actual coding. And also a simple yet powerful class in Spring Framework for data manipulation is CrudRepository. In this tutorial we will connect our Spring Bootapplication with a MySQL database and use CrudRepository for CRUD operations.

STEP 1 – Initialize the Spring Boot Project

If you are using Spring Tools Suite (STS) as your IDE for development, you can use it’s inbuilt New Spring Boot Project feature. If you’re not using it, just like me, you can use the online Spring initializer to initialize the project you want. I prefer Maven for my dependency management purposes over Gradle. Select Java as the language and I have selected 2.2.1 (SNAPSHOT) as the preferred Spring Boot version.

Dependencies required

We require mainly 3 dependencies added to our project.

  1. Spring Web
  2. MySQL Driver
  3. Spring Data JPA

Once all the fields have been filled out, click on the Generate button which will download the Spring Maven Project to your local machine. Then open the project using text editor or an IDE (I usually use Eclipse IDE) and you will be able to see a folder structure something similar to following.

spring maven file structure
spring maven file structure

Step 2 – Creating the application.properties file

Spring Boot allows you to concentrate more on actual coding and reduce configurations. We can use application.properties file to define our database connection attributes and Spring will automatically create the necessary connection using the provided attributes.

Create an application.properties file in the src/main/resources folder if it does not already exist.

application.properties file
application.properties file

We will be only using the following lines of configurations in the application.properties file.

application.properties

spring.jpa.hibernate.ddl-auto=none
spring.datasource.url=jdbc:mysql://${MYSQL_HOST:localhost}:3306/student
spring.datasource.username=root
spring.datasource.password=root
spring.jpa.hibernate.ddl-auto=none

Hibernate provides several options; none, update, create, create-drop . Since we are not doing any change to the database structure, we leave it as none here. (Actually you need not to define this here explicitly, since for all the databases other than default H2 database the default value is none)


spring.datasource.url=jdbc:mysql://${MYSQL_HOST:localhost}:3306/student

Specifies the jdbc url of your database. We will be using the same student database with student table which was used in the previous core java + mysql tutorial


spring.datasource.username=root
spring.datasource.password=root

Specifies the database username and password.

Step 3 – Creating the Student Entity Class

Student.java

package com.example.demo;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;

@Entity
public class Student {
	
	@Id
	@GeneratedValue(strategy=GenerationType.AUTO)
	private int id;
	
	private String name;
	private String address;
	private String course_code;

	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	

	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
		

	public String getAddress() {
		return address;
	}
	public void setAddress(String address) {
		this.address = address;
	}
		

	public String getCourse_code() {
		return course_code;
	}
	public void setCourse_code(String course_code) {
		this.course_code = course_code;
	}
}

Hibernate automatically translates any @Entity annotated class in to a table. Hence make sure you create the Entity class, named exactly same as in the database table. Or you can adjust the application.properties file’s spring.jpa.hibernate.ddl-auto configuration from none to update which, Hibernate will change the database according to the given entity structure.

@Id
@GeneratedValue(strategy=GenerationType.AUTO)
private int id;

@Id annotation specifies the primary key of the Entity. And @GeneratedValue(strategy=GenerationType.AUTO) specifies that for any new record inserted the primary key will be auto generated.

Step 4 – Creating the Repository – Extending Spring CrudRepository

In Spring Framework, The main idea of CrudRepository is to give user the opportunity to use general CRUD operations with data without creating own implementation. We can specifically define our own queries using @NameQuery or @Query annotations which, Spring will take care of implementing them. We will discuss about this in a separate tutorial.

For this tutorial, we will use only the inbuilt CRUD operations. For that we only need to create the StudentRepository interface extending CrudRepository interface.

StudentRepository.java

package com.example.demo;

import org.springframework.data.repository.CrudRepository;

public interface StudentRepository extends CrudRepository<Student, Integer>{

}

public interface StudentRepository extends CrudRepository<Student, Integer>{

First parameter (Student) is the type of entity which the Repository is working with. The second parameter (Integer) is the type of Id (Primary Key) of this entity.


Step 5 – Creating the Services Class

@Service annotation is used to indicate classes with some business functionalities or business logic. In our example since our focus is on CRUD operations on MySQL, we will first create methods to create, retrieve, update & delete (CRUD) data from our MySQL database in this service class. We will be using Spring Framework’s CrudRepository functionalities inside this service class to manipulate data.

StudentService.java

package com.example.demo;

import java.util.Optional;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service
public class StudentService {
	
	@Autowired
	private StudentRepository studentRepository;
	
	// Add new student
	public String addStudent(Student s) {
		
		try {
			studentRepository.save(s);
			return "saved";
		} catch(Exception e) {
			return "failed";
		}
	}


        // Update a Student
	public String updateStudent(Integer id, Student s) {
		try {
			s.setId(id);
			studentRepository.save(s);
			return "Updated";
		}catch(Exception e) {
			return "Failed";
		}
	}


	// Get all students
	public Iterable<Student> getAllStudent(){
		return studentRepository.findAll();
	}

	
	// Get single student by Id
	public Optional<Student> getStudent(Integer id) {
		return studentRepository.findById(id);
	}

	
	// Delete a Student
	public String deleteStudent(Integer id) {
		try{
			studentRepository.deleteById(id);
			return "Deleted";
		}catch(Exception e) {
			return "Failed";
		}
	}

	
}

@Autowired
private StudentRepository studentRepository;

Previously created StudentRepository bean has been injected using @Autowired annotation.


// Add new student
public String addStudent(Student s) {
		
	try {
		studentRepository.save(s);
		return "saved";
	} catch(Exception e) {
		return "failed";
	}
}

addStudent() method facilitates adding a new student to the database. We have used CrudRepository’s save() method here.


// Update a Student
public String updateStudent(Integer id, Student s) {
	try {
		s.setId(id);
		studentRepository.save(s);
		return "Updated";
	}catch(Exception e) {
		return "Failed";
	}
}

updateStudent() method facilitates updating an existing student record. We can use the same save() method in CrudRepository interface for updating as well. The only difference is that we need to set the primary key of the record to be updated of the new Student object. If the primary key is found in the database, it will automatically update the record with the new values.


// Get all students
public Iterable<Student> getAllStudent(){
	return studentRepository.findAll();
}

getAllStudent() method facilitates retrieving all the student records in the database. For this we use the findAll() method in Crudrepository interface.


// Get single student by Id
public Optional<Student> getStudent(Integer id) {
	return studentRepository.findById(id);
}

getStudent() method facilitates retriving single Student record from the database by id (primary key). We use the CrudRepository’s findById() method for this. It takes the primary key or the id of the record to be retrieved as the only input parameter.


// Delete a Student
public String deleteStudent(Integer id) {
	try{
		studentRepository.deleteById(id);
		return "Deleted";
	}catch(Exception e) {
		return "Failed";
	}
}

deleteStudent() method facilitates deleting a single Student record from the database by id (primary key). We use the deleteById() method for this. It takes the primary key or the id of the record to be deleted as the only input parameter.


Apart from above mentioned functionalities in CrudRepository interface, there are some other methods as well.

  • <S extends T> Iterable<S> saveAll(Iterable<S> entities) – Saves all the given entities.
  • boolean existsById(ID id) – Returns whether an entity with the given id exists.
  • long count() – Returns the number of entities available.
  • void delete(T entity) – Deletes a given entity.
  • void deleteAll(Iterable<? extends T> entities) – Deletes the given entities.
  • void deleteAll() – Deletes all entities managed by the repository.

Step 5 – Creating the Main Controller

We are about to expose the functionality of these CRUD operations as a web service to the outside world. For that we need to create a controller class which exposes endpoints to outside. We can use @Controller or @RestController annotations to declare such class. Both works fine but there’s a slight difference. RestController is a combination of Controller + Responsebody in Spring MVC.

In this tutorial we will use @Controller to annotate our class.

MainController.Java

package com.example.demo;

import java.util.Optional;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
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.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

@Controller
@RequestMapping(path="/student")
public class MainController {
	
	@Autowired
	private StudentService studentService;
	
	// Add new student
	@PostMapping(path="/add")
	public @ResponseBody String addNewStudent (@RequestBody Student s) {
		return studentService.addStudent(s);
	}
	
	// Get all students
	@GetMapping(path="/all")
	public @ResponseBody Iterable<Student> getAllStudent() {
		return studentService.getAllStudent();
	}
	
	// Get single student by Id
	@GetMapping(path="/{id}")
	public @ResponseBody Optional<Student> getStudentById(@PathVariable(name = "id") Integer id) {
		return studentService.getStudent(id);
	}
	
	// Update a Student
	@PostMapping(path="/update/{id}")
	public @ResponseBody String updateStudent(@PathVariable(name = "id") Integer id, @RequestBody 
        Student student) {
		return studentService.updateStudent(id,student);
	}

	
	// Delete a Student
	@DeleteMapping(path="/delete/{id}")
	public @ResponseBody String deleteStudent(@PathVariable(name = "id") Integer id) {
		return studentService.deleteStudent(id);
	}
}

@Controller
@RequestMapping(path="/student")
public class MainController {

@RequestMapping(path=”/student”) refers to the base URL of the API. If we host this using localhost, we will be having something like, http:localhost:8080/student as our base URL.

This concludes the tutorial on how to use CrudRepository in Spring Framework to manipulate data in a database. We have used MySQL, it will be the same for any other database type with minor changes. If you have any questions or issues regarding this tutorial please comment below.

Get full code from Github : $ git clone https://github.com/harinda05/crudrepository-with-spring.git

Leave a Reply

Your email address will not be published. Required fields are marked *