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
The solution suggested in the comments helped.
Implement a custom dialect that extends
org.hibernate.dialect.PostgreSQLDialect
and overrides thegetLimitHandler
to return theorg.hibernate.dialect.pagination.LimitOffsetLimitHandler.INSTANCE
.Then configure Hibernate to use that. specify in properties
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