crud operations in spring boot with Mysql

In this tutorial, we will learn crud operations in spring boot with Mysql

If you want to learn more about connecting MySQL from spring boot, please follow this link https://beginnersbug.com/connect-mysql-database-from-spring-boot/

What you’ll learn

End of this tutorial, you will learn crud operations in spring boot with Mysql

Save syntax

// Here studentsDao is the repository interface
studentsDao.save(entity);

Select all syntax

// Here studentsDao is the repository interface
studentsDao.findAll();

Select by id syntax

// Here studentsDao is the repository interface
studentsDao.findById(id);

Delete all syntax

// Here studentsDao is the repository interface
studentsDao.deleteAll();

Delete by id syntax

// Here studentsDao is the repository interface
studentsDao.deleteById(id);

Database Scripts

CREATE TABLE students (
    id int NOT NULL,
	firstname varchar(255) NOT NULL,
    lastname varchar(255) NOT NULL,    
    department int,
    PRIMARY KEY (id)
);

Model Class

import java.io.Serializable;

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

/**
 * The persistent class for the students database table.
 * 
 */
@Entity
@Table(name = "students")
@NamedQuery(name = "Student.findAll", query = "SELECT s FROM Student s")
public class Student implements Serializable {

	private static final long serialVersionUID = 1L;

	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	private Long id;

	private String department;

	private String firstname;

	private String lastname;

	public Student() {
	}

	public Long getId() {
		return id;
	}

	public void setId(Long id) {
		this.id = id;
	}

	public String getDepartment() {
		return this.department;
	}

	public void setDepartment(String department) {
		this.department = department;
	}

	public String getFirstname() {
		return this.firstname;
	}

	public void setFirstname(String firstname) {
		this.firstname = firstname;
	}

	public String getLastname() {
		return this.lastname;
	}

	public void setLastname(String lastname) {
		this.lastname = lastname;
	}

}

JpaRepository

Here I am using JpaRepository to achieve CRUD Operations easily. JpaRepository have inbuilt function like findAll,findById,save,delete,deleteById

StudentDao

You need to extend JpaRepository in your dao class with Entity class as like below

Here my Entity class is Students.java and Primary Key variable is Long

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> {

}

Controller Class

import java.util.List;
import java.util.Optional;

import javax.validation.Valid;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;

import com.beginnersbug.studentservice.dao.StudentDao;
import com.beginnersbug.studentservice.model.Student;

@RestController()
@RequestMapping("/api/student")
public class StudentController {

	@Autowired
	StudentDao studentsDao;

	@RequestMapping(method = RequestMethod.GET)
	public List<Student> getStudentsList() {
		return studentsDao.findAll();
	}

	@RequestMapping(value = "/{id}", method = RequestMethod.GET)
	public Student getStudent(@PathVariable("id") String id) {
		Optional<Student> findById = studentsDao.findById(Long.parseLong(id));
		return findById.get();
	}

	@RequestMapping(method = RequestMethod.POST)
	public ResponseEntity<String> addUser(@RequestBody Student student) {
		studentsDao.save(student);
		return new ResponseEntity<String>("Student Created Successfully", HttpStatus.CREATED);

	}

	@RequestMapping(method = RequestMethod.PUT)
	public ResponseEntity<String> updateUser(@Valid @RequestBody Student student) {
		Student updatedStudent = studentsDao.findById(student.getId()).get();
		updatedStudent.setFirstname(student.getFirstname());
		updatedStudent.setLastname(student.getLastname());
		studentsDao.save(updatedStudent);
		return new ResponseEntity<String>("Student Updated Sucessfully ", HttpStatus.NO_CONTENT);

	}

	@RequestMapping(method = RequestMethod.DELETE)
	public ResponseEntity<String> deleteAllStudents() {
		studentsDao.deleteAll();
		return new ResponseEntity<String>("Student deleted ", HttpStatus.NO_CONTENT);
	}

	@RequestMapping(value = "/{id}", method = RequestMethod.DELETE)
	public ResponseEntity<String> deleteStudent(@PathVariable("id") String id) {
		studentsDao.deleteById(Long.parseLong(id));
		return new ResponseEntity<String>("Student deleted ", HttpStatus.NO_CONTENT);
	}

}

In the above controller class, We are calling findAll, findById, save, delete, deleteById methods for CRUD Operations

Advantage of using JpaRepository

you don’t need to write any query or any methods, all are inbuilt in JpaRepository class

Exceptions

java.sql.SQLException: Field ‘id’ doesn’t have a default value

You will have chance to get above exception while implementing. Make sure below things

You should have below annotation in the model class @GeneratedValue(strategy=GenerationType.IDENTITY)

And also make sure your table has primary key & auto_increment parameter

org.hibernate.id.IdentifierGenerationException: ids for this class must be manually assigned before calling save()

Make sure your model class have @id and @GeneratedValue(strategy = GenerationType.IDENTITY)

Github

https://github.com/rkumar9090/student-service

connect MySQL database from spring boot