Creating SQL Queries in Hibernate: HQL, Criteria API, and Native SQL

How is SQL query created in Hibernate?

In Hibernate, SQL queries can be created in several ways, depending on the level of abstraction you want to use. Here are the primary methods for creating SQL queries in Hibernate:

1. Using HQL (Hibernate Query Language)

HQL is an object-oriented query language similar to SQL but operates on the entity objects rather than directly on the database tables. Here's how you can create an HQL query:

Session session = sessionFactory.openSession();
Transaction transaction = null;

try {
    transaction = session.beginTransaction();
    
    // HQL query
    String hql = "FROM Employee E WHERE E.salary > :salary";
    Query query = session.createQuery(hql);
    query.setParameter("salary", 50000);
    
    List<Employee> results = query.list();
    
    for (Employee employee : results) {
        System.out.println(employee);
    }
    
    transaction.commit();
} catch (Exception e) {
    if (transaction != null) {
        transaction.rollback();
    }
    e.printStackTrace();
} finally {
    session.close();
}

2. Using Criteria API

The Criteria API provides a programmatic way to create queries. It is more flexible and type-safe compared to HQL. Here's an example:

Session session = sessionFactory.openSession();
Transaction transaction = null;

try {
    transaction = session.beginTransaction();
    
    CriteriaBuilder builder = session.getCriteriaBuilder();
    CriteriaQuery<Employee> criteria = builder.createQuery(Employee.class);
    Root<Employee> root = criteria.from(Employee.class);
    
    criteria.select(root).where(builder.greaterThan(root.get("salary"), 50000));
    
    List<Employee> results = session.createQuery(criteria).getResultList();
    
    for (Employee employee : results) {
        System.out.println(employee);
    }
    
    transaction.commit();
} catch (Exception e) {
    if (transaction != null) {
        transaction.rollback();
    }
    e.printStackTrace();
} finally {
    session.close();
}

3. Using Native SQL Queries

If you need to execute raw SQL queries, you can do so using the createNativeQuery method. This is useful for complex queries that may not be easily expressed in HQL or Criteria API.

Session session = sessionFactory.openSession();
Transaction transaction = null;

try {
    transaction = session.beginTransaction();
    
    // Native SQL query
    String sql = "SELECT * FROM Employee WHERE salary > :salary";
    Query query = session.createNativeQuery(sql, Employee.class);
    query.setParameter("salary", 50000);
    
    List<Employee> results = query.getResultList();
    
    for (Employee employee : results) {
        System.out.println(employee);
    }
    
    transaction.commit();
} catch (Exception e) {
    if (transaction != null) {
        transaction.rollback();
    }
    e.printStackTrace();
} finally {
    session.close();
}

Summary

• HQL: Use when you want to work with entity objects and prefer a more object-oriented approach.
• Criteria API: Use for dynamic queries and when you want type safety.
• Native SQL: Use when you need to execute raw SQL queries directly against the database.

Each method has its use cases, and you can choose the one that best fits your needs.