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

1. Reason

we might face this exception if we are trying to execute stored procedure using JPA 2.1 and hibernate’s entity manager, and we 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;
}

The 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)

2. 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 the above correct usage will make the error disappear.

Happy Learning !!