List of usage examples for org.hibernate SQLQuery setParameter
@Override
NativeQuery<T> setParameter(int position, Object val);
From source file:com.vertec.daoimpl.GINDAOImpl.java
public String UpdateBranchStock(Gin ginItems) { try {/*from w ww . ja v a 2 s.co m*/ Session ses = NewHibernateUtil.getSessionFactory().openSession(); Transaction tr = ses.beginTransaction(); System.out.println("PRODUCT IS NOT AVAILABLE IN BRANCH PRODUCT MASTER...."); SQLQuery query2 = ses .createSQLQuery("Update branch_productmaster set quantity=quantity-:qty where bpm_id=:id "); query2.setParameter("id", ginItems.getBpmId().getBpmId()); query2.setParameter("qty", ginItems.getQty()); query2.executeUpdate(); tr.commit(); ses.close(); return VertecConstants.SUCCESS; } catch (Exception e) { e.printStackTrace(); return VertecConstants.ERROR; } }
From source file:com.vertec.daoimpl.GINDAOImpl.java
public String UpdateMRN(String poId, String productId, String qty) { Session session = NewHibernateUtil.getSessionFactory().openSession(); Transaction transaction = session.beginTransaction(); if (session != null) { try {//w ww. j a va2 s. co m SQLQuery query = session.createSQLQuery( "Update mrn set available_qty=available_qty-:qty where product_id=:productId and mrn_info_id=:mrnId"); query.setParameter("mrnId", poId); query.setParameter("productId", productId); query.setParameter("qty", qty); query.executeUpdate(); transaction.commit(); return VertecConstants.SUCCESS; } catch (Exception e) { e.printStackTrace(); return VertecConstants.ERROR; } finally { if (session != null && session.isOpen()) { session.close(); } } } return null; }
From source file:com.vertec.daoimpl.GINDAOImpl.java
public String getProductMaster(String pid, String pprice, String sprice) { String pmid = "0"; SessionFactory sf = NewHibernateUtil.getSessionFactory(); Session ses = sf.openSession();/* ww w.j a v a2 s .co m*/ Transaction tr = ses.beginTransaction(); SQLQuery query = ses.createSQLQuery( "SELECT product_id,product_master_id FROM product_master WHERE product_id=:productId AND purchased_price=:pprice AND selling_price=:sprice"); query.setParameter("productId", pid); query.setParameter("pprice", pprice); query.setParameter("sprice", sprice); List<Object[]> inList = query.list(); boolean bool = true; for (Object[] list : inList) { System.out.println("PRODUCT MASTER IS EXIST...............ID : " + list[1].toString()); bool = false; pmid = list[1].toString(); } if (bool) { try { System.out.println("PRODUCT MASTER IS NOT EXIST.............. "); ProductMaster pm = new ProductMaster(); pm.setPurchasedPrice(Double.valueOf(pprice)); pm.setSellingPrice(Double.valueOf(sprice)); Product pro = new PoDAOImpl().getProduct(Integer.valueOf(pid)); pm.setProductId(pro); pm.setIsAvailable(true); ses.save(pm); ses.flush(); tr.commit(); SQLQuery query1 = ses.createSQLQuery( "SELECT product_id,product_master_id FROM product_master WHERE product_id=:productId AND purchased_price=:pprice AND selling_price=:sprice"); query1.setParameter("productId", Integer.valueOf(pid)); query1.setParameter("pprice", Double.valueOf(pprice)); query1.setParameter("sprice", Double.valueOf(sprice)); List<Object[]> inList1 = query1.list(); for (Object[] list : inList1) { System.out.println("PRODUCT MASTER IS CREATED...............ID : " + list[1].toString()); bool = false; pmid = list[1].toString(); return list[1].toString(); } } catch (Exception e) { e.printStackTrace(); return null; } } ses.close(); return pmid; }
From source file:com.vertec.daoimpl.GINDAOImpl2.java
public String UpdateWarehouseStock(String whsId, String removeQty) { try {/*from w ww . j a va 2 s .c o m*/ Session ses = NewHibernateUtil.getSessionFactory().openSession(); Transaction tr = ses.beginTransaction(); SQLQuery query2 = ses .createSQLQuery("Update warehouse_stock set qty=qty-:qty where warehouse_stock_id=:id "); query2.setParameter("id", whsId); query2.setParameter("qty", removeQty); query2.executeUpdate(); tr.commit(); return VertecConstants.SUCCESS; } catch (Exception ex) { ex.printStackTrace(); return VertecConstants.ERROR; } }
From source file:com.vertec.daoimpl.GRNDAOImpl.java
public String UpdateBranchStock(Grn grnItems, Supplier supplier, SysUser user, Company company) { try {/*w w w. jav a 2 s . c o 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 UpdatePO(String poId, String productId, String qty) { Session session = NewHibernateUtil.getSessionFactory().openSession(); Transaction transaction = session.beginTransaction(); if (session != null) { try {// ww w . j a va 2 s . com SQLQuery query = session.createSQLQuery( "Update purchasing_order set available_qty=available_qty-:qty where product_product_id=:productId and po_info_po_info_id=:poId"); query.setParameter("poId", poId); query.setParameter("productId", productId); query.setParameter("qty", qty); query.executeUpdate(); transaction.commit(); return VertecConstants.SUCCESS; } catch (Exception e) { e.printStackTrace(); return VertecConstants.ERROR; } finally { if (session != null && session.isOpen()) { session.close(); } } } return null; }
From source file:com.vertec.daoimpl.InvoiceDAOImpl.java
/** * call from case "LoadCustomerInvoice" in InvoiceController * * @param customerId//from www . ja v a 2s . 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
/** * call from InvoiceController--> case"SelectDates" * * @param arr//from w w w .j av a 2s. co 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 {// www .ja v a 2 s . co 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 String ProductFromCategory(String id, int branchId) { String html = ""; Session session = NewHibernateUtil.getSessionFactory().openSession(); if (session != null) { try {/*from w ww. ja v a 2 s.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; }