# Hibernate Query API

## API of `org.hibernate.query.Query<T>`

* This interface is specified in `javax.persistence` specification

1. `Iterator iterate() throws HibernateException`
   * This api is deprecated since `5.2` version
   * Return the query results as an Iterator.
   * If the query contains multiple results per row, the results are returned Object\[].
   * Entities are returned in lazy manner.
   * Read more [here](https://stackoverflow.com/questions/14957220/difference-between-query-list-and-query-iterate) for difference between `list()` and `iterator()`.
2. ```java
       List<T> getResultList()
   ```
   * Execute query to get List of selected PERSISTENT POJOs
   * API of `org.hibernate.query.Query` interface, taken from `javax.persistence.TypedQuery<T>`.
   * Execute a `SELECT` query and return the query results as a generic `List<T>`.
   *   T is the type of POJO/Result.

       ```java
           String hql = "select b from BookPOJO b";
           try {
               List<Book> l1 = hs.createQuery(jpql, Book.class)
                               .getResultList();
           }
       ```
   * Passing IN parameters to query & execute it.
     * Objective : Display all books from specified author, with price less than the specified price.
     *   API from `org.hibernate.query.Query` interface

         ```java
             Query<R> setParameter(String name, Object value)
         ```
     * Bind a named query parameter using its inferred Type.
       * name : query parameter name
       * value : parameter value
     * How to set `IN` parameters ?
       * `org.hibernate.query.Query<T>` API
       * `public Query<T> setParameter(String pName, Object val);`
     *   Example,

         ```java
             String hql="select b from BookPOJO b where b.price < :sp_price and b.author = :sp_auth";
             List<Book> l1 = hibSession.createQuery(hql,Book.class).setParameter("sp_price", user_price).setParameter("sp_auth", user_auth).getResultList();
         ```
3. Updating POJOs
   * Can be done either with select, followed by update or ONLY with update queries. _(following is eg of 2nd option commonly known as bulk update)_.
   *   Objective : Decrease price of all books with author=specified author.

       ```java
           String jpql = "update BookPOJO b set b.price = b.price - :disc where b.author = :au and b.publishDate < :dt ";
           int updateCount= hs.createQuery(jpql).setParameter("disc", disc).setParameter("dt", d1).executeUpdate();
       ```
   * This approach is typically not recommended often, since it bypasses L1 cache. Interesting read [here](https://stackoverflow.com/questions/37827503/what-operations-in-jpa-bypass-persistance-context-cache-and-directly-invoke-in-d)
   * Cascading is not supported. Doesn't support optimistic locking directly.
   * Use case, for bulk updations to be performed on a standalone unrelated table.

### Pagination API

1. `Query setMaxResults(int maxResults)`
   * Set the maximum number of rows to retrieve.
   * If not set, there is no limit to the number of rows retrieved.
2.  `Query setFirstResult(int firstResult)`

    * Set the first row to retrieve.
    * If not set, rows will be retrieved beginnning from row 0. _(NOTE row num starts from 0)_.

    ```java
        List<CustomerPOJO> l1=session
            .createQuery("select c from CustomerPOJO c")
            .setFirstResult(30).setMaxResults(10).list();
    ```

    * How to count rows & use it in pagination techniques?

    ```java
        int pageSize = 10;
        String countQ = "Select count (f.id) from Foo f";
        Query countQuery = session.createQuery(countQ);
        Long countResults = (Long) countQuery.uniqueResult();
        int lastPageNumber = (int) ((countResults / pageSize) + 1);
        Query selectQuery = session.createQuery("From Foo");
        selectQuery.setFirstResult((lastPageNumber - 1) * pageSize);
        selectQuery.setMaxResults(pageSize);
        List<Foo> lastPage = selectQuery.list();
    ```
3. `<T> T getSingleResult()`
   * Executes a `SELECT` query that returns a single result.
   * Returns a single instance _(persistent object)_ that matches the query.
   * It throws:
     * NoResultException, if there is no result.
     * NonUniqueResultException, if more than one result.
     * IllegalStateException, if called for a JPQL `UPDATE` or `DELETE` statement.
4. How to get `Scrollable` Result from Query?
   * `ScrollableResults scroll(ScrollMode scrollMode) throws HibernateException`
   * The scrollability of the returned results depends upon JDBC driver support for scrollable ResultSets.
   * Return the query results as ScrollableResults. Then, one can use methods of ScrollableResults `first(), next(), last(), scroll(n)`.
5.  How to create `NamedQuery` from Session interface?

    * What is a named query ?
      * It's a technique to group the `HQL` statements in single location _(typically in POJOS)_ and lately refer them by some name whenever need to use them. It helps largely in code cleanup because these HQL statements are no longer scattered in whole code.
    * It is Fail fast: Their syntax is checked when the session factory is created, making the application fail fast in case of an error.
    * Reusable: They can be accessed and used from several places which increases re-usability.
    * Example, In POJO class, at class level, one can declare Named queries.

    ```java
        @Entity
        @NamedQueries({
                @NamedQuery(name = "DepartmentEntity.GET_DEPARTMENT_BY_ID", query="select d from DepartmentEntity d where d.id = :id")
        })
        public class Department{....}

        //Usage
        Department d1 = (Department) session.getNamedQuery("DepartmentEntity.GET_DEPARTMENT_BY_ID").setInteger("id", 1);
    ```
6.  How to invoke native sql from hibernate?

    ```java
        Query q = hs.createSQLQuery("select * from books").addEntity(BookPOJO.class);
       List<Object[]> l1 = q.list();
    ```

![Inheritance Hierarchy](https://574639531-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FJDnjsv52I3fJ56WbLgHu%2Fuploads%2Fgit-blob-c44f266ae0f10b16a2499efd7f52b2ff667e4c6a%2FQuery_Hierarchy.png?alt=media)

## Hibernate Criteria API

* A powerful and elegent alternative to `HQL`. Well adapted for dynamic search functionalities where complex Hibernate queries have to be generated 'on-the-fly'.
* Typical steps are,
  * Create a criteria for POJO
  * Add restrictions, projections, add order
  * Fire query (via list() or uniqueResult())

## Composite primary key

* Use annotation `@Embeddable` _(NOT @Entity)_.
* Must be Serializable.
* Must implement `hashCode()` & `equals()` as per general contract.
* In owning Entity class add usual annotation `@Id`.
