

Table of Contents
1. Overview
In this article, we will see how we can call Spring boot JPA call MySQL procedure or stored procedure. While working with @Procedure
to call stored procedure at that time throwing an exception like org.springframework.dao.InvalidDataAccessApiUsageException: Type cannot be null; nested exception is java.lang.IllegalArgumentException: Type cannot be null
to overcome this issue we have tried so many solutions but not able to find the proper solution so finally decide to call the stored procedure using the native query whose example as below:
2. Example
2.1 MySQL Store Procedure
DROP PROCEDURE IF EXISTS demo_database.getEmployeeList; CREATE PROCEDURE demo_database.`getEmployeeList`() BEGIN select * FROM employee; END;
Spring Boot JPA code to call MySQL stored procedure:
package com.javadeveloperzone.dao; import com.javadeveloperzone.model.Employee; import org.springframework.data.jpa.repository.Query; import org.springframework.data.repository.CrudRepository; import org.springframework.data.repository.query.Param; import org.springframework.stereotype.Component; import org.springframework.transaction.annotation.Transactional; import java.util.List; /** * Created by Java Developer Zone on 03-08-2017. */ @Component @Transactional public interface EmployeeDAO extends CrudRepository<Employee,Integer> { @Query(nativeQuery = true,value = "call getEmployeeList") // call store procedure List<Employee> getEmployeeList(); }
2.2 MySQL Store Procedure with Parameter
DROP PROCEDURE IF EXISTS demo_database.getEmployeeByName; CREATE PROCEDURE demo_database.`getEmployeeByName`(name varchar(255)) BEGIN select * FROM employee where employeeName=name; END;
Spring Boot JPA code to call MySQL stored procedure:
package com.javadeveloperzone.dao; import com.javadeveloperzone.model.Employee; import org.springframework.data.jpa.repository.Query; import org.springframework.data.repository.CrudRepository; import org.springframework.data.repository.query.Param; import org.springframework.stereotype.Component; import org.springframework.transaction.annotation.Transactional; import java.util.List; /** * Created by Java Developer Zone on 03-08-2017. */ @Component @Transactional public interface EmployeeDAO extends CrudRepository<Employee,Integer> { @Query(nativeQuery = true,value = "call getEmployeeByName(:name)") // call store procedure with arguments List<Employee> getEmployeeByName(@Param("name")String name); }
Was this post helpful?
Let us know if you liked the post. That’s the only way we can improve.
1 comment. Leave new
thanks guys.