Spring Data JPA - Pass column name and value as parameters

Question

I just wanted to know how to pass column name and its value to @Query annotation in Spring Data JPA.

Basically column names will be static and we used to put every column as a element in Entity class. But here I want something different, here column name will be dynamic I will be passing this value as Parameter to the method defined in repository.

Table - Calendar

Columns - id, PersonName, 1, 2, 3......31

Above is the table structure, 1,2,3,.....31 are the column names which represents calendar days and we have values in that columns. I'm using Spring Data JPA to fetch data from DB. Here I just wanted to fetch person name for a particular day. Below given the function defined in repository.

@Query("select c from Calendar c where :calendarDay=:value")
List<Calendar> getPersonName(@Param("calendarDay") String calendarDay, @Param("value") String value);

This is not working for me. Any help would be appreciated.

Solution 1

The only dynamic parameter Spring JPA supports is #{#entityName}. Dynamic column names in @Query annotations are not supported., and that is what you are trying to accomplish.

Your only option is to construct a query manually using either QueryDSL, Specifications or Criteria API or simply by building a query string and passing it to your EntityManager. Regardless, you'll have to write code for that.

See, for instance:

• https://spring.io/blog/2011/04/26/advanced-spring-data-jpa-specifications-and-querydsl/
• How to add custom column name Spring Data JPA?

Solution 2

Take a look at sping data Specifications. You can find your solution there!
Reading the docs you can see that if Calendar is your domain (I would try to find a different name for my domain, there is a Calendar class in Java SE already), then you could use something like the above,

@Repository
public interface CalendarRepository extends JpaRepository<Calendar, Integer>, JpaSpecificationExecutor<Calendar> {
}


public class CalendarSpecification implements Specification<Calendar> {

    private String randomColumnName; // A varchar column.
    private String valueToSearchFor;

    public CalendarSpecification(String randomColumnName, String valueToSearchFor) {
        this.randomColumnName = randomColumnName;
        this.valueToSearchFor = valueToSearchFor;
    }

    @Override
    public Predicate toPredicate(Root<Calendar> root, CriteriaQuery<?> query, CriteriaBuilder builder) {
        return builder.and(builder.equal(root.<String>get(this.randomColumnName), this.valueToSearchFor));
    }
}


@Service
public class CalendarService {

    @Autowired
    private CalendarRepository calendarRepository;

    public List<Calendar> findCustom(String randomColumnName, String valueToSearchFor) {
        CalendarSpecification cs = new CalendarSpecification(randomColumnName, valueToSearchFor);
        return calendarRepository.find(cs);
        // Or using lambda expression - without the need of CalendarSpecification class.
//      return calendarRepository.find((Root<ProductCategory> root, CriteriaQuery<?> query, CriteriaBuilder builder) -> {
//          return builder.and(builder.equal(root.<String>get(randomColumnName), valueToSearchFor));
//      });
    }
}

Solution 3

Maybe you can use CASE, WHEN.

SELECT 
   Id,
   PersonName,
CASE
    WHEN ? = 'day_01' THEN day_01
    WHEN ? = 'day_02' THEN day_02
    WHEN ? = 'day_03' THEN day_03
    WHEN ? = 'day_04' THEN day_04
    WHEN ? = 'day_05' THEN day_05'
    ELSE 0
END
 AS Value FROM Calendar

Java Code

// customize entity
public interface ITask {
    Long getId();
    String getName();
    String getValue();
}

@Repository
public interface CalendarRepository {
    static final String CASE_WHEN = "\nCASE\n"
            + "    WHEN :field = 'day_01' THEN day_01\n"
            + "    WHEN :field = 'day_02' THEN day_02\n"
            + "    WHEN :field = 'day_03' THEN day_03\n"
            + "    WHEN :field = 'day_04' THEN day_04\n"
            + "    WHEN :field = 'day_05' THEN day_05\n"
            + "    ELSE 0\n"
            + "END\n";
    
    @Query(nativeQuery = true, value = "SELECT Id, PersoneName, " + CASE_WHEN + " AS Value FROM Calendar WHERE field = :field")
    public List<ITask> findValues(@Param(value = "field") String field);
}

Comments

[Aliaksei Stadnik]

Maybe spring JPA Spel will help, spring.io/blog/2014/07/15/…

[Robert Niestroj]

You need to use Criteria API

[pvpkiran]

how about using spring data QueryDsl? Have you thought about it?

[Ilya Novoseltsev]

Indeed, try using SPeL: docs.spring.io/spring-data/jpa/docs/current/reference/html/…

[Georgios Syngouroglou]

I posted all the spring-data ways I know (includes your question also) here, stackoverflow.com/questions/48520827/…