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()]));
}
}