Examples with TypedQuery javax.persistence.TypedQuery used on opensource projects

Example 41 with TypedQuery

use of javax.persistence.TypedQuery in project openolat by klemens.

the class SharedWithMeQueries method searchSharedPagesEntries.

public List<AssessedPage> searchSharedPagesEntries(Identity member, SearchSharePagesParameters params) {
    StringBuilder sb = new StringBuilder(2048);
    sb.append("select binder.key,").append("  section.key, section.endDate,").append("  page.key, page.title, page.status, page.lastModified,").append("  body.lastModified,").append("  (select max(part.lastModified) from pfpagepart as part").append("   where part.body.key=body.key").append("  ) as partLastModified,").append("  uinfos.userStatus, uinfos.mark,").append("  owner").append(" from pfbinder as binder").append(" inner join binder.baseGroup as baseGroup").append(" inner join baseGroup.members as ownership on (ownership.role='").append(PortfolioRoles.owner.name()).append("')").append(" inner join ownership.identity as owner").append(" inner join fetch owner.user as owneruser").append(" inner join binder.sections as section").append(" inner join section.pages as page").append(" inner join page.body as body").append(params.isBookmarkOnly() ? " inner" : " left").append(" join pfpageuserinfos as uinfos on (uinfos.page.key=page.key and uinfos.identity.key=:identityKey)").append(" where ");
    if (params.isBookmarkOnly()) {
        sb.append(" uinfos.mark=true and");
    }
    String searchString = params.getSearchString();
    if (StringHelper.containsNonWhitespace(searchString)) {
        searchString = makeFuzzyQueryString(searchString);
        sb.append(" (");
        appendFuzzyLike(sb, "page.title", "searchString", dbInstance.getDbVendor());
        sb.append(") and");
    }
    if (params.getExcludedPageStatus() != null && !params.getExcludedPageStatus().isEmpty()) {
        sb.append(" (page.status is null or page.status not in (:excludedPageStatus)) and");
    }
    if (params.getExcludedPageUserStatus() != null && !params.getExcludedPageUserStatus().isEmpty()) {
        sb.append(" (uinfos.userStatus is null or uinfos.userStatus not in (:excludedPageUserStatus)) and");
    }
    sb.append(" (exists (select membership.key from bgroupmember as membership").append("   where membership.group.key=binder.baseGroup.key and membership.identity.key=:identityKey and membership.role in ('").append(PortfolioRoles.coach.name()).append("','").append(PortfolioRoles.reviewer.name()).append("')").append(" ) or exists (select sectionMembership.key from bgroupmember as sectionMembership").append("   where sectionMembership.group.key=section.baseGroup.key and sectionMembership.identity.key=:identityKey and sectionMembership.role in ('").append(PortfolioRoles.coach.name()).append("','").append(PortfolioRoles.reviewer.name()).append("')").append(" ) or exists (select page.key from pfpage as coachedPage").append("   inner join coachedPage.baseGroup as pageGroup").append("   inner join pageGroup.members as pageMembership on (pageMembership.identity.key=:identityKey and pageMembership.role in ('").append(PortfolioRoles.coach.name()).append("','").append(PortfolioRoles.reviewer.name()).append("'))").append("   where coachedPage.key=page.key").append(" ))");
    TypedQuery<Object[]> query = dbInstance.getCurrentEntityManager().createQuery(sb.toString(), Object[].class).setParameter("identityKey", member.getKey());
    if (StringHelper.containsNonWhitespace(searchString)) {
        query.setParameter("searchString", searchString.toLowerCase());
    }
    if (params.getExcludedPageStatus() != null && !params.getExcludedPageStatus().isEmpty()) {
        List<String> excludedPageStatus = params.getExcludedPageStatus().stream().map(s -> s.name()).collect(Collectors.toList());
        query.setParameter("excludedPageStatus", excludedPageStatus);
    }
    if (params.getExcludedPageUserStatus() != null && !params.getExcludedPageUserStatus().isEmpty()) {
        List<String> excludedPageUserStatus = params.getExcludedPageUserStatus().stream().map(s -> s.name()).collect(Collectors.toList());
        query.setParameter("excludedPageUserStatus", excludedPageUserStatus);
    }
    List<Object[]> objects = query.getResultList();
    List<AssessedPage> items = new ArrayList<>(objects.size());
    for (Object[] object : objects) {
        int pos = 0;
        Long binderKey = (Long) object[pos++];
        // Section key
        pos++;
        Date sectionDate = (Date) object[pos++];
        Long pageKey = (Long) object[pos++];
        String pageTitle = (String) object[pos++];
        PageStatus pageStatus = PageStatus.valueOfOrNull((String) object[pos++]);
        Date pageLastModified = (Date) object[pos++];
        Date bodyLastModified = (Date) object[pos++];
        Date partLastModified = (Date) object[pos++];
        PageUserStatus userStatus = PageUserStatus.valueOfWithDefault((String) object[pos++]);
        Boolean mark = (Boolean) object[pos++];
        Identity owner = (Identity) object[pos++];
        Date lastModified = pageLastModified;
        if (lastModified == null || (lastModified != null && partLastModified != null && partLastModified.after(lastModified))) {
            lastModified = partLastModified;
        }
        if (lastModified == null || (lastModified != null && bodyLastModified != null && bodyLastModified.after(lastModified))) {
            lastModified = bodyLastModified;
        }
        items.add(new AssessedPage(binderKey, sectionDate, pageKey, pageTitle, pageStatus, lastModified, mark, userStatus, owner));
    }
    return items;
}

Example 42 with TypedQuery

use of javax.persistence.TypedQuery in project tests by datanucleus.

the class JPQLQueryTest method testMapJoinEmbeddableWithKEYandVALUE.

/**
 * Test for KEY and VALUE use with Map<String, {embedded}> field.
 */
public void testMapJoinEmbeddableWithKEYandVALUE() {
    if (!storeMgr.getSupportedOptions().contains(StoreManager.OPTION_ORM_EMBEDDED_MAP)) {
        return;
    }
    try {
        EntityManager em = getEM();
        EntityTransaction tx = em.getTransaction();
        try {
            tx.begin();
            MapJoinHolder holder = new MapJoinHolder(3);
            holder.setName("First Holder");
            for (int i = 0; i < 3; i++) {
                MapJoinEmbeddedValue val = new MapJoinEmbeddedValue("Name" + i, "Description" + i);
                holder.getMap3().put("Key" + i, val);
            }
            em.persist(holder);
            em.flush();
            /**
             *This will generate:
             *
             *QueryCompilation:
             *  [result:PrimaryExpression{m3#VALUE}]
             *  [from:ClassExpression(alias=h
             *    join=JoinExpression{JOIN_LEFT_OUTER PrimaryExpression{h.map3} alias=m3
             *    on=DyadicExpression{PrimaryExpression{m3#KEY}  =  ParameterExpression{key}}})]
             *  [symbols:
             *    m3 type=org.datanucleus.samples.annotations.one_many.map_join.MapJoinEmbeddedValue,
             *    m3#VALUE type=org.datanucleus.samples.annotations.one_many.map_join.MapJoinEmbeddedValue,
             *    h type=org.datanucleus.samples.annotations.one_many.map_join.MapJoinHolder,
             *    m3#KEY type=java.lang.String,
             *    key type=unknown]
             *
             *SELECT M3.DESCRIPTION,M3."NAME"
             *FROM JPA_AN_MAPJOINHOLDER H
             *LEFT OUTER JOIN JPA_AN_MAPJOINHOLDER_MAP3 M3 ON H.ID = M3.MAPJOINHOLDER_ID AND M3.MAP3_KEY = ?
             */
            TypedQuery<MapJoinEmbeddedValue> q = em.createQuery("SELECT VALUE(m3) FROM MapJoinHolder h LEFT JOIN h.map3 m3 ON KEY(m3) = :key", MapJoinEmbeddedValue.class);
            q.setParameter("key", "Key1");
            List<MapJoinEmbeddedValue> results = q.getResultList();
            assertNotNull(results);
            assertEquals(1, results.size());
            MapJoinEmbeddedValue resultVal = results.get(0);
            assertEquals("Name1", resultVal.getName());
            assertEquals("Description1", resultVal.getDescription());
            // Try access to a field of the embedded value
            Query q2 = em.createQuery("SELECT VALUE(m3).name FROM MapJoinHolder h LEFT JOIN h.map3 m3 ON KEY(m3) = :key");
            q2.setParameter("key", "Key1");
            List results2 = q2.getResultList();
            assertNotNull(results2);
            assertEquals(1, results2.size());
            assertEquals("Name1", (String) results2.get(0));
            tx.rollback();
        } catch (PersistenceException e) {
            LOG.error("Exception in test", e);
            fail("Exception in test : " + e.getMessage());
        } finally {
            if (tx.isActive()) {
                tx.rollback();
            }
            em.close();
        }
    } finally {
        clean(MapJoinHolder.class);
    }
}

Example 43 with TypedQuery

use of javax.persistence.TypedQuery in project tests by datanucleus.

the class CriteriaStringsTest method testCriteriaAttributeConverterLiteral.

/**
 * Test query of entity with AttributeConverter field using Criteria and a literal of the converter type
 */
public void testCriteriaAttributeConverterLiteral() {
    EntityManager em = getEM();
    EntityTransaction tx = em.getTransaction();
    try {
        tx.begin();
        TypeHolder h = new TypeHolder(1, "First");
        h.setDetails(new ComplicatedType("FirstName", "LastName"));
        em.persist(h);
        em.flush();
        long id = h.getId();
        CriteriaBuilder cb = emf.getCriteriaBuilder();
        CriteriaQuery<TypeHolder> cq = cb.createQuery(TypeHolder.class);
        Root<TypeHolder> root = cq.from(TypeHolder.class);
        cq.where(cb.equal(root.get("details"), cb.literal(new ComplicatedType("FirstName", "LastName"))));
        TypedQuery q = em.createQuery(cq);
        List<TypeHolder> results = q.getResultList();
        assertNotNull(results);
        assertEquals(1, results.size());
        TypeHolder hr = results.get(0);
        assertEquals(id, hr.getId());
        tx.rollback();
    } catch (Exception e) {
        LOG.error("Exception thrown during test", e);
        fail("Exception caught during test : " + e.getMessage());
    } finally {
        if (tx.isActive()) {
            tx.rollback();
        }
        em.close();
    }
}

Example 44 with TypedQuery

use of javax.persistence.TypedQuery in project keycloak by keycloak.

the class JPAResourceServerStore method delete.

@Override
public void delete(ClientModel client) {
    String id = client.getId();
    ResourceServerEntity entity = entityManager.find(ResourceServerEntity.class, id);
    if (entity == null)
        return;
    // This didn't work, had to loop through and remove each policy individually
    // entityManager.createNamedQuery("deletePolicyByResourceServer")
    // .setParameter("serverId", id).executeUpdate();
    {
        TypedQuery<String> query = entityManager.createNamedQuery("findPolicyIdByServerId", String.class);
        query.setParameter("serverId", id);
        List<String> result = query.getResultList();
        for (String policyId : result) {
            entityManager.remove(entityManager.getReference(PolicyEntity.class, policyId));
        }
    }
    {
        TypedQuery<String> query = entityManager.createNamedQuery("findPermissionTicketIdByServerId", String.class);
        query.setParameter("serverId", id);
        List<String> result = query.getResultList();
        for (String permissionId : result) {
            entityManager.remove(entityManager.getReference(PermissionTicketEntity.class, permissionId));
        }
    }
    // entityManager.createNamedQuery("deleteResourceByResourceServer")
    // .setParameter("serverId", id).executeUpdate();
    {
        TypedQuery<String> query = entityManager.createNamedQuery("findResourceIdByServerId", String.class);
        query.setParameter("serverId", id);
        List<String> result = query.getResultList();
        for (String resourceId : result) {
            entityManager.remove(entityManager.getReference(ResourceEntity.class, resourceId));
        }
    }
    // entityManager.createNamedQuery("deleteScopeByResourceServer")
    // .setParameter("serverId", id).executeUpdate();
    {
        TypedQuery<String> query = entityManager.createNamedQuery("findScopeIdByResourceServer", String.class);
        query.setParameter("serverId", id);
        List<String> result = query.getResultList();
        for (String scopeId : result) {
            entityManager.remove(entityManager.getReference(ScopeEntity.class, scopeId));
        }
    }
    this.entityManager.remove(entity);
    entityManager.flush();
    entityManager.detach(entity);
}

Example 45 with TypedQuery

use of javax.persistence.TypedQuery in project keycloak by keycloak.

the class JPAScopeStore method findByResourceServer.

@Override
public List<Scope> findByResourceServer(Map<Scope.FilterOption, String[]> attributes, String resourceServerId, int firstResult, int maxResult) {
    CriteriaBuilder builder = entityManager.getCriteriaBuilder();
    CriteriaQuery<ScopeEntity> querybuilder = builder.createQuery(ScopeEntity.class);
    Root<ScopeEntity> root = querybuilder.from(ScopeEntity.class);
    querybuilder.select(root.get("id"));
    List<Predicate> predicates = new ArrayList();
    predicates.add(builder.equal(root.get("resourceServer").get("id"), resourceServerId));
    attributes.forEach((filterOption, value) -> {
        switch(filterOption) {
            case ID:
                predicates.add(root.get(filterOption.getName()).in(value));
                break;
            case NAME:
                predicates.add(builder.like(builder.lower(root.get(filterOption.getName())), "%" + value[0].toLowerCase() + "%"));
                break;
            default:
                throw new IllegalArgumentException("Unsupported filter [" + filterOption + "]");
        }
    });
    querybuilder.where(predicates.toArray(new Predicate[predicates.size()])).orderBy(builder.asc(root.get("name")));
    TypedQuery query = entityManager.createQuery(querybuilder);
    List result = paginateQuery(query, firstResult, maxResult).getResultList();
    List<Scope> list = new LinkedList<>();
    for (Object id : result) {
        list.add(provider.getStoreFactory().getScopeStore().findById((String) id, resourceServerId));
    }
    return list;
}