Generate HQL pojo classes automatically using hibernate tools

Install the hibernate tools from
http://www.blogger.com/img/blank.gif

Once installed, open the hibernate perspective and then generate a reverse cfg file from the existing (if you have one) or create a new by connecting to the db.

and then create a pakage, go to -> run-> hibernate code generation and create all the required tables and files with or without annotations.

refer:

http://shivasoft.in/blog/sql/myqsl/step-by-step-hibernate-tutorial-using-eclipse-wtp/

Using case in SQL/HQL query

select a, 
  case
    when b = '*' then 'star'
    when b = '+' then 'plus'
    when b = '-' then 'minus' 
    else '????'
  end 
from test_case_when;

Retrieving the data as List from HQL

Retrieving the data from the query as collection of pair.

Suppose the query is "Select username, count(id)..". If the query 

returns a list String, Long pair then the query actually

returns it as list<Object[]>. So collect the result in a 

variable List<Object[]>.

And then iterate over the object array. To get the values, 

Cast the corresponding object. 

 Query q1 = em.createQuery("SELECT e.name, e.salary

 FROM Employee e");
    List<Object[]> result1 = q1.getResultList();
    for (Object[] resultElement : result1) {
        String name = (String)resultElement[0];
        Double salary = (Double)resultElement[1];
        ...
    }

    Query q2 = em.createQuery("SELECT e.name, e.salary, 

e.department FROM Employee e");
    List<Object[]> result2 = q2.getResultList();
    for (Object[] resultElement : result2) {
        String name = (String)resultElement[0];
        Double salary = (Double)resultElement[1];
        Department dept = (Department)resultElement[2];
        ...
    }

    Query q3 = em.createQuery("SELECT COUNT(e), 

SUM(e.salary) FROM Employee e");
    Object[] result3 = (Object[])q3.getSingleResult();
    Long count = (Long)result3[0];
    Double sum = (Double)result3[1];

SQL Where IN + HQL

FROM
    Foo
WHERE
    Id = :id AND
    Bar IN (:barList)

To check in a set of results or values. 

Use Query.setParameterList(), Javadoc here. 

Retireve data from Data Base into a List of Map

final String SQL_QUERY = "select new map(l1.labelName as parentName,l2.labelName as childName,l1.jspPage as jspPage) from MenuBean as l1, MenuBean as l2 " +
                    "where l1.menuId=l2.parentMenuId and l2.typeId =:typeId ORDER BY l1.labelName,l2.labelName";
           
            final Query query = hibernateSession.createQuery(SQL_QUERY);
            query.setParameter("typeId", 1);
           
            List<Map> result = PersistenceServiceImpl.cast(query.list());