Springboot - Jpa运用MySQL的存储过程

一、什么是存储过程？

MySQL 5.0 版本开始支持存储过程。

存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。

存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。

存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

• 存储过程（procedure）类似于C语言中的函数
• 用来执行管理任务或应用复杂的业务规则
• 存储过程可以带参数，也可以返回结果
• 存储过程可以包含数据操纵语句、变量、逻辑 控制语句等 

优点：

1. 执行速度快：存储过程创建是就已经通过语法检查和性能优化，在执行时无需每次编译。存储在数据库服务器，性能高。
2. 允许模块化设计：只需创建存储过程一次并将其存储在数据库中，以后即可在程序中调用该过程任意次。存储过程可由在数据库编程方面有专长的人员创建，并可独立于程序源代码而单独修改 。
3. 提高系统安全性：可将存储过程作为用户存取数据的管道。可以限制用户对数据表的存取权限，建立特定的存储过程供用户使用，完成对数据的访问。存储过程的定义文本可以被加密，使用户不能查看其内容。
4. 减少网络流量：一个需要数百行Transact-SQL代码的操作由一条执行过程代码的单独语句就可实现，而不需要在网络中发送数百行代码。

缺点：

1. 存储过程会使得数据库占用的系统资源加大(cpu、memory)，数据库毕竟主要用来做数据存取的，并不进行复杂的业务逻辑操作。
2. 因为存储过程依旧是sql，所以没办法像编程语言那样写出复杂业务逻辑对应的存储过程。
3. 存储过程不容易进行调试。
4. 存储过程书写及维护难度都比较大。

 

二、在数据库中创建一个存储过程

创建存储过程：

DELIMITER $$
CREATE PROCEDURE encyclopedia.taxontop(IN top int)
BEGIN
	SELECT * FROM encyclopedia.taxon order by browse desc limit top;
END$$
DELIMITER ;

删除存储过程：

drop procedure  —— 删除存储过程
drop function  —— 删除存储函数

DROP {PROCEDURE|FUNCTION} Sp_name; 

相关语法学习：https://www.runoob.com/w3cnote/mysql-stored-procedure.html

在数据库中调用：

CALL encyclopedia.taxontop(20); 

 

三、spring data jpa调用存储过程

pom文件：

<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

实体类：

import java.io.Serializable;
import java.sql.Timestamp;

import javax.persistence.*;
import javax.validation.constraints.NotBlank;

import org.hibernate.annotations.Fetch;
import org.hibernate.annotations.FetchMode;
import org.hibernate.annotations.TypeDef;

import java.util.Date;
import java.util.List;

@Entity
@Table(name = "taxon", schema = "encyclopedia")
@NamedStoredProcedureQuery(name = "taxontop", procedureName = "taxontop",resultClasses = {Taxon.class},
	parameters = {
			@StoredProcedureParameter(mode = ParameterMode.IN, name = "top", type = Integer.class),
})
public class Taxon implements Serializable {
	private static final long serialVersionUID = 1L;

	@Id
	@Column(length=50)
	private String id;
	
	@Column(columnDefinition="varchar(1000)" )
	private String scientificname;
	
        public String getScientificname() {
		return scientificname;
	}

	public void setScientificname(String scientificname) {
		this.scientificname = scientificname;
	}

        ...

}

说明：

@Entity注解的作用是声明这是一个实体类

@Table注解，表名库名和表名

@NamedStoredProcedureQuery 申请一个存储过程

1. name属性是给这个存储结构起一个名字
2. procedureName属性是存储结构在数据库中的名字
3. resultClasses属性声明这个存储过程返回的结果集的类型
4. parameters属性声明这个存储结构的参数

 

测试：

@PersistenceContext
private EntityManager entityManager;

@RequestMapping(value="/demo", method = {RequestMethod.GET})
public String RestAPI(Model model,HttpServletRequest request) {
    	StoredProcedureQuery store = this.entityManager.createNamedStoredProcedureQuery("taxontop");
        store.setParameter("top", 20);
        List<Taxon> taxons =  store.getResultList();
        System.out.println(taxons.size());
        taxons.forEach((taxon) -> System.out.println(taxon.getScientificname()));
        
    	return "demo";
}

• createNamedStoredProcedureQuery()方法创建一个查询对象
• setParameter()来设置参数的值
• getResultList()方法来获取结果集

效果图：