Hibernate: Criteria Queries in Depth

Hibernate: Criteria Queries in Depth - Using Criteria Queries
(Page 3 of 4 )

Now that the common core classes have been introduced, their usage in applications can be discussed. I already said in the last article that in SQL, DML plays  the most important role in the R(retrieve) operation of the CRUD quad. This is reflected in the core classes of Criteria API. The retrieval of data itself can be separated into four major categories:

1. Projection

2. Restriction

3. Aggregation

4. Grouping

The usage of core classes among these categories cannot be generalized. The reason for this will be evident from the details. All the examples are based on the ORDER and PRODUCTS table.

Do not confuse the term "Projection" as it is used here with the Projection class. Projection in common terms means to retrieve, and in SQL it means the "Select" clause. The "Select" clause is just a part of the services provided by the Projection class. Following is SQL query for projection of all fields of the ORDER table in SQL:

SELECT * FROM ORDER

The Criteria equivalent would be:

List orders= session.createCriteria(Order.class).list();

The above statement executes the corresponding SQL statement at the database server, populates the instances of the Order ORM class, adds them to a list and returns the List object. Actually, the above statement is composed of two statements:  

Criteria criteria= session.createCriteria(Order.class)  and  

List orders=criteria.list().

The combination of such dependent statements is known as method chaining. From now on I will be using this technique extensively. The above code retrieves all the rows from the ORDER table. But what if only the data contained in one of the fields has to be retrieved, as in the following SQL query:

SELECT NAME FROM PRODUCT

Here, the Projection class comes into play. The above query can be rewritten into a Criteria query as:

List products=session.createCriteria(Product.class)
     . setProjection(Projection.property(\"name\"))
     .list();

It is clear from the above example that to query based on just one field, the fieldname is passed as an argument to the property() method of the Projection class. The Projection instance returned in turn becomes an argument to the setProjection() method. Similarly, to retrieve data based on two fields, ProjectionList has to be used. Hence the SQL query:

SELECT NAME, ID FROM PRODUCT

Would become

List products =session.createCriteria(Product.class).setProjection(
    Projections.propertyList()
        .add(Projection.property(\"name\"))
        .add(Projection.property(\"id\"))
    )
    .list();

Now let's make the query more complex by introducing joins. What would be the equivalent of a query such as the one below:

SELECT O.*, P.* FROM ORDERS O, PRODUCT P WHERE O.ORDER_ID=P.ORDER_ID;

If you think the Criteria representation of the above would be as complex, then have a look at the following:

List orders = session.createCriteria(Order.class)
            .setFetchMode(“products”,FetchMode.JOIN)
            .list();

It's as simple as that. The only thing to be done is to call the setFetchMode() of the Criteria class with two parameters: the name of the class with which the current class has to be joined and mode of the fetching of the data from the associated class. In the above case, the class name is actually the instance variable provided within the Order class. The mode is Join.

So retrieval is done, but there is just one problem. If the data has to be retrieved based on a condition, then what? Then Restriction has to be used.

In layman’s terms, restriction means imposing conditions. To retrieve data based on certain conditions, Restriction must be used. Here the Restriction class comes into the picture. All the conditions provided by SQL are available in Criteria. The ones most commonly used are as follows:

Restriction.between is used to apply a "between" constraint to the field.

Restriction.eq is used to apply an "equal" constraint to the field.

Restriction.ge is used to apply a "greater than or equal" constraint to the field.

 Restriction.gt is used to apply a "greater than" constraint to the field.

Restriction.idEq is used to apply an "equal" constraint to the identifier property.

Restriction.in is used to apply an "in" constraint to the field.

Restriction.isNotNull is used to apply an "is not null" constraint to the field.  

Restriction.isNull is used to apply an "is null" constraint to the field.

Restriction.ne is used to apply a "not equal" constraint to the field.     

     So a SQL such as this

     SELECT * FROM ORDERS WHERE ORDER_ID=’1092’;

      Would become   

    List orders= session.createCriteria(Order.class)
               .add(Restrictions.eq(“orderId”,”1092”))
               .list();

Applying the restrictions becomes easy in the case of joins as well. For example, the following query

    SELECT O.*, P.* FROM ORDERS O, PRODUCT P WHERE

    O.ORDER_ID=P.ORDER_ID AND P.ID=’1111’;

Would become

List orders = session.createCriteria(Order.class)
        .setFetchMode(“products”,FetchMode.JOIN)
        .add(Restrictions.eq(“id”,”1111”))
        .list();

Just adding the Restriction to Criteria returned by setFetchMode() does the same thing that the above given SQL does.

Through restriction conditions can be imposed on data retrieval, there are situations where the data to be retrieved has to be based on the groups of values of a column. In such conditions, Aggregation must be used. Criteria provides aggregation functionality through the Projection class itself. So to get the count of all the rows present in the ORDER table based on the ID field, the criteria query would be:

List orders = session.createCriteria(Order.class)
     .setProjection( Projections.projectionList()
     .add( Projections.count(“id”) ))
     .list();

Similarly all the aggregate functions can be used as they are provided as static functions. As shown in the above example, each function takes the field name as the argument.

When the aggregation functions are used, the values may have to be grouped according to a particular field. Grouping always operates on a dataset. In Criteria Query API, grouping is provided by the Projection class. The groupProperty() method of the Projections class provides the grouping functionality. So a query like the one given below:

SELECT COUNT(ID) FROM ORDER  HAVING PRICETOTAL>2000 GROUP BY ID

 Can be rewritten in Criteria query as follows:

List orders = session.createCriteria(Order.class)
     .setProjection( Projections.projectionList()
      .add( Projections.count(“id”) )
       .add( Projections.groupProperty(“id”) )
     )
      .list();

That brings us to the end of this section. In the next section I will be using the Criteria query APIs to rewrite the application written in the last part.

blog comments powered by Disqus