Hibernate Native Query

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 !