JPA Repository Dynamic Query with dynamic filter

[avaya1]

Blogpost:

I have a question about [Writing dynamic queries with Spring Data JPA]https://dimitr.im/writing-dynamic-queries-with-spring-data-jpa).

Description:

How do I implement Custom FindAll where the Query(HQL/JPQL/Native) is stored in DB and it is dynamic in nature (depending on the logged in user role). Also the user provide dynamic filter (additional condition to filter the Result). Example
Query in DB is : select s from StudentEnrolled se, Student s where se.calendarCourse.calendarCourseId = :calendarCourseId and se.courseGroup.courseGroupId = :courseGroupId and se.student.resourceId=s.resourceId Additional parameter passed by user(assume that the condition is valid and can be appended directly to the query) : s.age> :age

Sorting passed by User: desc name

with current custom implementation I have code as below String dbQuery="some query"; String additionalFilter ="some filter condition"; String orderBy="""//some field String finalQuery = dbQuery+additionalFilter+" order by "+orderBy;

org.hibernate.query.Query query = session.createQuery(selectQuery): HashMap<String,Object> params = //user parameter and application parameter . //some of the parameter need to be decrypted, which I do through the org.hibernate.query.Query query. Which provide the parameter information. I have some information in entity to indicate the value need to converted if received from user. HashMap<String,Object> paramsTransformed = SecuredQueryParamHandler.getTransformedValues(query, params );

query.setProperties(paramsTransformed ); //set pagination query.setFirstResult((pageNumber * pageSize)); query.setMaxResults(pageSize);

Please help:

[g00glen00b]

Repositories and JPA are made to create an abstraction layer on top of queries. That means that if you have a custom SQL query to execute, repositories are probably not the way to go. I would try to avoid executing queries like that at all cost, and if can't avoid it, I would go for JdbcTemplate to execute the query. What exactly is it that you're stuck with?

[avaya1]

Thanks for response. I was thinking of using fragment like below. All my repository interface should implement this additional fragment, I have two queries: 1. how do I get the resultEntityType

2. does spring create multiple instance of GenericCustomRepositoryImpl ? One for each repository.

public interface GenericCustomRepository<T> {
    long searchCount(SearchCriteria sc);
    List<T> search(SearchCriteria searchCriteria);
}

public class GenericCustomRepositoryImpl<T> implements GenericCustomRepository<T> {
    @PersistenceContext
    private EntityManager entityManager;
    @Override
    public List<T> search(SearchCriteria searchCriteria) {
                             Query query =  entityManager.createQuery(selectQuery.getQuery(), resultEntityType);
                            //set the parameter and execute the query
                           return query.getResultList();
        }
        public long searchCount(SearchCriteria sc) {
                             Query query =  entityManager.createQuery(selectQuery.getCountQuery(), Long.class);
                               //set the parameter and execute the query
                              return (Long) query.getSingleResult();
         }

[g00glen00b]

I don't think you can access the result type. What you could do in stead if make the GenericCustomRepositoryImpl<T> abstract, and add a constructor like:

public GenericCustomRepositoryImpl(Class<T> resultEntityType) {
    this.resultEntityType = resultEntityType;
}

And now you can create a custom repository implementation for each type, such as:

public class UserCustomRepository extends GenericCustomRepository<User> {
    public UserCustomRepository() {
        super(User.class);
    }
}

I don't have much experience with custom repository implementations though.