Unable to update row in MySQL using hibernate

Question

public class UpdateEmployee extends HttpServlet {
    private static final long serialVersionUID = 1L;
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException 
    {
        PrintWriter out= response.getWriter();
        EmployeeDetails emp = new EmployeeDetails();
        SessionFactory fact=new Configuration().configure().buildSessionFactory();
        Session ses = fact.openSession();
        int emp_id = Integer.parseInt(request.getParameter("emp_id"));
        int new_emp_id = Integer.parseInt(request.getParameter("new_emp_id"));
        String emp_name = request.getParameter("name");
        String emp_designation_id = request.getParameter("designationid");
        String emp_skills = request.getParameter("skills");
        String emp_department_id = request.getParameter("departmentid");
        String emp_notes = request.getParameter("notes");
        String emp_email = request.getParameter("email");
        String emp_phone = request.getParameter("phone");
        String emp_username = request.getParameter("username");
        String emp_password = request.getParameter("password");
        emp.setEmp_department_id(emp_department_id);
        emp.setEmp_designation_id(emp_designation_id);
        emp.setEmp_email(emp_email);
        emp.setEmp_id(new_emp_id);
        emp.setEmp_join_date(null);
        emp.setEmp_name(emp_name);
        emp.setEmp_notes(emp_notes);
        emp.setEmp_password(emp_password);
        emp.setEmp_phone(emp_phone);
        emp.setEmp_skills(emp_skills);
        emp.setEmp_username(emp_username);
        Query query = ses.createQuery("update EmployeeDetails emp set emp_id=:new_emp_id,emp_name=:emp_name,emp_designation_id=:emp_designation_id,emp_skills=:emp_skills,emp_department_id=:emp_department_id,emp_notes=:emp_notes,emp_email=:emp_email,emp_phone=:emp_phone,emp_username=:emp_username,emp_password=:emp_password where emp_id=:emp_id");
        query.setParameter(emp_id, emp_id);
        query.setParameter(new_emp_id, new_emp_id);
        query.setParameter(emp_name, emp_name);
        query.setParameter(emp_designation_id, emp_designation_id);
        query.setParameter(emp_skills, emp_skills);
        query.setParameter(emp_department_id, emp_department_id);
        query.setParameter(emp_notes, emp_notes);
        query.setParameter(emp_email, emp_email);
        query.setParameter(emp_username, emp_username);
        query.setParameter(emp_password, emp_password);
        int result = query.executeUpdate();
        out.print(result);
    }

}

My result when i try to update:

java.lang.IndexOutOfBoundsException: Remember that ordinal parameters are 1-based!
    org.hibernate.engine.query.ParameterMetadata.getOrdinalParameterDescriptor(ParameterMetadata.java:55)
    org.hibernate.engine.query.ParameterMetadata.getOrdinalParameterExpectedType(ParameterMetadata.java:61)
    org.hibernate.impl.AbstractQueryImpl.determineType(AbstractQueryImpl.java:382)
    org.hibernate.impl.AbstractQueryImpl.setParameter(AbstractQueryImpl.java:362)
    com.unisys.servlets.UpdateEmployee.doPost(UpdateEmployee.java:59)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:647)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:728)

I'm new to hibernate. I've looked up lot of references, any sort of guidance regarding updation would be much appreciated. I'm trying to update one value in the row using emp_id value and updating all the other columns in the database.

It might be caused by the parameters not being set in the order they appear within your query. You can add a parameter to the setParameter to specify the position. Like so: setParameter(1, new_emp_id, new_emp_id); Remember that the position starts at 1 not 0. — Andreas Brunnet
– Andreas Brunnet, Commented Aug 11, 2016 at 4:45
Remove colon in between this statement in update statement....emp_id=:new_emp_id — khaja firoz
– khaja firoz, Commented Aug 11, 2016 at 6:16

Hakuna Matata · Accepted Answer · 2016-08-12 04:43:14Z

1

Yes, you are correct. But here the guy is mixed up the "Named" parameters with indexed parameter concepts.

query.setParameter("emp_id", emp_id);
    query.setParameter("new_emp_id", new_emp_id);
    query.setParameter("emp_name", emp_name);
    query.setParameter("emp_designation_id", emp_designation_id);
    query.setParameter("emp_skills", emp_skills);
    query.setParameter("emp_department_id", emp_department_id);
    query.setParameter("emp_notes", emp_notes);
    query.setParameter("emp_email", emp_email);
    query.setParameter("emp_username", emp_username);
    query.setParameter("emp_password", emp_password);
    query.setParameter("emp_phone", emp_phone);
    int result = query.executeUpdate();

The main purpose of named parameter is we don't worry about the order and it is easily understandable and maintainable.

answered Aug 12, 2016 at 4:43

Hakuna Matata

7713 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

davidxxx · Accepted Answer · 2016-08-11 11:13:02Z

0

The java.lang.IndexOutOfBoundsException: Remember that ordinal parameters are 1-based! is misleading.

In your JPQL query, you use this condition emp_phone=:emp_phone with the emp_phone parameter but the parameter value is never set in the query :

    Query query = ses.createQuery(
     "update EmployeeDetails emp set..emp_phone=:emp_phone");

    query.setParameter(emp_id, emp_id);
    query.setParameter(new_emp_id, new_emp_id);
    query.setParameter(emp_name, emp_name);
    query.setParameter(emp_designation_id, emp_designation_id);
    query.setParameter(emp_skills, emp_skills);
    query.setParameter(emp_department_id, emp_department_id);
    query.setParameter(emp_notes, emp_notes);
    query.setParameter(emp_email, emp_email);
    query.setParameter(emp_username, emp_username);
    query.setParameter(emp_password, emp_password);
    int result = query.executeUpdate();

So, Hibernate throws an exception.

answered Aug 11, 2016 at 11:13

davidxxx

132k23 gold badges231 silver badges228 bronze badges

1 Comment

Hakuna Matata Over a year ago

Yes, you are correct. But here the guy is mixed up the "Named" parameters with indexed parameter concepts.

Collectives™ on Stack Overflow

Unable to update row in MySQL using hibernate

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related