您现在的位置:首页 > >

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 , JpaSpecificationExecutor {


/**
* 调用存储过程
* 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);//调用成功
}



}

?


热文推荐
猜你喜欢
友情链接: 团党工作范文 工作范文 表格模版 社科文档网 营销文档资料 工程文档大全