CODEWITHSUNDEEP
springjpaspring-dataspring-data-jpa
Abhij
Abhij

Reputation: 1272

Delete Operation on Embedded Object Spring JPA

I Have below Entities :

 @Entity(name = "USRGRP_MAP")
public class UserGroupMapping {

    @Id
    @Column(name = "USRGRP_ID")
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "USER_GRP_MAP_SEQ")
    @SequenceGenerator(sequenceName = "usrgrp_map_seq",allocationSize = 1,name = "USER_GRP_MAP_SEQ")
    private Long mappingId;

    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "USER_ID", referencedColumnName = "USER_ID")
    private User user;

    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "USR_GRP_ID", referencedColumnName = "USR_GRP_ID")
    private UserGroup group;

    @Column(name = "USR_USRGRP_ACT")
    private String userGroupAct;

    getter/setters
}

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

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

    @Column(name = "LOGIN_ID")
    private String userName;

    getter/setters
}

@Entity(name = "USR_GRP")
public class UserGroup {

    @Id
    @Column(name = "USR_GRP_ID")
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "USER_GRP_SEQ")
    @SequenceGenerator(sequenceName = "usr_grp_seq",allocationSize = 1,name = "USER_GRP_SEQ")
    private long groupId;

    @Column(name = "GRP_NM")
    private String groupName;

    @Column(name = "GRP_DESC")
    private String groupDesc;

     getter/setters
}

UserGroupMapping contains has many to one relationship with both user and group. Now I want to do CRUD operation on UserGroupMapping for that I have created repository as below: 

public interface UserGroupMappingRepository extends JpaRepository<UserGroupMapping, Long> {
    List<UserGroupMapping> findByGroup(UserGroup group);
    List<UserGroupMapping> findByUser(User user);
}

Now I want to write delete operation(for particular user and group) on UserGroupMapping without deleting any entry in USER and USR_GRP table , Just need to remove entry from USRGRP_MAP table.

I am trying to achieve it using native query:

@Query(value = "delete from USR_USRGRP_MAP where user_id = :userId and usr_grp_id = :groupId",nativeQuery = true)
    void deleteUserGroupMappingByUserAndGroup(@Param("userId") Long userId, @Param("groupId") Long groupId);

Facing Exception Invalid SQL grammar, although query work fine in sql developer.

Below is my service class:

@Service

public class UserGroupMappingServiceImpl implements UserGroupMappingService{

    @Autowired
    private UserGroupMappingRepository repository;


    @Override
    public void deleteUserGroupMapping(Long userId, Long groupId) {
        repository.deleteUserGroupMappingByUserAndGroup(userId,groupId);
    }


}

Could anyone suggest correct way to delete entry from UserGroupMapping without deleting user and group ?

Below is USRGRP_MAP table:

    USRGRP_ID    USER_ID USR_USRGRP_ID USR_USRGRP_ACT
------------- ---------- ------------- -
           41     306106            41 Y
           14     108527            14 Y
            8     295597             8 N
           10     296518            10 Y
           11     295597            11 Y

Thanks in advance .

Upvotes: 0

Views: 2586

Answers (1)

vRaptor
vRaptor

Reputation: 21

Try to change @Query(value = "delete from USR_USRGRP_MAP where user_id = :userId and usr_grp_id = :groupId",nativeQuery = true) void deleteUserGroupMappingByUserAndGroup(@Param("userId") Long userId, @Param("groupId") Long groupId);

To this:

@Modifying

@Query(value = "delete from USR_USRGRP_MAP where user_id = :userId and usr_grp_id = :groupId",nativeQuery = true) void deleteUserGroupMappingByUserAndGroup(@Param("userId") Long userId, @Param("groupId") Long groupId);

Cheers

~Emil

Upvotes: 1

Related Questions