Search This Blog

Saturday, 1 September 2012

Querying with HQL - the basics

Hibernate provides us with its own query language - HQL or Hibernate Query Language. To understand the same I created a simple Entity class:
public class Entity {
    private String name;
    private Integer id;
    private Date date;
    private Master master;
    private Set<Child> children = new HashSet<Child>();
    //setter getters

    @Override
    public String toString() {
     String masterId = "null";
     if (null != this.getMaster()) {
        masterId = this.getMaster().getId().toString();
     }
     return "[Entity] : ( id " + this.getId() + " , data : "
            + this.getName() + " , master.Id : " + masterId + " , date : "
            + this.getDate() + " )]";
     }

}
The hbm for the same is as below:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="com.model">
    <class name="Entity" table="ENTITY">
        <id name="id" column="ID" type="integer">
            <generator class="identity" />
        </id>
        <property name="name" type="string">
            <column name="NAME" />
        </property>
        <property name="date" type="timestamp">
            <column name="DATE" />
        </property>
        
        <many-to-one name="master" class="Master" foreign-key="ENTITY_FK1">
            <column name="MASTER_ID"></column>
        </many-to-one>
        <set name="children" cascade="all-delete-orphan" inverse="true">
            <key column="ENTITY_ID" not-null="true" />
            <one-to-many class="Child" />
        </set>
    </class>
</hibernate-mapping>
As can be seen the class shares a parent (many to one) relation with Master class. It also has a set of children (one to many) of type Child. The two classes are as below:
public class Master {
    private Long id;
    private String data;
        
        //setter- getters  
    @Override
    public String toString() {        
        return "[Master] : ( id " + this.getId() + " , data : " + data + " )]";
    }
    
}
Master.hbm.xml
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="com.model">
    <class name="Master" table="ENTITY_MASTER">
        <id name="id" column="ID" type="long">
            <generator class="identity" />
        </id>
        <property name="data" type="string">
            <column name="DATA" />
        </property>
    </class>
</hibernate-mapping> 
 
public class Child {
    private Integer id;
    private Entity parent;
    private Integer key;

        //setter- getters  
    @Override
    public String toString() {
        return "[Child] : ( id " + this.getId() + " , key : " 
                        + this.getKey() + " , parent.Id : " + this.getParent().getId() + " )]";
    }
    
}
Child.hbm.xml
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="com.model">
    <class name="Child" table="CHILD_ENTITY">
        <id name="id" column="ID" type="integer">
            <generator class="identity" />
        </id>
        <property name="key" type="integer">
            <column name="`KEY`" />
        </property>
        <many-to-one name="parent" class="Entity" foreign-key="CHILD_FK1">
            <column name="ENTITY_ID"></column>
        </many-to-one>
    </class>
</hibernate-mapping>
Now that I have a simple relationship, I decided to start executing queries on the classes. This involves:
  1. Create an instance of org.Hibernate.query object. the query instance is returned from the session.createQuery() (or session.createSQLQuery()) method. The object is used to specify how exactly the query should be executed.
  2. The next step is to bind any query parameters.
  3. We now execute the query object and get the data into memory from the database.
final Session session = sessionFactory.openSession();
Query q = session.createQuery("from Entity");
List<Entity> entities = q.list(); // no aliases added in the select clause either
System.out.println(entities);
The query executed a simple select from the entity table.
3093 [main] DEBUG org.hibernate.impl.SessionImpl  - opened session at timestamp:
 13410387899
3093 [main] DEBUG org.hibernate.engine.query.QueryPlanCache  - unable to locate 
HQL query plan in cache; generating (from Entity)
3390 [main] DEBUG org.hibernate.hql.ast.QueryTranslatorImpl  - parse() - HQL: fr
om com.model.Entity
3453 [main] DEBUG org.hibernate.hql.ast.AST  - --- HQL AST ---
 \-[QUERY] 'query'
    \-[SELECT_FROM] 'SELECT_FROM'
       \-[FROM] 'from'
          \-[RANGE] 'RANGE'
             \-[DOT] '.'
                +-[DOT] '.'
                |  +-[IDENT] 'com'
                |  \-[IDENT] 'model'
                \-[IDENT] 'Entity'
...
3703 [main] DEBUG org.hibernate.hql.ast.AST  - --- SQL AST ---
 \-[SELECT] QueryNode: 'SELECT'  querySpaces (ENTITY)
    +-[SELECT_CLAUSE] SelectClause: '{derived select clause}'
    |  +-[SELECT_EXPR] SelectExpressionImpl: 'entity0_.ID as ID0_' {FromElement{
explicit,not a collection join,not a fetch join,fetch non-lazy properties,classA
lias=null,role=null,tableName=ENTITY,tableAlias=entity0_,origin=null,colums={,cl
assName=com.model.Entity}}}
    |  \-[SQL_TOKEN] SqlFragment: 'entity0_.NAME as NAME0_, entity0_.DATE as DAT
E0_, entity0_.MASTER_ID as MASTER4_0_'
    \-[FROM] FromClause: 'from' FromClause{level=1, fromElementCounter=1, fromEl
ements=1, fromElementByClassAlias=[], fromElementByTableAlias=[entity0_], fromEl
ementsByPath=[], collectionJoinFromElementsByPath=[], impliedElements=[]}
       \-[FROM_FRAGMENT] FromElement: 'ENTITY entity0_' FromElement{explicit,not
 a collection join,not a fetch join,fetch non-lazy properties,classAlias=null,ro
le=null,tableName=ENTITY,tableAlias=entity0_,origin=null,colums={,className=com.
model.Entity}}
...
3734 [main] DEBUG org.hibernate.hql.ast.QueryTranslatorImpl  - HQL: from com.mod
el.Entity
3734 [main] DEBUG org.hibernate.hql.ast.QueryTranslatorImpl  - SQL: select entit
y0_.ID as ID0_, entity0_.NAME as NAME0_, entity0_.DATE as DATE0_, entity0_.MASTE
R_ID as MASTER4_0_ from ENTITY entity0_
...
3765 [main] DEBUG org.hibernate.engine.query.HQLQueryPlan  - HQL param location 
recognition took 15 mills (from Entity)
3781 [main] DEBUG org.hibernate.engine.query.QueryPlanCache  - located HQL query
 plan in cache (from Entity)
...
3781 [main] DEBUG org.hibernate.jdbc.AbstractBatcher  - about to open PreparedSt
atement (open PreparedStatements: 0, globally: 0)
3781 [main] DEBUG org.hibernate.jdbc.ConnectionManager  - opening JDBC connectio
n
Hibernate: 
    /* 
from
    Entity */  
    select
        entity0_.ID as ID0_,
        entity0_.NAME as NAME0_,
        entity0_.DATE as DATE0_,
        entity0_.MASTER_ID as MASTER4_0_ 
    from
        ENTITY entity0_
...
[[Entity] : ( id 1 , data : newOne , master.Id : 1 , date : null )]]
As can be seen from the above logs there was a quite process involved in getting the final result.
The HQL we supplied was converted into an AST or Abstract Syntax Tree. In simple terms it involves converting the string into a tree of expressions that can be interpreted by our programs. It then built a similar tree of equivalent SQL expressions.
The logs indicate consideration was given for column lazy settings, type of properties - are they collections or associations or scalar  if any, aliases specified etc. As we did not have a where clause nodes were generated for from clause and select clause only. I think (and this is an assumption) the nodes in the HQL tree were replaced with SQL nodes generating the final equivalent SQL. As Hibernate needs ANTLR on its class path, most probably this was the jar used for tree generation and parsing. The SQL query was then executed using JDBC and the result was converted by Hibernate into a list of Entity objects.
I executed the above code twice and noted an interesting fact. The entire Tree creation and  SQL translation code was executed only once - the first time. The query was then added to a QueryPlanCache. On the second run, it picked up the SQL from the cache and simply executed it saving quite some time. This is a session independent cache and is available across sessions.
First Run 
 3235 [main] DEBUG org.hibernate.impl.SessionImpl  - opened session at timestamp:
 13410411231
3235 [main] DEBUG org.hibernate.engine.query.QueryPlanCache  - unable to locate 
HQL query plan in cache; generating (from Entity)
3563 [main] DEBUG org.hibernate.hql.ast.QueryTranslatorImpl  - parse() - HQL: fr
om com.model.Entity
...
Second Run
3985 [main] DEBUG org.hibernate.impl.SessionImpl  - opened session at timestamp:
 13410411242
3985 [main] DEBUG org.hibernate.engine.query.QueryPlanCache  - located HQL query
 plan in cache (from Entity)
4125 [main] DEBUG org.hibernate.engine.query.HQLQueryPlan  - find: from Entity
4125 [main] DEBUG org.hibernate.jdbc.AbstractBatcher  - about to open PreparedSt
atement (open PreparedStatements: 0, globally: 0)

2 comments: