Example usage for org.hibernate SQLQuery setParameter

List of usage examples for org.hibernate SQLQuery setParameter

  1. HOME
  2. Java
  3. org.hibernate
  4. org.hibernate.*
  5. SQLQuery
  6. 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.AttendanceDAOImpl.java

public String IgnoreLeave(int em) {
    Session session = NewHibernateUtil.getSessionFactory().openSession();
    Transaction transaction = session.beginTransaction();
    if (session != null) {
        try {//from ww w .ja  va2 s  .co  m
            SQLQuery query = session.createSQLQuery("Update Leaves set is_approved=:isApproved where id=:id");
            query.setParameter("isApproved", false);
            query.setParameter("id", em);
            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.AttendanceDAOImpl.java

public String UpdateAttendance(SysUser user) {
    Session session = NewHibernateUtil.getSessionFactory().openSession();
    try {/*from w  ww  . ja v  a 2s . c o m*/
        String filename = "";
        DateFormat dateformat = new SimpleDateFormat("yyyy-MM-dd");
        DateFormat timeformat = new SimpleDateFormat("hh:mm");
        EmployeeDAOImpl ed = new EmployeeDAOImpl();
        Query query = session.createQuery("SELECT f FROM Fpfiles f");
        List<Fpfiles> fp = query.list();
        for (Fpfiles file : fp) {
            filename = file.getFilename();
        }

        List<Object> data = readexcel(filename);
        int UIDcol = 0;
        int DateTimecol = 0;
        for (Object object : data) {
            List<String> rows = (List<String>) object;
            int i = 0;
            for (String string : rows) {
                i++;
                System.out.println(i + ">>>>" + string);
                if (string.contains("UID")) {
                    System.out.println("GOT UID as :" + i + " " + string);
                    UIDcol = i;
                } else if (string.contains("DateTime")) {
                    System.out.println("GOT DATETIME as :" + i + " " + string);
                    DateTimecol = i;
                }
            }
        }

        boolean bool = false;
        for (Object object : data) {
            if (bool) {
                List<String> rows = (List<String>) object;
                int incr = 0;
                int UID = 0;
                Date Datetime = null;
                for (String string : rows) {
                    incr++;

                    System.out.println(incr + ">>>>" + string);

                    if (incr == UIDcol) {

                        UID = Integer.parseInt(string.replace(".0", ""));
                        System.out.println(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>UID " + string);
                    } else if (incr == DateTimecol) {
                        System.out.println(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>Date " + string);
                        DateFormat formatter = new SimpleDateFormat("E MMM dd HH:mm:ss Z yyyy");
                        Date date = (Date) formatter.parse(string);
                        //                            System.out.println(date);

                        Calendar cal = Calendar.getInstance();
                        cal.setTime(date);
                        //                            System.out.println(cal.get(Calendar.DATE));
                        //                            System.out.println(cal.get(Calendar.HOUR));
                        //                            System.out.println(cal.get(Calendar.MINUTE));
                        //
                        //                            System.out.println(cal.get(Calendar.YEAR) + "-" + cal.get(Calendar.MONTH) + "-" + cal.get(Calendar.DATE));
                        //                            System.out.println(cal.get(Calendar.HOUR_OF_DAY) + ":" + cal.get(Calendar.MINUTE));

                        String cmonth = "0";
                        if (cal.get(Calendar.MONTH) <= 9) {
                            cmonth += cal.get(Calendar.MONTH) + 1;
                        } else {
                            cmonth = cal.get(Calendar.MONTH) + 1 + "";
                        }

                        String cdate = "0";
                        if (cal.get(Calendar.DATE) <= 9) {
                            cdate += cal.get(Calendar.DATE);
                        } else {
                            cdate = cal.get(Calendar.DATE) + "";
                        }

                        String chour = "0";
                        if (cal.get(Calendar.HOUR) <= 9) {
                            chour += cal.get(Calendar.HOUR);
                        } else {
                            chour = cal.get(Calendar.HOUR) + "";
                        }

                        String cminutes = "0";
                        if (cal.get(Calendar.MINUTE) <= 9) {
                            cminutes += cal.get(Calendar.MINUTE);
                        } else {
                            cminutes = cal.get(Calendar.MINUTE) + "";
                        }

                        String cdate2 = cal.get(Calendar.YEAR) + "-" + cmonth + "-" + cdate;

                        String ctime = chour + ":" + cminutes;
                        Employee emp = ed.getEmployeeByUID(UID + "");

                        if (emp != null) {

                            Session session1 = NewHibernateUtil.getSessionFactory().openSession();
                            Query query1 = session1.createQuery(
                                    "SELECT a FROM Attendance a WHERE a.date=:date AND a.employeeId=:eid");
                            query1.setParameter("eid", emp);
                            query1.setParameter("date", dateformat.parse(cdate2));
                            Attendance att = (Attendance) query1.uniqueResult();
                            System.out.println("$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$");
                            System.out.println(att);
                            System.out.println("$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$");
                            session1.close();
                            if (att == null) {
                                System.out.println("Intime");
                                Attendance a = new Attendance();
                                a.setDate(dateformat.parse(cdate2));
                                a.setMarkedDate(new Date());
                                System.out.println("><<><><><<><><><><><><><><><><><<><>");
                                System.out.println(
                                        "____________________________________________________________");
                                System.out.println(UID);
                                System.out.println(emp.getFname());
                                System.out.println(
                                        "____________________________________________________________");
                                System.out.println("><<><><><<><><><><><><><><><><><<><>");
                                a.setEmployeeId(ed.getEmployeeByUID(UID + ""));
                                a.setIsValid(true);
                                a.setOuttime(null);
                                a.setIntime(cal.getTime());
                                a.setMarkedBy(user);
                                Save.Save(a);
                                System.out.println("Saved as New Row");
                            } else {
                                System.out.println("Out Time");
                                Session session2 = NewHibernateUtil.getSessionFactory().openSession();

                                //                                Query query2 = session.createQuery("SELECT a FROM Attendance a WHERE a.date='" + cdate2 + "' AND a.employeeId=(select id from Employee where fpid='" + UID + "') AND a.intime like '" + ctime + "%' AND a.outtime='' ");
                                //                                List<Attendance> att2 = query2.list();
                                //                                if (att2 != null) {
                                SQLQuery query3 = session2.createSQLQuery(
                                        "Update attendance a set a.outtime=:outtime where a.id=:id");
                                query3.setParameter("outtime", cal.getTime());
                                query3.setParameter("id", att);
                                query3.executeUpdate();
                                Transaction tr = session2.beginTransaction();
                                tr.commit();
                                System.out.println("Updated a Row");
                                //                                }
                                session2.close();
                            }
                        }
                    }

                }

            } else {
                bool = true;
            }
        }
        //            System.out.println("UID COLUMN : " + UIDcol);
        //            System.out.println("Date Time COLUMN : " + DateTimecol);
    } catch (Exception e) {
        e.printStackTrace();
        return VertecConstants.ERROR;
    } finally {
        if (session != null && session.isOpen()) {
            session.close();
        }
    }
    return VertecConstants.SUCCESS;
}

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

public String updateBudget(BudgetPlan bp) {

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

    if (session != null) {
        try {/*from w w w  .j  ava 2s .  c  o  m*/

            SQLQuery query = session.createSQLQuery(
                    "Update budget_plan set amount=:amount,date=:date,sys_user_id=:user where nominal_code_id=:ncid and year=:year and month=:month");
            System.out.println("GOT QUERY....");
            query.setParameter("amount", bp.getAmount());
            query.setParameter("date", new Date());
            query.setParameter("user", bp.getSysUserId());
            query.setParameter("ncid", bp.getNominalCodeId());
            query.setParameter("year", bp.getYear());
            query.setParameter("month", bp.getMonth());

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

public String removeCustomer(int customerId) {
    Session session = NewHibernateUtil.getSessionFactory().openSession();
    Transaction transaction = session.beginTransaction();

    if (session != null) {
        try {/* w  w  w. j a va  2s  . c o m*/

            SQLQuery query = session
                    .createSQLQuery("Update customer set is_active=:is_active where customer_id=:customer_id");

            query.setParameter("customer_id", customerId);
            query.setParameter("is_active", 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.CustomerDAOImpl.java

public String updateCustomer(Customer customer) {

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

    if (session != null) {
        try {//from  w  w  w .j a v  a  2  s  .com

            SQLQuery query = session.createSQLQuery(
                    "Update customer set customer_name=:customer_name,address=:address,hotline=:hotline,office_no=:office_no,fax_no=:fax_no,contact_person=:contact_person,contact_person_no=:contact_person_no,contact_person_email=:contact_person_email,credit_limit=:credit_limit,credit_period=:credit_period where customer_id=:customer_id");

            query.setParameter("customer_name", customer.getCustomerName());
            query.setParameter("address", customer.getAddress());
            query.setParameter("hotline", customer.getHotline());
            query.setParameter("office_no", customer.getOfficeNo());
            query.setParameter("fax_no", customer.getFaxNo());
            query.setParameter("contact_person", customer.getContactPerson());
            query.setParameter("contact_person_no", customer.getContactPersonNo());
            query.setParameter("contact_person_email", customer.getContactPersonEmail());
            query.setParameter("customer_id", customer.getCustomerId());
            query.setParameter("credit_limit", customer.getCreditLimit());
            query.setParameter("credit_period", customer.getCreditPeriod());

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

public String updateEmployee(Employee em) {
    Session session = NewHibernateUtil.getSessionFactory().openSession();
    Transaction transaction = session.beginTransaction();
    if (session != null) {
        try {//from   w w  w .  j a  v  a2  s.c  o  m
            SQLQuery query = session.createSQLQuery(
                    "Update employee set fname=:fname,appoint=:appoint,employee_type_id=:etype,experience=:exp,qualification=:quo,educational=:edu,height=:height,weight=:weight,contact_no=:contact,emergency=:emergency,lname=:lname,dob=:dob,nic=:nic,gender=:gender,basic_salary=:basic_salary,address=:address,designation_id=:designation_id,fpid=:fpid where id=:id");
            query.setParameter("fname", em.getFname());
            query.setParameter("lname", em.getLname());
            query.setParameter("dob", em.getDob());
            query.setParameter("nic", em.getNic());
            query.setParameter("gender", em.getGender());
            query.setParameter("basic_salary", em.getBasicSalary());
            query.setParameter("address", em.getAddress());
            query.setParameter("designation_id", em.getDesignationId());
            query.setParameter("fpid", em.getFpid());
            query.setParameter("id", em.getId());
            query.setParameter("contact", em.getContactNo());
            query.setParameter("emergency", em.getEmergency());
            query.setParameter("weight", em.getWeight());
            query.setParameter("height", em.getHeight());
            query.setParameter("edu", em.getEducational());
            query.setParameter("exp", em.getExperience());
            query.setParameter("quo", em.getQualification());
            query.setParameter("appoint", em.getAppoint());
            query.setParameter("etype", em.getEmployeeTypeId());

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

public String deleteEmployee(String id) {
    Session session = NewHibernateUtil.getSessionFactory().openSession();
    Transaction transaction = session.beginTransaction();
    if (session != null) {
        try {// w w w  . java  2 s .c o m
            SQLQuery query = session.createSQLQuery("Update employee set is_valid='0' where id=:id");
            query.setParameter("id", Integer.parseInt(id));
            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.ExpensesDAOImpl.java

public String updateProjectExpenses(ProjectExpenses exp) {
    Session session = NewHibernateUtil.getSessionFactory().openSession();
    Transaction transaction = session.beginTransaction();

    if (session != null) {
        try {//from   w  ww  . j a  va2  s  .co  m

            SQLQuery query = session
                    .createSQLQuery("Update project_expenses set description=:des,amount=:amt where id=:eId");

            query.setParameter("eId", exp.getId());
            query.setParameter("des", exp.getDescription());
            query.setParameter("amt", exp.getAmount());

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

public String updateFunction(FunctionData func) {
    Session session = NewHibernateUtil.getSessionFactory().openSession();
    Transaction transaction = session.beginTransaction();
    if (session != null) {
        try {//w w w  . j  av a 2 s .  c o m
            SQLQuery query = session
                    .createSQLQuery("Update function_data set name=:name,state_id=:stateId where id=:Id");
            query.setParameter("name", func.getName());
            query.setParameter("stateId", func.getStateId());
            query.setParameter("Id", func.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.FunctionDataDAOImpl.java

public String removeFunction(int funcId) {

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

    if (session != null) {
        try {/*from w  w w.j  a  va 2 s  .  c o  m*/

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

            query.setParameter("serId", 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;

}