HowToDoInJava

  • Python
  • Java
  • Spring Boot
  • Dark Mode
Home / Hibernate / Cannot mix named and positional parameters

[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.

exceptions-notes

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 !!

Share this:

  • Twitter
  • Facebook
  • LinkedIn
  • Reddit

About Lokesh Gupta

A family guy with fun loving nature. Love computers, programming and solving everyday problems. Find me on Facebook and Twitter.

Comments are closed on this article!

Search Tutorials

Hibernate Tutorial

  • Hibernate – Introduction
  • Hibernate – Hello World
  • Hibernate – Get/Fetch
  • Hibernate – Persist
  • Hibernate – Merge & Refresh
  • Hibernate – Get Entity Reference
  • Hibernate – BLOB
  • Hibernate – Save Update
  • Hibernate – Persistence LifeCycle
  • Hibernate – SessionFactory
  • Hibernate – Entities Equality
  • Hibernate – Cascade Types
  • Hibernate – Lazy Loading
  • Hibernate – Criteria Queries
  • Hibernate – HQL
  • Hibernate – Named Query
  • Hibernate – Mappings
  • Hibernate – First Level Cache
  • Hibernate – Second Level Cache
  • Hibernate – EhCache Configuration
  • Hibernate – OSCache Configuration
  • Hibernate – C3P0 Connection Pool
  • Hibernate – In memory Database
  • Hibernate – Bean Validation
  • Hibernate – Validator CDI
  • UnexpectedTypeException

Hibernate Annotations

  • Hibernate – JPA 2 Annotations
  • Annotations Vs Mappings
  • Hibernate – @Immutable
  • Hibernate – @NaturalId
  • Hibernate – @OneToMany
  • Hibernate – @ManyToMany
  • Hibernate – @OneToOne

Meta Links

  • About Me
  • Contact Us
  • Privacy policy
  • Advertise
  • Guest and Sponsored Posts

Recommended Reading

  • 10 Life Lessons
  • Secure Hash Algorithms
  • How Web Servers work?
  • How Java I/O Works Internally?
  • Best Way to Learn Java
  • Java Best Practices Guide
  • Microservices Tutorial
  • REST API Tutorial
  • How to Start New Blog

Copyright © 2020 · HowToDoInjava.com · All Rights Reserved. | Sitemap

  • Java 15 New Features
  • Sealed Classes and Interfaces
  • EdDSA (Ed25519 / Ed448)