Hibernate native SQL queries

Hibernate provide a createSQLQuery method to let you call your native SQL statement directly.

Here we are joining 3 tables.

 Session session = sessionFactory.getCurrentSession();
 Query query = session
                         .createSQLQuery("SELECT psh.* FROM prod_sales_history psh,customer c,city cit where (psh.status=:status1 or psh.status=:status2) and cit.id=:cid and c.city=cit.id and psh.cust_id=c.id order by order_date;")
                    .setParameter("cid", cityId).setParameter("status1", Constants.ORDER_COMPLETELY_APPROVED).setParameter("status2", Constants.ORDER_PARTIALLY_APPROVED);
            List<Object[]> prodList =query.list();
for (Object[] object : prodList) {
System.out.println(object[0].toString());
             }

Fetching unique result from 3 tables placing in HashMap.

Session session = sessionFactory.getCurrentSession();
        Query query0 = session.createSQLQuery("SELECT * FROM agent where id=:agentId")
                .setParameter("agentId", Integer.parseInt(agentId));
        query0.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        Query query = session.createSQLQuery("SELECT sum(pa.approved_sell_price) as amt FROM agent_order_map am,products_approved pa where  pa.status=:status and pa.id=am.products_approved_id and am.agent_id=:agentId")
                .setParameter("status", Constants.PRODUCT_AMOUNT_RECEIVED_AGENT)
                .setParameter("agentId", Integer.parseInt(agentId));
        query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        Query query1 = session.createSQLQuery("SELECT sum(amount) as amount FROM agent_payment where agent_id=:agentId")
                .setParameter("agentId", Integer.parseInt(agentId));
        query1.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        Map<String, Object> listResult = new HashMap();
        listResult.put("Agent",query0.uniqueResult());
        listResult.put("TotalAmountToBePaid", query.uniqueResult());
        listResult.put("TotalPaid", query1.uniqueResult());
        return listResult;