Spring Boot Crud Project using Mysql Database

Posted on September 22, 2020

16 min read

32,163

This Spring Boot tutorial will teach you how to do basic database functions that are CREATE RETIEVE UPDATE and DELETE . using Mysql Database. The INSERT, SELECT, UPDATE and DELETE statements can be used in any database system, because this is support by all relational database systems.

We will learn how to INSERT, SELECT, UPDATE and DELETE in database by writing code to manage the student table in the database named lindaschool. student table consist of following columns studentname,course,fee.

The package structure, you must following the standard package structure of spring boot and spring framework.for the example i have created the project name emp

com.example.emp
|
-------------------->EmpApplication.java
com.example.emp.service
|
--------------------->StudentService
com.example.emp.controller
|
----------------------> StudentController.java
com.example.emp.repository
|
-----------------------> StudentRepository.java
com.example.emp.domain
|
------------------------>Student.java

com.example.emp

-------------------->EmpApplication.java

com.example.emp.service

--------------------->StudentService

com.example.emp.controller

----------------------> StudentController.java

com.example.emp.repository

-----------------------> StudentRepository.java

com.example.emp.domain

------------------------>Student.java

when you create the spring boot application by default EmpApplication.java is created

package com.example.emp;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class EmpApplication {

	public static void main(String[] args) {
		SpringApplication.run(EmpApplication.class, args);
	}

}

package com.example.emp;

import org.springframework.boot.SpringApplication;

import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication

public class EmpApplication {

public static void main(String[] args) {

SpringApplication.run(EmpApplication.class, args);

}

First you must Create the package com.example.emp.service . inside the package you have to create the class

StudentService.java

package com.example.emp.service;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.example.emp.domain.Student;
import com.example.emp.repository.StudentRepository;

@Service
public class StudentService {
	
	@Autowired
    private StudentRepository repo;
	
	public List<Student> listAll() {
        return repo.findAll();
    }
     
    public void save(Student std) {
        repo.save(std);
    }
     
    public Student get(long id) {
        return repo.findById(id).get();
    }
     
    public void delete(long id) {
        repo.deleteById(id);
    }

}

package com.example.emp.service;

import java.util.List;

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

import org.springframework.stereotype.Service;

import com.example.emp.domain.Student;

import com.example.emp.repository.StudentRepository;

@Service

public class StudentService {

@Autowired

private StudentRepository repo;

public List<Student> listAll() {

return repo.findAll();

}

public void save(Student std) {

repo.save(std);

}

public Student get(long id) {

return repo.findById(id).get();

}

public void delete(long id) {

repo.deleteById(id);

}

after that you have to Create the package com.example.emp.controller . inside the package you have to create the class

Student Controller.java

package com.example.emp.controller;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.servlet.ModelAndView;

import com.example.emp.domain.Student;
import com.example.emp.service.StudentService;

@Controller
public class StudentController {
	
	 @Autowired
	    private StudentService service;

	    @GetMapping("/")
	    public String viewHomePage(Model model) {
	        List<Student> liststudent = service.listAll();
	        model.addAttribute("liststudent", liststudent);
	        System.out.print("Get / ");	
	        return "index";
	    }

	    @GetMapping("/new")
	    public String add(Model model) {
	        model.addAttribute("student", new Student());
	        return "new";
	    }

	    @RequestMapping(value = "/save", method = RequestMethod.POST)
	    public String saveStudent(@ModelAttribute("student") Student std) {
	        service.save(std);
	        return "redirect:/";
	    }

	    @RequestMapping("/edit/{id}")
	    public ModelAndView showEditStudentPage(@PathVariable(name = "id") int id) {
	        ModelAndView mav = new ModelAndView("new");
	        Student std = service.get(id);
	        mav.addObject("student", std);
	        return mav;
	        
	    }
	    @RequestMapping("/delete/{id}")
	    public String deletestudent(@PathVariable(name = "id") int id) {
	        service.delete(id);
	        return "redirect:/";
	    }
}

package com.example.emp.controller;

import java.util.List;

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

import org.springframework.stereotype.Controller;

import org.springframework.ui.Model;

import org.springframework.web.bind.annotation.GetMapping;

import org.springframework.web.bind.annotation.ModelAttribute;

import org.springframework.web.bind.annotation.PathVariable;

import org.springframework.web.bind.annotation.RequestMapping;

import org.springframework.web.bind.annotation.RequestMethod;

import org.springframework.web.servlet.ModelAndView;

import com.example.emp.domain.Student;

import com.example.emp.service.StudentService;

@Controller

public class StudentController {

@Autowired

private StudentService service;

@GetMapping("/")

public String viewHomePage(Model model) {

List<Student> liststudent = service.listAll();

model.addAttribute("liststudent", liststudent);

System.out.print("Get / ");

return "index";

}

@GetMapping("/new")

public String add(Model model) {

model.addAttribute("student", new Student());

return "new";

}

@RequestMapping(value = "/save", method = RequestMethod.POST)

public String saveStudent(@ModelAttribute("student") Student std) {

service.save(std);

return "redirect:/";

}

@RequestMapping("/edit/{id}")

public ModelAndView showEditStudentPage(@PathVariable(name = "id") int id) {

ModelAndView mav = new ModelAndView("new");

Student std = service.get(id);

mav.addObject("student", std);

return mav;

}

@RequestMapping("/delete/{id}")

public String deletestudent(@PathVariable(name = "id") int id) {

service.delete(id);

return "redirect:/";

}

First you must Create the package com.example.emp.repository inside the package you have to create the class

StudentRepository.java

package com.example.emp.repository;

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

import com.example.emp.domain.Student;


@Repository
public interface StudentRepository extends JpaRepository<Student, Long> {

}

package com.example.emp.repository;

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

import org.springframework.stereotype.Repository;

import com.example.emp.domain.Student;

@Repository

public interface StudentRepository extends JpaRepository<Student, Long> {

}

First you must Create the package com.example.emp.domain inside the package you have to create the class

Student.java

package com.example.emp.domain;

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

@Entity
public class Student {
	@Id
    @GeneratedValue(strategy= GenerationType.IDENTITY)
    private Long id;
    private String studentname;
    private String course;
    private int fee;
	public Student() {

	}
	public Student(Long id, String studentname, String course, int fee) {
	
		this.id = id;
		this.studentname = studentname;
		this.course = course;
		this.fee = fee;
	}
	public Long getId() {
		return id;
	}
	public void setId(Long id) {
		this.id = id;
	}
	public String getStudentname() {
		return studentname;
	}
	public void setStudentname(String studentname) {
		this.studentname = studentname;
	}
	public String getCourse() {
		return course;
	}
	public void setCourse(String course) {
		this.course = course;
	}
	public int getFee() {
		return fee;
	}
	public void setFee(int fee) {
		this.fee = fee;
	}

}

package com.example.emp.domain;

import javax.persistence.Entity;

import javax.persistence.GeneratedValue;

import javax.persistence.GenerationType;

import javax.persistence.Id;

@Entity

public class Student {

@Id

@GeneratedValue(strategy= GenerationType.IDENTITY)

private Long id;

private String studentname;

private String course;

private int fee;

public Student() {

}

public Student(Long id, String studentname, String course, int fee) {

this.id = id;

this.studentname = studentname;

this.course = course;

this.fee = fee;

}

public Long getId() {

return id;

}

public void setId(Long id) {

this.id = id;

}

public String getStudentname() {

return studentname;

}

public void setStudentname(String studentname) {

this.studentname = studentname;

}

public String getCourse() {

return course;

}

public void setCourse(String course) {

this.course = course;

}

public int getFee() {

return fee;

}

public void setFee(int fee) {

this.fee = fee;

}

after that you have to create the index.html page inside the src/main/resources inside the package you have a templates folder inside the folder you must create index.html page

<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml"
      xmlns:th="http://www.thymeleaf.org">

<head>
	<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootswatch/4.5.2/cosmo/bootstrap.min.css" />
	<script src= "https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/js/bootstrap.min.js" ></script>
</head>
<body>
<div>
	<h2 >Spring Boot Crud System -Student Registation</h2>
	<tr>
		<div align = "left" >		   
		     <h3><a  th:href="@{'/new'}">Add new</a></h3>     
	    </div>
	
	</tr>
	<tr>
	
	<div class="col-sm-5" align = "center">
                 <div class="panel-body" align = "center" >
                 <table class="table">
  <thead class="thead-dark">
    <tr>
      		<th>Student ID</th>
            <th>Student Name</th>
            <th>Course</th>
            <th>Fee</th>
            <th>Edit</th>
            <th>Delete</th>
   	</tr>
  </thead>
  <tbody>
      <tr  th:each="student : ${liststudent }">
		<td th:text="${student.id}">Student ID</td>
		<td th:text="${student.studentname}">StudentName</td>
		<td th:text="${student.course}">Course</td>
		<td th:text="${student.fee}">Fee</td>				
		<td>
			<a th:href="@{'/edit/' + ${student.id}}">Edit</a>
		</td>							    
		<td>
			<a th:href="@{'/delete/' + ${student.id}}">Delete</a>
		</td>		    
		</tr> 
   
  </tbody>
</table>
                    
                 </div>

            </div> 

	</tr>

	</tbody>
	</table>
	<div>
</body>
</html>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml"

xmlns:th="http://www.thymeleaf.org">

<head>

</head>

<body>

<div>

<h2 >Spring Boot Crud System -Student Registation</h2>

<tr>

</div>

</tr>

<tr>

<tr>

<th>Student ID</th>

<th>Student Name</th>

<th>Course</th>

<th>Delete</th>

</tr>

</thead>

<tbody>

<td th:text="${student.id}">Student ID</td>

<td th:text="${student.studentname}">StudentName</td>

<td th:text="${student.course}">Course</td>

<td>

</td>

<td>

<a th:href="@{'/delete/' + ${student.id}}">Delete</a>

</td>

</tr>

</tbody>

</table>

</div>

</tr>

</tbody>

</table>

<div>

</body>

</html>

inside the folder create the another file new.html

<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml"
      xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="utf-8" />
    <title>Create New Product</title>
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootswatch/4.5.2/cosmo/bootstrap.min.css" />
    <script src= "https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/js/bootstrap.min.js" ></script>
</head>
<body>
<div align="center">
    <h1>Create New Student</h1>
    <br />
     <div class="col-sm-4">
    <form action="#" th:action="@{/save}" th:object="${student}" method="post">

      
 		<div alight="left">
            <tr>
                <label class="form-label" >Student Name</label>
                <td>
	                <input type="hidden" th:field="*{id}" />
	                <input type="text" th:field="*{studentname}" class="form-control" placeholder="Student Name" />
                </td>
            </tr>
         </div>   
            
          	<div alight="left">
          
            <tr>
                 <label class="form-label" >Course</label>
                <td><input type="text" th:field="*{course}" class="form-control" placeholder="Course" /></td>
            </tr>
            </div> 
            <div alight="left">
                 <tr>
                 <label class="form-label" >Fee</label>
                <td><input type="text" th:field="*{fee}" class="form-control" placeholder="Fee" /></td>
            </tr>
            </div> 
			<br>
            <tr>
            <td colspan="2"><button type="submit" class="btn btn-info">Save</button> </td>
            </tr>
        </table>
    </form>
</div>
</body>
</html>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml"

xmlns:th="http://www.thymeleaf.org">

<head>

<title>Create New Product</title>

</head>

<body>

<h1>Create New Student</h1>

<br />

<tr>

<label class="form-label" >Student Name</label>

<td>

</td>

</tr>

</div>

<tr>

<label class="form-label" >Course</label>

</tr>

</div>

<tr>

</tr>

</div>

<br>

<tr>

</tr>

</table>

</form>

</div>

</body>

</html>

After that you must set database path and project configuration in application.properties file

spring.datasource.url=jdbc:mysql://localhost:3306/lindaschool?useUnicode=true&useJDBCCompliantTimezone
Shift=true&useLegacyDatetimeCode=false&serverTimezone=UTC
server.error.whitelabel.enabled=false
server.port=7050
spring.datasource.username=root
spring.datasource.password=

spring.jpa.open-in-view=false
spring.thymeleaf.cache=false
api.base.path = http://localhost:7050  // this the port i set where tomcat run

spring.datasource.url=jdbc:mysql://localhost:3306/lindaschool?useUnicode=true&useJDBCCompliantTimezone

Shift=true&useLegacyDatetimeCode=false&serverTimezone=UTC

server.error.whitelabel.enabled=false

server.port=7050

spring.datasource.username=root

spring.datasource.password=

spring.jpa.open-in-view=false

spring.thymeleaf.cache=false

api.base.path = http://localhost:7050 // this the port i set where tomcat run

Spring Boot Crud Project using Mysql Database

I have attached the video tutorial below it will help you to do this step by step.

Spring boot with React MongoDB database Full Stack Project

Angular Spring boot API Bootstrap Table load

Spring boot Swagger Configuration

Create Maven project in Intellij

Django Vue.js Full Stack App

Django React Full Stack Project

Create Maven project in Intellij

Hibernate crud tutorial create delete update search

Hibernate Tutorial Introduction IntelliJ IDEA

Leave a Reply Cancel reply

Check Also

Create Maven project in Intellij

Recent Posts