Spring Data Jpa Modifying Queries

Spring Data Jpa Query annotation provides a way to manually add queries. by default Query annotation works perfectly fine with inserting or retrieving data from the underlying database. in order to manipulate the data, Spring Data Jpa provides a @Modifying annotation.

@Modifying annotation is only applicable for method annotated with @Query annotation , it is not applicable for costume implemented methods or query derived from method name because they already specify the modification behavior.

drop database if exists AppleDb;
create database AppleDb;
use AppleDb;
create TABLE apple ( apple_id BIGINT NOT NULL, apple_name VARCHAR(255) DEFAULT NULL, taste VARCHAR(255) DEFAULT NULL, PRIMARY KEY (apple_id)) ENGINE=INNODB;
create TABLE hibernate_sequence ( next_val BIGINT) ENGINE=INNODB;
insert into hibernate_sequence values ( 1 );
insert into apple (apple_name, apple_id, taste) values ("Macintosh", 1, "tangy");
insert into apple (apple_name, apple_id, taste) values ("Fuji", 2, "sweet");
insert into apple (apple_name, apple_id, taste) values ("Gala", 3, "juicy");
insert into apple (apple_name, apple_id, taste) values ("Jonagold", 4, "sweet");
insert into apple (apple_name, apple_id, taste) values ("GrannySmith", 5, "sharp");
package com.sujan.example.jpa.controller;

import com.sujan.example.jpa.entity.Apple;
import com.sujan.example.jpa.repository.AppleRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
public class AppleController {
    @Autowired
    private AppleRepository appleRepository;

    @GetMapping
    void modifying() {
        List<Apple> findByAppleName = appleRepository.findByAppleName("Macintosh");
        System.out.println("findByAppleName = " + findByAppleName);

        int findByAppleNameAndTasteUsingParams = appleRepository.findByAppleNameAndTasteUsingParams("Macintosh", "sharp");
        System.out.println("findByAppleNameAndTasteUsingParams = " + findByAppleNameAndTasteUsingParams);

        findByAppleName = appleRepository.findByAppleName("Macintosh");
        System.out.println("findByAppleName = " + findByAppleName);

        int updateTasteByAppleNameUsingPosition = appleRepository.updateTasteByAppleNameUsingPosition("Macintosh", "juicy");
        System.out.println("updateTasteByAppleNameUsingPosition = " + updateTasteByAppleNameUsingPosition);

        findByAppleName = appleRepository.findByAppleName("Macintosh");
        System.out.println("findByAppleName = " + findByAppleName);
    }
}
package com.sujan.example.jpa.entity;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

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

@Data
@Entity
@ToString
@NoArgsConstructor
@AllArgsConstructor()
public class Apple {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long appleId;
    private String appleName;
    private String taste;
}
package com.sujan.example.jpa.repository;

import com.sujan.example.jpa.entity.Apple;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

@Repository
public interface AppleRepository extends JpaRepository<Apple, Long> {

    List<Apple> findByAppleName(String appleName);

    @Modifying
    @Transactional
    @Query("update Apple a set a.taste = ?1 where a.appleName = ?2")
    int updateTasteByAppleNameUsingPosition(String taste, String appleName);

    @Modifying
    @Transactional
    @Query("update Apple a set a.taste = :taste where a.appleName = :appleName")
    int findByAppleNameAndTasteUsingParams(@Param("appleName") String appleName, @Param("taste") String taste);
}
package com.sujan.example.jpa;

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

@SpringBootApplication
public class JpaApplication {
    public static void main(String[] args) {
        SpringApplication.run(JpaApplication.class, args);
    }
}
spring.datasource.url=jdbc:mysql://localhost:3306/AppleDb?autoReconnect=true&useSSL=false&createDatabaseIfNotExist=true
spring.datasource.username=root
spring.datasource.password=root
spring.jpa.show-sql=true
spring.datasource.initialization-mode=always
plugins {
    id 'org.springframework.boot' version '2.3.3.RELEASE'
    id 'io.spring.dependency-management' version '1.0.10.RELEASE'
    id 'java'
}
group = 'com.sujan'
version = '0.0.1-SNAPSHOT'
sourceCompatibility = '11'
configurations {
    compileOnly {
        extendsFrom annotationProcessor
    }
}
repositories {
    mavenCentral()
}
dependencies {
    implementation 'org.springframework.boot:spring-boot-starter-web'
    annotationProcessor 'org.projectlombok:lombok'
    compileOnly 'org.projectlombok:lombok'
    implementation 'org.springframework.boot:spring-boot-starter-data-jpa'
    runtimeOnly 'mysql:mysql-connector-java'
    testImplementation('org.springframework.boot:spring-boot-starter-test') {
        exclude group: 'org.junit.vintage', module: 'junit-vintage-engine'
    }
}
test {
    useJUnitPlatform()
}
curl --location --request GET 'http://localhost:8080/'
select apple0_.apple_id as apple_id1_0_, apple0_.apple_name as apple_na2_0_, apple0_.taste as taste3_0_ from apple apple0_ where apple0_.apple_name=?
findByAppleName = [Apple(appleId=1, appleName=Macintosh, taste=tangy)]
update apple set taste=? where apple_name=?
findByAppleNameAndTasteUsingParams = 1
select apple0_.apple_id as apple_id1_0_, apple0_.apple_name as apple_na2_0_, apple0_.taste as taste3_0_ from apple apple0_ where apple0_.apple_name=?
findByAppleName = [Apple(appleId=1, appleName=Macintosh, taste=tangy)]
update apple set taste=? where apple_name=?
updateTasteByAppleNameUsingPosition = 0
select apple0_.apple_id as apple_id1_0_, apple0_.apple_name as apple_na2_0_, apple0_.taste as taste3_0_ from apple apple0_ where apple0_.apple_name=?
findByAppleName = [Apple(appleId=1, appleName=Macintosh, taste=tangy)]

 

Spring Data Jpa Modifying Derived Delete Queries

Spring Data Jpa supports derived delete queries which help to avoid writing declared JPQL queries.

drop database if exists AppleDb;
create database AppleDb;
use AppleDb;
create TABLE apple ( apple_id BIGINT NOT NULL, apple_name VARCHAR(255) DEFAULT NULL, taste VARCHAR(255) DEFAULT NULL, PRIMARY KEY (apple_id)) ENGINE=INNODB;
create TABLE hibernate_sequence ( next_val BIGINT) ENGINE=INNODB;
insert into hibernate_sequence values ( 1 );
insert into apple (apple_name, apple_id, taste) values ("Macintosh", 1, "tangy");
insert into apple (apple_name, apple_id, taste) values ("Fuji", 2, "tangy");
insert into apple (apple_name, apple_id, taste) values ("Gala", 3, "tangy");
insert into apple (apple_name, apple_id, taste) values ("Jonagold", 4, "tangy");
insert into apple (apple_name, apple_id, taste) values ("GrannySmith", 5, "tangy");
insert into apple (apple_name, apple_id, taste) values ("Macintosh", 6, "juicy");
insert into apple (apple_name, apple_id, taste) values ("Fuji", 7, "juicy");
insert into apple (apple_name, apple_id, taste) values ("Gala", 8, "juicy");
insert into apple (apple_name, apple_id, taste) values ("Jonagold", 9, "juicy");
insert into apple (apple_name, apple_id, taste) values ("GrannySmith", 10, "juicy");
insert into apple (apple_name, apple_id, taste) values ("Macintosh", 11, "sweet");
insert into apple (apple_name, apple_id, taste) values ("Fuji", 12, "sweet");
insert into apple (apple_name, apple_id, taste) values ("Gala", 13, "sweet");
insert into apple (apple_name, apple_id, taste) values ("Jonagold", 14, "sweet");
insert into apple (apple_name, apple_id, taste) values ("GrannySmith", 15, "sweet");
package com.sujan.example.jpa.controller;

import com.sujan.example.jpa.repository.AppleRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

@RestController
public class AppleController {
    @Autowired
    private AppleRepository appleRepository;

    @GetMapping
    void modifying() {
        int deleteByAppleId = appleRepository.deleteByTaste("tangy");
        System.out.println("deleteByAppleId = " + deleteByAppleId);
        int deleteInBulkByRoleIdPosition = appleRepository.deleteInBulkByRoleIdPosition("juicy");
        System.out.println("deleteInBulkByRoleIdPosition = " + deleteInBulkByRoleIdPosition);
        int deleteInBulkByRoleIdParam = appleRepository.deleteInBulkByRoleIdParam("sweet");
        System.out.println("deleteInBulkByRoleIdParam = " + deleteInBulkByRoleIdParam);
    }
}
package com.sujan.example.jpa.entity;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

import javax.persistence.*;

@Data
@Entity
@ToString
@NoArgsConstructor
@AllArgsConstructor()
public class Apple {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long appleId;
    private String appleName;
    private String taste;

    @PrePersist
    public void PrePersist() {
        System.out.println("Apple.PrePersist");
    }

    @PostPersist
    public void PostPersist() {
        System.out.println("Apple.PostPersist");
    }

    @PreRemove
    public void PreRemove() {
        System.out.println("Apple.PreRemove");
    }

    @PostRemove
    public void PostRemove() {
        System.out.println("Apple.PostRemove");
    }

    @PreUpdate
    public void PreUpdate() {
        System.out.println("Apple.PreUpdate");
    }

    @PostUpdate
    public void PostUpdate() {
        System.out.println("Apple.PostUpdate");
    }

    @PostLoad
    public void PostLoad() {
        System.out.println("Apple.PostLoad");
    }
}
package com.sujan.example.jpa.repository;

import com.sujan.example.jpa.entity.Apple;
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 javax.transaction.Transactional;

@Repository
public interface AppleRepository extends JpaRepository<Apple, Long> {
    @Transactional
    int deleteByTaste(String taste);

    @Modifying
    @Transactional
    @Query("delete from Apple a where a.taste = ?1")
    int deleteInBulkByRoleIdPosition(String taste);

    @Modifying
    @Transactional
    @Query("delete from Apple a where a.taste = :taste")
    int deleteInBulkByRoleIdParam(String taste);
}
package com.sujan.example.jpa;

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

@SpringBootApplication
public class JpaApplication {
    public static void main(String[] args) {
        SpringApplication.run(JpaApplication.class, args);
    }
}
spring.datasource.url=jdbc:mysql://localhost:3306/AppleDb?autoReconnect=true&useSSL=false&createDatabaseIfNotExist=true
spring.datasource.username=root
spring.datasource.password=root
spring.jpa.show-sql=true
spring.datasource.initialization-mode=always
plugins {
    id 'org.springframework.boot' version '2.3.3.RELEASE'
    id 'io.spring.dependency-management' version '1.0.10.RELEASE'
    id 'java'
}
group = 'com.sujan'
version = '0.0.1-SNAPSHOT'
sourceCompatibility = '11'
configurations {
    compileOnly {
        extendsFrom annotationProcessor
    }
}
repositories {
    mavenCentral()
}
dependencies {
    implementation 'org.springframework.boot:spring-boot-starter-web'
    annotationProcessor 'org.projectlombok:lombok'
    compileOnly 'org.projectlombok:lombok'
    implementation 'org.springframework.boot:spring-boot-starter-data-jpa'
    runtimeOnly 'mysql:mysql-connector-java'
    testImplementation('org.springframework.boot:spring-boot-starter-test') {
        exclude group: 'org.junit.vintage', module: 'junit-vintage-engine'
    }
}
test {
    useJUnitPlatform()
}
curl --location --request GET 'http://localhost:8080/'
select apple0_.apple_id as apple_id1_0_, apple0_.apple_name as apple_na2_0_, apple0_.taste as taste3_0_ from apple apple0_ where apple0_.taste=?
Apple.PostLoad
Apple.PostLoad
Apple.PostLoad
Apple.PostLoad
Apple.PostLoad
Apple.PreRemove
Apple.PreRemove
Apple.PreRemove
Apple.PreRemove
Apple.PreRemove
delete from apple where apple_id=?
Apple.PostRemove
delete from apple where apple_id=?
Apple.PostRemove
delete from apple where apple_id=?
Apple.PostRemove
delete from apple where apple_id=?
Apple.PostRemove
delete from apple where apple_id=?
Apple.PostRemove
deleteByAppleId = 5
delete from apple where taste=?
deleteInBulkByRoleIdPosition = 5
delete from apple where taste=?
deleteInBulkByRoleIdParam = 5

Although the deleteByTaste method seems almost the same as the deleteInBulkByRoleIdPosition method and deleteInBulkByRoleIdParam method but there is a very important difference between the two methods in the way they execute.

by default, the Spring Data Jpa entity has 8 life cycle methods.


@PrePersist - called before persisting/saving an entity
@PostPersist - called after persisting/saving an entity
@PreRemove - called before removing/deleting an entity
@PostRemove - called after removing/deleting an entity
@PreUpdate - called before updating an entity
@PostUpdate - called after updating an entity
@PostLoad - called before loading an entity

these life cycle methods run on various stages of the entity lifecycle.

deleteByTaste first issues a select query, iterates on each record, runs all the entity life cycle methods and deletes them one by one.

deleteInBulkByRoleIdPosition and deleteInBulkByRoleIdParam method issues a single JPQL query against the underlying database, which is defined in @Query annotation and does not run any entity life cycle method.

follow us on

YouTube IconLinkedIn Icon