Question

If I have declared my (composite) primary key using @IdClass, how do I write my @Query to be able to issue a DELETE query using a Collection<MyIdClass> ?

Secondary question

Will the CASCADE actually trigger the deletion of the associated AnotherEntity despite using @Query?

Current model

@Entity
@Table(name = "myentity")
@JsonIgnoreProperties(ignoreUnknown = true)
@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
@IdClass(MyIdClass.class)
public class MyEntity {

    @Id
    @Column(updatable = false)
    private String foo;

    @Id
    @Column(updatable = false)
    private String bar;

    @OneToOne(cascade = CascadeType.ALL, orphanRemoval = true)
    @JoinColumn(name = "my_foreign_key", referencedColumnName = "external_pk")
    private AnotherEntity anotherEntity;
}

@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class MyIdClass implements Serializable {

    private String foo;
    private String bar;
}

@Entity
@Table(name = "anotherentity")
@JsonIgnoreProperties(ignoreUnknown = true)
@Data
@SuperBuilder
@NoArgsConstructor
@AllArgsConstructor
public class AnotherEntity {

    @Id
    @Column(name = "external_pk", nullable = false, updatable = false)
    private String externalPk;
}

What I've read

A few resources:

And I also found this SO question which seemed very close to what I'm looking for, but unfortunately there are no answers.

Goal

Something similar to:

@Repository
public interface MyCRUDRepository extends CrudRepository<MyEntity, MyIdClass> {

    @Modifying
    @Query("DELETE FROM myentity m WHERE m IN ?1") // how do I write this?
    void deleteAllWithIds(Collection<MyIdClass> ids);
}

Ultimately, I want to do this to batch my DELETE requests to increase the performance.

Pitfalls I'm trying to avoid

I know there is a deleteAll(Iterable<? extends MyEntity>) but then I need to actually have those entities to begin with, which would require extra calls to the DB.

There is also deleteById(MyIdClass), but that actually always issues a findById before sending a single DELETE statement as a transaction: not good for the performance!

Potentially irrelevant precision

I'm not sure if that can help, but my JPA provider is EclipseLink. My understanding is that there are properties for batching requests, and that's ultimately what I'm aiming to use.

However, I'm not entirely sure what are the internal requirements for that batching to happen. For example, if I did a deleteById in a for-loop, would the alternating SELECT and DELETE statements prevent the batching from happening? The documentation is quite scarce about that.

CodePudding user response：

I think you are looking for something that will generate a query like this

delete from myentity where MyIdClass in (? , ? , ?)

You can try from this post, it may help you.

CodePudding user response：

If you're positive IdClass is a better choice than EmbeddedId in your situation, you could add an extra mapping to MyEntity :

@Embedded
@AttributeOverrides({
  @AttributeOverride(name = "foo",
    column = @Column(name = "foo", insertable = false, updatable = false)),
  @AttributeOverride(name = "bar",
    column = @Column(name = "bar", insertable = false, updatable = false))})
private MyIdClass id;

and use it in you repository:

@Modifying
@Query("DELETE FROM MyEntity me WHERE me.id in (:ids)")
void deleteByIdIn(@Param("ids") Collection<MyIdClass> ids);

This will generate a single query: delete from myentity where bar=? and foo=? [or bar=? and foo=?]..., resulting in this test to pass (with following table records insert into myentity(foo,bar) values ('foo1', 'bar1'),('foo2', 'bar2'),('foo3', 'bar3'),('foo4', 'bar4');):

@Test
@Transactional
void deleteByInWithQuery_multipleIds_allDeleted() {
  assertEquals(4, ((Collection<MyEntity>) myEntityRepository.findAll()).size());

  MyIdClass id1 = new MyIdClass("foo1", "bar1");
  MyIdClass id2 = new MyIdClass("foo2", "bar2");

  assertDoesNotThrow(() -> myEntityRepository.deleteByIdIn(List.of(id1, id2)));
  assertEquals(2, ((Collection<MyEntity>) myEntityRepository.findAll()).size());
}