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

public String[] twoCriteria() {
    final Session session = HibernateUtil.getHibernateSession();
    final CriteriaBuilder cb = session.getCriteriaBuilder();
    final CriteriaQuery<Item> cr = cb.createQuery(Item.class);
    final Root<Item> root = cr.from(Item.class);
    Predicate[] predicates = new Predicate[2];
    predicates[0] = cb.isNull(root.get("itemDescription"));
    predicates[1] = cb.like(root.get("itemName"), "chair%");
    cr.select(root)
        .where(predicates);
    // cr.add(Restrictions.isNull("itemDescription"));
    // cr.add(Restrictions.like("itemName", "chair%"));
    Query<Item> query = session.createQuery(cr);
    final List<Item> notNullItemsList = query.getResultList();
    final String notNullDescItems[] = new String[notNullItemsList.size()];
    for (int i = 0; i < notNullItemsList.size(); i++) {
        notNullDescItems[i] = notNullItemsList.get(i)
            .getItemName();
    }
    session.close();
    return notNullDescItems;
}
 

/**
 * 根据分析时间来查询需要进行进展分析的工作ID列表
 * @param report_progress
 * @param count
 * @return
 * @throws Exception 
 */
//@MethodDescribe( "根据分析时间来查询需要进行进展分析的工作ID列表" )
public List<String> listIdsForNeedProgressAnalyse( String report_progress, int count ) throws Exception {
	if( report_progress == null || report_progress.isEmpty() ){
		throw new Exception( "report_progress is null." );
	}
	if( count == 0 ){
		throw new Exception( "count is 0." );
	}
	EntityManager em = this.entityManagerContainer().get( OkrWorkBaseInfo.class );
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery< String > cq = cb.createQuery( String.class );
	Root< OkrWorkBaseInfo > root = cq.from( OkrWorkBaseInfo.class );
	Predicate p = cb.isFalse( root.get( OkrWorkBaseInfo_.isCompleted ) );
	p = cb.and( p, cb.equal( root.get( OkrWorkBaseInfo_.workProcessStatus ), "执行中" ) );
	Predicate p1 = cb.isNull( root.get( OkrWorkBaseInfo_.progressAnalyseTime ) );		
	p1 = cb.or( p1, cb.notEqual( root.get( OkrWorkBaseInfo_.progressAnalyseTime ), report_progress ));		
	p = cb.and( p, p1 );
	cq.select( root.get( OkrWorkBaseInfo_.id ) );
	return em.createQuery( cq.where(p) ).setMaxResults( count ).getResultList();
}
 

/**
 * 根据分析时间来查询需要进行进展分析的工作ID列表
 * @param report_progress
 * @param count
 * @return
 * @throws Exception 
 */
//@MethodDescribe( "根据分析时间来查询需要进行进展分析的工作ID列表" )
public List<String> listIdsForNeedProgressAnalyse( String report_progress, int count ) throws Exception {
	if( report_progress == null || report_progress.isEmpty() ){
		throw new Exception( "report_progress is null." );
	}
	if( count == 0 ){
		throw new Exception( "count is 0." );
	}
	EntityManager em = this.entityManagerContainer().get( OkrWorkBaseInfo.class );
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery< String > cq = cb.createQuery( String.class );
	Root< OkrWorkBaseInfo > root = cq.from( OkrWorkBaseInfo.class );
	Predicate p = cb.isFalse( root.get( OkrWorkBaseInfo_.isCompleted ) );
	p = cb.and( p, cb.equal( root.get( OkrWorkBaseInfo_.workProcessStatus ), "执行中" ) );
	Predicate p1 = cb.isNull( root.get( OkrWorkBaseInfo_.progressAnalyseTime ) );		
	p1 = cb.or( p1, cb.notEqual( root.get( OkrWorkBaseInfo_.progressAnalyseTime ), report_progress ));		
	p = cb.and( p, p1 );
	cq.select( root.get( OkrWorkBaseInfo_.id ) );
	return em.createQuery( cq.where(p) ).setMaxResults( count ).getResultList();
}
 

/**
	 *
	 * @param subjectId
	 * @param showSubReply 是否平级显示所有的的回复, 如果为false则只显示第一层
	 * @return
	 * @throws Exception
	 */
	public Long countBySubjectId(String subjectId, Boolean showSubReply) throws Exception {
		EntityManager em = this.entityManagerContainer().get( BBSReplyInfo.class );
		CriteriaBuilder cb = em.getCriteriaBuilder();
		CriteriaQuery<Long> cq = cb.createQuery(Long.class);
		Root<BBSReplyInfo> root = cq.from( BBSReplyInfo.class);
		Predicate p = cb.equal( root.get( BBSReplyInfo_.subjectId ), subjectId );

		if( !showSubReply ){
			Predicate p_showSubReply = cb.isNull( root.get( BBSReplyInfo_.parentId ));
			p_showSubReply = cb.or( p_showSubReply, cb.equal( root.get( BBSReplyInfo_.parentId), ""));
			p = cb.and( p, p_showSubReply);
		}
		cq.select( cb.count( root ) );
//		System.out.println( ">>>>>SQL:" + em.createQuery(cq.where(p)).toString() );
		return em.createQuery(cq.where(p)).getSingleResult();
	}
 

/**
 *根据主题ID获取该主题所有的回复信息对象列表
 * @param subjectId
 * @param showSubReply 是否平级显示所有的的回复, 如果为false则只显示第一层
 * @param maxCount
 * @return
 * @throws Exception
 */
public List<BBSReplyInfo> listWithSubjectForPage(String subjectId, Boolean showSubReply, Integer maxCount, String orderType ) throws Exception {
	if( subjectId == null ){
		throw new Exception( "subjectId can not null." );
	}
	EntityManager em = this.entityManagerContainer().get( BBSReplyInfo.class );
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<BBSReplyInfo> cq = cb.createQuery( BBSReplyInfo.class );
	Root<BBSReplyInfo> root = cq.from( BBSReplyInfo.class );
	Predicate p = cb.equal( root.get( BBSReplyInfo_.subjectId ), subjectId );
	if( !showSubReply ){
		Predicate p_showSubReply = cb.isNull( root.get( BBSReplyInfo_.parentId ));
		p_showSubReply = cb.or( p_showSubReply, cb.equal( root.get( BBSReplyInfo_.parentId), ""));
		p = cb.and( p, p_showSubReply);
	}
	if( StringUtils.equalsIgnoreCase(orderType, "DESC")){
		cq.orderBy( cb.desc( root.get( BBSReplyInfo_.createTime ) ) );
	}else{
		cq.orderBy( cb.asc( root.get( BBSReplyInfo_.createTime ) ) );
	}
	if( maxCount == null ){
		return em.createQuery(cq.where(p)).setMaxResults( 2000 ).getResultList();
	}else{
		return em.createQuery(cq.where(p)).setMaxResults( maxCount ).getResultList();
	}
}
 

public void delete(String objectID, String additional, String type, String service) {
    Objects.requireNonNull(objectID, "objectId may not be null");
    Objects.requireNonNull(type, "type may not be null");
    Objects.requireNonNull(service, "service may not be null");
    EntityManager em = MCREntityManagerProvider.getCurrentEntityManager();
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<MCRPI> getQuery = cb.createQuery(MCRPI.class);
    Root<MCRPI> pi = getQuery.from(MCRPI.class);
    Predicate additionalPredicate = additional == null ? cb.isNull(pi.get(MCRPI_.additional))
        : cb.equal(pi.get(MCRPI_.additional), additional);
    em.remove(
        em.createQuery(
            getQuery
                .where(
                    cb.equal(pi.get(MCRPI_.mycoreID), objectID),
                    cb.equal(pi.get(MCRPI_.type), type),
                    additionalPredicate,
                    cb.equal(pi.get(MCRPI_.service), service)))
            .getSingleResult());
}
 

@Override
public Predicate toPredicate(Root<T> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
    From from = getRoot(property, root);
    String field = getProperty(property);
    if (values == null) {
        return cb.isNull(from.get(field));
    }
    if (values.length == 1) {
        return getPredicate(from, cb, values[0], field);
    }

    Predicate[] predicates = new Predicate[values.length];
    for (int i = 0; i < values.length; i++) {
        predicates[i] = getPredicate(root, cb, values[i], field);
    }
    return cb.or(predicates);
}
 

@Override
public Predicate toPredicate(Root<T> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
    if (converter.convert(expectedValue, Boolean.class)) {
        return cb.isNotNull(path(root));
    } else {
        return cb.isNull(path(root));
    }
}
 

private Predicate generatePredicate(Root<T> root, CriteriaBuilder criteriaBuilder, SpecificationOperator op) {
	/*
	 * 根据不同的操作符返回特定的查询
	 */
	if (SpecificationOperator.Operator.eq.name().equalsIgnoreCase(op.getOper())) {
		return criteriaBuilder.equal(root.get(op.getKey()), op.getValue());
	} else if (SpecificationOperator.Operator.ge.name().equalsIgnoreCase(op.getOper())) {
		return criteriaBuilder.ge(root.get(op.getKey()).as(Number.class), (Number) op.getValue());
	} else if (SpecificationOperator.Operator.le.name().equalsIgnoreCase(op.getOper())) {
		return criteriaBuilder.le(root.get(op.getKey()).as(Number.class), (Number) op.getValue());
	} else if (SpecificationOperator.Operator.gt.name().equalsIgnoreCase(op.getOper())) {
		return criteriaBuilder.gt(root.get(op.getKey()).as(Number.class), (Number) op.getValue());
	} else if (SpecificationOperator.Operator.lt.name().equalsIgnoreCase(op.getOper())) {
		return criteriaBuilder.lt(root.get(op.getKey()).as(Number.class), (Number) op.getValue());
	} else if (SpecificationOperator.Operator.likeAll.name().equalsIgnoreCase(op.getOper())) {
		return criteriaBuilder.like(root.get(op.getKey()).as(String.class), "%" + op.getValue() + "%");
	} else if (SpecificationOperator.Operator.likeL.name().equalsIgnoreCase(op.getOper())) {
		return criteriaBuilder.like(root.get(op.getKey()).as(String.class), op.getValue() + "%");
	} else if (SpecificationOperator.Operator.likeR.name().equalsIgnoreCase(op.getOper())) {
		return criteriaBuilder.like(root.get(op.getKey()).as(String.class), "%" + op.getValue());
	} else if (SpecificationOperator.Operator.isNull.name().equalsIgnoreCase(op.getOper())) {
		return criteriaBuilder.isNull(root.get(op.getKey()));
	} else if (SpecificationOperator.Operator.isNotNull.name().equalsIgnoreCase(op.getOper())) {
		return criteriaBuilder.isNotNull(root.get(op.getKey()));
	} else if (SpecificationOperator.Operator.notEqual.name().equalsIgnoreCase(op.getOper())) {
		return criteriaBuilder.notEqual(root.get(op.getKey()), op.getValue());
	}
	return null;
}
 

public List<String> listAppIdsWithOutAppType() throws Exception {
	EntityManager em = this.entityManagerContainer().get(AppInfo.class);
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<String> cq = cb.createQuery(String.class);
	Root<AppInfo> root = cq.from(AppInfo.class);
	Predicate p = cb.isNull( root.get(AppInfo_.appType) );
	p = cb.or( p, cb.equal( root.get(AppInfo_.appType), ""));
	p = cb.or( p, cb.equal( root.get(AppInfo_.appType), "未分类"));
	cq.select(root.get(AppInfo_.id));
	return em.createQuery(cq.where(p)).getResultList();
}
 

@Override
public Predicate toPredicate(Root<T> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
	if (converter.convert(expectedValue, Boolean.class)) {
		return cb.isNull(path(root));
	} else {
		return cb.isNotNull(path(root));
	}
}
 

@Override
public Predicate toPredicate(Root<T> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
	return cb.isNull(path(root));
}
 

public Page<ServiceOwner> queryServiceOwners(ServiceOwnerQuery serviceOwnerQuery, Pageable pageable) {
//        QServiceOwnerDO qServiceOwnerDO = QServiceOwnerDO.serviceOwnerDO;
//        Predicate predicate = null;
//        switch (queryRecords.getQueryItem()){
//            case ServiceOwnerQuery.QUERY_ITEM_BINDED:
//                predicate = qServiceOwnerDO.userId.isNotNull();
//            case ServiceOwnerQuery.QUERY_ITEM_UNBINDED:
//                predicate = qServiceOwnerDO.userId.isNotNull();
//        }
//        if(StringUtils.isNotEmpty(queryRecords.getServiceId())){
//
//        }

        Specification<ServiceOwnerDO> specification = new Specification<ServiceOwnerDO>() {

            @Override
            public Predicate toPredicate(Root<ServiceOwnerDO> root, CriteriaQuery<?> query, CriteriaBuilder cb) {

                List<Predicate> predicates = new ArrayList();

                switch (serviceOwnerQuery.getQueryItem()) {
                    case ServiceOwnerQuery.QUERY_ITEM_BINDED:
//                        predicates.add(cb.isNotNull(root.get("userId").as(String.class)));
                        Predicate p1 = cb.isNotNull(root.get("userId"));
                        Predicate p2 = cb.notEqual(root.get("userId").as(String.class), "");
                        predicates.add(cb.and(p1,p2));
                        break;
                    case ServiceOwnerQuery.QUERY_ITEM_UNBINDED:
                        Predicate unbindP1 = cb.isNull(root.get("userId").as(String.class));
                        Predicate unbindP2 = cb.equal(root.get("userId").as(String.class), "");
                        predicates.add(cb.or(unbindP1, unbindP2));
                        break;
                }
                if(StringUtils.isNotEmpty(serviceOwnerQuery.getServiceId())){
                    predicates.add(cb.equal(root.get("serviceId").as(String.class), serviceOwnerQuery.getServiceId()));
                }
                query.where(predicates.toArray(new Predicate[predicates.size()]));
                return query.getRestriction();
//                return cb.and(predicates.toArray(new Predicate[predicates.size()]));
            }
        };

        Page<ServiceOwnerDO> doPage = repository.findAll(specification, pageable);
        return PaginationUtils.convert(pageable, doPage, getModelMapper());
    }
 

@Override
public Predicate toPredicate(final Root<T> root, final CriteriaQuery<?> query, final CriteriaBuilder builder) {
    final List<Object> args = castArguments(root);
    final Object argument = args.get(0);
    switch (RsqlSearchOperation.getSimpleOperator(operator)) {

    case EQUAL: {
        if (argument instanceof String) {
            return builder.like(root.get(property), argument.toString().replace('*', '%'));
        } else if (argument == null) {
            return builder.isNull(root.get(property));
        } else {
            return builder.equal(root.get(property), argument);
        }
    }
    case NOT_EQUAL: {
        if (argument instanceof String) {
            return builder.notLike(root.<String> get(property), argument.toString().replace('*', '%'));
        } else if (argument == null) {
            return builder.isNotNull(root.get(property));
        } else {
            return builder.notEqual(root.get(property), argument);
        }
    }
    case GREATER_THAN: {
        return builder.greaterThan(root.<String> get(property), argument.toString());
    }
    case GREATER_THAN_OR_EQUAL: {
        return builder.greaterThanOrEqualTo(root.<String> get(property), argument.toString());
    }
    case LESS_THAN: {
        return builder.lessThan(root.<String> get(property), argument.toString());
    }
    case LESS_THAN_OR_EQUAL: {
        return builder.lessThanOrEqualTo(root.<String> get(property), argument.toString());
    }
    case IN:
        return root.get(property).in(args);
    case NOT_IN:
        return builder.not(root.get(property).in(args));
    }

    return null;
}
 

@Override
public Predicate toPredicate(Root<T> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
	return cb.isNull(root.<LocalDateTime>get(DELETED_FIELD));
}
 

public static <E, D extends Comparable<? super D>> Specification<E> toSpecification(final Range<E, D> range) {
    Validate.isTrue(range.isSet(), "You must pass an exploitable range");
    return new Specification<E>() {
        @Override
        public Predicate toPredicate(final Root<E> root, final CriteriaQuery<?> query, final CriteriaBuilder builder) {
            Predicate rangePredicate = null;

            if (range.isBetween()) {
                rangePredicate = builder.between(root.get(range.getField()), range.getFrom(), range.getTo());
            } else if (range.isFromSet()) {
                // rangePredicate =
                // builder.greaterThanOrEqualTo(root.get(range.getField()),
                // range.getFrom());
                rangePredicate = builder.greaterThan(root.get(range.getField()), range.getFrom());
            } else if (range.isToSet()) {
                // rangePredicate =
                // builder.lessThanOrEqualTo(root.get(range.getField()),
                // range.getTo());
                rangePredicate = builder.lessThan(root.get(range.getField()), range.getTo());
            }

            if (rangePredicate != null) {
                if (!range.isIncludeNullSet() || Boolean.FALSE.equals(range.getIncludeNull())) {
                    return rangePredicate;
                } else {
                    return builder.or(rangePredicate, builder.isNull(root.get(range.getField())));
                }
            }

            // no range at all
            // take the opportunity to keep only null...
            if (Boolean.TRUE.equals(range.getIncludeNull())) {
                return builder.isNull(root.get(range.getField()));
            }

            // ... or non-null only...
            if (Boolean.FALSE.equals(range.getIncludeNull())) {
                return builder.isNotNull(root.get(range.getField()));
            }

            throw new IllegalStateException("You must pass an exploitable range (should not happen here)");
        }
    };
}
 

@Override
public Predicate toPredicate(Root<T> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
	return parent == null ? cb.isNull(root.get("parent")) : cb.equal(root.get("parent"), parent);
}
 

@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);
    case IN:
        return builder.in(expression);
    default:
        return null;
    }
}
 

@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;
    }
}
 

@Override
public Predicate getPredicate(Root<Build> root, CriteriaBuilder builder) {
	return builder.isNull(root.get(Build.PROP_VERSION));
}