Trouble with spring jpa data native Sql query

Question

All my repositories are extending commonService Repository which in return are extending JpaRepository and JpaSpecification

public interface CommonReadRepository<T>
        extends JpaRepository<T, Long>, JpaSpecificationExecutor<T> {

i want to define native Sql query in CommonReadRepository like : Mysql:select * from table limit 1,20;

@Query(value = "select * from ?1 limit ?2,?3", nativeQuery = true)

 List<?> customFindQuery(String tableName,int offset,int limit);

but i recieve SqlGrammerException and unfortunately , i did not find much about syntax in documentation.

i know if i can define the queries in repositories then i know the table name but is it possible to make it generic ?

Thanks

perhaps if you post the full stack trace then there would be more info to actually comment on. More like a MySQL exception ... — Neil Stockton

Elia Rohana Elia Rohana · Accepted Answer · 2016-05-31T21:34:12

Base Repository Section defined in Spring data reference

follow the guidelines defined in the reference like the following :

@NoRepositoryBean
interface CommonReadRepository<T, ID extends Serializable> extends JpaRepository<T, ID> {

  List<T> custonFindQuery();
}
@Repository
interface UserRepository extends CommonReadRepository<User, Long> {
  User findByEmailAddress(EmailAddress emailAddress);
}

For your specific query List<?> customFindQuery(String tableName,int offset,int limit);

its already support in JpaRepository by calling the method:

Page<T> findAll(Pageable pageable)

For example:

Page<User> all = userRepository .findAll(new PageRequest(3, 10));

where offest = 30 (3 x 10), and limit = 10

Trouble with spring jpa data native Sql query

1 Answers