springboot_springdata-jpa @Procedure调用存储过程查询方法

项目地址: https://github.com/heng1234/springdata-jpa 

1、mysql数据创建表和存储过程

 CREATE TABLE `user` (
	`id` INT (11) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR (50) NOT NULL,
	`email` VARCHAR (200) NOT NULL,
	PRIMARY KEY (`id`)
)



-- 创建存储过程

create procedure pluslinout(IN arg int, OUT res int)
BEGIN
select (arg+10) into res;
END

2、在springboot项目创建User Entity

package com.hvly.springjp_1.com.hlvy.entity;


import lombok.Data;

import javax.persistence.*;

/**
 * User
 *
 * @author heng
 **/
//@Data//lombok里的
@Entity(name = "User")

/**
 *  @Procedure存储过程查询方法
 *  调用数据库存储过程需要在实体类定义定义
 * name: 在EntityManager中的名字 NamedStoredProcedureQuery使用
 * procedureName: 数据库里存储过程的名字
 * parameters: 使用IN/OUT参数
 * 
 * 存储过程使用了注解@NamedStoredProcedureQuery 并绑定到一个JPA表。
 * procedureName是存储过程的名字
 * name是JPA中存储过程的名字
 * 使用注解@StoredProcedureParameter来定义存储过程使用的IN/OU参书
 */

@NamedStoredProcedureQuery(name = "User.plusl",procedureName = "pluslinout",parameters = {
        @StoredProcedureParameter(mode = ParameterMode.IN,name = "arg",type=Integer.class),
        @StoredProcedureParameter(mode = ParameterMode.OUT,name="res",type = Integer.class)
})
public class User  {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)//自动增递
    private Long id;

    private String name;

    private String email;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }
}

JpaRepository

package com.hvly.springjp_1.com.hlvy.repository;

import com.hvly.springjp_1.com.hlvy.entity.User;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.repository.*;
import org.springframework.data.jpa.repository.query.Procedure;
import org.springframework.data.repository.query.Param;
import org.springframework.transaction.annotation.Transactional;

import javax.persistence.QueryHint;
import java.util.List;

/**
 * UserRepository
 *
 * @author heng
 **/
public interface UserJpaRepository extends JpaRepository<User,Long> , JpaSpecificationExecutor<User> {
    
  
    /**
     * 调用存储过程
     * pluslinout 存储过程名字
     * @param arg
     * @return
     */
    @Procedure("pluslinout")
    Integer explicitlyNamedPluslinout(Integer arg);
    /**
     * 调用存储过程
     * pluslinout 存储过程名字
     * @param arg
     * @return
     */
    @Procedure(procedureName = "pluslinout")
    Integer pluslinout(Integer arg);

    /**
     *  User.pluslIO自定义存储过程的名字
     * @param arg
     * @return
     */
    @Procedure(name = "User.plusl")
    Integer entityAnnotatedCustomNamedProcedurePluslIO(@Param("arg") Integer arg);


}

Controller

package com.hvly.springjp_1.com.hlvy.controller;

import com.hvly.springjp_1.com.hlvy.entity.SeLPUser;
import com.hvly.springjp_1.com.hlvy.entity.User;
import com.hvly.springjp_1.com.hlvy.repository.SpELUserRepository;
import com.hvly.springjp_1.com.hlvy.repository.UserAtRepository;
import com.hvly.springjp_1.com.hlvy.repository.UserJpaRepository;
import com.hvly.springjp_1.com.hlvy.repository.UserRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.domain.JpaSort;
import org.springframework.web.bind.annotation.*;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.util.List;

/**
 * UserController
 *
 * @author heng
 **/
@RestController
@RequestMapping("user")
public class UserController {

  

    @Autowired
    private UserJpaRepository userJpaRepository;

  
    /**
     * @Procedure
     * 调用存储过程
     */
    @RequestMapping("explicitlyNamedPluslinout")
    public Integer explicitlyNamedPluslinout(int arg){
       // return userJpaRepository.explicitlyNamedPluslinout(arg);//调用成功
       // return userJpaRepository.pluslinout(arg);//调用成功
        return userJpaRepository.entityAnnotatedCustomNamedProcedurePluslIO(arg);//调用成功
    }



}