在 SPRING Boot JPA 中调用带有本机查询中的参数的存储过程

今天药忘吃喽~ 2023-09-25 13:03 102阅读 0赞

#### 配置pom.xml ####

<?xml version="1.0" encoding="UTF-8"?>
    <project xmlns="http://maven.apache.org/POM/4.0.0"
    	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    	<modelVersion>4.0.0</modelVersion>
    	<parent>
    		<groupId>org.springframework.boot</groupId>
    		<artifactId>spring-boot-starter-parent</artifactId>
    		<version>2.2.0.M1</version>
    		<relativePath /> 
    	</parent>
    	<groupId>com.demo</groupId>
    	<artifactId>SpringBootDataJPA</artifactId>
    	<version>0.0.1-SNAPSHOT</version>
    	<name>SpringBootDataJPA</name>
    	<description>Spring Boot Data JPA</description>
    
    	<properties>
    		<java.version>1.8</java.version>
    	</properties>
    
    	<dependencies>
    		<dependency>
    			<groupId>org.springframework.boot</groupId>
    			<artifactId>spring-boot-starter-data-jpa</artifactId>
    		</dependency>
    
    		<dependency>
    			<groupId>mysql</groupId>
    			<artifactId>mysql-connector-java</artifactId>
    			<scope>runtime</scope>
    		</dependency>
    
    		<dependency>
    			<groupId>net.bytebuddy</groupId>
    			<artifactId>byte-buddy</artifactId>
    			<version>1.9.12</version>
    		</dependency>
    
    		<dependency>
    			<groupId>org.springframework.boot</groupId>
    			<artifactId>spring-boot-starter-test</artifactId>
    			<scope>test</scope>
    		</dependency>
    	</dependencies>
    
    	<build>
    		<plugins>
    			<plugin>
    				<groupId>org.springframework.boot</groupId>
    				<artifactId>spring-boot-maven-plugin</artifactId>
    			</plugin>
    		</plugins>
    	</build>
    
    	<repositories>
    		<repository>
    			<id>spring-snapshots</id>
    			<name>Spring Snapshots</name>
    			<url>https://repo.spring.io/snapshot</url>
    			<snapshots>
    				<enabled>true</enabled>
    			</snapshots>
    		</repository>
    		<repository>
    			<id>spring-milestones</id>
    			<name>Spring Milestones</name>
    			<url>https://repo.spring.io/milestone</url>
    		</repository>
    	</repositories>
    	<pluginRepositories>
    		<pluginRepository>
    			<id>spring-snapshots</id>
    			<name>Spring Snapshots</name>
    			<url>https://repo.spring.io/snapshot</url>
    			<snapshots>
    				<enabled>true</enabled>
    			</snapshots>
    		</pluginRepository>
    		<pluginRepository>
    			<id>spring-milestones</id>
    			<name>Spring Milestones</name>
    			<url>https://repo.spring.io/milestone</url>
    		</pluginRepository>
    	</pluginRepositories>
    
    </project>

#### 创建数据库 ####

创建一个名为**springbootdatajpa** 的数据库。此数据库有一个表：**产品**

--
    -- Table structure for table `product`
    --
    
    CREATE TABLE `product` (
      `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
      `name` varchar(250) COLLATE utf8_unicode_ci NOT NULL,
      `price` decimal(10,1) NOT NULL,
      `quantity` int(11) NOT NULL,
      `description` text COLLATE utf8_unicode_ci NOT NULL,
      `photo` varchar(250) COLLATE utf8_unicode_ci NOT NULL,
      `featured` tinyint(1) NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
    
    --
    -- Dumping data for table `product`
    --
    
    INSERT INTO `product` (`name`, `price`, `quantity`, `description`, `photo`, `featured`) VALUES('Mobile 1', '2.0', 2, 'description 1', 'thumb1.gif', 0);
    INSERT INTO `product` (`name`, `price`, `quantity`, `description`, `photo`, `featured`) VALUES('Mobile 2', '1.0', 5, 'description 2', 'thumb2.gif', 1);
    INSERT INTO `product` (`name`, `price`, `quantity`, `description`, `photo`, `featured`) VALUES('Mobile 3', '3.0', 9, 'description 3', 'thumb3.gif', 0);
    INSERT INTO `product` (`name`, `price`, `quantity`, `description`, `photo`, `featured`) VALUES('Computer 1', '5.0', 12, 'description 4', 'thumb1.gif', 1);
    INSERT INTO `product` (`name`, `price`, `quantity`, `description`, `photo`, `featured`) VALUES('Computer 2', '7.0', 5, 'description 5', 'thumb1.gif', 0);
    INSERT INTO `product` (`name`, `price`, `quantity`, `description`, `photo`, `featured`) VALUES('Computer 3', '12.0', 2, 'description 6', 'thumb2.gif', 1);
    INSERT INTO `product` (`name`, `price`, `quantity`, `description`, `photo`, `featured`) VALUES('Laptop 1', '3.0', 8, 'description 7', 'thumb2.gif', 0);
    INSERT INTO `product` (`name`, `price`, `quantity`, `description`, `photo`, `featured`) VALUES('Laptop 2', '4.0', 11, 'description 8', 'thumb3.gif', 1);
    INSERT INTO `product` (`name`, `price`, `quantity`, `description`, `photo`, `featured`) VALUES('Laptop 3', '2.0', 15, 'description 9', 'thumb2.gif', 0);

#### 创建存储过程 ####

DELIMITER $$
    CREATE PROCEDURE sp_findBetween(min decimal, max decimal)
    BEGIN
    	SELECT * FROM product where price BETWEEN min and max;
    END $$
    DELIMITER ;

产品表的结构

![f433073e3c809b5a1a2ddfaa65003007.png][]

产品表数据

![0d2d177a626afe4d16973386429111cd.png][]

#### 数据库配置 ####

在**src/main/resources**文件夹中打开**application.properties**文件，并添加连接到数据库的配置，如下所示：

spring.datasource.url= jdbc:mysql://localhost:3306/springbootdatajpa
    spring.datasource.username=root
    spring.datasource.password=123456

#### 实体类 ####

创建名为**com.demo.entities** 的新包。在此包中，创建名为 Product 的新 java 类**.java**如下所示：

package com.demo.entities;
    
    import java.io.Serializable;
    
    import java.math.BigDecimal;
    
    import javax.persistence.Column;
    import javax.persistence.Entity;
    import javax.persistence.GeneratedValue;
    import javax.persistence.GenerationType;
    import javax.persistence.Id;
    import javax.persistence.Table;
    
    @Entity
    @Table(name = "product")
    public class Product implements Serializable {
    	private static final long serialVersionUID = 1L;
    
    	@Id
    	@GeneratedValue(strategy = GenerationType.IDENTITY)
    	private int id;
    
    	private String name;
    
    	private BigDecimal price;
    
    	private int quantity;
    
    	private String description;
    
    	private String photo;
    
    	private boolean featured;
    
    	public int getId() {
    		return id;
    	}
    
    	public void setId(int id) {
    		this.id = id;
    	}
    
    	public String getName() {
    		return name;
    	}
    
    	public void setName(String name) {
    		this.name = name;
    	}
    
    	public BigDecimal getPrice() {
    		return price;
    	}
    
    	public void setPrice(BigDecimal price) {
    		this.price = price;
    	}
    
    	public int getQuantity() {
    		return quantity;
    	}
    
    	public void setQuantity(int quantity) {
    		this.quantity = quantity;
    	}
    
    	public String getDescription() {
    		return description;
    	}
    
    	public void setDescription(String description) {
    		this.description = description;
    	}
    
    	public String getPhoto() {
    		return photo;
    	}
    
    	public void setPhoto(String photo) {
    		this.photo = photo;
    	}
    
    	public boolean isFeatured() {
    		return featured;
    	}
    
    	public void setFeatured(boolean featured) {
    		this.featured = featured;
    	}
    
    }

#### 产品存储库接口 ####

创建名为**com.demo.repository** 的新包。在这个包中，创建名为**ProductRepository**的新接口.java从 Spring Framework 的**CrudRepository**接口实现如下：

package com.demo.repositories;
    
    import java.math.BigDecimal;
    import java.util.List;
    import org.springframework.data.jpa.repository.Query;
    import org.springframework.data.repository.CrudRepository;
    import org.springframework.data.repository.query.Param;
    import org.springframework.stereotype.Repository;
    import com.demo.entities.Product;
    
    @Repository("productRepository")
    public interface ProductRepository extends CrudRepository<Product, Integer> {
    
    	@Query(value = "{call sp_findBetween(:min, :max)}", nativeQuery = true)
    	public List<Product> findAllBetweenStoredProcedure(@Param("min") BigDecimal min, @Param("max") BigDecimal max);
    
    }

#### 产品服务接口 ####

创建名为**com.demo.services** 的新包。在此包中创建名为**ProductService**的新接口.java如下所示：

package com.demo.services;
    
    import java.math.BigDecimal;
    import java.util.List;
    import com.demo.entities.Product;
    
    public interface ProductService {
    
    	public List<Product> findAllBetweenStoredProcedure(BigDecimal min, BigDecimal max);
    
    }

#### 产品服务类 ####

在**com.demo.services**包中，创建名为**ProductServiceImpl**的新java类.java从**ProductService**接口实现

package com.demo.services;
    
    import java.math.BigDecimal;
    import java.util.List;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;
    import org.springframework.transaction.annotation.Transactional;
    import com.demo.entities.Product;
    import com.demo.repositories.ProductRepository;
    
    @Transactional
    @Service("productService")
    public class ProductServiceImpl implements ProductService {
    
    	@Autowired
    	private ProductRepository productRepository;
    
    	@Override
    	public List<Product> findAllBetweenStoredProcedure(BigDecimal min, BigDecimal max) {
    		return productRepository.findAllBetweenStoredProcedure(min, max);
    	}
    
    }

#### JPA 配置 ####

在**com.demo**包中，创建名为**JPAConfiguration**的新 java 类.java如下所示：

package com.demo;
    
    import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.ComponentScan;
    import org.springframework.context.annotation.Configuration;
    import org.springframework.context.annotation.PropertySource;
    import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
    import org.springframework.transaction.annotation.EnableTransactionManagement;
    
    import com.demo.services.ProductService;
    import com.demo.services.ProductServiceImpl;
    
    @Configuration
    @EnableAutoConfiguration
    @EnableTransactionManagement
    @EnableJpaRepositories(basePackages = { "com.demo.repositories" })
    @ComponentScan("com.demo")
    @PropertySource("classpath:application.properties")
    public class JPAConfiguration {
    
    	@Bean
    	public ProductService productService() {
    		return new ProductServiceImpl();
    	}
    
    }

#### 项目结构 ####

![1dc5cda29939812792a286ead2a73f1d.png][]

#### 应用 ####

创建名为**com.demo.main** 的新包。在此包中，创建名为 demo 的新 java 文件**.java**如下所示：

package com.demo.main;
    
    import java.math.BigDecimal;
    
    import org.springframework.context.annotation.AnnotationConfigApplicationContext;
    import org.springframework.context.support.AbstractApplicationContext;
    
    import com.demo.JPAConfiguration;
    import com.demo.entities.Product;
    import com.demo.services.ProductService;
    
    public class Demo {
    
    	public static void main(String[] args) {
    		try {
    			AbstractApplicationContext context = new AnnotationConfigApplicationContext(JPAConfiguration.class);
    			ProductService productService = context.getBean(ProductService.class);
    			System.out.println("Find product have price between 4 and 8");
    			for (Product product : productService.findAllBetweenStoredProcedure(BigDecimal.valueOf(4), BigDecimal.valueOf(8))) {
    				System.out.println("Id: " + product.getId());
    				System.out.println("Name: " + product.getName());
    				System.out.println("Price: " + product.getPrice());
    				System.out.println("========================");
    			}
    			context.close();
    		} catch (Exception e) {
    			System.out.println(e.getMessage());
    		}
    	}
    
    }

#### 输出 ####

Id: 4
    Name: Computer 1
    Price: 5.0
    ========================
    Id: 5
    Name: Computer 2
    Price: 7.0
    ========================
    Id: 12
    Name: Laptop 2
    Price: 4.0
    ========================

[f433073e3c809b5a1a2ddfaa65003007.png]: https://img-blog.csdnimg.cn/img_convert/f433073e3c809b5a1a2ddfaa65003007.png
[0d2d177a626afe4d16973386429111cd.png]: https://img-blog.csdnimg.cn/img_convert/0d2d177a626afe4d16973386429111cd.png
[1dc5cda29939812792a286ead2a73f1d.png]: https://img-blog.csdnimg.cn/img_convert/1dc5cda29939812792a286ead2a73f1d.png