Spring-data-jpa调用MySQL存储过程（返回值探究）

===================================================

由于多次测试，使用注解调用有返回值的存储过程较麻烦，这里先以远程SQL调用存储的方法进行测试。

===================================================

一、调用无返回值的存储过程

（1）实体类

package net.xiaof.testspringdatajpa.jpa.entity;

import lombok.Data;
import javax.persistence.*;

/**
 * @author: zhangxiaohu
 * @date: 2021/5/22 22:30
 * @Description:
 */
@Entity
@Table(name = "user")
@Data
public class User {

    /**
     * @GenericGenerator即Hibernate提供的主键生成策略:
     * table：使用一个特定的数据库表格来保存主键。
     * sequence：根据底层数据库的序列来生成主键，条件是数据库支持序列。这个值要与generator一起使用，generator 指定生成主键使用的生成器（可能是orcale中自己编写的序列）
     * identity：主键由数据库自动生成（主要是自动增长型数据库，如mysql）
     * auto：主键由程序控制，也是GenerationType的默认值
     */


//    @GenericGenerator(name = "idGenerator", strategy="identity")//Hibernate提供的主键生成策略注解
//    @GeneratedValue(generator = "idGenerator")//（JPA注解）使用generator引用了上面的name = "idGenerator"主键生成策略
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    @Column(name = "name")
    private String name;

    @Column(name = "age")
    private Integer age;

}

（2）Repository类

package net.xiaof.testspringdatajpa.jpa.repository;

import net.xiaof.testspringdatajpa.jpa.dto.AreaDto3;
import net.xiaof.testspringdatajpa.jpa.dto.AreaDto4;
import net.xiaof.testspringdatajpa.jpa.entity.User;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.jpa.repository.query.Procedure;
import org.springframework.data.repository.query.Param;

/**
 * @author: zhangxiaohu
 * @date: 2021/5/22 22:44
 * @Description:
 */
public interface UserRepository extends JpaRepository<User, Integer> {

    /**
     * 调用存储过程
     *
     * 执行日志：Hibernate: {call pro_model_area(?,?,?,?,?)}
     *
     * @param dept
     * @param arg1
     * @param arg2
     * @param arg3
     * @param arg4
     * @return
     */
    @Procedure(procedureName="pro_model_area")
    void callStoreProcess1(String dept, Integer arg1, Integer arg2, Integer arg3, Integer arg4);


}

（3）Controller类

package net.xiaof.testspringdatajpa.controller;

import net.xiaof.testspringdatajpa.jpa.dto.AreaDto;
import net.xiaof.testspringdatajpa.jpa.dto.AreaDto2;
import net.xiaof.testspringdatajpa.jpa.dto.AreaDto3;
import net.xiaof.testspringdatajpa.jpa.dto.AreaDto4;
import net.xiaof.testspringdatajpa.jpa.entity.User;
import net.xiaof.testspringdatajpa.jpa.repository.UserRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.web.bind.annotation.*;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.StoredProcedureQuery;
import java.util.List;
import java.util.Optional;

/**
 * @author: zhangxiaohu
 * @date: 2021/5/22 22:43
 * @Description:
 */
@RestController
@RequestMapping("/user")
public class UserController {

    @Autowired
    private UserRepository userRepository;

    /**
     * 测试调用存储过程，只调用，不接受返回值
     */
    @GetMapping("/callStoreProcess1")
    public void callStoreProcess1() {
        userRepository.callStoreProcess1("$,5bc3a2dc-3bd2-4376-bcc3-5612e28e55fe", 0, 0, 0, 0);
    }

}

---

二、（JPA类注解方式）调用有返回值的存储过程

（0）存储过程

# （1）返回结果集的存储过程，这里省略。

# （2）以下是返回一个整型/字符串的存储过程。此例中返回的id值是为了符合实体上的注解@Entity要求的主键id约束，接收数据要求存储过程必须返回id属性值（这里无实际意义）
CREATE DEFINER=`root`@`%` PROCEDURE `test_store_process1`(in param1 int(11),in param2 int(11))
BEGIN

/**
 * 使用方法： call test_store_process1(1,0)
 **/

 
SELECT 
22 as id,
NAME_NO as res 
from spfl_model_area_list_tmp t where t.`ORDER`=100;


END

（1）实体类

说明：@NamedStoredProcedureQuery注解

1）name为唯一名称，controller调用时用；procedureName 为存储过程名；@StoredProcedureParameter在本次测试中仅使用了入参功能（四个入参），出参ParameterMode.OUT没有测通，多一个出参，调用存储过程的语句自动会多一个占位符（如：call），与实际调用语句不符。
2）@Entity注解必须有
3）@Entity要求必须有主键id属性（存储过程可返回id任意值即可）
4）@Entity要求必须对应数据库表必须存在（JPA表检查用）

package net.xiaof.testspringdatajpa.jpa.dto;

import lombok.Data;

import javax.persistence.*;

/**
 * @author: zhangxiaohu
 * @date: 2021/5/22 23:53
 * @Description:
 */
@Entity
@NamedStoredProcedureQuery(name = "callResult1", procedureName = "pro_model_area",
        resultClasses = {AreaDto.class},
        parameters = {
                @StoredProcedureParameter(mode = ParameterMode.IN, name = "pOrgid", type = String.class),
                @StoredProcedureParameter(mode = ParameterMode.IN, name = "inMArea1", type = Integer.class),
                @StoredProcedureParameter(mode = ParameterMode.IN, name = "inMArea2", type = Integer.class),
                @StoredProcedureParameter(mode = ParameterMode.IN, name = "inMArea3", type = Integer.class),
                @StoredProcedureParameter(mode = ParameterMode.IN, name = "inMArea4", type = Integer.class)
})
@Data
public class AreaDto {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id=1;

    @Column(name = "name")
    private String name;
    @Column(name = "name_no")
    private String nameNo;
    @Column(name = "son_name")
    private String sonName;
    @Column(name = "domain")
    private String domain;
    @Column(name = "p_count")
    private String pCount;

}


// =============================================================================

package net.xiaof.testspringdatajpa.jpa.dto;

import lombok.Data;

import javax.persistence.*;

/**
 * @author: zhangxiaohu
 * @date: 2021/5/22 23:53
 *
 * 说明：
 * （1）@Entity必须有
 * （2）@Entity要求必须有主键id属性（存储过程可返回id任意值即可）
 * （3）@Entity要求必须对应数据库表必须存在（JPA表检查用）
 *
 */
@Entity
@NamedStoredProcedureQuery(name = "callResult2", procedureName = "test_store_process1",
        resultClasses = {AreaDto2.class},
        parameters = {
                @StoredProcedureParameter(mode = ParameterMode.IN, name = "param1", type = Integer.class),
                @StoredProcedureParameter(mode = ParameterMode.IN, name = "param2", type = Integer.class)
})
@Data
public class AreaDto2 {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id=1;

    @Column(name = "res")
    private String res;


}

（2）Repository类

此方法，不依赖与Repository类，可不写。

（3）Controller类

package net.xiaof.testspringdatajpa.controller;

import net.xiaof.testspringdatajpa.jpa.dto.AreaDto;
import net.xiaof.testspringdatajpa.jpa.dto.AreaDto2;
import net.xiaof.testspringdatajpa.jpa.entity.User;
import net.xiaof.testspringdatajpa.jpa.repository.UserRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.web.bind.annotation.*;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.StoredProcedureQuery;
import java.util.List;
import java.util.Optional;

/**
 * @author: zhangxiaohu
 * @date: 2021/5/22 22:43
 * @Description:
 */
@RestController
@RequestMapping("/user")
public class UserController {

    @PersistenceContext
    private EntityManager entityManager;

    /**
     * 测试调用存储过程，返回结果集
     */
    @GetMapping("/callStoreProcess2")
    public void callStoreProcess2() {
        StoredProcedureQuery store = this.entityManager.createNamedStoredProcedureQuery("callResult1");
        store.setParameter("pOrgid", "$,5bc3a2dc-3bd2-4376-bcc3-5612e28e55fe");
        store.setParameter("inMArea1", 1);
        store.setParameter("inMArea2", 1);
        store.setParameter("inMArea3", 1);
        store.setParameter("inMArea4", 1);

        List<AreaDto> alist = store.getResultList();//注意：接收数据的实体与存储过程返回的数据属性名相对应

        for (AreaDto a : alist) {
            System.out.println("【】"+a.toString());
        }
    }

    /**
     * 测试调用存储过程，返回整型（以集合来接收）
     */
    @GetMapping("/callStoreProcess3")
    public void callStoreProcess3() {
        StoredProcedureQuery store = this.entityManager.createNamedStoredProcedureQuery("callResult2");
        store.setParameter("param1", 1);
        store.setParameter("param2", 1);
        List<AreaDto2> alist = store.getResultList();//注意：接收数据的实体与存储过程返回的数据属性名相对应
        System.out.println("【Success】"+alist.get(0).getRes());
        // Hibernate: {call test_store_process1(?,?)}
        //【Success】1
    }

---

三、（Spring-data-jpa使用原生SQL方式）调用有返回值的存储过程（个人推荐使用）

（0）存储过程

# =========存储过程1：返回单个值=====================================================================
CREATE DEFINER=`root`@`%` PROCEDURE `test_store_process2`(in param1 int(11))
BEGIN

/**
 * 使用方法： call test_store_process2(5)
 **/

 
SELECT 
NAME_NO as res 
from spfl_model_area_list_tmp t where t.`ORDER`=100;


END






# =========存储过程2：返回两个值（实际返回三个值）============================================================
# 说明：此例中返回的id值是为了符合实体上的注解@Entity要求的主键id约束，接收数据要求存储过程必须返回id属性值（这里无实际意义）【切记：返回集合时，每条id要求值不同，因为id为主键约束，否则返回的集合多条内容完全相同】
CREATE DEFINER=`root`@`%` PROCEDURE `test_store_process3`(in param1 int(11),in param2 varchar(20))
BEGIN

/**
 * 使用方法： call test_store_process3(1,0)
 **/

 
SELECT 
1 as id,
domain as name,
NAME_NO as res 
from spfl_model_area_list_tmp t where t.`ORDER`=15;


END






# =========存储过程3：返回集合============================================================
# 说明：返回集合时，每条id要求值不同，因为id为主键约束，否则返回的集合多条内容完全相同
CREATE DEFINER=`root`@`%` PROCEDURE `test_store_process4`(in param1 int(11),in param2 varchar(20))
BEGIN

/**
 * 使用方法： call test_store_process4(1,0)
 **/

 
SELECT 
(@i:=@i+1) as id,
domain as name,
NAME_NO as res 
from spfl_model_area_list_tmp t,(select @i:=0) as it;


END

（1）实体类

说明： 接收单个返回值的存储过程时，此实体没有实际用处。

package net.xiaof.testspringdatajpa.jpa.dto;

import lombok.Data;

import javax.persistence.*;

/**
 * @author: zhangxiaohu
 * @date: 2021/5/22 23:53
 */
@Entity
@Data
public class AreaDto4 {

    /**
     * @GenericGenerator即Hibernate提供的主键生成策略:
     * table：使用一个特定的数据库表格来保存主键。
     * sequence：根据底层数据库的序列来生成主键，条件是数据库支持序列。这个值要与generator一起使用，generator 指定生成主键使用的生成器（可能是orcale中自己编写的序列）
     * identity：主键由数据库自动生成（主要是自动增长型数据库，如mysql）
     * auto：主键由程序控制，也是GenerationType的默认值
     */

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id=1;

    private String name;
    private Integer res;


}

（2）Repository类

package net.xiaof.testspringdatajpa.jpa.repository;

import net.xiaof.testspringdatajpa.jpa.dto.AreaDto3;
import net.xiaof.testspringdatajpa.jpa.dto.AreaDto4;
import net.xiaof.testspringdatajpa.jpa.entity.User;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.jpa.repository.query.Procedure;
import org.springframework.data.repository.query.Param;

import java.util.List;

/**
 * @author: zhangxiaohu
 * @date: 2021/5/22 22:44
 * @Description:
 */
//@NoRepositoryBean
public interface UserRepository extends JpaRepository<AreaDto4, Integer> {

    /**
     * 调用存储过程，返回一个单值，不依赖于当前类的父类JpaRepository<AreaDto4, Integer>中的泛型实体AreaDto4
     * @param param
     * @return
     */
    @Query(nativeQuery = true,value = "call test_store_process2(:param)")
    Integer callStoreProcess3(@Param("param") Integer param);

    /**
     * 调用存储过程，返回多个单值。这里以实体AreaDto4接收多个数据，【依赖】于当前类的父类JpaRepository<AreaDto4, Integer>中
     * 的泛型实体AreaDto4，需要注意AreaDto4属性与存储过程返回值属性对应
     * @param param1
     * @param param2
     * @return
     */
    @Query(nativeQuery = true,value = "call test_store_process3(:param1,:param2)")
    AreaDto4 callStoreProcess4(@Param("param1") Integer param1, @Param("param2") Integer param2);

    /**
     * 调用存储过程，返回集合。
     * （1）以List<AreaDto4>接收多个数据，【依赖】于当前类的父类JpaRepository<AreaDto4, Integer>中的泛型实体AreaDto4，需要注意AreaDto4属性与存储过程返回值属性对应。
     * （2）返回集合时，每条id要求值不同，因为id为主键约束，否则返回的集合多条内容完全相同
     * @param param1
     * @param param2
     * @return
     */
    @Query(nativeQuery = true,value = "call test_store_process4(:param1,:param2)")
    List<AreaDto4> callStoreProcess5(@Param("param1") Integer param1, @Param("param2") Integer param2);

}

（3）Controller类

package net.xiaof.testspringdatajpa.controller;

import net.xiaof.testspringdatajpa.jpa.dto.AreaDto4;
import net.xiaof.testspringdatajpa.jpa.entity.User;
import net.xiaof.testspringdatajpa.jpa.repository.UserRepository;
import org.hibernate.Hibernate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.web.bind.annotation.*;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.StoredProcedureQuery;
import java.util.List;
import java.util.Optional;

/**
 * @author: zhangxiaohu
 * @date: 2021/5/22 22:43
 * @Description:
 */
@RestController
@RequestMapping("/user")
public class UserController {

    @Autowired
    private UserRepository userRepository;

    /**
     * 测试调用存储过程，返回整型
     */
    @GetMapping("/callStoreProcess4")
    public void callStoreProcess4() {
        Integer res = userRepository.callStoreProcess3( 1);
        System.out.println("【Success】"+res);
//        Hibernate: call test_store_process2(?)
//      【Success】1
    }

    /**
     * 测试调用存储过程，返回多个单值（以对象接收）
     */
    @GetMapping("/callStoreProcess5")
    public void callStoreProcess5() {
        AreaDto4 a = userRepository.callStoreProcess4( 1,2);
        System.out.println("【Success】"+a.toString());
        // Hibernate: call test_store_process3(?,?)
        //【Success】AreaDto4(id=1, name=战略&运载&航天器&战术, res=15)
    }

    /**
     * 测试调用存储过程，返回集合
     */
    @GetMapping("/callStoreProcess6")
    public void callStoreProcess6() {
        List<AreaDto4> aList = userRepository.callStoreProcess5( 1,2);
        for (AreaDto4 a : aList) {
            System.out.println("【】"+a.toString());
        }
        // Hibernate: call test_store_process4(?, ?)
        //【】AreaDto4(id=1, name=战术, res=1)
        //【】AreaDto4(id=2, name=战略, res=2)
        //【】AreaDto4(id=3, name=航天器, res=4)
        //【】AreaDto4(id=4, name=运载, res=8)
        //【】AreaDto4(id=5, name=战略&战术, res=3)
        //【】AreaDto4(id=6, name=航天器&战术, res=5)
        //【】AreaDto4(id=7, name=运载&战术, res=9)
        //【】AreaDto4(id=8, name=战略&航天器, res=6)
        //【】AreaDto4(id=9, name=战略&运载, res=10)
        //【】AreaDto4(id=10, name=运载&航天器, res=12)
        //【】AreaDto4(id=11, name=战略&航天器&战术, res=7)
        //【】AreaDto4(id=12, name=战略&运载&战术, res=11)
        //【】AreaDto4(id=13, name=运载&航天器&战术, res=13)
        //【】AreaDto4(id=14, name=战略&运载&航天器, res=14)
        //【】AreaDto4(id=15, name=战略&运载&航天器&战术, res=15)
    }

}