[Solved] org.hibernate.QueryException: Cannot mix named and positional parameters

You might face this exception if you are trying execute stored procedure using JPA 2.1 and hibernate’s entity manager, and you are not using the entity manager’s registerStoredProcedureParameter() correctly.

Incorrect usage may look like below OR the one you have written.

@PersistenceContext
private EntityManager manager;

@Override
public boolean addEmployee(String firstName, String lastName, String email, Integer departmentId) {
	try 
	{
		StoredProcedureQuery storedProcedure = manager.createStoredProcedureQuery("ADD_EMPLOYEE_PROCEDURE")
				.registerStoredProcedureParameter(firstName , String.class , ParameterMode.IN)
				.registerStoredProcedureParameter(lastName , String.class, ParameterMode.IN)
				.registerStoredProcedureParameter(email , String.class, ParameterMode.IN)
				.registerStoredProcedureParameter(departmentId , Integer.class, ParameterMode.IN);
		
		storedProcedure.execute();
	} 
	catch (Exception e) {
		e.printStackTrace();
		return false;
	}
	return true;
}

Exception will look like this:

java.lang.IllegalArgumentException: org.hibernate.QueryException: Cannot mix named and positional parameters
	at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1750)
	at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1677)
	at org.hibernate.jpa.internal.StoredProcedureQueryImpl.registerStoredProcedureParameter(StoredProcedureQueryImpl.java:136)
	at com.howtodoinjava.jpa.demo.dao.EmployeeDAOImpl.addEmployee(EmployeeDAOImpl.java:31)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
	at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:281)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207)
	at com.sun.proxy.$Proxy31.addEmployee(Unknown Source)
	at com.jpa.demo.test.TestEmployeeDAO.testUpdateEmployee(TestEmployeeDAO.java:35)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)
	at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
	at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
	at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
	at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
	at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:73)
	at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:82)
	at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:73)
	at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
	at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:217)
	at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:83)
	at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
	at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
	at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
	at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
	at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
	at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
	at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:68)
	at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
	at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:163)
	at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:50)
	at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:459)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:675)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:382)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:192)
Caused by: org.hibernate.QueryException: Cannot mix named and positional parameters
	at org.hibernate.procedure.internal.ProcedureCallImpl.prepareForPositionalParameters(ProcedureCallImpl.java:303)
	at org.hibernate.procedure.internal.ProcedureCallImpl.registerParameter(ProcedureCallImpl.java:293)
	at org.hibernate.procedure.internal.ProcedureCallImpl.registerParameter(ProcedureCallImpl.java:277)
	at org.hibernate.jpa.internal.StoredProcedureQueryImpl.registerStoredProcedureParameter(StoredProcedureQueryImpl.java:128)

Solution

To fix this error, use correctly the registerStoredProcedureParameter() and setParameter() methods like below.

Read More : How to execute stored procedure with JPA 2.1 and Hibernate

@PersistenceContext
private EntityManager manager;

@Override
public boolean addEmployee(String firstName, String lastName, String email, Integer departmentId) {
	try 
	{
		StoredProcedureQuery storedProcedure = manager.createStoredProcedureQuery("ADD_EMPLOYEE_PROCEDURE")
				.registerStoredProcedureParameter(0 , String.class , ParameterMode.IN)
				.registerStoredProcedureParameter(1 , String.class, ParameterMode.IN)
				.registerStoredProcedureParameter(2 , String.class, ParameterMode.IN)
				.registerStoredProcedureParameter(3 , Integer.class, ParameterMode.IN);
		
		storedProcedure	.setParameter(0, firstName)
						.setParameter(1, lastName)
						.setParameter(2, email)
						.setParameter(3, departmentId);
		
		storedProcedure.execute();
	} 
	catch (Exception e) {
		e.printStackTrace();
		return false;
	}
	return true;
}

Fixing above correct usage will make the error disappear.

Happy Learning !!