SpringBoot Data - How to delete all rows that share an id?

user_3412229 0
Viewed 2532

Lets say I have a table called "USER". In this table, I have all the user data, and I assign the user a unique id and therefore each row is unique.

It looks something like this:

@Entity
@Table(name = "USER")
public class User {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "id_seq")
    @Column(name = "USER_ID")
    private Long userId;

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

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

    ... a lot more stuff ...

}

I also have another table where I assign all the roles the user has, which each row looks like:

@Entity
@Table(name = "USER_ROLES")
public class UserRoleMap {

    @Id
    @Column(name = "USER_ID")
    Long userId;

    @Column(name = "USER_ROLE_ID")
    Long userRoleId;

    @Column(name = "UPDATED_DATE")
    String updatedDate;

    @Column(name = "UPDATED_BY")
    String updatedBy;

}

This table has no explicit unique key. The data could easily look like the following, where 12278 is the unique userId.

12,278  8,420   pretend_timestamp   admin_user
12,278  8,430   pretend_timestamp   admin_user
12,278  8,476   pretend_timestamp   admin_user
12,278  8,478   pretend_timestamp   admin_user
12,278  18,578  pretend_timestamp   admin_user
12,278  8,482   pretend_timestamp   admin_user
12,278  18,539  pretend_timestamp   admin_user

So how do I do the following using idiomatic spring-boot? 

DELETE USER_ROLES where USER_ID = 12278

I have tried the following, but spring-boot throws an error:

userRoleRepository.deleteById(user.getUserId);

gives

org.hibernate.jdbc.BatchedTooManyRowsAffectedException: Batch update returned unexpected row count from update
spring-data-jpa spring-boot hibernate
2 Answers

On your UserRoleRepository interface try the following: void deleteAllByUserId(Long userId); and then call this in your service logic.

user_90852730

As per my knowledge the design of your UserRoleMap Entity is wrong, since your are mentioning userId as the Id(that means primary key).Thus, it is always required that your userId should be unique for each row in USER_ROLES table(which is the feature of primary key).

According to me you might not be defining userId as primary key in your table scripts while creating table and you might have inserted data via insert queries into database and not via spring-data-jpa.

Now in your spring-data-jpa when you are trying to deleteById , it expects to find only one row and delete(because your mentioning @Id for userId) but since you have multiple rows in your table with the same userId, you are facing the above exception.

The best possible solution would be create a proper primary key in your USER_ROLES table (might be the composite primary key of userId and roleId)

or delete via HQL

(delete from UserRoleMap where userId =:userID).

But ideally while using ORM a unique primary key in your mapping table is quite necessary otherwise you may face more issues.

user_76718760
Related