Issue
I have 20+ tables in my DB and I need to have a method to fetch all non-deleted rows from the tables. Therefore, I wrote the following interface:
@NoRepositoryBean
public interface CommonRepositry<T, ID extends Serializable> extends CrudRepository<T, UUID>{
List<T> findByIsDeleted(boolean isDeleted);
}
All my model repositories extend this CommonRepositry
and I'm able to access all the non-deleted rows:
public interface ModelNameRepository extends CommonRepositry<ModelName, UUID> {
// sevral spring JPA methods
As the tables are growing in size, I want to put an adjustable limit on the number of results that the method returns. There are two ways to do so and I'm having issues with both of them:
From the docs, this is how I am supposed to do it:
List<User> findTop1000ByIsDeleted(boolean isDeleted);
It only returns me the top 1000 rows.
PROBLEM: I want to externalise this 1000
to the properties file so that if I have to change, I do it only at a single place.
Another way to do that would be to use @Query
annotation and write the SQL query:
@Query(value = "SELECT * FROM table_name" + " WHERE is_deleted = :isDeleted LIMIT 1000;", nativeQuery = true)
List<T> findByIsDeleted(@Param("isDeleted")boolean isDeleted);
PROBLEM: how do I get the table_name? My model class names to table-names have simple CamelCase to camel_case mapping. But how do I get the model class name from T
?
Solution
Problem 1: You can pass a second parameter to your query, called Pageable.
List<T> findByIsDeleted(boolean isDeleted, Pageable pageable);
This Pageable
is an interface, but you can use the PageRequest
implementation for it. It requires two things to create a PageRequest
, the page number and the size (meaning that how many rows you want to fetch). Of course, in your case the page number is 0 and the size should be set by reading the value from the properties file.
Problem 2: You can externalize the table name to a class which only consists of constant values. You can use these values in your @Table
annotation on your entity class and here as well.
More reading: http://docs.spring.io/spring-data/jpa/docs/current/reference/html/#repositories.special-parameters
Answered By - Arnold Galovics
Answer Checked By - Robin (JavaFixing Admin)