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

Question

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);
}

"Even though the delete method with proper arguments is called..." -- where's the evidence? I see two selects and an insert, no delete. — Jim Garrison
– Jim Garrison, Commented Sep 2, 2016 at 19:48
I meant to delete method is used with proper argument but still the insert query is done instead of a delete . How come ? — Rajeev Massey
– Rajeev Massey, Commented Sep 2, 2016 at 19:52
Have you stepped through the code to the point where you are invoking the delete method? Are you sure that code is executing? — Jim Garrison
– Jim Garrison, Commented Sep 2, 2016 at 21:01

Keaz · Accepted Answer · 2018-07-17 05:30:49Z

1

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/

answered Jul 17, 2018 at 5:30

Keaz

9851 gold badge11 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Md Zahid Raza · Accepted Answer · 2016-09-03 10:05:24Z

0

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);
    }

}

edited Sep 3, 2016 at 10:05

answered Sep 2, 2016 at 21:34

Md Zahid Raza

9712 gold badges13 silver badges29 bronze badges

3 Comments

Rajeev Massey Over a year ago

id is auto generate attribute . So how is it possible to do it without first querying for the id .

Md Zahid Raza Over a year ago

id is generated automatically and set when you insert entity. If you want to delete, You need to set id which will be used for deleting that particular entity. If you do not know before, then you should perform find first and then delete.

Rajeev Massey Over a year ago

Is there any way where i can delete using journal_id and subscription_id?

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related