HQL

Hibernate Native Query with example

by

You can express a query in SQL, using createSQLQuery() and let Hibernate manage the mapping from result sets to objects.

Although we should use HQL wherever possible but there could be a few reasons where you want to use native SQL funtionalities, such as :

  1. Calling stored procedures
  2. Calling stored functions
  3. DB support some special features which are not present in HQL

How to write a native query in hibernate ?

You can specify all properties on an object with {objectname.*}, or you can specify the aliases directly with {objectname.property}.

public SQLQuery createSQLQuery(String queryString) throws 
                                                                                                   HibernateException

If using Oracle DB: using ROWNUM

List emps = session.createSQLQuery("SELECT {emp.*} FROM Employee 
                {emp} WHERE ROWNUM<10")
                .addEntity("emp", Employee.class)
                .list();
        System.out.println("Native Query result list :: "+emps);

If using mySql DB: using LIMIT

List emps = session.createSQLQuery("SELECT {emp.*} FROM Employee 
                {emp} LIMIT 10")
                .addEntity("emp", Employee.class)
                .list();
        System.out.println("Native Query result list :: "+emps);

Output

Native Query result list ::
[Employee{id=2, firstName='Mak', lastName='S', address='mak@pt.com'}, 
Employee{id=3, firstName='Mak', lastName='S', address='mak@pt.com'},  
Employee{id=4, firstName='mak', lastName='robins', address='NA'}]

Summary

In this article, we looked into Hibernate Native Query with some example, the reason why to use native query and when to use it.
I hope you liked the article !

Hibernate Named Query

by

Hibernate Named Queries are created via class-level annotations on entities; normally, the queries apply to the entity in whose source file they occur, but there’s no absolute requirement for this to be true.

Named queries are created with the @NamedQueries annotation, which contains an array of @NamedQuery sets; each has a query and a name.

Example :

@Entity
@Table(name = "Employee")
@NamedQueries({
        @NamedQuery(name = "Employee.findAll", query = "from Employee emp"),
        @NamedQuery(name = "Employee.findByName",
                query = "from Employee emp where emp.firstName=:firstName"),
})
public class Employee implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private int id;

    @Column(name = "first_name")
    private String firstName;

    @Column(name = "last_name")
    private String lastName;

    @Column(name = "address")
    private String address;

// getters & setters & toString()
// ...
}

Executing above named query  :

Query namedQuery = session.getNamedQuery("Employee.findAll");
List<Employee> employees = namedQuery.list();
employees.forEach(System.out::println);

Summary

In this tutorial, we learnt about what is hibernate named query and understood by seeing an example of how to declare a named query.
I hope you liked it !

Hibernate Query Language | HQL examples

by

Hibernate Query Language a.k.a HQL is a query language which deals directly with the persistent entity object instead of the database table directly.

In this section we will see what is hibernate query language, syntax to write various kinds of HQL like Select, updates, insert, delete.

Syntax

Query query=session.createQuery("HQL query comes here....Example.... 
FROM Employee set age=:age where name=:name");

1. SELECT Query

You can write it as “Select * from employee” or as “From Employee”.

Example: 

Query query = session.createQuery("From Employee");
List<Employee> list = query.getResultList();

2. Update Query

Example: 

Query query=session.createQuery("update Employee 
                                set age=:age where name=:name");
query.setInteger("age", 30);
query.setString("name", "John Doe");
int result=query.executeUpdate();
System.out.println("Rows affected: " + result);

3. Insert Query

Example: 

String hql = "INSERT INTO Employee(firstName, lastName, address)"
           + "SELECT firstName, lastName, address FROM old_employee"
           + "where id=:id";
Query query = session.createQuery(hql);
query.setString("id", 101);
int result = query.executeUpdate();
System.out.println("Rows affected: " + result);

4. Delete Query

Example: 

String hql = "DELETE FROM Employee "  + 
             "WHERE id = :emp_id";
Query query = session.createQuery(hql);
query.setParameter("emp_id", 101);
int result = query.executeUpdate();
System.out.println("Rows affected: " + result);

5. Pagination in HQL Query

Two methods to set the start and end positions.

Query setFirstResult(int startPosition) : start index position
Query setMaxResults(int maxResult) : number of results from the index set above

Example:

String hql = "FROM Employee";
Query query = session.createQuery(hql);
query.setFirstResult(5);
query.setMaxResults(10);
List results = query.list();

6. Aggregate functions in HQL Query

The aggregate functions available in HQL includes the following:

  1. avg(property name): The average of a property’s value.
  2. count(property name or *): The number of times a property occurs in the results.
  3. min(property name): The minimum value of the property values.
  4. max(property name): The maximum value of the property values.
  5. sum(property name): The sum total of the property values.

Example:

String hql = "SELECT count(distinct Emp.firstName) FROM Employee Emp";
Query query = session.createQuery(hql);
List results = query.list();

Summary

In this tutorial we learnt how to write HQL(hibernate query language) which is simple, easy for java developers. We went through CRUD operations syntax and examples in HQL, then went through the pagination and aggregate functions with examples.
I hope you liked it !

Hibernate Criteria Query

by

Hibernate Criteria Query API lets you create nested, structured query expressions in Java, it gives you an object oriented control over the queries, it provides a compile time syntax check which is not possible with a query language like HQL or SQL.

Since Hibernate 5.2, the Hibernate Criteria API is deprecated and 
new development is focused on the JPA Criteria API.

Hibernate version that We are using here : 

<dependency>
     <groupId>org.hibernate</groupId>
     <artifactId>hibernate-core</artifactId>
     <version>5.4.11.Final</version>
</dependency>

Hibernate Criteria Query : Example

Employee Entity 

@Entity
@Table(name = "Employee")
public class Employee implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private int id;

    @Column(name = "first_name")
    private String firstName;

    @Column(name = "last_name")
    private String lastName;

    @Column(name = "address")
    private String address;

// getters & setters & toString()
// ...
}

Fetch Employees from the Database using Criteria Query

// Criteria Query Example
CriteriaBuilder cb = session.getCriteriaBuilder();
CriteriaQuery<Employee> cr = cb.createQuery(Employee.class);
Root<Employee> root = cr.from(Employee.class);
cr.select(root);

Query criteriaQuery = session.createQuery(cr);
List<Employee> results = query.getResultList();
results.forEach(System.out::println); // print all Employee records

Explanation Step by Step :

  1. Create an instance of Session from the SessionFactory object
  2. Create an instance of CriteriaBuilder by calling the getCriteriaBuilder() method
  3. Create an instance of CriteriaQuery by calling the CriteriaBuilder createQuery() method
  4. Create an instance of Query by calling the Session createQuery() method
  5. Call the getResultList() method of the query object which gives us the results
  6. At last using java 8 for each loop to print the records.

Summary

In this tutorial, we focused on the basics of Criteria Queries in Hibernate and JPA,
Hope you liked it !