0 Replies Latest reply on Jan 19, 2020 7:01 PM by 4173838

    JPA Criteria Order by FIELD Not Working

    4173838

      I am trying the order the data by field

      e.g Query - select * from user where (location = "%loc%" and role = "TECH") order by field (department, "ELEC", "MECH");

      I created custom Order class for order by field its working fine only in single where condition if i provide two where condition Order by not working and returns 0 records.

      If I remove the order by it returns expected result.

      USER TABLE
         __________________________________________________
        
      |NAME  | AGE  | ROLE    | DEPARTMENT | LOCATION  |
        
      |______|______|______________________|___________|
        
      |name1 |  24  | TECH    | MECH       | location1 |
        
      |name2 |  25  | TECH    | ELEC       | location2 |
        
      |name3 |  26  | TECH    | COMP       | location3 |
        
      |name4 |  27  | NONTECH | CVL       | location4 |
        
      -------------------------------------------------------

      *** EXPECTED RESULT ***
          _________________________________________________
        
      |NAME    | AGE  | ROLE  | DEPARTMENT | LOCATION  |
        
      |________|______|____________________|___________|
        
      |name2   |  25  | TECH  | ELEC       | location2 |
        
      |name1   |  24  | TECH  | MECH       | location1 |
        
      |name3   |  26  | TECH  | COMP       | location3 |
        
      --------------------------------------------------

       

      Source Code

      @Autowired

      EntityManager em;

       

      public List<UserEntity> getUserDetails(Map<String, String> predicateMap) {

          CriteriaBuilder builder = em.getCriteriaBuilder();

          CriteriaQuery<UserEntity> criteriaQuery = builder.createQuery(UserEntity.class);

          Root<UserEntity> userData = criteriaQuery.from(UserEntity.class);

       

          //Order By field DEPARTMENT

          List<Expression<?>> departmentList = new ArrayList<>();

          departmentList.add(userData.get("department"));

          departmentList.add(builder.parameter(String.class, "param1"));

          departmentList.add(builder.parameter(String.class, "param2"));

          departmentList.add(builder.parameter(String.class, "param3"));

          departmentList.add(builder.parameter(String.class, "param4"));

       

          OrderByField order = new OrderByField(builder, departmentList); //custom class for ordeBy field

       

          //WHERE CLUASE for location and role

       

          List<Predicate> predicates = new ArrayList<>();

          predicates.add(builder.like(userData.get("location"), "%" + "loc" + "%"));

          predicates.add(builder.like(userData.get("role"), "%" + "TECH" + "%"));

          criteriaQuery.where(predicates.toArray(new Predicate[predicates.size()]));

          criteriaQuery.orderBy(order);

       

       

          TypedQuery<UserEntity> query = em.createQuery(criteriaQuery);

       

       

          //ORDER BY FIELD PARAMS

          query.setParameter("param1", "ELEC");

          query.setParameter("param2", "MECH");

          query.setParameter("param3", "CVL");

          query.setParameter("param4", "COMP");

       

       

          return query.getResultList();

       

      ---------------------------------------------------------------------------------------

      Entity Class

       

      import javax.persistence.Column;

      import javax.persistence.Entity;

      import javax.persistence.Id;

      import javax.persistence.Table;

       

      import org.springframework.stereotype.Component;

       

      @Component

      @Entity

      @Table(name = "USER_TABLE_TEST")

      public class UserEntity {

       

      @Id

      @Column(name = "UNIQUE_ID")

      private String uniqueId;

       

      @Column(name = "NAME")

      private String name;

       

       

      @Column(name = "AGE")

      private String age;

       

       

      @Column(name = "ROLE")

      private String role;

       

       

      @Column(name = "DEPARTMENT")

      private String department;

       

       

      @Column(name = "LOCATION")

      private String location;

       

      ----------------------------------------------------------------------------------------------

      Custom OrderBy Field Class

       

       

      import java.util.List;

       

       

      import javax.persistence.criteria.CriteriaBuilder;

      import javax.persistence.criteria.Expression;

      import javax.persistence.criteria.Order;

       

      public class OrderByField implements Order {

       

       

      private Expression<?> expression;

       

      @Override

      public Order reverse() {

      return null;

      }

       

       

      @Override

      public boolean isAscending() {

      return true;

      }

       

       

      @Override

      public Expression<?> getExpression() {

      return expression;

      }

       

       

      public OrderByField(CriteriaBuilder cb, List<Expression<?>> list) {

      this.expression = cb.function("field", Object.class, list.toArray(new Expression<?>[list.size()]));

      }

      }