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

public int getSupplier(String po) {
    int sid = 0;/*  www. jav  a  2s . c o m*/
    SessionFactory sf = NewHibernateUtil.getSessionFactory();
    Session ses = sf.openSession();
    SQLQuery query = ses.createSQLQuery(
            "SELECT s.supplier_id,s.supplier_name FROM po_info pi inner join supplier s ON s.supplier_id=pi.supplier_supplier_id WHERE pi.po_info_id='"
                    + po + "'");

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

    for (Object[] list : inList) {
        sid = Integer.parseInt(list[0].toString());
    }
    ses.close();
    return sid;
}

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

public JSONObject LoadPO(String poid) {

    SessionFactory sf = NewHibernateUtil.getSessionFactory();
    Session ses = sf.openSession();/*from   w  ww.  j a  v  a2 s . com*/
    SQLQuery query = ses.createSQLQuery(
            "SELECT po.product_product_id,p.product_name,p.product_code,po.available_qty,po.price,po.total FROM purchasing_order po INNER JOIN product p on p.product_id=po.product_product_id WHERE po.po_info_po_info_id='"
                    + poid + "'");

    List<Object[]> inList = query.list();
    JSONObject mainObject = new JSONObject();
    JSONArray jarr = new JSONArray();
    JSONObject product = null;
    for (Object[] list : inList) {
        product = new JSONObject();
        product.put("pid", list[0].toString());
        product.put("pcode", list[2].toString());
        product.put("pname", list[1].toString());
        product.put("qty", list[3].toString());
        product.put("price", list[4].toString());
        jarr.add(product);
    }
    mainObject.put("data", jarr);

    ses.close();

    return mainObject;
}

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

public String UpdateBranchStock(Grn grnItems, Supplier supplier, SysUser user, Company company) {
    try {/*from w  ww .  j  a  v a2s  .co  m*/
        System.out.println("IN UPDATE Branch STOCK METHOD.....");
        SessionFactory sf = NewHibernateUtil.getSessionFactory();
        Session ses = sf.openSession();
        Transaction tr = ses.beginTransaction();
        System.out.println("SESSION CREATED");
        String pmid = "0";
        SQLQuery query = ses.createSQLQuery(
                "SELECT pm.product_master_id,whs.bpm_id FROM branch_productmaster whs INNER JOIN product_master pm on whs.product_master_id=pm.product_master_id WHERE pm.purchased_price=:pprice AND pm.selling_price=:sprice AND pm.product_id=:pId AND whs.type=:type");
        query.setParameter("pprice", grnItems.getPPrice());
        query.setParameter("sprice", grnItems.getSPrice());
        query.setParameter("pId", grnItems.getProductProductId().getProductId());
        query.setParameter("type", supplier.getType());
        System.out.println("QUERY OK");
        List<Object[]> inList = query.list();
        boolean bool = true;
        System.out.println("LINE 139");
        for (Object[] list : inList) {
            bool = false;
            System.out.println("PRODUCT IS NOT AVAILABLE IN BRANCH PRODUCT MASTER....");
            pmid = list[0].toString();

            SQLQuery query2 = ses
                    .createSQLQuery("Update branch_productmaster set quantity=quantity+:qty where bpm_id=:id ");
            query2.setParameter("id", list[1].toString());
            query2.setParameter("qty", grnItems.getQty());
            query2.executeUpdate();
            tr.commit();
            return VertecConstants.SUCCESS;
        }

        if (bool) {
            System.out.println("PRODUCT IS NOT AVAILABLE IN PRODUCT MASTER....");
            Date d = new Date();
            BranchProductmaster bpm = new BranchProductmaster();
            bpm.setBranchId(user.getBranchBranchId());
            bpm.setLastUpdatedDate(d);
            String promid = getProductMaster(grnItems.getProductProductId().getProductId().toString(),
                    grnItems.getPPrice().toString(), grnItems.getSPrice().toString());
            System.out.println("PRODUCT MASTER ID : " + promid);
            ProductMaster pm = new ProductMaster(Integer.parseInt(promid));
            bpm.setProductMasterId(pm);
            bpm.setQuantity(grnItems.getQty());
            bpm.setType(supplier.getType());
            bpm.setCompanyId(company);
            ses.save(bpm);
            ses.flush();
            tr.commit();
            return VertecConstants.SUCCESS;
        }
        ses.close();
    } catch (Exception e) {
        e.printStackTrace();
        return VertecConstants.ERROR;
    }
    return VertecConstants.FAILED;
}

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

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

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

    if (session != null) {
        try {//from  w w w.  j a  v  a 2s  . com
            SQLQuery query = session.createSQLQuery(
                    "select p.product_name,grn.qty,grn.p_price,grn.s_price,grn.total from grn grn inner join product p on grn.product_product_id=p.product_id where grn.grn_info_grn_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.InvoiceDAOImpl.java

/**
 * call from case "LoadCustomerInvoice" in InvoiceController
 *
 * @param customerId/*w w  w.j a va 2  s.co m*/
 * @return List<Object[]> invoiceListAccordingCustomers
 */
public List<Object[]> invoiceForCustomers(int customerId) {
    Session session = NewHibernateUtil.getSessionFactory().openSession();
    Transaction transaction = session.beginTransaction();

    if (session != null) {
        try {

            SQLQuery query = session.createSQLQuery(
                    "SELECT i.invoice_id,i.invoiced_date,i.tot_after_discount as payemble FROM outstandig_invoice oi inner join invoice i on oi.invoice_id=i.invoice_id  where oi.balance_amount>0 and i.customer_id=:customerId and i.is_valid=:is_valid and i.invoice_id not in (select invoice_id from invoice_payment)");
            query.setParameter("customerId", customerId);
            query.setParameter("is_valid", true);
            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.InvoiceDAOImpl.java

public List<Object[]> invoiceForAll(String customerId, String branch, String type, String from, String to,
        Company com) {//from   ww  w. ja  v  a  2  s.co  m
    Session session = NewHibernateUtil.getSessionFactory().openSession();
    if (session != null) {
        try {
            String sql = "SELECT i.invoice_id,i.invoiced_date,i.tot_after_discount as payemble FROM outstandig_invoice oi inner join invoice i on oi.invoice_id=i.invoice_id  where i.is_valid='1' and i.company_id='"
                    + com.getId() + "'";
            if (type.equals("2")) {
                sql += " and i.customer_id='" + customerId + "'";
            } else if (type.equals("3")) {
                sql += " and i.invoiced_date between '" + from + "' and '" + to + "'";
            } else if (type.equals("4")) {
                sql += " and i.invoiced_date like '" + from + "%' ";
            } else if (type.equals("5")) {
                sql += " and i.branch_id='" + branch + "' ";
            }
            SQLQuery query = session.createSQLQuery(sql);
            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.InvoiceDAOImpl.java

/**
 * call from InvoiceController--> case"SelectDates"
 *
 * @param arr//from   www  .j av a  2s .  c o m
 * @return
 */
public List<Object[]> invoiceAccordingToPeriod(String[] arr, Company com) {

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

    if (session != null) {
        try {
            String sql = "SELECT i.invoice_id,i.invoiced_date,c.customer_name,i.tot_after_discount FROM invoice i inner join customer c on i.customer_id=c.customer_id where i.is_valid='1' and i.company_id='"
                    + com.getId() + "' and i.invoiced_date between :fromDate and :toDate";

            if (!arr[2].equals("ALL")) {
                sql += " and i.branch_id='" + arr[2] + "'";
            }

            SQLQuery query = session.createSQLQuery(sql);
            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.InvoiceDAOImpl.java

public List<Object[]> CanceledinvoiceAccordingToPeriod(String[] arr) {

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

    if (session != null) {
        try {/*  w  w w  .j a v  a2  s  . c  o m*/
            String sql = "SELECT i.invoice_id,i.invoiced_date,c.customer_name,i.tot_after_discount FROM invoice i inner join customer c on i.customer_id=c.customer_id where i.is_valid='0' and i.invoiced_date between :fromDate and :toDate";

            if (!arr[2].equals("ALL")) {
                sql += " and i.branch_id='" + arr[2] + "'";
            }

            SQLQuery query = session.createSQLQuery(sql);
            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.InvoiceDAOImpl.java

public List<Object[]> invoiceAccordingToBranch(String branch, Company com) {
    Session session = NewHibernateUtil.getSessionFactory().openSession();
    if (session != null) {
        try {/*from w  w  w  . j  a v  a  2 s. co  m*/
            String hql = "";
            if (!branch.equals("0")) {
                hql = "SELECT i.invoice_id,i.invoiced_date,c.customer_name,i.tot_after_discount FROM invoice i inner join customer c on i.customer_id=c.customer_id where i.is_valid='1' and i.company_id='"
                        + com.getId() + "' and i.branch_id='" + branch + "'";
            } else {
                hql = "SELECT i.invoice_id,i.invoiced_date,c.customer_name,i.tot_after_discount FROM invoice i inner join customer c on i.customer_id=c.customer_id where i.is_valid='1' and i.company_id='"
                        + com.getId() + "'";
            }
            SQLQuery query = session.createSQLQuery(hql);
            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.InvoiceDAOImpl.java

public String ProductFromCategory(String id, int branchId) {
    String html = "";

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

    if (session != null) {
        try {/*from  w  w  w. j  a va  2s. co m*/
            //               SQLQuery query = session.createSQLQuery("Select p.product_id,p.product_code,p.product_name From branch_stock bs inner join product p on bs.product_id=p.product_id where bs.branch_id=:branchId and p.product_category_id='"+id+"' and bs.quantity>0 group by p.product_id order by p.product_code asc");
            SQLQuery query = session.createSQLQuery(
                    "Select p.product_id,p.product_code,p.product_name From branch_productmaster bpm inner join product_master pm on bpm.product_master_id=pm.product_master_id inner join product p on pm.product_id=p.product_id where bpm.branch_id=:branchId and p.product_category_id='"
                            + id + "' and bpm.quantity>0 group by p.product_id order by p.product_code asc");
            query.setParameter("branchId", branchId);

            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;
}