Java.Hibernate.Beginner.How can you use native SQL queries in Hibernate?

You can execute raw SQL queries directly against the database using Hibernate’s native query API, which lets you write standard SQL (not HQL/JPQL) — useful when you need database-specific features or want maximum control.

---

🔹 How to create a native query:
Use Session.createNativeQuery() (Hibernate) or EntityManager.createNativeQuery() (JPA) to run a SQL statement.

---

🔹 Example: fetching results as entities

Session session = sessionFactory.openSession();

String sql = "SELECT * FROM users WHERE username = :username";
User user = session.createNativeQuery(sql, User.class)
                   .setParameter("username", "alice")
                   .uniqueResult();

System.out.println("User email: " + user.getEmail());

session.close();

✅ Here:

• createNativeQuery(sql, User.class) tells Hibernate to map the SQL result rows to your User entity.

🔹 Example: fetching scalar values (plain columns)

Session session = sessionFactory.openSession();

String sql = "SELECT username, email FROM users WHERE active = true";
List<Object[]> results = session.createNativeQuery(sql).list();

for (Object[] row : results) {
    System.out.println("Username: " + row[0] + ", Email: " + row[1]);
}

session.close();

🔹 Example with JPA’s EntityManager

String sql = "SELECT * FROM users WHERE id = ?";
User user = entityManager.createNativeQuery(sql, User.class)
                         .setParameter(1, 1L)
                         .getSingleResult();

🔹 Benefits of native SQL:
✅ Full power of SQL, including vendor-specific functions or hints.
✅ Can call stored procedures.
✅ Useful for reporting queries not easily expressed in HQL/JPQL.

🔹 Drawbacks:
❌ Loses portability — ties your query to the specific database’s SQL dialect.
❌ Bypasses some Hibernate optimizations (e.g., caching or automatic dirty checking).
❌ You’re responsible for correctly mapping results.

✅ Key takeaway:
Use createNativeQuery() when you need raw SQL for advanced or vendor-specific queries — but prefer HQL/JPQL for most CRUD to keep your app database-independent.