Spring Boot Project using Mysql Database

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 Employee table in the database named gcompany.  student table consist of following columns id,ename,mobile,salary

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 gcompany

com.example.gcompany
|
-------------------->EmpApplication.java
com.example.gcompany.service
|
--------------------->EmployeeService
com.example.gcompany.controller
|
----------------------> EmployeeController.java
com.example.gcompany.repository
|
-----------------------> EmployeeRepository.java
com.example.gcompany.domain
|
------------------------>Employee.java

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

package com.example.gcompany;

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

@SpringBootApplication
public class GcompanyApplication {

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

}

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

EmployeeService.java

package com.example.gcompany.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.gcompany.domain.Employee;
import com.example.gcompany.service.EmployeeService;

@Controller
public class EmployeeController {
	
	 @Autowired
	    private EmployeeService service;

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

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

	    @RequestMapping(value = "/save", method = RequestMethod.POST)
	    public String saveEmployee(@ModelAttribute("employee") Employee emp) {
	        service.save(emp);
	        return "redirect:/";
	    }

	    @RequestMapping("/edit/{id}")
	    public ModelAndView showEditEmployeePage(@PathVariable(name = "id") int id) {
	        ModelAndView mav = new ModelAndView("new");
	        Employee emp = service.get(id);
	        mav.addObject("employee", emp);
	        return mav;
	        
	    }
	    @RequestMapping("/delete/{id}")
	    public String deleteEmployeePage(@PathVariable(name = "id") int id) {
	        service.delete(id);
	        return "redirect:/";
	    }
}

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

EmployeeController.java

package com.example.gcompany.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.gcompany.domain.Employee;
import com.example.gcompany.service.EmployeeService;

@Controller
public class EmployeeController {
	
	 @Autowired
	    private EmployeeService service;

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

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

	    @RequestMapping(value = "/save", method = RequestMethod.POST)
	    public String saveEmployee(@ModelAttribute("employee") Employee emp) {
	        service.save(emp);
	        return "redirect:/";
	    }

	    @RequestMapping("/edit/{id}")
	    public ModelAndView showEditEmployeePage(@PathVariable(name = "id") int id) {
	        ModelAndView mav = new ModelAndView("new");
	        Employee emp = service.get(id);
	        mav.addObject("employee", emp);
	        return mav;
	        
	    }
	    @RequestMapping("/delete/{id}")
	    public String deleteEmployeePage(@PathVariable(name = "id") int id) {
	        service.delete(id);
	        return "redirect:/";
	    }

}

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

EmployeeRepository.java

package com.example.gcompany.repository;

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

import com.example.gcompany.domain.Employee;

@Repository
public interface EmployeeRepository extends JpaRepository<Employee, Long> {

}

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

Employee.java

package com.example.gcompany.domain;

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

@Entity
public class Employee {
	
	@Id
    @GeneratedValue(strategy= GenerationType.IDENTITY)
    private Long id;
    private String ename;
    private int mobile;
    private int salary;

    public Employee() {
		
	}

	public Employee(Long id, String ename, int mobile, int salary) {
		
		this.id = id;
		this.ename = ename;
		this.mobile = mobile;
		this.salary = salary;
	}

	public Long getId() {
		return id;
	}

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

	public String getEname() {
		return ename;
	}

	public void setEname(String ename) {
		this.ename = ename;
	}

	public int getMobile() {
		return mobile;
	}

	public void setMobile(int mobile) {
		this.mobile = mobile;
	}

	public int getSalary() {
		return salary;
	}

	public void setSalary(int salary) {
		this.salary = salary;
	}

	@Override
	public String toString() {
		return "Employee [id=" + id + ", ename=" + ename + ", mobile=" + mobile + ", salary=" + salary + "]";
	}
}

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 -Employee 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>Employee ID</th>
            <th>Employee Name</th>
            <th>Mobile</th>
            <th>Salary</th>
            <th>edit</th>
             <th>delete</th>
   	</tr>
  </thead>
  <tbody>
      <tr  th:each="employee : ${listemployee}">
		<td th:text="${employee.id}">Employee ID</td>
		<td th:text="${employee.ename}">Employee Name</td>
		<td th:text="${employee.mobile}">Mobile</td>
		<td th:text="${employee.salary}">Salary</td>				
		<td>
			<a th:href="@{'/edit/' + ${employee.id}}">Edit</a>
		</td>							    
		<td>
			<a th:href="@{'/delete/' + ${employee.id}}">Delete</a>
		</td>		    
		</tr> 
   
  </tbody>
</table>

                    
                 </div>

            </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 Employee</h1>
    <br />
     <div class="col-sm-4">
    <form action="#" th:action="@{/save}" th:object="${employee}" method="post">

      
 		<div alight="left">
            <tr>
                <label class="form-label" >Employee Name</label>
                <td>
	                <input type="hidden" th:field="*{id}" />
	                <input type="text" th:field="*{ename}" class="form-control" placeholder="Emp Name" />
                </td>
            </tr>
         </div>   
         
         
          <div alight="left">
            <tr>
                <label class="form-label" >mobile</label>
                
                <td>
                 <input type="text" th:field="*{mobile}" class="form-control" placeholder="mobile" />
                </td>
            </tr>
         </div>  
         <div alight="left">
                 <tr>
                 <label class="form-label" >salary</label>
                <td><input type="text" th:field="*{salary}" class="form-control" placeholder="salary" /></td>
            </tr>
            </div> 
            
			<br>
            <tr>
            <td colspan="2"><button type="submit" class="btn btn-info">Save</button> </td>
            </tr>

    </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/gcompany?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC
server.error.whitelabel.enabled=false
server.port=7070
spring.datasource.username=root
spring.datasource.password=

spring.jpa.open-in-view=false
spring.thymeleaf.cache=false
api.base.path = http://localhost:7070

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

Spring Boot Crud using Mysql Database

Share

Watch on