Java 类javax.persistence.criteria.Root 实例源码

public static Specification<User> byKeyword(String keyword, String role, String active){
    return (Root<User> root, CriteriaQuery<?> query, CriteriaBuilder cb) -> {

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

        if (StringUtils.hasText(keyword)) {
            predicates.add(
                cb.or(
                    cb.like(root.get(User_.email), "%" + keyword + "%"),
                    cb.like(root.get(User_.username), "%" + keyword + "%")
                ));
        }

        if (StringUtils.hasText(role) && !"ALL".equals(role)) {

            ListJoin<User, String> roleJoin = root.join(User_.roles);
            predicates.add(cb.equal(roleJoin, role));
        }
        if (StringUtils.hasText(active)) {
            predicates.add(cb.equal(root.get(User_.active), Boolean.valueOf(active)));
        }
        return cb.and(predicates.toArray(new Predicate[predicates.size()]));
    };
}

public List<PlayerDetails> getPlayersBySport(String sport) {
    logger.info("getPlayersByLeagueId");
    List<Player> players = null;

    try {
        CriteriaQuery<Player> cq = cb.createQuery(Player.class);
        if (cq != null) {
            Root<Player> player = cq.from(Player.class);
            Join<Player, Team> team = player.join(Player_.teams);
            Join<Team, League> league = team.join(Team_.league);

            // Get MetaModel from Root
            //EntityType<Player> Player_ = player.getModel();

            // set the where clause
            cq.where(cb.equal(league.get(League_.sport), sport));
            cq.select(player).distinct(true);
            TypedQuery<Player> q = em.createQuery(cq);
            players = q.getResultList();
        }
        return copyPlayersToDetails(players);
    } catch (Exception ex) {
        throw new EJBException(ex);
    }
}

@Override
public Predicate toPredicate(Root<TestSuite> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
    Predicate result = null;

    if (StringUtils.isNotEmpty(filter.getSuite())) {
        result = criteriaBuilder.like(criteriaBuilder.lower(root.get(SUITE_PROPERTY)),
                buildLikeValue(filter.getSuite()));
    }

    return result;
}

public static Specification<Post> filterByKeywordAndStatus(
    final String keyword,//
    final Post.Status status) {
    return (Root<Post> root, CriteriaQuery<?> query, CriteriaBuilder cb) -> {
        List<Predicate> predicates = new ArrayList<>();
        if (StringUtils.hasText(keyword)) {
            predicates.add(
                cb.or(
                    cb.like(root.get(Post_.title), "%" + keyword + "%"),
                    cb.like(root.get(Post_.content), "%" + keyword + "%")
                )
            );
        }

        if (status != null) {
            predicates.add(cb.equal(root.get(Post_.status), status));
        }

        return cb.and(predicates.toArray(new Predicate[predicates.size()]));
    };
}

public static DeploymentSpec findDeploymentSpecByVirtualSystemProjectAndRegion(EntityManager em, VirtualSystem vs,
        String projectId, String region) {

    CriteriaBuilder cb = em.getCriteriaBuilder();

    CriteriaQuery<DeploymentSpec> query = cb.createQuery(DeploymentSpec.class);

    Root<DeploymentSpec> root = query.from(DeploymentSpec.class);

    query = query.select(root)
            .where(cb.equal(root.get("projectId"), projectId),
                    cb.equal(root.get("region"), region),
                    cb.equal(root.get("virtualSystem"), vs));

    try {
        return em.createQuery(query).getSingleResult();
    } catch (NoResultException nre) {
        return null;
    }
}

@Override
public Page search(QueryCriteria criteria, Pageable pageable) {
    CriteriaBuilder builder = this.entityManager.getCriteriaBuilder();

    CriteriaQuery<Long> countCriteria = builder.createQuery(Long.class);
    Root<T> countRoot = countCriteria.from(this.domainClass);
    long total = this.entityManager.createQuery(
            countCriteria.select(builder.count(countRoot))
                    .where(toPredicates(criteria, countRoot, builder))
    ).getSingleResult();

    CriteriaQuery<T> pageCriteria = builder.createQuery(this.domainClass);
    Root<T> pageRoot = pageCriteria.from(this.domainClass);
    List<T> list = this.entityManager.createQuery(
            pageCriteria.select(pageRoot)
                    .where(toPredicates(criteria, pageRoot, builder))
                    .orderBy(toOrders(pageable.getSort(), pageRoot, builder))
    ).setFirstResult(pageable.getOffset())
            .setMaxResults(pageable.getPageSize())
            .getResultList();

    return new PageImpl<>(new ArrayList<>(list), pageable, total);
}

/**
 * Spec op query doen.
 * @param spec spec
 * @param query query
 * @param <S> query type
 * @return root
 */
protected final <S> Root<T> applySpecificationToQueryCriteria(final Specification<T> spec, final CriteriaQuery<S> query) {

    Assert.notNull(query, "Query mag niet null zijn.");
    final Root<T> root = query.from(getDomainClass());

    if (spec == null) {
        return root;
    }

    final CriteriaBuilder builder = em.getCriteriaBuilder();
    final Predicate predicate = spec.toPredicate(root, query, builder);

    if (predicate != null) {
        query.where(predicate);
    }

    return root;
}

private Predicate getPredicateGlobalSearch(final Root<T> root, final CriteriaQuery<?> query, final CriteriaBuilder cb,
    Predicate predicate) {
    // check whether a global filter value exists
    final String globalFilterValue = input.getSearch().getValue();
    if (globalFilterValue != null && globalFilterValue.trim().length() > 0) {
        LOGGER.fine("filtre global: {"+ globalFilterValue+"}");

        Predicate matchOneColumnPredicate = cb.disjunction();
        // add a 'WHERE .. LIKE' clause on each searchable column
        for (final DatatableColumn column : input.getColumns()) {
            if (column.getSearchable()) {
                LOGGER.log(Level.FINE, "filtre global pour colonne: {}", column);
                final Expression<String> expression = DatatableHelper.getExpression(root, column.getData(), String.class);

                matchOneColumnPredicate = cb.or(matchOneColumnPredicate,
                    cb.like(cb.lower(expression), getLikeFilterValue(globalFilterValue), ESCAPE_CHAR));
            }
        }
        predicate = cb.and(predicate, matchOneColumnPredicate);
    }
    return predicate;
}

@GET
@Produces({"application/xml", "application/json"})
@Path("/recent/region/producttype/{regionName}/{productTypeId}/{orderLineId}")
public List<LiveSalesList> findRecentRegionProductTypeFrom(@PathParam("regionName") String regionName, @PathParam("productTypeId") Integer productTypeId, @PathParam("orderLineId") Integer orderLineId) {
    CriteriaBuilder cb = getEntityManager().getCriteriaBuilder();
    javax.persistence.criteria.CriteriaQuery cq = cb.createQuery();
    Root<LiveSalesList> liveSalesList = cq.from(LiveSalesList.class);
    cq.select(liveSalesList);
    cq.where(cb.and(
        cb.equal(liveSalesList.get(LiveSalesList_.productTypeId), productTypeId),
        cb.equal(liveSalesList.get(LiveSalesList_.region), regionName),
        cb.gt(liveSalesList.get(LiveSalesList_.orderLineId), orderLineId)
    ));
    Query q = getEntityManager().createQuery(cq);
    q.setMaxResults(500);
    return q.getResultList();
}

private TypedQuery<ContextExtension> createQuery(String processId, String name, EntityManager manager) {
    CriteriaBuilder builder = manager.getCriteriaBuilder();
    CriteriaQuery<ContextExtension> query = builder.createQuery(ContextExtension.class);
    Root<ContextExtension> root = query.from(ContextExtension.class);

    List<Predicate> predicates = new ArrayList<>();
    if (processId != null) {
        predicates.add(builder.equal(root.get(ContextExtension.FieldNames.PROCESS_ID),
            builder.parameter(String.class, ContextExtension.FieldNames.PROCESS_ID)));
    }
    if (name != null) {
        predicates.add(builder.equal(root.get(ContextExtension.FieldNames.NAME),
            builder.parameter(String.class, ContextExtension.FieldNames.NAME)));
    }

    return manager.createQuery(query.select(root).where(predicates.toArray(new Predicate[0])));

}

@GET
@Produces({"application/xml", "application/json"})
@Path("/recent/producttype/{id}/{orderLineId}")
public List<LiveSalesList> findRecentProductTypeFrom(@PathParam("id") Integer productTypeId, @PathParam("orderLineId") Integer orderLineId) {
    CriteriaBuilder cb = getEntityManager().getCriteriaBuilder();
    javax.persistence.criteria.CriteriaQuery cq = cb.createQuery();
    Root<LiveSalesList> liveSalesList = cq.from(LiveSalesList.class);
    cq.select(liveSalesList);
    cq.where(cb.and(
        cb.equal(liveSalesList.get(LiveSalesList_.productTypeId), productTypeId),
        cb.gt(liveSalesList.get(LiveSalesList_.orderLineId), orderLineId)
    ));
    Query q = getEntityManager().createQuery(cq);
    q.setMaxResults(500);
    return q.getResultList();
}

private <S> S aggregate(CriteriaBuilder builder, CriteriaQuery<S> query, Root<E> root, Specification<E> spec, List<Selection<?>> selectionList, LockModeType lockMode) {
    if (selectionList != null) {
        Predicate predicate = spec.toPredicate(root, query, builder);
        if (predicate != null) {
            query.where(predicate);
        }
        query.multiselect(selectionList);
        return (S) em.createQuery(query).setLockMode(lockMode).getSingleResult();
    }
    return null;
}

@Override
public Predicate toPredicate(Root<TestRun> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
    Predicate result = null;

    if (!CollectionUtils.isEmpty(filter.getBuilds())) {
        result = criteriaBuilder.in(root.get(BUILD_PROPERTY)).value(filter.getBuilds());
    }

    if (!CollectionUtils.isEmpty(filter.getTestSuites())) {
        result = and(criteriaBuilder, result,
                criteriaBuilder.in(root.get("testSuite").get("id")).value(filter.getTestSuites()));
    }

    if (!CollectionUtils.isEmpty(filter.getGit())) {
        result = and(criteriaBuilder, result,
                criteriaBuilder.or(
                        criteriaBuilder.in(root.get("gitHash")).value(filter.getGit()),
                        criteriaBuilder.in(root.get("gitBranch")).value(filter.getGit())
                ));
    }

    result = and(criteriaBuilder, result, criteriaBuilder.isNotNull(root.get("testSuite")));

    return result;
}

public static User getUser(String username) {
    EntityManagerFactory emf = Persistence.createEntityManagerFactory("userData");
    EntityManager em = emf.createEntityManager();
    EntityTransaction tx = em.getTransaction();
    tx.begin();
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<User> q = cb.createQuery(User.class);
    Root<User> c = q.from(User.class);
    q.select(c).where(cb.equal(c.get("username"), username));
    TypedQuery<User> query = em.createQuery(q);
    List<User> users = query.getResultList();
    em.close();
    LOGGER.info("found " + users.size() + " users with username " + username);
    if (users.size() == 1)
        return users.get(0);
    else
        return null;
}

@SuppressWarnings("unchecked")
public static <T, N extends Number> Expression<N> getExpression(CriteriaBuilder cb, Root<T> root, String input) {
    StringTokenizer tokenizer = new StringTokenizer(input, "+-*/", true);
    Expression<N> expr = getPath(root, tokenizer.nextToken());
    if (tokenizer.hasMoreTokens()) {
        String op = tokenizer.nextToken();
        String name = tokenizer.nextToken();
        Expression<N> expr2 = getPath(root, name);
        if ("+".equals(op)) {
            expr = cb.sum(expr, expr2);
        } else if ("-".equals(op)) {
            expr = cb.diff(expr, expr2);
        } else if ("*".equals(op)) {
            expr = cb.prod(expr, expr2);
        } else if ("/".equals(op)) {
            expr = (Expression<N>) cb.quot(expr, expr2);
        }
    }
    return expr;
}

/**
 * Obtem lista com os resultados
 * @return Lista de resultados
 */
public List<T> getResults() {
    CriteriaQuery<T> criteriaQuery = criteriaBuilder.createQuery(entityClass);
    Root<T> root                   = criteriaQuery.from(entityClass);
    setupQuery(criteriaQuery, root);

    // ORDER BY
    if ( ! orders.isEmpty() ) {
        ArrayList<Order> jpaOrders = new ArrayList<>();
        for (OrderEntry orderField : orders) {
            if ( orderField.order.equals(OrderDirection.ASC) ) {
                jpaOrders.add( criteriaBuilder.asc(getPath(orderField.fieldNames, root)));
            } else {
                jpaOrders.add( criteriaBuilder.desc(getPath(orderField.fieldNames, root)));
            }
        }
        criteriaQuery.orderBy( jpaOrders );
    }

    if ( pageNumber != null ) {
        return em.createQuery(criteriaQuery).setFirstResult( (pageNumber - 1) * pageSize ).setMaxResults(pageSize)
                        .getResultList();
    } else {
        return em.createQuery(criteriaQuery).getResultList();
    }
}

/**
 * Returns a page of objects.
 *
 * @param entityClass The entity class.
 * @param page The page index (zero indexed).
 * @param pageSize The page size.
 * @return A page of objects.
 */
@Override
public <T extends BaseEntity> List<T> readPage(
        final Class<T> entityClass,
        final int page,
        final int pageSize) {

    final CriteriaBuilder cb = em.getCriteriaBuilder();
    final CriteriaQuery<T> cq = cb.createQuery(entityClass);
    final Root<T> root = cq.from(entityClass);
    cq.select(root);
    cq.orderBy(cb.desc(root.get("id")));
    return em.createQuery(cq)
            .setFirstResult(page * pageSize)
            .setMaxResults(pageSize)
            .getResultList();
}

public List<PlayerDetails> getPlayersByPosition(String position) {
    logger.info("getPlayersByPosition");
    List<Player> players = null;

    try {
        CriteriaQuery<Player> cq = cb.createQuery(Player.class);
        if (cq != null) {
            Root<Player> player = cq.from(Player.class);

            // Get MetaModel from Root
            //EntityType<Player> Player_ = player.getModel();

            // set the where clause
            cq.where(cb.equal(player.get(Player_.position), position));
            cq.select(player);
            TypedQuery<Player> q = em.createQuery(cq);
            players = q.getResultList();
        }
        return copyPlayersToDetails(players);
    } catch (Exception ex) {
        throw new EJBException(ex);
    }
}

public List<PlayerDetails> getPlayersNotOnTeam() {
    logger.info("getPlayersNotOnTeam");
    List<Player> players = null;

    try {
        CriteriaQuery<Player> cq = cb.createQuery(Player.class);
        if (cq != null) {
            Root<Player> player = cq.from(Player.class);

            // Get MetaModel from Root
            //EntityType<Player> Player_ = player.getModel();

            // set the where clause
            cq.where(cb.isEmpty(player.get(Player_.teams)));
            cq.select(player).distinct(true);
            TypedQuery<Player> q = em.createQuery(cq);
            players = q.getResultList();
        }
        return copyPlayersToDetails(players);
    } catch (Exception ex) {
        throw new EJBException(ex);
    }
}

@Override
public List<Community> findApprovedCommunities() {
    LOG.info("findApprovedCommunites()");
    CriteriaBuilder builder = em.getCriteriaBuilder();
    CriteriaQuery<Community> criteria = builder.createQuery(Community.class);
    Root<Community> community = criteria.from(Community.class);
    try {
        criteria.where(builder.equal(community.get("state"), new Enumeration(2)));
    } catch (DatabaseException e1) {
        LOG.error("Enumeration(2).APPROVED konnte nicht erstellt werden!");
    }
    TypedQuery<Community> query = em.createQuery(criteria);
    try {
        List <Community> coms = query.getResultList();
        for(Community c : coms) {
            initializeCom(c);
        }
        return coms;
    } catch (NoResultException e) {
        LOG.error(e.toString());
        return null;
    }
}

@Override
public Community findByName(String name) {
    LOG.info("findByName(name = " + name + ")");
    CriteriaBuilder builder = em.getCriteriaBuilder();
    CriteriaQuery<Community> criteria = builder.createQuery(Community.class);
    Root<Community> community = criteria.from(Community.class);
    criteria.where(builder.equal(community.get("name"), name));
    TypedQuery<Community> query = em.createQuery(criteria);
    try {
        Community c = query.getSingleResult();
        return initializeCom(c);
    } catch (NoResultException e) {
        LOG.error(e.toString());
        return null;
    }
}

private DeviceMemberEntity getDeviceMember(Long deviceId, Long memberId, String memName) {
    CriteriaBuilder criteriaBuilder = IsmDeviceApi.this.em.getCriteriaBuilder();
    CriteriaQuery<DeviceMemberEntity> query = criteriaBuilder.createQuery(DeviceMemberEntity.class);
    Root<DeviceMemberEntity> r = query.from(DeviceMemberEntity.class);

    if (memName != null) {
        query.select(r)
        .where(criteriaBuilder.and((criteriaBuilder.equal(r.get("device").get("id"), deviceId)),
                (criteriaBuilder.equal(r.get("name"), memName))));
    } else {
        query.select(r).where(criteriaBuilder.and((criteriaBuilder.equal(r.get("device").get("id"), deviceId)),
                criteriaBuilder.equal(r.get("id"), memberId)));
    }

    List<DeviceMemberEntity> result = IsmDeviceApi.this.em.createQuery(query).getResultList();

    return result.isEmpty() == true ? null : result.get(0);
}

private SecurityGroupEntity findSecurityGroupByName(final String name, DeviceEntity device) throws Exception {

        return this.txControl.supports(() -> {
            CriteriaBuilder cb = IsmSecurityGroupApi.this.em.getCriteriaBuilder();
            CriteriaQuery<SecurityGroupEntity> query = cb.createQuery(SecurityGroupEntity.class);
            Root<SecurityGroupEntity> root = query.from(SecurityGroupEntity.class);

            query.select(root).where(cb.equal(root.get("name"), name), cb.equal(root.get("device"), device));

            SecurityGroupEntity result = null;
            try {
                result = IsmSecurityGroupApi.this.em.createQuery(query).getSingleResult();
            } catch (NoResultException e) {
                LOG.error(
                        String.format("Cannot find Security group with name %s under device %s", name, device.getId()));
            }
            return result;
        });
    }

private SecurityGroupInterfaceEntity getSecurityGroupInterfaceById(String id, DeviceEntity device)
        throws Exception {
    return this.txControl.supports(() -> {
        CriteriaBuilder cb = IsmSecurityGroupInterfaceApi.this.em.getCriteriaBuilder();
        CriteriaQuery<SecurityGroupInterfaceEntity> query = cb.createQuery(SecurityGroupInterfaceEntity.class);
        Root<SecurityGroupInterfaceEntity> root = query.from(SecurityGroupInterfaceEntity.class);

        query.select(root).where(cb.equal(root.get("id"), Long.valueOf(id)), cb.equal(root.get("device"), device));

        SecurityGroupInterfaceEntity result = null;
        try {
            result = IsmSecurityGroupInterfaceApi.this.em.createQuery(query).getSingleResult();
        } catch (NoResultException e) {
            LOG.error(String.format("Cannot find Security group interface with id %s under device %s", id,
                    device.getId()));
        }
        return result;
    });
}

public void updateTask(final ToDoList pToDoList) {

       final CriteriaBuilder lCriteriaBuilder = entityManager.getCriteriaBuilder();

       //Creation de la requête d'update
       final CriteriaUpdate<ToDoList> lCriteriaUpdate = lCriteriaBuilder.createCriteriaUpdate(ToDoList.class);
       final Root<ToDoList> lRoot = lCriteriaUpdate.from(ToDoList.class);
       final Path<ToDoList> lPath = lRoot.get("id");
       //On utilise la variable pToDoList transmise en parametre de la methode
       final Expression<Boolean> lExpression = lCriteriaBuilder.equal(lPath, pToDoList.getId());
       lCriteriaUpdate.where(lExpression);
       lCriteriaUpdate.set("libelle", pToDoList.getLibelle());

       final Query lQuery = entityManager.createQuery(lCriteriaUpdate);
       final int lRowCount = lQuery.executeUpdate();

       //Si la requête modifie un nombre d'occurrences différent de 1 > erreur
       //Sinon update fait.
       if (lRowCount != 1) {
           final org.hibernate.Query lHQuery = lQuery.unwrap(org.hibernate.Query.class);
           final String lSql = lHQuery.getQueryString();
           throw new RuntimeException("Nombre d'occurences (" + lRowCount + 
                   ") modifiés différent de 1 pour " + lSql);
       }

}

@GET
@Path("email/{guardianEmail}")
@Produces({"application/xml", "application/json"})
@RolesAllowed({"Guardian", "Administrator"})
public List<Student> getStatusByGuardianEmail(@PathParam("guardianEmail") String email) {
    logger.log(Level.INFO, "Principal is: {0}", ctx.getCallerPrincipal().getName());
    CriteriaQuery<Student> cq = cb.createQuery(Student.class);
    Root<Student> student = cq.from(Student.class);
    Join<Student, Guardian> guardian = student.join(Student_.guardians);

    cq.select(student);
    cq.where(cb.equal(guardian.get(Guardian_.email), email));
    cq.distinct(true);

    TypedQuery<Student> q = em.createQuery(cq);
    List<Student> results = q.getResultList();
    logger.log(Level.INFO, "Guardian {0}has {1} students.", new Object[]{email, results.size()});
    return results;
}

@Override
public Predicate toPredicate(Root<User> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder cb) {
    Predicate resultPredicate = null;
    List<Predicate> predicates = buildPredicates(root, cb);
    if (!predicates.isEmpty()) {
        resultPredicate = cb.and(predicates.toArray(new Predicate[predicates.size()]));
    }

    return resultPredicate;
}

private List<Predicate> buildPredicates(Root<User> root, CriteriaBuilder cb) {
    List<Predicate> predicates = new ArrayList<>();
    conditions.forEach((k, v) -> {
        if (Optional.ofNullable(v).isPresent()) {
            predicates.add(cb.like(root.get(k), "%" + String.valueOf(v.toString()) + "%"));
        }
    });
    return predicates;
}

public default long count() {
    EntityManager entityManager = getEntityManager();
    CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();

    CriteriaQuery criteriaQuery = criteriaBuilder.createQuery();
    Root<E> root = criteriaQuery.from(getEntityClass());
    criteriaQuery.select(criteriaBuilder.count(root));
    TypedQuery<Long> query = entityManager.createQuery(criteriaQuery);

    return query.getSingleResult();
}

public List<Address> getAllAddresses() {
    CriteriaQuery<Address> cq = em.getCriteriaBuilder().createQuery(Address.class);
    Root<Address> address = cq.from(Address.class);
    cq.select(address);
    cq.where(cb.isTrue(address.get(Address_.active)));
    cq.distinct(true);
    TypedQuery<Address> q = em.createQuery(cq);
    return q.getResultList();
}

/**
 * Generate a criteria query predicate for a where clause based on the given parameters.
 *
 * @param root The root to use
 * @param cb The criteria builder to use
 * @param movie The MovieEntity object
 * @param field The movie field
 * @param status The contribution status
 * @param fromDate Creation date range "from"
 * @param toDate Creation date range "to"
 * @return The specification
 */
public static Predicate getFindPredicate(
        final Root<ContributionEntity> root,
        final CriteriaBuilder cb,
        @Nullable final MovieEntity movie,
        @Nullable final MovieField field,
        @Nullable final DataStatus status,
        @Nullable final Date fromDate,
        @Nullable final Date toDate
) {
    final List<Predicate> predicates = new ArrayList<>();

    if (Optional.ofNullable(movie).isPresent()) {
        predicates.add(cb.equal(root.get(ContributionEntity_.movie), movie));
    }
    if (Optional.ofNullable(field).isPresent()) {
        predicates.add(cb.equal(root.get(ContributionEntity_.field), field));
    }
    if (Optional.ofNullable(status).isPresent()) {
        predicates.add(cb.equal(root.get(ContributionEntity_.status), status));
    }
    if (fromDate != null) {
        predicates.add(cb.greaterThanOrEqualTo(root.get(ContributionEntity_.created), fromDate));
    }
    if (toDate != null) {
        predicates.add(cb.lessThanOrEqualTo(root.get(ContributionEntity_.created), toDate));
    }

    return cb.and(predicates.toArray(new Predicate[predicates.size()]));
}

private Predicate getPredicateColonnes(final Root<T> root, final CriteriaQuery<?> query, final CriteriaBuilder cb,
    Predicate predicate) {
    // check for each searchable column whether a filter value exists
    for (final DatatableColumn column : input.getColumns()) {
        final boolean isColumnSearchable =
            column.getSearchable() && column.getSearch() != null
                && hasText(column.getSearch().getValue());
        if (!isColumnSearchable) {
            continue;
        }

        LOGGER.fine("colonne a filtrer: {"+ column+"}");

        // the filter contains only one value, add a 'WHERE .. LIKE'
        // clause
        if (isBoolean(column.getSearch().getValue())) {
            final Expression<Boolean> booleanExpression = DatatableHelper.getExpression(root, column.getData(), Boolean.class);
            predicate =
                cb.and(predicate, cb.equal(booleanExpression, Boolean.valueOf(column.getSearch().getValue())));
        } else {
            final Expression<String> stringExpression = DatatableHelper.getExpression(root, column.getData(), String.class);
            predicate = cb.and(predicate, cb.like(cb.lower(stringExpression),
                getLikeFilterValue(column.getSearch().getValue()), ESCAPE_CHAR));
        }

    }
    return predicate;
}

protected List<T> findAll(Class<T> clazz) {
    CriteriaBuilder cb = getEntityManager().getCriteriaBuilder();
    CriteriaQuery<T> cq = cb.createQuery(clazz);
    Root<T> rootEntry = cq.from(clazz);
    CriteriaQuery<T> all = cq.select(rootEntry);
    TypedQuery<T> allQuery = getEntityManager().createQuery(all);
    return allQuery.getResultList();
}

public List<StatusEntry> getStatusEntriesByDate(Date date) {
    Calendar cal = Calendar.getInstance();
    cal.setTime(date);
    CriteriaQuery<StatusEntry> cq = cb.createQuery(StatusEntry.class);
    Root<StatusEntry> statusEntry = cq.from(StatusEntry.class);

    cq.select(statusEntry);
    cq.where(cb.equal(statusEntry.get(StatusEntry_.statusDate), cal));
    cq.distinct(true);

    TypedQuery<StatusEntry> q = em.createQuery(cq);
    return q.getResultList();
}

@GET
@Produces({"application/xml", "application/json"})
@Path("/recent/producttype/{id}")
public List<LiveSalesList> findRecentProductType(@PathParam("id") Integer productTypeId) {
    CriteriaBuilder cb = getEntityManager().getCriteriaBuilder();
    javax.persistence.criteria.CriteriaQuery cq = cb.createQuery();
    Root<LiveSalesList> liveSalesList = cq.from(LiveSalesList.class);
    cq.select(liveSalesList);
    cq.where(cb.equal(liveSalesList.get(LiveSalesList_.productTypeId), productTypeId));
    Query q = getEntityManager().createQuery(cq);
    q.setMaxResults(500);
    return q.getResultList();
}

public static List<SecurityGroup> listByProtectAllAndProjectId(EntityManager em, String projectId) {
    CriteriaBuilder cb = em.getCriteriaBuilder();

    CriteriaQuery<SecurityGroup> query = cb.createQuery(SecurityGroup.class);

    Root<SecurityGroup> root = query.from(SecurityGroup.class);
    query = query.select(root)
            .distinct(true)
            .where(cb.and(
                    cb.equal(root.get("projectId"), projectId),
                    cb.equal(root.get("protectAll"), true)));

    return em.createQuery(query).getResultList();
}

@Override
public ComputationLog findLog(DateTime timestamp) throws DatabaseException {
    // find log by time stamp
    CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    CriteriaQuery<ComputationLog> c = cb.createQuery(ComputationLog.class);
    Root<ComputationLog> log = c.from(ComputationLog.class);
    c.where(cb.equal(log.get(ComputationLog_.timestamp), timestamp));
    TypedQuery<ComputationLog> query = entityManager.createQuery(c);

    try {
        return query.getSingleResult();
    } catch (RuntimeException e) {
        throw new DatabaseException(e.getMessage());
    }
}

private ValueSetEntity findValueSetEntity(IdType theId) {

        ValueSetEntity valueSetEntity = null;
        // Only look up if the id is numeric else need to do a search
        if (daoutils.isNumeric(theId.getValue())) {
            valueSetEntity =(ValueSetEntity) em.find(ValueSetEntity.class, theId.getValue());
        }

        // if null try a search on strId
        if (valueSetEntity == null)
        {
            CriteriaBuilder builder = em.getCriteriaBuilder();

            CriteriaQuery<ValueSetEntity> criteria = builder.createQuery(ValueSetEntity.class);
            Root<ValueSetEntity> root = criteria.from(ValueSetEntity.class);
            List<Predicate> predList = new LinkedList<Predicate>();
            Predicate p = builder.equal(root.<String>get("strId"),theId.getValue());
            predList.add(p);
            Predicate[] predArray = new Predicate[predList.size()];
            predList.toArray(predArray);
            if (predList.size()>0)
            {
                criteria.select(root).where(predArray);

                List<ValueSetEntity> qryResults = em.createQuery(criteria).getResultList();

                for (ValueSetEntity cme : qryResults)
                {
                    valueSetEntity = cme;
                    break;
                }
            }
        }
        return valueSetEntity;
    }

/**
 * Cree une expression Criteria API avec l'atribut de l'entité passé en parametre
 * 
 * @param root
 *            entité JPA contenant le champ
 * @param columnData
 *            nom du champ
 * @param clazz
 *            class du champ
 * @param <S>
 *            type du champ
 * @return l'expression de l'atribut
 */
public static <S> Path<S> getExpression(final Root<?> root, final String columnData, final Class<S> clazz) {
    if (!columnData.contains(DatatableSpecification.ATTRIBUTE_SEPARATOR)) {
        // columnData is like "attribute" so nothing particular to do
        return root.get(columnData);
    }
    // columnData is like "joinedEntity.attribute" so add a join clause
    final String[] values = columnData.split(DatatableSpecification.ESCAPED_ATTRIBUTE_SEPARATOR);
    final Attribute<?, ?> attribute = root.getModel().getAttribute(values[0]);
    if (attribute == null) {
        throw new IllegalArgumentException(
            "Colonne '" + values[0] + "' (" + columnData + ") introuvable depuis l'entité '" + root.getJavaType()
                + "'");
    }
    if (attribute.getPersistentAttributeType() == PersistentAttributeType.EMBEDDED) {
        // with @Embedded attribute
        return root.get(values[0]).get(values[1]);
    }
    From<?, ?> from = root;
    for (int i = 0; i < values.length - 1; i++) {

        Join<?, ?> join = null;
        for (final Join<?, ?> joinCandidate : from.getJoins()) {
            if (joinCandidate.getAttribute().getName().equals(values[i])) {
                // LOGGER.debug("Trouve joint d'entite: '{}'", values[i]);
                join = joinCandidate;
            }
        }
        if (join == null) {
            // LOGGER.debug("Joigant entite '{}'...", values[i]);
            join = from.join(values[i], JoinType.INNER);
        }
        from = join;
    }
    return from.get(values[values.length - 1]);
}

@Override
public Predicate toPredicate(final Root<?> administratieveHandelingRoot, final CriteriaQuery<?> query, final CriteriaBuilder cb) {
    final Root<PersoonAfgeleidAdministratiefHistorie> persoonHistoryRoot = query.from(PersoonAfgeleidAdministratiefHistorie.class);

    // Join persoon
    final Predicate joinPredicate = administratieveHandelingRoot.get(ID).in(persoonHistoryRoot.get(ADMINISTRATIEVE_HANDELING).get(ID));

    // Bsn
    final Predicate bsnPredicate = cb.equal(persoonHistoryRoot.get(PERSOON).get(BURGERSERVICE_NUMMER), value);

    return cb.and(bsnPredicate, joinPredicate);
}