Example usage for org.hibernate SQLQuery setParameter

Introduction

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

Prototype

@Override
    NativeQuery<T> setParameter(int position, Object val);

Source Link

Usage

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 {/*  w w  w.j a  v  a 2 s .com*/

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

---

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

public String updateService(Service service) {
    Session session = NewHibernateUtil.getSessionFactory().openSession();
    Transaction transaction = session.beginTransaction();
    if (session != null) {
        try {// w w w .  j a va  2 s. co m
            SQLQuery query = session.createSQLQuery("Update service set service_name=:name where id=:Id");
            query.setParameter("name", service.getServiceName());
            query.setParameter("Id", service.getId());
            query.executeUpdate();
            transaction.commit();
            return VertecConstants.UPDATED;
        } catch (Exception e) {
            e.printStackTrace();
            return VertecConstants.ERROR;
        } finally {
            if (session != null && session.isOpen()) {
                session.close();
            }
        }
    }
    return null;
}

---

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

public String removeService(int serviceId) {

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

    if (session != null) {
        try {/*from   w  w  w. ja  v a2 s.  c o m*/

            SQLQuery query = session.createSQLQuery("Update service set is_valid=:is_valid where id=:serId");

            query.setParameter("serId", serviceId);
            query.setParameter("is_valid", false);

            query.executeUpdate();

            transaction.commit();
            return VertecConstants.UPDATED;

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

    return null;

}

---

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

/**
 * call from InvoiceController case "ToInvoice" call from StockController
 * case "ToBranch" and "ToVehicle"//from   w  ww.j a  va  2s .  co  m
 *
 * @param branchId
 * @return
 */
public List<Object[]> loadProductFromBranchStock(int branchId) {
    Session session = NewHibernateUtil.getSessionFactory().openSession();
    Transaction transaction = session.beginTransaction();

    if (session != null) {
        try {
            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 group by p.product_id order by p.product_code asc");
            query.setParameter("branchId", branchId);
            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.StockDAOImpl.java

/**
 * from StockController case "ToBranchPM"
 *
 * @param arr//w  w  w .  j  av a  2s  .  co m
 * @return
 */
public List<Object[]> loadProductMasterFromBPM(int[] arr) {
    Session session = NewHibernateUtil.getSessionFactory().openSession();
    Transaction transaction = session.beginTransaction();

    System.out.println("AAA");
    if (session != null) {
        try {
            SQLQuery query = session.createSQLQuery(
                    "SELECT bp.bpm_id,bp.product_master_id,p.product_id,p.product_code,p.product_name,bp.quantity,pm.purchased_price,pm.selling_price\n"
                            + "FROM branch_productmaster bp inner join product_master pm on bp.product_master_id=pm.product_master_id\n"
                            + "inner join product p on pm.product_id=p.product_id where bp.branch_id=:branch_id and p.product_id=:product_id");
            query.setParameter("branch_id", arr[0]);
            query.setParameter("product_id", arr[1]);

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

/**
 * from StockController case "ToAddBPM"//from ww  w  .j  a  v  a  2s  . c o  m
 *
 * @param arr
 * @return
 */
public List<Object[]> loadProductMasterToAddBPM(int[] arr) {
    Session session = NewHibernateUtil.getSessionFactory().openSession();
    Transaction transaction = session.beginTransaction();

    if (session != null) {
        try {
            SQLQuery query = session.createSQLQuery(
                    "SELECT * FROM product_master pm where pm.product_id=:product_id and pm.product_master_id not in (SELECT product_master_id FROM branch_productmaster where branch_id=:branchId);");
            //                SQLQuery query = session.createSQLQuery("SELECT pm.product_master_id,pm.product_id,pm.purchased_price,pm.selling_price,pm.is_available FROM product_master pm inner join branch_productmaster bpm on bpm.product_master_id=pm.product_master_id where pm.product_id:product_id and bpm.branch_id=:branchId ;");

            query.setParameter("branchId", arr[0]);
            query.setParameter("product_id", arr[1]);
            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.StockDAOImpl.java

/**
 * call from StockController case "ToAddNewVehicleStock"
 *
 * @param arr/*  w ww  .ja va2 s. com*/
 * @return
 */
public List<Object[]> loadToAddVehicleStock(int[] arr) {
    Session session = NewHibernateUtil.getSessionFactory().openSession();
    Transaction transaction = session.beginTransaction();

    if (session != null) {
        try {
            SQLQuery query = session.createSQLQuery(
                    "SELECT bp.bpm_id,bp.product_master_id,pmr.purchased_price,pmr.selling_price,bp.quantity FROM branch_productmaster bp\n"
                            + "inner join product_master pmr on bp.product_master_id=pmr.product_master_id\n"
                            + "where pmr.product_id=:product_id and bp.branch_id=:branch_id and pmr.is_available=:is_available and bp.bpm_id not in (SELECT vs.bpm_id\n"
                            + "FROM vehicle_stock vs\n" + "inner join vehicle v on vs.vehicle_id=v.vehicle_id\n"
                            + "inner join branch_productmaster bpm on vs.bpm_id=bpm.bpm_id\n"
                            + "inner join product_master pm on bpm.product_master_id=pm.product_master_id where pm.product_id=:product_id and v.branch_id=:branch_id and vs.vehicle_id=:vehicle_id)");
            query.setParameter("branch_id", arr[0]);
            query.setParameter("product_id", arr[1]);
            query.setParameter("vehicle_id", arr[2]);
            query.setParameter("is_available", true);
            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.StockDAOImpl.java

/**
 * call from StockController case "ToUpdateVehicleStock"
 *
 * @param arr/*from www.  j  a v  a2s . c o m*/
 * @return
 */
public List<Object[]> loadToUpdateVehicleStock(int[] arr) {
    Session session = NewHibernateUtil.getSessionFactory().openSession();
    Transaction transaction = session.beginTransaction();

    if (session != null) {
        try {
            SQLQuery query = session.createSQLQuery(
                    "SELECT vs.vehicle_stock_id,vs.bpm_id,vs.quantity as vquan,pm.purchased_price,pm.selling_price,bpm.quantity FROM vehicle_stock vs\n"
                            + "inner join branch_productmaster bpm on vs.bpm_id=bpm.bpm_id\n"
                            + "inner join product_master pm on bpm.product_master_id=pm.product_master_id\n"
                            + "where pm.product_id=:product_id and vs.vehicle_id=:vehicle_id and pm.is_available=:is_available");
            query.setParameter("product_id", arr[1]);
            query.setParameter("vehicle_id", arr[2]);
            query.setParameter("is_available", true);
            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.StockDAOImpl.java

/**
 * call from InvoiceController case "LoadBPMToInvoice"
 *
 * @param arr//from   w ww. j a  v  a 2 s  . c o m
 * @return
 */
public List<Object[]> loadBPMForInvoice(int[] arr) {
    Session session = NewHibernateUtil.getSessionFactory().openSession();
    Transaction transaction = session.beginTransaction();

    if (session != null) {
        try {
            SQLQuery query = session.createSQLQuery(
                    "SELECT bpm.bpm_id,bpm.product_master_id,bpm.quantity as branchquan,pm.purchased_price,pm.selling_price\n"
                            + "FROM branch_productmaster bpm inner join product_master pm on (bpm.product_master_id=pm.product_master_id)\n"
                            + "where pm.product_id=:product_id and bpm.branch_id=:branch_id and pm.is_available=:is_available");
            query.setParameter("product_id", arr[1]);
            query.setParameter("branch_id", arr[0]);
            query.setParameter("is_available", true);
            List<Object[]> bpmList = query.list();
            return bpmList;

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

    return null;
}

---

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

public List<Object[]> loadWSForInvoice(int pro) {
    Session session = NewHibernateUtil.getSessionFactory().openSession();

    if (session != null) {
        try {/*from   w w w.  j  a  va2  s.  c om*/

            SQLQuery query2 = session.createSQLQuery(
                    "select ws.warehouse_stock_id,ws.qty,pm.purchased_price,pm.selling_price from warehouse_stock ws inner join product_master pm on pm.product_master_id=ws.product_master_product_master_id where pm.product_id=:product_id and ws.qty>0");

            //                SQLQuery query = session.createSQLQuery("SELECT bpm.bpm_id,bpm.product_master_id,bpm.quantity as branchquan,pm.purchased_price,pm.selling_price\n"
            //                        + "FROM branch_productmaster bpm inner join product_master pm on (bpm.product_master_id=pm.product_master_id)\n"
            //                        + "where pm.product_id=:product_id and bpm.branch_id=:branch_id and pm.is_available=:is_available");
            query2.setParameter("product_id", pro);
            //                query.setParameter("is_available", true);
            List<Object[]> bpmList = query2.list();
            return bpmList;

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

    return null;
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48
• 49
• 50
• »