Custom Non-Select Queries with Spring Data JPA’s @Query , @Modifying , @Transactional Annotations

 Custom Non-Select Queries with Spring Data JPA’s @Query , @Modifying , @Transactional Annotations

For Explanation watch video:

Directory Structure

application.properties

#datasource properties

spring.datasource.url=jdbc:mysql://localhost:3306/new

spring.datasource.username=root

spring.datasource.password=root

#jpa 

spring.jpa.show-sql=true

spring.jpa.hibernate.ddl-auto=update

SpringBootDataJpaNonSelectApplication

package com.app;

import org.springframework.boot.SpringApplication;

import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication

public class SpringBootDataJpaNonSelectApplication {

public static void main(String[] args) {

SpringApplication.run(SpringBootDataJpaNonSelectApplication.class, args);

}

}

Employee.java

package com.app.model;

import javax.persistence.Column;

import javax.persistence.Entity;

import javax.persistence.GeneratedValue;

import javax.persistence.GenerationType;

import javax.persistence.Id;

import javax.persistence.Table;

import lombok.AllArgsConstructor;

import lombok.Data;

import lombok.NoArgsConstructor;

@Data

@NoArgsConstructor

@AllArgsConstructor

@Entity

@Table(name = "emptab")

public class Employee {

@Id

@GeneratedValue(strategy = GenerationType.IDENTITY)

@Column(name = "eid")

private Integer empId;

@Column(name = "ename")

private String empName;

@Column(name = "dept")

private String deptName;

@Column(name = "esal")

private Double empSal;

public Employee(String empName, String deptName, Double empSal) {

super();

this.empName = empName;

this.deptName = deptName;

this.empSal = empSal;

}

}

IEmployeeRepository.java

package com.app.repo;

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

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

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

import org.springframework.transaction.annotation.Transactional;

import com.app.model.Employee;

@Transactional

public interface IEmployeeRepository extends JpaRepository<Employee, Integer>{

//update sal using dept

@Query("update Employee set empSal=:sal where deptName=:dept")

@Modifying

int modifySalByDept(Double sal,String dept);

//delete query

@Query("delete from Employee where empId>=:start and empId<=:end")

@Modifying

int deleteEmpByIdRange(int start,int end);

}

TestRunner.java

package com.app.runner;

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

import org.springframework.boot.CommandLineRunner;

import org.springframework.stereotype.Component;

import com.app.repo.IEmployeeRepository;

@Component

public class TestRunner implements CommandLineRunner{

@Autowired

private IEmployeeRepository repo;

@Override

public void run(String... args) throws Exception {

int count = repo.deleteEmpByIdRange(3, 5);

System.out.println("Records deleted : "+count);

repo.findAll().forEach(System.out::println);

}

}