Hibernate ORM User Guide

[kuyeol]

Pro Jpa2 : ebook firstCheck! :1:

[!Note]

Criteria API

Code block : Examples

public Stream getGroupMembersStream(RealmModel realm, GroupModel group, String search, Boolean exact, Integer first, Integer max) { TypedQuery query; if (StringUtil.isBlank(search)) { query = em.createNamedQuery("groupMembership", UserEntity.class); } else if (Boolean.TRUE.equals(exact)) { query = em.createNamedQuery("groupMembershipByUser", UserEntity.class); query.setParameter("search", search); } else { query = em.createNamedQuery("groupMembershipByUserContained", UserEntity.class); query.setParameter("search", search.toLowerCase()); } query.setParameter("groupId", group.getId());

    return closing(paginateQuery(query, first, max).getResultStream().map(user -> new UserAdapter(session, realm, em, user)));
}

//!?!?

try (Stream<Object[]> persons = session.createQuery( "select p.name, p.nickName " + "from Person p " + "where p.name like :name", Object[].class) .setParameter("name", "J%") .getResultStream()) { persons.map(row -> new PersonNames((String) row[0], (String) row[1])) .forEach(this::process); }

Example 562. Hibernate getResultStream() with an entity result type

try(Stream persons = session.createQuery( "select p " + "from Person p " + "where p.name like :name", Person.class) .setParameter("name", "J%") .getResultStream()) {
Map<Phone, List> callRegistry = persons.flatMap(person -> person.getPhones().stream()) .flatMap(phone -> phone.getCalls().stream()) .collect(Collectors.groupingBy(Call::getPhone));
process(callRegistry); }

package org.keycloak.models.jpa;

import jakarta.persistence.TypedQuery;

public class PaginationUtils {

    public static final int DEFAULT_MAX_RESULTS = Integer.MAX_VALUE >> 1;

    public static <T> TypedQuery<T> paginateQuery(TypedQuery<T> query, Integer first, Integer max) {
        if (first != null && first >= 0) {
            query = query.setFirstResult(first);

            // Workaround for https://hibernate.atlassian.net/browse/HHH-14295
            if (max == null || max < 0) {
                max = DEFAULT_MAX_RESULTS;
            }
        }

        if (max != null && max >= 0) {
            query = query.setMaxResults(max);
        }

        return query;
    }

}

@Override
    public Stream<UserModel> searchForUserStream(RealmModel realm, String search, Integer firstResult, Integer maxResults) {
        Map<String, String> attributes = new HashMap<>(2);
        attributes.put(UserModel.SEARCH, search);
        attributes.put(UserModel.INCLUDE_SERVICE_ACCOUNT, Boolean.FALSE.toString());

        return searchForUserStream(realm, attributes, firstResult, maxResults);
    }

    @Override
    @SuppressWarnings("unchecked")
    public Stream<UserModel> searchForUserStream(RealmModel realm, Map<String, String> attributes, Integer firstResult, Integer maxResults) {
        CriteriaBuilder builder = em.getCriteriaBuilder();
        CriteriaQuery<UserEntity> queryBuilder = builder.createQuery(UserEntity.class);
        Root<UserEntity> root = queryBuilder.from(UserEntity.class);

        Map<String, String> customLongValueSearchAttributes = new HashMap<>();
        List<Predicate> predicates = predicates(attributes, root, customLongValueSearchAttributes);

        predicates.add(builder.equal(root.get("realmId"), realm.getId()));

        Set<String> userGroups = (Set<String>) session.getAttribute(UserModel.GROUPS);

        if (userGroups != null) {
            groupsWithPermissionsSubquery(queryBuilder, userGroups, root, predicates);
        }

        queryBuilder.where(predicates.toArray(Predicate[]::new)).orderBy(builder.asc(root.get(UserModel.USERNAME)));

        TypedQuery<UserEntity> query = em.createQuery(queryBuilder);

        UserProvider users = session.users();
        return closing(paginateQuery(query, firstResult, maxResults).getResultStream())
                // following check verifies that there are no collisions with hashes
                .filter(predicateForFilteringUsersByAttributes(customLongValueSearchAttributes, JpaHashUtils::compareSourceValueLowerCase))
                .map(userEntity -> users.getUserById(realm, userEntity.getId()))
                .filter(Objects::nonNull);
    }

public UserModel getServiceAccount(ClientModel client) {

        TypedQuery<UserEntity> query = em.createNamedQuery("getRealmUserByServiceAccount", UserEntity.class);

        query.setParameter("realmId", client.getRealm().getId());
        query.setParameter("clientInternalId", client.getId());

        List<UserEntity> results = query.getResultList();

        if (results.isEmpty()) {
            return null;
        } else if (results.size() > 1) {
            throw new IllegalStateException("More service account linked users found for client=" + client.getClientId() +
                    ", results=" + results);
        } else {

            UserEntity user = results.get(0);

            return new UserAdapter(session, client.getRealm(), em, user);
        }

public class PersonWrapper {

    private final Long id;

    private final String nickName;

    public PersonWrapper(Long id, String nickName) {
        this.id = id;
        this.nickName = nickName;
    }

    public Long getId() {
        return id;
    }

    public String getNickName() {
        return nickName;
    }
}

CriteriaBuilder builder = entityManager.getCriteriaBuilder();

CriteriaQuery<PersonWrapper> criteria = builder.createQuery(PersonWrapper.class);
Root<Person> root = criteria.from(Person.class);

Path<Long> idPath = root.get(Person_.id);
Path<String> nickNamePath = root.get(Person_.nickName);

criteria.select(builder.construct(PersonWrapper.class, idPath, nickNamePath));
criteria.where(builder.equal(root.get(Person_.name), "John Doe"));

List<PersonWrapper> wrappers = entityManager.createQuery(criteria).getResultList();

public class PersonSummaryDTO { 
private Number id; private String name; 
//Getters and setters are omitted for brevity 
public Number getId() { return id; } public void setId(Number id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } } List<PersonSummaryDTO> dtos = session.createNativeQuery(  "SELECT p.id as \"id\", p.name as \"name\" " +  "FROM Person p", Tuple.class) .setTupleTransformer(         (tuple, aliases) -> {           PersonSummaryDTO dto = new PersonSummaryDTO();          dto.setId( (Long)tuple[0] );            dto.setName( (String)tuple[1] );            return dto;         } ) .list();

List<Object[]> persons = session.createNativeQuery(     "SELECT * FROM Person", Object[].class) .addScalar("id", StandardBasicTypes.LONG) .addScalar("name", StandardBasicTypes.STRING) .list(); for(Object[] person : persons) {   Long id = (Long) person[0];     String name = (String) person[1]; }

https://docs.jboss.org/hibernate/stable/orm/userguide/html_single/Hibernate_User_Guide.html#sql-scalar-query

https://docs.jboss.org/hibernate/stable/orm/userguide/html_single/Hibernate_User_Guide.html#sql-dto-query

https://docs.jboss.org/hibernate/stable/orm/userguide/html_single/Hibernate_User_Guide.html#:~:text=public%20class%20PersonWrapper,createQuery(criteria).getResultList()%3B

[kuyeol]

6.3.13. GroupBy and Having The groupBy method of the CriteriaQuery interface is used to define a partitioning of the query results into groups. The having method of the CriteriaQuery interface can be used to filter over the groups. The arguments to the groupBy method are Expression instances. When the groupBy method is used, each selection item that is not the result of applying an aggregate method must correspond to a path expression that is used for defining the grouping. Requirements on the types that correspond to the elements of the grouping and having constructs and their relationship to the select items are as specified in Section 4.8. Example:

CriteriaQuery<Tuple> q = cb.createTupleQuery(); Root<Customer> customer = q.from(Customer.class); 
q.groupBy(customer.get(Customer_.status)); q.having(cb.in(customer.get(Customer_.status)).value(1).value(2)); q.select(cb.tuple( 
  customer.get(Customer_.status),   cb.avg(customer.get(Customer_.filledOrderCount)),   cb.count(customer))); 

This query is equivalent to the following Jakarta Persistence query language query:

SELECT c.status, AVG(c.filledOrderCount), COUNT(c) FROM Customer c 
GROUP BY c.status HAVING c.status IN (1, 2)

[kuyeol]

6.3.13. GroupBy and Having 
The groupBy method of the CriteriaQuery interface is used to define a partitioning of the query results into groups. The having method of the CriteriaQuery interface can be used to filter over the groups. 
The arguments to the groupBy method are Expression instances. 
When the groupBy method is used, each selection item that is not the result of applying an aggregate method must correspond to a path expression that is used for defining the grouping. Requirements on the types that correspond to the elements of the grouping and having constructs and their relationship to the select items are as specified in Section 4.8. 
Example: 
CriteriaQuery<Tuple> q = cb.createTupleQuery(); Root<Customer> customer = q.from(Customer.class); 
q.groupBy(customer.get(Customer_.status)); q.having(cb.in(customer.get(Customer_.status)).value(1).value(2)); q.select(cb.tuple( 
  customer.get(Customer_.status),   cb.avg(customer.get(Customer_.filledOrderCount)),   cb.count(customer))); 
This query is equivalent to the following Jakarta Persistence query language query: 
SELECT c.status, AVG(c.filledOrderCount), COUNT(c) FROM Customer c 
GROUP BY c.status HAVING c.status IN (1, 2)

`