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 criteriaIn 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
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
