CODEWITHSUNDEEP
javasqlhibernatespring-boot
Rajeev Massey
Rajeev Massey

Reputation: 137

Could not execute statement; SQL [n/a]; constraint [null];

I am getting this error while delete a row from a particular schema:

Hibernate: select journal0_.id as id1_2_0_, journal0_.content as content2_2_0_, journal0_.filename as filename3_2_0_, journal0_.subject as subject4_2_0_, journal0_.tags as tags5_2_0_, journal0_.user_id as user_id6_2_0_, journal0_.version as version7_2_0_ from journal journal0_ where journal0_.id=?
Hibernate: select nextval ('hibernate_sequence')
Hibernate: insert into subscription (journal_id, user_id, version, id) values (?, ?, ?, ?)
2016-09-03 01:08:01.581  WARN 13462 --- [nio-8080-exec-9] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 23505, SQLState: 23505
2016-09-03 01:08:01.581 ERROR 13462 --- [nio-8080-exec-9] o.h.engine.jdbc.spi.SqlExceptionHelper   : Unique index or primary key violation: "UK_9XFQUT5UKXNSBX8NL2LR23TC5_INDEX_9 ON PUBLIC.SUBSCRIPTION(USER_ID, JOURNAL_ID) VALUES (9, 2, 10)"; SQL statement:
insert into subscription (journal_id, user_id, version, id) values (?, ?, ?, ?) [23505-191]
2016-09-03 01:08:01.598  INFO 13462 --- [nio-8080-exec-9] o.h.e.j.b.internal.AbstractBatchImpl     : HHH000010: On release of batch it still contained JDBC statements
2016-09-03 01:08:01.632 ERROR 13462 --- [nio-8080-exec-9] c.j.exceptions.ErrorController           : Exception during execution of SpringSecurity application

org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; constraint [null]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement

Even though the delete method with proper arguments is called still it's running the insert query which is causing data violation issue.

Here is the model:

@Entity
@Table(uniqueConstraints={@UniqueConstraint(columnNames={"userId", "journalId"})})
public class Subscription {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Integer id;

    @Version
    private Integer version;

    private Integer userId;
    private Integer journalId;

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

    public Integer getId() {
        return this.id;
    }

    public void setVersion(Integer version) {
        this.version = version;
    }

    public Integer getVersion() {
        return this.version;
    }

    public void setUserId(Integer userId) {
        this.userId = userId;
    }

    public Integer getUserId() {
        return this.userId;
    }

    public void setJournalId(Integer journalId) {
        this.journalId = journalId;
    }

    public Integer getJournalId() {
        return this.journalId;
    }
}

This is where the delete method is called:

@Override
public void unsubscribeJournalForSubscriber(Journal journal, Account subscriber) {

    Subscription subscription = new Subscription();
    subscription.setJournalId(journal.getId());
    subscription.setUserId(subscriber.getId());


    this.subscriptionRepository.delete(subscription);
}

Upvotes: 7

Views: 32405

Answers (2)

Keaz
Keaz

Reputation: 985

If you want to delete records without using primary key then create a query for that. create a method in the "SubscriptionRepository" called

@Modifying @Query("delete from Subscription s where s.role. userId = ?1 and s. journalId = ?2") deleteByUserIdJournalId(Integer userId,Integer journalId) then use that method to delete your records. follow this https://docs.spring.io/spring-data/jpa/docs/current/reference/html/

Upvotes: 1

Md Zahid Raza
Md Zahid Raza

Reputation: 971

For deleting Subscription entity, you need to set only Primary key of the entity, which is id field of Subscription Entity. However you are setting journalId and userId field and trying to delete. You should only set the id field of Subscription entity and it will be deleted.

Update:

Your repository code should be

public interface SubscriptionRepository extends CrudRepository<Subscription, Integer> {

    /**
     * Returns array of Subscribed journals by specific user's id
     * 
     * @param userId
     * @return
     */
    ArrayList<Subscription> findByUserId(Integer userId);
    List<Subscription> findByUserIdAndJournalId(Integer userId, Integer journalId);

}

Now your business loginc code will be:

@Override
public void unsubscribeJournalForSubscriber(Journal journal, Account subscriber) {

    List<Subscription> list = subscriptionRepository.findByUserIdAndJournalId(subscriber.getId(), journal.getId());

    for(Subscription subscription : list){
        subscriptionRepository.delete(subscription);
    }

}

Upvotes: 0

Related Questions