Query APIs

/**
 * Base interface for all query types
 * @param <R> the result type
 */
public interface Query<R> extends CommonQueryContract {
    // Result retrieval

    /**
     * Execute the query and return the result list
     * @return list of query results
     */
    List<R> getResultList();

    /**
     * Execute the query and return a single result
     * @return single query result
     * @throws NonUniqueResultException if more than one result
     * @throws NoResultException if no result found
     */
    R getSingleResult();

    /**
     * Execute the query and return a single result or null
     * @return single query result or null if not found
     * @throws NonUniqueResultException if more than one result
     */
    R getSingleResultOrNull();

    /**
     * Execute the query and return the result list (Hibernate-specific)
     * @return list of query results
     */
    List<R> list();

    /**
     * Execute the query and return results as a Stream
     * @return Stream of query results (must be closed after use)
     */
    Stream<R> stream();

    /**
     * Execute the query and return results as a Stream (JPA standard method)
     * @return Stream of query results (must be closed after use)
     */
    Stream<R> getResultStream();

    // Parameter binding

    /**
     * Bind a value to a named parameter
     * @param name the parameter name
     * @param value the parameter value
     * @return this Query for chaining
     */
    <P> Query<R> setParameter(String name, P value);

    /**
     * Bind a value to a positional parameter
     * @param position the parameter position (1-based)
     * @param value the parameter value
     * @return this Query for chaining
     */
    <P> Query<R> setParameter(int position, P value);

    /**
     * Bind a temporal value with specific temporal type
     * @param name the parameter name
     * @param value the temporal value
     * @param temporalType the temporal type
     * @return this Query for chaining
     */
    Query<R> setParameter(String name, Object value, TemporalType temporalType);

    /**
     * Bind multiple parameters from a map
     * @param parameters map of parameter names to values
     * @return this Query for chaining
     */
    Query<R> setParameters(Map<String, Object> parameters);

    // Pagination

    /**
     * Set the position of the first result to retrieve
     * @param startPosition position of the first result (0-based)
     * @return this Query for chaining
     */
    Query<R> setFirstResult(int startPosition);

    /**
     * Set the maximum number of results to retrieve
     * @param maxResult maximum number of results
     * @return this Query for chaining
     */
    Query<R> setMaxResults(int maxResult);

    // Query configuration

    /**
     * Set the query timeout in seconds
     * @param timeout timeout in seconds
     * @return this Query for chaining
     */
    Query<R> setTimeout(int timeout);

    /**
     * Set the fetch size hint
     * @param size the fetch size
     * @return this Query for chaining
     */
    Query<R> setFetchSize(int size);

    /**
     * Set the cache mode for this query
     * @param cacheMode the cache mode
     * @return this Query for chaining
     */
    Query<R> setCacheMode(CacheMode cacheMode);

    /**
     * Enable or disable query result caching
     * @param cacheable whether to cache results
     * @return this Query for chaining
     */
    Query<R> setCacheable(boolean cacheable);

    /**
     * Set the cache region for this query
     * @param region the cache region name
     * @return this Query for chaining
     */
    Query<R> setCacheRegion(String region);
}

/**
 * Interface for selection queries (SELECT statements)
 * @param <R> the result type
 */
public interface SelectionQuery<R> extends Query<R> {
    /**
     * Set the order for the query results
     * @param orderList list of Order instances
     * @return this SelectionQuery for chaining
     */
    SelectionQuery<R> setOrder(List<Order> orderList);

    /**
     * Set the order for the query results
     * @param orders Order instances
     * @return this SelectionQuery for chaining
     */
    SelectionQuery<R> setOrder(Order... orders);

    /**
     * Apply pagination using Page object
     * @param page the page specification
     * @return this SelectionQuery for chaining
     */
    SelectionQuery<R> setPage(Page page);

    /**
     * Get results as a KeyedResultList
     * @param keyFunction function to extract keys from results
     * @return KeyedResultList instance
     */
    <K> KeyedResultList<K, R> getKeyedResultList(Function<R, K> keyFunction);

    /**
     * Execute query and return scrollable results
     * @return ScrollableResults for the query
     */
    ScrollableResults<R> scroll();

    /**
     * Execute query and return scrollable results with scroll mode
     * @param scrollMode the scroll mode to use
     * @return ScrollableResults for the query
     */
    ScrollableResults<R> scroll(ScrollMode scrollMode);
}

/**
 * Interface for native SQL queries
 * @param <R> the result type
 */
public interface NativeQuery<R> extends Query<R> {
    /**
     * Add an entity to the result set
     * @param alias the alias for the entity
     * @param entityClass the entity class
     * @return this NativeQuery for chaining
     */
    NativeQuery<R> addEntity(String alias, Class<?> entityClass);

    /**
     * Add a join to an entity
     * @param alias the alias for the join
     * @param path the property path
     * @return this NativeQuery for chaining
     */
    NativeQuery<R> addJoin(String alias, String path);

    /**
     * Add a scalar column to the result set
     * @param columnAlias the column alias
     * @param type the Hibernate type
     * @return this NativeQuery for chaining
     */
    NativeQuery<R> addScalar(String columnAlias, BasicType<?> type);

    /**
     * Add a scalar column with automatic type detection
     * @param columnAlias the column alias
     * @return this NativeQuery for chaining
     */
    NativeQuery<R> addScalar(String columnAlias);

    /**
     * Set the result set mapping name
     * @param name the result set mapping name
     * @return this NativeQuery for chaining
     */
    NativeQuery<R> setResultSetMapping(String name);

    /**
     * Enable synchronization with named queries
     * @param querySpace the query space
     * @return this NativeQuery for chaining
     */
    NativeQuery<R> addSynchronizedQuerySpace(String querySpace);

    /**
     * Enable synchronization with entity classes
     * @param entityClass the entity class
     * @return this NativeQuery for chaining
     */
    NativeQuery<R> addSynchronizedEntityClass(Class<?> entityClass);
}

/**
 * Interface for mutation queries (UPDATE, DELETE)
 */
public interface MutationQuery extends CommonQueryContract {
    /**
     * Execute the update or delete statement
     * @return number of entities updated or deleted
     */
    int executeUpdate();

    /**
     * Bind a value to a named parameter
     * @param name the parameter name
     * @param value the parameter value
     * @return this MutationQuery for chaining
     */
    <P> MutationQuery setParameter(String name, P value);

    /**
     * Bind a value to a positional parameter
     * @param position the parameter position (1-based)
     * @param value the parameter value
     * @return this MutationQuery for chaining
     */
    <P> MutationQuery setParameter(int position, P value);

    /**
     * Set the query timeout
     * @param timeout timeout in seconds
     * @return this MutationQuery for chaining
     */
    MutationQuery setTimeout(int timeout);
}

/**
 * Interface for building criteria queries
 */
public interface CriteriaBuilder {
    /**
     * Create a CriteriaQuery for the specified result type
     * @param resultClass the result type
     * @return CriteriaQuery instance
     */
    <T> CriteriaQuery<T> createQuery(Class<T> resultClass);

    /**
     * Create a tuple CriteriaQuery
     * @return CriteriaQuery for Tuple results
     */
    CriteriaQuery<Tuple> createTupleQuery();

    /**
     * Create a criteria update query
     * @param targetEntity the entity to update
     * @return CriteriaUpdate instance
     */
    <T> CriteriaUpdate<T> createCriteriaUpdate(Class<T> targetEntity);

    /**
     * Create a criteria delete query
     * @param targetEntity the entity to delete
     * @return CriteriaDelete instance
     */
    <T> CriteriaDelete<T> createCriteriaDelete(Class<T> targetEntity);

    // Predicate construction methods
    Predicate and(Predicate... restrictions);
    Predicate or(Predicate... restrictions);
    Predicate not(Predicate restriction);
    Predicate conjunction();
    Predicate disjunction();

    // Comparison predicates
    Predicate equal(Expression<?> x, Object y);
    Predicate notEqual(Expression<?> x, Object y);
    <Y extends Comparable<? super Y>> Predicate greaterThan(Expression<? extends Y> x, Y y);
    <Y extends Comparable<? super Y>> Predicate lessThan(Expression<? extends Y> x, Y y);
    <Y extends Comparable<? super Y>> Predicate greaterThanOrEqualTo(Expression<? extends Y> x, Y y);
    <Y extends Comparable<? super Y>> Predicate lessThanOrEqualTo(Expression<? extends Y> x, Y y);

    // String predicates
    Predicate like(Expression<String> x, String pattern);
    Predicate notLike(Expression<String> x, String pattern);

    // Collection predicates
    Predicate isEmpty(Expression<? extends Collection<?>> collection);
    Predicate isNotEmpty(Expression<? extends Collection<?>> collection);
    Predicate isMember(Object elem, Expression<Collection<?>> collection);

    // Null predicates
    Predicate isNull(Expression<?> x);
    Predicate isNotNull(Expression<?> x);

    // Aggregate functions
    <N extends Number> Expression<N> sum(Expression<N> x);
    Expression<Long> count(Expression<?> x);
    Expression<Long> countDistinct(Expression<?> x);
    <N extends Number> Expression<N> avg(Expression<N> x);
    <N extends Number> Expression<N> max(Expression<N> x);
    <N extends Number> Expression<N> min(Expression<N> x);

    // Ordering
    Order asc(Expression<?> x);
    Order desc(Expression<?> x);
}

/**
 * Criteria query interface for type-safe querying
 * @param <T> the result type
 */
public interface CriteriaQuery<T> extends AbstractQuery<T> {
    /**
     * Specify the selection
     * @param selection the selection expression
     * @return this CriteriaQuery
     */
    CriteriaQuery<T> select(Selection<? extends T> selection);

    /**
     * Specify multiple selections for tuple results
     * @param selections the selection expressions
     * @return this CriteriaQuery
     */
    CriteriaQuery<T> multiselect(Selection<?>... selections);

    /**
     * Create and add a root entity
     * @param entityClass the entity class
     * @return Root instance
     */
    <X> Root<X> from(Class<X> entityClass);

    /**
     * Specify the where clause
     * @param restriction the restriction predicate
     * @return this CriteriaQuery
     */
    CriteriaQuery<T> where(Predicate... restriction);

    /**
     * Specify group by expressions
     * @param grouping the grouping expressions
     * @return this CriteriaQuery
     */
    CriteriaQuery<T> groupBy(Expression<?>... grouping);

    /**
     * Specify having clause
     * @param restriction the having restriction
     * @return this CriteriaQuery
     */
    CriteriaQuery<T> having(Predicate... restriction);

    /**
     * Specify ordering
     * @param orders the order expressions
     * @return this CriteriaQuery
     */
    CriteriaQuery<T> orderBy(Order... orders);

    /**
     * Specify distinct results
     * @param distinct whether to return distinct results
     * @return this CriteriaQuery
     */
    CriteriaQuery<T> distinct(boolean distinct);

    /**
     * Get the query roots
     * @return set of Root instances
     */
    Set<Root<?>> getRoots();
}

/**
 * Root entity in a criteria query
 * @param <X> the entity type
 */
public interface Root<X> extends From<X, X> {
    /**
     * Create a join to a collection-valued association
     * @param attributeName the attribute name
     * @return Join instance
     */
    <Y> Join<X, Y> join(String attributeName);

    /**
     * Create a join with specified join type
     * @param attributeName the attribute name
     * @param jt the join type
     * @return Join instance
     */
    <Y> Join<X, Y> join(String attributeName, JoinType jt);

    /**
     * Create a fetch join
     * @param attributeName the attribute name
     * @return Fetch instance
     */
    <Y> Fetch<X, Y> fetch(String attributeName);

    /**
     * Create a fetch join with specified join type
     * @param attributeName the attribute name
     * @param jt the join type
     * @return Fetch instance
     */
    <Y> Fetch<X, Y> fetch(String attributeName, JoinType jt);

    /**
     * Get an attribute path
     * @param attributeName the attribute name
     * @return Path to the attribute
     */
    <Y> Path<Y> get(String attributeName);
}

/**
 * Represents an order-by fragment
 */
public class Order {
    /**
     * Create an ascending order
     * @param attribute the attribute name
     * @return Order instance for ascending sort
     */
    public static Order asc(String attribute);

    /**
     * Create a descending order
     * @param attribute the attribute name
     * @return Order instance for descending sort
     */
    public static Order desc(String attribute);

    /**
     * Create an order with explicit direction
     * @param attribute the attribute name
     * @param ascending true for ascending, false for descending
     * @return Order instance
     */
    public static Order by(String attribute, boolean ascending);

    /**
     * Get the attribute name
     * @return the attribute name
     */
    public String getAttributeName();

    /**
     * Check if this is ascending order
     * @return true if ascending
     */
    public boolean isAscending();
}

/**
 * Pagination specification
 */
public class Page {
    /**
     * Create a page specification
     * @param size the page size
     * @param number the page number (0-based)
     * @return Page instance
     */
    public static Page page(int size, int number);

    /**
     * Create first page with given size
     * @param size the page size
     * @return Page instance for first page
     */
    public static Page first(int size);

    /**
     * Get the page size
     * @return the page size
     */
    public int getSize();

    /**
     * Get the page number
     * @return the page number (0-based)
     */
    public int getNumber();

    /**
     * Get the offset for this page
     * @return the offset (0-based)
     */
    public int getOffset();
}

/**
 * Interface for scrollable query results
 * @param <R> the result type
 */
public interface ScrollableResults<R> extends AutoCloseable {
    /**
     * Move to the next result
     * @return true if there is a next result
     */
    boolean next();

    /**
     * Move to the previous result
     * @return true if there is a previous result
     */
    boolean previous();

    /**
     * Move to the first result
     * @return true if there is a first result
     */
    boolean first();

    /**
     * Move to the last result
     * @return true if there is a last result
     */
    boolean last();

    /**
     * Move to a specific position
     * @param position the position to move to
     * @return true if successful
     */
    boolean setRowNumber(int position);

    /**
     * Get the current result
     * @return the current result
     */
    R get();

    /**
     * Get a specific column value
     * @param column the column index
     * @return the column value
     */
    Object get(int column);

    /**
     * Get the current row number
     * @return the current row number
     */
    int getRowNumber();

    /**
     * Close the scrollable results
     */
    void close();
}

import org.hibernate.query.Query;

// Simple selection query
List<User> users = session.createQuery("FROM User u WHERE u.active = :active", User.class)
    .setParameter("active", true)
    .getResultList();

// Query with joins
List<Order> orders = session.createQuery(
    "SELECT o FROM Order o JOIN FETCH o.items WHERE o.customer.name = :customerName",
    Order.class)
    .setParameter("customerName", "John Doe")
    .getResultList();

// Projection query
List<Object[]> results = session.createQuery(
    "SELECT u.name, COUNT(o) FROM User u LEFT JOIN u.orders o GROUP BY u.name")
    .getResultList();

// Paginated query with ordering
List<User> users = session.createQuery("FROM User u", User.class)
    .setOrder(Order.asc("name"), Order.desc("createdDate"))
    .setFirstResult(0)
    .setMaxResults(20)
    .getResultList();

// Using Page object
Page page = Page.page(20, 0); // 20 items per page, first page
List<User> pagedUsers = session.createQuery("FROM User", User.class)
    .setPage(page)
    .getResultList();

// Simple native query
List<User> users = session.createNativeQuery(
    "SELECT * FROM users WHERE created_date > ?1", User.class)
    .setParameter(1, LocalDate.now().minusDays(30))
    .getResultList();

// Complex native query with result mapping
List<Object[]> results = session.createNativeQuery(
    "SELECT u.name, COUNT(o.id) as order_count " +
    "FROM users u LEFT JOIN orders o ON u.id = o.customer_id " +
    "GROUP BY u.id, u.name")
    .addScalar("name", StandardBasicTypes.STRING)
    .addScalar("order_count", StandardBasicTypes.LONG)
    .getResultList();

// Native query with entity result
List<User> users = session.createNativeQuery(
    "SELECT {u.*} FROM users u WHERE u.status = :status", User.class)
    .addEntity("u", User.class)
    .setParameter("status", "ACTIVE")
    .getResultList();

// Update query
int updatedCount = session.createMutationQuery(
    "UPDATE User u SET u.lastLogin = :loginTime WHERE u.id = :userId")
    .setParameter("loginTime", LocalDateTime.now())
    .setParameter("userId", 123L)
    .executeUpdate();

// Bulk delete query
int deletedCount = session.createMutationQuery(
    "DELETE FROM Order o WHERE o.status = :status AND o.createdDate < :cutoffDate")
    .setParameter("status", OrderStatus.CANCELLED)
    .setParameter("cutoffDate", LocalDate.now().minusYears(1))
    .executeUpdate();

// Using named query defined on entity
@Entity
@NamedQuery(name = "User.findByStatus",
            query = "FROM User u WHERE u.status = :status")
public class User {
    // entity definition
}

// Execute named query
List<User> activeUsers = session.createNamedQuery("User.findByStatus", User.class)
    .setParameter("status", UserStatus.ACTIVE)
    .getResultList();

// Process large result set with scrollable results
try (ScrollableResults<User> results = session.createQuery("FROM User", User.class)
        .scroll(ScrollMode.FORWARD_ONLY)) {

    int count = 0;
    while (results.next()) {
        User user = results.get();
        // Process user
        processUser(user);

        // Periodically flush and clear for memory management
        if (++count % 100 == 0) {
            session.flush();
            session.clear();
        }
    }
}

// Enable query result caching
List<User> users = session.createQuery("FROM User u WHERE u.department = :dept", User.class)
    .setParameter("dept", "Engineering")
    .setCacheable(true)
    .setCacheRegion("user-queries")
    .getResultList();

// Set fetch size for better performance
List<Order> orders = session.createQuery("FROM Order o JOIN FETCH o.items", Order.class)
    .setFetchSize(100)
    .getResultList();

// Query timeout
List<User> users = session.createQuery("FROM User", User.class)
    .setTimeout(30) // 30 seconds
    .getResultList();

// Good: Uses parameter binding
Query<User> query = session.createQuery("FROM User u WHERE u.name = :name", User.class)
    .setParameter("name", userName);

// Bad: String concatenation (SQL injection risk)
Query<User> query = session.createQuery("FROM User u WHERE u.name = '" + userName + "'", User.class);

// Type-safe criteria query construction
CriteriaBuilder cb = session.getCriteriaBuilder();
CriteriaQuery<User> cq = cb.createQuery(User.class);
Root<User> user = cq.from(User.class);

// Simple criteria query
cq.select(user)
  .where(cb.and(
      cb.equal(user.get("active"), true),
      cb.like(user.get("name"), "John%")
  ));

List<User> users = session.createQuery(cq).getResultList();

// Complex criteria query with joins
CriteriaQuery<Order> orderQuery = cb.createQuery(Order.class);
Root<Order> order = orderQuery.from(Order.class);
Join<Order, Customer> customer = order.join("customer");

orderQuery.select(order)
    .where(cb.and(
        cb.equal(customer.get("status"), CustomerStatus.ACTIVE),
        cb.greaterThan(order.get("total"), new BigDecimal("100.00"))
    ))
    .orderBy(cb.desc(order.get("createdDate")));

List<Order> orders = session.createQuery(orderQuery).getResultList();

// Criteria update query
CriteriaUpdate<User> updateQuery = cb.createCriteriaUpdate(User.class);
Root<User> userRoot = updateQuery.from(User.class);

updateQuery.set(userRoot.get("lastLogin"), LocalDateTime.now())
    .where(cb.equal(userRoot.get("id"), userId));

int updatedCount = session.createMutationQuery(updateQuery).executeUpdate();