CODEWITHSUNDEEP
springspring-dataspring-data-jpa
curious1
curious1

Reputation: 14717

Spring Data: "delete by" is supported?

I am using Spring JPA for database access. I am able to find examples such as findByName and countByName, for which I dont have to write any method implementation. I am hoping to find examples for delete a group of records based on some condition.

Does Spring JPA support deleteByName-like delete? Any pointer is appreciated.

Regards and thanks.

Upvotes: 139

Views: 276524

Answers (9)

Satish Patro
Satish Patro

Reputation: 4394

There are 2 options:

  1. Custom Query:

    @Modifying
@Query("delete from User where firstName = :firstName")
void deleteUsersByFirstName(@Param("firstName") String firstName);

  2. JPA Query by method

    List<User> deleteByLastname(String lastname);

When you go with query by method (second option) it will first do a get call

select * from user where last_name = :firstName

Then it will load it in a list, then it will call delete id one by one

delete from user where id = 18
delete from user where id = 19

But, the first option (custom query), it's just a single query, and it will delete wherever the value exists.

Since in second option makes multiple DB queries, try to use the first one.

Go through this link too https://www.baeldung.com/spring-data-jpa-deleteby

Upvotes: 26

Jess
Jess

Reputation: 3715

It works just

import org.springframework.transaction.annotation.Transactional;

    @Transactional
    Long removeAddressByCity(String city);

Upvotes: 1

mohamed lamkhayar
mohamed lamkhayar

Reputation: 1

@Query(value = "delete from addresses u where u.ADDRESS_ID LIKE %:addressId%", nativeQuery = true)
void deleteAddressByAddressId(@Param("addressId") String addressId);

Upvotes: 0

Digao
Digao

Reputation: 560

here follows my 2 cents. You can also use native queries, like:

@Modifying
@Query(value="delete from rreo r where r.cod_ibge = ?1 and r.exercicio= ?2", nativeQuery = true)
void deleteByParameters(Integer codIbge, Integer exercicio);

Upvotes: 0

amoljdv06
amoljdv06

Reputation: 2864

Yes , deleteBy method is supported To use it you need to annotate method with @Transactional

Upvotes: 2

Andrey Atapin
Andrey Atapin

Reputation: 7945

Deprecated answer (Spring Data JPA <=1.6.x):

@Modifying annotation to the rescue. You will need to provide your custom SQL behaviour though.

public interface UserRepository extends JpaRepository<User, Long> {
    @Modifying
    @Query("delete from User u where u.firstName = ?1")
    void deleteUsersByFirstName(String firstName);
}

Update:

In modern versions of Spring Data JPA (>=1.7.x) query derivation for delete, remove and count operations is accessible.

public interface UserRepository extends CrudRepository<User, Long> {

    Long countByFirstName(String firstName);

    Long deleteByFirstName(String firstName);

    List<User> removeByFirstName(String firstName);

}

Upvotes: 238

Suneet Khurana
Suneet Khurana

Reputation: 449

If you will use pre defined delete methods as directly provided by spring JPA then below two queries will be execute by the framework.

  • First collect data(like id and other column) using by execute select query with delete query where clause.

  • then after getting resultSet of first query, second delete queries will be execute for all id(one by one)

    Note : This is not optimized way for your application because many queries will be execute for single MYSQL delete query.

This is another optimized way for delete query code because only one delete query will execute by using below customized methods.



@NamedNativeQueries({

@NamedNativeQuery(name = "Abc.deleteByCreatedTimeBetween",
            query = "DELETE FROM abc WHERE create_time BETWEEN ?1 AND ?2")
    ,

    @NamedNativeQuery(name = "Abc.getByMaxId",
            query = "SELECT max(id) from abc")
})

@Entity
public class Abc implements Serializable {

}

@Repository
public interface AbcRepository extends CrudRepository {

    int getByMaxId();

    @Transactional
    @Modifying
    void deleteByCreatedTimeBetween(String startDate, String endDate);
}

Upvotes: 3

Christoph Strobl
Christoph Strobl

Reputation: 6726

Derivation of delete queries using given method name is supported starting with version 1.6.0.RC1 of Spring Data JPA. The keywords remove and delete are supported. As return value one can choose between the number or a list of removed entities.

Long removeByLastname(String lastname);

List<User> deleteByLastname(String lastname);

Upvotes: 94

geoand
geoand

Reputation: 64011

If you take a look at the source code of Spring Data JPA, and particularly the PartTreeJpaQuery class, you will see that is tries to instantiate PartTree. Inside that class the following regular expression

private static final Pattern PREFIX_TEMPLATE = Pattern.compile("^(find|read|get|count|query)(\\p{Lu}.*?)??By")

should indicate what is allowed and what's not.

Of course if you try to add such a method you will actually see that is does not work and you get the full stacktrace.

I should note that I was using looking at version 1.5.0.RELEASE of Spring Data JPA

Upvotes: 8

Related Questions