Spring Boot CRUD Example With MySQL/PostgreSQL

By
Rakesh
-

In this post, we will see Spring Boot CRUD Example with MySQL database. We are going to use Spring Boot, Spring Data JPA, and Hibernate for this tutorial. Make sure we have installed the below tools in our machine.

  1. JDK 1.8.
  2. Maven.
  3. Eclipse/IntelliJ idea or STS.
  4. MySQL Database.
  5. Postman.

Introduction – What we will learn in this tutorial.

Let’s see some brief points about this tutorial. We are going to use Spring Boot, Spring Data JPA, and Hibernate for this tutorial. This example has been tested with both using application.properties and application.yml. We are not going to create the table manually. Let’s hibernate do this job. we will have below REST API to test Spring Boot CRUD Example with MySQL Database.

Request method APIs/EndPoints
POSThttp://localhost:9091/student/create
PUThttp://localhost:9091/student/update
GEThttp://localhost:9091/student/{id}
DELETEhttp://localhost:9091/student/delete

We will also have REST API to perform Bulk CRUD operation using MySQL at the end of the tutorial.

Request Method APIs/End Points
POSThttp://localhost:9091/student/bulkcreate
PUThttp://localhost:9091/student/bulkupdate
GEThttp://localhost:9091/student/allstudent
DELETEhttp://localhost:9091/student/bulkdelete

Note – We will use Postman to test the example. We can also use the curl command to test the APIs.

Spring Boot CRUD Example with MySQL – Database Configuration using application.properties and application.yml.

maven dependency for mysql.

<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>

application.properties.

spring.datasource.url=jdbc:mysql://localhost:3306/springbootcrudexample
spring.datasource.username=root
spring.datasource.password=root
spring.jpa.hibernate.ddl-auto=create
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
server.port = 9091

application.yml

spring:
  jpa:
    show-sql: true
    hibernate:
      ddl-auto: create
  datasource:
    url: jdbc:mysql://localhost:3306/springbootcrudexample
    username: root
    password: root
server:
   port: 9091

Spring Boot CRUD Example with MySQL from scratch.

Open eclipse and create maven project, Don’t forget to check ‘Create a simple project (skip)’ click on next.  Fill all details(GroupId – springbootmysqlcrudexample, ArtifactId – springbootmysqlcrudexample, and name – springbootmysqlcrudexample) and click on finish. Keep packaging as the jar.

Add maven dependency.

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>springbootmysqlcrudexample</groupId>
	<artifactId>springbootmysqlcrudexample</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>springbootmysqlcrudexample</name>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.0.2.RELEASE</version>
	</parent>
	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
		</dependency>
	</dependencies>
</project>

The directory structure of the application.

Spring Boot CRUD Example With MySQL

Define Entity – Student.java

package com.springbootcrudexample.entity;

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

@Entity
public class Student {
	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	private Long id;

	@Column(name = "name")
	private String name;

	@Column(name = "roll_number")
	private String rollNumber;

	public Long getId() {
		return id;
	}

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

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getRollNumber() {
		return rollNumber;
	}

	public void setRollNumber(String rollNumber) {
		this.rollNumber = rollNumber;
	}

}

We are not using @Table annotation with Student.java. Hibernate will create a table name with the entity name. since in application.properties file we have mentioned, every time when we will restart the server hibernate will create the table automatically.

@Entity – Used with the entity class.

@Id – Specifies the primary key of an entity.

@GeneratedValue – Define the primary key generation strategy.

@Column – Used to map entity field with database column. The column name must the same as the database column name.

Define Service Interface – StudentService.java

package com.springbootcrudexample.service;

import org.springframework.stereotype.Component;
import com.springbootcrudexample.entity.Student;

@Component
public interface StudentService {
	public Student save(Student student);
	public Student update(Student student);
	public Student get(Long id);
	public void delete(Student student);
}

Define Repository – StudentRepository.java

We will create our repository interface extending JpaRepository. Spring Data JPA provides predefined repositories, using that we can create our Custom repository.

package com.springbootcrudexample.repository;

import java.io.Serializable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;
import com.springbootcrudexample.entity.Student;

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

}

We will use Spring Data JPA save() method to create and update an entity(see more details here). To retrieve and delete operation we will use findById() and delete() method.

Note – The methods save(), findById() and delete() method has been defined in CrudRepository interface. Since StudentRepository extends JpaRepository and JpaRepository extends PagingAndSortingRepository. Futher  PagingAndSortingRepository extends CrudRepository interface. All these methods(save(),findById() and delete()) will be available for StudentRepository.

StudentServiceImpl.java

package com.springbootcrudexample.serviceimpl;

import java.util.Optional;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.springbootcrudexample.entity.Student;
import com.springbootcrudexample.repository.StudentRepository;
import com.springbootcrudexample.service.StudentService;

@Service
public class StudentServiceImpl implements StudentService {

	@Autowired
	private StudentRepository studentRepository;

	@Transactional
	public Student save(Student student) {
		Student createResponse = studentRepository.save(student);
		return createResponse;
	}

	@Transactional
	public Student update(Student student) {
		Student updateResponse = studentRepository.save(student);
		return updateResponse;
	}

	@Transactional
	public Student get(Long id) {
		Optional<Student> studentResponse = studentRepository.findById(id);
		Student getResponse = studentResponse.get();
		return getResponse;
	}

	@Transactional
	public void delete(Student student) {
		studentRepository.delete(student);
	}
}

@Service annotation used with Service class where our business logic exists. See more about @Service, @Component, @Repository here.

@Transactional annotation used for transaction management. See a depth tutorial that explains how @Transactional annotation works.

Define controller class – Rest APIs

package com.springbootcrudexample.controller;

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.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import com.springbootcrudexample.entity.Student;
import com.springbootcrudexample.service.StudentService;

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

	@Autowired
	private StudentService studentService;

	@PostMapping("/create")
	public Student createStudent(@RequestBody Student student) {
		Student createResponse = studentService.save(student);
		return createResponse;
	}

	@PutMapping("/update")
	public Student updateStudent(@RequestBody Student student) {
		Student updateResponse = studentService.update(student);
		return updateResponse;
	}

	@GetMapping("/{id}")
	public Student getStudent(@PathVariable Long id) {
		Student getReponse = studentService.get(id);
		return getReponse;
	}

	@DeleteMapping("/delete")
	public String deleteStudent(@RequestBody Student student) {
		studentService.delete(student);
		return "Record deleted succesfully";
	}
}

@RestController annotation is used with class and combined form of @Controller and @ResponseBody.

@PostMapping is short form of @RequestMapping(method = RequestMethod.POST).

@PutMapping is short form of @RequestMapping(method = RequestMethod.PUT).

@GetMapping is short form of @RequestMapping(method = RequestMethod.GET).

@DeleteMapping is short form of @RequestMapping(method = RequestMethod.DELETE).

Define JpaConfig.java class.

package com.springbootcrudexample.config;

import org.springframework.cache.annotation.EnableCaching;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;

@Configuration
@EnableJpaRepositories(basePackages = "com.springbootcrudexample.repository")
public class JpaConfig {

}

In JpaConfig.java we can define configuration related stuff.

@Configuration – We use this annotation with the class for configuration purposes. For example, we can create a bean using @Bean annotation. The classes which are annotated with @Configuration annotation are automatically loaded by Spring while deployment. See more about @Configuration annotation here.

@EnableJpaRepositories – Used to enable JPA repositories. It has different attributes basePackages, basePackageClasses, includeFilters, excludeFilters etc.

Define SpringMain class

package com.springbootcrudexample.main;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.domain.EntityScan;
import org.springframework.context.annotation.ComponentScan;

@SpringBootApplication
@ComponentScan(basePackages = "com.springbootcrudexample.*")
@EntityScan("com.springbootcrudexample.*")
public class SpringMain {
	public static void main(String[] args) {
		SpringApplication.run(SpringMain.class, args);
	}
}

Some annotations related to Spring Boot CRUD Example With MySQL Database.

@SpringBootApplication – Used with class. Combination of @SpringBootConfiguration, @EnableAutoConfiguration, and @ComponentScan.

@ComponentScan – Used with class. It scans packages that we provide as basePackages value. For example in our case  i.e @ComponentScan(basePackages = “com.springbootcrudexample.*”) it will scan all com.springbootexample and its sub-packages.

@EntityScan(“com.springbootcrudexample.*”) – It will scan all entities available in com.springbootcrudexample package or its subpackages.

Spring Boot MySql CRUD Example testing using postman.

Let’s run the SpringMain.java class

Spring Boot CRUD Example With MySQL

Create a Student using:- POST – http://localhost:9091/student/create

Request and Response data to create Student.

{
"name":"rakesh",
"rollNumber":"0126CS071"
}

----
{
    "id": 1,
    "name": "rakesh",
    "rollNumber": "0126CS071"
}

Let’s verify the database. We should have one record in DB.

Update Student using:- PUT– http://localhost:9091/student/update

Request and Response data for update Operation. Let’s update the name.

{
    "id": 1,
    "name": "rahul",
    "rollNumber": "0126CS071"
}

-----

{
    "id": 1,
    "name": "rahul",
    "rollNumber": "0126CS071"
}
Spring Boot CRUD Example With MySQL

In database we should have updated name.

Get Student using:- GET– http://localhost:9091/student/{id}

Response data.

{
    "id": 1,
    "name": "rahul",
    "rollNumber": "0126CS071"
}

DeleteStudent using:- DELETE– http://localhost:9091/student/delete

Request Data for delete operation

{
    "id": 1,
    "name": "rahul",
    "rollNumber": "0126CS071"
}
Spring Boot CRUD Example With MySQL

Verify the database.

The query generated for the Spring Boot CRUD operation is as below.

The query generated for the create Operation.

Hibernate: 
    insert 
    into
        student
        (name, roll_number, id) 
    values
        (?, ?, ?)

The query generated for update Operation.

Hibernate: 
    select
        student0_.id as id1_0_0_,
        student0_.name as name2_0_0_,
        student0_.roll_number as roll_num3_0_0_ 
    from
        student student0_ 
    where
        student0_.id=?
Hibernate: 
    update
        student 
    set
        name=?,
        roll_number=? 
    where
        id=?

The query generated for retrieve Operation.

Hibernate: 
    select
        student0_.id as id1_0_0_,
        student0_.name as name2_0_0_,
        student0_.roll_number as roll_num3_0_0_ 
    from
        student student0_ 
    where
        student0_.id=?

The query generated for delete Operation.

Hibernate: 
    select
        student0_.id as id1_0_0_,
        student0_.name as name2_0_0_,
        student0_.roll_number as roll_num3_0_0_ 
    from
        student student0_ 
    where
        student0_.id=?
Hibernate: 
    delete 
    from
        student 
    where
        id=?

Spring Boot CRUD Example With MySQL – Performing Bulk CRUD Operation.

Spring Data JPA provides saveAll(), findAll() and deleteAll() methods to perform bulk operation. We need to modify our StudentController.java class, StudentService interface, and StudentServiceImpl.java class.

Student.java, JpaConfig.java, StudentRepository interface, and SpringMain.java class will remain the same.

StudentController.java

package com.springbootcrudexample.controller;

import java.util.List;

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.PostMapping;
import org.springframework.web.bind.annotation.PutMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import com.springbootcrudexample.entity.Student;
import com.springbootcrudexample.service.StudentService;

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

	@Autowired
	private StudentService studentService;

	@PostMapping("/bulkcreate")
	public List<Student> createStudents(@RequestBody List<Student> students) {
		List<Student> createResponse = studentService.saveAll(students);
		return createResponse;
	}

	@PutMapping("/bulkupdate")
	public List<Student> updateStudents(@RequestBody List<Student> students) {
		List<Student> updateResponse = studentService.updateAll(students);
		return updateResponse;
	}

	@GetMapping("/allstudent")
	public List<Student> getStudents() {
		List<Student> getresponse = studentService.getAll();
		return getresponse;
	}

	@DeleteMapping("/bulkdelete")
	public String deleteStudents(@RequestBody List<Student> students) {
		studentService.deleteAll(students);
		return "Records deleted succesfully";
	}
}

StudentService.java

package com.springbootcrudexample.service;

import java.util.List;
import org.springframework.stereotype.Component;
import com.springbootcrudexample.entity.Student;;

@Component
public interface StudentService {

	public List<Student> saveAll(List<Student> student);
	public List<Student> updateAll(List<Student> student);
	public List<Student> getAll();
	public void deleteAll(List<Student> student);
}

StudentServiceImpl.java

package com.springbootcrudexample.serviceimpl;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.springbootcrudexample.entity.Student;
import com.springbootcrudexample.repository.StudentRepository;
import com.springbootcrudexample.service.StudentService;

@Service
public class StudentServiceImpl implements StudentService {

	@Autowired
	private StudentRepository studentRepository;

	@Override
	@Transactional
	public List<Student> saveAll(List<Student> students) {
		List<Student> saveResponse = studentRepository.saveAll(students);
		return saveResponse;
	}

	@Override
	@Transactional
	public List<Student> updateAll(List<Student> students) {
		List<Student> updateResponse = studentRepository.saveAll(students);
		return updateResponse;
	}

	@Override
	@Transactional
	public List<Student> getAll() {
		List<Student> saveResponse = studentRepository.findAll();
		return saveResponse;
	}

	@Override
	@Transactional
	public void deleteAll(List<Student> students) {
		studentRepository.deleteAll(students);
	}
}

Testing of Spring Boot MySQL Bulk CRUD example using postman.

Create multiple Students using:- POST – http://localhost:9091/student/bulkcreate

Request Data ofr bulk create.

[
    {
        "name": "rakesh",
        "rollNumber": "0126CS071"
    },
    {
        "name": "Rohit",
        "rollNumber": "0126CS072"
    },
    {
        "name": "Nayak",
        "rollNumber": "0126CS073"
    }
]
Spring Boot CRUD Example With MySQL

Update multiple Students using:- PUT – http://localhost:9091/student/bulkupdate

Request Data for the bulk update operation.

[
    {
        "id": 1,
        "name": "rakesh",
        "rollNumber": "0126CS075"
    },
    {
        "id": 2,
        "name": "Rohit",
        "rollNumber": "0126CS076"
    },
    {
        "id": 3,
        "name": "Nayak",
        "rollNumber": "0126CS077"
    }
]
Spring Boot Example With MySQL

Get all students using: – GET – http://localhost:9091/student/allstudent

Spring Boot CRUD Example With MySQL

Delete all students – DELETE – http://localhost:9091/student/bulkdelete

Request Data for the bulk detele operation.

Request Data for the delete operation.

[
    {
        "id": 1,
        "name": "rakesh",
        "rollNumber": "0126CS075"
    },
    {
        "id": 2,
        "name": "Rohit",
        "rollNumber": "0126CS076"
    },
    {
        "id": 3,
        "name": "Nayak",
        "rollNumber": "0126CS077"
    }
]

Spring Boot Configuration with PostgreSQL and Oracle Database.

Spring Boot PostgreSQL database Configuration.

maven changes.

    <dependency>
        <groupId>org.postgresql</groupId>
        <artifactId>postgresql</artifactId>
        <scope>runtime</scope>
    </dependency>

application.properties file for Spring Boot PostgreSQL configuration.

spring.datasource.url=jdbc:postgresql://localhost:5432/postgres
spring.datasource.username=postgres
spring.datasource.password=postgres
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.PostgreSQLDialect
spring.jpa.hibernate.ddl-auto=create
spring.jpa.show-sql=true
server.port = 9091
spring.jpa.properties.hibernate.jdbc.lob.non_contextual_creation=true

Rest of code would be same.

Spring Boot oracle database configuration.

maven changes

    <dependency>
        <groupId>com.oracle</groupId>
        <artifactId>ojdbc6</artifactId>
        <version>11.2.0.3</version>
    </dependency>

application.properties file for Spring Boot Oracle configuration.

# Connection url for the database
spring.datasource.url=jdbc:oracle:thin:@localhost:1521:XE
spring.datasource.username=SYSTEM
spring.datasource.password=oracle
spring.datasource.driver-class-name=oracle.jdbc.driver.OracleDriver
# Show or not log for each sql query
spring.jpa.show-sql = true
spring.jpa.properties.hibernate.format_sql=true 
 
spring.jpa.hibernate.ddl-auto =create
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.Oracle10gDialect
server.port = 9091
#show sql values
#logging.level.org.hibernate.type.descriptor.sql=trace

hibernate.show_sql = true
#spring.jpa.hibernate.logging.level.sql =FINE
#show sql statement
#logging.level.org.hibernate.SQL=debug

If you see any error for oracle dependency then follow these steps.

That’s all about Spring Boot CRUD Example With MySQL/PostgreSQL database.

Download spring boot curd example from github.

Other Spring Boot CRUD Example.

Spring Data JPA tutorials.

Hibernate Tutorial with Spring Boot and MySql/Oracle.

Spring Boot Docs.

Summary – We have seen Spring Boot CRUD Example With MySQL/PostgreSQL database. We also see how to perform bulk crud operations using Spring Data JPA methods.

RELATED ARTICLESMORE FROM AUTHOR