Spring boot JPA call MySQL procedure

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);
}