Spring Data JPA - Pass column name and value as pa

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.

Answer 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?

Answer 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));
//      });
    }
}