[FIXED] Spring Data JPA: Ability to retrieve variable number of columns and specify any Where clause

Issue

I'm using Spring Data JPA to retrieve data from an Entity, say Employee, with 100 columns. User would not need all 100 cloumns each time. So I need a way to :

1. Allow the user to retrieve(select) a variable number of columns and specify a Where clause on any column from the 100 columns

How do I do that with Spring Data JPA? I was only able to retrieve a fixed set of results using:

@Repository
public interface EmployeeRepository  extends JpaRepository<Employee,Integer> {
        List<Employee> findByDateAndRegion(LocalDate Date, String region);
} 

This gives me data but in a very rigid and specific format. I checked the docs and I see there is a way to specify named queries but that seems to specific for my task. And the out of the box methods also seem specific, i.e it won't allow the user to specify which columns to retrieve or specify the where criteria.

Can anyone point me in the right direction regarding this? I think there should be an easy way to acheive this in Spring Data JPA, without writing extensive code for each and every column that can be retrieved?

Solution

Implement a custom method in your repository. In that method you can assemble the where clause and the select list executing it using one of many methods:

• Using the EntityManager, so you are still using JPA if that is of value for you. You can assemble your query either using the Criteria API or String concatenation to assembe JPQL or SQL.
• JdbcTemplate works with basic SQL, but you'd have to provide a RowMapper
• Use third party tools like jOOQ or Querydsl.

WARNING: When using String concatenation to assemble query strings make sure not to open up your code to SQL injection attacks. That is, make sure you are using only String values from trusted sources.

Answered By - Jens Schauder
Answer Checked By - Cary Denson (JavaFixing Admin)