I have a Entity Policy
public class Policy extends BaseEntity {
private Long id;
private String policyNumber;
......
@OneToMany(fetch = FetchType.LAZY, cascade = CascadeType.ALL)
@OrderBy(value = "id")
@JoinColumn(name = "policyID")
@Where(clause = "type = 'POLICY_INFO'")
private Set<AssignedUser> assignUsers = new HashSet<>();
}
I'm trying to get policies who are assigned to specific user. I'm using criteria builder for that and my function looks like this:
public PaginationResult getFilteredData(List<FilterConstraint> filters, Class className) {
if (filters.get(0).getCount() == 0) {
filters.get(0).setCount(50);
}
try {
CriteriaBuilder builder = em.getCriteriaBuilder();
CriteriaQuery<?> criteriaQueryMain = builder.createQuery(className);
Root<?> root = criteriaQueryMain.from(className);
Predicate predicate = builder.conjunction();
List<Predicate> predicateList = new ArrayList<>();
for (FilterConstraint constraint : filters) {
switch (constraint.getOperator()) {
case IN:
LocalDate inDate = new LocalDate();
Date maxRange = inDate.plusDays(Integer.parseInt(constraint.getValues().getMinValue())).toDate();
predicate = builder.and(builder.between(root.get(constraint.getField().getValue()), inDate.toDate(), maxRange));
break;
case RANGE:
LocalDate rangeDate = new LocalDate();
Date minDate = rangeDate.plusDays(Integer.parseInt(constraint.getValues().getMinValue())).toDate();
Date maxDate = rangeDate.plusDays(Integer.parseInt(constraint.getValues().getMaxValue())).toDate();
predicate = builder.and(builder.between(root.get(constraint.getField().getValue()), minDate, maxDate));
break;
case AFTER:
predicate = builder.and(builder.greaterThan(root.get(constraint.getField().getValue()), constraint.getValues().getStartDate()));
break;
case BEFORE:
predicate = builder.and(builder.lessThan(root.get(constraint.getField().getValue()), constraint.getValues().getStartDate()));
break;
case BETWEEN:
if (constraint.getField().getType() == FieldDataType.DATE) {
predicate = builder.and(builder.between(root.get(constraint.getField().getValue()), constraint.getValues().getStartDate(), constraint.getValues().getEndDate()));
} else if (constraint.getField().getType() == FieldDataType.INTEGER) {
predicate = builder.and(builder.between(root.get(constraint.getField().getValue()), Integer.valueOf(constraint.getValues().getMinValue()), Integer.valueOf(constraint.getValues().getMaxValue())));
} else {
predicate = builder.and(builder.between(root.get(constraint.getField().getValue()), constraint.getValues().getMinValue(), constraint.getValues().getMaxValue()));
}
break;
case EMPTY:
predicate = builder.and(builder.isNull(root.get(constraint.getField().getValue())));
break;
case EQUALS:
if (constraint.getField().getType() == FieldDataType.ENUM) {
if (constraint.getValues().getEnumValue().size() > 1) {
List<Predicate> predicates = new ArrayList<>();
for (EnumValue enumValue : constraint.getValues().getEnumValue()) {
for (Field f : className.getDeclaredFields()) {
if (f.getName().equals(constraint.getField().getValue())) {
System.out.println("T");
Class<?> clz = f.getType();
Object[] consts = clz.getEnumConstants();
Root<?> finalEqualsRoot = root;
Arrays.stream(consts)
.filter(e -> e.toString().equals(enumValue.getValue()))
.forEach(e -> predicates.add(builder.equal(finalEqualsRoot.get(constraint.getField().getValue()), e)));
}
}
}
predicate = builder.and(builder.or(predicates.toArray(new Predicate[]{})));
break;
}
predicate = builder.equal(root.get(constraint.getField().getValue()), getObjectBySelectedField(className, constraint));
break;
}
if (constraint.getField().getType() == FieldDataType.BOOLEAN) {
if (Boolean.valueOf(constraint.getValues().getValue()))
predicate = builder.and(builder.isTrue(root.get(constraint.getField().getValue())));
else predicate = builder.and(builder.isFalse(root.get(constraint.getField().getValue())));
break;
}
predicate = builder.and(builder.equal(root.get(constraint.getField().getValue()), constraint.getValues().getValue()));
/*predicate = builder.and(builder.equal(root.get(constraint.getField().getValue()).get("id"), constraint.getValues().getValue()));*/
break;
case NOT_EQUALS:
if (constraint.getField().getType() == FieldDataType.ENUM) {
if (constraint.getValues().getEnumValue().size() > 1) {
List<Predicate> predicates = new ArrayList<>();
for (EnumValue enumValue : constraint.getValues().getEnumValue()) {
for (Field f : className.getDeclaredFields()) {
if (f.getName().equals(constraint.getField().getValue())) {
System.out.println("T");
Class<?> clz = f.getType();
Object[] consts = clz.getEnumConstants();
Root<?> finalNotEqualsRoot = root;
Arrays.stream(consts)
.filter(e -> e.toString().equals(enumValue.getValue()))
.forEach(e -> predicates.add(builder.notEqual(finalNotEqualsRoot.get(constraint.getField().getValue()), e)));
}
}
}
predicate = builder.and(builder.and(predicates.toArray(new Predicate[]{})));
break;
}
predicate = builder.notEqual(root.get(constraint.getField().getValue()), getObjectBySelectedField(className, constraint));
break;
}
if (constraint.getValues().getValue() != null)
predicate = builder.and(builder.and(builder.notEqual(root.get(constraint.getField().getValue()), constraint.getValues().getValue())));
else
predicate = builder.and(builder.and(builder.notEqual(root.get(constraint.getField().getValue()), constraint.getValues().getStartDate())));
break;
case LESS_THAN:
predicate = builder.and(builder.lessThan(root.get(constraint.getField().getValue()), constraint.getValues().getValue()));
break;
case MORE_THAN:
predicate = builder.and(builder.greaterThan(root.get(constraint.getField().getValue()), constraint.getValues().getValue()));
break;
case NOT_EMPTY:
predicate = builder.and(builder.isNotNull(root.get(constraint.getField().getValue())));
break;
case ON:
predicate = builder.between(root.get(constraint.getField().getValue()), DateUtils.getFirstSecondOfDate(constraint.getValues().getStartDate()), DateUtils.getLastSecondOfDate(constraint.getValues().getStartDate()));
break;
case STARTS_WITH:
predicate = builder.and(builder.like(root.get(constraint.getField().getValue()), constraint.getValues().getValue() + "%"));
break;
case TODAY:
predicate = builder.and(builder.between(root.get(constraint.getField().getValue()), DateUtils.getFirstSecondOfDate(new Date()), DateUtils.getLastSecondOfDate(new Date())));
break;
case THIS_WEEK:
LocalDate thisWeekDate = new LocalDate();
predicate = builder.and(builder.between(root.get(constraint.getField().getValue()), thisWeekDate.minusDays(7).toDate(), thisWeekDate.toDate()));
break;
case THIS_MONTH:
LocalDate thisMonthDate = new LocalDate();
predicate = builder.and(builder.between(root.get(constraint.getField().getValue()), thisMonthDate.minusDays(thisMonthDate.getDayOfMonth() - 1).toDate(), thisMonthDate.toDate()));
break;
case THIS_YEAR:
LocalDate thisYearDate = new LocalDate();
predicate = builder.and(builder.between(root.get(constraint.getField().getValue()), thisYearDate.minusYears(1).toDate(), thisYearDate.toDate()));
break;
case LAST_DAYS:
LocalDate last_DaysDate = new LocalDate();
predicate = builder.and(builder.between(root.get(constraint.getField().getValue()), last_DaysDate.minusDays(Integer.valueOf(constraint.getValues().getValue())).toDate(), last_DaysDate.toDate()));
break;
}
if (!predicateList.contains(predicate)) {
predicateList.add(predicate);
}
}
if (className.equals(Policy.class)) {
Long userID = 555L;
Join assignUsers = root.join("assignUsers");
predicate = builder.equal(assignUsers.get("assignedUserId"), userID);
predicateList.add(predicate);
}
CriteriaQuery<Long> cq = builder.createQuery(Long.class);
cq.select(builder.count(cq.from(className)));
em.createQuery(cq);
cq.where(predicateList.toArray(new Predicate[]{}));
Long count = em.createQuery(cq).getSingleResult();
criteriaQueryMain.where(predicateList.toArray(new Predicate[]{}));
predicateList.clear();
TypedQuery<?> q = em.createQuery(criteriaQueryMain);
q.setMaxResults(filters.get(0).getCount());
int firstResult = filters.get(0).getPage() * filters.get(0).getCount() - filters.get(0).getCount();
q.setFirstResult(firstResult);
PaginationResult result = new PaginationResult();
result.setData(q.getResultList());
result.setMaxResults(count);
System.out.println(result.getData().size());
return result;
} catch (Exception e) {
System.out.println(e.getMessage());
return null;
}
}
When I try to run int I receive such error message:
org.hibernate.hql.internal.ast.QuerySyntaxException: Invalid path: 'generatedAlias1.assignedUserId' [select count(generatedAlias0) from com.clphub.model.policy.Policy as generatedAlias0 where ( generatedAlias0.id>100L ) and ( generatedAlias1.assignedUserId=555L )]
I tried a lot of combinations but can't get read of this error message. Problem occurs on count section of code. If I comment the count query - I get the data correctly.
Can someone tell me what is the problem? what I'm doing wrong?
I use JPA, Hibernate, Spring boot
In your count, try to put the root
It seems, by the exception, that the where clause is trying to get a variable that is not in "from".