org.hibernate.engine.jdbc.spi.SqlExceptionHelper - Invalid parameter index 2

Question

I am upgrading my application into java 8, hibernate5 and spring 4. There are some existing JPQL query in which i was using Pageable that is not working afer update. I am getting below exception

2019-09-03 07:55:18,814 [http-nio-8080-exec-1] WARN  org.hibernate.engine.jdbc.spi.SqlExceptionHelper - SQL Error: 0, SQLState: 07009
2019-09-03 07:55:18,815 [http-nio-8080-exec-1] ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper - Invalid parameter index 2.

I tried to change my return type from List to Page but no luck

My POM.xml
<dependency>
     <groupId>org.hibernate</groupId>
     <artifactId>hibernate-core</artifactId>
    <version>5.2.10.Final</version>
</dependency>
<dependency>
    <groupId>org.springframework.data</groupId>
    <artifactId>spring-data-jpa</artifactId>
         <version>1.4.3.RELEASE</version>
</dependency>

Repository 
@Query("select u from UserPasswordHistory as u where u.userId = ?1 order by createDate desc")   
    public List<UserPasswordHistory> findUserPasswordHistroy(Long userId, Pageable page);

Service
public List<UserPasswordHistory> findUserPasswordHistroy(Long userId, int resultLimit){
        try{
            Pageable topResult = new PageRequest(0,resultLimit);
            return pwdHistriyRepository.findUserPasswordHistroy(userId,topResult);

Entity
@Entity
@Table(name="user_password_history")
@Configurable(preConstruction = true, autowire = Autowire.BY_TYPE)
public class UserPasswordHistory implements Serializable {
    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "seq_id")
    private Long id;

    /**
     */
    @Column(name = "user_id")
    private Long userId;

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

I need to do pagination

score 0 · Answer 1 · answered Sep 03 '19 at 14:37

You can use Spring data repository magic method, no need to use @Query here. Please try :

public interface UserPasswordHistoryRepository extends JpaRepository<UserPasswordHistory, Long> {
    List<UserPasswordHistory> findByUserIdOrderByCreateDateDesc(Long userId, Pageable pageable);
}

To test for example :

userPasswordHistoryRepository.findByUserIdOrderByCreateDateDesc(1L, PageRequest.of(0, 2));

org.hibernate.engine.jdbc.spi.SqlExceptionHelper - Invalid parameter index 2

1 Answers1