Example usage for org.hibernate SQLQuery list

List of usage examples for org.hibernate SQLQuery list

  1. HOME
  2. Java
  3. org.hibernate
  4. org.hibernate.*
  5. SQLQuery
  6. 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.vertec.daoimpl.PrivilegeDAOImpl.java

/**
 * call from PrivilegeController--> case "SetPrivilegeItem"
 *
 * @param groupId//ww  w  .  ja v  a  2s  .c o m
 * @return
 */
@Override
public List<Object[]> loadAllUserGroupPrivilegeItem(int groupId) {
    Session session = NewHibernateUtil.getSessionFactory().openSession();
    Transaction transaction = session.beginTransaction();

    if (session != null) {
        try {
            SQLQuery query = session.createSQLQuery(
                    "SELECT privilege_item.privilege_item_id,priviledge_priviledge_id,privilege_item_name,priviledge_name FROM user_group_privilege_item\n"
                            + "inner join privilege_item on (user_group_privilege_item.privilege_item_id=privilege_item.privilege_item_id)\n"
                            + "inner join priviledge on (privilege_item.priviledge_priviledge_id=priviledge.priviledge_id) where user_group_id=:ugpId");
            query.setParameter("ugpId", groupId);

            List<Object[]> ugpList = query.list();
            return ugpList;

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (session != null && session.isOpen()) {
                session.close();
            }
        }
    }

    return null;
}

From source file:com.vertec.daoimpl.PrivilegeDAOImpl.java

/**
 * call from PrivilegeController--> case "SetPrivilegeItem"
 *
 * @param ugId//from w  w  w.j a  v  a  2s. c  o m
 * @return
 */
@Override
public List<Object[]> loadNotInAllPrivilegeItems(int ugId) {
    Session session = NewHibernateUtil.getSessionFactory().openSession();
    Transaction transaction = session.beginTransaction();

    if (session != null) {
        try {
            SQLQuery query = session.createSQLQuery(
                    "SELECT privilege_item_id,priviledge_priviledge_id,privilege_item_name,priviledge_name\n"
                            + "FROM privilege_item inner join priviledge on (privilege_item.priviledge_priviledge_id=priviledge.priviledge_id)\n"
                            + "where privilege_item_id not in (SELECT privilege_item_id FROM user_group_privilege_item where user_group_id=:user_group_id) ");
            query.setParameter("user_group_id", ugId);
            List<Object[]> prList = query.list();
            return prList;

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (session != null && session.isOpen()) {
                session.close();
            }
        }
    }

    return null;
}

From source file:com.vertec.daoimpl.ReportDAOImpl.java

public List<Object[]> invoiceItemToReport(int invoiceId) {
    Session session = NewHibernateUtil.getSessionFactory().openSession();
    org.hibernate.Transaction transaction = session.beginTransaction();

    if (session != null) {
        try {//from   ww w . j  a v a  2  s. co  m

            SQLQuery query = session.createSQLQuery(
                    "SELECT p.product_name,ii.unit_price,ii.quantity,ii.tot_amount,ii.discount,ii.tot_after_dis FROM invoice_item ii inner join product_master pm on ii.product_master_id=pm.product_master_id\n"
                            + "inner join product p on pm.product_id=p.product_id\n"
                            + "where ii.invoice_id=:invoiceId");
            query.setParameter("invoiceId", invoiceId);

            List<Object[]> inList = query.list();
            return inList;

        } catch (Exception e) {
            e.printStackTrace();

        } finally {
            if (session != null && session.isOpen()) {
                session.close();
            }
        }
    }
    return null;
}

From source file:com.vertec.daoimpl.ReportDAOImpl.java

public List<Object[]> dailyInvoiceReport(String selectDate) {
    Session session = NewHibernateUtil.getSessionFactory().openSession();
    org.hibernate.Transaction transaction = session.beginTransaction();

    if (session != null) {
        try {//from  www.jav  a 2  s  .c o  m

            SQLQuery query = session.createSQLQuery(
                    "SELECT i.invoice_id,c.customer_name,b.branch_name,i.invoice_total,i.discount,i.tot_after_discount,i.invoiced_date\n"
                            + "FROM invoice i inner join customer c on i.customer_id=c.customer_id inner join branch b on i.branch_id=b.branch_id\n"
                            + "where i.invoiced_date=:selectDate");
            query.setParameter("selectDate", selectDate);

            List<Object[]> inList = query.list();
            return inList;

        } catch (Exception e) {
            e.printStackTrace();

        } finally {
            if (session != null && session.isOpen()) {
                session.close();
            }
        }
    }
    return null;
}

From source file:com.vertec.daoimpl.ReportDAOImpl.java

public List<Object[]> periodicallyInvoiceReport(String[] daeArr) {
    Session session = NewHibernateUtil.getSessionFactory().openSession();
    org.hibernate.Transaction transaction = session.beginTransaction();

    if (session != null) {
        try {/* ww w  .  j a v  a  2s .  c  o m*/

            SQLQuery query = session.createSQLQuery(
                    "SELECT i.invoice_id,c.customer_name,b.branch_name,i.invoice_total,i.discount,i.tot_after_discount,i.invoiced_date\n"
                            + "FROM invoice i inner join customer c on i.customer_id=c.customer_id inner join branch b on i.branch_id=b.branch_id\n"
                            + "where i.invoiced_date between :fromDate and :toDate");
            query.setParameter("fromDate", daeArr[0]);
            query.setParameter("toDate", daeArr[1]);

            List<Object[]> inList = query.list();
            return inList;

        } catch (Exception e) {
            e.printStackTrace();

        } finally {
            if (session != null && session.isOpen()) {
                session.close();
            }
        }
    }
    return null;
}

From source file:com.vertec.daoimpl.ReportDAOImpl.java

public List<Object[]> periodicallyOutstanding() {
    Session session = NewHibernateUtil.getSessionFactory().openSession();
    org.hibernate.Transaction transaction = session.beginTransaction();

    if (session != null) {
        try {/*  w ww.  j a  va 2 s  . co m*/

            SQLQuery query = session.createSQLQuery(
                    "SELECT sum(balance_amount) as outstanding,c.customer_name FROM outstandig_invoice oi\n"
                            + "inner join invoice i on oi.invoice_id=i.invoice_id\n"
                            + "inner join customer c on i.customer_id=c.customer_id\n"
                            + "group by c.customer_id");

            List<Object[]> inList = query.list();
            return inList;

        } catch (Exception e) {
            e.printStackTrace();

        } finally {
            if (session != null && session.isOpen()) {
                session.close();
            }
        }
    }
    return null;
}

From source file:com.vertec.daoimpl.ReportDAOImpl.java

public List<Object[]> branchWiseProduct(int branchId) {
    Session session = NewHibernateUtil.getSessionFactory().openSession();
    org.hibernate.Transaction transaction = session.beginTransaction();

    if (session != null) {
        try {//from  w w w. ja v a 2  s . co  m

            SQLQuery query = session.createSQLQuery(
                    "SELECT p.product_name,p.product_code,pm.selling_price,bp.quantity FROM branch_productmaster bp inner join product_master pm on bp.product_master_id=pm.product_master_id\n"
                            + "inner join branch b on bp.branch_id=b.branch_id\n"
                            + "inner join product p on pm.product_id=p.product_id\n"
                            + "where b.branch_id=:branchId");
            query.setParameter("branchId", branchId);
            List<Object[]> inList = query.list();
            return inList;

        } catch (Exception e) {
            e.printStackTrace();

        } finally {
            if (session != null && session.isOpen()) {
                session.close();
            }
        }
    }
    return null;
}

From source file:com.vertec.daoimpl.ReportDAOImpl.java

public List<Object[]> branchWiseProductMaster(int branchId) {
    Session session = NewHibernateUtil.getSessionFactory().openSession();
    org.hibernate.Transaction transaction = session.beginTransaction();

    if (session != null) {
        try {//from  w w w  . j av  a2s.  c  o  m

            SQLQuery query = session.createSQLQuery("SELECT p.product_name,p.product_code,bs.quantity\n"
                    + "FROM branch_stock bs inner join branch b on bs.branch_id=b.branch_id\n"
                    + "inner join product p on bs.product_id=p.product_id\n" + "where b.branch_id=:branchId");
            query.setParameter("branchId", branchId);
            List<Object[]> inList = query.list();
            return inList;

        } catch (Exception e) {
            e.printStackTrace();

        } finally {
            if (session != null && session.isOpen()) {
                session.close();
            }
        }
    }
    return null;
}

From source file:com.vertec.daoimpl.ReportDAOImpl.java

public List<Object[]> releaseProductBranchWise(String[] date, int branchId) {
    Session session = NewHibernateUtil.getSessionFactory().openSession();
    org.hibernate.Transaction transaction = session.beginTransaction();

    if (session != null) {
        try {/*from w  w  w  .jav  a2 s.c om*/

            SQLQuery query = session.createSQLQuery(
                    "SELECT p.product_name,p.product_code,pm.selling_price,sum(ii.quantity) as pQuan\n"
                            + "FROM invoice_item ii inner join invoice i on ii.invoice_id=i.invoice_id\n"
                            + "inner join product_master pm on ii.product_master_id=pm.product_master_id\n"
                            + "inner join product p on pm.product_id=p.product_id\n"
                            + "inner join branch b on i.branch_id=b.branch_id\n"
                            + "where b.branch_id=:branchId and i.invoiced_date between :fromDate and :toDate group by pm.product_master_id");
            query.setParameter("branchId", branchId);
            query.setParameter("fromDate", date[0]);
            query.setParameter("toDate", date[1]);
            List<Object[]> inList = query.list();
            return inList;

        } catch (Exception e) {
            e.printStackTrace();

        } finally {
            if (session != null && session.isOpen()) {
                session.close();
            }
        }
    }
    return null;
}

From source file:com.vertec.daoimpl.ReportDAOImpl.java

public List<Object[]> releaseProductPeriodicalyyVehicle(String[] date) {
    Session session = NewHibernateUtil.getSessionFactory().openSession();
    org.hibernate.Transaction transaction = session.beginTransaction();

    if (session != null) {
        try {//  www.j a  v a2s .c o  m

            SQLQuery query = session.createSQLQuery(
                    "SELECT p.product_name,p.product_code,pm.selling_price,sum(ii.quantity) as pQuan,v.vehicle_reg_no FROM invoice i\n"
                            + "inner join invoice_item ii on i.invoice_id=ii.invoice_id\n"
                            + "left join vehicle v on i.vehicle_id=v.vehicle_id\n"
                            + "inner join product_master pm on ii.product_master_id=pm.product_master_id\n"
                            + "inner join product p on pm.product_id=p.product_id\n"
                            + "where i.invoice_type='V' and i.invoiced_date between :fromDate and :toDate group by ii.product_master_id,v.vehicle_id");
            query.setParameter("fromDate", date[0]);
            query.setParameter("toDate", date[1]);
            List<Object[]> inList = query.list();
            return inList;

        } catch (Exception e) {
            e.printStackTrace();

        } finally {
            if (session != null && session.isOpen()) {
                session.close();
            }
        }
    }
    return null;
}