HQL in Hibernate and Hibernate Example 5

HQL in Hibernate

    

    1)      Introducing HQL and the Query Object

    2)      Select and Pagination in HQL

    3)      Understanding Parameter Binding and SQL Injection

Why need HQL?

Till now we’ve seen get (), update () function which are used to retrieve single object and not multiple objects. When it comes to real world, the application always includes multiple objects retrieval and updating which requires a language like SQL. HQL is a handy language for doing this.

public class HibernateTestHQL {

     public static void main(String[] args) {

          

          SessionFactory sessionFactory = new                                      Configuration().configure().buildSessionFactory();

           Session session = sessionFactory.openSession();

           session.beginTransaction();

          

           Query query = session.createQuery("from PERSON");

           // For pagination and filtering of result from DB

           query.setFirstResult(5);

           query.setMaxResults(8);

           List<Person> list = (List<Person>) query.list();

           for(Person p: list)

                System.out.println(p.getName());

          

           We can also use particular column which we want to fetch

           Query query1 = session.createQuery("Select name from                    PERSON");

           query1.setFirstResult(4);

           List<String> listNames= query1.list();

           for(String name : listNames)

                System.out.println(name);

          

           /* Since its not advicable to put where clause values 
              between the query,its always good to put placeholders */

           Query query2 = session.createQuery("from PERSON where id=? or            name=?");

           query2.setLong(0,5);

           query2.setString(1,"Person 8");

           List<Person> list2 = (List<Person>) query2.list();

           for(Person p: list2)

                System.out.println("Using placeholder ? name is"                        +p.getName());

          

           // If we don't want to set using positions , we can use                  another placeholder as :

           Query query3 = session.createQuery("from PERSON where id=:id            or name=:name");

           query3.setLong("id", 4L);

           query3.setString("name","Person 3");

           List<Person> list3 = (List<Person>) query3.list();

           for(Person p: list3)

                System.out.println("Using placeholder : name is"                        +p.getName());

          

           session.getTransaction().commit();

           session.close();

     }

}

Output:

Hibernate: select * from ( select row_.*, rownum rownum_ from ( select person0_.id as id0_, person0_.name as name0_ from PERSON person0_ ) row_ where rownum <= ?) where rownum_ > ?

QUERY 
Person 6

Person 7

Person 8

Person 9

Person 10

QUERY 1

Hibernate: select person0_.name as col_0_0_ from PERSON person0_

Person 5

Person 6

Person 7

Person 8

Person 9

Person 10

QUERY 2

Hibernate: select person0_.id as id0_, person0_.name as name0_ from PERSON person0_ where person0_.id=? or person0_.name=?

Using placeholder ? name isPerson 5

Using placeholder ? name isPerson 8

QUERY 3

Hibernate: select person0_.id as id0_, person0_.name as name0_ from PERSON person0_ where person0_.id=? or person0_.name=?

Using placeholder : name isPerson 3

Using placeholder : name isPerson 4