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

public String ChangeStatus(String id) {
    Session session = NewHibernateUtil.getSessionFactory().openSession();
    Transaction transaction = session.beginTransaction();

    if (session != null) {
        try {/*  ww  w  .j  a v  a  2 s. c om*/
            Date d = new Date();
            SQLQuery query = session.createSQLQuery(
                    "Update po_info set status=:status,received_date=:date where po_info_id=:po_info_id");

            query.setParameter("po_info_id", id);
            query.setParameter("date", d);
            query.setParameter("status", true);

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

public String updateNominalCode(NominalCode nc) {
    Session session = NewHibernateUtil.getSessionFactory().openSession();
    Transaction transaction = session.beginTransaction();
    if (session != null) {
        try {//from w w  w. jav  a  2 s.c o m
            SQLQuery query = session
                    .createSQLQuery("Update nominal_code set name=:name,code=:code where id=:Id");
            query.setParameter("name", nc.getName());
            query.setParameter("Id", nc.getId());
            query.setParameter("code", nc.getCode());
            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.NominalCodeDAOImpl.java

public String removeNominalCode(int funcId) {

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

    if (session != null) {
        try {/* w w  w  .ja v  a2s . com*/

            SQLQuery query = session.createSQLQuery("Update nominal_code set isvalid=:is_valid where id=:ncId");
            query.setParameter("ncId", funcId);
            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.PaymentDAOImpl.java

/**
 * call from PaymentController--> case "LoadCusBal"
 *
 * @param customerId//from ww w .j av  a2  s.  co 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/*from ww w.  j  av a2 s  .co m*/
 * @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 payment//from  w  ww.  j  ava 2 s . co m
 * @return
 */

public String updateChequeStatus(Payment payment) {
    Session session = NewHibernateUtil.getSessionFactory().openSession();
    Transaction transaction = session.beginTransaction();

    if (session != null) {
        try {

            SQLQuery query = session.createSQLQuery("update payment p\n"
                    + "inner join invoice_payment ip on p.payment_id=ip.payment_id\n"
                    + "inner join outstandig_invoice oi on ip.invoice_id=oi.invoice_id\n"
                    + "set p.is_cleared=:is_cleared,ip.balance_last=:balance_last,oi.balance_amount=:balance_amount\n"
                    + "where p.payment_id=:payment_id and ip.invoice_id=:invoice_id and p.payment_type_id=:payment_type_id");

            //                Query query = session.createQuery("UPDATE SysUser as s set s.firstName=:firstName, s.lastName=:lastName, s.contactNo=:contactNo where s.username=:username");
            query.setParameter("is_cleared", true);
            query.setParameter("balance_amount", payment.getAmount());
            query.setParameter("balance_last", payment.getAmount());
            query.setParameter("payment_id", payment.getPaymentId());
            query.setParameter("invoice_id", payment.getPaymentTypeId().getPtId());
            query.setParameter("payment_type_id", 2);

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

/**
 * call from PaymentController--> case "DoClearCheque"
 * @param arr// w  w w .j a va 2  s.co m
 * @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.PrivilegeDAOImpl.java

/**
 * call from PrivilegeController--> case "SetPrivilege"
 *
 * @param ugId/*from  www . java  2  s .c  o m*/
 * @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;
}

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

/**
 * call from PrivilegeController--> case "SetPrivilegeItem"
 *
 * @param groupId//from w  ww  .j  ava 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/*w  w  w .  ja  v a 2 s  .  c om*/
 * @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;
}