I am encountering issues with my repository which extends CrudRepository, specifically with the update and delete queries. The repository interface is as shown below:

import com.rmit.sept.bk_loginservices.model.Business;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

@Repository
public interface BusinessRepository extends CrudRepository<Business, Long> {

    // Don't use `SELECT * FROM Business WHERE businessId = :businessId` it doesn't work!
    @Query("FROM Business b WHERE b.userKey = :userKey")
    Business getBusinessByUserPrimaryKey(Long userKey);

    @Modifying
    @Transactional
    @Query("UPDATE Business b SET b.approved = 1 WHERE b.businessId = :businessId")
    void approveBusiness(String businessId);

    @Modifying
    @Transactional
    @Query("DELETE FROM Business WHERE businessId = :businessId")
    void rejectBusiness(String businessId);
}

For whatever reason, the approveBusiness and rejectBusiness methods do not throw any errors and do not modify the MySQL database. I have no idea why it is doing this, and am struggling to locate the issue. So far, I have:

1. Verified that the correct query is being executed by turning on spring.jpa.show-sql=true
2. Verified that the controller and service layers are behaving as expected, and are passing in the correct businessId parameter.
3. Removed and readded the @Transactional annotation at the class level and for each method(as shown)

Any input would be great. Here is the configuration used in application.properties:

spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL57InnoDBDialect
spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl

and here is the Business class itself:

package com.rmit.sept.bk_loginservices.model;


import javax.persistence.*;

@Entity
@Table(name="business")
public class Business{

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(unique = true, name = "businessId")
    private String businessId;
    @Column(name = "approved")
    private Boolean approved;
    @Column(name = "businessAddress")
    private String businessAddress;
    @Column(name = "userKey")
    private Long userKey;

    // Hibernate needs this
    public Business(){

    }

    public Business(String businessId, boolean isApproved, String businessAddress, Long userKey){
        this.businessId = businessId;
        this.approved = isApproved;
        this.businessAddress = businessAddress;
        this.userKey = userKey;
    }

    public Long getUserKey() {
        return userKey;
    }

    public void setUserKey(Long userKey) {
        this.userKey = userKey;
    }

    public String getBusinessAddress() {
        return businessAddress;
    }

    public void setBusinessAddress(String businessAddress) {
        this.businessAddress = businessAddress;
    }

    public String getBusinessId() {
        return businessId;
    }

    public void setBusinessId(String businessId){
        this.businessId = businessId;
    }

    public Boolean getApproved() {
        return approved;
    }

    public void setApproved(Boolean isApproved) {
        this.approved = isApproved;
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }
}

CodePudding user response：

Can you please check that your primary key is of type Long But, you passed it in the string format? try to typecast to check if the problem is because of typecasting.

Also, make sure you are modifying the existing entity.

CodePudding user response：

I think you need to specify the name of parameter like

void approveBusiness(@Param("businessId") String businessId); 
// it's org.springframework.data.repository.query.Param class for import

As for rejectBusiness I would try additionally to Param change the annotation @Modifying as follows:

@Modifying(clearAutomatically = true)
@Query("DELETE FROM Business WHERE businessId = :businessId")
void rejectBusiness(@Param("businessId") String businessId);

Try removing @Transactional annotation for both methods