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

}

  • 1
    点赞
  • 6
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值