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

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

public static List<SecurityGroup> listByNetworkId(EntityManager em, String sgId, String networkId) {

        // get Network or VM from port ID then Verify SGM ID and get GD ID...

        CriteriaBuilder cb = em.getCriteriaBuilder();

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

        Root<SecurityGroup> root = query.from(SecurityGroup.class);
        Join<SecurityGroup, Object> join = root.join("securityGroupMembers");
        query = query.select(root)
                .distinct(true)
                .where(cb.and(
                        cb.equal(root.get("id"), sgId),
                        cb.equal(join.get("network").get("openstackId"), networkId)));

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

public static void main(String[] args) {
    EntityManagerFactory entityManagerFactory = Persistence
            .createEntityManagerFactory("pl.edu.bogdan.training.db.entity");
    EntityManager em = entityManagerFactory.createEntityManager();

    em.getTransaction().begin();

    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<User> cq = cb.createQuery(User.class);
    Root<User> from = cq.from(User.class);
    Join<User, Role> join = from.join("role", JoinType.LEFT);
    cq.where(cb.equal(join.get("name"), "ordinary"));
    TypedQuery<User> tq = em.createQuery(cq);
    List<User> users = tq.getResultList();
    for (User u : users) {
        System.out.println(u.getLastName());
    }

    em.getTransaction().commit();
    em.close();
    entityManagerFactory.close();
}

/**
 * Geef de Path voor de gegeven naam (kan punten bevatten om door objecten te lopen).
 *
 * @param base
 *            basis
 * @param naam
 *            naam
 * @param <T>
 *            attribuut type
 * @return path
 */
public static <T> Path<T> getPath(final Path<?> base, final String naam) {
    final Path<T> result;
    final int index = naam.indexOf('.');
    if (index == -1) {
        result = base.get(naam);
    } else {
        final String part = naam.substring(0, index);
        final String rest = naam.substring(index + 1);

        final Path<?> partPath = base.get(part);
        if (partPath.getModel() == null) {
            // Dan kunnen we hier niet door, maar moeten we via een join
            final Join<?, ?> join = ((From<?, ?>) base).join(part);
            result = getPath(join, rest);
        } else {
            result = getPath(partPath, rest);
        }
    }
    return result;
}

public static boolean isSecurityGroupExistWithProtectAll(EntityManager em, String projectId, Long vcId) {
    CriteriaBuilder cb = em.getCriteriaBuilder();

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

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

    return !em.createQuery(query).setMaxResults(1).getResultList().isEmpty();
}

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

@GET
@Path("id/{guardianId}")
@Produces({"application/xml", "application/json"})
public List<Student> getStatusByGuardianId(@PathParam("guardianId") Long id) {
    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_.id), id));
    cq.distinct(true);

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

public List<PlayerDetails> getPlayersByLeagueId(String leagueId) {
    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_.id), leagueId));
            cq.select(player).distinct(true);
            TypedQuery<Player> q = em.createQuery(cq);
            players = q.getResultList();
        }
        return copyPlayersToDetails(players);
    } catch (Exception ex) {
        throw new EJBException(ex);
    }
}

public List<PlayerDetails> getPlayersByLeagueId(String leagueId) {
    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_.team);
            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_.id), leagueId));
            cq.select(player).distinct(true);
            TypedQuery<Player> q = em.createQuery(cq);
            players = q.getResultList();
        }
        return copyPlayersToDetails(players);
    } catch (Exception ex) {
        throw new EJBException(ex);
    }
}

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_.team);
            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);
    }
}

public List<PlayerDetails> getPlayersByCity(String city) {
    logger.info("getPlayersByCity");
    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_.team);

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

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

private Map<Long, Integer> fetchUsedQuotas() {
    final CriteriaBuilder builder = entityManager.getCriteriaBuilder();
    final CriteriaQuery<Tuple> query = builder.createTupleQuery();
    final Root<HarvestReport> root = query.from(HarvestReport.class);

    Join<Harvest, HarvestQuota> joinedQuotas = root.join(HarvestReport_.harvests).join(Harvest_.harvestQuota, JoinType.LEFT);
    Path<Long> quotaId = joinedQuotas.get(HarvestQuota_.id);

    Expression<Long> count = builder.count(root.get(HarvestReport_.id));

    Predicate onlyApproved = builder.equal(root.get(HarvestReport_.state), HarvestReport.State.APPROVED);
    Predicate quotaNotNull = builder.isNotNull(quotaId);

    CriteriaQuery<Tuple> q = query
            .multiselect(quotaId, count)
            .where(onlyApproved, quotaNotNull)
            .groupBy(quotaId);
    return map(entityManager.createQuery(q).getResultList());
}

private static Predicate createPredicate(Root<Occupation> root, CriteriaBuilder cb, OccupationContactSearchDTO dto) {
    final List<Predicate> predicates = Lists.newArrayList();

    final Join<Occupation, Organisation> organisationJoin = root.join(Occupation_.organisation, JoinType.LEFT);

    predicates.add(validNow(root, cb));
    predicates.add(byOrgType(cb, organisationJoin, dto.getOrganisationType()));
    if (dto.getAreaCode() != null) {
        predicates.add(byArea(cb, organisationJoin, dto.getAreaCode()));
    }
    if (dto.getRhyCode() != null) {
        predicates.add(cb.equal(organisationJoin.get(Organisation_.officialCode), dto.getRhyCode()));
    }
    if (dto.getOccupationType() != null) {
        predicates.add(cb.equal(root.get(Occupation_.occupationType), dto.getOccupationType()));
    }

    return cb.and(toArray(predicates));
}

@SuppressWarnings("unchecked")
@Nonnull
public static <T, U> Join<T, U> join(
        @Nonnull final From<?, T> from, @Nonnull final PluralAttribute<? super T, ?, U> attribute) {

    Objects.requireNonNull(from, "from is null");
    Objects.requireNonNull(attribute, "attribute is null");

    if (attribute instanceof CollectionAttribute) {
        return from.join((CollectionAttribute<T, U>) attribute);
    }
    if (attribute instanceof SetAttribute) {
        return from.join((SetAttribute<T, U>) attribute);
    }
    if (attribute instanceof ListAttribute) {
        return from.join((ListAttribute<T, U>) attribute);
    }
    if (attribute instanceof MapAttribute) {
        return from.join((MapAttribute<T, ?, U>) attribute);
    }

    // Should never end up here.
    throw new IllegalArgumentException();
}

public List<Object[]> findPurchaseList(Date beginDate, Date endDate, Integer count) {
    CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
    CriteriaQuery<Object[]> criteriaQuery = criteriaBuilder.createQuery(Object[].class);
    Root<Member> member = criteriaQuery.from(Member.class);
    Join<Product, Order> orders = member.join("orders");
    criteriaQuery.multiselect(member.get("id"), member.get("username"), member.get("email"), member.get("point"), member.get("amount"), member.get("balance"), criteriaBuilder.sum(orders.<BigDecimal> get("amountPaid")));
    Predicate restrictions = criteriaBuilder.conjunction();
    if (beginDate != null) {
        restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.greaterThanOrEqualTo(orders.<Date> get("createDate"), beginDate));
    }
    if (endDate != null) {
        restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.lessThanOrEqualTo(orders.<Date> get("createDate"), endDate));
    }
    restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(orders.get("orderStatus"), OrderStatus.completed), criteriaBuilder.equal(orders.get("paymentStatus"), PaymentStatus.paid));
    criteriaQuery.where(restrictions);
    criteriaQuery.groupBy(member.get("id"), member.get("username"), member.get("email"), member.get("point"), member.get("amount"), member.get("balance"));
    criteriaQuery.orderBy(criteriaBuilder.desc(criteriaBuilder.sum(orders.<BigDecimal> get("amountPaid"))));
    TypedQuery<Object[]> query = entityManager.createQuery(criteriaQuery).setFlushMode(FlushModeType.COMMIT);
    if (count != null && count >= 0) {
        query.setMaxResults(count);
    }
    return query.getResultList();
}

public List<Object[]> findSalesList(Date beginDate, Date endDate, Integer count) {
    CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
    CriteriaQuery<Object[]> criteriaQuery = criteriaBuilder.createQuery(Object[].class);
    Root<Product> product = criteriaQuery.from(Product.class);
    Join<Product, OrderItem> orderItems = product.join("orderItems");
    Join<Product, com.easyshopping.entity.Order> order = orderItems.join("order");
    criteriaQuery.multiselect(product.get("id"), product.get("sn"), product.get("name"), product.get("fullName"), product.get("price"), criteriaBuilder.sum(orderItems.<Integer> get("quantity")), criteriaBuilder.sum(criteriaBuilder.prod(orderItems.<Integer> get("quantity"), orderItems.<BigDecimal> get("price"))));
    Predicate restrictions = criteriaBuilder.conjunction();
    if (beginDate != null) {
        restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.greaterThanOrEqualTo(order.<Date> get("createDate"), beginDate));
    }
    if (endDate != null) {
        restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.lessThanOrEqualTo(order.<Date> get("createDate"), endDate));
    }
    restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(order.get("orderStatus"), OrderStatus.completed), criteriaBuilder.equal(order.get("paymentStatus"), PaymentStatus.paid));
    criteriaQuery.where(restrictions);
    criteriaQuery.groupBy(product.get("id"), product.get("sn"), product.get("name"), product.get("fullName"), product.get("price"));
    criteriaQuery.orderBy(criteriaBuilder.desc(criteriaBuilder.sum(criteriaBuilder.prod(orderItems.<Integer> get("quantity"), orderItems.<BigDecimal> get("price")))));
    TypedQuery<Object[]> query = entityManager.createQuery(criteriaQuery).setFlushMode(FlushModeType.COMMIT);
    if (count != null && count >= 0) {
        query.setMaxResults(count);
    }
    return query.getResultList();
}

@Override
public Object[][] getEntryCourseTableData(SearchFilter filter) throws ProcessingException {
  Long eventNr = ((SingleEventSearchFormData) filter.getFormData()).getEvent().getValue();

  CriteriaBuilder b = JPA.getCriteriaBuilder();
  CriteriaQuery<Object[]> selectQuery = b.createQuery(Object[].class);
  Root<RtCourse> course = selectQuery.from(RtCourse.class);
  Join<RtCourse, RtEventClass> joinEventClasses = course.join(RtCourse_.rtEventClasses, JoinType.LEFT);
  Join<RtEventClass, RtRace> joinRaces = joinEventClasses.join(RtEventClass_.rtRaces, JoinType.LEFT);

  selectQuery.select(b.array(course.get(RtCourse_.id).get(RtCourseKey_.courseNr), course.get(RtCourse_.shortcut), b.countDistinct(joinRaces.get(RtRace_.entryNr)), b.countDistinct(joinRaces.get(RtRace_.id).get(RtRaceKey_.raceNr)))).where(eventNr != null ? b.equal(joinRaces.get(RtRace_.eventNr), eventNr) : b.conjunction()).groupBy(course.get(RtCourse_.id).get(RtCourseKey_.courseNr), course.get(RtCourse_.shortcut));

  List<Object[]> list = JPA.createQuery(selectQuery).getResultList();

  List<Object[]> result = new ArrayList<>();
  for (Object[] row : list) {
    Object[] resultRow = new Object[5];
    resultRow[0] = 0L; // Force Summary Order to bottom
    resultRow[1] = row[0];
    resultRow[2] = row[1];
    resultRow[3] = row[2];
    resultRow[4] = row[3];
    result.add(resultRow);
  }
  return JPAUtility.convertList2Array(result);
}

@Override
public boolean existsRaceWithStartTime(Long[] startlistSettingNrs) throws ProcessingException {
  if (startlistSettingNrs == null || startlistSettingNrs.length == 0) {
    return false;
  }

  CriteriaBuilder b = JPA.getCriteriaBuilder();
  CriteriaQuery<Long> selectQuery = b.createQuery(Long.class);
  Root<RtParticipation> participation = selectQuery.from(RtParticipation.class);
  Join<RtParticipation, RtEventClass> joinEventClass = participation.join(RtParticipation_.rtEventClass, JoinType.INNER);
  Join<RtEventClass, RtCourse> joinCourse = joinEventClass.join(RtEventClass_.rtCourse, JoinType.LEFT);

  selectQuery.select(b.count(participation.get(RtParticipation_.id).get(RtParticipationKey_.clientNr)))
      .where(
          b.and(
              b.isNotNull(participation.get(RtParticipation_.startTime)),
              b.equal(participation.get(RtParticipation_.id).get(RtParticipationKey_.clientNr), ServerSession.get().getSessionClientNr()),
              b.or(joinCourse.get(RtCourse_.startlistSettingNr).in(Arrays.asList(startlistSettingNrs)),
                  joinEventClass.get(RtEventClass_.startlistSettingNr).in(Arrays.asList(startlistSettingNrs)))
              ));
  List<Long> result = JPA.createQuery(selectQuery).getResultList();
  if (result == null || result.isEmpty()) {
    return false;
  }
  return result.get(0) > 0;
}

@Override
public List<RtMap> getMapTableData(Long eventNr) throws ProcessingException {
  CriteriaBuilder b = JPA.getCriteriaBuilder();
  CriteriaQuery<RtMap> select = b.createQuery(RtMap.class);
  Root<RtMap> rtmap = select.from(RtMap.class);

  List<Predicate> list = new ArrayList<>();
  Predicate restriction1 = b.equal(rtmap.get(RtMap_.key).get(RtMapKey_.clientNr), ServerSession.get().getSessionClientNr());
  list.add(restriction1);
  if (eventNr != null) {
    Join<RtMap, RtEventMap> joinEventMap = rtmap.join(RtMap_.rtEventMaps, JoinType.LEFT);
    Predicate restriction2 = b.equal(joinEventMap.get(RtEventMap_.id).get(RtEventMapKey_.eventNr), eventNr);
    list.add(restriction2);
  }

  select.where(b.and(list.toArray(new Predicate[list.size()])));

  return JPA.createQuery(select).getResultList();
}

@Override
public Object[][] getRankingClassesTableData(Long rankingNr) throws ProcessingException {
  CriteriaBuilder b = JPA.getCriteriaBuilder();
  CriteriaQuery<Object[]> selectQuery = b.createQuery(Object[].class);
  Root<RtRankingEvent> rankingEvent = selectQuery.from(RtRankingEvent.class);
  Join<RtRankingEvent, RtEvent> joinEvent = rankingEvent.join(RtRankingEvent_.rtEvent, JoinType.INNER);
  Join<RtEvent, RtEventClass> joinEventClass = joinEvent.join(RtEvent_.rtEventClasses, JoinType.INNER);

  Subquery<Long> numberOfEventsSubselect = selectQuery.subquery(Long.class);
  Root<RtRankingEvent> subroot = numberOfEventsSubselect.from(RtRankingEvent.class);
  numberOfEventsSubselect.select(b.count(subroot.get(RtRankingEvent_.id).get(RtRankingEventKey_.rankingNr))).where(b.and(b.equal(subroot.get(RtRankingEvent_.id).get(RtRankingEventKey_.rankingNr), rankingNr), b.equal(subroot.get(RtRankingEvent_.id).get(RtRankingEventKey_.clientNr), ServerSession.get().getSessionClientNr())));

  selectQuery.select(b.array(joinEventClass.get(RtEventClass_.id).get(RtEventClassKey_.classUid), b.count(rankingEvent.get(RtRankingEvent_.id).get(RtRankingEventKey_.eventNr)))).where(b.and(b.equal(rankingEvent.get(RtRankingEvent_.id).get(RtRankingEventKey_.rankingNr), rankingNr), b.equal(rankingEvent.get(RtRankingEvent_.id).get(RtRankingEventKey_.clientNr), ServerSession.get().getSessionClientNr()))).groupBy(joinEventClass.get(RtEventClass_.id).get(RtEventClassKey_.classUid)).having(b.equal(b.count(rankingEvent.get(RtRankingEvent_.id).get(RtRankingEventKey_.eventNr)), numberOfEventsSubselect));

  return JPAUtility.convertList2Array(JPA.createQuery(selectQuery).getResultList());
}

@Override
public Object[][] getCityTableData(CitySearchFormData formData) throws ProcessingException {
  CriteriaBuilder b = JPA.getCriteriaBuilder();
  CriteriaQuery<Object[]> selectQuery = b.createQuery(Object[].class);
  Root<RtCity> city = selectQuery.from(RtCity.class);
  Join<RtCity, RtCountry> joinCountry = city.join(RtCity_.rtCountry);
  Join<RtCity, RtAddress> joinAddress = city.join(RtCity_.rtAddresses, JoinType.LEFT);

  JPACityBoxSearchFormDataStatementBuilder builder = new JPACityBoxSearchFormDataStatementBuilder(city);
  builder.build(formData.getCityBox());

  selectQuery.select(b.array(city.get(RtCity_.id).get(RtCityKey_.cityNr), city.get(RtCity_.zip), city.get(RtCity_.name), city.get(RtCity_.areaUid), city.get(RtCity_.region), city.get(RtCity_.countryUid), joinCountry.get(RtCountry_.countryCode), b.count(joinAddress.get(RtAddress_.id).get(RtAddressKey_.addressNr)))).where(b.and(b.equal(city.get(RtCity_.id).get(RtCityKey_.clientNr), ServerSession.get().getSessionClientNr()), builder.getPredicate())).groupBy(city.get(RtCity_.id).get(RtCityKey_.cityNr), city.get(RtCity_.zip), city.get(RtCity_.name), city.get(RtCity_.areaUid), city.get(RtCity_.region), city.get(RtCity_.countryUid), joinCountry.get(RtCountry_.countryCode));

  List<Object[]> result = JPA.createQuery(selectQuery).getResultList();
  return JPAUtility.convertList2Array(result);
}

protected List<FeeFormData> loadFeeConfigurationAddInfo() {
  CriteriaBuilder b = JPA.getCriteriaBuilder();
  CriteriaQuery<Object[]> selectQuery = b.createQuery(Object[].class);
  Root<RtFee> feeRoot = selectQuery.from(RtFee.class);
  Join<RtFee, RtFeeGroup> joinFeeGroup = feeRoot.join(RtFee_.rtFeeGroup);
  Join<RtFeeGroup, RtAdditionalInformationDef> joinAddInfoDef = joinFeeGroup.join(RtFeeGroup_.rtAdditionalInformationDefs);

  selectQuery.select(b.array(feeRoot.get(RtFee_.id).get(RtFeeKey_.feeNr), joinAddInfoDef.get(RtAdditionalInformationDef_.id).get(RtAdditionalInformationDefKey_.additionalInformationUid), feeRoot.get(RtFee_.fee), feeRoot.get(RtFee_.currencyUid), feeRoot.get(RtFee_.evtFrom), feeRoot.get(RtFee_.evtTo), feeRoot.get(RtFee_.ageFrom), feeRoot.get(RtFee_.ageTo), joinFeeGroup.get(RtFeeGroup_.cashPaymentOnRegistration)));
  List<Object[]> resultList = JPA.createQuery(selectQuery).getResultList();

  List<FeeFormData> result = new ArrayList<>();
  for (Object[] row : resultList) {
    FeeFormData feeData = new FeeFormData();
    feeData.setFeeNr(TypeCastUtility.castValue(row[0], Long.class));
    feeData.setAdditionalInformationUid(TypeCastUtility.castValue(row[1], Long.class));
    feeData.getFee().setValue(TypeCastUtility.castValue(row[2], Double.class));
    feeData.getCurrency().setValue(TypeCastUtility.castValue(row[3], Long.class));
    feeData.getDateFrom().setValue(TypeCastUtility.castValue(row[4], Date.class));
    feeData.getDateTo().setValue(TypeCastUtility.castValue(row[5], Date.class));
    feeData.getAgeFrom().setValue(TypeCastUtility.castValue(row[6], Long.class));
    feeData.getAgeTo().setValue(TypeCastUtility.castValue(row[7], Long.class));
    feeData.getCashPaymentOnRegistrationProperty().setValue(BooleanUtility.nvl(TypeCastUtility.castValue(row[8], Boolean.class)));
    result.add(feeData);
  }
  return result;
}

@Test
public void criteria() {
    CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    CriteriaQuery<ClientEntityBrowserDto> c = cb.createQuery(ClientEntityBrowserDto.class);
    Root<Client> client = c.from(Client.class);

    Join<Client, ClientGroup> clientGroup = client.join("group", JoinType.LEFT);
    Join<Client, ClientStructure> clientStructure = client.join("structure", JoinType.LEFT);
    Join<ClientType, ClientTypeGroup> clientTypeGroup
        = client.join("type", JoinType.LEFT).join("clientTypeGroup", JoinType.LEFT);

    Selection<ClientEntityBrowserDto> selection
        = cb.construct(ClientEntityBrowserDto.class,
                       client.<Integer>get("id"),
                       client.<String>get("number"),
                       client.<String>get("name"),
                       clientTypeGroup.<String>get("name"),
                       clientGroup.<String>get("name"),
                       clientStructure.<String>get("name"));

    c.select(selection);
    c.distinct(true);
    TypedQuery<ClientEntityBrowserDto> query = entityManager.createQuery(c);
    assertTrue(query.getResultList().isEmpty());
}

public List<Employee> findUsingSubQuery(String projectName) {
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Employee> c = cb.createQuery(Employee.class);
    Root<Employee> emp = c.from(Employee.class);
    c.select(emp);

    TypedQuery<Employee> query = em.createQuery(c);

    if (projectName != null) {
        Subquery<Employee> sq = c.subquery(Employee.class);
        Root<Project> project = sq.from(Project.class);
        Join<Project, Employee> sqEmp = project.join("employees");
        sq.select(sqEmp)
                // set parameter
                .where(cb.equal(project.get("name"), cb.parameter(String.class, "project")));
        query.setParameter("project", projectName);

    }
    return null;
}

@Override
@SuppressWarnings( "unchecked" )
public List<T> getAllByAttributes( List<Attribute> attributes )
{
    Schema schema = schemaService.getDynamicSchema( getClazz() );

    if ( schema == null || !schema.havePersistedProperty( "attributeValues" ) || attributes.isEmpty() )
    {
        return new ArrayList<>();
    }

    query = getCriteriaQuery();

    Root root = query.from( getClazz() );
    Join joinAttributeValue = root.join( ("attributeValues"), JoinType.INNER );
    query.select( root );
    query.where( joinAttributeValue.get( "attribute" ).in( attributes ) );

    return sessionFactory.getCurrentSession().createQuery( query ).list();
}

@Override
@SuppressWarnings( "unchecked" )
public List<AttributeValue> getAttributeValueByAttribute( Attribute attribute )
{
    Schema schema = schemaService.getDynamicSchema( getClazz() );

    if ( schema == null || !schema.havePersistedProperty( "attributeValues" ) )
    {
        return new ArrayList<>();
    }

    query = getCriteriaQuery();

    Root root = query.from( getClazz() );
    Join joinAttributeValue = root.join( ("attributeValues"), JoinType.INNER );
    query.select( root.get( "attributeValues" ) );
    query.where( builder.equal( joinAttributeValue.get( "attribute" ), attribute ) );

    return sessionFactory.getCurrentSession().createQuery( query ).list();
}

@Override
@SuppressWarnings( "unchecked" )
public List<AttributeValue> getAttributeValueByAttributeAndValue( Attribute attribute, String value )
{
    Schema schema = schemaService.getDynamicSchema( getClazz() );

    if ( schema == null || !schema.havePersistedProperty( "attributeValues" ) )
    {
        return null;
    }

    query = getCriteriaQuery();

    Root root = query.from( getClazz() );
    Join joinAttributeValue = root.join( ("attributeValues"), JoinType.INNER );
    query.select( root.get( "attributeValues" ) );
    query.where(
        builder.and(
            builder.equal( joinAttributeValue.get( "attribute" ), attribute ),
            builder.equal( joinAttributeValue.get( "value" ), value ) ) );

    return sessionFactory.getCurrentSession().createQuery( query ).list();
}

private SqmAttributeJoin makeSqmAttributeJoin(SqmNavigableSourceReference sourceBinding, SqmFromElementSpace space, Join<?, ?> join) {
    final JpaAttributeJoin<?,?> jpaAttributeJoin = (JpaAttributeJoin<?, ?>) join;
    final String alias = jpaAttributeJoin.getAlias();

    final SqmAttributeReference attributeBinding = (SqmAttributeReference) parsingContext.findOrCreateNavigableBinding(
            sourceBinding,
            jpaAttributeJoin.getAttribute().getName()
    );

    // todo : handle treats

    final SqmAttributeJoin sqmJoin = querySpecProcessingStateStack.getCurrent().getFromElementBuilder().buildAttributeJoin(
            attributeBinding,
            alias,
            // todo : this is where treat would be applied
            null,
            convert( join.getJoinType() ),
            false,
            false
    );
    space.addJoin( sqmJoin );
    bindJoins( jpaAttributeJoin, sqmJoin.getBinding(), space );
    jpaPathResolutionMap.put( jpaAttributeJoin, sqmJoin.getBinding() );

    return sqmJoin;
}

protected CriteriaQuery<Product> getCriteriaForActiveProducts(Date currentDate) {
    // Set up the criteria query that specifies we want to return Products
    CriteriaBuilder builder = em.getCriteriaBuilder();
    CriteriaQuery<Product> criteria = builder.createQuery(Product.class);

    // The root of our search is Product
    Root<ProductImpl> product = criteria.from(ProductImpl.class);

    // We need to filter on active date on the sku
    Join<Product, Sku> sku = product.join("defaultSku");
    product.fetch("defaultSku");

    // Product objects are what we want back
    criteria.select(product);

    // Ensure the product is currently active
    List<Predicate> restrictions = new ArrayList<Predicate>();
    attachActiveRestriction(currentDate, product, sku, restrictions);

    // Add the restrictions to the criteria query
    criteria.where(restrictions.toArray(new Predicate[restrictions.size()]));

    //Add ordering so that paginated queries are consistent
    criteria.orderBy(builder.asc(product.get("id")));
    return criteria;
}

protected CriteriaQuery<Product> getCriteriaForActiveProducts(Date currentDate) {
    // Set up the criteria query that specifies we want to return Products
    CriteriaBuilder builder = em.getCriteriaBuilder();
    CriteriaQuery<Product> criteria = builder.createQuery(Product.class);

    // The root of our search is Product
    Root<ProductImpl> product = criteria.from(ProductImpl.class);

    // We need to filter on active date on the sku
    Join<Product, Sku> sku = product.join("defaultSku");
    product.fetch("defaultSku");

    // Product objects are what we want back
    criteria.select(product);

    // Ensure the product is currently active
    List<Predicate> restrictions = new ArrayList<Predicate>();
    attachActiveRestriction(currentDate, product, sku, restrictions);

    // Add the restrictions to the criteria query
    criteria.where(restrictions.toArray(new Predicate[restrictions.size()]));

    //Add ordering so that paginated queries are consistent
    criteria.orderBy(builder.asc(product.get("id")));
    return criteria;
}

/**
 *
 * @param projectId
 * @param count
 *
 * @return
 */
public List<HistoricValuesProject> getAllHistValuesForProject(
        Long projectId, int first, int count) {
    logger.infof(
            "loading %d Historic Values ordered by descending date for Project with ID %d ...",
            count, projectId);
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<HistoricValuesProject> criteria = cb.createQuery(HistoricValuesProject.class);
    Root<Project> root = criteria.from(Project.class);
    Join<Project, HistoricValuesProject> join = root.join(Project_.historicValues);
    criteria.select(join)
            .where(cb.and(
                    cb.equal(root.get(Project_.id), projectId),
                    cb.not(join.get(HistoricValuesProject_.deleted)))
                    )
            .orderBy(cb.desc(join.get(HistoricValuesProject_.date)));

    return em.createQuery(criteria).setFirstResult(first)
            .setMaxResults(count).getResultList();
}

/**
 * Return active file formats for the given DigitalObject type, filtered by the given migration attribute (only
 * those objects for which the given migration attribute returns an empty result will be checked for formats).
 * 
 * A format is considered active if it is used by a file belonging to the current version of a digital object that
 * has not been migrated from.
 * 
 * @param <T>
 *            type of the digital object
 * @param clazz
 *            class of the digital object
 * @param excludedMigration
 *            migration attribute to check
 * @return list of active file formats
 */
private <T extends DigitalObject> List<DataFileFormat> findActive(Class<T> clazz,
        ListAttribute<? super T, ? extends Migration<?, ?>> excludedMigration) {

    CriteriaQuery<DataFileFormat> query = criteriaBuilder.createQuery(DataFileFormat.class);

    Root<T> root = query.from(clazz);
    query.where(criteriaBuilder.isEmpty(root.<List<?>> get(excludedMigration.getName())));

    Join<T, ContentVersion> joinVersion = root.join(DigitalObject_.currentVersion);
    Join<ContentVersion, DataFileVersion> joinFileVersions = joinVersion.join(ContentVersion_.files);
    Join<DataFileVersion, DataFile> joinFiles = joinFileVersions.join(DataFileVersion_.dataFile);
    query.select(joinFiles.get(DataFile_.format));

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

@Override
public JobDefinitionEntity getJobDefinitionByAltKey(String namespace, String jobName)
{
    // Create the criteria builder and the criteria.
    CriteriaBuilder builder = entityManager.getCriteriaBuilder();
    CriteriaQuery<JobDefinitionEntity> criteria = builder.createQuery(JobDefinitionEntity.class);

    // The criteria root is the job definition.
    Root<JobDefinitionEntity> jobDefinition = criteria.from(JobDefinitionEntity.class);

    // Join to the other tables we can filter on.
    Join<JobDefinitionEntity, NamespaceEntity> namespaceJoin = jobDefinition.join(JobDefinitionEntity_.namespace);

    // Create the standard restrictions (i.e. the standard where clauses).
    Predicate namespaceRestriction = builder.equal(builder.upper(namespaceJoin.get(NamespaceEntity_.code)), namespace.toUpperCase());
    Predicate jobNameRestriction = builder.equal(builder.upper(jobDefinition.get(JobDefinitionEntity_.name)), jobName.toUpperCase());

    criteria.select(jobDefinition).where(builder.and(namespaceRestriction, jobNameRestriction));

    return executeSingleResultQuery(criteria,
        String.format("Found more than one Activiti job definition with parameters {namespace=\"%s\", jobName=\"%s\"}.", namespace, jobName));
}

@Override
public Map<Long, Long> countAllGroupByOwner() {
    CriteriaQuery<Tuple> query = criteriaBuilder.createTupleQuery();
    Root<DataFile> root = query.from(clazz);
    Join<DataFile, DataFileVersion> joinFileVersions = root.join(DataFile_.includedIn);
    Join<DataFileVersion, ContentVersion> joinVersion = joinFileVersions.join(DataFileVersion_.contentVersion);
    Join<ContentVersion, DigitalObject> joinObject = joinVersion.join(ContentVersion_.object);

    query.groupBy(joinObject.get(DigitalObject_.ownerId));
    query.multiselect(joinObject.get(DigitalObject_.ownerId), criteriaBuilder.countDistinct(root));

    Map<Long, Long> results = new HashMap<Long, Long>();
    for (Tuple tuple : entityManager.createQuery(query).getResultList()) {
        results.put((Long) tuple.get(0), (Long) tuple.get(1));
    }

    return results;
}

@Override
public Map<Long, Long> getSizeGroupByOwner() {
    CriteriaQuery<Tuple> query = criteriaBuilder.createTupleQuery();
    Root<DataFile> root = query.from(clazz);
    Root<DigitalObject> objectRoot = query.from(DigitalObject.class);

    Subquery<DataFileVersion> subquery = query.subquery(DataFileVersion.class);
    Root<DataFileVersion> subqueryRoot = subquery.from(DataFileVersion.class);
    Join<DataFileVersion, ContentVersion> joinVersion = subqueryRoot.join(DataFileVersion_.contentVersion);
    Join<ContentVersion, DigitalObject> joinObject = joinVersion.join(ContentVersion_.object);
    subquery.where(criteriaBuilder.and(criteriaBuilder.equal(joinObject, objectRoot),
        criteriaBuilder.equal(root, subqueryRoot.get(DataFileVersion_.dataFile))));
    subquery.select(subqueryRoot);

    query.where(criteriaBuilder.exists(subquery));
    query.groupBy(objectRoot.get(DigitalObject_.ownerId));
    query.multiselect(objectRoot.get(DigitalObject_.ownerId), criteriaBuilder.sum(root.get(DataFile_.size)));

    Map<Long, Long> results = new HashMap<Long, Long>();
    for (Tuple tuple : entityManager.createQuery(query).getResultList()) {
        results.put((Long) tuple.get(0), (Long) tuple.get(1));
    }

    return results;
}

@Override
public BusinessObjectDefinitionEntity getBusinessObjectDefinitionByKey(BusinessObjectDefinitionKey businessObjectDefinitionKey)
{
    // Create the criteria builder and the criteria.
    CriteriaBuilder builder = entityManager.getCriteriaBuilder();
    CriteriaQuery<BusinessObjectDefinitionEntity> criteria = builder.createQuery(BusinessObjectDefinitionEntity.class);

    // The criteria root is the business object definition.
    Root<BusinessObjectDefinitionEntity> businessObjectDefinitionEntity = criteria.from(BusinessObjectDefinitionEntity.class);

    // Join to the other tables we can filter on.
    Join<BusinessObjectDefinitionEntity, NamespaceEntity> namespaceEntity = businessObjectDefinitionEntity.join(BusinessObjectDefinitionEntity_.namespace);

    // Create the standard restrictions (i.e. the standard where clauses).
    Predicate queryRestriction =
        builder.equal(builder.upper(namespaceEntity.get(NamespaceEntity_.code)), businessObjectDefinitionKey.getNamespace().toUpperCase());
    queryRestriction = builder.and(queryRestriction, builder.equal(builder.upper(businessObjectDefinitionEntity.get(BusinessObjectDefinitionEntity_.name)),
        businessObjectDefinitionKey.getBusinessObjectDefinitionName().toUpperCase()));

    criteria.select(businessObjectDefinitionEntity).where(queryRestriction);

    return executeSingleResultQuery(criteria, String
        .format("Found more than one business object definition with parameters {namespace=\"%s\", businessObjectDefinitionName=\"%s\"}.",
            businessObjectDefinitionKey.getNamespace(), businessObjectDefinitionKey.getBusinessObjectDefinitionName()));
}

@Override
public Long countChangedObjects(Date from, Date until, NamespaceType prefix, ObjectType set) {
    CriteriaQuery<Long> query = criteriaBuilder.createQuery(Long.class);
    Root<Operation> root = query.from(clazz);
    Join<Operation, DigitalObject> operationToObject = root.join(Operation_.object);
    query = query.distinct(true).select(criteriaBuilder.countDistinct(operationToObject.get(DigitalObject_.id)));
    Predicate predicate = criteriaBuilder.greaterThanOrEqualTo(root.get(Operation_.date), from);
    predicate = criteriaBuilder.and(predicate, criteriaBuilder.lessThanOrEqualTo(root.get(Operation_.date), until));
    predicate = criteriaBuilder.and(predicate, criteriaBuilder.equal(root.get(Operation_.metadataType), prefix));
    if (set != null) {
        predicate = criteriaBuilder.and(predicate,
            criteriaBuilder.equal(operationToObject.get(DigitalObject_.type), set));
    }
    query = query.where(predicate);
    return entityManager.createQuery(query).getSingleResult();
}

@Override
public List<Operation> getChanges(Date from, Date until, NamespaceType prefix, ObjectType set, int offset,
        int pageSize) {
    CriteriaQuery<Operation> query = criteriaBuilder.createQuery(Operation.class);
    Root<Operation> root = query.from(clazz);
    Subquery<Long> subquery = query.subquery(Long.class);
    Root<Operation> subqueryRoot = subquery.from(clazz);
    subquery = subquery.select(criteriaBuilder.max(subqueryRoot.get(Operation_.id)));
    Predicate subqueryPredicate = criteriaBuilder.greaterThanOrEqualTo(subqueryRoot.get(Operation_.date), from);
    subqueryPredicate = criteriaBuilder.and(subqueryPredicate,
        criteriaBuilder.lessThanOrEqualTo(subqueryRoot.get(Operation_.date), until));
    subqueryPredicate = criteriaBuilder.and(subqueryPredicate,
        criteriaBuilder.equal(subqueryRoot.get(Operation_.metadataType), prefix));
    if (set != null) {
        Join<Operation, DigitalObject> operationToObject = subqueryRoot.join(Operation_.object);
        subqueryPredicate = criteriaBuilder.and(subqueryPredicate,
            criteriaBuilder.equal(operationToObject.get(DigitalObject_.type), set));
    }
    subquery = subquery.where(subqueryPredicate);
    subquery = subquery.groupBy(subqueryRoot.get(Operation_.object).get(DigitalObject_.id));
    query = query.where(root.get(Operation_.id).in(subquery));
    query.orderBy(criteriaBuilder.asc(root.get(Operation_.id)));
    return entityManager.createQuery(query).setFirstResult(offset).setMaxResults(pageSize).getResultList();
}

public Map<Customer, Double> sumPriceByPurchaseCustomer() {
    // Create "tuple" query for use with groupBy; otherwise, throws PersistenceException: Exception [EclipseLink-6051]
    CriteriaQuery<Tuple> query = em.getCriteriaBuilder().createTupleQuery();
    Root<Customer> from = query.from(Customer.class);
    Join<Customer, Purchase> joinPurchase = from.join("purchases");
    Join<Purchase, Product> joinProduct = joinPurchase.join("products");
    query.multiselect(from.get("id"), em.getCriteriaBuilder().sum(joinProduct.get("price").as(Double.class)));
    query.groupBy(from.get("id"));
    List<Tuple> results = em.createQuery(query).getResultList();

    Map<Customer, Double> ret = new HashMap<>();
    for (Tuple result : results) {
        Object[] arr = result.toArray();
        ret.put(customerService.findById((Long)arr[0]), ((Double)arr[1]));
    }
    return ret;
}

@Override
public StorageFileEntity getStorageFileByStorageNameAndFilePath(String storageName, String filePath)
{
    // Create the criteria builder and the criteria.
    CriteriaBuilder builder = entityManager.getCriteriaBuilder();
    CriteriaQuery<StorageFileEntity> criteria = builder.createQuery(StorageFileEntity.class);

    // The criteria root is the storage files.
    Root<StorageFileEntity> storageFileEntity = criteria.from(StorageFileEntity.class);

    // Join to the other tables we can filter on.
    Join<StorageFileEntity, StorageUnitEntity> storageUnitEntity = storageFileEntity.join(StorageFileEntity_.storageUnit);
    Join<StorageUnitEntity, StorageEntity> storageEntity = storageUnitEntity.join(StorageUnitEntity_.storage);

    // Create the standard restrictions (i.e. the standard where clauses).
    Predicate filePathRestriction = builder.equal(storageFileEntity.get(StorageFileEntity_.path), filePath);
    Predicate storageNameRestriction = builder.equal(builder.upper(storageEntity.get(StorageEntity_.name)), storageName.toUpperCase());

    criteria.select(storageFileEntity).where(builder.and(filePathRestriction, storageNameRestriction));

    return executeSingleResultQuery(criteria,
        String.format("Found more than one storage file with parameters {storageName=\"%s\"," + " filePath=\"%s\"}.", storageName, filePath));
}