Spring boot crud example with MySQL and Postman

Here we are discussing the Spring boot crud example with MySQL that will include all basic operations few queries for JPA and check responsesProject using Postman.

Project Explorer for Spring boot crud example with MySQL

Starting Spring boot Project with Spring Initializer

Open Spring Initializer

Click on Generate it will download file.

Open Eclipse IDE -> File-> import->Existing Maven Project.

Then import the project from a specific directory.

Open Java Resources-> src/main/java

then create packages

com.univ.app.controller
com.univ.app.dao
com.univ.app.entity
com.univ.app.service

Creating MySql Database

We have created a user and password and given all privileges to that account. created a database and created a table student.

CREATE USER 'sspu_userx25'@'localhost' IDENTIFIED BY 'MV7GG5TV2312';
GRANT ALL ON *.* TO 'sspu_userx25'@'localhost';
create database sspu;
use sspu;
create table student(
id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
roll_no bigint(20) unsigned NOT NULL,
fname varchar(100),
mname varchar(100),
lname varchar(30),
dob date,
admission_date date,
mail_id varchar(100),
mobile_no varchar(100),
semester varchar(100),
course varchar(100),
add_date timestamp DEFAULT CURRENT_TIMESTAMP,
primary key(id),
unique key(mobile_no),
unique key(mail_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8; 

To connect with the database all properties are at application.properties available in src/main/resources

spring.jpa.hibernate.ddl-auto=update
spring.datasource.url=jdbc:mysql://localhost:3306/sspu
spring.datasource.username=sspu_userx25
spring.datasource.password=MV7GG5TV2312
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.jpa.show-sql: true

Creating Files in Spring Boot Project

Entity Class

inside the com.univ.app.entity create a Student class (Student.java)

This class contains all filed that are available in the database and also its mapping.

package com.univ.app.entity;

import java.sql.Date;
import java.sql.Timestamp;
import java.util.Objects;

import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.Table;

@Entity
@Table(name = "student")
public class Student {
	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	@Column(name = "id")
	private Long id;
	@Column(name = "roll_no", nullable = false)
	private long rollNo;
	@Column(name = "fname", nullable = false)
	private String firstName;
	@Column(name = "mname", nullable = false)
	private String middleName;
	@Column(name = "lname", nullable = false)
	private String lastName;
	@Column(name = "dob", nullable = false)
	private Date dob;
	@Column(name = "admission_date", nullable = false)
	private Date admissionDate;
	@Column(name = "mail_id", nullable = false)
	private String mailId;
	@Column(name = "mobile_no", nullable = false)
	private String mobileNo;
	@Column(name = "semester", nullable = false)
	private String semester;
	@Column(name = "course", nullable = false)
	private String course;
	@Column(name = "add_date")
	private Timestamp addDate;

	public Student() {

	}

	public Student(Long id, long rollNo, String firstName, String middleName, String lastName, Date dob,
			Date admissionDate, String mailId, String mobileNo, String semester, String course, Timestamp addDate) {
		super();
		this.id = id;
		this.rollNo = rollNo;
		this.firstName = firstName;
		this.middleName = middleName;
		this.lastName = lastName;
		this.dob = dob;
		this.admissionDate = admissionDate;
		this.mailId = mailId;
		this.mobileNo = mobileNo;
		this.semester = semester;
		this.course = course;
		this.addDate = addDate;
	}

	@Override
	public int hashCode() {
		return Objects.hash(addDate, admissionDate, course, dob, firstName, id, lastName, mailId, middleName, mobileNo,
				rollNo, semester);
	}

	@Override
	public boolean equals(Object obj) {
		if (this == obj)
			return true;
		if (obj == null)
			return false;
		if (getClass() != obj.getClass())
			return false;
		Student other = (Student) obj;
		return Objects.equals(addDate, other.addDate) && Objects.equals(admissionDate, other.admissionDate)
				&& Objects.equals(course, other.course) && Objects.equals(dob, other.dob)
				&& Objects.equals(firstName, other.firstName) && Objects.equals(id, other.id)
				&& Objects.equals(lastName, other.lastName) && Objects.equals(mailId, other.mailId)
				&& Objects.equals(middleName, other.middleName) && Objects.equals(mobileNo, other.mobileNo)
				&& rollNo == other.rollNo && Objects.equals(semester, other.semester);
	}

	@Override
	public String toString() {
		return "Student [id=" + id + ", rollNo=" + rollNo + ", firstName=" + firstName + ", middleName=" + middleName
				+ ", lastName=" + lastName + ", dob=" + dob + ", admissionDate=" + admissionDate + ", mailId=" + mailId
				+ ", mobileNo=" + mobileNo + ", semester=" + semester + ", course=" + course + ", addDate=" + addDate
				+ "]";
	}

	public Long getId() {
		return id;
	}

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

	public long getRollNo() {
		return rollNo;
	}

	public void setRollNo(long rollNo) {
		this.rollNo = rollNo;
	}

	public String getFirstName() {
		return firstName;
	}

	public void setFirstName(String firstName) {
		this.firstName = firstName;
	}

	public String getMiddleName() {
		return middleName;
	}

	public void setMiddleName(String middleName) {
		this.middleName = middleName;
	}

	public String getLastName() {
		return lastName;
	}

	public void setLastName(String lastName) {
		this.lastName = lastName;
	}

	public Date getDob() {
		return dob;
	}

	public void setDob(Date dob) {
		this.dob = dob;
	}

	public Date getAdmissionDate() {
		return admissionDate;
	}

	public void setAdmissionDate(Date admissionDate) {
		this.admissionDate = admissionDate;
	}

	public String getMailId() {
		return mailId;
	}

	public void setMailId(String mailId) {
		this.mailId = mailId;
	}

	public String getMobileNo() {
		return mobileNo;
	}

	public void setMobileNo(String mobileNo) {
		this.mobileNo = mobileNo;
	}

	public String getSemester() {
		return semester;
	}

	public void setSemester(String semester) {
		this.semester = semester;
	}

	public String getCourse() {
		return course;
	}

	public void setCourse(String course) {
		this.course = course;
	}

	public Timestamp getAddDate() {
		return addDate;
	}

	public void setAddDate(Timestamp addDate) {
		this.addDate = addDate;
	}

}

Controller Class

Create StudentController inside com.univ.app.controller as below

We have created basic methods to add, update, delete, and fetch the record, also included methods to fetch records based on semester and course and the user can also update few fields separately like mail id and mobileNo.

The rest controller is used to access the data with different mapping styles like GetMapping, PostMapping, PutMapping etc.

package com.univ.app.controller;
import java.util.Optional;
import org.springframework.beans.factory.annotation.Autowired;
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.PutMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RestController;
import com.univ.app.entity.Student;
import com.univ.app.service.StudentService;
@RestController
public class StudentController {
	@Autowired
	StudentService service;
	@PostMapping("/add-student")
	public Student saveStudent(@RequestBody Student student) {
		return service.saveStudent(student);
	}
	@PostMapping("/add-students")
	public Iterable saveStudent(@RequestBody Iterable students) {
		return service.saveStudent(students);
	}
	@PutMapping("/update-student")
	public Student updateStudent(@RequestBody Student student) {
		return service.saveorUpdateStudent(student);
	}
	@GetMapping("/students")
	public Iterable getStudents() {
		return service.getStudents();
	}
	@GetMapping("/student/{id}")
	public Optional getStudent(@PathVariable("id") long id) {
		return service.getStudent(id);
	}
	@GetMapping("/student-fname/{firstname}")
	public Iterable getStudentbyFirstName(@PathVariable("firstname") String firstName) {
		return service.getStudentByFirstName(firstName);
	}
	@GetMapping("/student-course/{course}")
	public Iterable getStudentByCourse(@PathVariable("course") String course) {
		return service.getStudentByCourse(course);
	}
	@GetMapping("/student-semester/{semester}")
	public Iterable getStudentBySemester(@PathVariable("semester") String semester) {
		return service.getStudentBySemester(semester);
	}
	@GetMapping("/student-update-mail/{id}/{emailId}")
	public int updateStudentMail(@PathVariable("emailId") String emailId, @PathVariable("id") Long id) {
		return service.UpdateEmailId(emailId, id);
	}
	@GetMapping("/student-update-mobile/{id}/{mobileNo}")
	public int updateStudentMobile(@PathVariable("mobileNo") String mobileNo, @PathVariable("id") Long id) {
		return service.updateMobileNo(mobileNo, id);
	}
	@DeleteMapping("/remove-student/{id}")
	public void deleteById(@PathVariable("id") long id) {
		service.deleteById(id);
	}
}

Service class

StudentService.java inside com.univ.app.service

@Service annotation is used to make it a service class and dao is autowired to interact with StudentDAO.

package com.univ.app.service;
import java.util.Optional;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.univ.app.dao.StudentDAO;
import com.univ.app.entity.Student;
@Service
public class StudentService {
	@Autowired
	StudentDAO dao;
	public Student saveStudent(Student student) {
		return dao.save(student);
	}
	public Iterable saveStudent(Iterable students) {
		return dao.saveAll(students);
	}
	public Student saveorUpdateStudent(Student student) {
		Optional fetched = dao.findById(student.getId());
		Student s = fetched.get();
		s.setFirstName(student.getFirstName());
		s.setMiddleName(student.getMiddleName());
		s.setLastName(student.getLastName());
		s.setDob(student.getDob());
		s.setAdmissionDate(student.getAdmissionDate());
		s.setMailId(student.getMailId());
		s.setMobileNo(student.getMobileNo());
		s.setSemester(student.getSemester());
		s.setCourse(student.getCourse());
		s.setSemester(student.getSemester());
		return dao.save(s);
	}
	public Iterable getStudents() {
		return dao.findAll();
	}
	public Optional getStudent(long id) {
		return dao.findById(id);
	}
	public Iterable getStudentByFirstName(String firstName) {
		return dao.findStudentByFisrtName(firstName);
	}
	public Iterable getStudentByCourse(String course) {
		return dao.findStudentByCourse(course);
	}
	public Iterable getStudentBySemester(String semester) {
		return dao.findStudentBySemester(semester);
	}
	public void deleteById(long id) {
		dao.deleteById(id);
	}
	public int UpdateEmailId(String emailId, Long id) {
		return dao.updateEmailid(emailId, id);
	}
	public int updateMobileNo(String mobileNo, Long id) {
		return dao.updateMobileNo(mobileNo, id);
	}
}

DAO class

@Repository annotation is used to access data from a database.

StudentDAO extends JPARepositiiry.

Created Query for finding students by first name, course, and semester also created query for updating email and mobileNo.

package com.univ.app.dao;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;
import com.univ.app.entity.Student;
import jakarta.transaction.Transactional;
@Repository
public interface StudentDAO extends JpaRepository {
	@Query("SELECT s FROM Student s WHERE s.firstName = ?1")
	Iterable findStudentByFisrtName(String firstName);
	@Query("SELECT s FROM Student s WHERE s.course = ?1")
	Iterable findStudentByCourse(String course);
	@Query("SELECT s FROM Student s WHERE s.semester = ?1")
	Iterable findStudentBySemester(String semester);
	@Transactional
	@Modifying
	@Query("UPDATE Student s set s.mailId = ?1 where s.id=?2")
	int updateEmailid(String emailId, long id);
	@Transactional
	@Modifying
	@Query("UPDATE Student s set s.mobileNo = ?1 where s.id=?2")
	int updateMobileNo(String mobileNo, long id);
}

Checking Rest API with Postman

open postman and call APIs created in the controller. We called a few APIs as below.

Add Student API call

Get a Student with id

Update User Details

Similar way you can test all other APIs

Read More

Angular Spring Boot showing data in table

SpringBoot JPA @OneToMany Mapping : Fetch data from tables