Hibernate HQL

Session session = sessionFactory.openSession();

// SELECT all users
List<User> users = session.createQuery("FROM User", User.class).list();

// SELECT with alias
List<User> users2 = session.createQuery("FROM User u", User.class).list();

// SELECT specific columns (returns Object[])
List<Object[]> names = session.createQuery(
    "SELECT u.firstName, u.email FROM User u", Object[].class).list();

// WHERE clause
List<User> admins = session.createQuery(
    "FROM User u WHERE u.role = 'ADMIN'", User.class).list();

// WHERE with AND/OR
List<User> result = session.createQuery(
    "FROM User u WHERE u.age >= 18 AND u.active = true", User.class).list();

// ORDER BY
List<User> sorted = session.createQuery(
    "FROM User u ORDER BY u.lastName ASC, u.firstName ASC", User.class).list();

// LIKE
List<User> matching = session.createQuery(
    "FROM User u WHERE u.email LIKE '%@gmail.com'", User.class).list();

// IN
List<User> inList = session.createQuery(
    "FROM User u WHERE u.role IN ('ADMIN', 'MODERATOR')", User.class).list();

// BETWEEN
List<User> ageRange = session.createQuery(
    "FROM User u WHERE u.age BETWEEN 20 AND 30", User.class).list();

// IS NULL / IS NOT NULL
List<User> noEmail = session.createQuery(
    "FROM User u WHERE u.email IS NULL", User.class).list();

session.close();

Session session = sessionFactory.openSession();

// Named parameters (preferred - prevents SQL injection)
List<User> users = session.createQuery(
    "FROM User u WHERE u.email = :email AND u.role = :role", User.class)
    .setParameter("email", "alice@example.com")
    .setParameter("role", "ADMIN")
    .list();

// Positional parameters (legacy)
List<User> users2 = session.createQuery(
    "FROM User u WHERE u.email = ?1", User.class)
    .setParameter(1, "alice@example.com")
    .list();

// Aggregate functions
Long count = session.createQuery(
    "SELECT COUNT(u) FROM User u", Long.class).uniqueResult();

Double avgAge = session.createQuery(
    "SELECT AVG(u.age) FROM User u", Double.class).uniqueResult();

// GROUP BY
List<Object[]> roleCount = session.createQuery(
    "SELECT u.role, COUNT(u) FROM User u GROUP BY u.role", Object[].class).list();

// Pagination
List<User> page = session.createQuery("FROM User u ORDER BY u.id", User.class)
    .setFirstResult(0)   // Offset (0-based)
    .setMaxResults(10)   // Limit
    .list();

// UPDATE query
int updated = session.createMutationQuery(
    "UPDATE User u SET u.active = false WHERE u.lastLogin < :date")
    .setParameter("date", java.time.LocalDate.now().minusYears(1))
    .executeUpdate();

// DELETE query
int deleted = session.createMutationQuery(
    "DELETE FROM User u WHERE u.active = false")
    .executeUpdate();

session.close();

// Define named queries on the entity class
@Entity
@NamedQueries({
    @NamedQuery(
        name = "User.findByEmail",
        query = "FROM User u WHERE u.email = :email"
    ),
    @NamedQuery(
        name = "User.findActiveUsers",
        query = "FROM User u WHERE u.active = true ORDER BY u.username"
    ),
    @NamedQuery(
        name = "User.countByRole",
        query = "SELECT COUNT(u) FROM User u WHERE u.role = :role"
    )
})
@NamedNativeQuery(
    name = "User.findByEmailNative",
    query = "SELECT * FROM users WHERE email = :email",
    resultClass = User.class
)
public class User { ... }

// Use named queries
Session session = sessionFactory.openSession();

User user = session.createNamedQuery("User.findByEmail", User.class)
        .setParameter("email", "alice@example.com")
        .uniqueResult();

List<User> activeUsers = session.createNamedQuery("User.findActiveUsers", User.class)
        .list();

Long adminCount = session.createNamedQuery("User.countByRole", Long.class)
        .setParameter("role", "ADMIN")
        .uniqueResult();

session.close();