Hibernate Query Conditions – AND, OR, BETWEEN, LIKE, Sort, and Limit
Hibernate's HQL (Hibernate Query Language) supports all the SQL query conditions you're familiar with — AND, OR, BETWEEN, LIKE, ORDER BY, and result limiting — but using Java class and field names instead of table and column names. This tutorial covers each condition type with a complete working example.
Setup
We'll use the Product entity from the previous tutorials. Our sample data:
| id | name | price | quantity |
|----|-------------|---------|----------|
| 1 | Laptop | 75000 | 10 |
| 2 | Smartphone | 25000 | 50 |
| 3 | Tablet | 35000 | 30 |
| 4 | Monitor | 15000 | 20 |
| 5 | Keyboard | 2500 | 100 |
| 6 | Mouse | 1500 | 150 |
| 7 | Headphones | 8000 | 40 |
| 8 | Webcam | 4500 | 60 |
1. AND Condition
try (Session session = HibernateUtil.getSessionFactory().openSession()) {
// Products with price > 10,000 AND quantity > 15
List<Product> results = session.createQuery(
"FROM Product WHERE price > :minPrice AND quantity > :minQty",
Product.class)
.setParameter("minPrice", 10000.0)
.setParameter("minQty", 15)
.list();
System.out.println("=== AND Condition ===");
results.forEach(System.out::println);
// Laptop (75k, 10) – fails qty check
// Smartphone (25k, 50) – passes both
// Tablet (35k, 30) – passes both
// Monitor (15k, 20) – passes both
}
2. OR Condition
try (Session session = HibernateUtil.getSessionFactory().openSession()) {
// Products where price < 3000 OR quantity > 80
List<Product> results = session.createQuery(
"FROM Product WHERE price < :maxPrice OR quantity > :minQty",
Product.class)
.setParameter("maxPrice", 3000.0)
.setParameter("minQty", 80)
.list();
System.out.println("=== OR Condition ===");
results.forEach(System.out::println);
// Keyboard (2500, 100) – matches both
// Mouse (1500, 150) – matches both
}
3. BETWEEN Condition
try (Session session = HibernateUtil.getSessionFactory().openSession()) {
// Products with price between 5000 and 40000 (inclusive)
List<Product> results = session.createQuery(
"FROM Product WHERE price BETWEEN :low AND :high ORDER BY price",
Product.class)
.setParameter("low", 5000.0)
.setParameter("high", 40000.0)
.list();
System.out.println("=== BETWEEN Condition ===");
results.forEach(System.out::println);
// Monitor 15000, Smartphone 25000, Tablet 35000
}
4. LIKE Condition
try (Session session = HibernateUtil.getSessionFactory().openSession()) {
// Products whose name starts with "S" (case-insensitive with lower())
List<Product> startsWithS = session.createQuery(
"FROM Product WHERE lower(name) LIKE :pattern ORDER BY name",
Product.class)
.setParameter("pattern", "s%")
.list();
System.out.println("=== LIKE (starts with S) ===");
startsWithS.forEach(System.out::println);
// Smartphone, ...
// Products whose name contains "top"
List<Product> containsTop = session.createQuery(
"FROM Product WHERE lower(name) LIKE :pattern",
Product.class)
.setParameter("pattern", "%top%")
.list();
System.out.println("\n=== LIKE (contains 'top') ===");
containsTop.forEach(System.out::println);
// Laptop
}
5. Multiple Conditions Together
try (Session session = HibernateUtil.getSessionFactory().openSession()) {
// Products: (price between 5000-50000) AND name NOT LIKE 'S%'
List<Product> results = session.createQuery(
"FROM Product WHERE price BETWEEN :low AND :high " +
"AND lower(name) NOT LIKE :pattern ORDER BY price",
Product.class)
.setParameter("low", 5000.0)
.setParameter("high", 50000.0)
.setParameter("pattern", "s%")
.list();
System.out.println("=== Multiple Conditions ===");
results.forEach(System.out::println);
}
6. ORDER BY (Sort)
try (Session session = HibernateUtil.getSessionFactory().openSession()) {
// Sort by price descending
System.out.println("=== Sorted by Price DESC ===");
session.createQuery("FROM Product ORDER BY price DESC", Product.class)
.list()
.forEach(System.out::println);
// Sort by name ascending
System.out.println("\n=== Sorted by Name ASC ===");
session.createQuery("FROM Product ORDER BY name ASC", Product.class)
.list()
.forEach(System.out::println);
// Sort by two columns: category ASC, price DESC
System.out.println("\n=== Sorted by quantity DESC, name ASC ===");
session.createQuery("FROM Product ORDER BY quantity DESC, name ASC", Product.class)
.list()
.forEach(System.out::println);
}
7. Limit Results (Pagination)
try (Session session = HibernateUtil.getSessionFactory().openSession()) {
// Get top 3 most expensive products
List<Product> top3 = session.createQuery(
"FROM Product ORDER BY price DESC", Product.class)
.setMaxResults(3) // LIMIT 3
.list();
System.out.println("=== Top 3 Most Expensive ===");
top3.forEach(System.out::println);
// Pagination: page 2, 3 results per page
int page = 2;
int pageSize = 3;
List<Product> page2 = session.createQuery(
"FROM Product ORDER BY id", Product.class)
.setFirstResult((page - 1) * pageSize) // OFFSET
.setMaxResults(pageSize) // LIMIT
.list();
System.out.println("\n=== Page 2 (3 per page) ===");
page2.forEach(System.out::println);
}
8. Sort and Limit Combined
try (Session session = HibernateUtil.getSessionFactory().openSession()) {
// Top 5 products by quantity, cheapest price first within same quantity
List<Product> results = session.createQuery(
"FROM Product ORDER BY quantity DESC, price ASC",
Product.class)
.setMaxResults(5)
.list();
System.out.println("=== Sort + Limit ===");
results.forEach(System.out::println);
}
COUNT Query
try (Session session = HibernateUtil.getSessionFactory().openSession()) {
Long count = session.createQuery(
"SELECT COUNT(p) FROM Product p WHERE p.price > :minPrice",
Long.class)
.setParameter("minPrice", 10000.0)
.uniqueResult();
System.out.println("Products above Rs. 10,000: " + count);
}
HQL Quick Reference
| SQL | HQL Equivalent |
|---|---|
| WHERE col = value | WHERE field = :param |
| WHERE col1 = x AND col2 = y | WHERE field1 = :p1 AND field2 = :p2 |
| WHERE col BETWEEN a AND b | WHERE field BETWEEN :a AND :b |
| WHERE col LIKE 'S%' | WHERE field LIKE :pattern |
| ORDER BY col ASC | ORDER BY field ASC |
| LIMIT n | .setMaxResults(n) |
| OFFSET n | .setFirstResult(n) |
| SELECT COUNT(*) | SELECT COUNT(p) FROM Entity p |
Summary
Hibernate HQL supports all the standard query conditions — AND, OR, BETWEEN, LIKE — using Java entity and field names rather than table and column names. Always use named parameters (:param) instead of string concatenation to prevent SQL injection. For sorting, use ORDER BY fieldName ASC/DESC. For pagination, use setFirstResult() for the offset and setMaxResults() for the page size. These are all the building blocks you need to implement search, filter, and paginated list screens in any Java web application.
Comments