skip to Main Content

After upgrade to Spring Boot 3 I`ve got next problem;
My entity

@Data
@Table(name = "campaign_status")
@Entity
public class CampaignStatus {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "campaign_status_id")
    private Long campaignStatusId;

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

}

My jpa repository interface

@Repository
public interface CampaignStatusDao extends JpaRepository<CampaignStatus, Long> {
    CampaignStatus findTop1ByName(String status);
}

And I get the next errorin console

Hibernate: 
    select
        c1_0.campaign_status_id,
        c1_0.name 
    from
        campaign_status c1_0 
    where
        c1_0.name=? fetch first ? rows only

2023-01-26T11:43:12.854+02:00  WARN 18183 --- [           main] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: 42601
2023-01-26T11:43:12.854+02:00 ERROR 18183 --- [           main] o.h.engine.jdbc.spi.SqlExceptionHelper   : ERROR: syntax error at or near "$2"
  Position: 99

I tried with PostgreSQL 9.6.24 and there is no ERROR with the same code. But upgrade db version in production will be very painful. So may be there is a way to figure it out.
By the way the same error if I try to execute jpa method with paging

2

Answers


  1. Chosen as BEST ANSWER

    The solution suggested in the comments helped.

    Implement a custom dialect that extends org.hibernate.dialect.PostgreSQLDialect and overrides the getLimitHandler to return the org.hibernate.dialect.pagination.LimitOffsetLimitHandler.INSTANCE.

    Then configure Hibernate to use that. specify in properties

    spring.jpa.database-platform=your.package.CustomPostgreSQLDialect
    

  2. I too faced this issue in FindTop, FindFirst, FindFirst10 after Spring Boot 3 migration when my development db ran at PostgreSQL 9.6.5.
    I suspected that it was a version issue b/w JPA & PostgreSQL.
    I then checked with my QA & Others env they are running at PostgreSQL 11.6.
    So at the time, there is no issue.

    Solution 1:
    Upgrade your db to higher versions. That will do.

    Solution 2
    Go with Native Query

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search