When working with Hibernate, developers often use HQL (Hibernate Query Language) or the Criteria API to interact with the database. However, there are situations where you need more control over SQL execution. That’s where Native SQL Queries in Hibernate come in.
Native SQL queries let you run raw SQL directly within Hibernate, giving you full access to database-specific features, performance tuning, and complex queries that are otherwise difficult to express in HQL.
Think of Hibernate as your translator between Java and the database. Normally, it interprets your requests (via HQL or Criteria) into SQL. But sometimes, you want to speak directly in SQL without translation—that’s what native SQL queries provide.
Why Use Native SQL Queries?
- Access database-specific features not supported by HQL.
- Perform complex joins, subqueries, and stored procedure calls.
- Optimize performance-critical queries.
- Migrate legacy SQL queries into a Hibernate-based project.
Setting Up Native SQL Queries
Example Entity
@Entity
@Table(name = "employee")
public class Employee {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String name;
private String department;
private double salary;
// Getters and setters
}
Native SQL Query Example
String sql = "SELECT * FROM employee WHERE department = :dept";
Session session = sessionFactory.openSession();
Query query = session.createNativeQuery(sql, Employee.class);
query.setParameter("dept", "IT");
List<Employee> employees = query.getResultList();
Here we’re mapping the result of a raw SQL query directly to the Employee entity.
CRUD Operations with Native SQL
Insert Example
String sql = "INSERT INTO employee (name, department, salary) VALUES (:name, :dept, :sal)";
Transaction tx = session.beginTransaction();
Query query = session.createNativeQuery(sql);
query.setParameter("name", "John Doe");
query.setParameter("dept", "Finance");
query.setParameter("sal", 60000.0);
query.executeUpdate();
tx.commit();
Update Example
String sql = "UPDATE employee SET salary = :sal WHERE department = :dept";
Transaction tx = session.beginTransaction();
Query query = session.createNativeQuery(sql);
query.setParameter("sal", 75000.0);
query.setParameter("dept", "IT");
query.executeUpdate();
tx.commit();
Delete Example
String sql = "DELETE FROM employee WHERE id = :id";
Transaction tx = session.beginTransaction();
Query query = session.createNativeQuery(sql);
query.setParameter("id", 5L);
query.executeUpdate();
tx.commit();
Mapping Complex Results
You can map SQL results not just to entities, but also to DTOs.
String sql = "SELECT name, salary FROM employee WHERE department = :dept";
List<Object[]> results = session.createNativeQuery(sql).setParameter("dept", "HR").getResultList();
for (Object[] row : results) {
String name = (String) row[0];
Double salary = (Double) row[1];
System.out.println(name + " - " + salary);
}
For advanced mapping, Hibernate supports @SqlResultSetMapping.
Performance Considerations
- Caching: Native queries don’t automatically benefit from Hibernate’s second-level cache.
- Database Portability: Raw SQL may not work across different DB vendors.
- Batch Updates: Use parameterized queries to avoid SQL injection and optimize performance.
Real-World Use Cases
- Calling stored procedures.
- Running vendor-specific SQL functions.
- Migrating legacy SQL-heavy applications to Hibernate.
- Optimizing reporting and analytics queries.
Anti-Patterns and Pitfalls
- Overusing native queries instead of leveraging HQL or Criteria API.
- Forgetting to handle SQL injection (always use parameters).
- Breaking database portability by using vendor-specific syntax unnecessarily.
Best Practices
- Use native queries only when required.
- Always parameterize inputs.
- Prefer entity mappings for results instead of
Object[]. - Combine native queries with Hibernate’s transaction management.
📌 Hibernate Version Notes
Hibernate 5.x
createSQLQuery()was used (now deprecated).- Relied heavily on legacy APIs for SQL query handling.
Hibernate 6.x
- Uses
createNativeQuery()consistently. - Better integration with JPA Query API.
- Support for Jakarta Persistence (namespace changes).
- Enhanced SQL capabilities and type handling.
Integration with Spring Boot
@Repository
public class EmployeeRepository {
@PersistenceContext
private EntityManager entityManager;
public List<Employee> findByDepartment(String dept) {
String sql = "SELECT * FROM employee WHERE department = :dept";
return entityManager.createNativeQuery(sql, Employee.class)
.setParameter("dept", dept)
.getResultList();
}
}
Spring Boot simplifies session and transaction management, making it easier to mix Hibernate with native SQL queries.
Conclusion and Key Takeaways
- Native SQL Queries give you raw SQL power inside Hibernate.
- They’re useful for database-specific features and performance optimizations.
- But don’t overuse them—prefer HQL or Criteria API for portability.
- Always follow best practices to avoid SQL injection and portability issues.
FAQ
Q1: What’s the difference between Hibernate and JPA?
Hibernate is an implementation of JPA, with additional features like caching and Criteria API.
Q2: How does Hibernate caching improve performance?
It avoids hitting the database repeatedly by storing frequently accessed entities in memory.
Q3: What are the drawbacks of eager fetching?
It loads unnecessary data, leading to performance bottlenecks (like the N+1 select problem).
Q4: How do I solve the N+1 select problem in Hibernate?
Use JOIN FETCH, batch fetching, or second-level cache.
Q5: Can I use Hibernate without Spring?
Yes, Hibernate can work standalone, but Spring simplifies configuration and transaction handling.
Q6: What’s the best strategy for inheritance mapping?
It depends on the use case—Single Table is faster, Joined is normalized, Table-per-Class is less common.
Q7: How does Hibernate handle composite keys?
Using @EmbeddedId or @IdClass.
Q8: How is Hibernate 6 different from Hibernate 5?
Hibernate 6 aligns with Jakarta EE namespace and offers improved SQL support.
Q9: Is Hibernate suitable for microservices?
Yes, but you must be careful with caching and lazy loading in distributed environments.
Q10: When should I not use Hibernate?
When you need ultra-high-performance batch operations or direct control over SQL for analytics-heavy workloads.