Java Join.join方法代碼示例

import javax.persistence.criteria.Join; //導入方法依賴的package包/類
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);
    }
}
 

import javax.persistence.criteria.Join; //導入方法依賴的package包/類
public Collection<ExtGraph> findByWordAndPackageNo(String word, int packageno) {

        Long pkg = new Long(packageno);
        CriteriaBuilder criteriaBuilder = getEntityManager().getCriteriaBuilder();
        CriteriaQuery<ExtGraph> criteriaQuery = criteriaBuilder.createQuery(ExtGraph.class
        );

        Root<ExtGraph> root = criteriaQuery.from(ExtGraph.class);
        Join<ExtGraph, Synset> synset = root.join("synset", JoinType.INNER);
        Join<Synset, Sense> sts = synset.join("sense", JoinType.LEFT);
        List<Predicate> criteriaList = new ArrayList<>();

        Predicate firstCondition = criteriaBuilder.equal(root.get("word"), word);
        criteriaList.add(firstCondition);

        Predicate secondCondition = criteriaBuilder.equal(root.get("packageno"), pkg);
        criteriaList.add(secondCondition);

        Predicate thirdCondition = criteriaBuilder.equal(sts.get("senseIndex"), 0);
        criteriaList.add(thirdCondition);

        criteriaQuery.where(criteriaBuilder.and(criteriaList.toArray(new Predicate[0])));
        final TypedQuery<ExtGraph> query = getEntityManager().createQuery(criteriaQuery);

        return query.getResultList();
    }
 

import javax.persistence.criteria.Join; //導入方法依賴的package包/類
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();
}
 

import javax.persistence.criteria.Join; //導入方法依賴的package包/類
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);
    }
}
 

import javax.persistence.criteria.Join; //導入方法依賴的package包/類
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);
    }
}
 

import javax.persistence.criteria.Join; //導入方法依賴的package包/類
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);
    }
}
 

import javax.persistence.criteria.Join; //導入方法依賴的package包/類
@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;
}
 

import javax.persistence.criteria.Join; //導入方法依賴的package包/類
@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());
}
 

import javax.persistence.criteria.Join; //導入方法依賴的package包/類
/**
 * 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();
}
 

import javax.persistence.criteria.Join; //導入方法依賴的package包/類
@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;
}
 

import javax.persistence.criteria.Join; //導入方法依賴的package包/類
@Override
public boolean containsDataFiles(String identifier, String formatPuid) {
    CriteriaQuery<Long> query = criteriaBuilder.createQuery(Long.class);
    Root<DigitalObject> root = query.from(DigitalObject.class);

    Join<DigitalObject, Identifier> joinIdentifier = root.join(DigitalObject_.defaultIdentifier);
    Join<DigitalObject, ContentVersion> joinVersion = root.join(DigitalObject_.currentVersion);
    Join<ContentVersion, DataFileVersion> joinFileVersions = joinVersion.join(ContentVersion_.files);
    Join<DataFileVersion, DataFile> joinFiles = joinFileVersions.join(DataFileVersion_.dataFile);
    Join<DataFile, DataFileFormat> joinFormat = joinFiles.join(DataFile_.format);

    query.where(criteriaBuilder.and(criteriaBuilder.equal(joinIdentifier.get(Identifier_.identifier), identifier),
        criteriaBuilder.equal(joinFormat.get(DataFileFormat_.puid), formatPuid)));

    query.select(criteriaBuilder.count(root));

    return entityManager.createQuery(query).getSingleResult() > 0;
}
 

import javax.persistence.criteria.Join; //導入方法依賴的package包/類
public List<Product> findForPurchaseCustomerPremium() {
    // main query on products
    CriteriaQuery<Product> query = em.getCriteriaBuilder().createQuery(Product.class);
    Root<Product> from = query.from(Product.class);
    
    // subquery on product ids
    Subquery<Long> subQuery = query.subquery(Long.class);
    Root<Customer> subFrom = subQuery.from(Customer.class);
    Join<Customer, Purchase> joinPurchase = subFrom.join("purchases");
    Join<Purchase, Product> joinProduct = joinPurchase.join("products");
    // Explicitly add to SELECT clause; otherwise, throws Error Code: 30000 SQLSyntaxErrorException
    subQuery.select(joinProduct.get("id").as(Long.class)).distinct(true);
    subQuery.where(em.getCriteriaBuilder().equal(subFrom.get("premium"), true));
    
    query.select(from);
    query.where(em.getCriteriaBuilder().in(from.get("id")).value(subQuery));
    return em.createQuery(query).getResultList();
}
 

import javax.persistence.criteria.Join; //導入方法依賴的package包/類
public List<String> getSportsOfPlayer(String playerId) {
        logger.info("getSportsOfPlayer");
        List<String> sports = new ArrayList<>();

        try {
            CriteriaQuery<String> cq = cb.createQuery(String.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(player.get(Player_.id), playerId));
                cq.select(league.get(League_.sport)).distinct(true);
                TypedQuery<String> q = em.createQuery(cq);
                sports = q.getResultList();
            }

//        Player player = em.find(Player.class, playerId);
//        Iterator<Team> i = player.getTeams().iterator();
//        while (i.hasNext()) {
//            Team team = i.next();
//            League league = team.getLeague();
//            sports.add(league.getSport());
//        }
        } catch (Exception ex) {
            throw new EJBException(ex);
        }
        return sports;
    }
 

import javax.persistence.criteria.Join; //導入方法依賴的package包/類
private static Specification<CalendarEvent> byArea(final String areaId) {
    return (root, query, cb) -> {
        if (areaId == null) {
            return cb.conjunction();
        }

        final Join<CalendarEvent, Organisation> orgJoin = root.join(CalendarEvent_.organisation);
        final Join<Organisation, Organisation> parentOrgJoin = orgJoin.join(Organisation_.parentOrganisation);
        final Predicate thisIsWantedArea = getPredicate(cb, orgJoin, OrganisationType.RKA, areaId);
        final Predicate parentIsWantedArea = getPredicate(cb, parentOrgJoin, OrganisationType.RKA, areaId);
        return cb.or(thisIsWantedArea, parentIsWantedArea);
    };
}
 

import javax.persistence.criteria.Join; //導入方法依賴的package包/類
private void buildForeignECardBuilder(AbstractECardSearchBoxData searchFormData) throws ProcessingException {
  Subquery<Long> subquery = query.subquery(Long.class);
  Root<RtRegistration> subroot = subquery.from(RtRegistration.class);
  Join<RtRegistration, RtEntry> joinEntry = subroot.join(RtRegistration_.rtEntries, JoinType.LEFT);
  Join<RtEntry, RtRace> joinRace = joinEntry.join(RtEntry_.rtRaces, JoinType.LEFT);
  Join<RtRace, RtEcard> joinECard = joinRace.join(RtRace_.rtEcard, JoinType.LEFT);

  JPAECardBoxSearchFormDataStatementBuilder foreignBuilder = new JPAECardBoxSearchFormDataStatementBuilder(joinECard);
  foreignBuilder.build(searchFormData);

  existsInForeignBuilder(subquery, subroot, foreignBuilder);
}