Example usage for org.hibernate SQLQuery list

Introduction

In this page you can find the example usage for org.hibernate SQLQuery list.

Prototype

List<R> list();

Source Link

Document

Return the query results as a List.

Usage

From source file:com.mtech.springsecurity.service.AnalyticsService.java

public String getSalesPerProduct(SMEEntity entity) {
    JSONArray jArray = new JSONArray();
    SQLQuery query = getSession()
            .createSQLQuery("select st.stock_name, sum(sa.sales_value) sales_value  from stocks st\n"
                    + "    inner join sales sa on sa.stock_id = st.id\n" + "group by st.stock_name");
    query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
    List data = query.list();
    logger.warn("accountingperiod::" + data.size());
    for (Object object : data) {
        JSONObject jsonObject = new JSONObject();
        java.util.Map row = (java.util.Map) object;
        jsonObject.put("label", row.get("stock_name"));
        jsonObject.put("value", row.get("sales_value"));
        jArray.add(jsonObject);//from w  w  w  .j a v a  2 s.  com
    }

    return jArray.toString();
}

From source file:com.myapp.dao.EmployeeDao.java

public String getAllEmployees() {
    SessionFactory factory = NewHibernateUtil.getSessionFactory();
    Session session = factory.openSession();
    Transaction tx = session.beginTransaction();
    StringBuilder builder = new StringBuilder();
    try {// w w  w .  jav a  2  s  .c om
        SQLQuery query = session.createSQLQuery("select * from employees");
        List<Object[]> rows = query.list();
        int start = 0;

        builder.append("<table>");
        for (Object[] obj : rows) {
            builder.append("<tr>");
            String empid = obj[0].toString();
            builder.append("<td>" + empid + "</td>");
            String birthdate = obj[1].toString();
            builder.append("<td>" + birthdate + "</td>");
            String firstname = obj[2].toString();
            builder.append("<td>" + firstname + "</td>");
            String lastname = obj[3].toString();
            builder.append("<td>" + lastname + "</td>");
            String gender = obj[4].toString();
            builder.append("<td>" + gender + "</td>");
            String hiredate = obj[5].toString();
            builder.append("<td>" + hiredate + "</td>");
            builder.append("</tr><br>");
        }
        builder.append("</table>");
        tx.commit();

    } catch (Exception ex) {
        tx.rollback();
    } finally {
        session.close();

    }
    return builder.toString();

}

From source file:com.necl.core.daoImpl.SectionDaoImpl.java

@Override
public Section findById(String division, String section) throws Exception {
    logger.info("division : " + division + " Section : " + section);
    String sql = "SELECT DivisionCode, SectionCode, SectionName, SectionBudget, SectionActual "
            + "FROM tblMaster_Section " + "WHERE (SectionCode = :section) AND (DivisionCode = :division)";
    SQLQuery query = sessionFactory.getCurrentSession().createSQLQuery(sql);
    query.addEntity(Section.class);
    query.setParameter("section", section);
    query.setParameter("division", division);

    List results = query.list();

    return (Section) results.get(0);
}

From source file:com.necl.core.daoImpl.TicketHeaderDaoImpl.java

@Override
public List<TicketHeader> storedShowApproves(User user) throws Exception {

    String sql = "EXEC PRO_ShowApproves :positionCode , :divisionCode , :userName";
    SQLQuery query = sessionFactory.getCurrentSession().createSQLQuery(sql);
    query.setParameter("positionCode", user.getPositionCode());
    query.setParameter("divisionCode", user.getDivisionCode());
    query.setParameter("userName", user.getSsoId());
    query.addEntity(TicketHeader.class);
    List results = query.list();
    return results;
}

From source file:com.necl.core.daoImpl.TicketHeaderDaoImpl.java

@Override
public List<TicketHeader> storedShowTicket(String ssoId) throws Exception {
    String sql = "EXEC PRO_ShowTickets :ssoId";
    SQLQuery query = sessionFactory.getCurrentSession().createSQLQuery(sql);
    query.setParameter("ssoId", ssoId);
    query.addEntity(TicketHeader.class);
    List results = query.list();
    return results;
}

From source file:com.necl.core.daoImpl.TicketHeaderDaoImpl.java

@Override
public List<TicketHeader> findByType(String type) throws Exception {

    String sql = "SELECT * FROM tblTicketsH "
            + "WHERE (TicketType = :ticketType) AND (TicketsFinished = :ticketFinished)"
            + "ORDER BY ApplicationDate DESC ";
    SQLQuery query = sessionFactory.getCurrentSession().createSQLQuery(sql);
    query.addEntity(TicketHeader.class);
    query.setParameter("ticketType", type);
    query.setParameter("ticketFinished", "P");
    List results = query.list();

    return results;
}

From source file:com.necl.core.daoImpl.TicketHeaderDaoImpl.java

@Override
public List<TicketHeader> findByTypeArea(String type, String area) throws Exception {

    String sql = "SELECT *\n" + "FROM  APP_USER INNER JOIN\n"
            + "tblMaster_Branch ON APP_USER.branchId = tblMaster_Branch.BranchID INNER JOIN\n"
            + "tblTicketsH ON APP_USER.SSO_ID = tblTicketsH.ApplicationName\n"
            + "WHERE     (tblTicketsH.TicketType = :ticketType) AND (tblTicketsH.TicketsFinished = :ticketFinished) AND  (tblMaster_Branch.Area = :area)\n"
            + "ORDER BY tblTicketsH.ApplicationDate DESC";
    SQLQuery query = sessionFactory.getCurrentSession().createSQLQuery(sql);
    query.addEntity(TicketHeader.class);
    query.setParameter("ticketType", type);
    query.setParameter("ticketFinished", "P");
    query.setParameter("area", area);
    List results = query.list();

    return results;
}

From source file:com.neu.web.phmis.dao.AdminDao.java

public EnterpriseBean addEnterpriseBean(EnterpriseBean enterpriseBean) {

    try {/*from w  w  w .  j ava  2 s.c  o m*/

        session = HibernateUtil.getSessionFactory().openSession();

        session.beginTransaction();
        SQLQuery query = session.createSQLQuery(
                "CALL addEnterprise (:number, :name, :typeId, :countyId, :street, :suite, :city, :state, :zip, :contact, :url)");
        query.setString("number", enterpriseBean.getNumber());
        query.setString("name", enterpriseBean.getName());
        query.setInteger("typeId", enterpriseBean.getTypeId());
        query.setInteger("countyId", enterpriseBean.getCountyId());
        query.setString("street", enterpriseBean.getStreet());
        query.setString("suite", enterpriseBean.getSuite());
        query.setString("city", enterpriseBean.getCity());
        query.setString("state", enterpriseBean.getState());
        query.setString("zip", enterpriseBean.getZipCode());
        query.setString("contact", enterpriseBean.getContact());
        query.setString("url", enterpriseBean.getUrl());

        List<Integer> list = query.list();
        enterpriseBean.setId(list.get(0));
        session.getTransaction().commit();

    } catch (HibernateException e) {
        session.getTransaction().rollback();
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        session.close();
        return enterpriseBean;
    }

}

From source file:com.neu.web.phmis.dao.AdminDao.java

public void editEnterprise(EnterpriseBean enterpriseBean) {

    try {/* www. j  a  va2 s  .c  o m*/

        session = HibernateUtil.getSessionFactory().openSession();
        session.beginTransaction();

        SQLQuery query = session.createSQLQuery("SELECT creationDate FROM enterprise where id = :id");
        query.setInteger("id", enterpriseBean.getId());
        List list = query.list();
        enterpriseBean.setCreationDate((Date) list.get(0));

        session.update(enterpriseBean);
        session.getTransaction().commit();

    } catch (HibernateException e) {
        session.getTransaction().rollback();
        e.printStackTrace();
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        session.close();
    }

}

From source file:com.neu.web.phmis.dao.AdminDao.java

public UserBean addUser(UserBean userBean) {

    try {/* w  w  w.  j  a  v a 2  s. c o m*/

        session = HibernateUtil.getSessionFactory().openSession();
        session.beginTransaction();

        SQLQuery query = session.createSQLQuery(
                "CALL addUser (:number, :userName, :emailId, :roleId, :enterpriseId, :firstName, :lastName)");
        query.setString("number", userBean.getNumber());
        query.setString("userName", userBean.getUsername());
        query.setString("emailId", userBean.getEmailId());
        query.setInteger("roleId", userBean.getRoleBean().getId());
        query.setInteger("enterpriseId", userBean.getEnterpriseBean().getId());
        query.setString("firstName", userBean.getFirstName());
        query.setString("lastName", userBean.getLastName());
        List<Object[]> list = query.list();
        Object[] data = list.get(0);
        userBean.setId(Integer.parseInt(data[0].toString()));
        userBean.setPassword(data[1].toString());
        session.getTransaction().commit();

    } catch (HibernateException e) {
        session.getTransaction().rollback();
        e.printStackTrace();
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        session.close();
        return userBean;
    }

}