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;

Maintain session in Web Applications.

Provides a way to identify a user across more than one page request.
The servlet container uses this interface to create a session between an HTTP client and an HTTP server. The session persists for a specified time period, across more than one connection or page request from the user.

We can save user data in modal and modal can be set to session while login.

Example:AuthenticationResponse is a modal.

AuthenticationDAO.java

   @Autowired
    private HttpSession httpSession;
    public void setHttpSession(HttpSession httpSession) {
        this.httpSession = httpSession;
    }

AuthenticationResponse authentications = new AuthenticationResponse();
                authentications.setRole(role.getUserRole().getId());
                authentications.setUserId(id.getId());
    httpSession.setAttribute("isLogin", authentications);
          
Header is common in all pages and it is loaded first when page loads.
In Header we can check whether user session exist.

Header.html

  $.getJSON("rest/authentication/isLogin", function(res){
           if(res.status){
          // user session exit
     }else{              
               location.href="index.jsp"}
});
 
AuthenticationDAO.java

   @RequestMapping(value="/isLogin",method=RequestMethod.GET)
    public @ResponseBody  Boolean isLoginUser(){
        try {
            AuthenticationResponse loginAuth = (AuthenticationResponse) httpSession.getAttribute("isLogin");
            if(loginAuth!=null){
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();
               return false;   
        }
  }