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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | 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
1 2 3 4 5 6 | 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 | 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 | 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<Student> saveStudent(@RequestBody Iterable<Student> students) { return service.saveStudent(students); } @PutMapping("/update-student") public Student updateStudent(@RequestBody Student student) { return service.saveorUpdateStudent(student); } @GetMapping("/students") public Iterable<Student> getStudents() { return service.getStudents(); } @GetMapping("/student/{id}") public Optional<Student> getStudent(@PathVariable("id") long id) { return service.getStudent(id); } @GetMapping("/student-fname/{firstname}") public Iterable<Student> getStudentbyFirstName(@PathVariable("firstname") String firstName) { return service.getStudentByFirstName(firstName); } @GetMapping("/student-course/{course}") public Iterable<Student> getStudentByCourse(@PathVariable("course") String course) { return service.getStudentByCourse(course); } @GetMapping("/student-semester/{semester}") public Iterable<Student> 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 | 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<Student> saveStudent(Iterable<Student> students) { return dao.saveAll(students); } public Student saveorUpdateStudent(Student student) { Optional<Student> 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<Student> getStudents() { return dao.findAll(); } public Optional<Student> getStudent(long id) { return dao.findById(id); } public Iterable<Student> getStudentByFirstName(String firstName) { return dao.findStudentByFisrtName(firstName); } public Iterable<Student> getStudentByCourse(String course) { return dao.findStudentByCourse(course); } public Iterable<Student> 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | 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<Student, Long> { @Query("SELECT s FROM Student s WHERE s.firstName = ?1") Iterable<Student> findStudentByFisrtName(String firstName); @Query("SELECT s FROM Student s WHERE s.course = ?1") Iterable<Student> findStudentByCourse(String course); @Query("SELECT s FROM Student s WHERE s.semester = ?1") Iterable<Student> 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