CODEWITHSUNDEEP
javasqlspringpostgresqlspring-data
Mukit09
Mukit09

Reputation: 3399

Why Spring boot JPA native update is giving PSQLException with postgres Database?

I have this Entity class:

@Entity
@Table(name = "inbox_inbox")
@Getter
@Setter
@TypeDef(name = "jsonb", typeClass = JsonBinaryType.class)
public class Inbox implements Serializable {

    @Id
    private int id;
    @Column(name = "created")
    private Date created;
    @Column(name = "modified")
    private Date modified;
    @Column(name = "status")
    private String status;
}

I have this repository: 

@Repository
public interface InboxRepository extends JpaRepository<Inbox, Integer> {

    List<Inbox> findInboxesByStatus(String status);

    @Modifying
    @Transactional
    @Query(value = "update inbox_inbox i set i.status = ?2 where i.id = ?1", nativeQuery = true)
    int setInboxStatusById(int id, String status);
}

If I call findInboxesByStatus(String status) with required status, then it gives the expected result. But when calling setInboxStatusById() then it is giving me an exception!

I am giving my calling part here: 

int updatedRows = inboxRepository.setInboxStatusById(2, "processing");

And getting this exception: 

2020-02-10 22:21:57.486 DEBUG 7 --- [main] o.s.orm.jpa.JpaTransactionManager        : Creating new transaction with name [org.springframework.data.jpa.repository.support.SimpleJpaRepository.setInboxStatusById]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT
2020-02-10 22:21:57.486 DEBUG 7 --- [main] o.s.orm.jpa.JpaTransactionManager        : Opened new EntityManager [SessionImpl(1663686815<open>)] for JPA transaction
2020-02-10 22:21:57.486 DEBUG 7 --- [main] o.h.e.t.internal.TransactionImpl         : On TransactionImpl creation, JpaCompliance#isJpaTransactionComplianceEnabled == false
2020-02-10 22:21:57.486 DEBUG 7 --- [main] o.h.e.t.internal.TransactionImpl         : begin
2020-02-10 22:21:57.486 DEBUG 7 --- [main] org.postgresql.jdbc.PgConnection         :   setAutoCommit = false
2020-02-10 22:21:57.486 DEBUG 7 --- [main] o.s.orm.jpa.JpaTransactionManager        : Exposing JPA transaction as JDBC [org.springframework.orm.jpa.vendor.HibernateJpaDialect$HibernateConnectionHandle@10fc01e0]
2020-02-10 22:21:57.487 DEBUG 7 --- [main] org.hibernate.SQL                        : update inbox_inbox i set i.status = ? where i.id = ?
2020-02-10 22:21:57.487 DEBUG 7 --- [main] o.h.engine.jdbc.spi.SqlExceptionHelper   : could not execute statement [n/a]

org.postgresql.util.PSQLException: ERROR: column "i" of relation "inbox_inbox" does not exist
  Position: 26
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2497) ~[postgresql-42.2.8.jar!/:42.2.8]
    at .....

Why getting this? I searched for some solution in this site as well. But it seems, it is a new thread. So asking for help. Thanks in advance.

Upvotes: 0

Views: 1525

Answers (1)

Andronicus
Andronicus

Reputation: 26046

Using aliases in the update query is not allowed. Please, use the following:

@Modifying
@Transactional
@Query(value = "update inbox_inbox set status = ?2 where id = ?1", nativeQuery = true)
int setInboxStatusById(int id, String status);

Upvotes: 2

Related Questions