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

    配置pom.xml

    1. "1.0" encoding="UTF-8"?>
    2. <project xmlns="http://maven.apache.org/POM/4.0.0"
    3. 	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    4. 	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    5. 	<modelVersion>4.0.0modelVersion>
    6. 	<parent>
    7. 		<groupId>org.springframework.bootgroupId>
    8. 		<artifactId>spring-boot-starter-parentartifactId>
    9. 		<version>2.2.0.M1version>
    10. 		<relativePath /> 
    11. 	parent>
    12. 	<groupId>com.demogroupId>
    13. 	<artifactId>SpringBootDataJPAartifactId>
    14. 	<version>0.0.1-SNAPSHOTversion>
    15. 	<name>SpringBootDataJPAname>
    16. 	<description>Spring Boot Data JPAdescription>
    17.  
    18. 	<properties>
    19. 		<java.version>1.8java.version>
    20. 	properties>
    21.  
    22. 	<dependencies>
    23. 		<dependency>
    24. 			<groupId>org.springframework.bootgroupId>
    25. 			<artifactId>spring-boot-starter-data-jpaartifactId>
    26. 		dependency>
    27.  
    28. 		<dependency>
    29. 			<groupId>mysqlgroupId>
    30. 			<artifactId>mysql-connector-javaartifactId>
    31. 			<scope>runtimescope>
    32. 		dependency>
    33.  
    34. 		<dependency>
    35. 			<groupId>net.bytebuddygroupId>
    36. 			<artifactId>byte-buddyartifactId>
    37. 			<version>1.9.12version>
    38. 		dependency>
    39.  
    40. 		<dependency>
    41. 			<groupId>org.springframework.bootgroupId>
    42. 			<artifactId>spring-boot-starter-testartifactId>
    43. 			<scope>testscope>
    44. 		dependency>
    45. 	dependencies>
    46.  
    47. 	<build>
    48. 		<plugins>
    49. 			<plugin>
    50. 				<groupId>org.springframework.bootgroupId>
    51. 				<artifactId>spring-boot-maven-pluginartifactId>
    52. 			plugin>
    53. 		plugins>
    54. 	build>
    55.  
    56. 	<repositories>
    57. 		<repository>
    58. 			<id>spring-snapshotsid>
    59. 			<name>Spring Snapshotsname>
    60. 			<url>https://repo.spring.io/snapshoturl>
    61. 			<snapshots>
    62. 				<enabled>trueenabled>
    63. 			snapshots>
    64. 		repository>
    65. 		<repository>
    66. 			<id>spring-milestonesid>
    67. 			<name>Spring Milestonesname>
    68. 			<url>https://repo.spring.io/milestoneurl>
    69. 		repository>
    70. 	repositories>
    71. 	<pluginRepositories>
    72. 		<pluginRepository>
    73. 			<id>spring-snapshotsid>
    74. 			<name>Spring Snapshotsname>
    75. 			<url>https://repo.spring.io/snapshoturl>
    76. 			<snapshots>
    77. 				<enabled>trueenabled>
    78. 			snapshots>
    79. 		pluginRepository>
    80. 		<pluginRepository>
    81. 			<id>spring-milestonesid>
    82. 			<name>Spring Milestonesname>
    83. 			<url>https://repo.spring.io/milestoneurl>
    84. 		pluginRepository>
    85. 	pluginRepositories>
    86.  
    87. project>


     

    创建数据库

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

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

    创建存储过程

    1. DELIMITER $$
    2. CREATE PROCEDURE sp_findBetween(min decimal, max decimal)
    3. BEGIN
    4. 	SELECT * FROM product where price BETWEEN min and max;
    5. END $$
    6. DELIMITER ;

    产品表的结构

    产品表数据


     

    数据库配置

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

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

    实体类

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

    1. package com.demo.entities;
    2.  
    3. import java.io.Serializable;
    4.  
    5. import java.math.BigDecimal;
    6.  
    7. import javax.persistence.Column;
    8. import javax.persistence.Entity;
    9. import javax.persistence.GeneratedValue;
    10. import javax.persistence.GenerationType;
    11. import javax.persistence.Id;
    12. import javax.persistence.Table;
    13.  
    14. @Entity
    15. @Table(name = "product")
    16. public class Product implements Serializable {
    17. 	private static final long serialVersionUID = 1L;
    18.  
    19. 	@Id
    20. 	@GeneratedValue(strategy = GenerationType.IDENTITY)
    21. 	private int id;
    22.  
    23. 	private String name;
    24.  
    25. 	private BigDecimal price;
    26.  
    27. 	private int quantity;
    28.  
    29. 	private String description;
    30.  
    31. 	private String photo;
    32.  
    33. 	private boolean featured;
    34.  
    35. 	public int getId() {
    36. 		return id;
    37. 	}
    38.  
    39. 	public void setId(int id) {
    40. 		this.id = id;
    41. 	}
    42.  
    43. 	public String getName() {
    44. 		return name;
    45. 	}
    46.  
    47. 	public void setName(String name) {
    48. 		this.name = name;
    49. 	}
    50.  
    51. 	public BigDecimal getPrice() {
    52. 		return price;
    53. 	}
    54.  
    55. 	public void setPrice(BigDecimal price) {
    56. 		this.price = price;
    57. 	}
    58.  
    59. 	public int getQuantity() {
    60. 		return quantity;
    61. 	}
    62.  
    63. 	public void setQuantity(int quantity) {
    64. 		this.quantity = quantity;
    65. 	}
    66.  
    67. 	public String getDescription() {
    68. 		return description;
    69. 	}
    70.  
    71. 	public void setDescription(String description) {
    72. 		this.description = description;
    73. 	}
    74.  
    75. 	public String getPhoto() {
    76. 		return photo;
    77. 	}
    78.  
    79. 	public void setPhoto(String photo) {
    80. 		this.photo = photo;
    81. 	}
    82.  
    83. 	public boolean isFeatured() {
    84. 		return featured;
    85. 	}
    86.  
    87. 	public void setFeatured(boolean featured) {
    88. 		this.featured = featured;
    89. 	}
    90.  
    91. }


     

    产品存储库接口

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

    1. package com.demo.repositories;
    2.  
    3. import java.math.BigDecimal;
    4. import java.util.List;
    5. import org.springframework.data.jpa.repository.Query;
    6. import org.springframework.data.repository.CrudRepository;
    7. import org.springframework.data.repository.query.Param;
    8. import org.springframework.stereotype.Repository;
    9. import com.demo.entities.Product;
    10.  
    11. @Repository("productRepository")
    12. public interface ProductRepository extends CrudRepository {
    13.  
    14. 	@Query(value = "{call sp_findBetween(:min, :max)}", nativeQuery = true)
    15. 	public List findAllBetweenStoredProcedure(@Param("min") BigDecimal min, @Param("max") BigDecimal max);
    16.  
    17. }

    产品服务接口

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

    1. package com.demo.services;
    2.  
    3. import java.math.BigDecimal;
    4. import java.util.List;
    5. import com.demo.entities.Product;
    6.  
    7. public interface ProductService {
    8.  
    9. 	public List findAllBetweenStoredProcedure(BigDecimal min, BigDecimal max);
    10.  
    11. }

    产品服务类

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

    1. package com.demo.services;
    2.  
    3. import java.math.BigDecimal;
    4. import java.util.List;
    5. import org.springframework.beans.factory.annotation.Autowired;
    6. import org.springframework.stereotype.Service;
    7. import org.springframework.transaction.annotation.Transactional;
    8. import com.demo.entities.Product;
    9. import com.demo.repositories.ProductRepository;
    10.  
    11. @Transactional
    12. @Service("productService")
    13. public class ProductServiceImpl implements ProductService {
    14.  
    15. 	@Autowired
    16. 	private ProductRepository productRepository;
    17.  
    18. 	@Override
    19. 	public List findAllBetweenStoredProcedure(BigDecimal min, BigDecimal max) {
    20. 		return productRepository.findAllBetweenStoredProcedure(min, max);
    21. 	}
    22.  
    23. }


     

    JPA 配置

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

    1. package com.demo;
    2.  
    3. import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
    4. import org.springframework.context.annotation.Bean;
    5. import org.springframework.context.annotation.ComponentScan;
    6. import org.springframework.context.annotation.Configuration;
    7. import org.springframework.context.annotation.PropertySource;
    8. import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
    9. import org.springframework.transaction.annotation.EnableTransactionManagement;
    10.  
    11. import com.demo.services.ProductService;
    12. import com.demo.services.ProductServiceImpl;
    13.  
    14. @Configuration
    15. @EnableAutoConfiguration
    16. @EnableTransactionManagement
    17. @EnableJpaRepositories(basePackages = { "com.demo.repositories" })
    18. @ComponentScan("com.demo")
    19. @PropertySource("classpath:application.properties")
    20. public class JPAConfiguration {
    21.  
    22. 	@Bean
    23. 	public ProductService productService() {
    24. 		return new ProductServiceImpl();
    25. 	}
    26.  
    27. }

    项目结构


     

    应用

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

    1. package com.demo.main;
    2.  
    3. import java.math.BigDecimal;
    4.  
    5. import org.springframework.context.annotation.AnnotationConfigApplicationContext;
    6. import org.springframework.context.support.AbstractApplicationContext;
    7.  
    8. import com.demo.JPAConfiguration;
    9. import com.demo.entities.Product;
    10. import com.demo.services.ProductService;
    11.  
    12. public class Demo {
    13.  
    14. 	public static void main(String[] args) {
    15. 		try {
    16. 			AbstractApplicationContext context = new AnnotationConfigApplicationContext(JPAConfiguration.class);
    17. 			ProductService productService = context.getBean(ProductService.class);
    18. 			System.out.println("Find product have price between 4 and 8");
    19. 			for (Product product : productService.findAllBetweenStoredProcedure(BigDecimal.valueOf(4), BigDecimal.valueOf(8))) {
    20. 				System.out.println("Id: " + product.getId());
    21. 				System.out.println("Name: " + product.getName());
    22. 				System.out.println("Price: " + product.getPrice());
    23. 				System.out.println("========================");
    24. 			}
    25. 			context.close();
    26. 		} catch (Exception e) {
    27. 			System.out.println(e.getMessage());
    28. 		}
    29. 	}
    30.  
    31. }

    输出

    1. Id: 4
    2. Name: Computer 1
    3. Price: 5.0
    4. ========================
    5. Id: 5
    6. Name: Computer 2
    7. Price: 7.0
    8. ========================
    9. Id: 12
    10. Name: Laptop 2
    11. Price: 4.0
    12. ========================
  • 相关阅读:
    MySQL数据库学习【进阶篇】
    ARM64平台jetson nano上安装PyQt5环境
    C语言-找鞍点
    Creator 2.4.x 分享游戏图片
    【接口干货】热门、免费api集合
    uniapp scroll-view 下拉刷新动画无法关闭问题
    智能语音外呼系统 OKCC没有电脑的坐席能不能开展工作
    Elasticsearch实现检索词自动补全(检索词补全，自动纠错，拼音补全，繁简转换) 包含demo
    时序预测 | MATLAB实现基于SVM-Adaboost支持向量机结合AdaBoost时间序列预测
    EdgeMoE: Fast On-Device Inference of MoE-based Large Language Models
  • 原文地址：https://blog.csdn.net/allway2/article/details/127908062