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.InvoiceDAOImpl.java

public String ProductFromCategoryWarehouse(String id) {
    String html = "";

    Session session = NewHibernateUtil.getSessionFactory().openSession();

    if (session != null) {
        try {//from w  w w . j av a2 s  .  c o  m
            SQLQuery query = session.createSQLQuery(
                    "Select p.product_id,p.product_code,p.product_name From warehouse_stock bpm inner join product_master pm on bpm.product_master_product_master_id=pm.product_master_id inner join product p on pm.product_id=p.product_id where p.product_category_id='"
                            + id + "' and bpm.qty>0 group by p.product_id order by p.product_code asc");

            List<Object[]> inList = query.list();
            for (Object[] list : inList) {
                html += list[0] + ":::::" + list[1] + ":::::" + list[2] + ";;;;;";
            }

            if (!html.equals("")) {
                html = html.substring(0, html.length() - 5);
            }

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

    return html;
}

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

public String getItems(String id) {
    String html = "";
    Session session = NewHibernateUtil.getSessionFactory().openSession();
    if (session != null) {
        try {//from  w  w w . ja  va  2s.co m
            SQLQuery query = session.createSQLQuery(
                    "select p.product_name,po.qty from prn_item po inner join product p on po.product_product_id=p.product_id where po.prn_info_id='"
                            + id + "'");
            List<Object[]> inList = query.list();
            for (Object[] list : inList) {
                html += "<tr><td>" + list[0].toString() + "</td><td>" + list[1].toString() + "</td><tr>";
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (session != null && session.isOpen()) {
                session.close();
            }
        }
    }
    return html;
}

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

public String getItems1(String id) {
    String html = "";

    Session session = NewHibernateUtil.getSessionFactory().openSession();

    if (session != null) {
        try {/*  ww w.ja v  a  2s .  c om*/
            SQLQuery query = session.createSQLQuery(
                    "select p.product_name,mrn.qty,mrn.available_qty from mrn mrn inner join product p on mrn.product_id=p.product_id where mrn.mrn_info_id='"
                            + id + "'");

            List<Object[]> inList = query.list();
            for (Object[] list : inList) {
                html += "<tr><td>" + list[0].toString() + "</td><td>" + list[1].toString() + "</td><td>"
                        + list[2].toString() + "</td><tr>";
            }

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

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

public String ProductFromCategory(String id) {
    String html = "";

    Session session = NewHibernateUtil.getSessionFactory().openSession();

    if (session != null) {
        try {//from w  w w  .ja  v  a2  s.  c o m
            SQLQuery query = session.createSQLQuery(
                    "SELECT product_id,product_code,product_name FROM product where product_category_id='" + id
                            + "'");

            List<Object[]> inList = query.list();
            for (Object[] list : inList) {
                html += list[0] + ":::::" + list[1] + ":::::" + list[2] + ";;;;;";
            }

            if (!html.equals("")) {
                html = html.substring(0, html.length() - 5);
            }

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

    return html;
}

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

/**
 * call from PaymentController--> case "LoadCusBal"
 *
 * @param customerId//from   ww  w  .ja  va2s. c o  m
 * @return
 */

public List<Object[]> loadAccordigCus(int customerId) {

    Session session = NewHibernateUtil.getSessionFactory().openSession();
    Transaction transaction = session.beginTransaction();

    if (session != null) {
        try {

            SQLQuery query = session.createSQLQuery(
                    "SELECT oi.oi_id,oi.balance_amount,oi.invoice_id,i.tot_after_discount,i.invoiced_date\n"
                            + "FROM outstandig_invoice oi inner join invoice i on oi.invoice_id=i.invoice_id\n"
                            + "where oi.balance_amount>0 and i.customer_id=:customerId and i.is_valid=true");
            query.setParameter("customerId", customerId);
            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.PaymentDAOImpl.java

/**
 * call from PaymentController--> case "ChequeStatus"
 *
 * @param customerId//w  w w .j a v  a  2 s.c  om
 * @return
 */

public List<Object[]> loadChequeDetails(int customerId) {
    Session session = NewHibernateUtil.getSessionFactory().openSession();
    Transaction transaction = session.beginTransaction();

    if (session != null) {
        try {

            SQLQuery query = session.createSQLQuery(
                    "SELECT p.payment_id,p.cheque_no,p.bank_name,p.amount,i.invoice_id,p.cheque_date\n"
                            + "FROM payment p\n"
                            + "inner join invoice_payment ip on p.payment_id=ip.payment_id\n"
                            + "inner join invoice i on ip.invoice_id=i.invoice_id\n"
                            + "where p.payment_type_id=:payment_type_id and p.is_cleared=:is_cleared and i.customer_id=:customerId group by ip.payment_id");
            query.setParameter("payment_type_id", 2);
            query.setParameter("customerId", customerId);
            query.setParameter("is_cleared", false);
            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.PaymentDAOImpl.java

/**
 * call from PaymentController--> case "DoClearCheque"
 * @param arr//  w ww  . j  ava 2  s .com
 * @return
 */

public List<Object[]> paymentsAccordingToPeriod(String[] arr, Company com) {
    Session session = NewHibernateUtil.getSessionFactory().openSession();
    if (session != null) {
        try {
            SQLQuery query = session.createSQLQuery(
                    "SELECT p.payment_id,if(payment_type_id=2,'Cheque','Cash') as ptype,if(is_cleared=true,'Cleared','Not Cleared') as pstatus,\n"
                            + "p.payment_date,p.amount,ip.invoice_id,i.tot_after_discount,ip.balance_last,c.customer_name\n"
                            + "FROM payment p inner join invoice_payment ip on p.payment_id=ip.payment_id\n"
                            + "inner join invoice i on ip.invoice_id=i.invoice_id inner join customer c on i.customer_id=c.customer_id\n"
                            + "where i.company_id='" + com.getId()
                            + "' and p.payment_date between :fromDate and :toDate");
            query.setParameter("fromDate", arr[0]);
            query.setParameter("toDate", arr[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.PoDAOImpl.java

public String getItems1(String id) {
    String html = "";

    Session session = NewHibernateUtil.getSessionFactory().openSession();

    if (session != null) {
        try {//from www  . j av a  2s .c o m
            SQLQuery query = session.createSQLQuery(
                    "select p.product_name,po.qty,po.available_qty,po.price,po.total from purchasing_order po inner join product p on po.product_product_id=p.product_id where po_info_po_info_id='"
                            + id + "'");

            List<Object[]> inList = query.list();
            for (Object[] list : inList) {
                html += "<tr><td>" + list[0].toString() + "</td><td>" + list[1].toString() + "</td><td>"
                        + list[2].toString() + "</td><td>" + list[3].toString() + "</td><td>"
                        + list[4].toString() + "</td><tr>";
            }

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

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

public String ProductFromCategory(String id, Company company) {
    String html = "";

    Session session = NewHibernateUtil.getSessionFactory().openSession();

    if (session != null) {
        try {//  www. j a  v a  2 s .co  m
            SQLQuery query = session.createSQLQuery(
                    "SELECT product_id,product_code,product_name FROM product where product_category_id='" + id
                            + "' AND company_id='" + company.getId() + "'");

            List<Object[]> inList = query.list();
            for (Object[] list : inList) {
                html += list[0] + ":::::" + list[1] + ":::::" + list[2] + ";;;;;";
            }

            if (!html.equals("")) {
                html = html.substring(0, html.length() - 5);
            }

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

    return html;
}

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

/**
 * call from PrivilegeController--> case "SetPrivilege"
 *
 * @param ugId//from w  w  w.j  a v a2s  . com
 * @return
 */
public List<Object[]> loadNotInAllPrivileges(int ugId) {
    Session session = NewHibernateUtil.getSessionFactory().openSession();
    Transaction transaction = session.beginTransaction();

    if (session != null) {
        try {
            SQLQuery query = session.createSQLQuery(
                    "SELECT * FROM priviledge where priviledge_id not in (SELECT priviledge_id FROM user_group_priviledge 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;
}