2
public List<Series> findSeries(int period, String fieldname, int num) {
    TypedQuery<Series> query = em.createQuery(
            "select s from Series s where  s.period = ?1 order by ?2",
            Series.class);
    query.setParameter(1, period);
    query.setParameter(2, fieldname);
    query.setMaxResults(num);
    return query.getResultList();
}

This is the method I am using. I think order by isn't even getting executed, it doesn't give any error even when I pass incorrect fieldname.

Bindu Shrestha
  • 51
  • 1
  • 4
  • Have you tried entering a column name directly in `order by` instead of passing it as a paramter? What i think the problem is, your variable `fieldName` is of type String. The typed query is taking it as a String literal instead of a column name. `select s from series where s.period = ?1 order by "filedName"`. That's why I believe it is not working. – Ankit Jan 20 '15 at 08:31

2 Answers2

3

When it comes to dynamic limit and ordering, its best to use PagingAndSortingRepository so now my Repository extends this repository. I can simply use JPA criteria query as below.

If u want to learn more about JPA criteria query i found this very helpful http://docs.spring.io/spring-data/data-jpa/docs/1.0.x/reference/html/#jpa.query-methods.query-creation

 @Repository
public interface SeriesRepository extends PagingAndSortingRepository<Series,Long>{
List<Series> findByPeriod(int period, Pageable pageable);
}

And then when I call this method from my dao i can just instantiate PageRequest which is one of the implementation of Pageable. I can add limit and sorting order to this instance.

public List<Series> getSeriesByFilter(int period, String fieldname, int num) {
    Sort sort = new Sort(Sort.Direction.ASC, fieldname);
    Pageable pageable = new PageRequest(0, num, sort);
    return seriesRepo.findByPeriod(period, pageable);
}
Bindu Shrestha
  • 51
  • 1
  • 4
2

You cannot pass variables as column name in order by.

There is a work around which may help you achieve what you are trying.

public List<Series> findSeries(int period, String fieldname, int num) {
    String query = "select s from Series s where s.period = "+period+" order by "+fieldname;

     return entityManager.createQuery(query).getResultList();
}

Check this question Hibernate Named Query Order By parameter

There are ways to pass column name in order by in ASP, however I am not able to find anything in Spring or JPA.

"Order By" using a parameter for the column name

http://databases.aspfaq.com/database/how-do-i-use-a-variable-in-an-order-by-clause.html

Community
  • 1
  • 1
Ankit
  • 1,075
  • 1
  • 8
  • 19
  • thanks ankit. But i found there's a better way of doing it using Pageable interface. This link helped http://www.petrikainulainen.net/programming/spring-framework/spring-data-jpa-tutorial-part-seven-pagination/ – Bindu Shrestha Jan 20 '15 at 17:31