Java 类javax.persistence.Tuple 实例源码

@Override
public FindOIDsResult<O> findAll(final UserContext userContext) {
    CriteriaBuilder builder = _entityManager.getCriteriaBuilder();

    // use projections to return ONLY the oid (see http://stackoverflow.com/questions/12618489/jpa-criteria-api-select-only-specific-columns)
    CriteriaQuery<Tuple> query = builder.createTupleQuery();
    Root<DB> root = query.from(_DBEntityType);
    if (Facetables.hasFacet(_modelObjectType,HasVersionableFacet.class)) {
        query.multiselect(root.get("_oid"),
                          root.get("_version"));
    } else {
        query.multiselect(root.get("_oid"));
    }
    List<Tuple> tupleResult = _entityManager.createQuery(query)
                                                    .setHint(QueryHints.READ_ONLY,HintValues.TRUE)
                                            .getResultList();
    FindOIDsResult<O> outOids = _buildResultsFromOids(userContext,
                                                      tupleResult);
    return outOids;
}

@SuppressWarnings("rawtypes")
private static Object getValue(String propertyName, Object obj) {
    if (obj instanceof Map) {
          return ((Map) obj).get(propertyName);
       } else if (obj instanceof Tuple) {
           return ((Tuple) obj).get(propertyName);
       } else if (obj != null) {
           PropertyDescriptor pd = BeanUtils.getPropertyDescriptor(obj.getClass(), propertyName);
           try {
              return pd.getReadMethod().invoke(obj, new Object[]{});
           } catch (Exception e) {
               e.printStackTrace();
           }
       }
    return null;
}

@SuppressWarnings("rawtypes")
public LinqImpl(Class<?> domainClass, Class<?> resultClass, EntityManager entityManager) {
    super(domainClass, entityManager);
    if (Tuple.class.isAssignableFrom(resultClass)) {
        criteria = cb.createTupleQuery();
        root = criteria.from(domainClass);
    } else if (Map.class.isAssignableFrom(resultClass)) {
        criteria = cb.createQuery(Object[].class);
        root = criteria.from(domainClass);
        resultTransformer = Transformers.ALIAS_TO_MAP;
        Set<?> attrs = em.getMetamodel().entity(domainClass).getDeclaredSingularAttributes();
        String[] selections = new String[attrs.size()];
        int i = 0;
        for (Object attr : attrs) {
            selections[i] = ((SingularAttribute)attr).getName();
            i++;
        }
        select(selections);
    } else {
        criteria = cb.createQuery(resultClass);
        root = criteria.from(domainClass);
    }
    this.resultClass = resultClass;
}

@Override
public Optional<SearchResponse> search(final ServiceUser user,
                                       final SearchRequest request){
    final String dataSourceUuid = request.getQuery().getDataSource().getUuid();

    try (final Session session = database.openSession()) {
        final CriteriaBuilder cb = session.getCriteriaBuilder();

        final CriteriaQuery<Tuple> cq = cb.createTupleQuery();
        final Root<T> root = cq.from(this.dtoClass);

        cq.multiselect(this.fields.stream()
                .map(f -> root.get(f.getName()))
                .collect(Collectors.toList()));

        final Predicate requestPredicate = getPredicate(cb, root, request.getQuery().getExpression());
        final Predicate dataSourcePredicate = cb.equal(root.get(QueryableEntity.DATA_SOURCE_UUID), dataSourceUuid);

        cq.where(cb.and(requestPredicate, dataSourcePredicate));
        final List<Tuple> tuples = session.createQuery(cq).getResultList();
        final SearchResponse searchResponse = projectResults(request, tuples);

        return Optional.of(searchResponse);
    }
}

@Test
public void testGroupBy() throws Exception {

    Instance e1 = new Instance();
    e1.setName("testName1");
    e1.setNumber(1);
    e1.setField("A");
    em.persist(e1);

    Instance e2 = new Instance();
    e2.setName("testName2");
    e2.setNumber(2);
    e2.setField("A");
    em.persist(e2);

    JpaELFilterImpl el = new JpaELFilterImpl(em, Instance.class, Tuple.class);
    el.groupBy(new String[]{"field"}, "sum(number)", new String[]{"field"});
    List r = el.getResultList(Integer.MAX_VALUE, 0);

    assertNotNull(r);
    assertEquals(1, r.size());
    assertEquals("A", ((Tuple) r.get(0)).get(0));
    assertEquals(3, ((Tuple) r.get(0)).get(1));
}

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

/**
 * {@inheritDoc}
 */
@Override
public <T extends BaseEntity> List<Tuple> findByTupleQuery(TupleQueryCriteria<T> criteria) {
    EntityManager em = this.emf.createEntityManager();
    try {
        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<Tuple> cq = cb.createTupleQuery();
        Root<T> root = cq.from(criteria.getEntity());
        return em.createQuery(cq.multiselect(criteria.getSelections()
                .stream()
                .map(root::get)
                .collect(Collectors.toList())
                .toArray(new Selection[LEN_ZERO]))
                .where(cb.and(JpaUtil.getPredicates(criteria.getCriteriaAttributes(), cb, root))))
                .getResultList();
    } catch (RuntimeException ex) {
        LOGGER.error(ex.getMessage(), ex);
        throw new PersistenceException(ex.getMessage(), ex);
    } finally {
        JpaUtil.closeEntityManager(em);
    }
}

@Test
public void selectTuples() {
    log.info("... selectTuples ...");

    EntityManager em = emf.createEntityManager();
    em.getTransaction().begin();

    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Tuple> q = cb.createTupleQuery();
    Root<Author> author = q.from(Author.class);
    q.multiselect(author.get(Author_.firstName).alias("firstName"), 
                    author.get(Author_.lastName).alias("lastName"));

    TypedQuery<Tuple> query = em.createQuery(q);
    List<Tuple> authorNames = query.getResultList();

    for (Tuple authorName : authorNames) {
        log.info(authorName.get("firstName") + " "
                + authorName.get("lastName"));
    }

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

@Test
public void callSizeFunction() {
    log.info("... callSizeFunction ...");

    EntityManager em = emf.createEntityManager();
    em.getTransaction().begin();

    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Tuple> cq = cb.createTupleQuery();
    Root<Author> root = cq.from(Author.class);
    cq.multiselect(root, cb.size(root.get(Author_.books)));
    cq.groupBy(root.get(Author_.id));

    TypedQuery<Tuple> q = em.createQuery(cq);
    List<Tuple> results = q.getResultList();

    for (Tuple r :  results) {
        log.info(r.get(0) + " wrote " +  r.get(1) + " books.");
    }

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

@Test
public void getBooks() {
    log.info("... getBooks ...");

    EntityManager em = emf.createEntityManager();
    em.getTransaction().begin();

    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Tuple> cq = cb.createTupleQuery();
    Root<Book> root = cq.from(Book.class);
    cq.multiselect(root.get(Book_.title), root.get(Book_.publishingDate));

    List<Tuple> results = em.createQuery(cq).getResultList();

    for (Tuple r : results) {
        log.info(r.get(0) + " was published on " + r.get(1));
    }

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

private <R> R getSecureResult(R result) {
    if (result == null) {
        return null;
    }
    if (isSimplePropertyType(result.getClass())) {
        return result;
    }
    if (result instanceof Tuple) {
        return (R)new SecureTuple((Tuple)result);
    }
    if (!(result instanceof Object[])) {
        return result;
    }
    Object[] scalarResult = (Object[])result;
    List<Object> entitiesToIgnore = new ArrayList<Object>();
    for (int i = 0; i < scalarResult.length; i++) {
        if (scalarResult[i] != null && !isSimplePropertyType(scalarResult[i].getClass())) {
            entitiesToIgnore.add(scalarResult[i]);
        }
    }
    DefaultAccessManager.Instance.get().ignoreChecks(AccessType.READ, entitiesToIgnore);
    return (R)scalarResult;
}

@Test
public void shouldUseTuple(){

            String searchName = "borowiec2";
            CriteriaBuilder cb = em.getCriteriaBuilder();
            //CriteriaQuery<Object[]> c = cb.createQuery(Object[].class); //<4>
            CriteriaQuery<Tuple> criteria = cb.createQuery(Tuple.class); //<1>
            Root<Person> root = criteria.from(Person.class);//<2>
            ParameterExpression<String> lastNameParameter = cb.parameter(String.class,"lastName");
            criteria.multiselect(root.get("firstName"), root.get("version")).where(cb.equal(root.get("lastName"),lastNameParameter));

            List<Tuple> tupleResult = em.createQuery(criteria).setParameter("lastName", searchName).getResultList();//<3>
            for (Tuple t : tupleResult) {
               log.info("fistName : {} , version : {} ",t.get(0),t.get(1));
            }
            Assertions.assertThat(tupleResult.size()).isEqualTo(1);
}

/**
 * JAVADOC Method Level Comments
 *
 * @param input JAVADOC.
 *
 * @return JAVADOC.
 */
protected Collection<ProcessToken> populate(Collection<Tuple> input) {
    Collection<ProcessToken> result = new ArrayList<ProcessToken>();

    for (Tuple tuple : input) {
        ProcessToken wt = (ProcessToken) tuple.get(0);
        PersistableEntity pe = (PersistableEntity) tuple.get(1);

        Assert.notNull(wt, "token should not be null");
        Assert.notNull(pe, "domain should not be null");
        wt.setDomainObject(pe);
        result.add(wt);
    }

    return result;
}

/**
 * JAVADOC Method Level Comments
 *
 * @param wfid JAVADOC.
 *
 * @return JAVADOC.
 */
@Override
public Collection<ProcessToken> findByProcessDefinitionId(String wfid) {
    CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    CriteriaQuery<Tuple> tcq = cb.createTupleQuery();
    Root<ProcessToken> rt = tcq.from(ProcessToken.class);
    Predicate tokp = cb.equal(rt.get("processDefinitionId"), wfid);
    Path<Object> pid = rt.get("domainObjectId");

    Root<EntityDescriptor> rc = tcq.from(EntityDescriptor.class);
    Predicate clap = cb.equal(pid, rc.get("localId"));

    tcq.multiselect(rt, rc).where(cb.and(clap, tokp));

    Collection<Tuple> results = entityManager.createQuery(tcq).getResultList();

    if ((results == null) || (results.size() == 0)) {
        LOG.warn("Failed to find workflow instances for the workflowId:" + wfid);

        return Collections.emptyList();
    }

    return populate(results);
}

@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 Map<Long, Long> countAllGroupByOwner() {
    CriteriaQuery<Tuple> query = criteriaBuilder.createTupleQuery();
    Root<T> root = query.from(clazz);
    Join<?, ContentVersion> joinVersion = joinVersions(root);
    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<T> root = query.from(clazz);
    Root<DigitalObject> objectRoot = query.from(DigitalObject.class);

    Subquery<T> subquery = query.subquery(clazz);
    Root<T> subqueryRoot = subquery.from(clazz);
    Join<?, ContentVersion> joinVersion = joinVersions(subqueryRoot);
    Join<ContentVersion, DigitalObject> joinObject = joinVersion.join(ContentVersion_.object);
    subquery.where(criteriaBuilder.and(criteriaBuilder.equal(joinObject, objectRoot),
        criteriaBuilder.equal(root, subqueryRoot)));
    subquery.select(subqueryRoot);

    query.where(criteriaBuilder.exists(subquery));
    query.groupBy(objectRoot.get(DigitalObject_.ownerId));
    query.multiselect(objectRoot.get(DigitalObject_.ownerId), criteriaBuilder.sum(root.get(MetadataFile_.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;
}

/**
 * Returns a map containing metadata formats and the number of metadata files using them.
 * 
 * Only the formats that have a non-zero metadata file count are included in the map.
 * 
 * @param types
 *            the types of metadata files to be taken into account
 * @return a <format type, number of metadata files using it> map
 */
protected Map<NamespaceType, Long> getCounts(Collection<MetadataType> types) {
    CriteriaQuery<Tuple> query = criteriaBuilder.createTupleQuery();
    Root<MetadataFile> root = query.from(MetadataFile.class);
    Join<MetadataFile, MetadataNamespace> joinNamespace = root.join(MetadataFile_.usedNamespaces);

    query.where(root.get(MetadataFile_.type).in(types));

    query.groupBy(joinNamespace.get(MetadataNamespace_.type));

    query.multiselect(joinNamespace.get(MetadataNamespace_.type), criteriaBuilder.count(root));

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

    return results;
}

/**
 * Gets a list of notification registration keys from the list of tuples that contain notification registration name and namespace columns.
 *
 * @param tuples the list tof tuples that contain notification registration name and namespace columns
 * @param notificationRegistrationNamespaceColumn the column that contains the namespace of the notification registration
 * @param notificationRegistrationNameColumn the column that contains the name of the notification registration
 *
 * @return the list of notification registration keys
 */
protected List<NotificationRegistrationKey> getNotificationRegistrationKeys(List<Tuple> tuples, Path<String> notificationRegistrationNamespaceColumn,
    Path<String> notificationRegistrationNameColumn)
{
    List<NotificationRegistrationKey> notificationRegistrationKeys = new ArrayList<>();

    // Populate the "keys" objects from the returned tuples (i.e. 1 tuple for each row).
    for (Tuple tuple : tuples)
    {
        NotificationRegistrationKey notificationRegistrationKey = new NotificationRegistrationKey();
        notificationRegistrationKeys.add(notificationRegistrationKey);
        notificationRegistrationKey.setNamespace(tuple.get(notificationRegistrationNamespaceColumn));
        notificationRegistrationKey.setNotificationName(tuple.get(notificationRegistrationNameColumn));
    }

    return notificationRegistrationKeys;
}

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);
    ListJoin<Customer, Purchase> joinPurchase = from.join(Customer_.purchases);
    ListJoin<Purchase, Product> joinProduct = joinPurchase.join(Purchase_.products);
    query.multiselect(from.get(BaseModel_.id), em.getCriteriaBuilder().sum(joinProduct.get(Product_.price)));
    query.groupBy(from.get(BaseModel_.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;
}

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

@Transactional(readOnly = true)
private List<DetachNicFromVmMsg> getVmNicDetachMsgs(List<L2NetworkDetachStruct> structs) {
    List<DetachNicFromVmMsg> dmsgs  = new ArrayList<>();

    for (L2NetworkDetachStruct s : structs) {
        String sql = "select vm.uuid, nic.uuid from VmInstanceVO vm, VmNicVO nic, L3NetworkVO l3"
                + " where vm.clusterUuid = :clusterUuid"
                + " and l3.l2NetworkUuid = :l2NetworkUuid"
                + " and nic.l3NetworkUuid = l3.uuid "
                + " and nic.vmInstanceUuid = vm.uuid";
        TypedQuery<Tuple> q = dbf.getEntityManager().createQuery(sql, Tuple.class);
        q.setParameter("clusterUuid", s.getClusterUuid());
        q.setParameter("l2NetworkUuid", s.getL2NetworkUuid());
        dmsgs.addAll(q.getResultList().stream().map((t) -> {
            DetachNicFromVmMsg msg = new DetachNicFromVmMsg();
            msg.setVmInstanceUuid(t.get(0, String.class));
            msg.setVmNicUuid(t.get(1, String.class));
            bus.makeTargetServiceIdByResourceUuid(msg, VmInstanceConstant.SERVICE_ID, msg.getVmInstanceUuid());
            return msg;
        }).collect(Collectors.toList()));
    }

    return dmsgs;
}

@Transactional(readOnly = true)
private void validate(APIDetachL3NetworkFromVmMsg msg) {
    String sql = "select vm.uuid, vm.type, vm.state from VmInstanceVO vm, VmNicVO nic where vm.uuid = nic.vmInstanceUuid and nic.uuid = :uuid";
    TypedQuery<Tuple> q = dbf.getEntityManager().createQuery(sql, Tuple.class);
    q.setParameter("uuid", msg.getVmNicUuid());
    Tuple t = q.getSingleResult();
    String vmUuid = t.get(0, String.class);
    String vmType = t.get(1, String.class);
    VmInstanceState state = t.get(2, VmInstanceState.class);

    if (!VmInstanceState.Running.equals(state) && !VmInstanceState.Stopped.equals(state)) {
        throw new ApiMessageInterceptionException(operr("unable to detach a L3 network. The vm[uuid: %s] is not Running or Stopped; the current state is %s",
                        msg.getVmInstanceUuid(), state));
    }

    msg.setVmInstanceUuid(vmUuid);
}

@Transactional(readOnly = true)
public long getUsedDataVolumeCount(String accountUuid) {
    String sql = "select count(vol)" +
            " from VolumeVO vol, AccountResourceRefVO ref " +
            " where vol.type = :vtype" +
            " and ref.resourceUuid = vol.uuid " +
            " and ref.accountUuid = :auuid" +
            " and ref.resourceType = :rtype" +
            " and vol.status != :status ";
    TypedQuery<Tuple> volq = dbf.getEntityManager().createQuery(sql, Tuple.class);
    volq.setParameter("auuid", accountUuid);
    volq.setParameter("rtype", VolumeVO.class.getSimpleName());
    volq.setParameter("vtype", VolumeType.Data);
    volq.setParameter("status", VolumeStatus.Deleted);
    Long n = volq.getSingleResult().get(0, Long.class);
    n = n == null ? 0 : n;
    return n;
}

private void findReservedCapacityByHypervisorType() {
    SimpleQuery<HostVO> hq = dbf.createQuery(HostVO.class);
    hq.select(HostVO_.uuid, HostVO_.hypervisorType);
    hq.add(HostVO_.uuid, Op.IN, hostUuids);
    hq.add(HostVO_.state,Op.EQ, HostState.Enabled);
    hq.add(HostVO_.status,Op.EQ, HostStatus.Connected);
    List<Tuple> tuples = hq.listTuple();

    for (Tuple t : tuples) {
        String huuid = t.get(0, String.class);
        String hvType = t.get(1, String.class);

        HostReservedCapacityExtensionPoint ext = exts.get(hvType);
        if (ext == null) {
            continue;
        }

        ReservedHostCapacity hc = result.get(huuid);
        if (hc.getReservedMemoryCapacity() == -1) {
            hc.setReservedMemoryCapacity(ext.getReservedHostCapacity().getReservedMemoryCapacity());
        }
        if (hc.getReservedCpuCapacity() == -1) {
            hc.setReservedCpuCapacity(ext.getReservedHostCapacity().getReservedCpuCapacity());
        }
    }
}

@Transactional(readOnly = true)
public Map<String, String> findByL3Uuids(Collection<String> l3Uuids) {
    String sql = "select t.tag, l3.uuid" +
            " from SystemTagVO t, L3NetworkVO l3" +
            " where t.resourceType = :ttype" +
            " and t.tag like :tag" +
            " and t.resourceUuid = l3.l2NetworkUuid" +
            " and l3.uuid in (:l3Uuids)" +
            " group by l3.uuid";
    TypedQuery<Tuple> tq = dbf.getEntityManager().createQuery(sql, Tuple.class);
    tq.setParameter("tag", TagUtils.tagPatternToSqlPattern(KVMSystemTags.L2_BRIDGE_NAME.getTagFormat()));
    tq.setParameter("l3Uuids", l3Uuids);
    tq.setParameter("ttype", L2NetworkVO.class.getSimpleName());
    List<Tuple> ts = tq.getResultList();

    Map<String, String> bridgeNames = new HashMap<>();
    for (Tuple t : ts) {
        String brToken = t.get(0, String.class);
        String l3Uuid = t.get(1, String.class);
        bridgeNames.put(l3Uuid, KVMSystemTags.L2_BRIDGE_NAME.getTokenByTag(brToken, KVMSystemTags.L2_BRIDGE_NAME_TOKEN));
    }

    return bridgeNames;
}

@Transactional
private Map<String, String> getPublicL3BridgeNamesByVipUuids(List<String> vipsUuids) {
    String sql = "select l3.uuid, vip.uuid from L3NetworkVO l3, VipVO vip where vip.l3NetworkUuid = l3.uuid and vip.uuid in (:uuids)";
    TypedQuery<Tuple> q = dbf.getEntityManager().createQuery(sql, Tuple.class);
    q.setParameter("uuids", vipsUuids);
    List<Tuple> ts = q.getResultList();
    Map<String, String> vipL3 = new HashMap<String, String>();
    for (Tuple t : ts) {
        String l3Uuid = t.get(0, String.class);
        String vipUuid = t.get(1, String.class);
        vipL3.put(vipUuid, l3Uuid);
    }

    DebugUtils.Assert(!vipL3.isEmpty(), "how can we get an empty public L3Network list?");

    Map<String, String> brNames = new BridgeNameFinder().findByL3Uuids(vipL3.values());
    Map<String, String> vipBr = new HashMap<String, String>();
    for (Map.Entry<String, String> e : vipL3.entrySet()) {
        vipBr.put(e.getKey(), brNames.get(e.getValue()));
    }
    return vipBr;
}

@Override
public RangeSet getVipUsePortRange(String vipUuid, String protocol, VipUseForList useForList){
    RangeSet portRangeList = new RangeSet();
    List<RangeSet.Range> portRanges = new ArrayList<RangeSet.Range>();

    if (protocol.toUpperCase().equals(PortForwardingProtocolType.UDP.toString()) || protocol.toUpperCase().equals(PortForwardingProtocolType.TCP.toString())) {
        List<Tuple> pfPortList = Q.New(PortForwardingRuleVO.class).select(PortForwardingRuleVO_.vipPortStart, PortForwardingRuleVO_.vipPortEnd)
                .eq(PortForwardingRuleVO_.vipUuid, vipUuid).eq(PortForwardingRuleVO_.protocolType, PortForwardingProtocolType.valueOf(protocol.toUpperCase())).listTuple();
        Iterator<Tuple> it = pfPortList.iterator();
        while (it.hasNext()){
            Tuple strRange = it.next();
            int start = strRange.get(0, Integer.class);
            int end = strRange.get(1, Integer.class);

            RangeSet.Range range = new RangeSet.Range(start, end);
            portRanges.add(range);
        }
    }

    portRangeList.setRanges(portRanges);
    return portRangeList;
}

public Map<String, List<String>> getTags(List<String> resourceUuids, Class resourceClass) {
    SimpleQuery<SystemTagVO> q = dbf.createQuery(SystemTagVO.class);
    q.select(SystemTagVO_.tag, SystemTagVO_.resourceUuid);
    q.add(SystemTagVO_.resourceType, Op.EQ, resourceClass.getSimpleName());
    q.add(SystemTagVO_.resourceUuid, Op.IN, resourceUuids);
    q.add(SystemTagVO_.tag, useOp(), useTagFormat());
    List<Tuple> ts = q.listTuple();
    Map<String, List<String>> ret = new HashMap<>();
    for (Tuple t : ts) {
        String uuid = t.get(1, String.class);
        List<String> tags = ret.get(uuid);
        if (tags == null) {
            tags = new ArrayList<>();
            ret.put(uuid, tags);
        }
        tags.add(t.get(0, String.class));
    }
    return ret;
}

@Transactional(readOnly = true)
private List<ImageDeletionStruct> getImageOnBackupStorage(List<String> bsUuids) {
    String sql = "select ref.backupStorageUuid, img from ImageVO img, ImageBackupStorageRefVO ref where img.uuid = ref.imageUuid and ref.backupStorageUuid in (:bsUuids) group by img.uuid";
    TypedQuery<Tuple> q = dbf.getEntityManager().createQuery(sql, Tuple.class);
    q.setParameter("bsUuids", bsUuids);
    List<Tuple> ts = q.getResultList();

    Map<String, ImageDeletionStruct> tmp = new HashMap<String, ImageDeletionStruct>();
    for (Tuple t : ts) {
        String bsUuid = t.get(0, String.class);
        ImageVO img = t.get(1, ImageVO.class);
        ImageDeletionStruct struct = tmp.get(img.getUuid());
        if (struct == null) {
            struct = new ImageDeletionStruct();
            struct.setImage(ImageInventory.valueOf(img));
            struct.setBackupStorageUuids(new ArrayList<String>());
            tmp.put(img.getUuid(), struct);
        }
        struct.getBackupStorageUuids().add(bsUuid);
    }

    List<ImageDeletionStruct> structs = new ArrayList<ImageDeletionStruct>();
    structs.addAll(tmp.values());
    return structs;
}

@Transactional
Tuple _findTuple() {
    assert _selects.size() > 1 : String.format("findTuple() needs more than one parameter in Query.Select(), you have put %s parameter in Query.select(..), either correcting the parameter or using find() or findValue()", _selects.size());
    done();
    Tuple ret = null;
    try {
        Query q = _dbf.getEntityManager().createQuery(_query);
        if (limit != null) {
            q.setMaxResults(limit);
        }
        ret = (Tuple)q.getSingleResult();
    } catch (NoResultException e) {
    } catch (EmptyResultDataAccessException e) {
    }
    return ret;
}

@Override
public FindOIDsResult<O> findByCreateDate(final UserContext userContext,
                                          final Range<Date> createDate) {
    CriteriaBuilder builder = _entityManager.getCriteriaBuilder();

    // use projections to return ONLY the oid (see http://stackoverflow.com/questions/12618489/jpa-criteria-api-select-only-specific-columns)
    CriteriaQuery<Tuple> query = builder.createTupleQuery();
    Root<DB> root = query.from(_DBEntityType);
    if (Facetables.hasFacet(_modelObjectType,HasVersionableFacet.class)) {
        query.multiselect(root.get("_oid"),
                          root.get("_version"));
    } else {
        query.multiselect(root.get("_oid"));
    }
    Predicate where = _buildDateRangePredicate(builder,root,"_createDate",
                                               createDate);
    if (where != null) query.where(where);
    List<Tuple> tupleResult = _entityManager.createQuery(query)
                                                    .setHint(QueryHints.READ_ONLY,HintValues.TRUE)
                                            .getResultList();
    FindOIDsResult<O> outOids = _buildResultsFromOids(userContext,
                                                      tupleResult);
    return outOids;
}

@Override
public FindOIDsResult<O> findByLastUpdateDate(final UserContext userContext,
                                              final Range<Date> lastUpdateDate) {
    CriteriaBuilder builder = _entityManager.getCriteriaBuilder();

    // use projections to return ONLY the oid (see http://stackoverflow.com/questions/12618489/jpa-criteria-api-select-only-specific-columns)
    CriteriaQuery<Tuple> query = builder.createTupleQuery();
    Root<DB> root = query.from(_DBEntityType);
    if (Facetables.hasFacet(_modelObjectType,HasVersionableFacet.class)) {
        query.multiselect(root.get("_oid"),
                          root.get("_version"));
    } else {
        query.multiselect(root.get("_oid"));
    }
    Predicate where = _buildDateRangePredicate(builder,root,"_lastUpdateDate",
                                               lastUpdateDate);
    if (where != null) query.where(where);
    List<Tuple> tupleResult = _entityManager.createQuery(query)
                                                    .setHint(QueryHints.READ_ONLY,HintValues.TRUE)
                                            .getResultList();
    FindOIDsResult<O> outOids = _buildResultsFromOids(userContext,
                                                      tupleResult);
    return outOids;
}

@Override
public FindOIDsResult<O> findByCreator(final UserContext userContext,
                                       final UserCode creatorUserCode) {
    CriteriaBuilder builder = _entityManager.getCriteriaBuilder();

    // use projections to return ONLY the oid (see http://stackoverflow.com/questions/12618489/jpa-criteria-api-select-only-specific-columns)
    CriteriaQuery<Tuple> query = builder.createTupleQuery();
    Root<DB> root = query.from(_DBEntityType);
    if (Facetables.hasFacet(_modelObjectType,HasVersionableFacet.class)) {
        query.multiselect(root.get("_oid"),
                          root.get("_version"));
    } else {
        query.multiselect(root.get("_oid"));
    }
    Predicate where = _buildUserPredicate(builder,root,"_creator",
                                          creatorUserCode);
    if (where != null) query.where(where);
    List<Tuple> tupleResult = _entityManager.createQuery(query)
                                                    .setHint(QueryHints.READ_ONLY,HintValues.TRUE)
                                            .getResultList();
    FindOIDsResult<O> outOids = _buildResultsFromOids(userContext,
                                                      tupleResult);
    return outOids;
}

@Override
public FindOIDsResult<O> findByLastUpdator(final UserContext userContext,
                                           final UserCode lastUpdatorUserCode) {
    CriteriaBuilder builder = _entityManager.getCriteriaBuilder();

    // use projections to return ONLY the oid (see http://stackoverflow.com/questions/12618489/jpa-criteria-api-select-only-specific-columns)
    CriteriaQuery<Tuple> query = builder.createTupleQuery();
    Root<DB> root = query.from(_DBEntityType);
    if (Facetables.hasFacet(_modelObjectType,HasVersionableFacet.class)) {
        query.multiselect(root.get("_oid"),
                          root.get("_version"));
    } else {
        query.multiselect(root.get("_oid"));
    }
    Predicate where = _buildUserPredicate(builder,root,"_lastUpdator",
                                          lastUpdatorUserCode);
    if (where != null) query.where(where);
    List<Tuple> tupleResult = _entityManager.createQuery(query)
                                                    .setHint(QueryHints.READ_ONLY,HintValues.TRUE)
                                            .getResultList();
    FindOIDsResult<O> outOids = _buildResultsFromOids(userContext,
                                                      tupleResult);
    return outOids;
}

public List<Tuple> getWinningBidTuple(Long itemId) {
   CriteriaBuilder builder = entityManager.getCriteriaBuilder();
   CriteriaQuery<Tuple> query = builder.createTupleQuery();
   Root<Item> itemRoot = query.from(Item.class);
   Root<Order> orderRoot = query.from(Order.class);
   Root<Bid> bidRoot = query.from(Bid.class);
   Root<BazaarAccount> userRoot = query.from(BazaarAccount.class);
   Join<Order,Bid> j1 = orderRoot.join(Order_.bid);
   Join<Order,Item> j2 = orderRoot.join(Order_.item);
   Join<Order,BazaarAccount> j3 = orderRoot.join(Order_.bidder);
   Path<Long> itemIdPath = itemRoot.get(Item_.itemId);
   Predicate itemPredicate = builder.equal(itemIdPath,itemId);
   query.multiselect( 
           userRoot.get( BazaarAccount_.username ), 
           bidRoot.get( Bid_.bidPrice ),
           itemRoot.get(Item_.itemName) ,
           itemRoot.get(Item_.description));
   TypedQuery<Tuple> q = entityManager.createQuery(query);
   query.where(itemPredicate); 

   List<Tuple> results = q.getResultList();
   for(Tuple result : results) {
       logger.log(Level.INFO, "Item: {0}", result.get(itemRoot.get(Item_.itemName)));
   }
   return q.getResultList();
}

@Test
public void testTuple() {
    doInJPA( entityManager -> {
        List<Tuple> postDTOs = entityManager.createQuery(
            "select " +
            "       p.id as id, " +
            "       p.title as title " +
            "from Post p " +
            "where p.createdOn > :fromTimestamp", Tuple.class)
        .setParameter( "fromTimestamp", Timestamp.from(
            LocalDateTime.of( 2016, 1, 1, 0, 0, 0 ).toInstant( ZoneOffset.UTC ) ))
        .getResultList();

        assertFalse( postDTOs.isEmpty() );

        Tuple postDTO = postDTOs.get( 0 );
        assertEquals( 1L, postDTO.get( "id" ) );
        assertEquals( "High-Performance Java Persistence", postDTO.get( "title" ) );
    } );
}

@Test
public void testTupleNativeQuery() {
    doInJPA( entityManager -> {

        List<Tuple> postDTOs = entityManager.createNativeQuery(
            "SELECT " +
            "       p.id AS id, " +
            "       p.title AS title " +
            "FROM Post p " +
            "WHERE p.created_on > :fromTimestamp", Tuple.class)
        .setParameter( "fromTimestamp", Timestamp.from(
            LocalDateTime.of( 2016, 1, 1, 0, 0, 0 ).toInstant( ZoneOffset.UTC ) ))
        .getResultList();

        assertFalse( postDTOs.isEmpty() );

        Tuple postDTO = postDTOs.get( 0 );
        assertEquals( 1L, ((Number) postDTO.get( "id" )).longValue() );
        assertEquals( "High-Performance Java Persistence", postDTO.get( "title" ) );
    } );
}

public List<Tuple> getTuples() {
    return doInJPA(entityManager -> {
        Class<Post> entityClass = Post.class;
        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        CriteriaQuery<Tuple> query = cb.createTupleQuery();
        Root<?> root = query.from(entityClass);
        query.select(
            cb.tuple(
                root.get("id"),
                cb.function("now", Date.class)
            )
        );

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