Named Queries in HQL with hibernate 4.3

We can have queries saved with a name and later we can retrieve them simply using that name. We use @NamedQuery annotation to declare a named query. We can also have a named query for native sql. For native SQL we use the annotation @NamedNativeQuery annotation. This is one of the important advantages of having a named query: you can write native SQL. You can retrieve a Query object from a saved query name (hql and native named query) using session.getNamedQuery method.

We will understand things better with some examples. If you are not following the tutorials in order, refer to the article http://javajee.com/lab-your-first-hibernate-43-program for the basic setup including hibernate configuration file. Change hbm2ddl.auto property to update and run that test class once to create a record, if you have not created a record already; if a record is already present with the same primary key, you will get duplicate key exception. You should also be familiar with Java Collection Lists. Also, refer to http://javajee.com/introduction-to-hibernate-query-language-hql-in-hibernate-43 for HQL basics and http://javajee.com/parameter-binding-in-hql-using-hibernate-43 to know about parameter binding.

Add  @NamedQuery annotation to the User entity class in addition to the @Entity annotation as:

package com.javajee.hiberex.dto;

 

import javax.persistence.Entity;

import javax.persistence.Id;

import javax.persistence.NamedQuery;

 

@Entity

@NamedQuery(name="User.getId", query="Select id from User where id > 0")

public class User {  

Import NamedQuery from javax.persistence.

 

Within the test class, you can retrieve the query object using session.getNamedQuery(), and then retrieve the list like you do with any other Query object:

Configuration configuration = new Configuration();

    configuration.configure();

   ServiceRegistry serviceRegistry = new StandardServiceRegistryBuilder()
                .applySettings(configuration.getProperties()).build();

    SessionFactory sessionFactory = configuration.buildSessionFactory(serviceRegistry);

    Session session = sessionFactory.openSession();

    session.beginTransaction();

    Query query = session.getNamedQuery("User.getId");

    List<Integer> ids = (List<Integer>) query.list();

    for (int i : ids) {

      System.out.println(i);

    }

    session.getTransaction().commit();

 

You can also use parameter binding (including named parameter binding) with a named query as:

@NamedQuery(name="User.getId", query="Select id from User where id > ?")

You can set the values for parameter binding the same way you do for any you do with any other Query object:

Configuration configuration = new Configuration();

    configuration.configure();

    ServiceRegistry serviceRegistry = new StandardServiceRegistryBuilder()
                .applySettings(configuration.getProperties()).build();

    SessionFactory sessionFactory = configuration.buildSessionFactory(serviceRegistry);

    Session session = sessionFactory.openSession();

    session.beginTransaction();

 

    int idVar = 20;

    Query query = session.getNamedQuery("User.getId");

    query.setInteger(0, idVar);

    List<Integer> ids = (List<Integer>) query.list();

    for (int i : ids) {

      System.out.println(i);

    }

    session.getTransaction().commit();

 

Named Native Query

The @NamedNativeQuery annotation can be used to create a named SQL query. Here we can specify a native SQL query, not a HQL query. Therefore you can use the “select * from …” syntax. Remember that you should use table names and column names in an SQL query, not class names and field names as in HQL. As a consequence of not using class names, hibernate now won’t know which class it should return and hence we can specify the class using resultClass attribute of the  @NamedNativeQuery annotation.

import javax.persistence.NamedNativeQuery;

 

@Entity

@NamedNativeQuery(name = "User.getAll",

  query = "Select * from User where id > ?",

  resultClass = User.class)

public class User {

…  

 

You can retrieve the result in the same way as you do for the @NamedQuery annotation:

Configuration configuration = new Configuration();

    configuration.configure();

   ServiceRegistry serviceRegistry = new StandardServiceRegistryBuilder()
                .applySettings(configuration.getProperties()).build();

    SessionFactory sessionFactory = configuration.buildSessionFactory(serviceRegistry);

    Session session = sessionFactory.openSession();

    session.beginTransaction();

 

    int idVar = 20;

    Query query = session.getNamedQuery("User.getAll");

    query.setInteger(0, idVar);

    List<User> users = (List<User>) query.list();

    for (User u : users) {

      System.out.println(u.getId());

    }

    session.getTransaction().commit();

 

When specifying a @NamedNativeQuery, if you don’t specify the mapping entity class using resultClass attribute of the  @NamedNativeQuery annotation, you might get the below error in some versions of hibernate:

Exception in thread "main" java.lang.ClassCastException: [Ljava.lang.Object; cannot be cast to com.javajee.User
    at com.javajee.UserTest.main(UserTest.java:26)

Tags: 

Search the Web

Custom Search

Searches whole web. Use the search in the right sidebar to search only within javajee.com!!!