Java Code Examples for javax.persistence.criteria.CriteriaBuilder#lessThan()

@Override
public Predicate toPredicate(final Root<User> root, final CriteriaQuery<?> query, final CriteriaBuilder builder) {
	switch (criteria.getOperation()) {
	case EQUALITY:
		return builder.equal(root.get(criteria.getKey()), criteria.getValue());
	case NEGATION:
		return builder.notEqual(root.get(criteria.getKey()), criteria.getValue());
	case GREATER_THAN:
		return builder.greaterThan(root.get(criteria.getKey()), criteria.getValue().toString());
	case LESS_THAN:
		return builder.lessThan(root.get(criteria.getKey()), criteria.getValue().toString());
	case LIKE:
		return builder.like(root.get(criteria.getKey()), criteria.getValue().toString());
	case STARTS_WITH:
		return builder.like(root.get(criteria.getKey()), criteria.getValue() + "%");
	case ENDS_WITH:
		return builder.like(root.get(criteria.getKey()), "%" + criteria.getValue());
	case CONTAINS:
		return builder.like(root.get(criteria.getKey()), "%" + criteria.getValue() + "%");
	default:
		return null;
	}
}
 

/**
 * 迟到、缺勤、早退、工时不足、异常打卡，但未申诉通过的
 * @param year
 * @param month
 * @return
 * @throws Exception
 */
//@MethodDescribe("获取所有需要导出所有异常数据（未申诉的、申诉未通过的）")
public List<String> getDetailsWithAllAbnormalCase( String year, String month ) throws Exception {
	if( year == null || month == null ){
		return null;
	}
	EntityManager em = this.entityManagerContainer().get( AttendanceDetail.class );
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<String> cq = cb.createQuery(String.class);
	Root<AttendanceDetail> root = cq.from( AttendanceDetail.class);
	cq.select( root.get(AttendanceDetail_.id ));
	
	Predicate p = cb.lessThan( root.get(AttendanceDetail_.appealStatus), 9); //如果等于9就是申诉通过
	p = cb.and(p, cb.equal( root.get(AttendanceDetail_.cycleYear), year ));
	p = cb.and(p, cb.equal( root.get(AttendanceDetail_.cycleMonth), month ));
	
	Predicate orCase = cb.isTrue(root.get(AttendanceDetail_.isLate)); //迟到
	orCase = cb.or( orCase, cb.isTrue( root.get(AttendanceDetail_.isLeaveEarlier)) ); //或者早退
	orCase = cb.or( orCase, cb.isTrue( root.get(AttendanceDetail_.isAbnormalDuty) )); //或者异常打卡
	orCase = cb.or( orCase, cb.isTrue( root.get(AttendanceDetail_.isAbsent) )); //或者缺勤
	orCase = cb.or( orCase, cb.isTrue( root.get(AttendanceDetail_.isLackOfTime) )); //或者工时不足
	
	Predicate where = cb.and( p, orCase );
	
	return em.createQuery(cq.where(where)).setMaxResults(20000).getResultList();
}
 

private List<Draft> listDraft(EntityManagerContainer emc, String sequence) throws Exception {
	Date date = new Date();
	date = DateUtils.addMinutes(date, 0 - Config.processPlatform().getDeleteDraft().getThresholdMinutes());
	EntityManager em = emc.get(Draft.class);
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<Tuple> cq = cb.createQuery(Tuple.class);
	Root<Draft> root = cq.from(Draft.class);
	Path<String> idPath = root.get(Draft_.id);
	Path<String> sequencePath = root.get(JpaObject_.sequence);
	Predicate p = cb.lessThan(root.get(JpaObject_.createTime), date);
	if (StringUtils.isNotEmpty(sequence)) {
		p = cb.and(p, cb.greaterThan(sequencePath, sequence));
	}
	cq.multiselect(idPath, sequencePath).where(p).orderBy(cb.asc(sequencePath));
	List<Tuple> os = em.createQuery(cq).setMaxResults(200).getResultList();
	List<Draft> list = new ArrayList<>();
	for (Tuple o : os) {
		Draft draft = new Draft();
		draft.setId(o.get(idPath));
		draft.setSequence(o.get(sequencePath));
		list.add(draft);
	}
	return list;
}
 

private void clearScheduleLog(Business business) throws Exception {
	List<ScheduleLog> list = new ArrayList<>();
	do {
		EntityManagerContainer emc = business.entityManagerContainer();
		EntityManager em = emc.get(ScheduleLog.class);
		CriteriaBuilder cb = em.getCriteriaBuilder();
		CriteriaQuery<ScheduleLog> cq = cb.createQuery(ScheduleLog.class);
		Root<ScheduleLog> root = cq.from(ScheduleLog.class);
		Calendar threshold = Calendar.getInstance();
		threshold.add(Calendar.DATE, -7);
		Predicate p = cb.lessThan(root.get(ScheduleLog_.createTime), threshold.getTime());
		list = em.createQuery(cq.select(root).where(p)).setMaxResults(2000).getResultList();
		if (!list.isEmpty()) {
			emc.beginTransaction(ScheduleLog.class);
			list.stream().forEach(o -> {
				try {
					emc.remove(o, CheckRemoveType.all);
				} catch (Exception e) {
					logger.error(e);
				}
			});
			emc.commit();
		}
	} while (!list.isEmpty());
}
 

@Override
public Predicate getPredicate(Root<Issue> root, CriteriaBuilder builder) {
	Path<Long> attribute = root.get(Issue.PROP_NUMBER);
	Predicate numberPredicate;
	
	if (operator == IssueQueryLexer.Is)
		numberPredicate = builder.equal(attribute, number.getNumber());
	else if (operator == IssueQueryLexer.IsGreaterThan)
		numberPredicate = builder.greaterThan(attribute, number.getNumber());
	else
		numberPredicate = builder.lessThan(attribute, number.getNumber());
	
	return builder.and(
			builder.equal(root.get(Issue.PROP_PROJECT), number.getProject()),
			numberPredicate);
}
 

@Override
public Predicate getPredicate(Root<PullRequest> root, CriteriaBuilder builder) {
	Path<Long> attribute = root.get(PullRequest.PROP_NUMBER);
	Predicate numberPredicate;
	
	if (operator == PullRequestQueryLexer.Is)
		numberPredicate = builder.equal(attribute, number.getNumber());
	else if (operator == PullRequestQueryLexer.IsGreaterThan)
		numberPredicate = builder.greaterThan(attribute, number.getNumber());
	else
		numberPredicate = builder.lessThan(attribute, number.getNumber());
	
	return builder.and(
			builder.equal(root.get(PullRequest.PROP_TARGET_PROJECT), number.getProject()),
			numberPredicate);
}
 

/**
 * 迟到、缺勤、早退、工时不足、异常打卡，但未申诉通过的
 * @param year
 * @param month
 * @return
 * @throws Exception
 */
//@MethodDescribe("获取所有需要导出所有异常数据（未申诉的、申诉未通过的）")
public List<String> getDetailsWithAllAbnormalCase( String year, String month ) throws Exception {
	if( year == null || month == null ){
		return null;
	}
	EntityManager em = this.entityManagerContainer().get( AttendanceDetail.class );
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<String> cq = cb.createQuery(String.class);
	Root<AttendanceDetail> root = cq.from( AttendanceDetail.class);
	cq.select( root.get(AttendanceDetail_.id ));
	
	Predicate p = cb.lessThan( root.get(AttendanceDetail_.appealStatus), 9); //如果等于9就是申诉通过
	p = cb.and(p, cb.equal( root.get(AttendanceDetail_.cycleYear), year ));
	p = cb.and(p, cb.equal( root.get(AttendanceDetail_.cycleMonth), month ));
	
	Predicate orCase = cb.isTrue(root.get(AttendanceDetail_.isLate)); //迟到
	orCase = cb.or( orCase, cb.isTrue( root.get(AttendanceDetail_.isLeaveEarlier)) ); //或者早退
	orCase = cb.or( orCase, cb.isTrue( root.get(AttendanceDetail_.isAbnormalDuty) )); //或者异常打卡
	orCase = cb.or( orCase, cb.isTrue( root.get(AttendanceDetail_.isAbsent) )); //或者缺勤
	orCase = cb.or( orCase, cb.isTrue( root.get(AttendanceDetail_.isLackOfTime) )); //或者工时不足
	
	Predicate where = cb.and( p, orCase );
	
	return em.createQuery(cq.where(where)).setMaxResults(20000).getResultList();
}
 

@Override
public Predicate getPredicate(Root<Issue> root, CriteriaBuilder builder) {
	Path<Integer> attribute = root.get(Issue.PROP_COMMENT_COUNT);
	if (operator == IssueQueryLexer.Is)
		return builder.equal(attribute, value);
	else if (operator == IssueQueryLexer.IsGreaterThan)
		return builder.greaterThan(attribute, value);
	else
		return builder.lessThan(attribute, value);
}
 

@Override
protected Predicate getValuePredicate(Join<?, ?> field, CriteriaBuilder builder) {
	if (operator == IssueQueryLexer.IsBefore)
		return builder.lessThan(field.get(IssueField.PROP_ORDINAL), date.getTime());
	else
		return builder.greaterThan(field.get(IssueField.PROP_ORDINAL), date.getTime());
}
 

@Override
protected Predicate getValuePredicate(Join<?, ?> field, CriteriaBuilder builder) {
	Path<Integer> attribute = field.get(IssueField.PROP_ORDINAL);
	if (operator == IssueQueryLexer.Is)
		return builder.equal(attribute, value);
	else if (operator == IssueQueryLexer.IsGreaterThan)
		return builder.greaterThan(attribute, value);
	else
		return builder.lessThan(attribute, value);
}
 

@Override
public Predicate getPredicate(Root<Issue> root, CriteriaBuilder builder) {
	Path<Date> attribute = IssueQuery.getPath(root, Issue.PROP_LAST_UPDATE + "." + LastUpdate.PROP_DATE);
	if (operator == IssueQueryLexer.IsBefore)
		return builder.lessThan(attribute, date);
	else
		return builder.greaterThan(attribute, date);
}
 

public Long countNextSubject( Date latestReplyTime ) throws Exception {
	if( latestReplyTime == null ){
		throw new Exception( "latestReplyTime is null!" );
	}
	EntityManager em = this.entityManagerContainer().get( BBSSubjectInfo.class );
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery< Long> cq = cb.createQuery(  Long.class );
	Root<BBSSubjectInfo> root = cq.from( BBSSubjectInfo.class );
	Predicate p = cb.lessThan( root.get( BBSSubjectInfo_.latestReplyTime ), latestReplyTime );
	cq.select( cb.count( root ) );
	return em.createQuery(cq.where(p)).getSingleResult();
}
 

private List<Read> listRead(EntityManagerContainer emc, Date threshold, String sequence) throws Exception {
	EntityManager em = emc.get(Read.class);
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<Tuple> cq = cb.createQuery(Tuple.class);
	Root<Read> root = cq.from(Read.class);
	Path<String> id_path = root.get(Read_.id);
	Path<String> job_path = root.get(Read_.job);
	Path<String> sequence_path = root.get(Read_.sequence);
	Path<String> title_path = root.get(Read_.title);
	Path<Date> startTime_path = root.get(Read_.startTime);
	Predicate p = cb.lessThan(startTime_path, threshold);
	if (StringUtils.isNotEmpty(sequence)) {
		p = cb.and(p, cb.greaterThan(sequence_path, sequence));
	}
	cq.multiselect(id_path, job_path, sequence_path, title_path, startTime_path).where(p)
			.orderBy(cb.asc(sequence_path));
	List<Tuple> os = em.createQuery(cq).setMaxResults(200).getResultList();
	List<Read> list = new ArrayList<>();
	for (Tuple o : os) {
		Read read = new Read();
		read.setId(o.get(id_path));
		read.setJob(o.get(job_path));
		read.setSequence(o.get(sequence_path));
		read.setTitle(o.get(title_path));
		read.setStartTime(o.get(startTime_path));
		list.add(read);
	}
	return list;
}
 

private List<Task> listTask(EntityManagerContainer emc, Date threshold, String sequence) throws Exception {
	EntityManager em = emc.get(Task.class);
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<Tuple> cq = cb.createQuery(Tuple.class);
	Root<Task> root = cq.from(Task.class);
	Path<String> id_path = root.get(Task_.id);
	Path<String> job_path = root.get(Task_.job);
	Path<String> sequence_path = root.get(Task_.sequence);
	Path<String> title_path = root.get(Task_.title);
	Path<Date> startTime_path = root.get(Task_.startTime);
	Predicate p = cb.lessThan(startTime_path, threshold);
	if (StringUtils.isNotEmpty(sequence)) {
		p = cb.and(p, cb.greaterThan(sequence_path, sequence));
	}
	cq.multiselect(id_path, job_path, sequence_path, title_path, startTime_path).where(p)
			.orderBy(cb.asc(sequence_path));
	List<Tuple> os = em.createQuery(cq).setMaxResults(200).getResultList();
	List<Task> list = new ArrayList<>();
	for (Tuple o : os) {
		Task task = new Task();
		task.setId(o.get(id_path));
		task.setJob(o.get(job_path));
		task.setSequence(o.get(sequence_path));
		task.setTitle(o.get(title_path));
		task.setStartTime(o.get(startTime_path));
		list.add(task);
	}
	return list;
}
 

private List<Work> listWork(EntityManagerContainer emc, Date threshold, String sequence) throws Exception {
	EntityManager em = emc.get(Work.class);
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<Tuple> cq = cb.createQuery(Tuple.class);
	Root<Work> root = cq.from(Work.class);
	Path<String> id_path = root.get(Work_.id);
	Path<String> job_path = root.get(Work_.job);
	Path<String> sequence_path = root.get(Work_.sequence);
	Path<String> title_path = root.get(Work_.title);
	Path<Date> activityArrivedTime_path = root.get(Work_.activityArrivedTime);
	Predicate p = cb.lessThan(activityArrivedTime_path, threshold);
	if (StringUtils.isNotEmpty(sequence)) {
		p = cb.and(p, cb.greaterThan(sequence_path, sequence));
	}
	cq.multiselect(id_path, job_path, sequence_path, title_path, activityArrivedTime_path).where(p)
			.orderBy(cb.asc(sequence_path));
	List<Tuple> os = em.createQuery(cq).setMaxResults(200).getResultList();
	List<Work> list = new ArrayList<>();
	for (Tuple o : os) {
		Work work = new Work();
		work.setId(o.get(id_path));
		work.setJob(o.get(job_path));
		work.setSequence(o.get(sequence_path));
		work.setTitle(o.get(title_path));
		work.setStartTime(o.get(activityArrivedTime_path));
		list.add(work);
	}
	return list;
}
 

private Long countExpiredWork(Business business) throws Exception {
	EntityManager em = business.entityManagerContainer().get(Work.class);
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<Long> cq = cb.createQuery(Long.class);
	Root<Work> root = cq.from(Work.class);
	Predicate p = cb.lessThan(root.get(Work_.expireTime), new Date());
	cq.select(cb.count(root)).where(p);
	return em.createQuery(cq).getSingleResult();
}
 

@Override
public Predicate getPredicate(Root<PullRequest> root, CriteriaBuilder builder) {
	Path<Date> attribute = root.get(PullRequest.PROP_SUBMIT_DATE);
	if (operator == PullRequestQueryLexer.IsBefore)
		return builder.lessThan(attribute, date);
	else
		return builder.greaterThan(attribute, date);
}
 

private List<Message> listMessage(Business business) throws Exception {
	EntityManager em = business.entityManagerContainer().get(Message.class);
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<Message> cq = cb.createQuery(Message.class);
	Root<Message> root = cq.from(Message.class);
	Date limit = DateUtils.addDays(new Date(), -Config.communicate().clean().getKeep());
	Predicate p = cb.lessThan(root.get(Message_.createTime), limit);
	return em.createQuery(cq.select(root).where(p)).setMaxResults(2000).getResultList();
}
 

private Long countExpiredTask(Business business) throws Exception {
	EntityManager em = business.entityManagerContainer().get(Task.class);
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<Long> cq = cb.createQuery(Long.class);
	Root<Task> root = cq.from(Task.class);
	Predicate p = cb.lessThan(root.get(Task_.expireTime), new Date());
	cq.select(cb.count(root)).where(p);
	return em.createQuery(cq).getSingleResult();
}
 

@SuppressWarnings({ "rawtypes", "unchecked" })
public Predicate toPredicate(Root<?> root, CriteriaQuery<?> query, CriteriaBuilder builder) {
    Path expression = null;
    if (fieldName.contains(".")) {
        
        System.out.println(root);
        String[] names = StringUtils.split(fieldName, ".");
        expression = root.get(names[0]);
        for (int i = 1; i < names.length; i++) {
            expression = expression.get(names[i]);
        }
    } else {
        expression = root.get(fieldName);
    }

    switch (operator) {
    case EQ:
        return builder.equal(expression, value);
    case NE:
        return builder.notEqual(expression, value);
    case LIKE:
        return builder.like((Expression<String>) expression, "%" + value + "%");
    case RLIKE:
        return builder.like((Expression<String>) expression, value + "%");
    case LLIKE:
        return builder.like((Expression<String>) expression, value + "%");
    case LT:
        return builder.lessThan(expression, (Comparable) value);
    case GT:
        return builder.greaterThan(expression, (Comparable) value);
    case LTE:
        return builder.lessThanOrEqualTo(expression, (Comparable) value);
    case GTE:
        return builder.greaterThanOrEqualTo(expression, (Comparable) value);
    case ISNULL:
        return builder.isNull(expression);
    case NOTNULL:
        return builder.isNotNull(expression);
    default:
        return null;
    }
}