📊 Level Up with Hibernate Queries: HQL vs Native SQL — What No One Told You 😲

So, your Hibernate app runs. It saves, updates, deletes.
But what about fetching that specific data?
Like “all users with more than 5 posts”? Or “posts containing the word ‘Java’”?

Today you’ll learn to master Hibernate queries using:

✅ HQL (Hibernate Query Language) — SQL’s cooler, object-oriented cousin
✅ Native SQL — when you want full control (or just can’t resist raw SQL)

Let’s dive in and unlock the real power of Hibernate.

---

🔍 HQL vs Native SQL: Quick Comparison

Feature	HQL	Native SQL
Based On	Java classes & fields	Database tables & columns
Safe & Portable	✅	❌ (DB-specific)
Flexibility	Good	Ultimate control
Performance Tweak	Decent	Fine-grained control

---

🧪 Let’s Build a Demo

We’ll query data from two entities:

• User
• Post

(Already created in Post 4)

---

✅ HQL: Hibernate Query Language (Object-Oriented SQL)

✨ Example 1: Get All Users

String hql = "FROM User";
List<User> users = session.createQuery(hql, User.class).getResultList();

This is the HQL version of SELECT * FROM users, but instead of table names, it uses the class name User.

---

🔍 Example 2: Find User by Name

String hql = "FROM User u WHERE u.name = :name";
User user = session.createQuery(hql, User.class)
                   .setParameter("name", "Devesh")
                   .uniqueResult();

:name is a named parameter — no SQL injection worries here.

---

🧮 Example 3: Count Posts

String hql = "SELECT COUNT(p) FROM Post p";
Long count = session.createQuery(hql, Long.class).uniqueResult();
System.out.println("Total posts: " + count);

🔗 Example 4: Get All Posts for a User

String hql = "FROM Post p WHERE p.user.id = :userId";
List<Post> posts = session.createQuery(hql, Post.class)
                          .setParameter("userId", 1)
                          .getResultList();

🔧 Native SQL: When You Just Gotta Write Raw SQL

🧨 Example 1: Select All Users

List<Object[]> results = session.createNativeQuery("SELECT * FROM users").getResultList();
for (Object[] row : results) {
    System.out.println("ID: " + row[0] + ", Name: " + row[1]);
}

⚡ Example 2: Custom SQL + Entity Mapping

List<User> users = session
    .createNativeQuery("SELECT * FROM users WHERE username LIKE '%esh'", User.class)
    .getResultList();

Hibernate maps each row to a User object — powerful, right?

---

📌 Pro Tip: Mix Native SQL for Performance

Use HQL for 90% of tasks.
But reach for Native SQL when:

• You’re doing JOINs across non-Hibernate-managed tables
• Need DB-specific functions (e.g., LIMIT, ILIKE)
• Chasing performance gains for complex queries

🔒 Bonus: Pagination with HQL

List<Post> pagedPosts = session.createQuery("FROM Post", Post.class)
    .setFirstResult(0) // page 1
    .setMaxResults(10) // 10 per page
    .getResultList();

🧠 Summary

• Use HQL for readable, type-safe queries
• Use Named Parameters (:param) for safety
• Native SQL is your fallback when you need brute-force control
• Hibernate gives you the best of both worlds 🔥