Hibernate Querying

Topics in This Section

•  Briefly review SQL querying
•  Hibernate’s different approaches to querying a database

Querying for Data

• Pull back specific data from a database based on specific requirements
• Retrieve a specific object
• Retrieve a collection of a specific object type
• Retrieve a collection of different object types
• In the database, handled through the Structured Query Language (SQL)
• SELECT statements for querying

Querying Terminology

Restriction

Narrowing down the results (rows) based on specified criteria
In SQL, accomplished with the use of conditions appearing in ‘where’ clause
 Example
 SELECT * FROM EBILL WHERE EBILL_ID=1;

Projection

 Narrowing down the data (columns) we return

In SQL, accomplished by identifying columns in the ‘select’ clause

Example

SELECT EBILL_ID FROM EBILL WHERE AMOUNT > 1000;

Aggregation

Grouping similar results together based on common attributes

Example

SELECT EBILLER_ID, AVG(BALANCE) FROM EBILL GROUP BY EBILLER_ID;

SQL Query Options

SELECT <column(s)> // projection

FROM <table(s)>

[ WHERE ] // restriction

<condition(s)>

[ ORDER_BY ] // ordering

<ordering(s)>

[ GROUP BY ] // aggregation

<column(s)>

[ HAVING ] // group restriction

<condition(s)>

Querying with Hibernate

Provides multiple interfaces for querying

• Query by ID
• Query by Criteria
• Query by Example
• Hibernate Query Language
• Native SQL calls through Hibernate

Query by ID

• Retrieve an object by its ID Most common and easiest approach to obtaining objects
• Fastest type of query, but can only return a single object at a time
• Assuming primary key index on the id column

Account account = (Account)session.get(Account.class,accountId);

Query by Criteria

Hibernate provides alternate ways of manipulating objects and in turn data available in RDBMS tables. One of the methods is Criteria API which allows you to build up a criteria query object programmatically where you can apply filtration rules and logical conditions.

Restrictions with Criteria:  You can use add() method available for Criteria object to add restriction for a criteria query. Following is the example to add a restriction to return the records with salary is equal to 2000:

Criteria cr = session.createCriteria(Employee.class);

cr.add(Restrictions.eq("salary", 2000));

List results = cr.list();

You can create AND or OR conditions using LogicalExpression restrictions as follows:

Criteria cr = session.createCriteria(Employee.class);
Criterion salary = Restrictions.gt("salary", 2000);
Criterion name = Restrictions.ilike("firstNname","zara%");
LogicalExpression orExp = Restrictions.or(salary, name);
cr.add( orExp );
List results = cr.list();

Set Projection on specific properties.

criteria.setProjection( Projections.projectionList()

.add( Projections.property("ename") )

    .add( Projections.property("salary") )

  );

Query by Example 

Set up  example object(s) for Hibernate to use to generate a database query.

Create ‘Example’ criterion based on these objects

org.hibernate.criterion.Example

Create by using static create(Object obj);

Creates an Example object used for querying for the supplied object type

AccountOwner owner = new AccountOwner();
owner.setLastName("Hall");
Example exampleOwner = Example.create(owner)
List hallList = session createCriteria(AccountOwner.class)
.add(exampleOwner)
.list();

Hibernate Query Language

• Hibernate created a new language named Hibernate Query Language (HQL), the syntax is quite similar to database SQL language. The main difference between is HQL uses class name instead of table name, and property names instead of column name.
• Instead of returning plain data, HQL queries return the query result(s) in the form of object(s)/tuples of object(s) that are ready to be accessed, operated upon, and manipulated programmatically. This approach does away with the routine task of creating and populating objects from scratch with the "resultset" retrieved from database queried.
• HQL fully supports polymorphic queries. That is, along with the object to be returned as a query result, all child objects (objects of subclasses) of the given object shall be returned.
• HQL facilitates writing database-type independent queries that are converted to the native SQL dialect of the underlying database at runtime. This approach helps tap the extra features the native SQL query provides, without using a non-standard native SQL query.

 HQL Syntax

Clauses in the HQL are: 

from , select , where , order by ,group by

Aggregate functions are:

avg(...), sum(...), min(...), max(...) 

count(*)

count(...), count(distinct ...), count(all...)

String hql = "SELECT SUM(E.salary), E.firtName FROM Employee E " +

             "GROUP BY E.firstName";

Query query = session.createQuery(hql);

List results = query.list();

Using Named Paramters

Hibernate supports named parameters in its HQL queries. This makes writing HQL queries that accept input from the user easy and you do not have to defend against SQL injection attacks.

String hql = "FROM Employee E WHERE E.id = :employee_id";

Query query = session.createQuery(hql);

query.setParameter("employee_id",10);

List results = query.list();

Native SQL calls through Hibernate

You can use native SQL to express database queries if you want to utilize database-specific features such as query hints or the CONNECT keyword in Oracle. Hibernate 3.x allows you to specify handwritten SQL, including stored procedures, for all create, update, delete, and load operations.

Returning Scalar Values – All Columns

Query getEBills = session.createSQLQuery("SELECT * FROM EBILL"); 

     List listOfRowValues = getEBills.list();

     for (Object[] singleRowValues : listOfRowValues) {

  // returned in the order on the table

     long id = (long)singleRowValues[0];

     double balance = ( ) g [ ]; balance)singleRowValues[1];

      ...

     }

Return List of Objects

Query getEBills = session.createSQLQuery( "SELECT * FROM EBill")

.addEntity(EBill.class);

List ebills = getEBills.list();

HQL vs HCQL

HQL is to perform both select and non-select operations on the data,  but Criteria is only for selecting the data, we cannot perform non-select operations using criteria
HQL is suitable for executing Static Queries, where as Criteria is suitable for executing Dynamic Queries
Criteria used to take more time to execute then HQL
With Criteria we are safe with SQL Injection because of its dynamic query generation but in HQL as your queries are either fixed or parametrized, there is no safe from SQL Injection.

Thank You Prikshit