In the previous chapters, you've seen some basic HQL statements for querying objects. For example, returning to the bookshop example, you can use the following HQL to query for all books, and then call the list() method to retrieve the result list that contains book objects:

Query query = session.createQuery("from Book");
List books = query.list();

Now, let's look at the from clause, which is the only required part of a HQL statement. The following HQL statement queries for books whose name is "Hibernate". Notice that name is a property of the Book object:

from Book
where name = 'Hibernate'

Or, you can assign an alias for the object. This is useful when you're querying multiple objects in one query. You should use the naming conventions for classes and instances in Java. Note that the as keyword is optional:

from Book as book
where book.name = 'Hibernate'

You can then integrate the alias with the query:

Query query = session.createQuery("from Book where book.name='Hibernate'");
List books = query.list();

You need to use the EntityManager to create a javax.persistence.Query instance in JPA QL. The select clause is required in Java persistence, as shown here:

public List<Book> readFromManager() {

    EntityManager manager = SessionManager.getEntityManager();
    EntityTransaction tran = manager.getTransaction();
    tran.begin();
    Query query = manager.createQuery("select b from Book b");
    List<Book> list = query.getResultList();
    return list;
  }

Note that you need to place the persistence.xml file in the META-INF folder. Also remember to place all the required jars in the classpath.

In HQL, you can use where clauses to filter results, just as you do in SQL. For multiple conditions, you can use and, or, and not to combine them. This is called applying restrictions:

from Book book
where book.name like '%Hibernate%' and book.price between 100 and 200

You can check whether an associated object is null or not by using is null or is not null:

from Book book
where book.publisher is not null

Notice that the null check can't be performed on a collection. So, if you have a query like the following, you get an exception from Hibernate:

from Book8_1 book
where book.chapters is not null

Hibernate provides the empty key word, which you can use to check if a collection is empty:

from Book8_1 book
where book.chapters is not empty

Let's say you want to retrieve all books published by the publishers "Apress" and "friendsOfED". In technical terms, you want to retrieve based on a property (publisher's name) of the association (publisher). For this kind of requirement, you can use implicit joins in the where clause. You create an implicit join by using the . dot operator:

from Book book
where book.publisher.name in ('Apress', 'friendsOfED')

Remember that for a collection association, if you reference it more than one time, you should use an explicit join to avoid duplicated joins. Explicit joins require the use of the join keyword. Querying using joins is described in detail later in this chapter.

Hibernate provides a function that lets you check the size of a collection. You can use the special property size or the special size() function. Hibernate uses a select count(...) subquery to get the size of the collection:

from Book book
where book.chapters.size > 10
from Book book
where size(book.chapters) > 10

When the number of records returned by a query is large, it's desirable to retrieve only a subset of the actual number of records. The subset is usually equal to the page size (number of records); this feature is called pagination. The Hibernate and JPA Query interfaces provide two methods to enable pagination:

The implementation looks like this:

public List<Book> readFromManager() {

    EntityManager manager = SessionManager.getEntityManager();
    EntityTransaction tran = manager.getTransaction();
    tran.begin();
    Query query = manager.createQuery("select b from Book b");
    query.setFirstResult(5);
    query.setMaxResults(15);
    List<Book> list = query.getResultList();
    return list;
  }

In this example, the records starting with the fifth row and the next 15 records after that are retrieved.

You use parameter binding to bind values to the query parameters. Parameter binding makes the code look cleaner; the code is also safer. It isn't recommended that you inject user input into a query using string concatenation; this can lead to a security issue called SQL injection.

public static void main(String[] args) {

    List books = exampleSQLInjection("Hibernate");
    if(books!=null)
    {
      System.out.println("Size- "+books.size());
      Iterator it = books.iterator();
      while(it.hasNext())
      {
        System.out.println("book- "+it.next());
      }
    }

}

public static List exampleSQLInjection(String userInput)
{
  Session session = getSession();
  String q="from Book8_1 book where book.bookName='"+userInput+"'";
  Query query = session.createQuery(q);
  List books = query.list();
  return books;
}

Hibernate' or 'x'='x

List books = exampleSQLInjection("Hibernate' or 'x'='x");

Hibernate and JPA support two types of parameter binding:

You can specify query parameters the same way you do for SQL queries. If you're sure only one unique object will be returned as a result, you can call the uniqueResult() method to retrieve it. Null is returned if nothing matches:

query = session.createQuery("from Book where isbn = ?");
query.setLong(0, 520);
Book bookCh8 = (Book) query.uniqueResult();

This example uses ? to represent a query parameter and set it by index (which is zero-based, not one-based as in JDBC). This kind of parameter binding is called positional parameter binding. You can also use named parameter binding for queries. The advantages of using named parameters is that they're easy to understand and can occur multiple times:

Query query = session.createQuery("from Book where isbn =:isbn");
query.setLong("isbn", 520);
Book bookCh8 = (Book) query.uniqueResult();
System.out.println(bookCh8);

You can put query statements in any mapping definitions and refer them by name in the code. These are called named queries. But for easier maintenance, you should centralize all your named queries in one mapping definition, say NamedQuery.hbm.xml. In addition, setting up a mechanism for naming queries is also beneficial—you need to assign each query a unique name. You should also put the query string in a <![CDATA[...]]> block to avoid conflicts with special XML characters. Named queries don't have to be HQL or JPA QL strings—they can be native SQL queries:

<hibernate-mapping>
  <query name="Book.by.isbn">
    <![CDATA[from Book where isbn = ?]]>
  </query>
</hibernate-mapping>

To reference a named query, you use the session.getNamedQuery() method:

Query query = session.getNamedQuery("Book.by.isbn");
query.setString(0, "1932394419");
Book book = (Book) query.uniqueResult();

The JPA specifies the @NamedQuery and @NamedNativeQuery annotations. The name and query attributes are required. You can place these annotations in the metadata of a particular entity or into a JPA XML file:

@NamedQueries({
  @NamedQuery(
  name="Book.by.isbn",
  query="from Book where isbn = ?"
  )
})
@Entity (name="book")
@Table  (name="BOOK", schema="BOOKSHOP")
public class Book{...}

In HQL, you can use the join keyword to join associated objects. The following query finds all the books published by the publisher "Apress". The result contains a list of object pairs in the form of an array of Objects(Object[]). Each pair consists of a book object and a publisher object:

from Book book join book.publisher publisher
where publisher.name = 'Apress'

In addition to many-to-one associations, all other kinds of associations can be joined. For example, you can join the one-to-many association from book to chapters. The following query finds all the books containing a chapter titled "Hibernate Basics". The result contains a list of object pairs; each pair consists of a book object and a collection of chapters:

from Book book join book.chapters chapter
where chapter.title = 'Hibernate Basics'

In the previous joins, you specify the join keyword to join associated objects. This kind of join is called an explicit join. You can also reference an association directly by name: this causes an implicit join. For example, the previous two queries can be expressed as follows. The result contains only a list of book objects, because no join is specified in the from clause:

from Book book
where book.publisher.name = 'Manning'

Note that implicit joins are directed along many-to-one or one-to-one associations. They're never directed along many-to-many associations. This means you can never use something like

String QueryString3 =
  "from Book8_1 book where book.chapters.title = 'Many-To-Many Association'";
Query query1 = session.createQuery(QueryString3);
List<Chapter8_1> chptrs = query1.list();

The correct usage is as follows:

String queryString =
  "from Book8_1 book join book.chapters chapter where chapter.title=:title";
Query query = session.createQuery(queryString);
query.setString("title", "Many-To-Many Association");
List books = query.list();

If you use the following HQL to query for books joined with publishers, books with null publisher aren't included. This type of join is called an inner join, and it's the default if you don't specify a join type or if you specify inner join. It has the same meaning as an inner join in SQL:

from Book book join book.publisher

If you want to get all books regardless of whether the publisher is null, you can use a left join by specifying left join or left outer join:

from Book book left join book.publisher

HQL supports two other types of joins: right joins and full joins. They have the same meaning as in SQL but are seldom used.

The following HQL retrieves books and their associated chapters where at least one of the chapter titles includes the word Hibernate. The result contains pairs consisting of a book and a chapter:

from Book book join book.chapters chapter
where chapter.title like '%Hibernate%'

You can use join fetch to force a lazy association to be initialized. It differs from a pure join in that only the parent objects are included in the result:

from Book book join fetch book.publisher publisher

An inner join fetch query doesn't return book objects with a null publisher. If you want to include them, you must use left join fetch:

from Book book left join fetch book.publisher publisher

HQL and JPA QL support the aggregate functions count(), min(), max(), sum(), and avg(). If you want to know how many books are in your bookshop, you have to do something like this:

String q2 = "select count(i) from Book i";
Query query = session.createQuery(q2);
Long count = (Long) query.uniqueResult();

To find the minimum and maximum prices of the books in the bookshop, you can use a query like the following:

String q3 = "select min(i.price),max(i.price) from Book i";
Query query = session.createQuery(q3);
Object[] count = (Object[]) query.uniqueResult();
System.out.println("Minimum price-  "+count[0]);
System.out.println("Maximum price- "+count[1]);

Note that this query's return type is an Object[] array.

You can sort the result list with an order by clause. Multiple fields and ascending/descending order can be specified:

from Book book
order by book.name asc, book.publishDate desc

HQL also supports the group by and having clauses. They're translated into SQL by Hibernate:

select book.publishDate, avg(book.price)
from Book book
group by book.publishDate

select book.publishDate, avg(book.price)
from Book book group by book.publishDate
having avg(book.price) > 10